Mysql 主从部署（二）

Mysql主从原理

MySQL主从复制的基本交互过程，如下：

1、slave端的IO线程连接上master端，并请求从指定binlog日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。

2、master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。

3、slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relaylog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info（该文件存在slave端）文件中，以便在下一次读取的时候能够清楚的告诉master“我需要从哪个binlog文件的哪个pos节点位置开始，请把此节点以后的日志内容发给我”。

4、slave端的SQL线程在检测到relaylog文件中新增内容后，会马上解析该log文件中的内容。然后还原成在master端真实执行的那些SQL语句，并在自身按顺丰依次执行这些SQL语句。这样，实际上就是在master端和slave端执行了同样的SQL语句，所以master端和slave端的数据是完全一样的。

在master与slave之间实现整个复制过程

主要由三个线程来完成：

其中两个线程(SQL线程和IO线程)在slave端，另外一个线程(IO线程)在master端

可通过show slave status查看线程状态

简化该交互过程：

1、master在执行sql之后，记录二进制log文件（bin-log）。

2、slave连接master，并从master获取binlog，存于本地relay-log中，然后从上次记住的位置起执行SQL语句，一旦遇到错误则停止同步。

要点：

要实现MySQL的Replication，首先必须打开master端的binlog (mysql-bin.xxxxxx)日志功能，否则无法实现mysql的主从复制。

因为mysql的整个主从复制过程实际上就是：slave端从master端获取binlog日志，然后再在自己身上完全顺序的执行该日志中所记录的各种SQL操作。

master节点配置

修改my.cnf配置文件

注：将该配置追加到原配置后面即可

[work@wzx-mysql-master conf]$ vim /home/work/mysql/conf/my.cnf
# server-id需要保证唯一性 不可与其他从服务器相同 如果为0会拒绝所有从服务器连接
server-id=10000
log-bin=master-bin
# binlog的索引文件 可以不配置 默认会根据上面配置增加index后缀
log-bin-index=master-bin.index
binlog_format=mixed

重启mysql加载新配置

[work@wzx-mysql-master conf]$ mysql.server restart
Shutting down MySQL..[  确定  ]
Starting MySQL.[  确定  ]

确认master二进制文件和pos点信息

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

开放远程登录权限，否则无法完成设置

注：可以只允许slave节点ip可以访问，本次设置为开放所有ip

mysql> use mysql; 
mysql> update user set host='%' where user='root';
mysql> flush privileges;

Slave节点配置

修改my.cnf配置文件

注：将该配置追加到配置后面即可

[work@wzx-mysql-slave conf]$ vim /home/work/mysql/conf/my.cnf
# server-id需要保证唯一性 不可与其他从服务器相同 如果为0会拒绝所有从服务器连接
server-id=10001
log-bin=master-bin
# binlog的索引文件 可以不配置 默认会根据上面配置增加index后缀
log-bin-index=master-bin.index
binlog_format=mixed
read-only

重启mysql服务加载新配置

[work@wzx-mysql-slave conf]$ mysql.server restart
Shutting down MySQL..[  确定  ]
Starting MySQL.[  确定  ]

slave mysql配置主从

mysql> change master to master_host='172.17.1.16',master_port=36001,master_user='root',master_password='QWE@work$2023',master_log_file='master-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

-> master_host='172.17.1.16',  //主服务器的地址

-> master_port=36001, //主服务器端口

-> master_user='root',    //master数据库账号

-> master_password='xxxxx', //master数据库密码

-> master_log_file='master-bin.000001', //主服务器二进制文件

-> master_log_pos=154; //主服务器position行数

检查主从状态

注：Slave_IO_Running: Yes Slave_SQL_Running: Yes两个线程都为yes即主从状态建立正常

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.1.16
                  Master_User: root
                  Master_Port: 36001
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: wzx-mysql-slave-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             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: 154
              Relay_Log_Space: 538
              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: 10000
                  Master_UUID: 464a77b5-97b9-11ed-8fa7-fa2020250b77
             Master_Info_File: /home/work/mysql/data/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)

验证主从效果

master创建wzx数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database wzx;
Query OK, 1 row affected (0.00 sec)

slave上查看数据库是否相同

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wzx                |
+--------------------+
5 rows in set (0.00 sec)