MYSQL主从复制和读写分离

最新推荐文章于 2025-12-05 18:26:01 发布

原创最新推荐文章于 2025-12-05 18:26:01 发布 · 100 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

本文详细介绍了MySQL的主从复制模式（异步、全同步、半同步），以及如何实现主从复制和读写分离，包括配置、时间同步和常见问题。同时提到了Mysql-proxy和Amoeba等开源工具在读写分离中的应用。

mysql主从复制和读写分离：

主从复制：主mysql上的数据，新增，修改库，表，表里的数据，都会同步到从mysql上

Mysql的主从复制的模式：

1.异步复制：mysql的默认复制就是异步复制。只要执行完之后，客户端提交事务，主mysql会立即把结果返回给从服务器，主mysql并不关心从mysql是否已经接受，并且处理。

主一旦崩溃，主mysql的事务可能没有传到从mysql，这个时候强行把从提升为主，可能到新的主mysql，数据不完整。（很少见，工作当中异步复制）

2.全同步复制：主库执行完成一个事务，所有的从库都执行了该事务之后才会返回客户端。

因为需要等待所有从库全部执行完成，性能必然下降。（对数据一致性，和数据完整要求很好的场景）

3.半同步复制：介于异步复制和全同步复制之间。主库执行完一个客户端提交的事务之后，至少等待一个从库接受并处理完成之后才会返回给客户端。半同步在一定程度上提高了数据的安全性。也会有一定的延迟。

这个延迟一般是一个tcp/ip的往返时间。（从发送到接受的时间，单位是秒），半同步复制最好在电视的网络中使用。

时间<1ms：round-trip time RTT

架构主从复制和读写分离：

mysql1主 192.168.120.80

mysql2从 192.168.120.90

mysql3从 192.168.120.100

test1读写分离的服务器 192.168.120.70

test2客户端 192.168.120.110

三台mysql安装时间同步工具

yum install ntp -y

Mysql主从服务器时间同步----

##主服务器设置##

修改主服务器的时间同步配置文件

vim /etc/ntp.conf

--末尾添加--

server 127.127.120.0 #设置本地是时钟源，注意修改网段

fudge 127.127.120.0 stratum 8 #设置时间层级为8（限制在15内）

数字越小，时间精确度越高，设置fudge 8 时间等级是8，最高到15。从本地获取时间源同步，不走网络。

刷新

systemctl restart ntpd

两台从服务器配置

##两台从服务器设置##

service ntpd start

/usr/sbin/ntpdate 192.168.120.80 #进行时间同步

crontab -e

*/30 * * * * /usr/sbin/ntpdate 192.168.120.80

三台服务器一起查看时间 date

主服务器的mysql配置

修改配置文件

vim /etc/my.cnf

log-bin=master-bin

binlog_format = MIXED

log-slave-updates=true

写入到自己的二进制日志

刷新

systemctl restart mysqld

进入主数据库

mysql -u root -p123456

给从库授权

GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.120.%' IDENTIFIED BY '123456';

刷新

FLUSH PRIVILEGES;

查看当前主服务器的状态信息，通过位置点同步

从服务器1：

修改配置文件

vim /etc/my.cnf

relay-log=relay-log-bin:

指定了从服务器上中继日志的基本文件名。在这个例子中，

中继日志的文件名将以 "relay-log-bin" 开头。

relay-log-index=slave-relay-bin.index:

指定了中继日志索引文件的名称。中继日志索引文件用于记录中继日志文件的顺序和位置。

在这个例子中，索引文件名为 "slave-relay-bin.index"。

relay_log_recovery=1

默认是0,1开启中继日志的护肤。从服务器出现异常或者崩溃，从服务会从主服务器的二进制的争取读取和应用中继日志。同步

刷新

systemctl restart mysqld

从服务器2

vim /etc/my.cnf

刷新

systemctl restart mysqld

进入这两个从服务器数据库：

CHANGEmastertomaster_host='192.168.120.80',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;

#配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致

启动同步

start slave;

查看 Slave 状态

show slave status\G;

确保 IO 和 SQL 线程都是 Yes，代表同步正常。

Slave_IO_Running:YES：负责和主库的IO通信

Slave_SQL_Running:YES：负责自己的slave mysql进程

主服务器创建库和表，从服务器同步创建

从服务器创建库和表，主服务器没有响应

Slave io running no

网络问题
my.cnf 配置文件写错了
CHANGEmastertomaster_host='192.168.233.21',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604; 要么是文件名错了，要么位置偏移量不对
防火墙的安全机制的问题

主从复制是单向的，只能从主复制到从服务器。

主从复制延迟问题

网络延迟
主从硬件设备（CPU主频、内存IO、硬件IO）
同步复制而不是异步复制

解决方案：

硬件方面，主库一般来说不需要动的太多，从库的硬件配置要更好。提升随机写的性能。硬盘可以考虑换成固态的。升级cpu的核数，扩展一下内存。尽量使用物理机（不要用云服务器）
网络层面：主从服务器都配置在一个局域网内，尽量避免跨网段和跨机房。
架构方面：读写分离，把写入控制在主库，从库负责读，降低从库的压力
配置方面：mysql配置。从配置文件的角度实现性能最大化

追求安全性的配置：

innodb_flush_log_at_trx_commit=1

#每次事务提交时都会刷新事务日志。以确保持久性，最高级别的数据安全性，但是会影响性能，默认就是1。

0 就是事务提交时不会立刻刷新，而是每秒刷新一次。可以提高性能，但是发生故障会导致数据丢失。

2 事务提交时，事务日志不会写入硬盘而是保存在系统缓存，不会进行刷新。一定的安全性和性能。内存要求比较高。

（生产中默认是1）

sync_binlog=1

#1也是默认值。每次提交事务之后，直接把二进制日志刷新到磁盘，可以确保日志的持久性。占用比较高的性能，但是安全性能搞。

0二进制日志写入到缓存，也不会刷新日志。故障发生也会丢失数据，内存的要求也提高了

3每3个事务执行一次刷新到磁盘，提高性能，但是一旦崩溃，数据会大量丢失

追求性能化：

sync_binlog=0