Category: MySQL

MySQL – Run Command On All Tables

January 1, 2020

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’

December 16, 2018

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

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

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

Change Minimum Word Length In Fulltext Search

April 21, 2017

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%';

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;

MySQL InnoDB File Per Table

May 22, 2016

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:

[mysqld]
innodb_file_per_table=1

1 2	[mysqld] innodb_file_per_table=1

2. Change table to use single file:

ALTER TABLE table_name ENGINE=InnoDB;

1	ALTER TABLE table_name ENGINE=InnoDB;

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

mysqldump --all-databases > db.sql
mysql < db.sql

mysqldump --all-databases > db.sql

mysql < db.sql

Mysqldump TRIGGERS Only

September 17, 2015

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

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt [dbname]

1	mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt [dbname]

Reliable Penguin provides managed web hosting, systems administration, website and server migrations, and expert consulting.

Recent Updates

Fixing WP‑CLI “eval()’d code” Parse Errors on PHP 8.4 During “wp db import”

Force a 404 With a CloudFront Function (Always)

HTTP Basic Auth Is Dead

WordPress 6.8 Speculative Loading Can Break HTTP Basic Auth

Zero‑Trust for WordPress Admin with Cloudflare Access (Email One‑Time Codes)