Mysql读写分离(1)-服务器的设置(主从复制)

1.简介

随着网站访问和请求量的增加，单台数据库服务器的连接已耗尽，会出现连接请求还在等待，或是数据库服务器崩溃等现象，这时候我们考虑如何减少数据库的连接，可以通过优化代码、使用缓存、数据库读写分离等方式解决此问题。

什么是读写分离：将数据库的读、写操作分别作用到不同的数据库（不同物理机）上。 

适用场景：读操作远大于写操作，包含大量复杂统计、离线计算等任务（比如定时按各维度对数据进行统计分析，或者结算佣金等） 

系统环境： linux centos 5.6 两台

mysql版本: 5.6

Mysql主从同步的现实： 

       1、Master库将变更记录到binlog中 

       2、Slave将master的binlog拷贝到它的relay log(中继日志)中

       3、slave重做中继日志中的事件

 

2.详细设置

==2-1主库操作==

1、在my.ini文件的[mysqld]节中添加如下配置项： 

server-id=1

log-bin=master-bin.log

sync_binlog=1

binlog-do-db=test_db  #指定对名称为test_db的数据库记录二进制日志

binlog-ignore-db = mysql #指定不对名称为mysql的数据库记录二进制日志 

binlog_format = mixed #binlog日志格式

expire_logs_days = 7  #超过7天的binlog删除###

 

2、使用root用户登录主库mysql，为同步操作创建一个用户，从库通过该用户同步主库数据

 

3、对待同步用户授权：

 

GRANT REPLICATION SLAVE ON *.* to 'test'@'%' identified by '123';

建立一个用户名是test的用户，认证密码是123。’test‘@'%' 表示允许在任意ip连接。拥有主从复制权限。*.* 表示所有数据库

其他例子：

 

grant select,insert,update,delete on db.* to test1@"192.169.0.122" Identified by "abc";

增加一个用户test1密码为abc，让他只能允许在192.169.0.122登录，并对db数据库下的所有数据表拥有插入、修改、删除的权限

 

4、重启主库mysql服务，并再次登录后查看主库状态：

 

show master status;

 如图:

 

记住下面的 master-bin.000002 和 120 这是从库要同步的文件和同步的位置 ()

 

好了，主库的配置完成了，现在进行从库的配置

 

==2-2从库操作==

1、登录从库的服务器，在my.ini文件的[mysqld]节中添加如下配置项：

server-id=2

log-bin=myslave-bin.log

sync_binlog=1

replicate-do-db = test_db   #复制名称为test_db的数据库 ,如果不设置,默认复制全部数据库                                                         

replicate-ignore-db = mysql   #不需要（忽略）复制名称为mysql的数据库                                            

replicate-ignore-db = information_schema    #不需要（忽略）复制名称为information_schema的数据库 

 

2、使用root用户登录从库mysql，修改从库的master信息

 

change master to master_host='192.168.1.109',master_user='test',master_password='123',master_log_file='mysql-bin.000002',master_log_pos=120;

留意上面参数的值：

master_host   主库的ip

master_log_file  上面提到的主库的日志名称

master_log_pos  上面提到的同步位置

千万不要搞错了，搞错了就会配置失败。

 

3、启动从库：start slave;

4、查看从库状态：show slave status;下面红色框框里面的两个参数都是Yes，则说明主从配置成功！

 

如果没有出现YES，请参考 Last_IO_Error: 这行的信息，参考下面的常见问题配置

 

 

3.测试

我们在主库里

 

然后马上到从库里查找(感觉应该会有几毫秒的延迟，这里不知道怎样保证事务安全，这个拓展到下篇文章再说了)

 

 

 

 

4.常见问题

4.1 不能连接的问题

主库关闭防火墙

或者用iptables 单独允许3306连接

然后从库重启mysql服务

 

4.2 出现 The slave I/O thread stops because master and slave have equal MySQL server UUIDs

首先停止mysql服务

找到auto.cnf的位置，然后把它删掉

启动就可以

参考:https://www.jb51.net/article/147106.htm

 

4.3 如果出现类似字样  “Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file n”

 

stop slave;

reset slave;

start slave;

 

 

5.参考

https://blog.csdn.net/qq_35622837/article/details/81130776

https://blog.csdn.net/anzhen0429/article/details/77014565