文档课题：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