Top Nav

Archive | MySQL

Change Minimum Word Length In Fulltext Search

By default, MySQL sets the minimum word length in full text search indexes to 4 characters. Here are the steps to lower this limit to 3.

Edit /etc/my.cnf and add

Restart MySQL to apply the change.

Get a list of full text indexes using this query:

Do a repair on each table with a full text index:

 

 

 

0

MySQL InnoDB File Per Table

By default MySQL 5.5 and prior store all InnoDB tables in a single table space or file. Improved performance and managability can be accheived by using the innodb_file_per_table option to cause MySQL to use a separate file for each table. This option is enabled by default on MySQL 5.6 and later.

To implement:
1. Add option to my.cnf:

2. Change table to use single file:

If you want to covert all databases then dump the entire database and import back into the server.

 

0

Mysqldump TRIGGERS Only

Here’s the command to dump the triggers and stored procedures for a database:

 

0

Revoke MySQL DROP Privilege On Plesk Database User

Had a request from client to add database user on a Plesk server but to remove the DROP DATABASE privilege so that they could not accidentally delete their database. By default, database users created through Plesk do have the DROP DATABASE privilege. Plesk does not currently allow fine-grained control over database privileges so the only way to implement this change is to modify the privileges directly from a MySQL command line. After some testing we found these command will have the desired effect:

Of course replace “testdb” and “testdbuser” with your actual database and username.

0

Scripting CHECK / REPAIR TABLES

Here’s a one-liner that will check all tables in a database:

http://codeinthehole.com/writing/checking-all-mysql-tables/

And here’s an article that shows the use of mysqlcheck:

http://www.thegeekstuff.com/2011/12/mysqlcheck/

mysqlcheck, unlike myisamchk, can be run when the mysql daemon is running

This command will check, repair and optimize all tables in all databases:

 

0