Improve & Optimizing Performance of the MySQL Restore with mysqldump

Improve & Optimizing Performance of the MySQL Restore with mysqldump

Backup and restoration are common activity are very common activity which DBAs do on regular basis. But important fact is how we can improve and optimize the performance of backup and restore operation. 

In this blog , We are going to discuss about the method with which we can improve the performance of the MySQL Database restoration via mysqldump. Below are the details of two method which can be used to improve performance:

1)  Split the dump file info multiple file for each table. And then import the multiple file with parallel sessions into database. Below script can be used to split mysqldump into multiple files:

#!/bin/bash

if [ $# -lt 1 ] ; then
  echo "USAGE $0 DUMP_FILE [TABLE]"
  exit
fi

if [ $# -ge 2 ] ; then
  csplit -s -ftable $1 "/-- Table structure for table/" "%-- Table structure for table \`$2\`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1"
else
  csplit -s -ftable $1 "/-- Table structure for table/" {*}
fi

[ $? -eq 0 ] || exit

mv table00 head

FILE=`ls -1 table* | tail -n 1`
if [ $# -ge 2 ] ; then
  mv $FILE foot
else
  csplit -b '%d' -s -f$FILE $FILE "/40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*}
  mv ${FILE}1 foot
fi

for FILE in `ls -1 table*`; do
  NAME=`head -n1 $FILE | cut -d$'\x60' -f2`
  cat head $FILE foot > "$NAME.sql"
done

rm head foot table*

2)  Second Method to improve the performance of data load is to disable AUTOCOMMIT ,UNIQUE_CHECKS & FOREIGN_KEY_CHECKS in dump file .

In order to achieve, 

2.1) Put the commands at the top of the dump file

SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

2.2)Put these statements at the end of the dump file

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;

If dump file is of very big size , it will be very difficult to edit the dump. To solve this issue you can use below method:

Create a file with below entry:

vi append_mysqldump.txt

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;
 

Edit the dump file using below commands:

sed '1 s/^/SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;\n/' mysqldb.sql > mysqldb_first.sql 

sed '1 s/^/SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;\n/' mysqldb_first.sql > mysqldb_second.sql  

sed '1 s/^/SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;\n/' mysqldb_second.sql > mysqldb.sql 

Now append File append_mysqldump.txt to mysqldb.sql dump:

cat append_mysqldump.txt >> mysqldb.sql 

Load dump file to MySQL DB:

mysql -u root -p lams_logger < mysqldb.sql

Enter password:

you can learn more about MySql and InnoDB from below books:

MySQL Replication Simplified: Easy step-by-step examples to establish, troubleshoot and monitor replication

Introducing InnoDB Cluster: Learning the MySQL High Availability Stack

MySQL Cookbook: Solutions for Database Developers and Administrators

Effective MySQL Backup and Recovery

=======================================================================

You can also , Visit our Blogs for related topic on Backup & Restore:

1) Restore MySQL Database from mysqlbackup

2) Backup MSSQL Sever Database to a mapped drive 

3)Restore MySQL InnoDB Cluster from mysqlbackup(MySql Enterprise Backup)