两个版本的Mysql 主从复制

MySQL  AB复制

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 database testdb;

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 database testdb;

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)