This paper introduces two methods of backup and restore MySQL data

Time:2021-4-19

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 ./mysql-relay-log.info

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


/var/lib/mysql/mysql-relay-bin.000002
720
mysql-bin.0000023968

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='192.168.10.201', 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.
Troubleshooting
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

resolvent

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


SELINUX=disabled

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