MySQL系列之十三 MySQL的复制_Mysql_

 ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master1 server_id=1 ~]# systemctl restart mariadb ~]# mysql MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass'; #授权同步账户 MariaDB [(none)]> SHOW MASTER LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | master1-bin.000001 | 26756 | | master1-bin.000002 | 921736 | | master1-bin.000003 | 401 | #记录此位置，从服务器从这里开始同步 +--------------------+-----------+

 ~]# vim /etc/my.cnf [mysqld] server_id=2 #服务器ID唯一 relay_log=relay-log relay_log_index=relay-log.index read_only=ON ~]# systemctl restart mariadb ~]# mysql MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', #指定主节点IP -> MASTER_USER='testuser', #同步用户的用户名 -> MASTER_PASSWORD='testpass', #密码 -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master1-bin.000003', #以上记录的文件 -> MASTER_LOG_POS=401, #位置 -> MASTER_CONNECT_RETRY=10; #重试时间10秒 MariaDB [(none)]> START SLAVE; #开始主从复制

 在主节点上生成一些数据： MariaDB [(none)]> CREATE DATABASE testdb; MariaDB [(none)]> use testdb MariaDB [testdb]> create table testlog (id int auto_increment primary key,name char(30),age int default 20); MariaDB [testdb]> delimiter $$ MariaDB [testdb]> create procedure pro_testlog() -> begin -> declare i int; -> set i = 1; -> while i < 100000 -> do insert into testlog(name,age) values (concat('testuser',i),i); -> set i = i +1; -> end while; -> end$$ MariaDB [testdb]> delimiter ; MariaDB [testdb]> START TRANSACTION; MariaDB [testdb]> CALL pro_testlog; MariaDB [testdb]> COMMIT;

 在从节点上查看同步情况： MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog; +----------+ | COUNT(*) | +----------+ | 99999 | #同步成功 +----------+ MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row **************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.7 Master_User: testuser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master1-bin.000003 Read_Master_Log_Pos: 10389814 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 10389944 Relay_Master_Log_File: master1-bin.000003 Slave_IO_Running: Yes #IO线程已启动 Slave_SQL_Running: Yes #SQL线程已启动 Seconds_Behind_Master: 0 #主从复制的时间差 Master_Server_Id: 1

 ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master1 server_id=1 ~]# systemctl restart mariadb ~]# mysqldump -A -F --single-transaction --master-data=1 > full.sql ~]# scp full.sql root@192.168.0.8:/root/ ~]# mysql -e 'GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass';'

 ~]# vim /etc/my.cnf [mysqld] server_id=2 relay_log=relay-log relay_log_index=relay-log.index read_only=ON ~]# systemctl restart mariadb ~]# vim full.sql #在备份的SQL文件中加入以下信息 CHANGE MASTER TO MASTER_HOST='192.168.0.7', MASTER_USER='testuser', MASTER_PASSWORD='testpass', MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000005', MASTER_LOG_POS=245, MASTER_CONNECT_RETRY=10; ~]# mysql < full.sql #导入SQL的同时配置已经完成 MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog; +----------+ | COUNT(*) | +----------+ | 99999 | +----------+ MariaDB [(none)]> START SLAVE; #启动复制

 [root@master ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master server_id=1 [root@master ~]# systemctl restart mariadb MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 26753 | | master-bin.000002 | 921736 | | master-bin.000003 | 401 | +-------------------+-----------+

 [root@slave1 ~]# vim /etc/my.cnf [mysqld] log_bin #注意，级联架构中中继从节点一定得开二进制日志功能 binlog_format=ROW read_only=ON server_id=2 log_slave_updates #这项为关键，作用是将从服务的数据改变记录到二进制日志文件中 relay_log=relay-log relay_log_index=relay-log.index [root@slave1 ~]# systemctl start mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000003', -> MASTER_LOG_POS=401, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE; MariaDB [(none)]> SHOW MASTER LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+ MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';

 [root@slave2 ~]# vim /etc/my.cnf [mysqld] read_only=ON server_id=3 relay_log=relay-log relay_log_index=relay-log.index [root@slave2 ~]# systemctl start mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE;

 [root@slave3 ~]# vim /etc/my.cnf [mysqld] read_only=ON server_id=4 relay_log=relay-log relay_log_index=relay-log.index [root@slave3 ~]# systemctl start mariadb MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE; 到此已经搭建好了级联复制，接下来测试一下把~

 [mysqld] log_bin binlog_format=ROW log-basename=master1 server_id=1 relay_log=relay-log relay_log_index=relay-log.index auto_increment_offset=1 #自增长字段从1开始 auto_increment_increment=2 #每次增长2，也就是说master1节点写入的数据的id字段全部是奇数 [root@master ~]# systemctl start mariadb MariaDB [(none)]> SHOW MASTER LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | master1-bin.000001 | 27033 | | master1-bin.000002 | 942126 | | master1-bin.000003 | 245 | +--------------------+-----------+ MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master2-bin.000003', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE;

 [mysqld] log_bin binlog_format=ROW log-basename=master2 server_id=2 relay_log=relay-log relay_log_index=relay-log.index auto_increment_offset=2 #自增长字段从1开始 auto_increment_increment=2 #每次增长2，也就是说master1节点写入的数据的id字段全部是偶数 [root@master2 ~]# systemctl start mariadb MariaDB [(none)]> SHOW MASTER LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | master2-bin.000001 | 27036 | | master2-bin.000002 | 942126 | | master2-bin.000003 | 245 | +--------------------+-----------+ MariaDB [(none)]> GRANT REPLICATION SLAVE ON
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：MySQL系列之六 用户与授权_Mysql_
                                                                
下一篇：MySQL系列之二 多实例配置_Mysql_