mysql 主从复制。

时间 2019-12-05 标签 mysql 主从复制

MySQL Replication html

Replication能够实现将数据从一台数据库服务器（master）复制到一或多台数据库服务器（slave）
默认状况下属于异步复制，无需维持长链接经过配置，能够复制全部的库或者几个库，甚至库中的一些表是MySQL内建的，自己自带的mysql

官方文档
Replication的原理
简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操做

DML:SQL操做语句，update,insert，delete
Relay log :中继日志
Replication的做用
一、Fail Over 故障切换
二、Backup Server 备份服务，没法对SQL语句执行产生的故障恢复，有限的备份
三、High Performance高性能，能够多台slave,实现读写分离nginx

Replication如何工做
总体上来讲，复制有3个步骤：sql

(1) master将改变记录到二进制日志(binary log)中（这些记录叫作二进制日志事件，binary log events）；
(2) slave将master的binary log events拷贝到它的中继日志(relay log)；
(3) slave重作中继日志中的事件，修改salve上的数据。
数据库

mysql主从复制中：
第一步：master记录二进制日志。在每一个事务更新数据完成以前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即便事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
第二步：slave将master的binary log拷贝到它本身的中继日志。首先，slave开始一个工做线程——I/O线程。I/O线程在master上打开一个普通的链接，而后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，若是已经执行完master产生的全部文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并从新执行其中的事件而更新slave的数据，使其与master中的数据一致。vim

此外，在master中也有一个工做线程和其它MySQL的链接同样，slave在master中打开一个链接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操做不能在slave上并行操做。
Replication常见方案：
一、One master and Muti salve 一主多备服务器

通常用来作读写分离的，master写，其余slave读，这种架构最大问题I/O压力集中
在Master上<多台同步影响IO>
二、M-S-S
使用一台slave做为中继，分担Master的压力，slave中继须要开启bin-log,并配置log-slave-updatesmarkdown

Slave中继可以使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志
三、M-M 双主互备
不少人误觉得这样能够作到MySQL负载均衡，实际什么没什么好处，每一个服务器须要作一样的同步更新，破坏了事物的隔离性和数据的一致性网络

四、M-M-M
监控三台机器互相作对方的mastersession

天生的缺陷：复制延迟，slave上同步要慢于master,若是大并发的状况那延迟更严重
Mysql在5.6已经自身能够实现fail over
五、One slave Muti master 一从对多主
好处：节省成本，将多个master数据自动化整合
缺陷：对库和表数据的修改较多

部署MySQL主从同步
环境准备：
主机名 IP 系统/MySQL版本角色

test63  192.168.1.63    Rhel6.5/5.5.32  Master
test64  192.168.1.64    Rhel6.5/5.5.32  slave

模式：C/S
端口：3306
配置主数据库服务器testd63
建立须要同步的数据库：

mysql> create database HA;
mysql> use HA;
mysql> create table T1(id int,name varchar(20));
service mysqld stop

配置my.cnf:
vim /etc/my.cnf

log-bin=mysql-bin-master  #启用二进制日志
server-id       = 1   #本机数据库ID 标示
binlog-do-db=HA   #能够被从服务器复制的库。二进制须要同步的数据库名
binlog-ignore-db=mysql  #不能够被从服务器复制的库
# 重启mysql:
service mysqld restart

受权：

mysql> grant replication slave on *.* to slave@192.168.1.64 identified by "123456";
// 查看状态信息
mysql> show master status; +-------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------------+----------+--------------+------------------+
| mysql-bin-master.000001 | 259 | HA | mysql | +-------------------------+----------+--------------+------------------+

mysql> show binlog events\G

// 复制前要保证同步的数据库一致
mysqldump  -usystem -p123456  HA >HA.sql  #也能够导出数据库
// 将导出的数据库传给从服务器
方法一：scp HA.sql  192.168.1.64:/root
方法二：使用nc命令
NetCat，它短小精悍、功能实用，被设计为一个简单、可靠的网络工具，可经过TCP或UDP协议传输读写数据。同时，它仍是一个网络应用Debug分析器，由于它能够根据须要建立各类不一样类型的网络链接。
语法：
服务器端：nc发送数据的语法：  nc   -l  端口  < 要传输的文件
客户端：  nc接受数据的语法：  nc   远程nc服务器端IP  端口   >  文件名
开始传送：
[root@testd63 ~]# nc -l 888 <HA.sql
[root@testd63 ~]# lsof -i :888
COMMAND  PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
nc      3464 root    3u  IPv4  18662      0t0  TCP *:cddbp (LISTEN)
开始接收：
记得清空两台服务器的iptables,要否则端口不能通讯
iptables –F
/etc/init.d/iptables save
nc 192.168.1.63 888 > HA.sql

配置从数据库服务器test64
两台数据库服务器mysql版本要一致

mysql> show variables like '%version%;`

测试链接到主服务器是否成功

mysql -uslave -p123456 -h 192.168.1.63

只有复制的权限
导入数据库，和主数据库服务器保持一致

mysql> create database HA;
mysql -usystem -p123456 HA<HA.sql

修改从服务器配置文件：
从服务器不必开启bin-log日志

service mysqld stop
vim /etc/my.cnf
server-id       = 2  #从服务器ID号，不要和主ID相同 ，若是设置多个从服务器，每一个从服务器必须有一个惟一的server-id值，必须与主服务器的以及其它从服务器的不相同。能够认为server-id值相似于IP地址：这些ID值能惟一识别复制服务器群集中的每一个服务器实例。

mysql> change master to master_host='192.168.1.63',master_user='slave',master_password='123456';
master_log_file=' mysql-bin.000007' ,master_log_pos= 295 
mysql> start slave; 
mysql> show slave status\G  查看状态

Slave_IO_Running ：一个负责与主机的io通讯
Slave_SQL_Running：负责本身的slave mysql进程
再到主服务器上查看状态：

mysql> show processlist \G

插入数据测试同步：

mysql> insert into T1 values(1,'天河');

从数据库上查看：

select * from HA.T1

排错：
若是遇到主从不一样步，看一下主从bin-log的位置，而后再同步。

mysql> show master status \G;

从服务器执行MySQL命令下：

mysql> slave stop；             #先中止slave服务
mysql> change master to master_log_file='mysqllog.000004' ,master_log_pos=106;
#根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，达到同步的效果
mysql>slave start;                      #启动从服务器同步服务
mysql> show slave 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备份。
五：部署MySQL主主双向主从复制  M-M
经过mysql主主：进行mysql 双向同步数据库HA的配置
mysql主：服务端：testd63   IP：192.168.1.63
mysql主：服务端：test64   IP：192.168.1.64  
在上面主从的基础上进行配置
// 配置test64
它有两种身份：
身份1： test64的主。 身份2： testd63的从。
vim /etc/my.cnf
log-bin=mysql-bin-slave
binlog-do-db=HA
binlog-ignore-db=mysql  #避免同步mysql用户 相关配置
service mysqld restart

mysql> grant replication slave on *.* to slave64@'192.168.1.63' identified by '123456';
配置testd63
vim /etc/my.cnf

[root@testd63 ~]# mysql -usystem -p123456
mysql> change master to master_host='192.168.1.64',master_user='slave64',master_password='123456';
master_log_file='mysqllog.000004' ,master_log_pos=106;
mysql> start slave;
mysql> show slave status\G

检查：
test64上查看slave状态

mysql> show slave status\G

testd63上查看slave状态

mysql> show slave status\G

插入数据测试：
在testd63上插入数据，test64上查看

mysql> insert into T1 values(2,'天清');

在test64上插入数据，testd63上查看

mysql> insert into T1 values(3,'黑客');

注意：这种M-M架构没有什么好处，他每每给咱们产生一种实现了负载均衡的错觉

部署M-S-S模型
环境：
testd63 master mysql5.5.32 192.168.1.63
test64 slave中继 mysql5.5.32 192.168.1.64
test62 slave mysql5.5.32 192.168.1.62
部署master—->testd63:

受权用户：mysql> grant replication slave on *.* to 'repl'@192.168.1.64 identified by '123456';
[root@testd63 ~]# vim /etc/my.cnf #修改配置

binlog_format=row
service mysqld restart
导出Master完整备份，  mysqldump  .sql
[root@testd63 ~]# innobackupex --user=system --password=123456 /opt/backup
[root@testd63 ~]# innobackupex --user-memory=500M --apply-log /opt/backup/2015-11-12_15-10-50/
将备份拷贝至slave中继，和slave
[root@testd63 ~]# scp -r /opt/backup/ 192.168.1.64:/opt/
[root@testd63 ~]# scp -r /opt/backup/ 192.168.1.62:/opt/

部署slave中继------> test64
修改主配置文件也要开启bin-log：
log-bin=mysql-bin-slave1
log-slave-updates=1   把它从relay-log当中读取出来的二进制日志而且这本机上执行的操做也记录这本身的二进制日志里面，这样才能使第三台slave经过中继slave读取到相应数据变化
binlog_format=row
server-id       = 2
拷贝master导出的数据到data下：
[root@test64 ~]# service mysqld stop
[root@test64 ~]# rm -rf /usr/local/mysql/data/*
[root@test64 ~]# mv /opt/backup/2015-11-12_15-10-50/* /usr/local/mysql/data/
[root@test64 ~]# chown -R mysql:mysql /usr/local/mysql/data/

受权：
mysql> change master to master_host='192.168.1.63',master_user='repl',master_password='123456';
master_log_file='mysqllog.000004' ,master_log_pos=106;
mysql> start slave;

再受权一个用户给slave:
mysql> grant replication slave on *.* to 'repl'@192.168.1.62 identified by '123456';
部署slave------>test62
导入数据：
[root@test62 ~]# service mysqld stop
[root@test62 ~]# rm -rf /usr/local/mysql/data/*
[root@test62 ~]# rm -rf /usr/local/mysql/data/*
[root@test62 ~]# mv /opt/backup/2015-11-12_15-10-50/* /usr/local/mysql/data/
[root@test62 ~]# chown -R mysql:mysql /usr/local/mysql/data/

修改配置：
log-bin=mysql-bini-slave2
binlog_format=row
server-id       = 3
[root@test62 data]# service mysqld restart 重启报错

[root@test62 data]# rm ibdata1 ib_logfile0 ib_logfile1
rm: remove regular file `ibdata1'? y rm: remove regular file `ib_logfile0'? y rm: remove regular file `ib_logfile1'? y

[root@test62 data]# kill -9 9021
[root@test62 data]# kill -9 9312
[root@test62 data]# service mysqld restart

指定slave中继做为主：
mysql> change master to master_host='192.168.1.64',master_user='repl',master_password='123456';
master_log_file='mysqllog.000004' ,master_log_pos=106;
mysql> start slave;

从master上插入数据测试：
mysql> create table mermber(id int(4) unsigned not null auto_increment,name varchar(20),primary key(id));
mysql> insert into mermber values(1,'天屠'),(2,'孤叶');
而后分别在slave中继，与slave上查看

排错：
错误1：
此方法可能失效，自行验证
mysql> show slave status\G

由结果能够看到：
Read_Master_Log_Pos: 288
Exec_Master_Log_Pos: 107
Last_SQL_Errno: 1146
Last_SQL_Error: Error executing row event: 'Table 'HA.student' doesn't exist' 由于只对HA记录了binlog,当在mydb库操做其它数据库的表，但该表在slave上又不存在时就出错了。 到master上查看事件记录 mysql> show binlog events in 'mysql-bin-master.000002' from 107\G 由上面的结果可知，咱们须要跳过两个事务173，288 而后到salve中继操做： mysql> slave stop; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; 跳过一个事务 mysql> slave start; 这个搞定 或者： 它提供了一个session粒度的选项，经过关闭这个选项能够不让主库将打开这个选项或关闭链接前的SQL语句写入binlog。 set sql_log_bin=off; mysql>alter table aaa add column xxx int default 1 after yyy;

模拟故障：

因为历史遗留问题，MySQL主从库的表结构不一致，主库的某个表tableA比从库表tableA少了一个字段
当尝试在主库上更改表结构时，这行alter语句会随着binlog同步到从库，若是从库执行这行语句时出错，主从同步线程就会自动中止，那样只能人为手动处理错误，而后再启动slave上的主从同步线程。场景大概是下面这个样子：
先在从库添加这个字段：
mysql> alter table student add age int default 0 after name;
再在主库添加这个字段：
mysql> alter table student add age int default 0 after name;修改主库上的表结构，添加一个字段
从库会同步主库的，可是从库已经纯在了这个字段
查看slave状态

解决方法1：
跳过错误的事物
从库上执行：

mysql> stop slave;
set global sql_slave_skip_counter=1;
mysql> start slave;

不少slave数据库的时候这样改太麻烦了
解决方法2:
slave比较少的时候还能够，可是当从库有几十台时，逐台去处理既费时又容易出错，怎样在主库这一侧一劳永逸地避免呢？
那很简单，咱们不要让主库将alter语句记录到binlog中就行
咱们直接这主库中关闭binlog记录

mysql> set sql_log_bin=off;
// 而后咱们再执行alter语句
mysql> alter table student add age int default 0 after name;
// 再开启bin-log
mysql> set sql_log_bin=on;

错误2：

# binlog主从不一致

这种要求对齐binlog
先到做为它的主上查看binlog

//Slave上不对应
//Slave上操做：
mysql> stop slave;
mysql> change master to master_host='192.168.1.64',master_user='repl',master_password='123456',master_log_file='mysql-bin-slave1.000002',master_log_pos=415;
mysql> start slave;

Ok,恢复正常