主从介绍

什么是mysql主从

所谓mysql主从就是建立两个完全一样的数据库，其中一个为主要使用的数据库，另一个为次要的数据库，一般在企业中，存放比较重要的数据的数据库服务器需要配置主从，这样可以防止因数据库服务器宕机导致数据丢失，还能保证业务量太多、数据太多和访问人数太多时服务的质量（服务器响应速度），还能提供故障切换、读写分离、和备份等等功能。

主从形式

在这里插入图片描述

  • 一主一从
  • 主主复制：当作备份使用，当主服务器出现故障时，另一个主服务器会自动顶上。
  • 一主多从：用来实现读写分离，当写操作较少时，读操作较多时使用，主服务器用来实现写操作，从服务器用来实现读操作。
  • 多主一从：用来实现读写分离，当写操作较多时，读操作较少时使用，主服务器用来实现写操作，从服务器用来实现读操作。
  • 联级复制

传统主从和gtid主从的区别

传统主从
传统主从复制主要是基于二进制日志文件位置的复制，因此主必须启动二进制日志记录并建立唯一的服务器ID，复制组中的每个服务器都必须配置唯一的服务器ID。如果您省略server-id（或者明确地将其设置为其默认值0），则主设备将拒绝来自从设备的任何连接。
gtid主从
MySQL 5.6 的新特性之一，全局事务标识符（GTID）是创建的唯一标识符，并与在源（主）服务器上提交的每个事务相关联。此标识符不但是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系 。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一，在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

主从复制的原理

在这里插入图片描述
主从复制的工作流程：主库将用户所有的写操作（增删改，查除外）记录到binlog日志当中并且生成一个log dump线程，从库生成I/O和SQL线程，从库的I/O线程向主库的log dump线程以I/O流的形式发送请求，主库的log dump线程收到从库I/O线程的请求后将binlog日志发送给从库，从库I/O线程收到binlog日志后将其写道relay log（中继日志）文件中，再由从库的SQL线程将relay log文件中的日志解析成sql脚本，最后执行生成的sql脚本文件，来实现主从的操作一致，达到最终数据一致的目的。

主从复制配置

主从复制配置步骤：

  • 确保所有主从主机的防火墙已经关闭
  • 确保主数据库和从数据库里的数据一样
  • 再主数据库里创建一个同步账号并且授权给从数据库使用
  • 配置主数据库配置文件
  • 配置从数据库配置文件

传统主从的配置

实验环境：

数据库角色 IP 系统版本
主数据库 192.168.247.137 CentOS8
从数据库 192.168.247.211 CentOS8
从数据库 192.168.247.213 CentOS8

关闭所有主机的防火墙

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0

确保数据一致性
主：

[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

从数据库1：

[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

从数据库2：

[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

如果数据库中的数据不一样，则需要全备主服务器中数据，然后恢复到从服务器中，备份时使用以下命令锁表，使数据库不能插入数据，在从数据库中使用备份与主数据库数据一致后关闭锁表。

mysql> FLUSH TABLES WITH READ LOCK;

创建账号并授权给从数据库

mysql> create user 'hanao'@'192.168.247.211' identified by 'hanao.';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'hanao'@'192.168.247.211';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'hanao'@'192.168.247.213' identified by 'hanao.';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'hanao'@'192.168.247.213';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

配置主数据库

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
server-id=1			//唯一id，主数据库必须比从数据库小
log-bin=mysql_bin		//logbin文件
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//mysql_bin.000001为日志文件名，154为数据库pos

配置从数据库
配置第一个从数据库

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
server-id=2					//唯一id，主数据库必须比从数据库小
relay-log=slave_relay_bin//中继日志文件，用来存放I/O线程获取的日志信息
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> change master to
       master_host='192.168.247.137',			//主数据库的ip
       master_user='hanao',							//主数据库授权的远程登录账号
       master_password='hanao.',					//主数据库授权的远程登录密码
       master_log_file='mysql_bin.000001',	//主数据库的日志文件
       master_log_pos=154;							//主数据库日志文件中的pos值
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;										//启动从数据库配置
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.247.137
                  Master_User: hanao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave_relay_bin.000001
                Relay_Log_Pos: 320
        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: 154
              Relay_Log_Space: 527
              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: 3c716ba6-ace1-11eb-bd0d-000c294bb269
             Master_Info_File: /opt/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)

配置第二个从数据库

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
server-id=3
relay-log=slave_relay_bin
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> change master to
    -> master_host='192.168.247.137',
    -> master_user='hanao',
    -> master_password='hanao.',
    -> master_log_file='mysql_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)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.247.137
                  Master_User: hanao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave_relay_bin.000001
                Relay_Log_Pos: 320
        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: 154
              Relay_Log_Space: 527
              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: 3c716ba6-ace1-11eb-bd0d-000c294bb269
             Master_Info_File: /opt/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)

测试
主数据库中插入数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database hanao;
Query OK, 1 row affected (0.01 sec)

mysql> use hanao;
Database changed
mysql> create table student(id int not null,name varchar(100));
Query OK, 0 rows affected (0.03 sec)

mysql> insert student(id,name) values(1,'hanao'),(2,'ha');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

第一个从数据库中查看数据

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

mysql> use hanao;
Database changed
mysql> select * from student;
+----+-------+
| id | name  |
+----+-------+
|  1 | hanao |
|  2 | ha    |
+----+-------+
2 rows in set (0.00 sec)

第二个从数据库中查看数据

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

mysql> select * from hanao.student;
+----+-------+
| id | name  |
+----+-------+
|  1 | hanao |
|  2 | ha    |
+----+-------+
2 rows in set (0.00 sec)

gtid主从配置

实验环境：

数据库角色 IP 系统版本
主数据库 192.168.247.137 CentOS8
从数据库 192.168.247.211 CentOS8
从数据库 192.168.247.214 CentOS8

关闭所有主机的防火墙

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0

确保数据一致性
主：

[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

从数据库1：

[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

从数据库2：

[root@localhost ~]# mysql -uroot -phanao.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

如果数据库中的数据不一样，则需要全备主服务器中数据，然后恢复到从服务器中，备份时使用以下命令锁表，使数据库不能插入数据，在从数据库中使用备份与主数据库数据一致后关闭锁表。

mysql> FLUSH TABLES WITH READ LOCK;

创建远程连接账号并授权给从数据库

mysql> create user 'hanao'@'192.168.247.211' identified by 'hanao.';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'hanao'@'192.168.247.211';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'hanao'@'192.168.247.214' identified by 'hanao.';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'hanao'@'192.168.247.214';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置主库

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
server_id=1
gtid_mode=on 					//开启gtid模块
enforce_gtid_consistency=on	//强制gtid一致性，开启后对于特定create table不被支持
log_bin=master-binlog		//日志文件
log-slave-updates=1			//更新日志文件
binlog_format=row 				//binlog日志格式为一条一条的
skip_slave_start=1				
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

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

配置从库
第一个从数据库的配置

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
gtid_mode=on
enforce_gtid_consistency=on
server_id=2
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

CHANGE MASTER TO
     MASTER_HOST='192.168.247.137',
     MASTER_USER='hanao',
     MASTER_PASSWORD='hanao.',
     MASTER_PORT=3306,
     MASTER_AUTO_POSITION = 1;
     
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.247.137
                  Master_User: hanao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 375
        Relay_Master_Log_File: master-binlog.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: 586
              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: 3c716ba6-ace1-11eb-bd0d-000c294bb269
             Master_Info_File: /opt/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: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

第二个从数据库的配置

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
gtid_mode=on
enforce_gtid_consistency=on
server_id=3
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

CHANGE MASTER TO
     MASTER_HOST='192.168.247.137',
     MASTER_USER='hanao',
     MASTER_PASSWORD='hanao.',
     MASTER_PORT=3306,
     MASTER_AUTO_POSITION = 1;

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

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.247.137
                  Master_User: hanao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 375
        Relay_Master_Log_File: master-binlog.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: 586
              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: 3c716ba6-ace1-11eb-bd0d-000c294bb269
             Master_Info_File: /opt/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: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

测试
主数据库新建插入操作：

mysql> create database ha;
Query OK, 1 row affected (0.01 sec)

mysql> use ha;
Database changed
mysql> create table student(id int not null,name varchar(100));
Query OK, 0 rows affected (0.03 sec)

mysql> insert student(id,name) values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from ha.student;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.01 sec)

第一个从数据库查看结果

mysql> select * from ha.student;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)

第二个从数据库查看结果

mysql> select * from ha.student;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)
# centos # mysql
Logo
魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

cover

【计算机视觉】Pixel逐像素分类&Mask掩码分类理解摘要

avatar 魔乐社区

计算机视觉（opencv）实战三十二——CascadeClassifier 人脸微笑检测（摄像头）

本文从原理到实现，详细介绍了基于 OpenCV Haar 分类器的人脸与微笑检测：讲解了 Haar 特征和级联检测原理。对代码逐行拆解并解释参数含义。画出完整流程图，帮助理解执行过程。给出了常见问题和优化建议，甚至扩展到深度学习方法。这种方法简单、轻量、实时性好，非常适合入门和小型应用项目。但如果需要更高准确率和更强鲁棒性，建议使用深度学习检测器替代 Haar 分类器。

avatar 魔乐社区
cover

小杰机器学习（seven）——贝叶斯分类

avatar 魔乐社区