rhel 7.3搭建mysql的主从复制—非单机场景

文档课题：rhel 7.3搭建mysql的主从复制—非单机场景.
数据库：mysql 8.0.27
系统：rhel 7.3
安装包：mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
环境介绍：

1、理论知识
master将操作语句记录到binlog日志，然后授予slave远程连接权限(master需开启binlog，为数据安全考虑，slave也开启binlog）.
slave开启IO线程和SQL线程，其中IO线程负责读取master的binlog到中继日志；SQL线程负责从中继日志读出binlog内容，并更新到slave的数据库里，如此保证slave和master数据一致性.
Mysql复制至少需要两个Mysql服务，当然Mysql服务可以分布在不同的服务器上，也可以分布在一台服务器上，此次测试Mysql在不同的服务器上.master和slave两节点间时间需同步.
2、开始搭建
2.1、安装mysql
准备两台安装好mysql 8.0.27的服务器.
2.2、主库配置
2.2.1、修改配置文件
--修改主节点配置文件
[root@leo-mysql-master etc]# vi /etc/my.cnf
添加如下内容：
log_bin=master_bin   --开启二进制
server_id=1          --设置当前节点全局唯一ID，后续在创建连接时需要该参数.
2.2.2、重启mysql服务
[root@leo-mysql-master etc]# service mysql stop
Shutting down MySQL. SUCCESS! 
[root@leo-mysql-master etc]# service mysql start
Starting MySQL. SUCCESS! 
[root@leo-mysql-master etc]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 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 \G
*************************** 1. row ***************************
             File: master_bin.000001
         Position: 957
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)
2.2.3、创建复制用户
创建复制用户账号，192.168.133.%表示允许IP前边是192.168.133的repluser用户连接master主机.
mysql> create user repluser@'192.168.133.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
授权用户
mysql> grant replication slave, replication client on *.* to repluser@'192.168.133.%';
Query OK, 0 rows affected (0.00 sec)
2.3、从库配置
2.3.1、修改配置文件
[root@leo-mysql-slave etc]# cp /etc/my.cnf /etc/my_bak.cnf
[root@leo-mysql-slave etc]# vi /etc/my.cnf
添加如下：
server_id=2          --当前节点全局性ID号
log-bin=slave_log     --开启二进制日志
read_only=ON        --设置从库只读，对root用户无效
relay_log=relay-log    --relay log的文件路径
relay_log_index=relay-log.index  -默认值hostname-relay-bin.index
2.3.2、重启mysql服务
重启从库mysql服务.
[root@leo-mysql-slave etc]# service mysql stop
Shutting down MySQL.. SUCCESS! 
[root@leo-mysql-slave etc]# service mysql start
Starting MySQL. SUCCESS!
2.3.3、启动复制线程
从库使用有复制权限的用户连接至主库，并启动复制线程
[root@leo-mysql-slave etc]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 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.133.111',master_user='repluser',master_password='123456',master_log_file='master_bin.000001',MASTER_LOG_POS=957;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
2.3.4、查看状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.133.111
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master_bin.000001
          Read_Master_Log_Pos: 957
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 325
        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: 957
              Relay_Log_Space: 528
              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: 3393dd41-fb9f-11ed-a80f-000c291140b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW SLAVE STATUS' is deprecated and will be removed in a future release. Please use SHOW REPLICA STATUS instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3、数据测试
3.1、建测试数据
现在主库上创建测试库booksDB，然后创建表books并插入记录.测试数据是否会同步到两个从库.
mysql> create database booksDB;
Query OK, 1 row affected (0.00 sec)

mysql> use booksDB;
Database changed
mysql> create table books
    -> (
    -> bk_id int not null primary key,
    -> bk_title varchar(50) not null,
    -> copyright year not null
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into books values 
    -> (11078,'Learning MYSQL',2010),
    -> (11033,'Study Html',2011),
    -> (11035,'How to use php',2003),
    -> (11072,'Teach yourself javascript',2005),
    -> (11028,'Learning C++',2005),
    -> (11069,'MYSQL professional',2009),
    -> (11026,'Guide to MySQL 5.7',2008),
    -> (11041,'Inside VC++',2011);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title                  | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7        |      2008 |
| 11028 | Learning C++              |      2005 |
| 11033 | Study Html                |      2011 |
| 11035 | How to use php            |      2003 |
| 11041 | Inside VC++               |      2011 |
| 11069 | MYSQL professional        |      2009 |
| 11072 | Teach yourself javascript |      2005 |
| 11078 | Learning MYSQL            |      2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
3.2、验证数据
在从库执行以下命令，查看数据是否已经同步.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| booksDB            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use booksDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| books             |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title                  | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7        |      2008 |
| 11028 | Learning C++              |      2005 |
| 11033 | Study Html                |      2011 |
| 11035 | How to use php            |      2003 |
| 11041 | Inside VC++               |      2011 |
| 11069 | MYSQL professional        |      2009 |
| 11072 | Teach yourself javascript |      2005 |
| 11078 | Learning MYSQL            |      2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
mysql> show replica status \G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.133.111
                  Source_User: repluser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: master_bin.000001
          Read_Source_Log_Pos: 1897
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 1265
        Relay_Source_Log_File: master_bin.000001
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 1897
              Relay_Log_Space: 1468
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: 3393dd41-fb9f-11ed-a80f-000c291140b1
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

说明：如上所示，主节点数据成功同步到从节点.

参考网址：
https://blog.csdn.net/weixin_46780832/article/details/129657998
https://blog.csdn.net/weixin_45648789/article/details/129125997