MySQL Replication：
Master/Slave
Master: write/read
Slaves: read
为什么？
冗余：promte（提升为主），异地灾备
人工
工具程序
负载均衡：转移一部分“读”请求；
支援安全的备份操作：
…
主/从架构：
异步复制：master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。
半同步复制：master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MYSQL引入的。
一主多从；
一从一主；
级联复制；分层次,每一级服务器上一级服务器同步数据,并传递给下一级的服务器
循环复制；
双主复制；
一从多主（不建议）：
每个主服务器提供不同的数据库；可以使用多线程复制，但是每个主服务器必须是提供不同的数据库
==========================================================================
主从复制配置：（异步复制）
从服务器需要启动两个重要的线程：
第一个：从主服务器不断同步时间到本地的中继日志文件中 — > IO线程
第二个：从本地中继日志读取时间进行重放(replay) — > SQL线程
单主模型：
配置前准备：
时间同步：可以编辑 /etc/chrony.conf 文件添加合适的服务器时间源，然后重启服务
复制的开始位置：
从0开始；
从主服务器备份中恢复到从节点后启动的复制，指示比从零开始多了一步（从主服务器上备份数据库到从服务器上恢复，然后加上binlog做时间点恢复）；
主从服务器mysqld程序版本不一致？
mysql版本只能是主服务器版本低于从服务器，或者与从服务器一致；但是从服务器版本不能低于主服务器
—————————————————————————————————————————————————
主服务器：
配置文件my.cnf
server_id=1（不能与其他节点相同）
log-bin=master-log
innodb_file_per_table = ON
skip_name_resolve = ON
启动服务：
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
#为了安全起见，只需要授予最小权限REPLICATION SLAVE,REPLICATION CLIENT就可以了，而且需要注意，创建账户的时候二进制日志是否开启，开启了也会记录创建用户语句，视情况而定时候需要跳过；
mysql> FLUSH PRIVILEGES;
—————————————————————————–
从服务器：
配置文件my.cnf
server_id=2（不能与其他节点相同）
relay_log=relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
#注意：从服务器没有必要的话，可以不启动二进制日志
启动服务：
mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
mysql> SHOW SLAVE STATUS;
mysql> SET @@global.read_only=1 #开启只读功能，但是对于root用户以及super权限的用户都无效
mysql> show slave hosts; #查看slave的连接状态
—————————————————————————–
—————————————————————————–
双主模型：
要点：
时间同步：可以编辑 /etc/chrony.conf 文件添加合适的服务器时间源，然后重启服务
双方版本：需要一致
中继日志：双方均要启用中继日志（relay-log）
二进制日志：双方都要启动二进制日志
自动增长字段：一方使用奇数，另一方使用偶数，避免重叠
—————————————————————————–
—————————————————————————–
主服务器（server_id=1）：
配置文件my.cnf
server_id=1（不能与其他节点相同）
log-bin=master-log
relay-log=relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
auto_increment_offset = 1 (设置字段增长以1开始，使用奇数)
auto_increment_increment = 2 (设置字段每次增长2)
启动服务：（双方均要创建可用于复制表的用户，只需要最低权限，以及互相添加为主服务器）
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
#为了安全起见，只需要授予最小权限REPLICATION SLAVE,REPLICATION CLIENT就可以了，而且需要注意，创建账户的时候二进制日志是否开启，开启了也会记录创建用户语句，视情况而定时候需要跳过；
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
mysql> SHOW SLAVE STATUS;
mysql> SET @@global.read_only=1 #开启只读功能，但是对于root用户以及super权限的用户都无效
—————————————————————————–
—————————————————————————–
主服务器（server_id=2）：
配置文件my.cnf
server_id=2（不能与其他节点相同）
log-bin=master-log
relay-log=relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
auto_increment_offset = 2 (设置字段增长以2开始，使用偶数)
auto_increment_increment = 2 (设置字段每次增长2)
启动服务：（双方均要创建可用于复制表的用户，只需要最低权限，以及互相添加为主服务器）
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
#为了安全起见，只需要授予最小权限REPLICATION SLAVE,REPLICATION CLIENT就可以了，而且需要注意，创建账户的时候二进制日志是否开启，开启了也会记录创建用户语句，视情况而定时候需要跳过；
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
mysql> SHOW SLAVE STATUS;
—————————————————————————–
MariaDB [(none)]> show slave status\G #开启从服务器后的状态参数解析
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.1.35.1
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 1078
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-log.000001
Slave_IO_Running: No
Slave_SQL_Running: No 由于还没有启动复制功能，所以这两项为NO
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: 这些为过滤项，默认为空，及全部复制；也可以指定复制（忽略）的表或者库
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1078
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key: #基于ssl复制的功能选项
Seconds_Behind_Master: NULL #检查落后服务器多少秒，如果落后的秒数比价大，可以重启IO线程（先STOP SLAVE IO_THREAD；然后再START SLAVE IO_THREAD）
Master_SSL_Verify_Server_Cert: No #是否启用基于ssl功能进行复制
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error: #线程的错误号
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
—————————————————————————–
—————————————————————————–
双主模型弊端：
在第二个节点创建数据库
MariaDB [(none)]> create database mydb;
在第一个节点创建一个边，并定义自动增长的行
MariaDB [mydb]> create table tbl1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30));
返回第二节点，并查看在第二节点上创建的table，并插入数据（到此可以看出双主互相复制）
MariaDB [(none)]> use mydb
MariaDB [mydb]> desc tbl1;
MariaDB [mydb]> insert into tbl1 (name) values ('stu1');
由于定义了第二节点auto_increment_offset = 2 ， 所以有关自动增长字段的数据都是从二开始，并每次使用都自增加2
在第一节点查看并插入数据
上图就是设置双主关于增长字段的跳跃性的弊端，只要不冲突，应该不会有问题。所以设置双主模型最好有一个全局序列符号生成器，需要增长字段的时候，由序列符号生成器提供，而不是服务器自己定义。
—————————————————————————–
—————————————————————————–
以下为用到的命令解析
START SLAVE #启动从服务器的复制功能
MariaDB [(none)]> HELP START SLAVE
Name: 'START SLAVE'
Description:
Syntax:
START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_types:
[thread_type [, thread_type] … ]
thread_type: IO_THREAD | SQL_THREAD
—————————————————————————–
—————————————————————————–
CHANGE MASTER TO #在从服务器上设置主服务器信息的命令
MariaDB [(none)]> HELP CHANGE MASTER TO
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] …
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval #重试的时间间隔，一般使用默认值
| MASTER_HEARTBEAT_PERIOD = interval #探测主服务器的时间间隔，一般使用默认值
| MASTER_LOG_FILE = 'master_log_name' #从主服务器的哪个二进制日志文件开始复制
| MASTER_LOG_POS = master_log_pos #从主服务器的二进制日志文件的哪个pos开始复制
| RELAY_LOG_FILE = 'relay_log_name' #复制到从服务器的哪个中继日志文件，一般使用默认值
| RELAY_LOG_POS = relay_log_pos #复制到从服务器中继日志文件的哪个，一般使用默认值pos位置
| MASTER_SSL = {0|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'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list) #一从多住的选项，一般不建议一从多主；
server_id_list:
[server_id [, server_id] … ]
—————————————————————————–
—————————————————————————–
STOP SLAVE #停止从服务器的复制功能
MariaDB [hellodb]> HELP STOP
Name: 'STOP SLAVE'
Description:
Syntax:
STOP SLAVE [thread_types]
thread_types:
[thread_type [, thread_type] … ]
thread_type: IO_THREAD | SQL_THREAD
—————————————————————————–
—————————————————————————–
总结：
复制时应该注意的问题：
1、从服务设定为“只读”；（双主模式无需设置）
在从服务器启动read_only，但仅对非SUPER权限的用户有效；
阻止所有用户：
mysql> FLUSH TABLES WITH READ LOCK;
2、尽量确保复制时的事务安全
在master节点启用参数：
sync_binlog = ON #每次事务提交的时候，都立即将二进制日志时间都重内存同步到磁盘中，能确保从服务器能立即得到事件，而且能保护本地数据安全
如果用到的是InnoDB存储引擎：
innodb_flush_logs_at_trx_commit=ON #在事务提交时，立即刷写事务日志从内存到磁盘上
innodb_support_xa=ON #支持分布式事务
#这两项启动起来，能在一定程度上确保从服务器能够立即得到主服务器的最新事件
3、从服务器意外中止时尽量避免自动启动复制线程
由于数据是重要的，服务器能意外终止的时间都是奇异的，所以，终止后不要让从服务器自动启动复制线程，等人工手动排查后才从新开启复制线程；有可能复制线程复制到一半的时候出现意外，复制线程或许没有这种功能—–不知道时候需要再次复制上次中断的事件
4、从节点：设置参数
每一个从服务器都会保存一个文件 relay-log-info，是记录在主服务器上复制二进制日志的位置，以及本地中继日志的位置，为避免产生繁忙的IO操作，这些参数都是先保存在内存上的，到一定程度才会同步到磁盘上的，所以不安全
sync_master_info=ON
sync_relay_log_info=ON
以上两项为立即刷写数据从内存到磁盘
===================================================================
===================================================================
===================================================================
===================================================================
半同步复制
每次有时间提交的时候，主服务器完成本地的所有写入操作（事务日志，二进制日志），还要确保这个事件已经写入到从服务器的中继日志中以及完成重放，并报告主服务器已经完成写入操作了，最后主服务器才能告诉客户端数据已经写入，虽然影响性能，但是数据安全性提高了。
半同步复制需要安装Google的开源插件
主服务器需要的插件 –> semisync_master.so
从服务器需要的插件 –> semisync_slave.so
支持多种插件的位置：/usr/lib64/mysql/plugins/
需要安装方可使用：
mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
—————————————————————————–
在主从复制的基础上开始做：
主节点：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安装模块
SHOW PLUGIN; #查看安装模块的情况
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
rpl_semi_sync_master_enabled OFF 半同步复制功能主节点是否开启
rpl_semi_sync_master_timeout 10000 半同步复制功能主节点等待从节点报告的超时时长，单位为毫秒
rpl_semi_sync_master_trace_level 32 主节点的追踪级别
rpl_semi_sync_master_wait_no_slave ON
MariaDB [mydb]> SET GLOBAL rpl_semi_sync_master_enabled=ON;
从节点：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
rpl_semi_sync_slave_enabled OFF
rpl_semi_sync_slave_trace_level 32
MariaDB [mydb]> STOP SLAVE IO_THREAD;
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
MariaDB [mydb]> START SLAVE IO_THREAD;
判断方法：
主节点：
MariaDB [mydb]> SELECT @@global.rpl_semi_sync_master_clients；
或者：
MariaDB [mydb]> SHOW STATUS LIKE ’%semi%‘；
| Rpl_semi_sync_master_clients 半同步复制的连接个数
| Rpl_semi_sync_master_net_avg_wait_time 主节点网络等待平均时长
| Rpl_semi_sync_master_net_wait_time 主节点网络等待的累积时长
| Rpl_semi_sync_master_net_waits 主节点网络等待的累积次数
| Rpl_semi_sync_master_no_times 主节点关闭半同步复制的次数
| Rpl_semi_sync_master_no_tx 主节点等待超时的次数
| Rpl_semi_sync_master_status 是否为半同步复制状态
| Rpl_semi_sync_master_timefunc_failures 时间函数未正常工作的次数
| Rpl_semi_sync_master_tx_avg_wait_time 主节点等待事务提交的平均时长
| Rpl_semi_sync_master_tx_wait_time 主节点等待事务提交的总时长
| Rpl_semi_sync_master_tx_waits 主节点等待事务提交的次数
| Rpl_semi_sync_master_wait_pos_backtraverse 改变当前等待最小二进制日志的次数
| Rpl_semi_sync_master_wait_sessions 当前有多少个session 因为slave 的回复而造成等待
| Rpl_semi_sync_master_yes_tx 主节点收到从节点回复的次数
===================================================================
===================================================================
复制过滤器：
仅复制有限一个或几个数据库相关的数据，而非所有；由复制过滤器进行；
有两种实现思路：
(1) 主服务器过滤
主服务器仅向二进制日志中记录有关特定数据库相关的写操作；
坏处：其它库的point-recovery将无从实现；
好处：节约网络IO以及磁盘IO
binlog_do_db= #白名单控制
binlog_ignore_db= #黑名单控制
如果基于上述控制，及在主节点用二进制日志文件控制，则其他库的二进制日志信息无法得到保存，当需要数据恢复的时候，是无法恢复没有保存二进制日志信息的数据库的信息，慎用！
(2) 从服务器过滤
从服务器的SQL THREAD仅重放关注数据库或表相关的事件，并将其应用于本地；
坏处：网络IO和磁盘IO；
好处：数据安全性更高
MariaDB [mydb]> show slave status\G
Replicate_Do_DB=
Replicate_Ignore_DB=
Replicate_Do_Table=
Replicate_Ignore_Table=
Replicate_Wild_Do_Table= #wild为允许使用通配符
Replicate_Wild_Ignore_Table= #wild为允许使用通配符
由于是GLOBAL参数，只有root用户才有权限修改，但是建议写在配置文件中/etc/my.cnf
#通常不会在表级别过滤数据
===================================================================
===================================================================
基于SSL复制
前提：启用SSL功能；
===================================================================
===================================================================
复制的监控和维护：
(1) 清理日志：PURGE
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };
binary：二进制日志文件
master：日志文件
清除前先备份，或者复制文件出来，再清理，过一段时间确实无用，再作左后的删除
————————————————————————————–
(2) 复制监控
MASTER:
SHOW MASTER STATUS;
SHOW BINLOG EVENTS;
SHOW BINARY LOGS;
SLAVE:
SHOW SLAVE STATUS;
判断从服务器是否落后于主服务器：
Seconds_Behind_Master: 0 如果主从服务器均不繁忙，但是从服务器依然落后于主服务器，可以重启从服务器的 IO_THREAD （先stop slave IO_THREAD 然后 start slave IO_THREAD）
————————————————————————————–
(3) 如何确定主从节点数据是否一致？
通过表的CHECKSUM检查；
使用percona-tools中pt-table-checksum；
————————————————————————————–
(4) 主人数据不一致时的修复方法？
重新复制；
数据集过大，可以使用单表导入导出；
===================================================================
===================================================================
原创文章，作者：hunter，如若转载，请注明出处：http://www.178linux.com/60958
