随笔- 87 文章- 10 评论- 0 阅读- 91278

Centos7 搭建Mysql8.0 主从和主主同步

一、环境说明

MASTER：
[root@server01 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
外网IP：192.168.100.20
内网IP：192.168.200.20
 
SLAVE：
[root@server02 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
外网IP：192.168.100.21
内网IP：192.168.200.21

二、两台机器分别安装MySQL8.033版本

注意：主从机器MySQL版本要保持一致

从官网下载MySQL源
[root@server01 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
 
安装MySQL
[root@server01 ~]# yum install -y mysql80-community-release-el7-7.noarch.rpm
[root@server01 ~]# yum -y install mysql-community-server
[root@server01 ~]# mysql -V
mysql  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)
 
启动mysql并设为开机自启
[root@server01 ~]# systemctl start mysqld.service
[root@server01 ~]# systemctl enable mysqld.service
 
查看mysql默认登录密码
[root@server01 ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2023-06-28T21:12:45.766727Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: uQof%up,J5hH
 
登录mysql
[root@server01 ~]# mysql -uroot -puQof%up,J5hH
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: 8.0.33
 
Copyright (c) 2000, 2023, 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> \q
Bye

两台机器执行同样操作进行安装

三、防火墙开放3306端口

[root@server01 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@server01 ~]# systemctl restart firewalld

四、mysql主从配置

1、主库配置

#主mysql服务更改配置文件修改
[root@server01 ~]# cat /etc/my.cnf
 
#新增服务唯一ID ，并开启二进制日志
 
#server ID 要唯一
server-id=20
# 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-master-bin
 
#修改完配置之后需要重启
[root@server01 ~]# systemctl restart mysqld
 
#登录mysql
[root@server01 ~]# mysql -u root -p123456
 
#查看server id是否存在
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 20    |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.01 sec)
 
创建用来同步数据的用户syncadm,并授权。
mysql> create user 'syncadm'@'%' IDENTIFIED by 'syncadm';
# 授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'syncadm'@'%';
 
# 查看主sql状态，记录下File和Position的值，并且不进行其他操作以免引起Position的变化。
mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-master-bin.000001 |      716 |              |                  |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2、从库配置

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

#修改从库配置文件
[root@server02 ~]# cat /etc/my.cnf
# 设置server_id,注意要唯一
server-id=30
# 开启二进制日志功能
log-bin=mysql-slave-bin
# relay_log配置中继日志
relay_log=edu-mysql-relay-bin
 
#修改完配置之后需要重启
[root@server02 ~]# systemctl restart mysqld
 
#登录进入mysql
[root@server02 ~]# mysql -u root -p123456
 
＃执行同步前，要先关闭slave
mysql> stop slave;
 
 
#在从库配置主服务器信息
mysql> change master to master_host='192.168.200.20', master_user='syncadm', master_password='syncadm', master_port=3306, master_log_file='mysql-master-bin.000001', master_log_pos= 716,master_connect_retry=30;
 
#参数说明
master_host     #Master的地址
master_port     #Master的端口号
master_user     #用于数据同步的用户
master_password     #用于同步的用户的密码
master_log_file     #指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos      #从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry    #如果连接失败，重试的时间间隔，单位是秒，默认是60秒
 
#在从mysql中查看主从同步状态：
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.200.20
                  Master_User: syncadm
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: edu-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 0
              Relay_Log_Space: 180
              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: NULL
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: 20
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           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.00 sec)
 
此时的SlaveIORunning 和 SlaveSQLRunning 都是No，因为我们还没有开启主从复制过程。
 
#开启主从复制：
mysql> start slave;
 
#再次查看同步状态：
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.200.20
                  Master_User: syncadm
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-master-bin.000001
          Read_Master_Log_Pos: 716
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 333
        Relay_Master_Log_File: mysql-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: 716
              Relay_Log_Space: 547
              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: 20
                  Master_UUID: 862226ba-15f8-11ee-bd87-000c2990ff75
             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.00 sec)
 
SlaveIORunning 和 SlaveSQLRunning 都是Yes说明主从复制已经开启。

主从同步总结：

1、主库在配置文件中设置server id和开启bin-log日志文件

2、主库创建并授权用来同步的用户

3、在主库查看主sql状态，记录下File和Position的值，并且不进行其他操作以免引起Position的变化

4、从库在配置文件中设置server id和开启bin-log日志，并配置relay_log中继日志

5、在从库配置主库的授权的同步信息

6、在从库使用start slave；开启主从同步

7、在从库查看同步状态，SlaveIORunning 和 SlaveSQLRunning 都是Yes说明主从复制已经开启

其他：

-- 停止同步
STOP SLAVE;
-- 重置同步
reset slave;

-- 开启同步
start slave；
-- 查看同步状态
show slave status;

1	`五、mysql主主同步搭建主库1配置`

cat /etc/my.cnf
#server id
server-id=100
#自增id起始值
auto_increment_offset=1
#每次自增数字
auto_increment_increment=2
#开启log bin 日志
log-bin = mysql-bin
cat /etc/my.cnf
#server id
server-id=100
#自增id起始值
auto_increment_offset=1
#每次自增数字
auto_increment_increment=2
#开启log bin 日志
log-bin = mysql-bin
cat /etc/my.cnf
#server id
server-id=100
#自增id起始值
auto_increment_offset=1
#每次自增数字
auto_increment_increment=2
#开启log bin 日志
log-bin = mysql-bin
＃确保binlog日志写入后与硬盘同步
sync_binlog = 1      
 
#重启mysql
systemctl restart mysqld.service
 
#登录进入mysql
mysql -uroot -p
 
#创建用来同步数据的用户syncadm,并授权。
create user 'syncadm'@'%' IDENTIFIED by 'syncadm';
 
授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'syncadm'@'%';
 
# 查看主sql状态，记录下File和Position的值，并且不进行其他操作以免引起Position的变化。
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1972 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#重启mysql
systemctl restart mysqld.service
 
#登录进入mysql
mysql -uroot -p
 
#创建用来同步数据的用户syncadm,并授权。
create user 'syncadm'@'%' IDENTIFIED by 'syncadm';
 
授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'syncadm'@'%';
 
# 查看主sql状态，记录下File和Position的值，并且不进行其他操作以免引起Position的变化。
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1972 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
#重启mysql
systemctl restart mysqld.service
 
#登录进入mysql
mysql -uroot -p
 
#创建用来同步数据的用户syncadm,并授权。
create user 'syncadm'@'%' IDENTIFIED by 'syncadm';
 
授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'syncadm'@'%';
 
# 查看主sql状态，记录下File和Position的值，并且不进行其他操作以免引起Position的变化。
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1972 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

主库2配置

cat /etc/my.cnf
#server id
server-id = 120
#自增id起始值
auto_increment_offset = 2
#每次自增数字
auto_increment_increment = 2
#开启log bin日志
log-bin = mysql-bin
 
＃确保binlog日志写入后与硬盘同步
sync_binlog = 1      
 
#重启mysql
systemctl restart mysqld.service
 
#登录进入mysql
mysql -uroot -p
 
#创建用来同步数据的用户syncadm,并授权。
create user 'syncadm'@'%' IDENTIFIED by 'syncadm';
 
# 授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'syncadm'@'%';
  
# 查看主sql状态，记录下File和Position的值，并且不进行其他操作以免引起Position的变化。
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1202 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置同步数据

在主库1操作

change master to master_host='192.168.200.23', master_user='syncadm', master_password='syncadm', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 1202,master_connect_retry=30;
 
 
start slave;
 
#查看同步状态
 show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.200.23
                  Master_User: syncadm
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1202
               Relay_Log_File: server03-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: 1202
              Relay_Log_Space: 539
              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: 120
                  Master_UUID: e33563e2-167a-11ee-8313-000c29ce56d6
             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.00 sec)
 
SlaveIORunning 和 SlaveSQLRunning 都是Yes说明主从复制已经开启。

在主库2操作

change master to master_host='192.168.200.22', master_user='syncadm', master_password='syncadm', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 1972,master_connect_retry=30;
 
start slave;
 
#查看同步状态
show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.200.22
                  Master_User: syncadm
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1972
               Relay_Log_File: server04-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: 1972
              Relay_Log_Space: 539
              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: 100
                  Master_UUID: dab796c2-167a-11ee-8341-000c29b4d207
             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.00 sec)
 
SlaveIORunning 和 SlaveSQLRunning 都是Yes说明主从复制已经开启。

说明：

在主主同步配置时，需要将两台服务器的：

auto_increment_increment 增长量都配置为2

auto_increment_offset        分别配置为1和2。这是序号，第一台从1开始，第二台就是2，以此类推!这样效果就是：master的数据

id是1,3,5,7..., slave的数据id是2,4,6,8....

这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。（针对的是有自增长属性的字段）

测试同步

#在主库1创建数据库并写入数据
 
#查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
#创建test2数据库
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
 
#进入test2数据库
mysql> use test2;
Database changed
 
#创建user表
create table user(
    `deptld` varchar(15),
    `id` int(15),
    `name` varchar(15),
    `salary` int(10)
)
 
#查看表的名称
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| user            |
+-----------------+
 
#查看表的结构
mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptld | varchar(15) | YES  |     | NULL    |       |
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(15) | YES  |     | NULL    |       |
| salary | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
#在主库2查看创建的数据库是否同步过来
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)
 
mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptld | varchar(15) | YES  |     | NULL    |       |
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(15) | YES  |     | NULL    |       |
| salary | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+