MySQL主从复制描述
发布时间：2023-07-25 14:18:51 所属栏目：MySql教程 来源：
导读：MySQL主从复制介绍
1.1 MySQL主从复制原理介绍
MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Sl
1.1 MySQL主从复制原理介绍
MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Sl
|
MySQL主从复制介绍 1.1 MySQL主从复制原理介绍 MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Slave），在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的，其中有两个线程（SQL线程和IO线程）在Slave端，另外一个线程（I/O线程）在Master端。 要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后在Slave上以相同顺序执行获取的binlog日志中所激励的各种SQL操作。 要打开MySQL的binlog记录功能，可通过在MySQL的配置文件my.cnf的mysql模块（[mysql]标识后的参数部分）增加“log-bin”参数选项来实现，具体信息如下： [mysqld] log-bin = /data/3306/mysql-bin MySQL主从复制介绍 下面针对MySQL主从复制原理的重点进行小结。 ◆ 主从复制是异步的逻辑的SQL语句级的复制。 ◆ 复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程。 ◆ 实现主从复制的必要条件是主库要开启记录binlog功能。 ◆ 作为复制的所有MySQL节点的server-id都不能相同。 ◆ binlog文件只记录对数据有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（select，show）语句。 忘了数据库密码 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; 1.2 MySQL主从复制实践 环境:多实例 10.0.0.52 3306 10.0.0.52 3307 3306---->3307复制---->3309 ---->3008复制 3306主---->3307从 架构实践：3306----->3307 1.2.1 开启主库binlog,配置server-id※※※※※※ [root@db02 ~]# egrep -i"server-id|log-bin" /data/3306/my.cnf log-bin = /data/3306/mysql-bin server-id = 6 重启服务 /data/3306/mysql restart 从库 [root@db02 ~]# egrep -i"server-id|log-bin" /data/3307/my.cnf #log-bin = /data/3307/mysql-bin server-id = 7 1.2.2 主库创建rep用户 grant 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.% | 1.2.3 从主库导出数据 按照我们见过的内容，直接取今天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.主库全备 全备三个重要命令 mysqldump cp/tar xtrabackup 拿到位置点是关键 sed-n '22p' all_2017-06-28.sql [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 root root 178468 6月 28 11:11 all_2017-06-28.sql.gz 3.主库解锁 mysql> unlock table; Query OK, 0 rows affected (0.00 sec) 1.2.4 从库导入全备的数据 [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 <all_2017-06-28.sql 1.2.5 找位置点，然后change master to从库 [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 TO MASTER_HOST='172.16.1.52', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=720; 打开复制开关slave： mysql> start slave; Query OK, 0 rows affected (0.03 sec) mysql> show slave status\G 显示如下结果证明主从复制实践成功 [root@db02 backup]# mysql -S /data/3307/mysql.sock -e "show slavestatus\G"|egrep "_Running|Behind_Master"|head -3 Slave_IO_Running:Yes Slave_SQL_Running:Yes Seconds_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，这个是在复制的过程中，从库比主库延迟的秒数，这个参数很重要，但企业里更准确地判断主从延迟的方法为：在主库写时间戳，然后从库读取时间戳和当前数据库时间的进行比较，从而认定是否延迟。 1.2.6 登录3306查看管理的主机 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.3 MySQL主从复制问题汇总 故障1：主库show master status；没返回状态结果。 mysql> show master status; Empty set (0.00 sec) 解答：上述问题原因是binlog功能开关没开或没生效。binlog功能开启正确的配置结果如下： [root@db02 ~]# grep "log-bin"/data/3306/my.cnf log-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 TO MASTER_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 start MySQL is running... [root@db02 ~]# ps -ef |grep mysql 发现没有服务端口号 root 1271 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 start Starting MySQL... [root@db02 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock 登录 [root@db02 ~]# cat /data/3306/oldboy_3306.err 出现问题看日志 1.4 生产环境下轻松部署MySQL主从复制 1.4.1 快速配置MySQL主从复制 步骤如下： 1. 安装好要配置从库的数据库，配置好log-bin和server-id参数。 2. 无需配置主库my.cnf文件，主库的log-bin和server-id参数默认就是配好的 3. 登录主库，增加从库连接主库同步的账户，例如rep，并授权replicationslave同步的权限。 4. 使用曾经在半夜通过mysqldump带-x和--master-data=1的命令及茶树定时备份的全备数据备份文件，把它恢复到从库。 5. 在从库执行change master to.....语句，无需binlog文件及对应位置点。 6. 从库开启同步开关，start slave。 7. 从库show slave status\G, 检查同步状态，并在主库进行更新测试。 1.4.2 无需熬夜，轻松部署MySQL主从复制 实战过程如下： 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 from change master tomaster_log_file='mysql-bin.000005',master_log_pos=107; 2) 找机会在需要做复制的从库上导入全备做从库，命令如下： gzip -d 2017-07-08.sql.gz mysql -uroot -poldboy123 -S /data/3308/mysql.sock<2017-07-08.sql mysql -uroot -poldboy123 -S /data/3308/mysql.sock<<EOF CHANGEMASTER TO MASTER_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 status\G #<===查看主从复制状态 1.5 MySQL主从复制线程状态说明及用途 1.5.1 MySQL主从复制I/O线程状态说明 1）登录主数据库查看MySQL线程的同步状态 命令如下： mysql> show processlist\G *************************** 1. row*************************** Id: 7 User: rep Host:10.0.0.52:27306 db:NULL Command: Binlog Dump Time: 538 State:init State:Master has sent all binlog to slave;waiting for binlog to beupdated Info: NuLL 1 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;swithching to 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/O和SQL线程。从库I/O线程的状态如下： mysql> show processlist\G *************************** 1. row*************************** Id: 1 User:system user Host: db:NULL Command: 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 master Requesting 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官方手册。 1.5.2查看MySQL线程同步状态的用途 通过MySQL线程同步状态可以看到同步是否正常进行，故障的位置是什么，另外还可查看数据库同步是否完成，可用于主库宕机切换数据库或者人工数据库主从切换迁移等。 例如：主库宕机，要选择最快的从库将其提升为主库，就需要查看主从库的线程状态，如果主从复制在正常情况下进行角色切换，也需要查看主从库的线程状态，根据复制状态确定更新是否完成。 （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
