主从复制php技巧_Mysql高可用主从复制

许可将来自一个MySQL数据库做事器（主理事器）的数据复制到一个或多个MySQL数据库做事器（从做事器）。

根据从数据库做事器配置，

您可以复制主理事器数据库中的所有数据库，

也可以选择数据库乃至选定的表。

MySQL主从复制的优点

Replication 的事理

条件是作为主理事器角色的数据库做事器必须开启二进制日志

主理事器上面的任何修正都会通过 I/O tread(I/O 线程)保存在二进制日志Binary log里面。
从做事器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主理事器上面要求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。
从做事器上面同时开启一个 SQL thread 定时检讨Realy log(这个文件也是二进制的)，如果创造有更新立即把更新的内容在本机的数据库上面实行一遍。

每个从做事器都会收到主理事器二进制日志的全部内容的副本。

从做事器卖力决定该当实行二进制日志中的哪些语句。

除非另行指定，否则主从二进制日志中的所有事宜都在从站上实行。

如果须要，您可以将从做事器配置为仅处理一些特天命据库或表的事宜。

主要: 您无法将主理事器配置为仅记录特定事宜。

每个从站(从做事器)都会记录二进制日志坐标：

文件名文件中它已经从主站读取和处理的位置。

由于每个从做事器都分别记录了自己当前处理二进制日志中的位置，

因此可以断开从做事器的连接，

重新连接然后规复连续处理。

一主多从

如果一主多从的话，

这时主库既要卖力写又要卖力为几个从库供应二进制日志。

此时可以稍做调度，将二进制日志只给某一从，

这一从再开启二进制日志并将自己的二进制日志再发给其它从。

或者是干脆这个从不记录只卖力将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时该当也轻微要好一些。

事情事理图如下：

主从复制配置

配置步骤：

在主理事器上，必须启用二进制日志记录并配置唯一的做事器ID。
须要重启做事器。

编辑主理事器的配置文件my.cnf，添加如下内容

[mysqld]log-bin=/var/log/mysql/mysql-binserver-id=1

创建日志目录并授予权限

shell> mkdir /var/log/mysqlshell> chown mysql.mysql /var/log/mysql

重启做事

shell> systemctl restart mysqld

把稳：

如果省略server-id（或将其显式设置为默认值0），

则主理事器谢绝来自从做事器的任何连接。

为了在利用带事务的InnoDB进行复制设置时尽可能提高持久性和同等性，您该当在master my.cnf文件中利用以下配置项：

innodb_flush_log_at_trx_commit = 1sync_binlog = 1

确保在主理事器上skip_networking选项处于OFF关闭状态, 这是默认值。

如果是启用的，则从站无法与主站通信，并且复制失落败。

mysql> show variables like '%skip_networking%';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| skip_networking | OFF |+-----------------+-------+1 row in set (0.00 sec)该当创建一个专门用于复制数据的用户

每个从做事器须要利用MySQL 主理事器上的用户名和密码连接到主站。

例如，操持利用用户repl可以从任何主机上连接到master上进行复制操作,

并且用户repl仅可以利用复制的权限。

在主理事器上实行如下操作

mysql> CREATE USER 'repl'@'%' mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'%' identified by 'QFedu123!';在从做事器上利用刚才的用户进行测试连接

shell> mysql -urepl -p'QFedu123!' -hmysql-master1

下面的操作根据如下情形连续

主理事器中有数据

在主理事器中导涌现有的数据

如果主数据库包含现有数据，则必须将此数据复制到每个从站。
有多种方法可以实现:

利用[mysqldump]工具创建要复制的所有数据库的转储。
这是推举的方法，尤其是在利用时[InnoDB]

shell> mysqldump -u用户名 -p密码 --all-databases --master-data=1 > dbdump.db

这里的用户是主理事器的用户

如果不该用--master-data参数，则须要手动锁定单独会话中的所有表。

从主理事器中利用scp或rsync等工具，把备份出来的数据传输到从做事器中。

在主理事中实行如下命令

scp dbdump.db root@mysql-slave1:/root/

这里的mysql-slave1须要能被主理事器解析出 IP 地址，或者说可以在主理事器中ping通。

配置从做事器，并重启在从做事器上编辑其配置文件my.cnf并添加如下内容：

// my.cnf 文件[mysqld]server-id=2导入数据到从做事器，并配置连接到主理事器的干系信息

登录到从做事器上，实行如下操作

/导入数据/mysql> source /root/fulldb.dump

在从做事器配置连接到主理事器的干系信息

mysql> CHANGE MASTER TOMASTER_HOST='mysql-master1', -- 主理事器的主机名(也可以是 IP) MASTER_USER='repl', -- 连接到主理事器的用户MASTER_PASSWORD='QFedu123!'; == 到主理事器的密码启动从做事器的复制线程

mysql> start slave;Query OK, 0 rows affected (0.09 sec)

检讨是否成功

在从做事上实行如下操作，查看从做事器端 IO线程和 SQL 线程是否是OK

mysql> show slave status\G

输出结果中该当看到 I/O 线程和 SQL 线程都是YES, 就表示成功。

实行此过程后，在主理事上操作的修正数据的操作都会在从做事器中实行一遍，这样就担保了数据的同等性。

将新的做事器加入，变为从做事器

和上面的步骤一样，但是新加入的做事器的server-id的值不能和现有都做事器server-id的值一样。

主理事器中无数据

主理事器中设置

my.cnf配置文件

[mysqld]log-bin=/var/log/mysql/mysql-binserver-id=1

设置log-bin时必须同时设置server-id

创建日志目录并授予权限

shell> mkdir /var/log/mysqlshell> chown mysql.mysql /var/log/mysql

重启做事

从做事器设置

my.cnf配置文件

[mysqld]server-id=3

重启做事

查看主理事器的二进制日志的名称

通过利用命令行客户端连接到主理事器来启动主理事器上的会话，并通过实行以下[FLUSH TABLES WITH READ LOCK]语句来刷新所有表和阻挡写语句：

mysql> FLUSH TABLES WITH READ LOCK;

mysql> show master status \G 1. row File: mysql-bin.000001 Position: 0 Binlog_Do_DB: Binlog_Ignore_DB:Executed_Gtid_Set:1 row in set (0.00 sec)在从做事器的 mysql 中实行如下语句

mysql> CHANGE MASTER TOMASTER_HOST='mysql-master1',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;mysql> start slave;

查看

在master上实行show binlog events命令，可以看到第一个binlog文件的内容。

mysql> show binlog events\G 1. row Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_desc Server_id: 1End_log_pos: 107 Info: Server ver: 5.5.28-0ubuntu0.12.10.2-log, Binlog ver: 4 2. row Log_name: mysql-bin.000001 Pos: 107 Event_type: Query Server_id: 1End_log_pos: 181 Info: create user rep 3. row Log_name: mysql-bin.000001 Pos: 181 Event_type: Query Server_id: 1End_log_pos: 316 Info: grant replication slave on . to rep identified by '123456'3 rows in set (0.00 sec)Log_name 是二进制日志文件的名称，一个事宜不能横跨两个文件Pos 这是该事宜在文件中的开始位置Event_type 事宜的类型，事宜类型是给slave通报信息的基本方法，每个新的binlog都以Format_desc类型开始，以Rotate类型结束Server_id 创建该事宜的做事器idEnd_log_pos 该事宜的结束位置，也是下一个事宜的开始位置，因此事宜范围为Pos~End_log_pos - 1Info 事宜信息的可读文本，不同的事宜有不同的信息从站停息复制

您可以利用[STOP SLAVE和[START SLAVE]语句停滞并启动从站上的复制 。

要停滞从主理事器处理二进制日志，请利用[STOP SLAVE]

mysql> STOP SLAVE;

当复制停滞时，从I / O线程停滞从主二进制日志读取事宜并将它们写入中继日志，并且SQL线程停滞从中继日志读取事宜并实行它们。

您可以通过指定线程类型单独停息I / O或SQL线程：

mysql> STOP SLAVE IO_THREAD;mysql> STOP SLAVE SQL_THREAD;

要再次开始实行，请利用以下[START SLAVE]语句：

mysql> START SLAVE;

要启动特定线程，请指定线程类型：

mysql> START SLAVE IO_THREAD;mysql> START SLAVE SQL_THREAD;

在主理事器上，输出SHOW PROCESSLIST如下所示：

mysql> SHOW PROCESSLIST\G 1\. row Id: 2 User: root Host: localhost:32931 db: NULLCommand: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL

这里，线程2是Binlog Dump为连接的从属做事的复制线程。

该State信息表明所有未完成的更新已发送到从站，并且主站正在等待更多更新发生。

如果Binlog Dump在主理事器上看不到任何 线程，则表示复制未运行; 也便是说，目前没有连接任何从站。

在从属做事器上，输出SHOW PROCESSLIST如下所示：

mysql> SHOW PROCESSLIST\G 1\. row Id: 10 User: system user Host: db: NULLCommand: Connect Time: 11 State: Waiting for master to send event Info: NULL 2\. row Id: 11 User: system user Host: db: NULLCommand: Connect Time: 11 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL

该State信息指示线程10是与主理事器通信的I / O线程，并且线程11是处理存储在中继日志中的更新的SQL线程。
两个线程都处于空闲状态，等待进一步更新。

基于事务的主从复制

便是利用 GTID 来实现的复制

GTID（全局事务标示符）最初由google实现，在MySQL 5.6中引入.GTID在事务提交时天生，由UUID和事务ID组成.uuid会在第一次启动MySQL时天生，保存在数据目录下的auto .CNF文件里，事务ID则从1开始自增利用GTID的好处紧张有两点：

不再须要指定传统复制中的master_log_files和master_log_pos，使主从复制更大略可靠可以实现基于库的多线程复制，减小主从复制的延迟实验环境哀求： 5.7.6 以上版本主库配置

[mysqld]log-bin=/var/log/mysql/mysql-binserver-id=1gtid_mode=ONenforce_gtid_consistency=1 # 逼迫实行GTID同等性。


重启做事

其他和之前的一样

创建专属用户并授权假如有数据导出数据

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123';mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'%';mysql>

从库配置

测试用户有效性

shell> mysql -urepl -p'123' -hmysql-master1

[mysqld]server-id=2gtid_mode=ONenforce_gtid_consistency=1# 可选项, 把连接到 master 的信息存到数据库中的表中master-info-repository=TABLErelay-log-info-repository=TABLE

重启做事

假如有数据，先导入数据

mysql> source dump.db

Mysql 终端实行连接信息

mysql> CHANGE MASTER TOMASTER_HOST='172.16.153.10',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_AUTO_POSITION=1;> start slave;

检讨 slave 状态

mysql> show slave status\G

设置 从做事器只读状态

查看当前只读的状态SHOW VARIABLES LIKE '%read_only%';

设置普通用户只读SET GLOBAL read_only=1;

设置超级用户只读SET GLOBAL super_read_only=1;

mysql> SHOW VARIABLES LIKE '%read_only%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_read_only | OFF || read_only | OFF || super_read_only | OFF || transaction_read_only | OFF || tx_read_only | OFF |+-----------------------+-------+5 rows in set (0.01 sec)mysql> SET GLOBAL read_only=1;Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE '%read_only%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_read_only | OFF || read_only | ON || super_read_only | OFF || transaction_read_only | OFF || tx_read_only | OFF |+-----------------------+-------+5 rows in set (0.00 sec)mysql> SET GLOBAL super_read_only=1;Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE '%read_only%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_read_only | OFF || read_only | ON || super_read_only | ON || transaction_read_only | OFF || tx_read_only | OFF |+-----------------------+-------+5 rows in set (0.00 sec)开启 GTID 后的导出导入数据的把稳点

当前数据库实例中开启了 GTID 功能,

在开启有 GTID 功能的数据库实例中,

导出个中任何一个库,

如果没有显示的指定--set-gtid-purged参数, 都会提示这一行信息.

意思是默认情形下, 导出的库中含有 GTID 信息,

如果不想导出包含有 GTID 信息的数据库, 须要显示地添加--set-gtid-purged=OFF参数.

mysqldump -uroot -p --set-gtid-purged=OFF --all-databases > alldb.db

导入数据是就可以像往常一样导入了。

多线程复制配置

多线程复制在5.6中被引入，并且在5.7中得到了进一步的完善。

5.7中是基于逻辑时钟的办法进行的多线程复制。

配置过程：先在从库上查看默认的多线程复制类型

mysql> show variables like "slave_parallel_type";+---------------------+----------+| Variable_name | Value |+---------------------+----------+| slave_parallel_type | DATABASE |+---------------------+----------+1 row in set (0.01 sec)接着在从库上停滞目前正在运行复制链路

停滞之前可以查看目前的线程数

show processlist;

mysql> stop slave配置并发线程的办法

mysql> set global slave_parallel_type = "logical_clock";Query OK, 0 rows affected (0.00 sec)配置并发数量

mysql> set global slave_parallel_workers = 4;Query OK, 0 rows affected (0.00 sec)mysql> show variables like "slave_parallel_workers";+------------------------+-------+| Variable_name | Value |+------------------------+-------+| slave_parallel_workers | 4 |+------------------------+-------+1 row in set (0.00 sec)启动从做事器的复制链路

mysql> start slave主从复制架构（主主，主备，主备+多从）主主复制

上图中，Master-Master复制的两台做事器，既是master，又是另一台做事器的slave。

这样，任何一方所做的变更，都会通过复制运用到其余一方的数据库中。

在这种复制架构中，各自上运行的不是同一db，比如左边的是db1,右边的是db2，db1的从在右边反之db2的从在左边，两者互为主从。

主动—被动模式的Master-Master(Master-Master in Active-Passive Mode)

上图中，这是由master-master构造变革而来的，

它避免了M-M的缺陷，

实际上，这是一种具有容错和高可用性的系统。

它的不同点在于个中只有一个节点在供应读写做事，

其余一个节点时候准备着，当主节点一旦故障立时接替做事。

带从做事器的Master-Master构造(Master-Master with Slaves)

这种构造的优点便是供应了冗余。

在地理上分布的复制构造，它不存在单一节点故障问题，

而且还可以将读密集型的要求放到slave上。

半同步机制（扩展）

MySQL-5.5 及以上支持半同步复制。

早前的MySQL复制只能是基于异步来实现，从MySQL-5.5开始，支持半自动复制。

在以前的异步（asynchronous）复制中，主库在实行完一些事务后，是不会管备库的进度的。
如果备库处于掉队，而更不幸的是主库此时又涌现Crash（例如宕机），这时备库中的数据便是不完全的。
简而言之，在主库发生故障的时候，我们无法利用备库来连续供应数据同等的做事了。
Semisynchronous Replication(半同步复制)则一定程度上担保提交的事务已经传给了至少一个备库。
Semi synchronous中，仅仅担保事务的已经通报到备库上，但是并不确保已经在备库上实行完成了。

此外，还有一种情形会导致主备数据不一致。
在某个session中，主库上提交一个事务后，会等待事务通报给至少一个备库，如果在这个等待过程中主库Crash，那么也可能备库和主库不一致，这是很致命的。
如果主备网络故障或者备库挂了，主库在事务提交后等待10秒（rpl_semi_sync_master_timeout的默认值）后，就会连续。
这时，主库就会变回原来的异步状态。

MySQL在加载并开启Semi-sync插件后，每一个事务需等待备库吸收日志后才返回给客户端。
如果做的是小事务，两台主机的延迟又较小，则Semi-sync可以实现在性能很小丢失的情形下的零数据丢失。

开启半同步机制

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.08 sec)mysql> show global variables like '%semi%'; +-------------------------------------------+------------+| Variable_name | Value |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled | OFF || rpl_semi_sync_master_timeout | 10000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_for_slave_count | 1 || rpl_semi_sync_master_wait_no_slave | ON || rpl_semi_sync_master_wait_point | AFTER_SYNC |+-------------------------------------------+------------+6 rows in set (0.00 sec)mysql>加密主从复制

主理事器

创建 CA 证书和私钥 公钥

shell> mysql_ssl_rsa_setup

My.cnf 文件配置项

以下的情形是用yum安装 mysql 的情形

[mysqld]ssl-ca=/var/lib/mysql/ca.pemssl-cert=/var/lib/mysql/server-cert.pemssl-key=/var/lib/mysql/server-key.pem

从做事器配置

首先要担保从做事器的 sql 线程和 io 线程处于关闭状态

mysql> stop slave;mysql> stop slave sql_thread;

mysql> CHANGE MASTER TO -> MASTER_HOST='master_hostname', -> MASTER_USER='repl', -> MASTER_PASSWORD='password', -> MASTER_SSL=1, -> MASTER_SSL_CA = 'ca_file_name', -> MASTER_SSL_CAPATH = 'ca_directory_name', -> MASTER_SSL_CERT = 'cert_file_name', -> MASTER_SSL_KEY = 'key_file_name';mysql> START SLAVE;

关于复制用户

全新创建

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password' -> REQUIRE SSL;mysql> GRANT REPLICATION SLAVE ON . -> TO 'repl'@'%.example.com';

给原来的用户添加REQUIRE SSL

mysql> ALTER USER 'repl'@'%.example.com' REQUIRE SSL;二进制日志的自动删除配置

Mysql 终端中设置

不用重启做事

下面的命令是只保留 10 天内的日志，便是10天前的全部删除

mysql> set global expire_logs_days = 10;

当二进制日志的大小达到[max_binlog_size]系统变量的值时，将刷新二进制日志 。

如果对二进制日志的写入导致当前日志文件大小超过此变量的值，则做事器将轮转二进制日志（关闭当前文件并打开下一个文件）。

事务在一个块中写入二进制日志，因此它永久不会在几个二进制日志之间拆分。

配置文件中设置

此方法须要重启做事

[mysqld]expire_logs_days=10

在 MySQL 终端中手动删除

--打消MySQL-bin.010日志mysql> PURGE MASTER LOGS TO 'MySQL-bin.010';--打消2008-06-22 13:00:00前binlog日志mysql> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; --打消3天前binlog日志BEFORE，变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。
mysql> PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 3 DAY); php7进阶到架构师干系阅读

https://www.kancloud.cn/gofor/gofor

末了，欢迎大家留言补充，谈论~~~

标签：MySQL做事