Mysql主从同步
一、功能作用
1.做数据热备：如果主数据库宕机，可以切换到从数据库上。也可以用从数据库做备份、统计，不影响主数据库的性能。
2.架构的扩展：业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3.实现读写分离：如果读和写都在同一台数据库服务器上，面对大的并发，消耗资源较大。
二、原理
简单来说，主从同步有3个步骤：
1.master将改变记录到二进制日志（binlog）中（这些记录叫做二进制日志事件binary log events）
2.slave将master的bin log events复制到中继日志（relaylog）中
3.slave重做中继日志中的事件，修改slave上的数据
DML：SQL操作语句，update，insert，delete
relay log : 中继日志
三、主从复制过程
1.master记录二进制日志。
在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
2.slave将master的binary log拷贝到它自己的中继日志。
首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
3.SQL slave thread（SQL从线程）处理该过程的最后一步。
SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。
四、常见类型
1.一主多备
一般用来做读写分离，master写，其他slave读，这种架构最大问题I/O压力集中。在master上，多台同步影响I/O。
2.M-S-S
使用一台slave做中继，分担master的压力，slave中继需要开启bin-log，并配置log-slave-updates
Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志(不实用,造成资源的浪费)
3、M-M双主互备 (互为主从)
很多人误以为这样可以做到MySQL负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性
4、M-M-M
监控三台机器互相做对方的master
天生的缺陷：复制延迟，slave上同步要慢于master,如果大并发的情况那延迟更严重
5、One slave Muti master 一从对多主
好处：节省成本，将多个master数据自动化整合
缺陷：对库和表数据的修改较多
五、配置主从
主：
编辑mysql配置文件，定义master和slave。
[root@test1 ~]# vim /etc/my.cnf
server-id=1 //本机数据库ID标识
log-bin=master-bin //启动二进制日志
innodb-file-per-table=ON //使用独立的表空间（优化，可不加）
skip-name-resolve=ON //跳过DNS解析，加快连接速度（优化，可不加）
授权从主机
mysql> grant replication slave on *.* to slave@’192.168.0.%’ identified by “123456”;
刷新使其立即生效
mysql> flush privileges;
重启mysql
[root@test1 ~]# systemctl restart mysql
mysql> show master status;
+—————————-+—————+———————+—————————+—————————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————————-+—————+———————+—————————+—————————-+
| master-bin.000001 | 154 | | | |
+—————————-+—————+———————+—————————+—————————-+
1 row in set (0.00 sec)
查看二进制日志
[root@test1 ~]# ls /var/lib/mysql
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql performance_schema server-cert.pem
ca-key.pem client-key.pem ib_logfile0 master-bin.000001 mysql.sock private_key.pem server-key.pem
ca.pem ib_buffer_pool ib_logfile1 master-bin.index mysql.sock.lock public_key.pem sys
MariaDB [(none)]> show binlog events\G
从：
测试连接master数据库
[root@test2 ~]# mysql -uslave -p123456 -h192.168.0.31
修改mysql配置文件，定义ID
[root@test2 ~]# vim /etc/my.cnf
server-id=2
relay-log-index=relay-log.index
skip-name-reslove=ON
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
配置主从：test2
mysql>change master to master_host=’192.168.0.31’,master_user=’slave’,master_password=’123456’,master_log_file=’master-bin.000003’,master_log_pos=601;
启动slave
mysql> start slave;
查看状态
mysql> show slave status \G
Slave_IO_Running ：一个负责与主机的io通信
Slave_SQL_Running：负责自己的slave mysql进程
注意：如果出现NO则检查防火墙和selinux是否关闭，主从mysql的UUID是否重复。若UUID重复，修改/var/lib/mysql/auto.cnf
查看主服务器状态
mysql> show processlist \G
插入数据测试同步：
mysql> create database bbs;
mysql> use bbs;
mysql> create table T1(id int,name varchar(10));
mysql> insert into T1 values(1,’man’);
从数据库上查看：
六、排错
如果遇到主从不同步，看一下主从bin-log的位置，然后再同步。
在主服务器上看二进制日志事件列表
mysql> showbinlog events \G
从服务器执行MySQL命令下：
mysql> stop slave； #先停止slave服务
mysql> changemaster to master_log_file=’mysql-bin-master.000001’,master_log_pos=1164;
#根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，达到同步的效果
mysql>slavestart; #启动从服务器同步服务
mysql> showslave status\G; #用show slave status\G;看一下从服务器的同步情况
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
如果都是yes，那代表已经在同步
重启从服务器，再查看状态：
停止从服务器slave stop;
开启从服务器slave start;
排错思路：
1、二进制日志没有开启
2、IPTABLES 没有放开端口
3、对应的主机 IP地址写错了
SQL线程出错
1、主从服务器数据库结构不统一
出错后，数据少，可以手动解决创建插入，再更新slave状态。
注：如果主上误删除了。那么从上也就误删除了。#因此主上要定期做mysqldump备份
青旅半醒
超、凢脫俗
系统管理员
小鱼儿
还没有评论，来说两句吧...