Top Nav

Archive | MySQL

Add Extra MySQL Root User

To add an extra “root” capable MySQL user start by logging in to the server on SSH and connect to MySQL CLI using an existing “root” level account. Next add the new user account:

Next grant permissions:

Now flush the privileges cache:

You might want to double check the results with:

 

 

0

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