Archive | Databases

Add Extra MySQL Root User

By admin on February 27, 2020 in MySQL

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:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'somepass';

1	CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'somepass';

Next grant permissions:

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' WITH GRANT OPTION;

1	GRANT ALL PRIVILEGES ON . TO 'myuser'@'localhost' WITH GRANT OPTION;

Now flush the privileges cache:

FLUSH PRIVILEGES;

1	FLUSH PRIVILEGES;

You might want to double check the results with:

SHOW GRANTS FOR 'myuser'@'localhost';

1	SHOW GRANTS FOR 'myuser'@'localhost';

MySQL – Run Command On All Tables

By admin on January 1, 2020 in MariaDB, MySQL, Uncategorized

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

mysql -h localhost --password=mypassword -u root -Nse 'show tables' mydb \
   | while read table; do mysql -h localhost -p --password=mypassword -u root \
   -e "show create table $table" mydb; done

mysql -h localhost --password=mypassword -u root -Nse 'show tables' mydb \

| while read table; do mysql -h localhost -p --password=mypassword -u root \

-e "show create table $table" mydb; done

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.

Unknown collation: ‘utf8mb4_unicode_ci’

By admin on December 16, 2018 in MySQL

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

Unknown collation: 'utf8mb4_unicode_ci'

1	Unknown collation: 'utf8mb4_unicode_ci'

or:

Unknown collation: 'utf8mb4_unicode_520_ci'

1	Unknown collation: 'utf8mb4_unicode_520_ci'

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:

sed -i 's/utf8mb4_unicode_520_ci/utf8_unicode_ci/g' your_file.sql
sed -i 's/utf8mb4/utf8/g' your_file.sql

1 2	sed -i 's/utf8mb4_unicode_520_ci/utf8_unicode_ci/g' your_file.sql sed -i 's/utf8mb4/utf8/g' your_file.sql

PostgreSQL DESCRIBE TABLE Equivalent

By admin on November 13, 2017 in PostgreSQL

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:

\d+ tablename

1	\d+ tablename

Change Minimum Word Length In Fulltext Search

By admin on April 21, 2017 in MySQL

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

ft_min_word_len = 3

1	ft_min_word_len = 3

Restart MySQL to apply the change.

Get a list of full text indexes using this query:

use information_schema;
SELECT TABLE_SCHEMA, TABLE_NAME FROM statistics WHERE index_type LIKE 'FULLTEXT%';

1 2	use information_schema; SELECT TABLE_SCHEMA, TABLE_NAME FROM statistics WHERE index_type LIKE 'FULLTEXT%';

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

repair table [dbname].[tablename] quick;

1	repair table [dbname].[tablename] quick;

1 2 … 8 Next →

Top Nav

Navigation