mysql主从日常管理维护

mysql主从日常管理维护1）查看从服务器状态为了防止复制过程中出现故障从而导致复制进程停止，我们需要经常检查从服务器的复制状态。一般使用showslavestatus命令来检查。例如：mysqlshowslavestatus\G;***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.1.109Master_User:mysqlMaster_Port:3306Connect_Retry:60Master_Log_File:mysql3306-bin.000024Read_Master_Log_Pos:438065348Relay_Log_File:mysql-relay.000751Relay_Log_Pos:438065489Relay_Master_Log_File:mysql3306-bin.000024Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:mysql,testReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:438065348Relay_Log_Space:438065489Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:01rowinset(0.00sec)ERROR:Noqueryspecified在显示的这些信息中，我们主要关心Slave_IO_Running和Slave_SQL_Running这两个进程状态是否是“yes”，这两个进程的含义分表如下：Slave_IO_Running：此进程负责从服务器（slave）从主服务器（master）上读取BINLOG日志，并写入从服务器上的中继日志中。Slave_SQL_Running：此进程负责读取并且执行中继日志中的BINLOG日志。只要其中有一个进程是no，则表示复制进程停止，错误原因可以从“Last_Errno”字段的值中看到。除了查看上面的信息，用户还可以通过这个命令了解从服务器的配置情况以及当前和主服务器的同步情况，包括指向那个主服务器，主服务器的端口，复制使用的用户，当前日志恢复到位置等。这些信息都是记录在从服务器这一端的，主服务器上并没有相应的信息。2）主从服务器同步维护在某些繁忙的OLTP（在线事务处理）系统上，由于主服务器更新频繁，而从服务器由于各种原因（比如硬件性能较差）导致更新速度较慢，从而使得主从服务器之间的数据差距越来越大最终对某些应用产生影响。在这种情况下，我们需要定期的进行主从服务器的数据同步，使得主从数据差距能减到最小。常用的方法是：在负责较低的时候暂时阻塞主数据库的更新，强制主从数据库更新同步。具体步骤如下：1.在主服务器上，执行以下语句（注意，会阻塞主数据库的所有更新操作）Mysqlflushtableswithreadlock;Mysqlshowmasterstatus\G;记录show语句的输出的日志名和偏移量，这些都是从服务器复制的目的坐标。2.在从服务器上，执行下面语句，其中master_pos_wait()函数的参数是前面步骤中得到的复制坐标值。Mysqlselectmaster_pso_wait('mysql-bin.0001','974');这个select语句会阻塞直到从服务器达到指定的日志文件和偏移量后，返回0，如果返回-1，则表示超时退出。查询返回0时，则从服务器与主服务器同步。3.在主服务器上，执行下面的语句允许主服务器重新开始处理更新Mysqlunlocktables;1、在主上flushtableswithreadlock;2、在从上slavestop3、在主上showmasterstatus4、在从上CHANGEMASTERTOMASTER_HOST='192.168.1.109',Master_User='mysql',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql3306-bin.000002',MASTER_LOG_POS=98;5、从上slavestart6、主上unlocktables;3）从服务器复制出错的处理从服务器更新失败时，首先需要确定是否是从服务器的表与主服务器的不同造成的。如果是表结构不同导致的，则修改从服务器的表与主服务器的相同，然后重新运行startslave语句。如果不是表结构不同导致的更新失败，则需要确认手动更新是否安全，然后护士来之主服务器的更新失败的语句。跳过来自主服务器的命令：SETGLOBALSQL_SLAVE_SKIP_COUNTER=n,其中n的取值为1或者2。如果来自主服务器的更新语句不使用AUTO_INCREMENT或LAST_INSERT_ID()，n值应为1，否则，值为2.原因是使用AUTO_INCREMENT或LAST_INSERT_ID()的语句需要从二进制日志中取两个事件。以下例子就是在从服务器端牟尼跳过主服务器的两个更新语句的效果。1.首先在从服务器上停止slave线程Mysqlstopslave;MysqlSETGLOBALSQL_SLAVE_SKIP_COUNTER=22.然后在主服务器上插入数据3.从服务器端启动复制进程，检查测试的表，首先发现插入的两条记录呗跳过了，只执行了第3条插入语句。4）logevententryexceededmax_allowed_packet的处理如果应用中使用大的BLOG列或者长字符串，那么在从服务器上恢复的时候，可能出现“logevententryexceededmax_allowed_packet”错误，这个因为含有大文本的记录无法通过网络进行传输导致。解决的办法就是在主从服务器上增加max_allowed_packet参数的大小，这个参数的默认值是1MB，可以按照实际需要进行修改，比如下列中将其增大为512MB。mysqlshowvariableslike'max_allowed_packet';+--------------------+-----------+|Variable_name|Value|+--------------------+-----------+|max_allowed_packet|536870912|+--------------------+-----------+1rowinset(0.00sec)可以在配置文件中写入[mysqldump]quickmax_allowed_packet=512M5）多主复制时的自增长变量冲突问题在大多数情况下，一般只使用单主复制（一台主服务器对一台或者多台从服务器）。但是在某些情况下，可能会需要使用多主复制（多台主服务器对一台从服务器）。这个时候，如果主服务器的表采用自动增长变量，那么复制到从服务器的同一张表后很可能会引起主键冲突，因为系统参数auto_increment_increment和auto_increment_offset默认值为1，这样多台主服务器的自增变量列迟早会发生冲突。在单数复制时，可以采用默认设置，不会有主键冲突发生。但是使用多主复制时，就需要定制auto_increment_increment和auto_increment_offset的设置，保证多主之间复制到从数据库不会有重复冲突，比如两个master的情况可以按照以下设置：Master1：auto_increment_increment=2，auto_increment_offset=1：（1,3,5,7………)Master2:auto_increment_increment=2,auto_increment_offset=0(0,2,4,6……..)查看：showvariableslike'auto_inc%';6）查看从服务器的复制进度很多情况下，我们都想知道从服务器复制的进如何。知道了这个差距，可以帮助我们判断是否需要手工来做主从的同步工作，也可以帮助我们判断从服务器上做统计的数据精度如果。这个值可以通过showprocesslist中的slave_SQL_running线程的time值得到，它记录了从服务器当期执行sql时间戳与系统时间之间的差距，单位是秒。mysqlshowprocesslist\G;***************************1.row***************************Id:1User:systemuserHost:db:NULLCommand:ConnectTime:4636437State:WaitingformastertosendeventInfo:NULL***************************2.row***************************Id:2User:systemuserHost:db:NULLCommand:ConnectTime:2这个time值就是最后执行的复制操作大概是在主服务器上2秒钟前得更新State:Hasreadallrelaylog;waitingfortheslaveI/OthreadtoupdateitInfo:NULL***************************3.row***************************Id:625916User:rootHost:localhostdb:NULLCommand:QueryTime:0State:NULLInfo:showprocesslist3rowsinset(0.00sec)ERROR:Noqueryspecified