涂抹mysql笔记-mysql复制特性

weixin_34314962

于 2018-06-03 21:59:35 发布

阅读量87

点赞数

原文链接：http://blog.51cto.com/xiaocao13140/2124299

版权

涂抹mysql笔记-mysql复制特性

mysql复制特性：既可以实现整个服务（all databases）级别的复制，也可以只复制某个数据库或某个数据库中的某个指定的表对象。即可以实现A复制到B（主从单向复制），B再复制到C。也可以实现A直接复制到B和C（单主多从复制），甚至A的数据复制给B，B的数据也复制会A（双主复制）

<>mysql复制处理数据时，有三种不同的模式：
1、基于语句复制（Statement Based Replication）：基于实际执行的sql语句的模式方案简称SBR
2、基于记录复制（Row Based Replication）：基于修改的列的复制模式简称RBR
3、混合复制模式（Mixed Based Replication）：基于上述两种模式的混合简称MBR

创建复制环境：环境条件：master在创建过程中没有读写操作。
1、停止mysql服务。
2、复制数据文件 /mysql 打包复制到从服务器
3、修改初始化参数添加server_id和开启binlog
mkdir -p /mysql/relaylog
[mysqld]
server_id=0611
log-bin=/mysql/binlog/mysql-bin
relay-log=/mysql/relaylog/mysql-relay-bin
relay-log-index=/mysql/relaylog/mysql-relay-bin.index
master-info-file=/mysql/conf/master.info
relay-log-info-file=/mysql/conf/relay-log.info
4、创建复制用户：mysql中的slave若想获取二进制日志，它是得主动连接master节点去请求数据。因为slave节点将关于master的配置都保存在master.info文件中，该文件明文记录连接master节点的所有配置，包括连接的用户名、密码。所以最好单独建个复制账户保证安全性。
system@(none)>grant replication slave on *.* to ‘repl‘ @‘192.168.1.%‘ identified by ‘oralinux‘;
Query OK, 0 rows affected (0.00 sec) 上述命令创建了一个名为repl的用户，允许从192.168.1网段连接服务器，仅拥有读取二进制日志的权限。
5、获取master端的binlog位置和position，执行show master status;
system@(none)>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000019 | 322 | | | |
+------------------+----------+--------------+------------------+-------------------+
File列显示当前正在使用的二进制日志文件名，Position则显示该日志文件中当前写入的位置。
1 row in set (0.00 sec)
6、配置slave端的选项文件：增加server_id
mkdir -p /mysql/relaylog
[mysqld]
server_id=0612
log-bin=/mysql/binlog/mysql-bin
relay-log=/mysql/relaylog/mysql-relay-bin
relay-log-index=/mysql/relaylog/mysql-relay-bin.index
master-info-file=/mysql/conf/master.info
relay-log-info-file=/mysql/conf/relay-log.info
7、删除slave端data下的auto.cnf
rm /mysql/data/auto.cnf
8、启动slave服务并配置slave到master的连接：
system@(none)>change master to master_host=‘192.168.1.6‘,master_port=3306,master_user=‘repl‘,master_password=‘oralinux‘,master_log_file=‘mysql-bin.000019‘,master_log_pos=322;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
9、启动slave端：start slave;
system@(none)>start slave;
Query OK, 0 rows affected (0.02 sec)
10、复制环境数据同步测试：
prompt Master>
Master>create table 5ienet.jason_v2(id int);
Query OK, 0 rows affected (0.10 sec)
创建好表后再slave节点查看表是否生成。
prompt Slave>
Slave>desc 5ienet.jason_v2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
可以看到slave端表已经发现。如果slave端没有找到表对象，数据没有成功同步过来使用show slave status;查看slave端的数据接收和应用状态。
Slave>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 228
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 391
Relay_Master_Log_File: mysql-bin.000021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 228
Relay_Log_Space: 727
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:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 611
Master_UUID: 2584299a-2100-11e7-af61-080027196296
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
查看Last_IO_Error参数是否有值，如果有值则代表主从复制环境有误。

##################################################################################################################################
#修改relay_log生成位置mysql slave启动后会在data目录下生成mysql-relay-bin日志，这样不利于管理。修改要在mysql slave停止状态下修改。#
#拷贝data下的mysql-relay-bin日志集索引文件到新建的relaylog路径下 #
#mkdir /mysql/relaylog #
#cp /mysql/data/mysql-relay-bin* /mysql/relaylog #
#修改my.cnf #
#[mysqld] #
#relay-log=/mysql/relaylog/mysql-relay-bin #
#relay-log-index=/mysql/relaylog/mysql-relay-bin.index #
#编辑mysql-relay-bin.index把路径改为新的绝对路径，编辑relay-log.info文件也改为绝对路径。 #
##################################################################################################################################

常用的复制环境管理命令：在slave节点执行获取该节点与master节点的同步信息：
show slave status \G
Slave_IO_State：显示Slave当前状态
Slave_IO_Running：显示I/O线程是否在允许，正常情况下应该在允许，除非dba手动将其停止，或者出现错误
Slave_SQL_Running：显示sql线程是否在允许，正常情况下应该在运行，除非dba手动将其停止或者出现错误
Last_IO_Error/Last_SQL_Error：正常情况下应该是控制，如果遇到错误那么在这里就会输出错误信息。
Seconds_Behind_Master：显示当前slave节点与master节点的同步延迟。Slave节点接收到Master的日志文件和已经应用的日志文件位置之间的差距，比如说I/O线程接收到的日志文件写入位置为34560，而sql线程才应用到34000，这两个位置之间时间上的差距是1小时，那么Seconds_Behind_Master就会显示3600秒，而有可能此时Master已经写到34660的位置了，只是还没有被IO线程读取到slave节点，这个参数显示的值并不是完全精确的主从之间的延迟时间，而只是slave节点本地日志接收和应用上的差异。这个参数值最好是0，如果参数值较大（延迟较大）可能性有两种，一个是IO_THREAD在运行，但SQL_THREAD被停止了。如果不是手动停止的SQL_THREAD那么八成是出现错误了。那么可以根据Last_SQL_Error参数总的信息进一步分析。
Master_Log_File/Read_Master_Log_Pos：显示当前读取的master节点二进制日志文件和文件位置。
Relay_Master_Log_File/Exec_Master_Log_Pos：显示当前slave节点正在应用的日志文件位置。
Relay_Log_File/Relay_Log_Pos：显示当前Slave节点正在处理中继的日志文件和位置

Master>show processlist \G
*************************** 1. row ***************************
Id: 5
User: repl
Host: linux02:55206
db: NULL
Command: Binlog Dump
Time: 2813
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

Slave>show processlist \G
*************************** 1. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 2726
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 1493
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
通过Master/Slave中连接信息表明每一组Master、Slave都有三个线程（Master1个、Slave2个）维护复制环境中数据的同步。

启停Slave线程：
Slave>stop slave;
Query OK, 0 rows affected (0.00 sec)

Slave>show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 450
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 505
Relay_Master_Log_File: mysql-bin.000021
Slave_IO_Running: No
Slave_SQL_Running: 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: 450
Relay_Log_Space: 841
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:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 611
Master_UUID: 2584299a-2100-11e7-af61-080027196296
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
Slave>start slave;
Query OK, 0 rows affected (0.00 sec)
Slave>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 450
Relay_Log_File: mysql-relay-bin.000008
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 450
Relay_Log_Space: 841
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:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 611
Master_UUID: 2584299a-2100-11e7-af61-080027196296
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

Slave服务由两个线程组成：
IO_THREAD：负责读取Master端的二进制日志，并写入到本地的中继日志（relay-log）并复制更新master.info文件
SQL_THREAD：复制从本地中继日志中读取事件并执行。并复制更新relay-log.info文件
这是两个独立的线程，他们的启动和停止也可以分别控制。这两个线程的启动和停止并没有依赖性，可以选择启动SQL_THREAD线程，停止IO_THREAD线程。或者做相反的操作。
灵活利用这两个子线程的启停可以使复制环境的应用场景更加灵活，如下执行备份时为了保持备份数据一致性，很多人往往会选项停止整个slave，但是IO_THREAD并不需要停止，它可以继续从Master读取二进制日志，保持在本地的中继日志中。这样还可以起到对Master节点数据冗余保护的作用。我们只需要停止SQL_THREAD，记录下当前应用到的日志文件名和位置，就可以开始备份任务。因为此时slave不会有数据更新。相当于此时数据库处于只读状态，这样创建出来就是体制下备份了。当然数据在备份期间与master是不同步的

单独启动或停止IO_THREAD/SQL_THREAD只需在start slave后加线程名称就可以
Slave>stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

Slave> start slave sql_thread;
Query OK, 0 rows affected (0.29 sec)

Slave>

<>复制特性的实施原理和关键
复制特性依赖于二进制日志，slave端获取master的二进制日志并应用这些日志进行数据同步。二进制日志在记录事件时支持多种格式由binlog_format参数控制：
基于语句记录（Statement-Based Logging，SBL）对应的参数值为statement
基于行格式记录（Row-Based Logging RBL）对应的参数值为row
混合模式记录（Mixed-Based Logging MBL）对应的参数值为mixed

复制格式种类从逻辑上将分为三类：
基于语句复制（Statement-Based Replication SBR）二进制日志文件中保存的是执行的sql语句。5.1.4版本之前只有这一种日志记录方式
基于行复制（Row-Based Replication RBR）二进制日志文件中写入事件时，记录的是变更的记录行的信息。
混合记录模式（Mixed-Based Replication MBR）记录事件到二进制日志时根据需要动态修改日志的格式。注意这是一种记录的模式不是记录的格式。这种模式下，默认还是会选择基于语句的格式记录日志，只有在需要的场景下，才会自动切换成基于行的格式记录日志。
mysql5.6版本中默认的日志记录格式是基于语句，建议修改为混合模式:
[mysqld]
binlog_format=mixed

使用SBR的优点：
1、技术成熟3.23版本就提供对这种记录格式的支持
2、生成日志少，特别是对于大量更新及删除的操作。
3、由于能够记录下数据库做过的所有变更操作日志可用于行为审计
使用SBR的缺点：
1、存在安全隐患，Master孔二道门椎间盘买个产生的修改操作（INSERT DELETE UPDATE REPLACE）并不是都能通过基于语句方式完整的复制到Slave节点，对于不确定的行为在基于语句复制时，很难确保Slave节点会执行并获得正确的数据。这点从逻辑上证明了主从出现不一致的合理性。比如说Master节点和Slaves节点分别执行FOUND_ROWS()、SYSDATE()、UUID()这类函数可能出现返回不同的结果，如果使用了这些函数那么执行语句时就排除下列警告信息（客户端通过show warnings查看）：
[Warning] Statement is not safe to log in Statement format
2、执行insert...select语句时需要持有更多的行锁（相比RBR而言）
3、update要扫描表（无可用索引情况下）时需要持有更多的行锁（相比RBR而言）
4、对于InnoDB引擎，insert语句使用aotu_increment会阻塞其他insert语句。
5、对于复杂的语句，Slave节点执行时语句必须先被评估，而对于基于row格式复制，则slave节点只需要修改具体的记录即可。（不必执行跟master端相同的sql语句，这既是有点也是缺点）
6、如果语句在slave节点执行时操作失败，基于Statement格式复制就会增加主从不一致的概率
7、如单条语句中执行的函数中调用now()返回日志相同，但是存储过程就不一定了
8、对象定义必须（最好）拥有唯一键。主要是为了避免冲突。

使用RBR的优点：
1、所有修改都能被完全地复制到slave节点
2、与其他RDBMS实现的技术类似，其他数据库软件管理和维护方面的经验也可以继承使用
3、Master端执行修改操作时，仅需极少的锁持有，因此可获得更高的并发性能。
使用RBR的缺点：
1、可能会生产更多的日志。创建备份和恢复可能需要更长的时间。以及二进制日志会被更长时间加锁以写数据，也可能带来额外的并发性能上的问题。
2、UDFS生成BLOB值需要花费比基于statement格式日志更长的时间。这是因为Blob列的值是被记录的，而不是语句生成的。
3、不能通过分析日志来获取曾经执行过的语句。不过可通过mysqlbinlog工具能够看到哪些数据被修改了。
4、对于非事务存储引擎，比如MyISAM表对象，Slave节点应用insert操作时，使用RBR模式要比使用SBR模式持有更强的锁定，这也就是说使用RBR模式在Slave节点上没有并行插入的概念。

SBR和RBR不同场景的表现：
1、有条很复杂的sql执行一小时最终修改了一条记录，若使用SBR模式那么salve节点也要执行一小时才能完成，但是使用RBR最终记录的是执行后的变更结果，那么slave端应用瞬秒就完成了。
2、有条简单的sql语句，向库中插入一千万条语句，采用RBR模式一千万条记录生成的二进制日志非常多，那么slave端需要长时间接收，以后还得话长时间慢慢应用。若使用SBR那么二进制日志中记录的事件就是该条sqk语句，占不了几个字节，salve端很快就接收完毕，然后开始应用。总体开销比RBR模式小很多。

MBR模式也是SBR模式，只有存在数据安全隐患时自动将记录格式变更为基于行格式记录。

<>中继日志文件和状态文件：中继日志文件和二进制日志文件极为相似，这俩唯一的区别更多是逻辑上的，二进制日志文件用于保存节点自身产生的事件。中继日志文件则是保存接收自其它节点的事件（也是二进制格式的）也可以通过mysqlbinlog命令解析。中继日志文件默认保存在data下host_name-relay-bin.nnnnnn的命名规则。其保存路径可通过--relay-log和--relay-log-index参数进行自定义。

slave节点在满足下列条件时触发创建新的中继日志文件，并更新相关索引文件：
1、启动slaves节点I/O线程时
2、执行日志刷新命令，比如flush logs或mysqladmin flush-logs等
3、中继日志文件达到指定最大值。有两种情况：
如果max_relay_log_size参数值大于0，则日志文件超过该值后即会重建。
如果max_relay_log_size参数值为0，则通过max_binlog_size确定单个Relay日志文件的最大值。
中继日志文件的管理可以完全交由slave节点的SQL_THREAD线程来维护。他会自动删除无用的中继日志文件，至于到底如何删除以及何时进行删除，并没有明确的机制由SQL_THRAD线程自己全部搞定。

master.info和relay-log.info信息默认保存在data目录下，可以通过--master-info-file和--relay-log-info-file参数修改文件的名称和保存路径
master.info：保存复制环境中连接Master节点的配置信息，5.6版本后也可以将这些信息保存在mysql.slave_master_info表对象
relay-log.info：保存处理进度及中继日志文件的位置。5.6后也可以保存在mysql.slave_relay_log_info表对象中。

<>复制过程规则：
Slave节点在接收日志时没有选择权，Master节点写过的日志它全得收到本地，保存在中继日志文件中。
1、库级过滤规则
2、表级复制选项
3、过滤规则的应用示例：Slave节点过滤Master节点jason库和jason_mc中所有对象，以及5ienet库下的rep_t1对象所有操作。并复制jason库下的j1表。
master节点修改my.cnf：binlog_format=statement
slave节点my.cnf文件：
replicate-ignore-db=jason
replicate-ignore-db=jason_mc
replicate-ignore-table=5ienet.rep_t1
replicate-do-table=jason.j1

修改完后重启master和slave服务

高级应用技巧
1、通过Xtrabackup创建slave节点：操作过程不需要重启master节点。
1.1、创建完整备份：使用我们封装好的脚本进行备份。脚本内容如下：
[mysql@linux01 scripts]$ cat /mysql/scripts/mysql_env.ini
#set env
MYSQL_USER=system
MYSQL_PASS=‘oralinux‘

#check parameter
if [ $# -ne 1 ]
then
HOST_PORT=3306
else
HOST_PORT=$1
fi

新建mysql_full_backup_byxtra.sh
#!/bin/sh
#Created by jason 20170414

source /mysql/scripts/mysql_env.ini

echo > $BACKUPLOG_FILE
echo -e "===Jobs started at `date +%F‘ ‘%T‘ ‘%w`===\n" >> $BACKUPLOG_FILE
echo -e "===First cp my.cnf file to backup directory===" >> $BACKUPLOG_FILE
/bin/cp $ORI_CONF_FILE $NEW_CONF_FILE
echo > $BACKUPLOG_FILE

echo -e "****Executed command:${MYSQL_CMD} | gzip > $BACKUP_FILE" >> $BACKUPLOG_FILE
${MYSQL_CMD} 2>> $BACKUPLOG_FILE | gzip - > $BACKUP_FILE
echo -e "****Executed finished at `date +%F‘ ‘%T‘ ‘%w` ===" >> $BACKUPLOG_FILE
echo -e "****Backup file size: `du -sh $BACKUP_FILE` ===\n" >> $BACKUPLOG_FILE
echo -e "---Find expired backup and delete those files---" >> $BACKUPLOG_FILE
for tfile in $(/usr/bin/find $BACKUP_PATH/ -mtime +6)
do
if [ -d $tfile ] ; then
rmdir $tfile
elif [ -f $tfile ] ; then
rm -f $tfile
fi
echo -e "---Delete file:$tfile---" >> $BACKUPLOG_FILE
done
echo -e "\n===Job ended at `date +%F‘ ‘%T‘ ‘%w`===\n" >> $BACKUPLOG_FILE
./mysql_full_backup_byxtra.sh创建备份。
1.2、复制和创建备份集，scp把备份集复制到slave节点
scp xtra_fullbak_2017-04-18.tar.gz linux02:/mysql/backup/
scp my_2017-04-18.cnf linux02:/mysql/backup/
chown mysql:mysql xtra_fullbak_2017-04-18.tar.gz
chown mysql:mysql my_2017-04-18.cnf
解压缩备份集注意执行解压时务必附加参数-i
tar -zixvf xtra_fullbak_2017-04-18.tar.gz -C /mysql/data
准备数据执行innobackupex命令附加--apply-log参数：
innobackupex --apply-log /mysql/data 这一步是为了使数据文件达到一致性状态最后提示complete OK就对了。
1.3、创建复制环境专用账户：前面已创建repl账户这里不做重复
1.4、配置slave节点初始化参数文件：在master节点的初始化参数文件复制一份即可。修改slave节点my.cnf添加server_id=0612
1.5、启动slave节点
1.6、配置slave节点复制环境：查看Xtrabackup中的xtrabackup_binlog_info文件：
[mysql@linux02 data]$ cat /mysql/data/xtrabackup_binlog_info
mysql-bin.000022 120
1.7、执行change master命令：
system@(none)>change master to master_host=‘192.168.1.6‘,master_port=3306,master_user=‘repl‘,master_password=‘oralinux‘,master_log_file=‘mysql-bin.000022‘,master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
1.8、启动slave服务：start slave;
1.9、检查
system@(none)>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000022
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 120
Relay_Log_Space: 456
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:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 611
Master_UUID: 2584299a-2100-11e7-af61-080027196296
Master_Info_File: /mysql/conf/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

利用slave节点创建备份：
1、停止slave服务中的SQL_THREAD线程
2、记录当前接收和应用的二进制日志文件及位置
3、执行备份命令
4、再次记录当前接收和应用的二进制日志文件及位置
5、启动slave服务中的SQL_THERAD线程。

创建slave节点的备份脚本mysqldump方式：mysql_full_backup_slave.sh
#!/bin/sh
#Created by jason 20170418

show_slave_status(){
echo -e "---master.info:---" >> $BACKUPLOG_FILE
cat /mysql/conf/master.info |sed -n ‘2,3p‘ >> $BACKUPLOG_FILE
echo -e "--show slave status:---" >> $BACKUPLOG_FILE
echo "show slave status \G" | $MYSQL_CMD | egrep "Slave_IO_Running|Slave_SQL_Running|Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Relay_Log_File|Relay_Log_Pos" >> $BACKUPLOG_FILE
echo -e "" >> $BACKUPLOG_FILE
}

#source /mysql/scripts/mysql_env.ini
#set env
MYSQL_USER=system
MYSQL_PASS=‘oralinux‘

#check parameter
if [ $# -ne 1 ]
then
HOST_PORT=3306
else
HOST_PORT=$1
fi

BACKUP_PATH=/mysql/backup
BACKUP_FILE=${BACKUP_PATH}/dbfullbak_`date +%F`.sql.gz
BACKUPLOG_FILE=${BACKUP_PATH}/dbfullbak_`date +%F`.log
MYSQL_PATH=/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /mysql/conf/mysql.sock"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /mysql/conf/mysql.sock -A -R --single-transaction -l --default-character_set=utf8"
echo > $BACKUPLOG_FILE
echo -e "===Jobs started at `date +%F‘ ‘%T‘ ‘%w`===\n" >> $BACKUPLOG_FILE
echo -e "****stared position:===" >> $BACKUPLOG_FILE
echo "stop slave SQL_THREAD;" | $MYSQL_CMD
show_slave_status
echo -e "****Executed command:${MYSQL_DUMP} | gzip > $BACKUP_FILE" >> $BACKUPLOG_FILE
${MYSQL_DUMP} | gzip > $BACKUP_FILE
echo -e "****Executed finished at `date +%F‘ ‘%T‘ ‘%w` ===" >> $BACKUPLOG_FILE
echo -e "****Backup file size: `du -sh $BACKUP_FILE` ===\n" >> $BACKUPLOG_FILE
echo -e "****recheck position===" >> $BACKUPLOG_FILE
show_slave_status
echo "start slave SQL_THREAD;" | $MYSQL_CMD
echo -e "---Find expired backup and delete those files---" >> $BACKUPLOG_FILE
for tfile in $(/usr/bin/find $BACKUP_PATH/ -mtime +6)
do
if [ -d $tfile ] ; then
rmdir $tfile
elif [ -f $tfile ] ; then
rm -f $tfile
fi
echo -e "---Delete file:$tfile---" >> $BACKUPLOG_FILE
done
echo -e "\n===Job ended at `date +%F‘ ‘%T‘ ‘%w`===\n" >> $BACKUPLOG_FILE

使用Xtrabackup方式备份mysql_full_backupxtra_slave.sh：
#!/bin/sh
#Created by jason 20170418

#source /mysql/scripts/mysql_env.ini
#set env
MYSQL_USER=system
MYSQL_PASS=‘oralinux‘

#check parameter
if [ $# -ne 1 ]
then
HOST_PORT=3306
else
HOST_PORT=$1
fi

BACKUP_PATH=/mysql/backup
BACKUP_FILE=${BACKUP_PATH}/xtra_fullbak_`date +%F`.tar.gz
BACKUPLOG_FILE=${BACKUP_PATH}/xtra_fullbak_`date +%F`.log
ORI_CONF_FILE=/mysql/conf/my.cnf
NEW_CONF_FILE=$BACKUP_PATH/my_`date +%F`.cnf
MYSQL_PATH=/mysql/bin
MYSQL_BACKUP_PATH=/mysql/xtrabackup/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /mysql/conf/mysql.sock"
MYSQL_BACKUP="${MYSQL_BACKUP_PATH}/innobackupex --defaults-file=$ORI_CONF_FILE --user=xtrabk --password=‘oralinux‘ --stream=tar /tmp"
echo > $BACKUPLOG_FILE
echo -e "===Jobs started at `date +%F‘ ‘%T‘ ‘%w`===\n" >> $BACKUPLOG_FILE
echo -e "===First cp my.cnf file to backup directory===" >> $BACKUPLOG_FILE
/bin/cp $ORI_CONF_FILE $NEW_CONF_FILE
echo > $BACKUPLOG_FILE
echo -e "****stared position:===" >> $BACKUPLOG_FILE
echo "stop slave SQL_THREAD;" | $MYSQL_CMD
show_slave_status
echo -e "****Executed command:${MYSQL_BACKUP} | gzip > $BACKUP_FILE" >> $BACKUPLOG_FILE
${MYSQL_BACKUP} 2>> $BACKUPLOG_FILE | gzip - > $BACKUP_FILE
echo -e "****Executed finished at `date +%F‘ ‘%T‘ ‘%w` ===" >> $BACKUPLOG_FILE
echo -e "****Backup file size: `du -sh $BACKUP_FILE` ===\n" >> $BACKUPLOG_FILE
echo -e "****recheck position===" >> $BACKUPLOG_FILE
show_slave_status
echo "start slave SQL_THREAD;" | $MYSQL_CMD
echo -e "---Find expired backup and delete those files---" >> $BACKUPLOG_FILE
for tfile in $(/usr/bin/find $BACKUP_PATH/ -mtime +6)
do
if [ -d $tfile ] ; then
rmdir $tfile
elif [ -f $tfile ] ; then
rm -f $tfile
fi
echo -e "---Delete file:$tfile---" >> $BACKUPLOG_FILE
done
echo -e "\n===Job ended at `date +%F‘ ‘%T‘ ‘%w`===\n" >> $BACKUPLOG_FILE
通过脚本备份完成后查看读取和应用的master节点二进制日志及位置有无变化，只要Relay_Master_Log_File和Exec_Master_Log_Pos属性值没有变化我们就可以人为备份数据是一致的。后续希望时间增量备份，只要把备份操作之后的所有二进制文件保存到备份路径下即可。恢复时从指定位置（Relay_Master_Log_File和Exec_Master_Log_Pos顺序所在的位置）开始应用即可。

复制文件方式创建备份：
1、停止Slave节点数据库服务
2、复制数据库主目录
3、启动slave节点数据库服务

<>部署级联Slave增强复制性能
--log-slave-updates参数即使是应用中继日志产生的数据库修改，也将会写到本地二进制日志文件中。
1、配置RelaySlave节点修改my.cnf在[mysqld]区块中添加log-salve-update如下：
vi /mysql/conf/my.cnf
[mysqld]
log-salve-update
2、重新启动RelaySlave节点的mysql服务使参数生效。正确的步骤先停止SQL_THREAD线程：
stop slave sql_thread;
3、查看当前节点二进制日志文件和位置：show master status;
system@(none)>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000026 | 418 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4、RelaySlave节点的重新启动。
注：RelaySlave节点也必须开启binlog
5、级联的slave3节点创建，创建步骤省略。（参照前面的各种方法都行）
6、级联的slave3创建成功并启动后，配置到master节点的连接。注意此处的master是RelaySlave节点执行命令如下：
change master to master_host=‘192.168.1.7‘,master_port=3306,master_user=‘repl‘,master_password=‘oralinux‘,master_log_file=‘mysql-bin.000026‘,master_log_pos=418;
7、级联的slave3节点start slave; 并使用show slave status;查看两个关键线程启动没有。

同步机制：master节点每进行一个操作，在事务提交并返回成功信息给发出请求的会话前，先等待salve节点在本地执行这个事务，当salve执行成功并返回成功执行的消息给master节点。master才会将事务提交信息返回给发出请求的会话。在分布式事务中管叫两阶段提交。这种方式能够最大程度地保证数据安全，但是缺点也很明显，客户端每提交一个请求，从事务启动到成功执行，中间可能出现较长时间的延迟，影响性能。
半同步机制：Master在返回操作成功（或失败）信息给发起请求的客户端前，还是要将事务发送给Slave节点（不这样不足以保证安全性和及时性）不过为了降低中间的数据通信、数据传输等时间等待等成本。它还是做了一定的取舍。在半同步机制下，Master节点只要确认至少一个Slave节点接收到乐事务，即可向发起请求的客户端返回操作成功的信息。Master节点甚至不需要等待Slave节点也成功执行完这个事务，只要至少有一个Slave节点接收到这个事务，并且将之成功写入到本地的中继日志文件就算成功。这种模式下Master节点出现宕机也没关系。Slave节点还能扛起数据恢复的重任。

配置半同步复制环境
5.5版本官方引进半同步复制插件。需要安装semisynchronous这个插件。通过show plugins;查看我们现在的环境没有安装这个插件。
show variables like ‘plugin_dir‘; 插件存放的位置以.so结尾的文件
system@5ienet> show variables like ‘plugin_dir‘;
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| plugin_dir | /mysql/lib/plugin/ |
+---------------+--------------------+
1 row in set (0.00 sec)
[mysql@linux01 scripts]$ ll /mysql/lib/plugin/ |grep semi
-rwxr-xr-x. 1 mysql mysql 435084 3月 30 17:30 semisync_master.so
-rwxr-xr-x. 1 mysql mysql 258182 3月 30 17:30 semisync_slave.so

1、在master节点执行命令、加载semisynchronous插件：install plugin rpl_semi_sync_master soname ‘semisync_master.so‘;
master@mysql>install plugin rpl_semi_sync_master soname ‘semisync_master.so‘;
Query OK, 0 rows affected (0.00 sec)
在slave节点加载插件：install plugin rpl_semi_sync_slave soname ‘semisync_slave.so‘;
slave@mysql>install plugin rpl_semi_sync_slave soname ‘semisync_slave.so‘;
Query OK, 0 rows affected (0.01 sec)
配置完成后通过show plugins;查看插件是否处于可用状态
2、在master节点设置下列变量：
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=3000;
在slave节点只需要设置一个变量：
set global rpl_semi_sync_slave_enabled=1;
以上参数可以动态修改，但是强烈建议将所有配置的变量保存在初始化参数文件中。这样每次启动mysql时不用再手动配置了。
Master：[mysqld]
#replcation_master
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000

Slave：[mysqld]
#replcation_slave
rpl_semi_sync_slave_enabled=1

set global rpl_semi_sync_master_enabled=1;用于控制是否在master节点启用半同步复制，默认为1即启用状态。
set global rpl_semi_sync_master_timeout=3000; 用于指定master节点等待slave相应的时间，单位是毫秒我们这里设置为3秒，若超出指定的时间slave节点仍无响应，那么当前复制环境就临时被转换为异步复制。
set global rpl_semi_sync_slave_enabled=1; 控制slave是否启用半同步复制。

3、重新启动Slave节点的IO_THREAD线程。这一步主要是为了叫Slave节点重新连接master节点，注册成为半同步Slave身份。
slave@mysql>stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

slave@mysql>start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

监控半同步复制环境
Slave节点与半同步复制相关的状态变量只有一项Rpl_semi_sync_slave_status，标示当前slave是否启动半同步复制模式。
slave@mysql>show status like ‘rpl%‘;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)

Master节点与半同步复制相关的变量要多一些，其中值得关注的有：
Rpl_semi_sync_master_clients：显示当前处于半同步模式的Slave节点数量。
Rpl_semi_sync_master_status：标示当前Master节点是否启用了半同步模式。
Rpl_semi_sync_master_no_tx：当前未成功发送到Slave节点的事务数量。
Rpl_semi_sync_master_yes_tx：当前已成功发送到Slave节点的事务数量。

master@mysql>show status like ‘rpl%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1367 |
| Rpl_semi_sync_master_net_wait_time | 1367 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1513 |
| Rpl_semi_sync_master_tx_wait_time | 1513 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

在master节点执行一条语句：insert into 5ienet.jason_v2 values(3);
master@mysql>insert into 5ienet.jason_v2 values(3);
Query OK, 1 row affected (0.01 sec)
在Slave节点查询数据：select * from 5ienet.jason_v2;
slave@mysql>select * from 5ienet.jason_v2;
+------+
| id |
+------+
| 1 |
| 2 |
| 4 |
| 3 |
+------+
4 rows in set (0.00 sec)
然后再次查询Master节点中的相关变量值，会发现已经产生了变化
master@mysql>show status like ‘rpl%‘;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1444 |
| Rpl_semi_sync_master_net_wait_time | 2889 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1688 |
| Rpl_semi_sync_master_tx_wait_time | 3377 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
根据这些状态值我们就可以分析当前半同步复制的运行情况了。如果半同步出现异常。那么像Rpl_semi_sync_master_no_tx变量值就会累加，而Rpl_semi_sync_master_net_wait_time和Rpl_semi_sync_master_net_avg_wait_time这类以时间计数的变量值增长更是迅猛。这时候只要再检查一下Rpl_semi_sync_master_status和Rpl_semi_sync_slave_status的值就能判断到底是半同步还是异步复制了。

复制环境中的故障切换
主从是个逻辑概念，我们把数据库的写挡在那个数据库实例执行，那么该实例就可被视为Master。因此如果master出现故障，我们将应用连接的数据库指向一台新的mysql实例即可。其次slave节点随时都可以通过change master to语句修改其参照的master。需要注意的是预备切换的slave节点需要开启binlog

举例：一主三从的架构，主故障，slave2接管应用。从宕机到现在slave2触发了很多数据变更。若让slave1和slave3节点选择从slave2节点的当前位置开始读取数据，那么中间的数据必然就丢失了。所以这种问题如何解决？所以实际操作中，不是执行故障切换时将哪个slave转换为master角色，二是若master角色拥有多个slave实例，将其中某个slave提升为新的master后，其他那些slave如何在不影响数据完整性的前提下注册到新的master节点中。
所以在master出现故障后，第一时间检查slave1-3各节点slave状态信息(show slave status;)重点关注当前读取的master节点日志文件和读取位置（Master_Log_File和Read_Master_Log_Pos），已经执行过的日志位置（Exec_Master_Log_Pos），Slave节点IO和SQL线程运行状态。主从之前的延迟间隔（Seconds_Behind_Master）等信息。也可以再通过show processlist;语句查看相关线程的当前状态是否包含有“Slave has read all relay log”之类的字眼，来交叉验证slave节点的数据应用情况。如果三个节点中接收到的返回信息都相同，说明3个节点的数据处于一致状态。这就好办了。

登陆到slave2节点执行下列命令：
stop slave;
reset slave;
这两条命令会清除slave2节点中与slave相关的配置，删除master.info relay-log文件然后执行：
show master status; 记录当前正在操作的二进制日志文件名和写入位置。接下来可以叫应用层修改连接地址改为slave2实例。先将业务恢复。对于其他的slave节点，现在我们已经拥有了最关键的master_log_file和master_log_pos两个信息。因此随时可以配置slave1和slave3两个实例执行change master to命令使其连接slave2节点获取数据。而后master节点恢复后也可以通过change master to 语句将其变为复制环境中的一个slave节点。

延迟复制：指定slave节点中change master to master_delay=n; 单位是秒。设置完成后start slave;就可以是指生效，无需重启mysql。这样设置后slave节点接收到master节点生成的二进制日志不会马上应用而是等待。知道时间符合设定的延迟条件后才开始应用。
设置延迟后通过show slave status;查看slave节点复制信息时有3个列值于此有关：
SQL_Delay：显示当前设定的延迟时间，单位为秒
SQL_Remaining_Delay：当Slave_SQL_Running_State列的状态是“Waiting until MASTER_DELAY seconds after master execute event”时，本列显示的值就是距离延迟阈值的时间，换个说法是还有多长时间才能开始应用。否则的话本列值应该是NULL。
Slave_SQL_Running_State：显示当前SQL_THREAD的状态，当SQL_THREAD处于延迟等待阶段，show processlist;显示该进程状态时，将会显示为“Waiting until MASTER_DELAY seconds after master execute event”这些信息都说明当前环境配置了延迟复制。

涂抹mysql笔记-mysql复制特性

原文：http://www.cnblogs.com/datalife/p/6781302.html

转载于:https://blog.51cto.com/xiaocao13140/2124299

weixin_34314962

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
涂抹mysql笔记-mysql复制特性

涂抹mysql笔记-mysql复制特性mysql复制特性：既可以实现整个服务（all databases）级别的复制，也可以只复制某个数据库或某个数据库中的某个指定的表对象。即可以实现A复制到B（主从单向复制），B再复制到C。也可以实现A直接复制到B和C（单主多从复制），甚至A的数据复制给B，B的数据也复制会A（双主复制）<>mysql复制处理数据时，有三种不...
复制链接

扫一扫