MySQL 5.5级联复制配置流程

曾经终败给现在 2024-04-17 23:55 27阅读 0赞

如下图，A节点是B节点的Master，B节点是C节点的Master  
 A -> B -> C  
  
 在已有的A -> B复制环境中，添加级联slave节点C  
 \--各节点IP如下  
 A 192.168.78.141  
 B 192.168.78.137  
 C 192.168.78.135  
  
 A -> B复制配置流程，请参考文章  
 http://blog.itpub.net/26506993/viewspace-2091601/  
  
**--在节点B上，增加下面参数后，重启MySQL数据库服务**  
 \--log-slave-updates  
  
 通常，在Mysql复制环境中，slave节点不会将从主节点接收的数据更新写入到它的二进制日志中。这个参数将会使SQL线程在应用日志的时候将相关变更记录到它自己的二进制日志中。  
  
 \[root@localhost 5505\]\# vim /mysql\_data/cnf/my.cnf  
 \[mysqld\]  
 \# Replication  
 log-slave-updates  
  
 \--节点B，重启Mysql服务  
 \[root@localhost bin\]\# /data/bin/mysqladmin -usystem -p'Mysql\#2015' shutdown  
 160502 19:36:01 mysqld\_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended  
 \[1\]+  Done                    /data/bin/mysqld\_safe --defaults-file=/mysql\_data/cnf/my.cnf  
 \[root@localhost bin\]\# /data/bin/mysqld\_safe --defaults-file=/mysql\_data/cnf/my.cnf &  
 \[1\] 57552  
 \[root@localhost bin\]\# 160502 19:36:13 mysqld\_safe Logging to '/mysql\_log/err.log'.  
 160502 19:36:13 mysqld\_safe Starting mysqld daemon with databases from /mysql\_data/5505  
  
 \--查看参数是否生效  
 mysql> show variables like 'log\_slave\_updates';  
 \+-------------------+-------+  
 | Variable\_name     | Value |  
 \+-------------------+-------+  
 | log\_slave\_updates | ON    |  
 \+-------------------+-------+  
 1 row in set (0.00 sec)  
  
**--节点B，停止sql\_thread线程**  
 mysql> stop slave sql\_thread;  
 Query OK, 0 rows affected (0.03 sec)  
  
**--节点B，记录下二进制日志的文件和位置**  
 mysql> show master status;  
 \+------------------+----------+--------------+------------------+  
 | File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
 \+------------------+----------+--------------+------------------+  
 | mysql-bin.000005 |      107 |              |                  |  
 \+------------------+----------+--------------+------------------+  
 1 row in set (0.00 sec)  
  
**--配置B -> C的复制环境**  
  
 \--在C节点，安装好Mysql数据库软件  
  
 \--创建数据文件目录  
 \[root@localhost 70005\]\# mkdir -p /mysql\_data/70005  
  
 \--创建配置文件的目录  
 \[root@localhost 70005\]\# mkdir -p /mysql\_data/cnf/  
  
 \--创建日志文件所在目录  
 \[root@localhost ~\]\# mkdir -p /mysql\_log/binlog  
 \[root@localhost ~\]\# mkdir -p /mysql\_log/innodb  
 \[root@localhost ~\]\# chown -R mysql.mysql /mysql\_log/  
  
 \--在B节点，使用Xtrabackup创建完整备份  
 \[root@localhost percona-xtrabackup-2.4.2-Linux-x86\_64\]\# ./bin/innobackupex  --defaults-file=/mysql\_data/cnf/my.cnf --stream=tar /tmp --user system --password 'Mysql\#2015' | gzip ->   
 /backup/xtra\_fullbackup\_20160503.tar.gz  
  
 \--拷贝备份和配置文件到C节点  
 \[root@localhost backup\]\# scp /backup/xtra\_fullbackup\_20160503.tar.gz  root@192.168.78.135:/backup/20160503  
 \[root@localhost percona-xtrabackup-2.4.2-Linux-x86\_64\]\# scp /mysql\_data/cnf/my.cnf  root@192.168.78.135:/mysql\_data/cnf/  
  
 \--C节点，解压备份到数据文件目录  
 \[root@localhost 5505\]\# tar xivfz /backup/20160503/xtra\_fullbackup\_20160503.tar.gz -C /mysql\_data/70005  
 \[root@localhost ~\]\# chown -R mysql.mysql /mysql\_data/  
  
 \--在C节点上面需要安装Xtraback，可参看文章  
 http://blog.itpub.net/26506993/viewspace-2087734/  
 http://blog.itpub.net/26506993/viewspace-2088737/  
  
**--节点C，使用Xtrabackup准备数据、应用日志，使数据文件达到一致性的状态**  
 \[root@localhost bin\]\# ./innobackupex --defaults-file=/mysql\_data/cnf/my.cnf  --apply-log /mysql\_data/70005  
 .....  
 InnoDB: Log scan progressed past the checkpoint lsn 68409356  
 InnoDB: Doing recovery: scanned up to log sequence number 68409365 (0%)  
 InnoDB: Doing recovery: scanned up to log sequence number 68409365 (0%)  
 InnoDB: Database was not shutdown normally!  
 InnoDB: Starting crash recovery.  
 InnoDB: xtrabackup: Last MySQL binlog file position 823, file name /mysql\_log/binlog/mysql-bin.000002  
 InnoDB: Removed temporary tablespace data file: "ibtmp1"  
 InnoDB: Creating shared tablespace for temporary tables  
 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...  
 InnoDB: File './ibtmp1' size is now 12 MB.  
 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.  
 InnoDB: 32 non-redo rollback segment(s) are active.  
 InnoDB: 5.7.11 started; log sequence number 68409365  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: FTS optimize thread exiting.  
 InnoDB: Starting shutdown...  
 InnoDB: Shutdown completed; log sequence number 68409384  
 160502 20:41:13 completed OK!  
  
 \--节点B，在Master节点B上面创建复制专用账户  
 mysql> grant replication slave on \*.\* to 'repl'@'192.168.78.%' identified by 'Mysql\#2015';  
 Query OK, 0 rows affected (0.04 sec)  
  
**--节点C，配置Slave节点C的配置文件**  
 \[root@localhost bin\]\# vim /mysql\_data/cnf/my.cnf   
 \# Log  
 server-id = 50005  
 log-bin = /mysql\_log/binlog/product-node3-mysql-bin  
 relay-log = /mysql\_log/binlog/product-node3-relay-bin  
 relay-log-index = /mysql\_log/binlog/product-node3-relay-index  
 binlog\_cache\_size = 32M  
 max\_binlog\_cache\_size = 512M  
 max\_binlog\_size = 512M  
 binlog\_format = MIXED  
  
 \--节点C，启动Slave节点的Mysql服务  
 \[root@localhost bin\]\# /software/bin/mysqld\_safe --defaults-file=/mysql\_data/cnf/my.cnf &  
  
**--节点C，配置Slave节点复制环境**  
 \--节点C，在Slave节点执行CHANGE MASTER语句  
 mysql> change master to  
     -> master\_host='192.168.78.137',  
     -> master\_port=5505,  
     -> master\_user='repl',  
     -> master\_password='Mysql\#2015',  
     -> master\_log\_file='mysql-bin.000005',  
     -> master\_log\_pos=107;  
  
**--节点C，启动应用线程**  
 mysql> start slave;  
  
 \--节点C，查看应用状态  
 mysql> show slave status\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                Slave\_IO\_State: Waiting for master to send event  
                   Master\_Host: 192.168.78.137  
                   Master\_User: repl  
                   Master\_Port: 5505  
                 Connect\_Retry: 60  
               Master\_Log\_File: mysql-bin.000005  
           Read\_Master\_Log\_Pos: 107  
                Relay\_Log\_File: product-node3-relay-bin.000002  
                 Relay\_Log\_Pos: 253  
         Relay\_Master\_Log\_File: mysql-bin.000005  
              Slave\_IO\_Running: Yes  
             Slave\_SQL\_Running: Yes  
  
**--启动节点B的SQL线程**  
 mysql> start slave sql\_thread;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2092735/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2092735/