节点信息

节点名称	节点IP
mysql01	172.16.1.10
mysql02	172.16.1.14

基础配置

修改主机名

hostnamectl set-hostname mysql01
hostnamectl set-hostname mysql02
bash

关闭firewalld

systemctl disable firewalld
systemctl stop firewalld

关闭selinux

setenforce 0

安装mysql yum源

yum install http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

安装mysql

yum install mysql-community-server

配置文件

mysql01

[mysqld]
server-id=10
sync-binlog=1
log_bin=master-bin-10
log_bin_index=master-bin.index
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
relay_log_index=master_relay_bin.index
relay_log=master_relay_bin
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled=1
loose_rpl_semi_sync_slave_enabled=1
loose_rpl_semi_sync_master_timeout=5000

mysql02

[mysqld]
server-id=14
sync-binlog=1
log_bin=master-bin-10
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
relay_log_index=master_relay_bin.index
relay_log=master_relay_bin
relay_log_purge=0
innodb_log_file_size=256M
expire-logs-days=1
read_only=1
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled=1
loose_rpl_semi_sync_slave_enabled=1
loose_rpl_semi_sync_master_timeout=5000

数据库初始化

启动mysql服务

systemctl start mysqld

查看mysql登录密码

$ grep 'temporary password' /var/log/mysqld.log
2024-04-10T02:02:11.364742Z 1 [Note] A temporary password is generated for root@localhost: cFdG8e/=Lh+w

修改root登录密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '!QAZ2wsx';
FLUSH PRIVILEGES;

主从配置

主库配置

创建同步用户

create user 'repl'@'%' identified by '!QAZ2wsx';
grant replication slave,replication client on *.* to 'repl'@'%';
flush privileges;

查看master日志

mysql> show master status \G;
*************************** 1. row ***************************
             File: master-bin-10.000002
         Position: 1163
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,performance_schema,sys
Executed_Gtid_Set: 57a13b62-f6de-11ee-b128-000c2930ff4f:1-5
1 row in set (0.01 sec)

从库配置

设置主库信息

change master to master_host='172.16.1.10',master_user='repl',master_password='!QAZ2wsx',master_log_file='master-bin-10.000002',master_log_pos=1163;

启动从库

start slave;

查看从库状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin-10.000002
          Read_Master_Log_Pos: 1163
               Relay_Log_File: master_relay_bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: master-bin-10.000002
             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: 1163
              Relay_Log_Space: 532
              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: 10
                  Master_UUID: 57a13b62-f6de-11ee-b128-000c2930ff4f
             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: 5c256daf-f6de-11ee-b17c-000c298cef5e:1-2
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

主从测试

主库创建数据库

create database gts DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

从库查询该数据库

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