MySql主从配置

MySQL Replication

Replication可以实现将数据从一台数据库服务器（master）复制到一或多台数据库服务器（slave）
默认情况下属于异步复制，无需维持长连接 通过配置，可以复制所有的库或者几个库，甚至库中的一些表 是MySQL内建的，本身自带的

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

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

---

Replication如何工作
整体上来说，复制有3个步骤：

(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中的数据一致。

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

一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中
在Master上<多台同步影响IO>
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,如果大并发的情况那延迟更严重
Mysql在5.6已经自身可以实现fail over
5、One slave Muti master 一从对多主
好处：节省成本，将多个master数据自动化整合
缺陷：对库和表数据的修改较多

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

test63  192.168.1.63    Rhel6.5/5.5.32  Mastertest64  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 :888COMMAND  PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAMEnc      3464 root    3u  IPv4  18662      0t0  TCP *:cddbp (LISTEN)开始接收：记得清空两台服务器的iptables,要不然端口不能通信iptables –F/etc/init.d/iptables savenc 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 stopvim /etc/my.cnfserver-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: YesSlave_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.63mysql主：服务端：test64   IP：192.168.1.64  在上面主从的基础上进行配置// 配置test64它有两种身份：身份1： test64的主。 身份2： testd63的从。vim /etc/my.cnflog-bin=mysql-bin-slavebinlog-do-db=HAbinlog-ignore-db=mysql  #避免同步mysql用户 相关配置service mysqld restart

mysql> grant replication slave on *.* to slave64@'192.168.1.63' identified by '123456';配置testd63vim /etc/my.cnf[root@testd63 ~]# mysql  -usystem -p123456mysql> 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=rowservice 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-slave1log-slave-updates=1   把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台slave通过中继slave读取到相应数据变化binlog_format=rowserver-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-slave2binlog_format=rowserver-id       = 3[root@test62 data]# service mysqld restart  重启报错[root@test62 data]# rm ibdata1 ib_logfile0 ib_logfile1rm: remove regular file `ibdata1'? yrm: remove regular file `ib_logfile0'? yrm: 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: 288Exec_Master_Log_Pos: 107Last_SQL_Errno: 1146Last_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-logmysql> 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,恢复正常