MySQL 5.5级联复制配置的案例分析
发布时间：2021-12-24 12:47:24 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍MySQL 5.5级联复制配置的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！ 如下图，A节点是B节点的Master，B节点是C节点的Master A - B - C 在已有的A - B复制环境中，添加级联slave节点C --各节点IP如下
|
这篇文章主要介绍MySQL 5.5级联复制配置的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！ 如下图，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 statusG *************************** 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; 以上是“MySQL 5.5级联复制配置的示例分析”这篇文章的所有内容，感谢各位的阅读 （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐