本文整理自网络,侵删。
目录
- 一、MySQL复制相关概念
- 二、简单的一主一从架构实现
- 1、新数据库搭建主从架构
- 2、旧数据库新加从服务器
- 三、级联复制架构实现
- 四、主主复制架构
- 五、半同步复制的实现
- 六、加密传输复制的实现
- 七、MySQL复制的相关指令和变量总结
一、MySQL复制相关概念
- 主从复制:主节点将数据同步到多个从节点
- 级联复制:主节点将数据同步到一个从节点,其他的从节点在向从节点复制数据
- 同步复制:将数据从主节点全部同步到从节点时才返回给用户的复制策略叫同步复制
- 异步复制:只要数据写入到主节点就立即返回给用户同步完成
- 读写分离:在前端加一个调度器,负责将改变数据的语句和查询数据的语句分开调度,把写操作调度到主节点,读操作调度到从节点
主节点:
- dump Thread:为每个Slave的I/O Thread启动一个dump线程,用于向其发送binary log events
从节点:
- I/O Thread:向Master请求二进制日志事件,并保存于中继日志中
- SQL Thread:从中继日志中读取日志事件,在本地完成重放
跟复制功能相关的文件:
- master.info:用于保存slave连接至master时的相关信息,例如账号、密码、服务器地址等
- relay-log.info:保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系
复制架构:
- 一主一从
- 一主多从
- 主主复制
- 环状复制
- 级联复制
- 多主一从
常见的架构有主从架构或者级联架构
二、简单的一主一从架构实现
1、新数据库搭建主从架构
1)主服务器配置
~]# 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 | #记录此位置,从服务器从这里开始同步 +--------------------+-----------+
2)从服务器配置
~]# 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; #开始主从复制
3)测试
在主节点上生成一些数据: 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
2、旧数据库新加从服务器
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';'
2)从服务器配置
~]# 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; #启动复制
三、级联复制架构实现
1)主节点
[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 | +-------------------+-----------+
2)从节点
[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';
3)从节点的从节点
[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;
4)从节点的从节点2
[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; 到此已经搭建好了级联复制,接下来测试一下把~
四、主主复制架构
容易产生的问题:数据不一致,因此慎用;考虑要点:自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 开始点
auto_increment_increment=2 增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
1)主1
[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;
2)主2
[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 *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master1-bin.000003', -> MASTER_LOG_POS=245, -> MASTER_CONNECT_RETRY=10; MariaDB [(none)]> START SLAVE;
3)测试
在master1上创建表,增加数据 MariaDB [(none)]> CREATE DATABASE db1; MariaDB [(none)]> use db1 MariaDB [db1]> CREATE TABLE t1(id INT(2) AUTO_INCREMENT PRIMARY KEY,name CHAR(30)); MariaDB [db1]> INSERT t1(name) VALUES ('tom'); MariaDB [db1]> INSERT t1(name) VALUES ('maria'); MariaDB [db1]> SELECT * FROM t1; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 3 | maria | +----+-------+ 在master2上增加数据 MariaDB [db1]> INSERT t1(name) VALUES ('jerry'); MariaDB [db1]> INSERT t1(name) VALUES ('tony'); MariaDB [db1]> SELECT * FROM t1; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 3 | maria | | 4 | jerry | | 6 | tony |
五、半同步复制的实现
默认情况下,MySQL的复制功能是异步的,异步复制可以提供最佳的性能,主库把binlog日志发送给从库即结束,并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时,有可能从服务器没有接收到主服务器发送过来的binlog日志,这就会造成主服务器和从服务器的数据不一致,甚至在恢复时造成数据的丢失;半同步复制的机制是只有当主节点和从节点同步完成,仅有一台同步完成即可,返回写入完成,这样的机制保证了数据的安全性。
相关阅读 >>
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » MySQL系列之十三 MySQL的复制
标签:mysql
相关推荐
评论
管理员已关闭评论功能...