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、从库配置
#修改从库配置文件
[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;
五、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 增长量都配置为2auto_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 | | +--------+-------------+------+-----+---------+-------+
******************************我也想难过的时候到海边走走，可是我的城市没有海。******************************
浙公网安备 33010602011771号