mysql的主从复制

Ap7ricity

已于 2024-03-12 21:38:32 修改

阅读量840

点赞数 20

文章标签： mysql 数据库

于 2024-03-12 21:37:49 首次发布

本文链接：https://blog.csdn.net/weixin_69152872/article/details/136663895

版权

本文详细描述了如何在两台MySQL服务器间进行主从复制，包括设置二进制日志、配置服务器ID、同步初始数据、授权复制用户、配置slave服务器以及验证复制效果的过程。

摘要由CSDN通过智能技术生成

mysql主从复制的逻辑图：

实验步骤

准备2台服务器
1. master 192.168.152.135
2. Slave 192.168.152.136
确保2台服务器上的mysql服务是启动的

在主服务器上开启二进制日志，server_id=1

[root@sc-master ~]# cat /etc/my.cnf
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#开启二进制日志
log_bin
server_id = 1 #master上的server id配置成1
#每隔15天清除二进制日志
expire_logs_days = 15

在从服务器上也可以开启二进制日志，server_id=2

[root@sc-slave ~]# cat /etc/my.cnf
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#开启二进制日志
log_bin
server_id = 2
#每隔15天清除二进制日志
expire_logs_days = 15

2 初始化master和slaver上的数据，让两者数据保存一致

3，确保主服务器上开启二进制日志

root@(none) 20:41 mysql>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+

4，将之前二进制文件删除，产生一个新的日志，方便后面的操作

root@(none) 20:41 mysql>reset master;
Query OK, 0 rows affected (0.00 sec)

root@(none) 20:41 mysql>show master status
-> ;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| sc-master-bin.000001 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5，在master上建立授权用户，给slave来复制二进制日志

grant replication slave on *.* to 'renxj'@'192.168.152.%' identified by 'Sanchuang1234#';

6，在slave服务器上配置master info信息

CHANGE MASTER TO MASTER_HOST='192.168.152.135' ,
MASTER_USER='renxj',
MASTER_PASSWORD='Sanchuang1234#',
MASTER_PORT=3306,
MASTER_LOG_FILE='sc-master-bin.000001',
MASTER_LOG_POS=154;

7，查看slave是否配置成功

root@(none) 20:58 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.152.135
Master_User: renxj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sc-master-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: sc-slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: sc-master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
此时还未开服务

8，将slave的IO线程和SQL线程起来，并查看

root@(none) 21:01 mysql>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@(none) 21:01 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 21:02 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.152.135
Master_User: renxj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sc-master-bin.000001
Read_Master_Log_Pos: 450
Relay_Log_File: sc-slave-relay-bin.000002
Relay_Log_Pos: 620
Relay_Master_Log_File: sc-master-bin.000001
Slave_IO_Running: Yes #2个都是yes说明io线程和sql线程已经启动
Slave_SQL_Running: Yes

9，测试主从复制的效果

master上建立：
root@(none) 21:16 mysql>create database yuanrd;
Query OK, 1 row affected (0.00 sec)

root@(none) 21:17 mysql>use yuanrd;
Database changed
root@yuanrd 21:17 mysql>show tables;
Empty set (0.00 sec)

root@yuanrd 21:17 mysql>show tables;
+------------------+
| Tables_in_yuanrd |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)