This article introduces two methods for backing up and restoring MySQL DATA: mysql.

This article introduces two methods for backing up and restoring MySQL DATA: mysql.

Back up and restore using mysqldump
Back up data using mysqldump

mysqladmin stop-slave -uroot -pmysqldump --all-databases > fulldb.dumpmysqladmin start-slave -uroot -ptar -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 the relay-log.info file (in the case of a mysql-relay-log.info) that contains information similar to the following:

/var/lib/mysql/mysql-relay-bin.000002720mysql-bin.0000023968

The red highlighted part indicates the execution status of binary logs on the current MySQL master server. This data is crucial when restoring the slave server.
Restore with mysqldump

mysql -uroot -p < /root/dbdump.dbstop slave;CHANGE MASTER TO MASTER_HOST='192.168.10.201', MASTER_USER='slave_user', MASTER_PASSWORD='abc@DEF', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=3968;start slave;show slave status\G

In the status, if there are the following two rows, it indicates that the slave server is working normally:

Slave_IO_Running: YesSlave_SQL_Running: Yes

Back up and restore database files (Raw Data)
Back up database files

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

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

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

You also 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

Solution

SeLinux can be disabled (configuration file/etc/selinux/config ),

SELINUX=disabled

After modification, restart.

You can also add the following parameters during tar command compression and decompression:

tar --selinux --acls --xattrs