14.xtrabackup的数据迁移

1.node09数据备份(192.168.177.138)

[root@node09 ~]# xtrabackup --backup --target-dir=/backup/
[root@node09 backup]# pwd
/backup
[root@node09 backup]# ll
total 18460
-rw-r----- 1 root root      431 Jul 25 02:53 backup-my.cnf
drwxr-x--- 2 root root      146 Jul 25 02:53 hellodb
-rw-r----- 1 root root 18874368 Jul 25 02:53 ibdata1
drwxr-x--- 2 root root     4096 Jul 25 02:53 mysql
drwxr-x--- 2 root root     4096 Jul 25 02:53 performance_schema
drwxr-x--- 2 root root       20 Jul 25 02:53 test
-rw-r----- 1 root root       22 Jul 25 02:53 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Jul 25 02:53 xtrabackup_checkpoints
-rw-r----- 1 root root      464 Jul 25 02:53 xtrabackup_info
-rw-r----- 1 root root     2560 Jul 25 02:53 xtrabackup_logfile

2.数据迁移到node10

[root@node09 backup]# scp -r /backup/ 192.168.177.139:/

3.node10数据还原

[root@node10 ~]# systemctl stop mariadb
[root@node10 ~]# cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
datadir=/data/mysql

[root@node10 ~]# mkdir /data/mysql
[root@node10 ~]# chown mysql.mysql /data/mysql
[root@node10 ~]# xtrabackup --copy-back --target-dir=/backup/
[root@node10 ~]# chown -R mysql.mysql /data/mysql
[root@node10 ~]# ll /data/mysql/
total 40980
drwxr-x--- 2 mysql mysql      146 Jul 18 11:15 hellodb
-rw-r----- 1 mysql mysql 18874368 Jul 18 11:15 ibdata1
-rw-r----- 1 mysql mysql  5242880 Jul 18 11:15 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 Jul 18 11:15 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 18 11:15 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jul 18 11:15 mysql
drwxr-x--- 2 mysql mysql     4096 Jul 18 11:15 performance_schema
drwxr-x--- 2 mysql mysql       20 Jul 18 11:15 test
-rw-r----- 1 mysql mysql       35 Jul 18 11:15 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      464 Jul 18 11:15 xtrabackup_info
-rw-r----- 1 mysql mysql        1 Jul 18 11:15 xtrabackup_master_key_id
[root@node10 ~]# systemctl start mariadb

4.数据验证

[root@node10 ~]# mysql

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
......
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)