扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
MySQL AB复制
栾川ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18980820575（备注：SSL证书合作）期待与您的合作！
Mysql AB 复制又称主从复制，实现的是数据同步，要求最好所有的mysql版本相同，如果版本不一致，从服务器版本要高于主服务器，而且版本不一致不能做双向复制。
AB复制主要的有点有两点：
1、解决宕机带来的数据不一致，因为mysql AB 复制可以实时备份数据，
2、减轻数据库服务压力
但是mysql AB复制不适用于大数据环境，如果是大数据环境推荐使用集群
Mysql复制的三个主要步骤：
①主服务器更改记录到二进制文件中（二进制日志事件）
②从服务器吧主服务器的二进制文件拷贝到自己的中继日志中
③从服务器执行中继日志中的事件，达到自己和主服务器的环境一致
因为Mysql5.1和5.7版本有点差距
所以此篇博文以5.1和5.7实现各自版本的主从复制
开始搭建：
环境：redhat6.5
三台装有相同版本MySQL 5.1的虚拟机：
Server1(master):172.25.141.4
Server2(master&slave):172.25.141.5
Server3(slave):172.25.141.6
Server1(172.25.141.4):
vim /etc/my.cnf （添加以下）
server-id=1
log-bin=mysql-bin
binlog-do-db=testdb
binlog-ignore-db=mysql
/etc/init.d/mysqld start
mysql ##登陆
mysql> create databasetestdb;
mysql> grant replication slave on *.* to redhat@'172.25.141.5' identified by 'test123';
mysql> show master status; ##查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|mysql-bin.000003 | 1019 | testdb | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>quit
mysqlbinlog mysql-bin.000003 ##查看MySQL日志
Server2(172.25.141.5):
mysql -h 172.25.141.4 -u redhat -ptest123 ##测试能否登上
vim /etc/my.cnf（添加以下）
server-id=2
mysql
mysql> create databasetestdb;
mysql> change master to master_host='172.25.141.4',master_user='redhat',master_password='test123',master_log_file='mysql-bin.000003',master_log_pos=1019;
###master_log_file与master status里面的File名一样
###master_log_pos=1019数字一定与master的position一样
mysql> slave start;
mysql> show slave status\G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
#######################################################
测试：
Server1:
mysql> use testdb;
mysql> create table users ( username varchar(25) not null, password varchar(25) not null );
mysql> insert into users values ('user1','123');
mysql> insert into users values ('user2','456');
Server2端database testdb也会出现以上所添加项目：
mysql> select * from users;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
+----------+----------+
2 rows in set (0.00 sec)
####delete from users where username='user1';
####Server1端删除某项Server2端也会删除
#######################################################
server1(master)------>server2(master&slave)------>server3(slave)
Server2(172.25.141.5):
vim /etc/my.cnf
server-id=2
log-bin=mysql-bin
binlog-do-db=testdb
binlog-ignore-db=mysql
log-slave-updates
/etc/init.d/mysqld start
mysql
mysql> grant replication slave on *.* to redhat@'172.25.141.6' identified by 'test123';
mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|mysql-bin.000001 | 106 | testdb | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Server3(172.25.141.6):
mysqlbinlog --start-position=193 mysql-bin.000003 | mysql -uroot -p***
###导入serverA之前的MySQL操作日志,因为server3之前并没有那些操作，要同步数据那此时server3的环境要与server1一致
vim /etc/my.cnf
Server-id=3
/etc/init.d/mysqld start
mysql
mysql> change master to master_host='172.25.141.5',master_user='redhat',master_password='test123',master_log_file='mysql-bin.000001',master_log_pos=106 ;
mysql> slave start;
mysql> show slave status\G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
出现这个则证明server3的slave开启成功，可以同步数据
MySQL 5.7
两台装有相同版本MySQL的虚拟机：
Server1(master)
Server2(slave)
安装包：mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
Server1:
tar xf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
yum install * -y
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog-do-db=testdb
gtid-mode=on
enforce-gtid-consistency=on
/etc/init.d/mysqld start
cat /var/log/mysqld.log | grep temporary ##查看root密码
mysql_secure_installation
##更改密码，必须含有大小写字母、数字和特殊字符并不少于8个字符
mysql -p**** ##登陆
mysql> create database testdb;
mysql> grant replication slave on *.* to redhat@172.25.141.5 identified by '@Ling110';
mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 319 | testdb | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Server2:
tar xf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
yum install * -y
mysql -h 172.25.141.4 -uredhat -p@Ling110 ##测试能否登上
vim /etc/my.cnf
server-id=2
gtid-mode=on
enforce-gtid-consistency=on
/etc/init.d/mysqld start
mysql_secure_installation
mysql -p****
mysql> create database testdb;
mysql> change master to master_host='172.25.141.4',master_user='redhat',master_password='@Ling110',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
测试同MySQL5.1
MySQL5.7和MySQL5.1不同的一点在于MySQL5.7可以开启多线程模式，有效解决数据同步的延迟问题
开启多线程模式：
Server2:
vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK ##开启多线程模式
slave-parallel-workers=16
##16为官方推荐数目，0为原始单线程模式，切记不可设为1，性能会比0还差，因为还是单线程但多了一层转发降低效率
master-info-repository=TABLE ##优化
relay_log_info_repository=TABLE ##优化
/etc/init.d/mysqld restart
mysql -p***
mysql> show processlist; ##可以看到开启的多个线程
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 12 | Slave has read all relay log; waiting for more updates | NULL |
| 2 | system user | | NULL | Connect | 13 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 21 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
19 rows in set (0.00 sec)
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网