MySQL主从复制与读写分离

最新推荐文章于 2025-07-07 00:22:25 发布

原创最新推荐文章于 2025-07-07 00:22:25 发布 · 335 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

20 篇文章

订阅专栏

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)

Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。

因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力这样的方案来进行部署与实施的。

如下图所示：

linux上：

/etc/my.cnf 文件原始内容：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

准备工作

1. 安装 MySQL5.7.22

如果从服务器是克隆的主服务器，则修改 auto.cnf（vi /var/lib/mysql/auto.cnf）文件中 server-uuid 值
不然后面主从复制会报 1593 错误，修改完记得重启MySQL

如果不知道auto.cnf 文件在哪，可通过命令 find / -name 'auto.cnf' 进行查找：
[root@nfs_client ~]# find / -name 'auto.cnf'
/var/lib/mysql/auto.cnf

另一说法：

错误处理：
如果出现此错误：
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
因为是mysql是克隆的系统所以mysql的uuid是一样的，所以需要修改。
解决方法：
删除/var/lib/mysql/auto.cnf文件，重新启动服务。

mysql主从正式开始：

一主从数据库

主数据库： 192.168.0.181 /linux
从数据库： 192.168.0.182 /linux

二主数据库master修改：

[root@master ~]# find / -name 'my.cnf'
/etc/my.cnf

1.修改mysql配置

找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/my.cnf,在[mysqld]部分插入如下两行：

log-bin=mysql-bin   # [必须]启用二进制日志
server-id=181       # [必须]服务器唯一ID，默认是1，一般取IP最后一段

注: server_id 必须唯一。

2.重启mysql,创建用于同步的用户账号(在master上操作) （修改完my.cnf，必须要重启MySQL）

创建完用于同步的用户账号，一定要对该用户账号赋权，并刷新权限。如果不赋权的话，会造成mysql从机 Slave_IO 异常。

打开MySQL会话：
[root@master etc]# mysql -hlocalhost -uroot -p
Enter password:

创建用户并授权：用户 root 密码： 123456
mysql> create user 'root'@'192.168.0.182' identified by '123456'; #创建用户
报错了：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决方案：改策略：
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
再次创建用户就不会报错了：
mysql> create user 'root'@'192.168.0.182' identified by '123456'; # 创建用于同步的用户账号
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'root'@'192.168.0.182'; # 分配权限
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.01 sec)

3.查看master状态，记录二进制文件名(mysql-bin.000009)和位置(626)：

三从服务器slave修改
1.修改mysql配置

#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin   #[不是必须]启用二进制日志
server-id=182       #[必须]服务器唯一ID，默认是1，一般取IP最后一段

eg:我的:
[mysqld]
server-id=182

2.重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql> change master to
-> master_host='192.168.0.181',
-> master_user='root',
-> master_password='123456',
-> master_log_file='mysql-bin.000009',
-> master_log_pos=626;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
mysql>
报错原因：因为第一步，没有重启mysql.

所以，重启mysql
[root@slave1 ~]# systemctl stop mysqld.service
[root@slave1 ~]# systemctl start mysqld.service登录mysql ,执行同步sql语句

[root@slave1 ~]# mysql -uroot -p
Enter password: 

mysql> change master to
    -> master_host='192.168.0.181',
    -> master_user='root',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000009',
    -> master_log_pos=626;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql>

3.启动slave同步进程：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

4.查看slave状态：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.251
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 626
               Relay_Log_File: node1-relay-bin.000023
                Relay_Log_Pos: 839
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。

5 验证主从复制效果

主服务器上的操作：
在主服务器上创建数据库first_db
mysql> create database first_db;
Query OK, 1 row affected (0.00 sec)

在主服务器上创建表first_tb
mysql> use first_db;
Database changed
mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)

在主服务器上的表first_tb中插入记录
mysql> insert into first_tb values (001,’myself’);
Query Ok, 1 row affected (0.00 sec)

注意：从机上设置同步关系
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
-> master_host='192.168.0.181',
-> master_user='root',
-> master_password='123456',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=325;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>

如果不先执行“stop slave;”，则可能遇到如下错误：
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

由于前面一步调用“stop slave;”，停止了复制。在完成后，需再启动复制：

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)