This paper introduces two methods of backup and restore MySQL data


Using mysqldump for backup and restore
Using mysqldump for backup

mysqladmin stop-slave -uroot -p
mysqldump --all-databases > fulldb.dump
mysqladmin start-slave -uroot -p
tar -czf /tmp/dbdump.tar.gz ./fulldb.dump ./

In addition to backing up the dump of the entire database, we also need to back up relay- File (the above example is MySQL relay)- ), which contains information similar to the following:


The red highlight indicates the execution status of the binary log on the current MySQL master server. This data is critical when restoring from the server.
Restore with mysqldump

mysql -uroot -p < /root/dbdump.db
stop slave;
CHANGE MASTER TO MASTER_HOST='', MASTER_USER='slave_user', MASTER_PASSWORD='[email protected]', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=3968;
start slave;
show slave status\G

In status, if there are two lines below, it means that the slave server is working normally:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Backup and restore using raw data
Backup database files

service mariadb stop
tar --selinux --acls --xattrs -czPf /root/dbbackup.tar.gz /var/lib/mysql/
service mariadb start

Note: the red parameter allows tar to back up SELinux attribute and other ACL attributes at the same time to prevent it from being used after being restored to the target server.
Restore database files

service mariadb stop
tar --selinux --acls --xattrs -xzPf /root/dbbackup.tar.gz -C /
service mariadb start

At the same time, you need to specify these parameters when restoring data files.
Error message

150401 9:58:06 [ERROR] mysqld: File '/var/lib/mysql/mysql-bin.index' not found (Errcode: 13)
150401 9:58:06 [ERROR] Aborting

Check SELinux settings

ll -Z mysql-bin.index
-rw-rw----. mysql mysql unconfined_u :o bject_r:var_lib_t:s0 mysql-bin.index


You can disable SELinux (configuration file / etc / SELinux / config),


It needs to be restarted after modification.

You can also add the following parameters when compressing and decompressing the tar command:

tar --selinux --acls --xattrs