[MySQL] Backup, Restore, Import, and Export

When needed, snapshot backups of MySQL instance are not that useful. So, I tend to backup my DB with mysqldump. The script below dump all of schemas including system tables, users and privileges. It also drops table if exists and add them with its data. This script saved my days so often.

#!/bin/bash
sudo mysqldump -A -Y --master-data=2 --add-drop-table --lock-all-tables -uroot -p > mybackup.sql

One DB/Schema can be exported/backed up as below.

#!/bin/bash
mysqldump --add-drop-table --databases db-name -u root -p > mybackup.sql

Upgrading MySQL

When you try to import old MySQL dump into the new MySQL, you often can get ‘mysql.innodb_index_stats’ doesn’t exist in engine and more. There are two solutions for that.

First of all, you can ignore innodb index and table when dumping. as below.

#!/bin/bash
sudo mysqldump -A -Y --master-data=2 --add-drop-table --lock-all-tables --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats -uroot -p > mybackup.sql

Or you can dump the whole instance and import as below. Please note that “-f” is the magic word here.

 [sudo] mysql -u root -p -f < sample.sql

DB backup senario

Dump files are created from log files. So, it is possible to perform full backup and incremental backup by flushing log files as planned.

The script below performs a full backup every Sunday and it flushes logs and delete master logs to make it sure next backup won’t overlaps with this one. Other days, it just flushes logs only when exporting daily incremental backup.

#!/bin/bash
if [ "`date +%A`" == "Sunday" ]; then

	mysqldump -A -Y --flush-logs --delete-master-logs --master-data=2 --add-drop-table --lock-all-tables -uroot -pPassword > mybackup.sql

else

        today=`date +%A`
        logno=`ls -d /var/log/mysql/mysql-bin.?????? | sed 's/^.*\.//' | sort -g | tail -n 1`
        mysqladmin -uroot -pPassword flush-logs
        mysqlbinlog /var/log/mysql/mysql-bin.$logno > $today.sql

fi

Setting up mint MySQL

After installation, you can log in MySQL. It is the most crucial task to set up root user correctly. Otherwise, you won’t be able to connect with DB remotely at all.

First login

# This will log you in as user root.
sudo mysql 

Then, create root user with password

CREATE USER 'root'@'localhost' IDENTIFIED BY 'YourPassword';
CREATE USER 'root'@'%' IDENTIFIED BY 'YourPassword'; 

MySQL documents say ‘root’@’%’ will create ‘root’@’localhost’ as well. However, it is safer to run both of them. You can give different password to make your credentials more secure.

Then, Grant privileges

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES; 

For MySQL Server initial installation, you can google them. Just be cautious not to follow wrong instructions. I found this one is quite updated and useful.