兩個(gè)版本的Mysql 主從復(fù)制

MySQL  AB復(fù)制

Mysql AB 復(fù)制又稱主從復(fù)制，實(shí)現(xiàn)的是數(shù)據(jù)同步，要求最好所有的mysql版本相同，如果版本不一致，從服務(wù)器版本要高于主服務(wù)器，而且版本不一致不能做雙向復(fù)制。

AB復(fù)制主要的有點(diǎn)有兩點(diǎn)：

1、解決宕機(jī)帶來的數(shù)據(jù)不一致，因?yàn)?/span>mysql AB 復(fù)制可以實(shí)時(shí)備份數(shù)據(jù)，

2、減輕數(shù)據(jù)庫服務(wù)壓力

但是mysql AB復(fù)制不適用于大數(shù)據(jù)環(huán)境，如果是大數(shù)據(jù)環(huán)境推薦使用集群

Mysql復(fù)制的三個(gè)主要步驟：

①主服務(wù)器更改記錄到二進(jìn)制文件中（二進(jìn)制日志事件）

②從服務(wù)器吧主服務(wù)器的二進(jìn)制文件拷貝到自己的中繼日志中

③從服務(wù)器執(zhí)行中繼日志中的事件，達(dá)到自己和主服務(wù)器的環(huán)境一致

因?yàn)?/span>Mysql5.1和5.7版本有點(diǎn)差距

所以此篇博文以5.1和5.7實(shí)現(xiàn)各自版本的主從復(fù)制

開始搭建：

環(huán)境：redhat6.5

三臺(tái)裝有相同版本MySQL 5.1的虛擬機(jī)：

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狀態(tài)

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數(shù)字一定與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也會(huì)出現(xiàn)以上所添加項(xiàng)目：

mysql> select * from users;

+----------+----------+

| username | password |

+----------+----------+

| user1    | 123      |

| user2    | 456      |

+----------+----------+

2 rows in set (0.00 sec)

####delete from users where username='user1';

####Server1端刪除某項(xiàng)Server2端也會(huì)刪除

#######################################################

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***

###導(dǎo)入serverA之前的MySQL操作日志,因?yàn)?/span>server3之前并沒有那些操作，要同步數(shù)據(jù)那此時(shí)server3的環(huán)境要與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

               ......

出現(xiàn)這個(gè)則證明server3的slave開啟成功，可以同步數(shù)據(jù)

MySQL 5.7

兩臺(tái)裝有相同版本MySQL的虛擬機(jī)：

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

##更改密碼，必須含有大小寫字母、數(shù)字和特殊字符并不少于8個(gè)字符

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不同的一點(diǎn)在于MySQL5.7可以開啟多線程模式，有效解決數(shù)據(jù)同步的延遲問題

開啟多線程模式：

Server2:

vim  /etc/my.cnf

slave-parallel-type=LOGICAL_CLOCK  ##開啟多線程模式

slave-parallel-workers=16

##16為官方推薦數(shù)目，0為原始單線程模式，切記不可設(shè)為1，性能會(huì)比0還差，因?yàn)檫€是單線程但多了一層轉(zhuǎn)發(fā)降低效率

master-info-repository=TABLE   ##優(yōu)化

relay_log_info_repository=TABLE  ##優(yōu)化

/etc/init.d/mysqld  restart

mysql  -p***

mysql> show processlist;  ##可以看到開啟的多個(gè)線程

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)