Top Nav

Archive | Databases

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

PostgreSQL DESCRIBE TABLE Equivalent

In MySQL I always use “DESCRIBE TABLE” or “SHOW CREATE TABLE” to show the schema of a table but I always forget how to do this in PostgreSQL. The PostgreSQL equivalent is:

 

 

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