Mysql5.5主从半同步复制配置流程

环境 master:192.169.1.40 slave:192.169.1.41 mysql版本:5.5.20-log master操作 创建slave权限的用户 GRANTreplication slave ON *.* TO 'sla'@'192.169.1.41' IDENTIFIED BY 'sla' WITH GRANT OPTION; 查看服务器的状态或功能是否开启的指令 show status like '%semi%'; show master status; show variables like '%semi%'; 安装半同步复制插件master端 #在mysql命令行状态下操作 mysql>install plugin rpl_semi_sync_mastersoname 'semisync_master.so'; 修改配置文件my.cnf #开启binlog log-bin = mysql-bin #binlog格式:混合。如不设置,默认为SBR模式,在导入含innodb表的数据库时会出错binlog_format=mixed #master一般设置为1 server-id = 1 #开启半同步复制模式,每次重启mysql就不需要手动重新开启半同步复制模式master端rpl_semi_sync_master_enabled = 1 #当slave停机时,master等待6000毫秒(6秒)后,没接收到slave的回应,则切换到异步复制模式 rpl_semi_sync_master_timeout = 6000 #需要同步的数据库 binlog-do-db=db1 binlog-do-db=db2 #需要忽略的数据库 binlog-ignore-db = mysql 重启数据库 此时master需要同步的数据库有任何数据改变,都会被写入binlog 日志里,而且此时的slave还没赶上的话,mysql第一条更新类语句执行时会等待6000毫秒(即上面设置的rpl_semi_sync_master_timeout),然后切换为异步复制模式,直到至少有一台slave赶上才转为半同步复制模式。 可以用show status like '%semi%';查看,其中比较重要的参数: Rpl_semi_sync_master_clients代表有几个slave加入半同步复制模式 Rpl_semi_sync_master_status为on代表已开启半同步复制模式,为off代表已切换为异步复 制模式 Rpl_semi_sync_master_yes_tx在收到slave确认时master提交事务的数量 Rpl_semi_sync_master_no_tx在未收到slave确认时master提交事务的数量 导出数据库 #--master-data=2表示在导出数据库时会打印CHANGE MASTER指令,并加上注释符号。一 般会记录如下信息: #MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; #这个选项还会开启--lock-all-tables,在完成时系统会自动关闭--lock-tables /usr/local/mysql/bin/mysqldump -ubackup --master-data=2 db1> /home/BACKUP/mysqldata/db1.sql & /usr/local/mysql/bin/mysqldump -ubackup --master-data=2 db2> /home/BACKUP/mysqldata/db2.sql & 传输sql文件到slave rsync-v--progress/home/BACKUP/mysqldata/*.sql192.169.1.41::data/ 前提需要在192.169.1.41配置rsync,并启动rsync --daemon vi /etc/rsyncd.conf uid = user gid = user usechroot = false strict modes = false usechroot = no max connections = 4 log file = /var/log/rsyncd.log pid file = /dev/shm/rsyncd.pid lock file = /dev/shm/rsync.lock [data] path = /home/BACKUP read only = false uid = user gid = user hosts allow = 192.169.1.40 hosts deny = 0.0.0.0/0 comment = Web App ignore errors Slave操作 导入数据库 /usr/local/mysql/bin/mysql -ubackupdb1 SET SESSION binlog_format = 'STATEMENT'; mysql> SET SESSION binlog_format = 'ROW'; mysql> SET SESSION binlog_format = 'MIXED'; 两种模式各自的优缺点: SBR 的优点: 历史悠久,技能成熟 binlog文件较小 binlog中包含了所有数据库修改信息,可以据此来审核数据库的安全等情况binlog可以用于实时的还原,而不仅仅用于复制 主从版本可以不一样,从服务器版本可以比主服务器版本高 SBR 的缺点: 不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。 调用具有不确定因素的UDF 时复制也可能出疑问 运用以下函数的语句也不能被复制: * LOAD_FILE() * UUID() * USER() * FOUND_ROWS() * SYSDATE() (除非启动时启用了–sysdate-is-now 选项) INSERT … SELECT 会产生比RBR 更多的行级锁 复制须要执行全表扫描(WHERE 语句中没有运用到索引)的UPDATE 时,须要比RBR 请求更多的行级锁 对于有AUTO_INCREMENT 字段的innodb表而言,INSERT 语句会阻塞其他INSERT 语句 对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而RBR 模式下,只会对那个发生变化的记录产生影响 存储函数(不是存储流程)在被调用的同时也会执行一次NOW() 函数,这个可以说是坏事也可能是好事 确定了的UDF 也须要在从服务器上执行 数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错 执行复杂语句如果出错的话,会消耗更多资源 RBR 的优点: 任何情况都可以被复制,这对复制来说是最安全可靠的 和其他大多数数据库系统的复制技能一样 多数情况下,从服务器上的表如果有主键的话,复制就会快了很多 复制以下几种语句时的行锁更少: * INSERT … SELECT * 包含AUTO_INCREMENT 字段的INSERT * 没有附带条件或者并没有修改很多记录的UPDATE 或DELETE 语句 执行INSERT,UPDATE,DELETE 语句时锁更少 从服务器上采用多线程来执行复制成为可能 RBR 的缺点: binlog大了很多 复杂的回滚时binlog中会包含大量的数据 主服务器上执行UPDATE 语句时,所有发生变化的记录都会写到binlog中,而SBR 只会写一次,这会导致频繁发生binlog的并发写疑问 UDF 产生的大BLOB 值会导致复制变慢 不能从binlog中看到都复制了写什么语句(加密过的) 当在非事务表上执行一段堆积的SQL语句时,最好采用SBR 模式,否则很容易导致主从服务器的数据不一致情况发生 另外,针对系统库mysql里面的表发生变化时的处理准则如下: 如果是采用INSERT,UPDATE,DELETE 直接操作表的情况,则日志格式根据 binlog_format的设定而记录 如果是采用GRANT,REVOKE,SET PASSWORD 等管理语句来做的话,那么无论如何 都采用SBR 模式记录。 注:采用RBR 模式后,能处理很多原先出现的主键重复问题。实例: 对于insert into db_allot_ids select * from db_allot_ids这个语句: 在BINLOG_FORMAT=STATEMENT 模式下: BINLOG日志信息为: —————————————– BEGIN /*!*/; # at 173 #090612 16:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1244793942/*!*/; insert into db_allot_ids select * from db_allot_ids /*!*/; —————————————– 在BINLOG_FORMAT=ROW 模式下: BINLOG日志信息为: —————————————– BINLOG ' hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRz AAIBAwAA hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEA AAD8AQEAAAA= '/*!*/; 日常维护(谨慎重启服务器),如reset master; 有时候因为主服务器的更新过于频繁,造成了从服务器更新速度较慢,当然问题是多种多样,有可能是网络搭建的结构不好或者硬件的性能较差,从而使得主从服务器之间的差距越来越大,最终对某些应用产生了影响,在这种情况下,我们需要定期进行主从服务器的数据同步,具体步骤如下在主服务器上 mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.03 sec) mysql> show master status\G; ***************************1. row *************************** File: mysql-bin.000004 Position: 102 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) 记录出日志的名字和偏移量,这些是从服务器复制的目的目标 在从服务器上,使用master_POS_WAIT()函数得到复制坐标值 mysql>select master_pos_wait('mysql-bin.000004','102'); +-------------------------------------------+ | master_pos_wait('mysql-bin.000004','102') | +-------------------------------------------+ |0| +-------------------------------------------+ 1 row in set (0.00 sec) 这个select 语句会阻塞直到从服务器达到指定日志文件和偏移量后,返回0,如果是-1,则表示超时推出,查询是0时,表示从服务器与主服务器已经同步 Mysqlbinlog日志清理 手动清理binlog 查看所有binlog日志 show binary logs; 查看指定binlog的事件内容 showbinlog events in 'mysql-bin.000010'; 清理mysql-bin.000010之前的日志文件 purge binary logs to 'mysql-bin.000010'; 设置自动清理binlog, 配置my.cnf: expire_logs_days = 7 或者在运行时修改: show binary logs; show variables like '%log%'; set global expire_logs_days = 7; 跳过错误 slave stop; SET GLOBAL sql_slave_skip_counter = 1; slave start; show slave status\G 临时表和内存表 1.临时表:建在内存里,数据在内存里 2.内存表:表建在磁盘里,数据在内存里 做mysql主从复制的时候,mysql可以同步内存表,但是不能同步临时表!