mysql主从配置定义及流程讲析


下文内容主要给大家带来mysql主从配置定义及流程讲析,这里所讲到的知识,与书籍略有不同,都是开发云专业技术人员在与用户接触过程中,总结出来的,具有一定的经验分享价值,希望给广大读者带来帮助。Mysql主从同步的配置主库IP:192.168.1.2从库IP:192.168.1.3添加一个用于主从同步的用户:GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’ IDENTIFIED BY‘1q2w3e4r’;如果监控mysql主从的话,请加上一个super权限:GRANT SUPER, REPLICATION SLAVE ON *.* TO ‘repl’@’%’ IDENTIFIED BY ‘1q2w3e4r’;1、主库的配置1.1.mysql5.0以下版本的配置修改主库mysql配置配置文件,在[mysqld]段添加以下内容:server-id = 1log-bin=/home/mysql/logs/binlog/bin-logmax_binlog_size = 500Mbinlog_cache_size = 128Kbinlog-do-db = adbbinlog-ignore-db = mysqllog-slave-updates1.2. mysql5.0以上版本的配置修改主库mysql配置配置文件,在[mysqld]段添加以下内容:server-id = 1log-bin=/home/mysql/logs/binlog/bin-logmax_binlog_size = 500Mbinlog_cache_size = 128Kbinlog-do-db = adbbinlog-ignore-db = mysqllog-slave-updatesexpire_logs_day=2binlog_format=”MIXED”1.3.各个参数的含义和相关注意项:server-id = 1#云服务器标志号,注意在配置文件中不能出现多个这样的标识,如果出现多个的话mysql以第一个为准,一组主从中此标识号不能重复。log-bin=/home/mysql/logs/binlog/bin-log#开启bin-log,并指定文件目录和文件名前缀。max_binlog_size = 500M#每个bin-log最大大小,当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。binlog_cache_size = 128K #日志缓存大小binlog-do-db = adb#需要同步的数据库名字,如果是多个,就以此格式在写一行即可。binlog-ignore-db = mysql #不需要同步的数据库名字,如果是多个,就以此格式在写一行即可。log-slave-updates #当Slave从Master数据库读取日志时更新新写入日志中,如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。expire_logs_day=2#设置bin-log日志文件保存的天数,此参数mysql5.0以下版本不支持。binlog_format=”MIXED” #设置bin-log日志文件格式为:MIXED,可以防止主键重复。2、从库的配置2.1.mysql5.1.7以前版本修改从库mysql配置配置文件,在[mysqld]段添加以下内容:server-id=2master-host=192.168.1.2master-user=replmaster-password=1q2w3e4rmaster-port=3306master-connect-retry=30slave-skip-errors=1062replicate-do-db = adbreplicate-ignore-db = mysqlslave-skip-errors=1007,1008,1053,1062,1213,1158,1159master-info-file = /home/mysql/logs/master.inforelay-log = /home/mysql/logs/relay-binrelay-log-index = /home/mysql/logs/relay-bin.indexrelay-log-info-file = /home/mysql/logs/relay-log.info如果修改了连接主库相关信息,重启之前一定要删除master.info文件,否则重启之后由于连接信息改变从库而不会自动连接主库,造成同步失败。此文件是保存连接主库信息的。2.2.mysql5.1.7以后版本Mysql5.1.7版本在丛库上面的配置很少,主要是采用了新的同步信息记录方式,他不在支持在配置文件中配置连接主库的相关信息,而是把连接等相关信息记录在master-info-file = /home/mysql/logs/master.info文件中,如果入库变了,直接在mysql命令行执行连接信息的改变即可生效,比较灵活了,而不用去重启mysql。修改从库mysql配置配置文件,在[mysqld]段添加以下内容:slave-skip-errors=1007,1008,1053,1062,1213,1158,11592.3.各个参数的含义和相关注意项这里只讲一下2个参数,其他全部是从库连接主库的信息和中间日志relay-log的设置。master-connect-retry=30 #这个选项控制重试间隔,默认为60秒。slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 #这个是在同步过程中忽略掉的错误,这些错误不会影响数据的完整性,有事经常出现的错误,一般设置忽略。其中1062为主键重复错误。3、实现主从同步3.1.实现数据库的统一检查主从数据库的配置文件,查看是否已正确配置。首次实现 同步要备份主库上需要同步的数据库,然后完整的导入到从库中。注:mysql5.0之前的版本涉及到mysql本身复制过滤存在问题,需要把所有的数据库都备份导入到丛库,保持。3.2.查看并记录主库bin-log信息进入主库mysql中,执行:show master status;显示信息如下:mysql> show master status;+————-+———-+————–+——————+| File | Position | Binlog_do_db | Binlog_ignore_db |+————-+———-+————–+——————+| bin-log.003 | 4 | adb | mysql |+————-+———-+————–+——————+1 row in set (0.00 sec)记录File 和Position信息;3.3.在从库上执行同步语句进入mysql,执行以下语句:slave stop;change master tomaster_host=’192.168.1.2′,master_user=’repl’,master_password=’1q2w3e4r’,master_port=3306,master_log_file=’bin-log.003′,master_log_pos=4;slave start;3.4.查看主从同步状态进入mysql,执行show slave statusG;显示如下(mysql版本不同查询的结果不同,但是重要的指标还是一样的):Mysql5.0之前的版本如下:Mysql5.5之前的版本如下:Mysql5.5的版本如下:重要的指标为:Slave_IO_Running: YesSlave_SQL_Running: YesMaster_Log_File: bin-log.003Relay_Master_Log_File: bin-log.003Read_Master_Log_Pos: 4Exec_master_log_pos: 4Seconds_Behind_Master: 0(5.0之前版本没有这个选项)以上选项是两两对应的,只要结果是一致的,就说明主从同步成功。3.5.同步中的常见的错误和处理1、现象:在从库上面show slave statusG;出现下列情况, Slave_IO_Running: Yes Slave_SQL_Running: No Seconds_Behind_Master: NULL原因:a.程序可能在slave上进行了写操作;b.也可能是slave机器重起后,事务回滚造成的;c.有可能是在同步过程中遇到某种错误,这个会在查看从库中状态时看到错误提示,最少见的就是主键重复1062的错误。解决方法:进入mastermysql> show master status;+———————-+———-+————–+——————+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+———————-+———-+————–+——————+| mysql-bin.000040 | 324 |adb | mysql|+———————-+———-+————–+——————+然后到slave服务器上执行手动同步slave stop;change m开发云主机域名aster tomaster_host=’10.14.0.140′,master_user=’repl’,master_password=’1q2w3e4r’,master_port=3306,master_log_file=’mysql-bin.000040′,master_log_pos=324;slave start;show slave statusG;2、现象:从数据库无法同步,show slave status显示: Slave_IO_Running: No Slave_SQL_Running: Yes Seconds_Behind_Master: NULL解决:首先查看数据库的err日志,查看是什么错误提示,看从库连接主库的IP、用户、密码等相关信息是否有误,如果有误,重新执行同步;如果确认无误,重启主数据库。mysql> show master status;+——————+———-+————–+——————+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+——————+———-+————–+——————+| mysql-bin.000001 | 98 | adb| mysql|+——————+———-+————–+——————+进入从库mysql,执行:slave stop;change master to Master_Log_File=’mysql-bin.000001′,Master_Log_Pos=98;slave start;或是这样:stop slave;set global sql_slave_skip_counter =1;start slave;这个现象主要是master数据库存在问题,由于连接主库信息错误、主库数据库挂掉如果说常见错等原因引起的,我在实际的操作中先重启master后重启slave即可解决这问题,出现此问题,必须要要重启master数据库。四、mysql主主和主主集群1、mysql主主的实现在实际的生产应用中,为了在主库出现崩溃或是主服务器出现严重故障时快速的恢复业务,会直接切换到从库上,当主库故障处理完成后让他直接作为丛库来运行,此时主主就是一个不错的选择。五、mysql主从的监控在mysql主从的应用中,只要进行了合理设置,基本上不会出现问题,但是对他的监控是必不可少的,以免由于真的出现问题又不知道而造成不必要的数据损失。1、mysql主从监控的主要思路Mysql主从的监控,其主要是监控从库上的一些重要参数:Slave_IO_Running: YesSlave_SQL_Running: YesMaster_Log_File: bin-log.003Relay_Master_Log_File: bin-log.003Read_Master_Log_Pos: 4Exec_master_log_pos: 4Seconds_Behind_Master: 0(5.0之前版本没有这个选项)通过以上的参数可以反映出主库和从库状态是否正常,从库是否落后于主库等。值得一提的是在mysql5.0以前的版本,Slave_IO_Running这个状态指标不可靠,会在主库直接挂掉的情况下不会变成NO,Seconds_Behind_Master参数也不存在。监控以上参数即可监控mysql主从。2、mysql主从监控的实现不管mysql是那个版本,其中的从库上的Exec_master_log_pos、Exec_master_log_pos;主库上的 Master上的Log_File,Position,这四个参数可以判断出当前主从的状态。以下是适用于mysql所有版本的主从监控shell脚本:#/bin/shuser=replpasswd=123415master_ip=”192.168.1.2″log=”/data3/check_repl.log”value(){master=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h$master_ip -e “show master statusG;”|egrep “File|Position”`#mysql 4.0slave=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h227.0.0.1 -e “show slave statusG;”|egrep “Relay_Master_Log_File|Exec_master_log_pos”`#mysql 5.0#slave=`mysql -u$user -p$passwd -e “show slave statusG;”|egrep “Relay_Master_Log_File|Exec_Master_Log_Pos”`#取主库上的bin-log号及写入的当前日志位置Master_Log=`echo $master |awk ‘{print $2}’|awk -F “.” ‘{print $2}’`Master_Log_Pos=`echo $master |awk ‘{print $4}’`#取从库上当前同步主库的位置Relay_Master_Log_File=`echo $slave |awk ‘{print $2}’|awk -F “.” ‘{print $2}’`Exec_Master_Log_Pos=`echo $slave |awk ‘{print $4}’`echo “Master_Log:”$Master_Log>>$logecho “Master_Log_Pos:”$Master_Log_Pos>>$logecho “Relay_Master_Log_File:”$Relay_Master_Log_File>>$logecho “Exec_Master_Log_Pos:”$Exec_Master_Log_Pos>>$log}for((i=1;idoecho “#################################”>>$logvaluetime=`date +”%Y-%m-%d %H:%M:%S”`if [ $Master_Log -eq $Relay_Master_Log_File ];then A=`expr $Master_Log_Pos – $Exec_Master_Log_Pos` if [ $A -lt 0 ];then A=`expr 0 – $A` fi echo $A>>$log if [ $A -lt 10000 ];then echo “$time Master-Slave is OK.”>>$log #echo “$i” break else if [ $i ge 3 ];then echo “$time Warning:Slave-Master lag $A ” >>$log echo “$i” fi sleep 30 continue fielse sleep 60 fi if [ $i -eq 10 ];then echo “$i” echo “$timeError:Slave-Master must be check !” >>$log fidone在mysql5.0以后的版本,mysql主从已经相当的成熟了,可以只监控Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master状态就可以了,这里不再做说明。对于以上关于mysql主从配置定义及流程讲析,如果大家还有更多需要了解的可以持续关注我们开发云的行业推新,如需获取专业解答,可在官网联系售前售后的,希望该文章可给大家带来一定的知识更新。

相关推荐: MySQL中or、in、union与索引优化详析

本文缘起自《一分钟了解索引技巧》的作业题。假设订单业务表结构为:其中:假设订单有三种状态:0已下单,1已支付,2已完成业务需求,查询未完成的订单,哪个SQL更快呢?结论:方案1最慢,方案2,3,4都能命中索引但是…一:union all 肯定是能够命中索引…

免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 06/07 17:12
下一篇 06/07 17:12

相关推荐