Do you have a copy of your MySQL directory but are unable to take a .sql dump of a specific database in order to restore it? You can start a secondary temporary mysql instance from this data in order to take a .sql dump of that database.

First you would create a directory to move the data in to so you can start the instance.

mkdir -p /home/temp/restore.mysql/mysql/

Then sync over the necessary data to start the instance. In this example I am taking the live data from my server, but if you have a backup server you would use the same format. The mysql dir, the ib* files, the performance_schema dir, and the database dir, which in this case is called disc4lif_wp669.

rsync -avHP /var/lib/mysql/mysql /var/lib/mysql/ib* /var/lib/mysql/performance_schema /var/lib/mysql/disc4lif_wp669 /home/temp/restore.mysql/mysql/

Once you have synced over the required data you will need to update the ownership of the temp directory, and the permissions, in order to start the secondary instance. Make sure you use absolute paths with this chown command so you don’t accidentally change the ownership on the wrong directory

chown -R mysql. /home/temp/restore.mysql/

chmod 751 /home/temp/restore.mysql/mysql/

Now we need to set a variable to this directory for easy configuration of the secondary instance.

dir=/home/temp/restore.mysql/mysql/

Now we can start the secondary instance using this variable. If you have innodb data you should likely start the secondary instance with –innodb-force-recovery=4. If its just myisam data you can remove this part of the configuration line.

mysqld --datadir=$dir --socket=$dir/socket.mysql --pid-file=$dir/mysql.pid --log-error=$dir/mysql.err --skip-grant-tables --skip-networking --innodb-force-recovery=4 --user=mysql &

The & symbol runs the program in the background so you can continue typing in your SSH session. If there are any issues they should be printed to the screen, or you can check the mysql.err file to see what occurred. For instance if you updated MySQL to say MySQL 5.7, and the data was from MySQL 5.6 this will fail, and you can’t start a MySQL 5.7 server from MySQL 5.6 data.

Now that we have the instance started we can create a dumps directory, and take a dump of the database from the secondary instance.

mkdir -p /home/temp/restore.mysql/mysql/dumps

mysqldump --socket=$dir/socket.mysql disc4lif_wp669 > disc4lif_wp669.sql

Confirm the dump completed as expected by checking the last line of the .sql file.

tail -1 disc4lif_wp669.sql
-- Dump completed on 2019-03-22 19:01:02

If there was an issue, and the database is stored in myisam storage engine, you can check the table with the issue using mysqlcheck. This command below would repair the wp_options table granted it was stored in mysiam.

mysqlcheck -r --socket=$dir/socket.mysql disc4lif_wp669 wp_options

Now that you repaired the table that was broken try to take the dump again. Now that you have a current dump from the secondary instance its time to stop the secondary instance.

mysqladmin -S $dir/socket.mysql shutdown

Now I would consider you take a current dump of the live data before restoring from the backup data.

mysqldump disc4lif_wp669 > disc4lif_wp669.sql.`date +\%Y\%m\%d_\%H\%M\%S`

Now you can restore the .sql file you took from the secondary instance.

mysql disc4lif_wp669 < disc4lif_wp669.sql

Here is a video showing this process in action. https://youtu.be/UChE0uPtH3k

That is it. If this helped you consider following me on twitch and youtube. https://twitch.tv/djrunkie