mysqldump和xtrabackup两种方式进行非空库复制搭建

最新推荐文章于 2024-09-19 19:26:49 发布

wukong_666

最新推荐文章于 2024-09-19 19:26:49 发布

阅读量847

点赞数

分类专栏： mysql 主从复制

本文链接：https://blog.csdn.net/wukong_666/article/details/72477678

版权

mysql 主从复制专栏收录该内容

10 篇文章 1 订阅

订阅专栏

mysqldump和xtrabackup两种方式进行非空库复制搭建
1、mysqldump非空库复制搭建
2、xtrabackup非空库复制搭建

mysqldump和xtrabackup两种方式进行非空库复制搭建

1、mysqldump非空库复制搭建

背景
- 主库：IP：192.168.1.21；版本：5.7.18
- 备库：IP：192.168.1.128；版本：5.6.36
从库上先停掉复制

 
 root@localhost : (none) 01:57:45> stop slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost : (none) 01:57:54> reset slave;
Query OK, 0 rows affected (0.10 sec)
root@localhost : (none) 01:57:58> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 143
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
root@localhost : (none) 01:58:00> reset slave all;
Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 01:58:17> show slave status;
Empty set (0.00 sec)

在主库上创建新的测试数据

 
 mysql> create database sbtest;
Query OK, 1 row affected (0.02 sec)
mysql> use sbtest;
Database changed
mysql> create table xiyouji(
    -> id int,
    -> role varchar(11) );
Query OK, 0 rows affected (0.14 sec)
mysql> insert into xiyouji values (1,'songwukong'),(2,'tangsanzang'),(3,'zhuwuneng'),(4,'shawujing');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from xiyouji;
+------+-------------+
| id   | role        |
+------+-------------+
|    1 | songwukong  |
|    2 | tangsanzang |
|    3 | zhuwuneng   |
|    4 | shawujing   |
+------+-------------+
4 rows in set (0.00 sec)

mysqldump把数据导出并导入到从库中

 
 [root@host-192-168-1-21 mysqldump]# mysqldump -uroot -p -S /usr/local/mysql/mysql.sock --single-transaction --master-data=2 --flush-logs --all-databases > ./master_slave.sql
[root@host-192-168-1-21 mysqldump]# mysql -uroot -h192.168.1.128 < ./master_slave.sql 
ERROR 1726 (HY000) at line 232: Storage engine 'InnoDB' does not support system tables. [mysql.help_category]
#原因：主库是5.7版本，从库是5.6版本。mysql.help_category这个系统表在5.7中有，但是5.6中没有。当然不止这一个表，sys整个库都是新增的。

不备份系统库表，只备份业务表

 
 [root@host-192-168-1-21 mysqldump]# mysqldump -uroot -p -S /usr/local/mysql/mysql.sock --single-transaction --master-data=2 --flush-logs sbtest > ./master_slave.sql
[root@host-192-168-1-21 mysqldump]# mysql -uroot -h192.168.1.128 < ./master_slave.sql 
ERROR 1046 (3D000) at line 28: No database selected
#原因；这个报错原因就是之前在练习mysqldump备份时候遇到过的，原因如下：
mysqldump -uroot -S /usr/local/mysql5711/mysql.sock -p --single-transaction --master-data=2 --flush-logs --databases sbtest > ./danku.sql
mysqldump -uroot -S /usr/local/mysql5711/mysql.sock -p --single-transaction --master-data=2 --flush-logs sbtest > ./danku2.sql
#区别：前者加了--databases参数，生成的sql文件中含有CREATE DATABASE语句；后者不加这个参数，直接跟单库名，生成的sql文件中没有CREATE DATABASE语句。

重新导出sql文件

 
 [root@host-192-168-1-21 mysqldump]# mysqldump -uroot -p -S /usr/local/mysql/mysql.sock --single-transaction --master-data=2 --flush-logs --databases sbtest > ./master_slave.sql
[root@host-192-168-1-21 mysqldump]# mysql -uroot -h192.168.1.128 < ./master_slave.sql
[root@host-192-168-1-21 mysqldump]# cat master_slave.sql |grep -i 'change master'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=154;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000025 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在从库上查看数据是否已经导入成功

 
 root@localhost : mysql 02:28:24> use sbtest;
Database changed
root@localhost : sbtest 02:28:39> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| xiyouji          |
+------------------+
1 row in set (0.00 sec)
root@localhost : sbtest 02:28:42> select * from xiyouji;
+------+-------------+
| id   | role        |
+------+-------------+
|    1 | songwukong  |
|    2 | tangsanzang |
|    3 | zhuwuneng   |
|    4 | shawujing   |
+------+-------------+
4 rows in set (0.00 sec)

设置主从复制

 
 root@localhost : mysql 02:22:53> show slave status;
Empty set (0.00 sec)
root@localhost : mysql 02:26:54> CHANGE MASTER TO master_host='192.168.1.21',master_user='repl',master_password='repl',MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.16 sec)
root@localhost : mysql 02:28:15> start slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost : sbtest 02:30:28> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000025
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000025
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 459
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
#主从复制同步成功

验证

 
 #主库上新建表，并插入数据
mysql> use sbtest;
Database changed
mysql> create table sanguoyanyi(
    -> id int,
    -> people varchar(13) );
Query OK, 0 rows affected (0.09 sec)
mysql> insert into sanguoyanyi values (1,'caocao'),(2,'guojia');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from sanguoyanyi;
+------+--------+
| id   | people |
+------+--------+
|    1 | caocao |
|    2 | guojia |
+------+--------+
2 rows in set (0.00 sec)

 
 #从库上查看数据是否同步成功：
root@localhost : sbtest 02:30:32> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sanguoyanyi      |
| xiyouji          |
+------------------+
2 rows in set (0.00 sec)
root@localhost : sbtest 02:34:24> select * from sanguoyanyi;
+------+--------+
| id   | people |
+------+--------+
|    1 | caocao |
|    2 | guojia |
+------+--------+
2 rows in set (0.00 sec)

2、xtrabackup非空库复制搭建

背景
- 主库：IP：192.168.1.21；版本：5.7.18
- 备库：IP：192.168.1.128；版本：5.7.18
  - 在128上新搭5.7.18版本的mysql。版本不同的mysql server用全库物理备份会出现各种问题。
清空192.168.1.128从库

 
 root@localhost : sbtest 02:39:32> drop database sbtest;
Query OK, 2 rows affected, 1 warning (0.06 sec)

在从库上安装xtrabackup

 
 [root@host-192-168-1-128 program]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm
[root@host-192-168-1-128 program]# rpm -ivh percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm 
warning: percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
    libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.7-1.el6.x86_64
    perl(DBD::mysql) is needed by percona-xtrabackup-24-2.4.7-1.el6.x86_64
    rsync is needed by percona-xtrabackup-24-2.4.7-1.el6.x86_64
[root@host-192-168-1-128 program]# yum install rsync
[root@host-192-168-1-128 program]# yum install perl-DBD-MySQL 
[root@host-192-168-1-128 program]# wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
[root@host-192-168-1-128 program]# ls
libev-4.15-1.el6.rf.x86_64.rpm  percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm
[root@host-192-168-1-128 program]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 
warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing...                ########################################### [100%]
   1:libev                  ########################################### [100%]
[root@host-192-168-1-128 program]# rpm -ivh percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm 
warning: percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                ########################################### [100%]
   1:percona-xtrabackup-24  ########################################### [100%]

主库上的业务库信息

 
 mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sbtest             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use sbtest
Database changed
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sanguoyanyi      |
| xiyouji          |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from sanguoyanyi;
+------+--------+
| id   | people |
+------+--------+
|    1 | caocao |
|    2 | guojia |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from xiyouji;
+------+-------------+
| id   | role        |
+------+-------------+
|    1 | songwukong  |
|    2 | tangsanzang |
|    3 | zhuwuneng   |
|    4 | shawujing   |
+------+-------------+
4 rows in set (0.00 sec)

在主库上用innobackup备份数据并传输到从库服务器。注意：两台机器之间需要ssh免密钥登录

 
 innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --password=123 --no-timestamp --stream='tar' /home/backup/xtrabackup/ |ssh root@192.168.1.128 "cat - > /home/backup/backup_`date +%y%m%d`.tar"

将主库的配置文件拷贝到从库

 
 scp /usr/local/mysql/my.cnf root@192.168.1.128:/usr/local/mysql/
#修改server-id

停掉从库的mysql

 
 [root@host-192-168-1-128 backup]# service mysql stop
Shutting down MySQL.. SUCCESS!

解压数据包，应用日志并做数据恢复

 
 [root@host-192-168-1-128 backup]# ls
backup_170517.tar
[root@host-192-168-1-128 backup]# pwd
/home/backup
[root@host-192-168-1-128 backup]# tar xvf backup_170517.tar 
[root@host-192-168-1-128 backup]# ls
backup_170517.tar  ib_buffer_pool  IBdata2  performance_schema  sys                     xtrabackup_checkpoints  xtrabackup_logfile
backup-my.cnf      IBdata1         mysql    sbtest              xtrabackup_binlog_info  xtrabackup_info
[root@host-192-168-1-128 backup]# innobackupex --apply-log --read-only /home/backup/
[root@host-192-168-1-128 data]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /home/backup/
170517 04:47:15 completed OK!

修改datadir属组

 
 [root@host-192-168-1-128 home]# chown -R mysql:mysql /home/data/

启动mysql

 
 mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --user=mysql
ps -ef|grep mysql
确定是否运行

查看数据，和主库一致
设置主从复制

 
 [root@host-192-168-1-128 backup]# cat xtrabackup_binlog_info 
mysql-bin.000025    639
root@localhost : mysql 09:22:53> CHANGE MASTER TO master_host='192.168.1.21',master_user='repl',master_password='repl',MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=639;
root@localhost : sbtest 09:25:28> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000025
          Read_Master_Log_Pos: 639
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000025
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 639
              Relay_Log_Space: 459
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
#同步成功