MySQL数据库主从同步的搭建（一看就会！！！）

MySQL从库小弟上还会运行一个SQL进程，该进程用于监视自身的中继日志，当发现自身的中继日志发生改变时，也就是与主库大哥的数据同步时发现日志有变更，立即将该中继日志改变对应的数据更改操作写入自身的数据库。[root@server51 ~]# mysqldump -hlocalhost -uroot -p'123qqq...A' -A > ab1.sql#备份已有数据。[root@server51

Aprilqxs

1035人浏览 · 2023-05-23 17:57:35

Aprilqxs · 2023-05-23 17:57:35 发布

老规矩先上理论~

什么是MySQL主从同步
实现不同MySQL服务器之间数据实时同步的解决方案

作用：

故障切换。
提供一定程度上的备份服务。
实现MySQL数据库的读写分离

过程：

依据MySQL主从同步的身份，可以将MySQL服务器分为主库和从库两种身份。MySQL从库同步主库的数据，MySQL主库将数据同步给从库

首先：当有数据更改语句执行时，MySQL主库要在更新数据的同时，它就会写二进制日志，将数据修改的内容记录进入日志中。
然后：MySQL从库上运行这一些I/O进程，这个进程会监视MySQL主库上的二进制日志（大哥你的数据有变化吗），大哥说有，然后从库小弟就会立即同步到自身的中继日志。
最后：MySQL从库小弟上还会运行一个SQL进程，该进程用于监视自身的中继日志，当发现自身的中继日志发生改变时，也就是与主库大哥的数据同步时发现日志有变更，立即将该中继日志改变对应的数据更改操作写入自身的数据库。从而实现同步效果~

将server51和server52搭建成MySQL主从结构

步骤：

server51(主服务器)
       1）开启binlog日志
       2）授权主从同步用户
       3）备份已有数据
   server2(从服务器)
       1）设置serverid，可不开启binlog日志
       2）还原数据(实现主从结构前保证服务器基础数据统一)
       3）搭建主从关系

server51操作

[root@server51 ~]# sed -rn '4,6p' /etc/my.cnf   #确保启用binlog日志
[mysqld]
log_bin=/mylog/db1
server_id=51
[root@server51 ~]# ls /mylog/                   #查看binlog日志文件

[root@server51 ~]# mysql -hlocalhost -uroot -p'123qqq...A' #登录MySQL服务

mysql> GRANT REPLICATION SLAVE ON *.* TO
-> repluser@'%' IDENTIFIED BY '123qqq...A'; #授权主从同步用户

mysql> SHOW GRANTS FOR repluser@'%'; #确认用户权限

mysql> SHOW MASTER STATUS; #查看活跃binlog日志信息

[root@server51 ~]# mysqldump -hlocalhost -uroot -p'123qqq...A' -A > ab1.sql #备份已有数据

[root@server51 ~]# scp ab1.sql 192.168.88.52:/root #同步备份文件

server52操作

[root@server52 ~]# vim /etc/my.cnf               #修改MySQL主配置文件
[root@server52 ~]# sed -rn '4,5p' /etc/my.cnf
[mysqld]
server_id=52   #设置serverid

[root@server52 ~]# systemctl restart mysqld #重启服务使配置生效

[root@server52 ~]# ls ab1.sql #确认主服务器备份数据同步成功
[root@server52 ~]# mysql -hlocalhost -uroot -p'123qqq...A' < ab1.sql #数据还原

[root@server52 ~]# mysql -hlocalhost -uroot -p'123qqq...A'                #登录MySQL服务
mysql> CHANGE MASTER TO                   #修改主服务为
-> MASTER_HOST = "192.168.88.51",   #主服务器地址
-> MASTER_USER = "repluser",       #连接主服务器用户
-> MASTER_PASSWORD = "123qqq...A",   #连接主服务器用户密码
-> MASTER_LOG_FILE = "db1.000003",   #主服务器正在使用的binlog日志
-> MASTER_LOG_POS = 28900637;       #从binlog日志什么位置开始同步
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> START SLAVE; #启动IO/SQL线程
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G               #查看主从同步状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.88.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db1.000003
Read_Master_Log_Pos: 28900637
  Relay_Log_File: server52-relay-bin.000002
  Relay_Log_Pos: 314
Relay_Master_Log_File: db1.000003
Slave_IO_Running: Yes       #IO线程正常工作
Slave_SQL_Running: Yes       #SQL线程正常工作
...
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
mysql> exit
Bye
[root@server52 ~]# ls /var/lib/mysql/master.info

测试主从同步

server51执行写操作
[root@server51 ~]# mysql -hlocalhost -uroot -p'123qqq...A' -e "CREATE DATABASE msdb";

[root@server51 ~]# mysql -hlocalhost -uroot -p'123qqq...A' -e "CREATE table msdb.user(name CHAR(20));"

[root@server51 ~]# mysql -hlocalhost -uroot -p'123qqq...A' -e "INSERT INTO msdb.user VALUES ('zhangsan');"

[root@server51 ~]# mysql -hlocalhost -uroot -p'123qqq...A' -e "SELECT * FROM msdb.user;"
+----------+
| name |
+----------+
| zhangsan |
+----------+

#server52自动同步
[root@server52 ~]# mysql -hlocalhost -uroot -p'123qqq...A' \
> -e "SELECT * FROM msdb.user;" #操作server51的过程中server52自动同步server51的新增数据
+----------+
| name |
+----------+
| zhangsan |
+----------+

#发现未操作的52机器已经同步51的数据

错误处理：

I/O 线程connecting状态：无法连接到主服务器

防火墙未关

查看配置 host，user，pasword 。。。。是否写错

UUID重复：原因使用了mysql服务器机器进行克隆

操作错机器

搭建主从同步前的数据备份与恢复有问题（基础数据不统一）

!!!记得在指定日志目录存放目录的时候chown mysql：mysql /mylog

#下一节课讲MySQL主从同步结构模式~~~