Top Nav

Archive | MySQL

MySQL – Run Command On All Tables

Here’s a one-liner to run a SQL command on all tables in a database:

  • Replace “show create table” with the command that you wish to execute.
  • Replace “mydb” with your database name.
  • Replace “root” and “mypassword” with you username and password.
  • Replace “localhost” with your database server hostname or IP address.

 

0

Unknown collation: ‘utf8mb4_unicode_ci’

When trying to import MySQL dumps from newer servers to older servers you may get:

or:

The quick and dirty solution is to do a search and replace on the database dump changing “utf8mb4_unicode_ci” and “utf8mb4_unicode_520_ci” to “utf8_unicode_ci”.

This can be accomplished with sed:

0

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