How to optimize and repair MySQL database to improve server/vps performance

[X]

We will discuss about analyze, repair, and optimize MySQL/MariaDB tables and databases.

Check one table in the database

The following command will check the table posts in the database blog:

$ mysqlcheck -c blog posts
blog.posts                         OK

If your database is protected by a password add -u root -p at the end of the command:

$ mysqlcheck -c blog posts -u root -p
Enter password:
blog.posts                         OK

Analyze all tables in a database

The following command will check the table posts in the database blog:

$ mysqlcheck -a blog posts
blog.posts                         OK

If the MySQL/MariaDB server is running on a remote host, add -h at the end of the command:

$ mysqlcheck -a blog posts -h remotehost.com
blog.posts                         OK

Optimize all tables in all database

$ mysqlcheck -o --all-databases
blog.users
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
mysql.time_zone_transition_type                    Table is already up to date

Table does not support optimize, doing recreate + analyze instead means that we’re doing OPTIMIZE on an InnoDB table that doesn’t support this option. When doing OPTIMIZE on an InnoDB table it creates an empty table, copies all rows from the existing table into to the new one, deletes the old one and renames the new table, and then runs ANALYZE on the table.
Table is already up to date means that the table is already up to date and there is no need to check it.

Repair multiple databases

The following command will repair all tables in both blog and blog2 databases:

$ mysqlcheck -r --databases blog blog2

If you see note : The storage engine for the table doesn't support repair it means that you are doing REPAIR on an InnoDB.

Optimize and repair all tables in all databases

The following command will check all tables in all databases and if some table is corrupted it will automatically fix it that table:

$ mysqlcheck --auto-repair -o --all-databases
Updated: August 28, 2017 — 7:52 pm

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright SolveHostBD © 2016, All Rights Reserved.