Mysql 主从复制实战(学习笔记十三)-低调大师

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看当前复制状态：

② 启动从服务器复制线程，让slave连接master，并开始重做master二进制日志中的事件。

MariaDB [(none)]> change master to master_host='192.168.30.107',

-> master_user='slave',

-> master_password='along',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=245;

MariaDB [(none)]> start slave; # 启动复制线程，就是打开I/O线程和SQL线程；实现拉主的bin-log到从的relay-log上；再从relay-log写到数据库内存里

③ 查看从服务器状态

假如master 已经运行很久了，想对新安装的slave 进行数据同步，甚至它没有master 的数据。

4、测试

① 在主上创建一个along库

② 从上自动生成along数据库

5、若要继续添加新salve，实现一主多从

（1）在主master-mysql 上

① 进行完全备份

mysqldump --all-databases > /backup/mysql-all-backup-`date+%F-%T`.sql

把备份生成的文件发给salve-mysql2机器上scp/backup/mysql-all-backup-2017-11-20-22\:04\:06.sql @192.168.30.2:

② 查看现在的二进制文件状态

MariaDB [(none)]> show master status;

（2）在从slave-mysql2上

① vim /etc/my.cnf 修改主配置文件，设为从

② 进行master的完全备份恢复

mysql -uroot -p < mysql-all-backup-2017-11-20-22\:04\:06.sql

① MariaDB [(none)]> stop slave; 关闭两个线程

恢复完后，数据直接与主完全一致

③ 启动从服务器复制线程

MariaDB [(none)]> change master to master_host='192.168.30.107',

-> master_user='slave',

-> master_password='along',

-> master_log_file='mysql-bin.000003',

-> master_log_pos=500;

7、解除主从关系，恢复独立的服务器

② vim /etc/my.cnf 删除3行

relay-log =mysql-relay-log

read-only =1log_slave_updates =1

③ systemctl restart mariadb 重启服务

主从+复制过滤

1、环境准备

机器名称IP配置服务角色备注

master-mysql192.168.30.107主数据库二进制日志

slave-mysql1192.168.30.7从数据库中继日志

slave-mysql2192.168.30.2从数据库中继日志

2、在主master 主服务器上

① vim /etc/my.cnf 修改mysql主配置文件，对master进行配置，打开二进制日志，指定唯一的servr ID，设置复制过滤

server-id=1#配置server-id，让主服务器有唯一ID号

log-bin=mysql-bin #打开Mysql日志，日志格式为二进制

skip-name-resolve #关闭名称解析，（非必须）

binlog_ignore_db=home #除了home数据库，其他都允许从复制主的二进制文件

#binlog_do_db=along #仅允许从复制along数据库的二进制文件

② 创建并授权slave mysql 用的复制帐号

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%'IDENTIFIED BY'along';

分析：在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。

③ 查看主服务器状态

在Master的数据库执行show master status，查看主服务器二进制日志状态，位置号

3、在从slave mysql1上

① 修改主配置文件

vim /etc/my.cnf 打开中继日志，指定唯一的servr ID，设置只读权限

server-id=2#配置server-id，让从服务器有唯一ID号

relay_log = mysql-relay-bin #打开Mysql日志，日志格式为二进制

read_only =1 #设置只读权限

log_bin = mysql-bin #开启从服务器二进制日志，（必须）

log_slave_updates =1#使得更新的数据写进二进制日志中

② 启动从服务器复制线程，让slave连接master，并开始重做master二进制日志中的事件。

MariaDB [(none)]> change master to master_host='192.168.30.107',

-> master_user='slave',

-> master_password='along',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=245;

MariaDB [(none)]> start slave; # 启动复制线程，就是打开I/O线程和SQL线程；实现拉主的bin-log到从的relay-log上；再从relay-log写到数据库内存里

③ 查看从服务器状态

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看当前复制状态：

在主上创建一个along、home库；从上自动生成along、home数据库

4、测试主从和复制过滤

（1）测试主从关系

（2）测试复制过滤

① 在主上：在along库中创建一个classes的表；从上自动生成

MariaDB [home]> create table classes (id int not null,name varchar(20));

② 在主上：在home库中创建一个classes的表；从上没有生成

MariaDB [home]> create table classes (id int not null,name varchar(20));

slave-mysql1 上，过滤成功

5、设置slave-mysql2 为slave-mysql1 的从，且在mysql2 设置复制过滤

（1）在slave-mysql1上，不用怎么设置

因为上边主配置文件已经开启了自己的二进制文件；且slave-mysql1 是从开始就同步master的，所以授权命令也同步过了

MariaDB [home]> select user,host from mysql.user; 可以查看自己授权过的用户

（2）slave-mysql2 上，可以像上实验一样，先给主的完全备份在本机恢复一下

① 在主上完备

mysqldump --all-databases > /backup/mysql-all-backup-`date+%F-%T`.sqlscp/backup/mysql-all-backup-2017-11-21-11:14:59.sql @192.168.30.2:

② 进行master的完全备份恢复

mysql -uroot -p < mysql-all-backup-2017-11-20-22\:04\:06.sql

③ 在slave-mysql2 上

vim /etc/my.cnf 修改主配置文件，设为从；且设置过滤

server-id=3relay-log =mysql-relay-log

read-only =1log-bin = mysql-bin

log_slave_updates =1replicate_do_dB=along #只复制它的主的along数据库

启动从服务器复制线程，让slave连接master，并开始重做master二进制日志中的事件。

④ mysql 打开数据库，查看数据恢复成功；

MariaDB [(none)]> change master to master_host='192.168.30.107',

-> master_user='slave',

-> master_password='along',

-> master_log_file='mysql-bin.000008',

-> master_log_pos=773;

MariaDB [(none)]> start slave;

⑤ MariaDB [(none)]> show slave status \G; 查看，两个进程打开，且只复制主的along数据库

6、测试主从从和slave-mysql2的复制过滤

（1）在主上删除job数据库，master 和slave-mysql1 都删除成功

MariaDB [home]> drop database job; 删除job库

因为slave-mysql2 只同步slave-mysql1 的along库，所以没有删除

（2）在主上的along数据库，创建一个grade 表，master 和slave-mysql1 都删除成功

MariaDB [along]> create table grade (id int not null,name varchar(20));

在slave-mysql2 上也自动生成成功

双主

应用：只适合小型公司，小并发访问量，毕竟同时写入易出错

1、环境准备

机器名称IP配置服务角色备注

mysql1192.168.30.107数据库中继日志、二进制日志

mysql2192.168.30.7数据库中继日志、二进制日志

2、配置总配置文件，除了ID号和起始数，两边都是一样的

vim /etc/my/cnf

server-id=1 #mysql1的配置ID为1，mysql2的ID为2

relay-log =mysql-relay-log

log-bin = mysql-bin

log_slave_updates =1auto_increment_increment=2 #表示自增长字段每次递增的量，步长

auto_increment_offset=1#表示自增长字段从那个数开始，mysql1从1开始；mysql2从2开始

systemctl start mariadb

3、互相设为对方的从

（1）授权远程登录的用户

mysql1、2 上

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%'IDENTIFIED BY'along';

（2）开启复制线程

① mysql1 上

MariaDB [(none)]> change master to master_host='192.168.30.7',

-> master_user='slave',

-> master_password='along',

-> master_log_file='mysql-bin.000002',

-> master_log_pos=245;

MariaDB [(none)]> start slave; # 启动复制线程

② mysql2 上

MariaDB [(none)]> change master to master_host='192.168.30.107',

-> master_user='slave',

-> master_password='along',

-> master_log_file='mysql-bin.000002',

-> master_log_pos=245;

MariaDB [(none)]> start slave; # 启动复制线程

4、测试双方互为主从的关系

在mysql1上，删除test数据库；mysql2 上也自动删除

在mysql2上，创建xiaohei数据库；mysql2 上也自动生成

5、设置自增长ID的表

MariaDB [along]> create table home (id int PRIMARY KEY AUTO_INCREMENT,name varchar(20));

① 在mysql1上向表中插入数据

MariaDB [along]> insert into home(name) value('mayun'),('mahuateng'),('wangjianlin');

② 在mysql2上向表中插入数据

MariaDB [along]> insert into home(name) value('dinglei'),('liyanhong'),('leijun');

场景

公司规模已经形成，用户数据已成为公司的核心命脉，一次老王一不小心把数据库文件删除，通过mysqldump备份策略恢复用了两个小时，在这两小时中，公司业务中断，损失100万，老王做出深刻反省，公司也因此对于数据库的性能和可靠性提出更高要求。要求对数据库进行改造，使其承载力进行提升，故障修复时间减少，有没有能实现的方案呢？

数据库常遇到的问题

一、性能问题

1、向上拓展 scale up：针对单台服务器，提高服务器的硬件性能，比如：内存，cpu等，个体本身容易达到极限

2、向外拓展 scale out：多台服务器形成集群，共同完成一件事情

高可用架构对于互联网服务基本是标配，无论是应用服务还是数据库服务都需要做到高可用。虽然互联网服务号称7*24小时不间断服务，但多多少少有一些时候服务不可用，比如某些时候网页打不开，百度不能搜索或者无法发微博，发微信等。一般而言，衡量高可用做到什么程度可以通过一年内服务不可用时间作为参考，要做到3个9的可用性，一年内只能累计有8个小时不可服务，而如果要做到5个9的可用性，则一年内只能累计5分钟服务中断。所以虽说每个公司都说自己的服务是7*24不间断的，但实际上能做到5个9的屈指可数，甚至根本做不到，国内互联网巨头BAT(百度，阿里巴巴，腾讯)都有因为故障导致的停服问题。对于一个系统而言，可能包含很多模块，比如前端应用，缓存，数据库，搜索，消息队列等，每个模块都需要做到高可用，才能保证整个系统的高可用。对于数据库服务而言，高可用可能更复杂，对用户的服务可用，不仅仅是能访问，还需要有正确性保证，因此，对于实现数据库高可用，对互联网公司来说极其重要！

企业级数据库高可用架构图

Mysql主从架构技术说明

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

(1) 数据分布 (Data distribution )

(2) 负载平衡(load balancing)

(3) 备份(Backups)

(4) 高可用性和容错性 High availabilityand failover

Mysql复制如何工作

Mysql的复制功能主要有3个步骤：

(1) 主服务器（master）将改变记录到二进制日志(binarylog)中（这些记录叫做二进制日志事件，binary log events）

(2) 从服务器（slave）将主服务器master的binary logevents拷贝到它的中继日志(relay log)

(3) slave重做中继日志中的事件，将改变反映它自己的数据。

1、该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日志，在事件写入二进制日志完成后，master通知存储引擎提交事务。此后可接收slave的请求

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中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

I/O线程：将master数据库二进制日志拉到slave数据库上，并将二进制日志写到中继日志，写完之后，他会睡眠并等待master数据库二进制日志更新，一旦更新，就会写入slave数据库的中继日志中

SQL线程：读取中继日志的事件，并在数据库中执行，写入到内存中，使slave数据库的数据与master数据库中的数据一致

Mysql实现企业级数据库主从复制架构实战

注意：slave数据库只能是可读的，不能是可写的，如果改变了slave数据库的数据，master不能从slave数据库上同步数据，导致主从数据库数据不一致。

实战演练

一、环境准备

centos系统服务器2台、一台用户做Mysql主服务器，一台用于做Mysql从服务器，都在同一个网段中，配置好yum源、防火墙关闭、各节点时钟服务同步、各节点之间可以通过主机名互相通信

二、准备步骤：

1、iptables -F && setenforce 清空防火墙策略，关闭selinux

2、拿两台服务器都使用yum方式安装Mysql服务，要求版本一致

3、分别启动两台服务器mysql服务，确保服务正常

三、实现步骤:

1、配置master主服务器

对master进行配置，包括打开二进制日志，指定唯一的servr ID。例如，在配置文件加入如下值

vim /etc/my.cnf

server-id=1#配置server-id，让主服务器有唯一ID号（让从服务器知道他的主服务器是谁）

log-bin=mysql-bin#打开Mysql日志，日志格式为二进制

skip-name-resolve#关闭名称解析，（非必须）

然后重启数据库服务

systemctl restart mariadb

2．创建复制帐号

在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master

。进行复制操作的用户会授予REPLICATION SLAVE权限。（给从服务器授权，让他能从主服务器拷贝二进制日志）

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.10.%' IDENTIFIED BY 'magedu';

3．查看主服务器状态

在Master的数据库执行show master status，查看主服务器二进制日志状态

4、配置slave从服务器

对slave进行配置，打开中继日志，指定唯一的servr ID，设置只读权限。在配置文件加入如下值

vim /etc/my.cnf

server-id=2#配置server-id，让从服务器有唯一ID号

relay_log = mysql-relay-bin#打开Mysql日志，日志格式为二进制

read_only = 1#设置只读权限

log_bin = mysql-bin#开启从服务器二进制日志

log_slave_updates = 1#使得更新的数据写进二进制日志中

然后重启数据库服务

systemctl restart mariadb

5．启动从服务器复制线程

让slave连接master，并开始重做master二进制日志中的事件。

CHANGE MASTER TO MASTER_HOST='192.168.10.190',

MASTER_USER='slave',

MASTER_PASSWORD='magedu',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=278;

执行start slave；# 启动复制线程。

6、查看从服务器状态

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看前复制状态：

SHOW SLAVE STATUS\G

理想的结果是在主服务器上添加的数据，在从服务器上也会同步

7.测试

在主服务器上

在从服务器上

四、添加新slave服务器

假如master已经运行很久了，想对新安装的slave进行数据同步，甚至它没有master的数据。

此时，有几种方法可以使slave从另一个服务开始，例如，从master拷贝数据，从另一个slave克隆，从最近的备份开始一个slave。为了加快Slave与master同步，可用以下方式先进行数据同步：

也可以搭建主从从架构，让从服务器之间进行复制

就是在从服务器也开启二进制日志，然后从的从I/O线程再将从的二进制日志给拷贝过来写入到自己的relay log中，然后sql线程再读取relay log中的事件，在数据库中执行，写入到内存中。

Mysql复制过滤器

复制过滤器：

仅复制有限一个或几个数据库相关的数据，而非所有；由复制过滤器进行；

有两种实现思路：

(1) 主服务器

主服务器仅向二进制日志中记录有关特定数据库相关的写操作；

binlog_do_db=

binlog_ignore_db=

(2) 从服务器

从服务器的SQL THREAD仅重放关注的数据库或表相关的事件，并将其应用于本地；

在实际应用场景中，MySQL复制90%以上都是一个Master复制到一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只要Master和Slave的压力不是太大（尤其是Slave端压力）的话，异步复制的延时一般都很少很少。尤其是自从Slave端的复制方式改成两个线程处理之后，更是减小了Slave端的延时问题。而带来的效益是，对于数据实时性要求不是特别高的应用，只需要通过廉价的pcserver来扩展Slave的数量，将读压力分散到多台Slave的机器上面，即可通过分散单台数据库服务器的读压力来解决数据库端的读性能瓶颈，毕竟在大多数数据库应用系统中的读压力还是要比写压力大很多。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈。

单一master和多slave架构图

(1) 不同的slave扮演不同的作用(例如使用不同的索引，或者不同的存储引擎)；

(2) 用一个slave作为备用master，只进行复制；#主服务器挂了之后，可在从服务器执行

1> 在备机上执行STOP SLAVE 和RESET MASTER

2> 查看show slave status \G;

3> 然后修改应用的连接地址。

(3) 用一个远程的slave，用于灾难恢复；

二、互为主从Master-Master(Master-Master in Active-Active Mode)

Master-Master复制的两台服务器，既是master，又是另一台服务器的slave。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

即：在两台服务器上既执行master的操作又执行slave的操作（注意：两台数据库都必须是可写的）

互为主从复制过程

互为主从：两个节点各自都要开启binlog和relay log；

1、数据不一致；

2、自动增长id；

什么是自增长ID？

对于某些唯一性的字段，可以通过设置自增长ID来实现,自增长ID的数据，代表这个表中存在一条唯一的记录；而自增长id是肯定不会重复的；

创建表，设置ID为自增长

create table userInfo (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL)；

两边插入数据看数据增长

insert into userInfo(name) value('xiao'),('da'),('lao');

定义一个节点使用奇数id

auto_increment_increment=2#表示自增长字段每次递增的量

auto_increment_offset=1#表示自增长字段从那个数开始

另一个节点使用偶数id

auto_increment_increment=2

auto_increment_offset=2

配置：

1、server_id必须要使用不同值；

2、均启用binlog和relay log； read only = 0(因为互为主从，所以必须是可写的)

3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；

relay_log = relay-log

skip-name-resolve = on

log_slave_updates = 1

auto_increment_increment=2

auto_increment_offset=1

另一台数据库服务器上

vim /etc/my.cnf

server-id = 2

relay_log = relay-log

log_bin = mysql-log

skip-name-resolve = on

log_slave_updates = 1

auto_increment_increment=2

auto_increment_offset=2

修改完配置文件后，重启数据库服务

systemctl restart mariadb

2．创建复制帐号

分别在两台数据库服务器上创建复制账号

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.10.%' IDENTIFIED BY 'magedu';

3．启动从服务器复制线程

让slave连接master，并开始重做master二进制日志中的事件。

CHANGE MASTER TO MASTER_HOST='192.168.10.190',

MASTER_USER='slave',

MASTER_PASSWORD='magedu',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=278;

执行start slave；# 启动复制线程。

另一台数据库服务器也是如此

4、查看从服务器状态

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看前复制态：

SHOW SLAVE STATUS\G