mysql主从复制 基于GTID的主从复制

一．主从复制原理

从库生成两个线程，一个I/O线程，一个SQL线程；

i/o线程去请求主库的binlog，并且得到的binlog日志写到relay log(中继日志)文件中；

主库会生成一个log dump线程，用来给从库的I/O线程传binlog；

SQL线程，会读取中继日志文件，并解析成具体的操作执行，来实现主从的操作一致，而最终数据一致；

二.实验环境

实验主机：rhel6.5

server1:172.25.254.1

server2:172.25.254.2

三．实验具体步骤

1.下载安装包：mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar，解压

[root@server1 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar 
[root@server1 ~]# ls
mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
mysql-community-test-5.7.17-1.el6.x86_64.rpm
[root@server1 ~]# rpm -qa | grep mysql   #查到linux本身有mysql5.1要卸载，但容易卸载一些其他的依赖包，因此这里直接安装mysql5.7会覆盖源软件
mysql-libs-5.1.71-1.el6.x86_64
[root@server1 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm  mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y   #安装软件包
[root@server1 ~]# scp  mysql-community-client-5.7.17-1.el6.x86_64.rpm  mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm server2:
The authenticity of host 'server2 (172.25.254.2)' can't be established.
RSA key fingerprint is 2d:ee:0e:75:83:5d:48:50:16:0b:3b:39:b2:e8:57:11.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server2,172.25.254.2' (RSA) to the list of known hosts.
root@server2's password: 
mysql-community-client-5.7.17-1.el6.x86_64.rp 100%   23MB  22.7MB/s   00:00    
mysql-community-common-5.7.17-1.el6.x86_64.rp 100%  328KB 327.6KB/s   00:00    
mysql-community-libs-5.7.17-1.el6.x86_64.rpm  100% 2125KB   2.1MB/s   00:00    
mysql-community-libs-compat-5.7.17-1.el6.x86_ 100% 1683KB   1.6MB/s   00:00    
mysql-community-server-5.7.17-1.el6.x86_64.rp 100%  151MB  75.8MB/s   00:02    
[root@server1 ~]# scp  mysql-community-client-5.7.17-1.el6.x86_64.rpm  mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm server3: 

2.server2和server3上安装软件

3.编辑server1的/etc/my.cf

[root@server1 ~]# vim /etc/my.cnf

server-id=1
log-bin=mysql-bin

4.开启数据库，安全初始化数据库密码，密码为字符加数字加符号大于8位

[root@server1 ~]# /etc/init.d/mysqld start   #开启数据库，并且初始化
Initializing MySQL database:  
                                                           [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server1 ~]# grep password /var/log/mysqld.log     #过滤初始化的密码
2018-10-20T14:08:25.670692Z 1 [Note] A temporary password is generated for root@localhost: in+ZYgutO5:k   
[root@server1 ~]# mysql -p
Enter password:      #用鼠标选择填入上面初始化的密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;     #查看数据库失败
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.   #你必须重置密码
mysql> ^DBye
[root@server1 ~]# mysql_secure_installation   #安全初始化

Securing the MySQL server deployment.

Enter password for user root:     #输入初始化密码

The existing password for the user account root has expired. Please set a new password.

New password:   #重置新密码必须有大小写，数字，特殊字符

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : #是否修改root密码

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y #移除匿名用户
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y#禁止root用户登录
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y  #移除测试数据库
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.  

All done! 
[root@server1 ~]# mysql -p
Enter password: 
--省--

测试数据库密码，查看master状态

mysql> show databases;   #密码改成功可以查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> show master status;  #查看主的状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      710 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在master上进行授权

[root@server1 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant  replication  slave  on  *.*  to repl@'%'  identified  by 'XueMiao@0325';  #授权用户
Query OK, 0 rows affected, 1 warning (0.63 sec)

5.在server2上用repl用户访问

[root@server2 ~]# mysql -h 172.25.254.1 -u repl
ERROR 1045 (28000): Access denied for user 'repl'@'server2' (using password: NO)
[root@server2 ~]# mysql -h 172.25.254.1 -u repl -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

6.在server2上安装mysql，编辑配置文件，重启mysql，安全初始化

[root@server2 ~]# vim /etc/my.cnf
server-id=2  
[root@server2 ~]# /etc/init.d/mysqld start
Initializing MySQL database:  
                                                           [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server2 ~]# grep password /var/log/mysqld.log 
2018-10-20T14:52:03.497723Z 1 [Note] A temporary password is generated for root@localhost: p_.lptuG>6V/
-省-
[root@server2 ~]# mysql_secure_installation 
-省-

7..server1授权用户

[root@server1 mysql]# mysql -p
--省--
mysql> grant  replication  slave  on  *.*  to repl@'%'  identified  by 'XueMiao@0325';
Query OK, 0 rows affected, 1 warning (0.63 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      993 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

8.在server2中显示slave状态，改变master

[root@server2 ~]# mysql -p
--------
mysql> show slave status\G;   显示slave状态
Empty set (0.00 sec)

ERROR: 
No query specified

mysql> change   master  to master_host='172.25.254.1',master_user='repl',master_password='XueMiao@0325',master_log_file='mysql-bin.000003',master_log_pos=993; #根据show slave status\G;    #master_log_file 和master_log_pos需要在主的服务器上查看必须和master保持一致
Query OK, 0 rows affected, 2 warnings (0.95 sec)

mysql> start slave;
Query OK, 0 rows affected (0.10 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 993
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes   #开启即设置完成
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 993
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 9c5aafdf-d471-11e8-bf5a-5254002ebd88
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

9.server1中创建westos数据库，stu表，server2中查看是否可以查询

mysql> use westos
Database changed
mysql> create table stu (
    -> stuid varchar(10) not null,
    -> password varchar(20)not null);
Query OK, 0 rows affected (1.00 sec)

mysql> insert into stu values('stu1','111');
Query OK, 1 row affected (0.09 sec)

在server1上写入数据server2上可以同步

基于GTID的主从复制

一.实验原理

MySQL-5.6.2开始支持，MySQL-5.6.10后完善，GTID 分成两部分，一部分是服务的UUid,UUID保存在mysql数据目录的auto.cnf文件中,
这是一个非常重要的文件，不能删除，这一部分是不会变的。另外一部分就是事务ID了，随着事务的增加，值依次递增。

工作原理：

1.master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2.slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3.sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4.如果有记录，说明该GTID的事务已经执行，slave会忽略。
5.如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6.在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

GTID的优点：

1.一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次
2.GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
3.减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机

二.实验步骤：

gtid的配置只要在master的/etc/my.cnf中添加这两行

gtid_mode=ON
enforce_gtid_consistency=ON

slave中添加

enforce_gtid_consistency=ON

其他的初始化密码，master上进行用户授权，slave改变msater的ip，用户，只有一个不同，不用指定日志文件

change   master  to master_host='172.25.254.1',master_user='repl',master_password='XueMiao@0325',MASTER_AUTO_POSITION=1; 

基于GTID的并行复制

一.基本原理

由于master是多线程的，而slave是单线程的，所以在复制的时候，会出现读取和执行速度不一致。此时我们通过设置sql线程的个数来实现，进行线程的优化，当master中把数据库的更新写到二进制文件中，并通知slave进行读取；然后slave的io进程把读取到的数据存储在中继日志中；最后slave的多个线程对中继日志进行回放执行命令，从而达到并行复制。

二.实验步骤

在gtid的基础上对slave的配置文件进行修改

vim /etc/my.cnf

server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_recovery=ON

--enforce-gtid-consistency

服务器通过允许执行只能使用GTID安全地记录的语句来强制执行GTID一致性

 --gtid-mode

在MySQL 5.7.6及更高版本中，该 gtid_mode变量是动态的，并允许基于GTID的复制在线配置

slave-parallel-type

DATABASE：默认值，基于库的并行复制方式

LOGICAL_CLOCK：基于组提交的并行复制方式

slave-parallel-workers=0则是单线程设置没有意义

master_info_repository=TABLE    master的信息存放在系统表中，而不是文件中
relay_log_info_repository=TABLE  io从主获取的信息