MYSQL8主从模式配置实践

环境：

服务器操作系统Rocky 9.4 

主服务器IP:192.168.1.216

从服务器IP:192.168.1.217

数据库:mysql 8.0.38

一、服务器环境准备

1. 两台服务器安装操作系统
2. 配置IP地址
3. 安装及启动MYSQL。

（详略）

二、主从库配置

1、配置主服务器的my.cnf，启用二进制日志。

根据网上资料，修改主服务器配置 (my.cnf):

[mysqld]

log_bin = /var/log/mysql/mysql-bin.log

server-id = 1

修改后重启MYSQL报错

Job for mysqld.service failed because the control process exited with error code.

See "systemctl status mysqld.service" and "journalctl -xeu mysqld.service" for details.

运行# journalctl -xeu mysqld.service 

结果为：

░░ An ExecStart= process belonging to unit mysqld.service has exited.

░░

░░ The process' exit code is 'exited' and its exit status is 1.

8月 05 11:22:32 localhost.localdomain systemd[1]: mysqld.service: Failed with r>

░░ Subject: Unit failed

░░ Defined-By: systemd

░░ Support: https://wiki.rockylinux.org/rocky/support

░░

░░ The unit mysqld.service has entered the 'failed' state with result 'exit-cod>

8月 05 11:22:32 localhost.localdomain systemd[1]: Failed to start MySQL Server.

░░ Subject: mysqld.service 单元已失败

░░ Defined-By: systemd

░░ Support: https://wiki.rockylinux.org/rocky/support

░░

░░ mysqld.service 单元已失败。

░░

░░ 结果为“failed”。

8月 05 11:22:32 localhost.localdomain systemd[1]: mysqld.service: Consumed 3.26>

░░ Subject: Resources consumed by unit runtime

░░ Defined-By: systemd

░░ Support: https://wiki.rockylinux.org/rocky/support

░░

░░ The unit mysqld.service completed and consumed the indicated resources.

lines 122-144/144 (END)

判断log_bin设置错误，修改为

[mysqld]

log_bin = mysql-bin

#systemctl start mysqld   执行成功，

查看状态：

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

1 row in set (0.01 sec)

确认已启用二进制日志模式

2、确定主服务器的日志文件名及位置值。

查询主服务器状态：

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      157 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

获得二进制日志文件名mysql-bin.000001，位置值为157。

这两个信息后面从服务器配置要用到。

3、在主服务器上创建复制用户并授权。

mysql> CREATE USER 'replica'@'%' IDENTIFIED BY 'replica@12345';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

mysql> FLUSH PRIVILEGES;

4、从服务器配置 (my.cnf):

[mysqld]

server-id = 2

5启动复制:

mysql> CHANGE MASTER TO

mysql> MASTER_HOST='192.168.1.216',   #设置主服务器IP

mysql> MASTER_USER='replica',      #设置复制用户账号名

mysql> MASTER_PASSWORD='replica@12345',   #复制用户账号密码

mysql> MASTER_LOG_FILE=' mysql-bin.000001’ , #主服务器的log文件名

mysql> MASTER_LOG_POS=157 ;   #主服务器log位置值

mysql>START SLAVE;  #在从服务器上启动复制。

6、使用SHOW SLAVE STATUS;命令检查复制状态

mysql> SHOW SLAVE STATUS;

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for source to send event

                  Master_Host: 192.168.1.216

                  Master_User: replica

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 157

               Relay_Log_File: localhost-relay-bin.000002

                Relay_Log_Pos: 326

        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: 157

              Relay_Log_Space: 540

              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: db18853e-4411-11ef-9160-0050569d34b9

             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: 0

            Network_Namespace:

1 row in set, 1 warning (0.01 sec)

其中：

Slave_IO_Running:Yes:负责和主库的IO通信

Slave_SQL_Running: Yes 负责自己的slave mysql进程

确认配置完成。

7、测试主服务器数据能否成功复制

测试方法：在主服务器创建测试数据库及表，插入测试数据。然后在从服务器进行查询，看数据是否成功复制。

-- 在主服务器创建新数据库，数据库名为test_db

mysql>CREATE DATABASE test_db;

-- 选择新数据库

mysql>USE test_db;

-- 创建一个简单的用户表

mysql>CREATE TABLE users (

  id INT AUTO_INCREMENT PRIMARY KEY,

  username VARCHAR(255) NOT NULL,

  email VARCHAR(255) NOT NULL

);

-- 插入一些测试数据

mysql>INSERT INTO users (username, email) VALUES (‘user01’, ‘user01@test.com’);

mysql>INSERT INTO users (username, email) VALUES (‘user02’, ‘user02@test.com’);

--在从服务器选择新数据库

mysql>USE test_db;

--查询用户表数据

mysql>select * from users;

测试证明主库数据变更已成功复制到从库。