Rate This Document

Findability

Accuracy

Completeness

Readability

Performing Full Backup and Restoration

Create a backup directory on the primary database.

mkdir -p /data/xbackup/full/
ll /data/xbackup/full/
rm -rf /data/xbackup/full/*

Back up the data of the primary database (full backup).
1. Go to the bin directory of XtraBackup.
  - XtraBackup 8.0.13:
```
cd /usr/local/xtrabackup-8.0.13/bin/
```
  - XtraBackup 2.4.20:
```
cd /usr/local/xtrabackup-2.4.20/bin/
```
2. Back up the data of the primary database.
```
./xtrabackup --user=root --password=123456 --port=3306 --host=192.168.220.58 --socket=/data/mysql/run/mysql.sock --parallel=16 --backup --target-dir=/data/xbackup/full/
```
3. Query the log sequence number (LSN) in the xtrabackup_checkpoints file in the full backup directory.
```
cat /data/xbackup/full/xtrabackup_checkpoints
```
4. Query the binlog position in xtrabackup_binlog_info file in the full backup directory.
```
cat /data/xbackup/full/xtrabackup_binlog_info
```

Query backup data on the primary database.

ll /data/xbackup/full/
du -sh /data/xbackup/full/

Copy the backup data from the primary database to the secondary databases.
1. Create a backup directory on the secondary databases.
```
mkdir -p /data/xbackup/full/
ll /data/xbackup/full/
rm -rf /data/xbackup/full/*
```
2. Copy the backup data from the primary database to the secondary databases.
```
scp -r /data/xbackup/full/* root@192.168.220.152:/data/xbackup/full/
```
3. Query backup data on the secondary databases.
```
ll /data/xbackup/full/
du -sh /data/xbackup/full/
```

Stop the MySQL process on the secondary databases.

ps -ef | grep mysql
/usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock
ps -ef | grep mysql

Perform the following operations on the secondary databases to restore the backup data of the primary database to the secondary databases (restoration of full backup):
1. Go to the bin directory of XtraBackup.
  - XtraBackup 8.0.13:
```
cd /usr/local/xtrabackup-8.0.13/bin/
```
  - XtraBackup 2.4.20:
```
cd /usr/local/xtrabackup-2.4.20/bin/
```
2. Prepare the backup data.
```
./xtrabackup --prepare --target-dir=/data/xbackup/full/
ll /data/xbackup/full/
```
  If "completed OK" is displayed, the preparation is complete.
3. Restore backup data on the secondary databases.
```
rsync -avrP /data/xbackup/full/ /data/mysql/data/
```

Start the databases.

ps -ef | grep mysql
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

Log in to the databases to query data.

/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock
show databases;

Parent topic: Adding a Secondary Database