mysqldump: Got error: 145: Table ‘./wordpress/bot_prli_clicks’ is marked as crashed and should be repaired when using LOCK TABLES
Earlier I was in the process of upgrading MySQL on a CentOS Linux server and ran into an error when I attempted to backup all of the MySQL databases. When issuing the mysqldump command I received an error 145 noting that one of the mysql database tables was marked as crashed and needed to be repaired. Below I have noted the error in more detail as well as how to repair any tables that are marked as crashed.
mysqldump Error 145 Table Marked As Crashed:
[root@dev 02282011]# mysqldump -u root -p -A > mysql-02282011.sql Enter password: mysqldump: Got error: 145: Table './wordpress/bot_prli_clicks' is marked as crashed and should be repaired when using LOCK TABLES [root@dev 02282011]#
The table can be repaired using the myisamchk command as displayed in the below example output. It is highly likely that is one table is showing marked as crashed during mysqldump output that there are multiple tables that are marked as crashed so simply run the below command for each of the tables that have crashed. The output of repairing each table could be different so I have listed the myisamchk repair command for a couple tables to show the possible differences in output. You should run myisamchk from the directory where the table files are stored for the specific database. The below example is a default installation of MySQL on a CentOS Linux server and the database with the crashed tables was named wordpress so the directory the below commands were run from was the /var/lib/mysql/wordpress directory. If you list the contents of the directory you should see multiple files per table so for the bot_prli_clicks table you would see bot_prli_clicks.frm, bot_prli_clicks.MYD, and bot_prli_clicks.MYI files. If the path to your MySQL database is different you could run a search using locate for the database name dot MYD to locate the correct path.
Repair MySQL Crashed Table With myisamchk:
[root@dev wordpress]# myisamchk -r bot_prli_clicks - recovering (with sort) MyISAM-table 'bot_prli_clicks' Data records: 879 - Fixing index 1 Found block that points outside data file at 147388 - Fixing index 2 - Fixing index 3 [root@dev wordpress]#
As you can see multiple items were fixed when running myisamchk against the bot_prli_clicks tables located in the wordpress database. Below is another example of a table in the wordpress database that needed to be repaired.
Repair Another MySQL Crashed Table With myisamchk:
[root@dev wordpress]# myisamchk -r bot_redirection_logs - recovering (with sort) MyISAM-table 'bot_redirection_logs' Data records: 47649 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 - Fixing index 5 - Fixing index 6 - Fixing index 7 [root@dev wordpress]
I ended up having to repair 3 different tables and located each table that needed to be repaired by repairing the table reported in the mysqldump error, running mysqldump again, repairing the next table, etc. Once all of the tables were repaired I was able to backup all of the MySQL databases as shown in the below output.
Backup MySQL Using mysqldump After Crashed Tables Repaired:
[root@dev 02282011]# mysqldump -u root -p -A > mysql-02282011.sql Enter password: [root@dev 02282011]#
After backing up MySQL I proceeded with the MySQL upgrade from MySQL 5.0.X to MySQL 5.1.X.
High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. High Performance MySQL teaches you advanced techniques in depth so you can bring out MySQL's full power. Learn how to design schemas, indexes, queries and advanced MySQL features for maximum performance, and get detailed guidance for tuning your MySQL server, operating system, and hardware to their fullest potential. You'll also learn practical, safe, high-performance ways to scale your applications with replication, load balancing, high availability, and failover. This second edition is completely revised and greatly expanded, with deeper coverage in all areas. Major additions include:
The book also includes chapters on benchmarking, profiling, backups, security, and tools and techniques to help you measure, monitor, and manage your MySQL installations.