1. 说明1.1 xtrabackupmysqldump对于导出10G以下的数据库或几个表还是适用的而且更快捷。一旦数据量达到100-500G无论是对原库的压力还是导出的性能mysqldump就力不从心了。Percona-Xtrabackup备份工具是实现MySQL在线热备工作的不二选择可进行全量、增量、单表备份和还原。但当数据量更大时可能需要考虑分库分表或使用 LVM 快照来加快备份速度了2.2版本 xtrabackup 能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份innobackupex通过perl封装了一层xtrabackup对MyISAM的备份通过加表读锁的方式实现。2.3版本 xtrabackup 命令直接支持MyISAM引擎。XtraBackup优势 无需停止数据库进行InnoDB热备增量备份MySQL流压缩到传输到其它服务器能比较容易地创建主从同步备份MySQL时不会增大服务器负载1.2 replication为什么要做主从复制我想这是要在实施以前要想清楚的问题。是为了实现读写分离减轻主库负载或数据分析 为了数据安全做备份恢复主从切换做高可用大部分场景下以上三个问号一主一从都能够解决而且任何生产环境都建议你至少要有一个从库假如你的读操作压力特别大甚至要做一主多从还可以不同的slave扮演不同的角色例如使用不同的索引或者不同的存储引擎或使用一个小内存server做slave只用于备份。当然slave太多也会对master的负载和网络带宽造成压力此时可以考虑级联复制即 A-B-C 还有需要考虑的是一主一从一旦做了主从切换不通过其它HA手段干预的话业务访问的还是原IP而且原主库很容易就作废了。于是 主-主 复制就产生了凭借各自不同的 server-id 可以避免 “A的变化同步到BB应用变化又同步到A” 这样循环复制的问题。但建议是主主复制其中一个主库强制设置为只读主从切换后架构依然是可用的。复制过程是slave主动向master拉取而不是master去推的所以理想情况下做搭建主从时不需要master做出任何改变甚至停服slave失败也不影响主库。复制类型基于语句的复制STATEMENT在主服务器上执行的SQL语句在从服务器上执行同样的语句有可能会由于SQL执行上下文环境不同而是数据不一致例如调用NOW()函数。MySQL在5.7.7以前默认采用基于语句的复制在 5.7.7 及以后版本默认改用 row-based。基于行的复制ROW把改变的内容复制过去而不是把命令在从服务器上执行一遍。从mysql5.0开始支持能够严格保证数据完全一致但此时用mysqlbinlog去分析日志就没啥意义。因为任何一条update语句都会把涉及到的行数据全部set值所以binlog文件会比较大。遇到的一个坑是迁移时从库改正了字段默认值定义但数据在主库更改后即使产生的新数据默认值是正确的但基于行的复制依然用不正确的值字段全部更新了混合类型的复制:MIXED默认采用基于语句的复制一旦发现基于语句的无法精确的复制时就会采用基于行的复制。mysql系统库mysql库里面表的日志记录格式需要说明在通过如INSERT、UPDATE、DELETE、TRUNCATE等方式直接修改数据的语句使用binlog_format指定的方式记录但使用GRANT、ALTER、CREATE、RENAME等改动的mysql库里数据的会强制使用statement-based方式记录binlog。可以在线修改二进制日志类型如SET SESSION binlog_formatMIXED;需要SUPER权限。复制类型还可以分为 异步复制和半同步复制。通常没说明指的都是异步即主库执行完Commit后在主库写入Binlog日志后即可成功返回客户端无需等等Binlog日志传送给从库一旦主库宕机有可能会丢失日志。而半同步复制是等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后才返回Commit操作成功给客户端如此半同步就保证了事务成功提交后至少有两份日志记录一份在主库Binlog上另一份在从库的Relay Log上从而进一步保证数据完整性半同步复制很大程度取决于主从网络RTT往返时延以插件 semisync_master/semisync_slave 形式存在。原理master将改变记录到二进制日志(binary log)中这些记录叫做二进制日志事件binary log eventsslave将master的binary log events拷贝到它的中继日志(relay log)slave重做中继日志中的事件将改变反映它自己的数据。该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日志即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后master通知存储引擎提交事务。下一步将master的binary log拷贝到它自己的中继日志。首先slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接请求从指定日志文件的指定位置之后的日志内容然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件如果已经跟上master它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。SQL slave threadSQL从线程处理该过程的最后一步。SQL线程从中继日志读取事件并重放其中的事件而更新slave的数据使其与master中的数据一致。只要该线程与I/O线程保持一致中继日志通常会位于OS的缓存中所以中继日志的开销很小。此外在master中也有一个工作线程和其它MySQL的连接一样slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的也就是说master上的并行更新操作不能在slave上并行操作。补充mysql 5.7开始加入了多源复制这个特性对同时有很多个mysql实例是很有用的阿里云RDS迁移实现了类似的方式。从MySQL 5.6.2开始mysql binlog支持checksum校验并且5.6.6默认启用CRC32这对自己模拟实现mysql复制的场景有影响。下面开始配置主从主从版本一致—主库授权复制帐号—确保开启binlog及主从server_id唯一—xtrabackup恢复到从库—记录xtrabackup_binlog_info中binlog名称及偏移量—从库change master to —slave start—检查两个yes2. 创建复制账号在主库上mysql GRANT REPLICATION SLAVE ON *.* TO slave_ali192.168.5.% IDENTIFIED BY slave_ali_pass; mysql FLUSH PRIVILEGES;3. 使用Percona-Xtrabackup恢复数据这里假设比较简单的情况全量备份全量恢复不涉及增量。安装和具体使用见[文章]()。赋予备份用户权限mysql CREATE USER bkpuserlocalhost IDENTIFIED BY bkppass; mysql GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,PROCESS,SUPER ON *.* TO bkpuserlocalhost; mysql FLUSH PRIVILEGES;完整的选项使用请执行innobackupex –-help这里只介绍使用常用的选项进行完整备份及增量备份和还原。这一节是把数据恢复到从库借此记录一下xtrabackup的使用用了云之后备份技能都丢了~。生产环境你应该是早就有了xtrabackup的备份做从库时只需要把备份拷过来解压恢复。假设 MySQL 安装目录在/opt/mysqlmy.cnf配置文件/opt/mysql/my.cnf端口3306数据目录/opt/mysql_datasock位于/opt/mysql_data/mysql.sock。备份数据放在/data/backup/mysql/。1. 全量备份$ export BKP_PASSbkppass $ innobackupex --defaults-file/opt/mysql/my.cnf --hostlocalhost --port3306 --userbkpuser --password${BKP_PASS} /data/backup/mysql默认会以当天 日期时间 戳命名备份目录如 2015-09-16_00-00-02。一般会对它进行tar压缩由于tar只能单进程所以往往这个压缩过程会比备份过程耗时2倍还多。拷贝到需要恢复做从库的目录。如果手头有一份未压缩的全备数据要在另一台恢复其实还不如直接 rsync 过来将近400G的数据压缩与解压缩过程特别漫长。2. 全量恢复在恢复的数据库服务器从库上恢复准备 $ innobackupex --use-memory16G --apply-log 2015-09-16_00-00-02 确认数据库是关闭的并且datadir目录下为空 $ innobackupex --defaults-file/opt/mysql/my.cnf --use-memory16G --copy-back 2015-09-16_00-00-02第一步是恢复准备apply-log应用全备时 log sequence number 之后的数据完了后会输出类似 InnoDB: Last MySQL binlog file position 0 262484673, file name ./mysql-bin.000135 的信息告诉我们了后面的从库应该从哪个地方开始复制。时间不会很长但最好用screen之类的软件放到后台执行以免终端断开功亏一篑。第二步使用新的my.cnf文件将完整的mysql数据文件拷贝到datadir下。4. 做从库上面恢复过程最后一步apply-log完成之后会得到一个lsn position 和binlog文件名262484673、mysql-bin.000135。下面开始从库制作。一般在copy-back之后需要修改数据文件目录的属性# chown -R mysql.mysql /opt/mysql_data4.1 my.cnf从库的配置文件简单一点可以从主库拷贝过来但根据需要要注意以下几处server-id一定不能与主库相同否则会出现如下错误Slave: received end packet FROM server, apparent master shutdown从库一般作为只读库使用所以为安全起见设置只读set global read_only1;可以在从服务器的 my.cnf 里加入read-only参数来实现这一点唯一需要注意的一点事read-only仅对没有super权限的用户有效。所以最好核对一下连接从服务器的用户确保其没有super权限。关于从库的事件MYSQL Replication 可以很好的达到你的预期从库的事件不会自己去执行主库会把event执行的结果直接同步。在statement模式下复制的是 event BODY 里的SQL在row模式下是主库事件执行完成后影响的行精确复制。从库 event_scheduler 参数是被忽略的并且每个event 状态会是 SLAVESIDE_DISABLED 但CREATE/ALTER EVENT等操作语句是会复制。主从切换后从库事件状态会变成ENABLE。参数调整从库是不允许写入的否则数据就不一致了。从库实例的配置可以不要主库那么高比如原16G的buffer pool根据用途从库可以设到4-8G当时前提是将来你也不打算把它切换为主库用。相应的read_buffer_sizesort_buffer_size, query_cache_size 这些读相关参数可以略微增大。当然我一般都懒得去改。skip-slave-start主从创建完成后默认情况下次启动从库会自动启动复制进程一般这也正是我们需要的但在维护阶段时你可能不想从库启动后立即开始复制--skip-slave-start选项可以帮到你。log-slave-updates正常情况从库是不需要写回放日志产生的binlog无形中增加服务器压力。但如果你想要实现级联复制即A - B - CB同时是A的从库也是C的主库就需要开启 log-bin 和 log-slave-updates 。另外建议显示设置log-binmysql-bin确保主从正常切换。show variables like log%查看当前值。关于过滤表见[mysql-replica-filter]()sync_binlogFor the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit1 and sync_binlog1 in the master my.cnf file.上面的话同时也意味着性能最低。可以在这埋点假如出现慢的情况把两参数调成2。4.2 启动从库启动数据库注意看日志# /opt/mysql/bin/mysqld_safe --defaults-file/opt/mysql/my.cnf 提示如果你不确定这个库是谁的从库保守起见加上--skip-slave-start启动兴许能防止数据不一致。4.3 change master在从库上$ mysql -uslave_ali -pslave_ali_pass -S /opt/mysql_data/mysql.sock mysql change master to master_hostMASTER_HOST, master_port3306, master_userslave_ali,master_passwordslave_ali_pass, master_log_filemysql-bin.000135, master_log_pos262484673;上面的 master_log_file 和 master_log_pos 即是输出的值也可以在新的数据目录下xtrabackup_binlog_info找到信息。mysql show slave status\G mysql start slave; mysql show slave status\G4.4 验证同步延迟从库执行 show slave status\G节选Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 931 Relay_Log_File: slave1-relay-bin.000056 Relay_Log_Pos: 950 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 931 Relay_Log_Space: 408 Seconds_Behind_Master: 0Master_Log_File I/O线程当前正在读取的主服务器二进制日志文件的名称Read_Master_Log_Pos本机I/O线程读取主服务器二进制日志位置上面2各值与在主库执行show master status;看到的值如果基本接近说明从库IO线程已经赶上了主库的binlog。Relay_Master_Log_File: 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称Exec_Master_Log_Pos: SQL线程执行来自master的二进制日志最后一个事件位置与上面的Relay_Master_Log_File一起同Master_Log_File、Read_Master_Log_Pos比较能看到SQL线程是否已经赶上从库本地的IO线程。Slave_IO_RunningI/O线程是否启动并成功连接到主服务器上一般和下面的Slave_IO_Running和Seconds_Behind_Master一起监控主从健康状态Slave_SQL_RunningSQL线程是否启动Seconds_Behind_Master: 从属服务器“落后”多少秒官网的解释是The number of seconds that the slave SQL thread is behind processing the master binary log。但是当 SBM 为 0 时也不代表一定没有延迟因为可能因为网络慢的缘故从库的IO线程传输binlog太慢它的SQL线程应用日志很容易就赶上relay log但实际主库产生的binlog比传输的快就会造成为0的假象。有时你反复status会发现 Seconds_Behind_Master 的值在0与一个很大的数之间波动有可能是主库上执行了一个非常大的event没执行完毕的时候从库SBM显示为0event执行完成并传输完binlog后就会显示SBM非常巨大。我在从机房迁移mysql到阿里云上部分库老出现这种情况应该跟网络和大event都有关系。另外relay log 中event记录的时间戳是主库上的时间戳而SQL thread的时间戳是从库上的如果主库和从库的时间偏差较大那么这个SBM的意义就基本不存在了。5. 参考高性能Mysql主从架构的复制原理及配置详解How does MySQL Replication really work?XtraBackup不停机不锁表搭建MySQL主从同步实践MySQL复制原理与配置许多模糊的内容还是看官网的