Mysql主从同步M-S部署

1
2
3
实验环境:Centos6.5 minimal 64位系统
服务器IP:192.168.10.27(master)
192.168.10.29(slave)

说明:

1
2
3
4
1.	安装包已经提前下载好,此文档中不再演示下载步骤
2. 根据笔者习惯,所有的源码包都在/a01/apps/apps_src/
3. 所有的服务安装路径都在/a01/apps/xxxxxxx
4. 服务器只安装了MySQL,如果不会安装,请参考之前文章

包版本:

1
MySQL:		mysql-5.7.19.tar.gz或者mysql-boost-5.7.19.tar.gz

在讲Mysql主从同步之前,我们先来说说Mysql复制的概念

Mysql复制概念说明

Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的。复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。

需要注意的是:

在进行mysql复制时,所有对复制中的表的更新必须在主服务器上进行。否则必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

Mysql支持哪些复制
1
2
3
1、基于语句的复制: 在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制。 
2、基于行的复制:把改变的内容复制过去,而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
3、混合类型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。
Mysql复制能解决的问题
1
2
3
4
5
1、数据分布 (Data distribution )
2、负载平衡(load balancing)
3、数据备份(Backups) ,保证数据安全
4、高可用性和容错行(High availability and failover)
5、实现读写分离,缓解数据库压力

Mysql主从同步原理

master服务器将数据的改变记录二进制binlog日志,当master上的数据发生改变时,则将其改变写入二进制日志中;salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/OThread请求master二进制事件,同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从节点本地的中继日志中,从节点将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后I/OThread和SQLThread将进入睡眠状态,等待下一次被唤醒。

注意几点:
1
2
3
4
5
1、master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。
2、slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和master数据保持一致了。
3、Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
4、Mysql复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)
5、master和slave两节点间时间需同步
主从同步的条件:
1
2
3
4
5
6
7
1)开启Binlog功能
2)主库要建立账号
3)从库要配置master.info(CHANGE MASTER to...相当于配置密码文件和Master的相关信息)
4)start slave 开启复制功能
5)保证主从服务器之间网络互通,能使用对方授权信息连接到对方数据库
6)防火墙开启3306端口
7)同步数据之前,双方数据库中需要同步的数据要保持一致
主从同步需要注意:
1
2
3
4
5
1)主从复制是异步的逻辑的SQL语句级的复制
2)复制时,主库有一个I/O线程,从库有两个线程,I/O和SQL线程,其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和master数据保持一致了。
3)实现主从复制的必要条件是主库要开启记录binlog功能,通常为了数据安全考虑,slave也开启binlog功能
4)作为复制的所有Mysql节点的server-id都不能相同
5) binlog文件只记录对数据库有更改的SQL语句(来自主库内容的变更),不记录任何查询(select,show)语句
彻底解除主从同步关系:
1
2
3
1 )stop slave;
2 )reset slave; 或直接删除master.info和relay-log.info这两个文件;
3 )修改my.cnf删除主从相关配置参数。

Mysql主从同步部署

master数据库部署

一、创建需要同步的数据库
1
2
3
4
5
6
7
mysql -u root -p123456

mysql> create database nongkaige;
mysql> use nongkaige;
mysql> create table if not exists nong(id int(10),name varchar(20));
mysql> insert into nongkaige.nong values(1,"nongkaige"),(2,"huanglihua"),(3,"nongziyi");
mysql> select * from nongkaige.nong;

PLOhm6.png

二、配置my.cnf
1
2
3
4
5
6
7
8
vim /etc/mysql/my.cnf
在[mysqld]下添加:

server-id=1 #数据库唯一ID,主从的标识号绝对不能重复
log-bin=mysql-bin-master #启用二进制日志,并指定前缀名,可自定义
binlog-do-db=nongkaige #需要同步的数据库,如果多个,则另写几行即可,如果不指定对哪个库进行同步,就去掉此行,表示同步所有库(除了ignore忽略的库)
binlog-ignore-db=mysql #不同步mysql系统库,如果多个,则另写几行即可
sync-binlog=1 #确保binlog日志写入后与硬盘同步,此参数不是强制要求写入

温馨提示: 在主服务器上最重要的二进制日志设置是sync_binlog,这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上,即使服务器崩溃也会把事件写入日志中。

sync_binlog这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。

对于”sync_binlog”参数的各种设置的说明如下:

1
2
1)sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
2)sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。

从以往经验和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

三、导出master需要同步的数据库,然后导入到slave数据库,保证双方在同步之前数据一致
  • (导出之前先锁定只读数据库,防止导出的时候有数据写入,unlock tables命令解除锁定)
1
2
3
4
mysql> flush tables with read lock;

mysqldump -u root -p123456 -B nongkaige > nongkaige.sql
scp nongkaige.sql 192.168.10.29:/root/
四、设置数据同步权限
1
2
3
4
mysql -u root -p123456

mysql> grant replication slave on *.* to slave@'192.168.10.29' identified by "123456";
mysql> flush privileges;

查看权限方式:

1
mysql> show grants for slave@'192.168.10.29';

PLOokD.png

五、检测slave数据库能不能使用授权的用户密码登录到master数据库上
1
mysql -u slave -p123456 -h 192.168.10.27

PLObpd.png

六、重启master数据库,查看主服务器master状态(注意,是重启,reload也不顶用)
1
2
3
4
service mysqld restart
mysql -u root -p123456

mysql> show master status;

PLOx78.png

slave数据库部署

一、配置my.cnf
1
2
3
4
5
vim /etc/mysql/my.cnf
在[mysqld]下添加:

server-id=2
slave-skip-errors=all #跳过所有的错误,继续执行复制操作,此参数不是必须写入参数。
二、重启slave数据库
1
service mysqld restart
三、导入从master传过来的数据
1
2
3
4
5
6
mysql -u root -p123456

(要先创建nongkaige库,和master上的一样,否则一会导入会报错)
mysql> create database nongkaige;
mysql> use nongkaige;
mysql> source /root/nongkaige.sql;

也可以不登录mysql导入:

1
mysql -u root -p123456 nongkaige < nongkaige.sql

查看一下是否导入成功:

PLXptg.png

四、配置主从同步指令

(执行同步之前,要先关闭slave)

1
2
3
4
mysql> stop slave;
mysql> change master to master_host='192.168.10.27',master_user='slave',master_password='123456';
mysql> start slave;
mysql> show slave status \G

PLXi1s.png

Slave_IO_Running :一个负责与主机的io通信

Slave_SQL_Running:负责自己的slave mysql进程

如上,当IO和SQL线程的状态均为Yes,则表示主从同步已经实现了!

测试主从同步M-S效果

1、先在master数据库上写入数据(先解锁只读数据,否则无法写入)
1
2
mysql> unlock tables;
mysql> insert into nongkaige.nong values(100,"pipixia");
2、在slave数据库上查看,发现master上新写入的数据已经同步过来了
1
mysql> select * from nongkaige.nong;

PLXEn0.png

博主QQ:1012405802
技术交流QQ群:830339411
版权声明:网站内容有原创和转载,如有侵权,请联系删除,谢谢!!
感谢打赏,93bok因你们而精彩!!(支付宝支持花呗)
0%