本文详细介绍了MySQL主从同步的配置步骤，包括一主多从和主从从配置，以及主主配置。重点讲解了主从同步的工作原理，通过修改配置文件、授权、备份与恢复数据，设置偏移量等操作实现数据同步。同时，文章还提供了实际的配置案例，帮助读者理解和应用。
mysql主从同步
- 大概过程
- 主数据库开启binlog日志功能记录自己的所有修改操作
- 从数据库开启IO和SQL线程监听主库，IO线程复制binlog二进制日志，SQL线程执行相应命令来同步数据
- 原理
- 主数据库开启binlog，就会有偏移量（position 可通过show master status来查看)来记录每个修改操作，从数据库建立主从的时候会记录当前主数据库的偏移量，当IO线程发现主数据库最新偏移量和本机从数据库偏移量不同的时候，就会把最新的binlog日志复制过来合并到中继日志中，并且通知SQL线程执行原本偏移量到最新偏移量的日志内容所代表的SQL语句。
- MySQL Replication复制的基本过程
1. Slave上面的IO线程连接上Master，并请求从指定日志文件的指定位置(Slave上的偏移量决定)之后的日志内容
2. Master接收到来自Slave的IO线程的请求后，通过复制的IO线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息以及Master端的binlog日志文件的名称以及binlog的位置；
3. Slave的IO线程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log(中继日志)的最末端，并将读取到的Master端的binlog文件名和位置记录到master-info文件中，下次可以根据master-info中的内容快速找到binlog日志
4. Slave的IO线程会通知SQL线程中继日志内容增加，SQL线程会马上解析relay-log(中继日志)的内容，执行对应的SQL语句来完成数据同步
主从配置案例
前置准备
-
测试机
-
主机 用途 192.168.4.51 主数据库服务器 192.168.4.52 从数据库服务器 192.168.4.1 客户端测试用
-
-
环境说明
- 4.51是主数据库服务器，4.52是从数据库服务器，通过调取主服务器上的binlog日志 在本地重做对应的库、表，实现与主服务器的数据同步
过程
#4.51
vim /etc/my.cnf
=======================
[mysqld]
server_id=51 #ID 和集群中其他的数据库要不同
log-bin=master51 #日志名 默认在/var/lib/mysql/目录下
=============================
systemctl restart mysqld
#用户授权 授权从服务器
mysql -uroot -p
grant replication slave on *.* to look@'%' identified by '123abc...A'
show master status\G #查看主数据状态
*************************** 1. row ***************************
File: master51.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
#4.52
vim /etc/my.cnf
=================
[mysqld]
server_id=52
=============================
systemctl restart mysqld
#4.51
mysqldump -uroot -p --master-data mydb1 > /mydb1.sql #备份
scp /mydb1.sql root@192.168.4.52:/root/ #给从数据库服务器
#4.52
mysql -uroot -p
create database mydb1;
exit
mysql -uroot -p mydb1 < /root/mydb1.sql #恢复数据
mysql -uroot -p
show slave status #查看状态信息
Empty set (0.00 sec) #还没设置
change master to
master_host='192.168.4.51',
master_user='look',
master_password='123abc...A',
master_log_file='master51-bin.000001',
master_log_pos=154; #偏移量需要和主服务器传过来的日志一致
#可以查看下偏移量
vim /root/mydb1.sql
=========================
.............
..................
CHANGE MASTER TO MASTER_LOG_FILE='master51.000001, MASTER_LOG_POS=154'
===================================================================
#继续操作
stop slave;
reset slave;
start slave; #启动从服务
show slave status; #查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: look
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 154
Relay_Log_File: db3-relay-bin.000003
Relay_Log_Pos: 365
Relay_Master_Log_File: master51.000001
Slave_IO_Running: Yes #IO线程要YES
Slave_SQL_Running: Yes #SQL线程要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: 154
Relay_Log_Space: 570
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: 51
Master_UUID: a962cbce-5cbf-11eb-8f09-000c2999bd9d
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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
#4.51授权客户端访问数据库
mysql -uroot -p
grant select,insert,update,delete on *.* to admin@'%' identified by '123abc...A';
#4.1测试
mysql -h192.168.4.51 -uadmin -p123abc...A
show grants; #查看权限
+------------------------------------------------------------+
| Grants for admin@% |
+------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'admin'@'%' |
+------------------------------------------------------------+
insert into mydb1.tb1 values(2,'ttt'); #插入一条数据
#4.52从数据库服务器查看是否同步
mysql -uroot -p
select * from mydb1.tb1;
+----+------+
| id | name |
+----+------+
| 1 | Jack |
| 2 | ttt | #成功
+----+------+
一主多从配置
前置准备
-
测试机
-
主机 说明 192.168.4.51 主数据库 192.168.4.52 从数据库 192.168.4.1 从数据库
-
过程
接上个案例
#4.51 备份数据库
mysqldump -uroot -p123abc...A --master-data -B mydb1 > mydb1.sql
#--master-data 默认为1 为生成的文件插入binlog日志名称和偏移量
#如果--master-data=2 那么插入的语句就会被注释
scp mydb1.sql root@192.168.4.1:/root/
#如果不加--master-data .sql文件中没有偏移量那行
#4.1 恢复数据
vim /etc/my.cnf
==================
server_id=53
==============================
systemctl restart mysqld
mysql -uroot -p < /root/mydb1.sql #恢复数据库
#查看下偏移量
grep 'CHANGE MASTER TO' /root/mydb1.sql
CHANGE MASTER TO MASTER_LOG_FILE='master51.000001', MASTER_LOG_POS=1197;
mysql -uroot -p
change master to
master_host='192.168.4.51',
master_user='look',
master_password='123abc...A',
master_log_file='master51-bin.000001',
master_log_pos=1197;
start slave;
#测试 在4.51增加数据
#在其他机子上查看验证
主从从配置
前置准备
-
机子配置
-
主机 说明 192.168.4.51 主数据库 192.168.4.52 192.168.4.51的从数据库 192.168.4.1的主数据库 192.168.4.1 192.168.4.52的从数据库
-
-
说明
- 链式结构 192.168.4.52是192.168.4.51的从数据库又是192.168.4.1的主数据库
过程
承接上面环境
#4.52 用户授权 授权同步时的用户
mysql -uroot -p
grant replication slave on *.* to look2@'%' identified by '123abc...A';
exit
vim /etc/my.cnf
==========================
server_id=52
log-bin=db52 #当4.1的主 开启binlog日志
log_slave_updates #允许级联复制
======================================
systemctl restart mysqld
mysql -uroot -p
show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db52.000001 | 154 | | | |
+-------------+----------+--------------+------------------+-------------------+
#4.1 连接即可
mysql -uroot -p
stop slave; #先停止之前一主多从
change master to
master_host='192.168.4.52',
master_user='look2',
master_password='123abc...A',
master_log_file='db52.000001',
master_log_pos=154;
start slave;
#4.52插入数据
mysql -uroot -p
insert into mydb1.tb1 values(3,'tomasi');
insert into mydb1.tb1 values(4,'java');
#4.1查看验证
mysql -uroot -p
select * from mydb1.tb1;
+----+--------+
| id | name |
+----+--------+
| 1 | Jack |
| 2 | tt |
| 3 | tomasi | #有了
| 4 | java | #有了
+----+--------+
#4.51插入数据
mysql -uroot -p
select * from mydb1.tb1; #修改从数据库不会更改主数据库
+----+------+
| id | name |
+----+------+
| 1 | Jack |
| 2 | tt |
+----+------+
insert into mydb1.tb1 values(5,'alice');
select * from mydb1.tb1; #查看下
+----+-------+
| id | name |
+----+-------+
| 1 | Jack |
| 2 | tt |
| 5 | alice |
+----+-------+
#4.52查看
mysql -uroot -p
select * from mydb1.tb1;
+----+--------+
| id | name |
+----+--------+
| 1 | Jack |
| 2 | tt |
| 3 | tomasi |
| 4 | java |
| 5 | alice | # 有了
+----+--------+
#4.1查看
mysql -uroot -p
select * from mydb1.tb1;
+----+--------+
| id | name |
+----+--------+
| 1 | Jack |
| 2 | tt |
| 3 | tomasi |
| 4 | java |
| 5 | alice | #有了
+----+--------+
总结
- 中间主从服务器4.52需要开启级联log_slave_updates
- 中间主从服务器4.52需要给从服务器4.1授权用户grant replication slave on . to look2@’%’ identified by ‘123abc…A’;
- 中间主从服务器4.52需要开启binlog日志
- 从服务器4.1需要和4.52保持相同的偏移量 4.52和4.51保持相同的偏移量
主主配置
互为主从 都开启binlog日志 设置相同偏移量即可
1816
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
