CentOS7安装mysql5.7

开始

官方文档，点击Replication,点击 Configuring Replication

基础环境

IP	node
192.168.0.9	master
192.168.0.6	slave

基于二进制日志文件位置的复制

点击Setting Up Binary Log File Position Based Replication

设置源(master)配置

进行更改后，重新启动服务器sudo service mysqld restart

# on master my.cnf
[mysqld]
#启用二进制日志记录
log-bin=mysql-bin
# 唯一
server-id=1

为复制创建用户

每个副本都使用 MySQL 用户名和密码连接到源，因此源上必须有一个用户帐户，副本可以用来连接.

# on master
mysql> CREATE USER 'repl'@'192.168.0.6' IDENTIFIED BY 'MyNewPass4!';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.6';
Query OK, 0 rows affected (0.00 sec)

获取复制源的二进制日志坐标

# on master
#阻止写入，退出客户端，锁就会被释放，可不执行
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     611 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

设置副本（slave）

进行更改后，重新启动服务器sudo service mysqld restart

# on slave my.cnf
[mysqld]
#指定唯一的服务器 ID
server-id=2

副本不需要启用二进制日志记录以进行复制。但是，副本上的二进制日志记录意味着副本的二进制日志可用于数据备份和崩溃恢复。启用了二进制日志记录的副本也可以用作更复杂的复制拓扑的一部分。如果要在副本上启用二进制日志记录，请使用配置文件部分中的log-bin选项

为副本设置master配置

要将副本设置为与复制源通信，请使用必要的连接信息配置副本。为此，请在副本上执行以下语句，将选项值替换为与您的系统相关的实际值

#模板
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='source_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

#案例执行 on slave mysqlCli
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.9',MASTER_USER='repl', MASTER_PASSWORD='MyNewPass4!',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=611;

启动复制

# on slave mysqlCli
mysql> START SLAVE;

基于全局事务标识符进行复制

点击 Replication with Global Transaction Identifiers

启用 GTID

# on master my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

gtid_mode=ON
enforce-gtid-consistency=ON

# on slave my.cnf
[mysqld]
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON

重启

# on master
[root@localhost ~]# sudo service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

# on slave
[root@localhost ~]# sudo service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

创建复制用户

# on master
mysql> CREATE USER 'repl1'@'192.168.0.6' IDENTIFIED BY 'MyNewPass4!';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'192.168.0.6';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

将副本配置为使用基于 GTID 的自动定位

# 模板
mysql> CHANGE MASTER TO
     >     MASTER_HOST = host,
     >     MASTER_PORT = port,
     >     MASTER_USER = user,
     >     MASTER_PASSWORD = password,
     >     MASTER_AUTO_POSITION = 1;

# 案例执行
# on slave
mysql>  CHANGE MASTER TO MASTER_HOST = '192.168.0.9', MASTER_PORT = 3306, MASTER_USER = 'repl1',MASTER_PASSWORD = 'MyNewPass4!', MASTER_AUTO_POSITION = 1;
ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL '' first
# 解决
mysql> STOP SLAVE FOR CHANNEL '';
Query OK, 0 rows affected (0.00 sec)

mysql>  CHANGE MASTER TO MASTER_HOST = '192.168.0.9', MASTER_PORT = 3306, MASTER_USER = 'repl1',MASTER_PASSWORD = 'MyNewPass4!', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启动复制

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

检查

# on slave mysqlCli
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.9
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1770
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1479
        Relay_Master_Log_File: mysql-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: 1770
              Relay_Log_Space: 1690
              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: bfc4a270-a8fb-11ec-ae2a-000c29156cda
             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)

Slave_IO_State       #从站的当前状态

Slave_IO_Running： Yes   #读取主程序二进制日志的I/O线程是否正在运行

Slave_SQL_Running： Yes  #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样

Seconds_Behind_Master    #是否为0，0就是已经同步了