MySQL主从复制介绍

MySQL的主从复制是一个异步的复制过程(虽然一般情况下感觉是实时的),数据将从一个MySQL数据库(我们称之为Master)复制到另一个MySQL数据库(我们称之为Slave),在MasterSlave之间实现整个主从复制的过程是由三个线程参与完成的,其中有两个线程(SQL线程和IO线程)在Slave端,另外一个线程(I/O线程)在Master端。要实现MySQL的主从复制,首先必须打开Master端的binlog记录功能,否则就无法实现。因为整个复制过程实际上就是SlaveMaster端获取binlog日志,然后在Slave上以相同顺序执行获取的binlog日志中所激励的各种SQL操作。 要打开MySQLbinlog记录功能,可通过在MySQL的配置文件my.cnfmysql模块([mysql]标识后的参数部分)增加“log-bin”参数选项来实现,具体信息如下:[mysqld]log-bin = /data/3306/mysql-bin 下面针对MySQL主从复制原理的重点进行小结。 主从复制是异步的逻辑的SQL语句级的复制。 复制时,主库有一个I/O线程,从库有两个线程,I/OSQL线程。 实现主从复制的必要条件是主库要开启记录binlog功能。 作为复制的所有MySQL节点的server-id都不能相同。 binlog文件只记录对数据有更改的SQL语句(来自主数据库内容的变更),不记录任何查询(selectshow)语句。忘了数据库密码mysqld_safe –defaults-file=/data/3306/my.cnf–skip-grant-table –user=mysql & 放后台运行然后不用输入密码进行登录mysql -uroot -p -S /data/3306/mysql.sock进入数据库后设置密码update mysql.user setpassword=password(‘oldboy123′) where user=’root’ and host=’localhost’;刷新权限flush privileges;环境:多实例10.0.0.52 330610.0.0.52 33073306—->3307复制—->3309—->3008复制3306—->3307架构实践3306—–>3307[root@db02 ~]# egrep -i”server-id|log-bin” /data/3306/my.cnflog-bin = /data/3306/mysql-binserver-id = 6重启服务/data/3306/mysql restart从库[root@db02 ~]# egrep -i”server-id|log-bin” /data/3307/my.cnf#log-bin = /data/3307/mysql-binserver-id = 7grant replication slave on *.* to ‘rep’@’172.16.1.%’identified by ‘oldboy123’;mysql> grant replication slave on *.* to’rep’@’172.16.1.%’ identified by ‘oldboy123’;Query OK, 0 rows affected (0.04 sec)mysql> select user,host from mysql.user;+——+————+| user | host|+——+————+| root | 127.0.0.1|| rep |172.16.1.% |按照我们见过的内容,直接取今天00点的备份就可以1.先锁表flush table with read lock;mysql> flush table with read lock;Query OK, 0 rows affected (0.00 sec)mysql> show master status;+——————+———-+————–+——————+——————-+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+——————+———-+————–+——————+——————-+| mysql-bin.000001 | 120 | | | |+——————+———-+————–+——————+——————-+1 row in set (0.00 sec)2.主库全备全备三个重要命令mysqldumpcp/tarxtrabackup拿到位置点是关键 sed-n ’22p’ all_2017-06-28.sq开发云主机域名l[root@db02 ~]#mysqldump -B –master-data=2 –single-transaction -S /data/3306/mysql.sock -A|gzip>/data/backup/all_$(date+%F).sql.gz[root@db02 ~]# ls -l /data/backup/总用量 228-rw-r–r– 1 rootroot 178468 6 28 11:11 all_2017-06-28.sql.gz3.主库解锁mysql> unlock table;Query OK, 0 rows affected (0.00 sec)[root@db02 scripts]# cd/data/backup/[root@db02 backup]# gzip-d all_2017-06-28.sql.gz [root@db02 backup]#mysql -S /data/3307/mysql.sock [root@db02 backup]# sed-n ’22p’ all_2017-06-28.sql— CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001’, MASTER_LOG_POS=720;在从库3307添加:CHANGE MASTER TOMASTER_HOST=’172.16.1.52′,MASTER_PORT=3306,MASTER_USER=’rep’,MASTER_PASSWORD=’oldboy123′,MASTER_LOG_FILE=’mysql-bin.000001′,MASTER_LOG_POS=720;打开复制开关slavemysql> start slave;Query OK, 0 rows affected (0.03 sec)mysql> show slave statusG显示如下结果证明主从复制实践成功[root@db02 backup]# mysql -S /data/3307/mysql.sock -e “show slavestatusG”|egrep “_Running|Behind_Master”|head -3Slave_IO_Running:YesSlave_SQL_Running:YesSeconds_Behind_Master: 0#######################################################################################Slave_IO_Running:Yes,这个是I/O线程状态,I/O线程负责从从库去主库读取binlog日志,并写入从库的中继日志,状态为Yes表示I/O线程工作正常。
Slave_SQL_Running:Yes,这个是SQL线程状态,SQL线程负责读取中继日志(relay-log)中的数据并转化为SQL语句应用到从数据库中,状态为Yes表示I/O线程工作正常。
Seconds_Behind_Master:0,这个是在复制的过程中,从库比主库延迟的秒数,这个参数很重要,但企业里更准确地判断主从延迟的方法为:在主库写时间戳,然后从库读取时间戳和当前数据库时间的进行比较,从而认定是否延迟。mysql> showslave hosts;+———–+——+——+———–+————————————–+| Server_id | Host | Port | Master_id |Slave_UUID |+———–+——+——+———–+————————————–+| 7| | 3307 | 6 |295750c8-54c1-11e7-80dd-000c29fc02ee || 8| | 3308 | 6 | 328e8c80-54c1-11e7-80dd-000c29fc02ee|+———–+——+——+———–+————————————–+2 rows in set (0.00 sec) 故障1:主库show master status;没返回状态结果。mysql> show master status;Empty set (0.00 sec) 解答:上述问题原因是binlog功能开关没开或没生效。binlog功能开启正确的配置结果如下:[root@db02 ~]# grep “log-bin”/data/3306/my.cnflog-bin = /data/3306/mysql-bin[root@db02 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e “showvariables like ‘log_bin’;”Warning: Using a password on the command lineinterface can be insecure.+—————+——-+| Variable_name | Value |+—————+——-+| log_bin| ON |+—————+——-+故障二:出现错误信息Last_IO_Error:Got fatal error 1236 from master when reading datafrom binary log‘Could notfind first log file name in binary log index file’解答:上面故障的原因是执行CHANGE MASTER命令时某一个参数的值多了个空格,因而产生错误,如下:CHANGE MASTER TOMASTER_HOST=’172.16.1.52′,MASTER_PORT=3306,MASTER_USER=’rep’,MASTER_PASSWORD=’oldboy123′,MASTER_LOG_FILE= mysql-bin.000001 ‘, #内容的两端不能有空格。MASTER_LOG_POS=120;故障三:服务无法启动。故障语句如下:[root@db02 ~]# /data/3306/mysql startMySQL is running…[root@db02 ~]# ps -ef |grep mysql 发现没有服务端口号root1271 1234 0 08:36 pts/0 00:00:00 grep mysql解决:原因是启动脚本里对mysql.sock是否存在做了判断,如果存在mysql.sock,就认为服务运行是个小bug,读者可以自行更改启动脚本解决。[root@db02 ~]# rm -f /data/3306/mysql.sock /data/3306/*.pid[root@db02 ~]# /data/3306/mysql startStarting MySQL…[root@db02 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock 登录[root@db02 ~]# cat /data/3306/oldboy_3306.err 出现问题看日志步骤如下:1.安装好要配置从库的数据库,配置好log-binserver-id参数。2.无需配置主库my.cnf文件,主库的log-binserver-id参数默认就是配好的3.登录主库,增加从库连接主库同步的账户,例如rep,并授权replicationslave同步的权限。4.使用曾经在半夜通过mysqldump-x–master-data=1的命令及茶树定时备份的全备数据备份文件,把它恢复到从库。5.在从库执行change master to…..语句,无需binlog文件及对应位置点。6.从库开启同步开关,start slave7.从库show slave statusG, 检查同步状态,并在主库进行更新测试实战过程如下:1)在主库上通过定时任务执行如下命令,备份导出主库数据:mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -A –events -B -x –master-data=1|grep >/opt/$(date +%F).sql.gz–master-data=1参数会在备份数据里增加如下语句;– position to start replication or point-in-timerecovery fromchange master tomaster_log_file=’mysql-bin.000005′,master_log_pos=107;2) 找机会在需要做复制的从库上导入全备做从库,命令如下:gzip -d 2017-07-08.sql.gzmysql -uroot -poldboy123 -S /data/3308/mysql.sock
mysql -uroot -poldboy123 -S /data/3308/mysql.sock
CHANGEMASTER TOMASTER_HOST=’172.16.1.52′,MASTER_PORT=3306,MASTER_USER=’rep’,MASTER_PASSWORD=’oldboy123′,EOF 这里的change master后面无需指定binlog文件名及具体位置,因为这部分已经在还原数据时提前应用到数据库里了(备份时–master-data=1的功劳)start slave#开启主从复制开关show slave statusG #查看主从复制状态1)登录主数据库查看MySQL线程的同步状态命令如下:mysql> show processlistG*************************** 1. row*************************** Id: 7 User: rep Host:10.0.0.52:27306 db:NULLCommand: Binlog Dump Time: 538 State:initState:Master has sent all binlog to slave;waiting for binlog to beupdatedInfo: NuLL1 row in set (0.00 sec)提示:上述状态的意思是线程已经从binlog日志读取所有更新,并已经发送到了从数据库服务器。线程目前为空闲状态,等待由主服务器上二进制日子中的新事件更新。 下表列出了主服务器的binlog Dump线程中State列的最常见状态。如果你没有在主服务器上看见人和我binlog Dump线程,这说明复制没有运行,二进制binlog日志由各种事件组成,事件通常会为更新添加新加信息。 主库I/O线程工作状态 主库I/O线程工作状态 解释说明Sending binlog event to slave线程已经从二进制binlog日志读取了一个事件并且正将它发送到从服务器Finnished reading one binlog;swithchingto next binlog线程已经读完二进制binlog日志文件,并且正打开下一个要发送到从服务器的binlog日志文件Has sent all binlog to slave;waiting for binlog to be updated线程已经从binlog日志读取所有更新并已经发送到了从数据库服务器,线程现在为空闲状态,等待由主服务器上二进制binlog日志中的新事件更新Waiting to finalize termination线程停止时发生的一个很简单的状态2)登录从库数据库查看MySQL线程工作状态从库有两个线程,即I/OSQL线程。从库I/O线程的状态如下:mysql> show processlistG*************************** 1. row*************************** Id: 1 User:system user Host: db:NULLCommand: Connect Time: 36 State:Waiting for master to send event Info:NULL下表列出了从服务器的I/O线程的state列的最常见的状态,该状态也出现在Slave_IO_State列,由SHOW SLAVE STATUS显示 从库IO线程工作状态从库I/O线程工作状态 解释说明Connecting to master线程正试图连接主服务器Checking master version同主服务器之间建立连接后临时出现的状态Registering slave on masterRequesting binlog dump建立同主服务器之间的连接后立即临时出现的状态,线程向主服务器发送一条请求,索取从请求的二进制binlog日志文件名和位置开始的二进制binlog日志的内容 Waiting to reconnect after a failed binlog dump request如果二进制binlog日志转储请求失败,线程进入睡眠状态,然后定期尝试重新连接。可以使用–master-connect-retry选项指定重试之间的间隔Reconnecting after a failed binlog dump线程正尝试重新连接主服务器 从库SQL线程状态从库SQL线程状态 解释说明Reading all relay log线程已经从中继日志读取一个事件,可以对事件进行处理了Has read all relay log;waiting for the slave I/O thread to update it线程已经处理了中继日志文件中的所有事件,现在正等待I/O线程将新事件写入中继日志Waiting for slave mutex on exit线程停止时发生的一个很简单的状态 有关MySQL主从复制参与线程的状态更多信息,请参考MySQL官方手册。 通过MySQL线程同步状态可以看到同步是否正常进行,故障的位置是什么,另外还可查看数据库同步是否完成,可用于主库宕机切换数据库或者人工数据库主从切换迁移等。 例如:主库宕机,要选择最快的从库将其提升为主库,就需要查看主从库的线程状态,如果主从复制在正常情况下进行角色切换,也需要查看主从库的线程状态,根据复制状态确定更新是否完成。

相关推荐: SQL 增加列、修改列、删除列

SQL语句增加列、修改列、删除列1.增加列:alter table tableName add columnName varchar(30)2.1. 修改列类型:alter table tableName alter column columnName var…

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

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 06/06 20:36
下一篇 06/06 20:36

相关推荐

发表评论

您的电子邮箱地址不会被公开。