MySQL主从复制配置
作用
-
减轻数据库的负载,优化数据库性能,实现读写分离减轻单库CURD的压力,同时也具有备份数据库的作用。
-
主从复制分为Mater(主库)和Slave(从库),Master库主要负责, 增 删 改 操作, 由于查询操作较多,所以Slave库只负责 查询 操作,这样就实现了读写分离,很大的减轻了单个数据库的压力。
主从复制的原理
- MySQL主从复制的基础是二进制日志文件(binary log file)。
Mater库必须开启binlog,当Master库发生操作,将操作记录(事件)存在binlog里。
Slave库将Mater库的binlog文件,同步到本机并记录在relay log中,
然后根据relay log记录的(事件)执行到自己的数据库,达到同步数据的效果。
同步过程
-
概念
-
Master有一个log dump线程,用来和Slave的I/O线程传递binlog,
当Slave (slave start)连接到Master的时候,Master机器会为Slave开启
binlog dump线程。当Master 的 binlog发生变化的时候,binlog dump
线程会通知Slave,并将相应的binlog内容发送给Slave。 -
Slave有两个线程,其中I/O线程用来同步Master的binlog并生成relay log,
另外一个SQL线程用来把relay log里面的sql语句落地(执行)
-
-
实现步骤
-
Slave 上的IO线程连接上 Master, 并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
-
Master 接收到来自 Slave 的 IO 线程的请求后,通过负责复制的 IO线程根据请求信息读取指定日志指定位置之后的日志信息,
返回给 Slave 端的 IO线程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 BinaryLog 中的位置。 -
Slave 的 IO 线程接收到信息后,将接收到的日志内容依次写入到 Slave 端的RelayLog文件(mysql-relay-lin.xxxxxx)的最末端,
并将读取到的Master端的bin-log的文件名和位置记录到 master-info文件中,以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”; -
Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后,会马上解析该 Log 文件中的内容成为在 Master端真实执行时候的那些可执行的 Query 语句,并在自身执行这些 Query。这样,实际上就是在 Master 端和 Slave端执行了同样的 Query,所以两端的数据是完全一样的。
-
主从复制一主一从配置
-
准备工作
-
主从数据库版本最好一致
-
主从数据库内数据保持一致
-
主数据库IP 192.168.10.164
-
从数据库IP 192.168.10.116
-
-
修改Master数据库配置文件
#vi /etc/my.cnf [mysqld] # 服务器唯一ID,默认是1,一般取IP最后一段 server-id=164 # 启用二进制日志 log-bin=mysql-bin # 不需要备份的数据库,多个写多行 binlog-ignore-db=mysql binlog-ignore-db=information_schema # 设置需要复制的数据库,多个写多行 binlog-do-db=test # 设置logbin格式 binlog_format=STATEMENT # 自动清理 2 天前的log文件,可根据需要修改 expire_logs_days=2
-
修改Slave数据库配置文件
#vi /etc/my.cnf [mysqld] # 启用中继日志 relay-log=mysql-relay # 服务器唯一ID,默认是1,一般取IP最后一段 server-id = 116
-
重启两个数据库服务
service mysqld restart
-
在Master服务器上建立帐户并授权slave
#进入数据库 #mysql -uroot -p #添加账户 一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.10.116,加强安全。 mysql>GRANT REPLICATION SLAVE ON *.* to 'slave用户名'@'%' identified by '123456密码';
-
查看Master状态
show master status;
-
配置Slave数据库
mysql>change master to master_host ='192.168.10.164', master_user='slave', master_password='123456', master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=49604;
#开启复制 Mysql>start slave; #关闭复制 Mysql>stop slave; #查看slave状态 Mysql>show slave status\G; #重新配置主从 Mysql>stop slave; Mysql>reset master;
- 最后测试查看数据是否同步
主从复制双主双从配置
-
准备四台数据库
角色 IP地址 Master1 192.168.1.101 Slave1 192.168.1.102 Master2 192.168.1.103 Slave2 192.168.1.104 -
修改Master1数据库配置文件
#vi /etc/my.cnf [mysqld] # 服务器唯一ID,默认是1,一般取IP最后一段 server-id=101 # 启用二进制日志 log-bin=mysql-bin # 不需要备份的数据库,多个写多行 binlog-ignore-db=mysql binlog-ignore-db=information_schema # 设置需要复制的数据库,多个写多行 binlog-do-db=test # 设置logbin格式 binlog_format=STATEMENT # 自动清理 2 天前的log文件,可根据需要修改 expire_logs_days=2 # 在作为从数据库的时候,有写入操作也要更新二进制日志文件 log-slave-updates # 表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535 auto-increment-increment=2 # 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535 auto-increment-offset=1
-
修改Master2数据库配置文件
#vi /etc/my.cnf [mysqld] # 服务器唯一ID,默认是1,一般取IP最后一段 server-id=102 # 启用二进制日志 log-bin=mysql-bin # 不需要备份的数据库,多个写多行 binlog-ignore-db=mysql binlog-ignore-db=information_schema # 设置需要复制的数据库,多个写多行 binlog-do-db=test # 设置logbin格式 binlog_format=STATEMENT # 自动清理 2 天前的log文件,可根据需要修改 expire_logs_days=2 # 在作为从数据库的时候,有写入操作也要更新二进制日志文件 log-slave-updates # 表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535 auto-increment-increment=2 # 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535 auto-increment-offset=2
-
修改Slave1数据库配置文件
#vi /etc/my.cnf [mysqld] # 从服务器唯一ID,默认是1,一般取IP最后一段 server-id=103 # 启用中继日志 relay-log=mysql-relay
-
修改Slave2数据库配置文件
#vi /etc/my.cnf [mysqld] # 从服务器唯一ID,默认是1,一般取IP最后一段 server-id=104 # 启用中继日志 relay-log=mysql-relay
-
重启四台 mysql 服务
-
分别在Master1 和 Master2 服务器上建立帐户并授权slave
# 进入数据库 mysql -uroot -p # 添加账户 一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.1.102,加强安全。 mysql>GRANT REPLICATION SLAVE ON *.* to 'slave用户名'@'%' identified by '123456密码';
-
分别查询Master1 和 Master2 的状态,
show master status;
特别重要》》》
分别记录下File和Position的值
执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
《《《
-
分别在从机上配置需要复制的主机 Slave1 复制 Master1,Slave2 复制 Master2
CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='主机添加的账号', MASTER_PASSWORD='主机添加的密码', MASTER_LOG_FILE='mysql-bin.记录的File具体数字',MASTER_LOG_POS=记录的Positio具体值;
-
分别启动两台从服务器复制功能,同时查看复制状态
# 启动复制 start slave; # 查看状态判断 Slave_IO_Running 和 Slave_SQL_Running 都为Yes时说明主从配置成功! show slave status\G;
-
配置两个主机互相复制 Master2 复制 Master1,Master1 复制 Master2
CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='主机添加的账号', MASTER_PASSWORD='主机添加的密码', MASTER_LOG_FILE='mysql-bin.记录的File具体数字',MASTER_LOG_POS=记录的Positio具体值;
-
分别启动两台主服务器复制功能,同时查看复制状态
# 启动复制 start slave; # 查看状态判断 Slave_IO_Running 和 Slave_SQL_Running 都为Yes时说明主从配置成功! show slave status\G;
-
最后测试查看数据是否同步
特别感谢各位大神的分享
- https://www.cnblogs.com/wangxin-king/p/5806630.html
- https://blog.51cto.com/13589255/2095858
- https://blog.csdn.net/linlinv3/article/details/51783976