备案控制台
探索云世界
开发者社区 数据库 文章 正文

mysql主从数据库不同步的3种解决方法

2017-11-16 2434
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介：

先上Master库：
mysql>show processlist; 查看下进程是否Sleep太多。发现很正常。
show master status; 也正常。
mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
再到Slave上查看
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
可见是Slave不同步

下面介绍两种解决方法：


方法一：忽略错误后，继续同步
该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况
解决：
stop slave;
#表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，现在主从同步状态正常了。。。


方式二：指定跳过错误代码，继续同步

主键冲突、表已存在等错误代码如1062,1032,1060等，可以在mysql主配置文件指定略过此类异常并继续下条sql同步，这样也可以避免很多主从同步的异常中断

[mysqld]

slave-skip-errors = 1062,1032,1060

重新启动mysql

service mysqld restart

之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

方式三：重新做主从，完全同步
该方法适用于主从库数据相差较大，或者要求数据完全统一的情况
解决步骤如下：

1.先进入主库，进行锁表，防止数据写入
使用命令：
mysql> flush tables with read lock;
注意：该处是锁定为只读状态，语句不区分大小写


2.进行数据备份
#把数据备份到mysql.bak.sql文件
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql
这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失


3.查看master 状态
mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)


4.把mysql备份文件传到从库机器，进行数据恢复
#使用scp命令
[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/


5.停止从库的状态
mysql> stop slave;


6.然后到从库执行mysql命令，导入数据备份mysql> source /tmp/mysql.bak.sql


7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项
change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;


8.重新开启从同步
mysql> stop slave;


9.查看同步状态
mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes






一般在线增加从库有两种方式，一种是通过mysqldump备份主库，恢复到从库，mysqldump是逻辑备份，数据量大时，备份速度会很慢，锁表的时间也会很长。另一种是通过xtrabackup工具备份主库，恢复到从库，xtrabackup是物理备份，备份速度快，不锁表。为什么不锁表？因为自身会监控主库日志，如果有更新的数据，就会先写到一个文件中，然后再回归到备份文件中，从而保持数据一致性。


服务器信息：


主库：192.168.18.212（原有）

从库1：192.168.18.213（原有）

从库2：192.168.18.214（新增）


数据库版本：MySQL5.5


存储引擎：Innodb


测试库名：weibo


一、mysqldump方式


MySQL主从是基于binlog日志，所以在安装好数据库后就要开启binlog。这样好处是，一方面可以用binlog恢复数据库，另一方面可以为主从做准备。


原有主库配置参数如下：


# vi my.cnf
server- id  = 1  #id要唯一
log-bin = mysql-bin  #开启binlog日志
auto-increment-increment = 1  #在Ubuntu系统中MySQL5.5以后已经默认是1
auto-increment-offset = 1
slave-skip-errors = all  #跳过主从复制出现的错误


1. 主库创建同步账号


mysql> grant all on *.* to 'sync'@'192.168.18.%' identified by 'sync';


2. 从库配置MySQL


# vi my.cnf
server- id  = 3  #这个设置3
log-bin = mysql-bin  #开启binlog日志
auto-increment-increment = 1  #这两个参数在Ubuntu系统中MySQL5.5以后都已经默认是1
auto-increment-offset = 1
slave-skip-errors = all  #跳过主从复制出现的错误


3. 备份主库


# mysqldump -uroot -p123 --routines --single_transaction --master-data=2 --databases weibo > weibo.sql



参数说明：


--routines：导出存储过程和函数


--single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。


--master-data：默认等于1，将dump起始（change master to）binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。


4. 把备份库拷贝到从库


# scp weibo.sql root@192.168.18.214:/home/root


5. 在主库创建test_tb表，模拟数据库新增数据，weibo.sql是没有的


mysql> create table test_tb(id int,name varchar(30));


6. 从库导入备份库


# mysql -uroot -p123 -e 'create database weibo;'
# mysql -uroot -p123 weibo < weibo.sql


7. 在备份文件weibo.sql查看binlog和pos值


# head -25 weibo.sql
-- CHANGE MASTER TO MASTER_LOG_FILE= 'mysql-bin.000001' , MASTER_LOG_POS=107;  #大概22行


8. 从库设置从这个日志点同步，并启动


mysql> change master to master_host= '192.168.18.212' ,
-> master_user= 'sync' ,
-> master_password= 'sync' ,
-> master_log_file= 'mysql-bin.000001' ,
-> master_log_pos=107;
mysql> start slave;


mysql> show slave status\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection  id : 90
Current database: *** NONE ***
*************************** 1. row ***************************
Slave_IO_State: Waiting  for  master to send event
Master_Host: 192.168.18.212
Master_User:  sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 358
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 504
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......



可以看到IO和SQL线程均为YES，说明主从配置成功。


9. 从库查看weibo库里面的表


mysql> show tables;
+---------------------------+
| Tables_in_weibo |
+---------------------------+
| test_tb |


发现刚才模拟创建的test_tb表已经同步过来！


二、xtrabackup方式（推荐）


在上面配置基础上做实验，先删除掉从库配置：


mysql> stop slave;  #停止同步
mysql> reset slave;  #清除从连接信息
mysql> show slave status\G;  #再查看从状态，可以看到IO和SQL线程都为NO
mysql> drop database weibo;  #删除weibo库


此时，从库现在和新装的一样，继续前进！


1. 主库使用xtrabackup备份


# innobackupex --user=root --password=123 ./


生成一个以时间为命名的备份目录：2015-07-01_16-49-43


# ll 2015-07-01_16-49-43/
total 18480
drwxr-xr-x 5 root root 4096 Jul 1 16:49 ./
drwx------ 4 root root 4096 Jul 1 16:49 ../
-rw-r--r-- 1 root root 188 Jul 1 16:49 backup-my.cnf
-rw-r----- 1 root root 18874368 Jul 1 16:49 ibdata1
drwxr-xr-x 2 root root 4096 Jul 1 16:49 mysql/
drwxr-xr-x 2 root root 4096 Jul 1 16:49 performance_schema/
drwxr-xr-x 2 root root 12288 Jul 1 16:49 weibo/
-rw-r--r-- 1 root root 21 Jul 1 16:49 xtrabackup_binlog_info
-rw-r----- 1 root root 89 Jul 1 16:49 xtrabackup_checkpoints
-rw-r--r-- 1 root root 563 Jul 1 16:49 xtrabackup_info
-rw-r----- 1 root root 2560 Jul 1 16:49 xtrabackup_logfile


2. 把备份目录拷贝到从库上


# scp -r 2015-07-01_16-49-43 root@192.168.18.214:/home/root


3. 从库上把MySQL服务停掉，删除datadir目录，将备份目录重命名为datadir目录


# sudo rm -rf /var/lib/mysql/
# sudo mv 2015-07-01_16-49-43/ /var/lib/mysql
# sudo chown mysql.mysql -R /var/lib/mysql
# sudo /etc/init.d/mysql start
# ps -ef |grep mysql #查看已经正常启动
mysql 8832 1 0 16:55 ? 00:00:00  /usr/sbin/mysqld


b2


mysql> create table test_tb2(id int,name varchar(30));


5. 从备份目录中xtrabackup_info文件获取到binlog和pos位置


# cat /var/lib/mysql/xtrabackup_info
uuid = 201af9db-1fce-11e5-96b0-525400e4239d
name =
tool_name = innobackupex
tool_command = --user=root --password=... ./
tool_version = 1.5.1-xtrabackup
ibbackup_version = xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision  id : )
server_version = 5.5.43-0ubuntu0.12.04.1-log
start_time = 2015-07-01 16:49:43
end_time = 2015-07-01 16:49:46
lock_time = 1
binlog_pos = filename  'mysql-bin.000001' , position 429  #这个位置
innodb_from_lsn = 0
innodb_to_lsn = 1598188
partial = N
incremental = N
format  =  file
compact = N
compressed = N


6. 从库设置从这个日志点同步，并启动


mysql> change master to master_host= '192.168.18.212' ,
-> master_user= 'sync' ,
-> master_password= 'sync' ,
-> master_log_file= 'mysql-bin.000001' ,
-> master_log_pos=429;
mysql> start slave;


mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting  for  master to send event
Master_Host: 192.168.18.212
Master_User:  sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 539
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 363
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......


可以看到IO和SQL线程均为YES，说明主从配置成功。


9. 从库查看weibo库里面的表


mysql> show tables;
+---------------------------+
| Tables_in_weibo |
+---------------------------+
| test_tb |
| test_tb2 |









本文转自 chengxuyonghu 51CTO博客，原文链接：http://blog.51cto.com/6226001001/1675105，如需转载请自行联系原作者
文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
MySQL
SQL
监控
数据库
关键词：
mysql数据库
数据库mysql
数据库同步
云数据库 RDS MySQL 版同步
云数据库 RDS MySQL 版解决方法
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
余二五
目录
相关文章
江帅帅
|
6天前
|
关系型数据库 MySQL 分布式数据库
《MySQL 简易速速上手小册》第6章：MySQL 复制和分布式数据库（2024 最新版）
《MySQL 简易速速上手小册》第6章：MySQL 复制和分布式数据库（2024 最新版）
江帅帅
37 2
东北赵四
|
3天前
|
SQL 存储 关系型数据库
数据库开发之mysql前言以及详细解析
数据库开发之mysql前言以及详细解析
东北赵四
14 0
认真学习的小雅兰.
|
8天前
|
SQL 关系型数据库 MySQL
MySQL环境搭建——“MySQL数据库”
MySQL环境搭建——“MySQL数据库”
认真学习的小雅兰.
36 0
认真学习的小雅兰.
|
8天前
|
SQL NoSQL 关系型数据库
初识MySQL数据库——“MySQL数据库”
初识MySQL数据库——“MySQL数据库”
认真学习的小雅兰.
14 0
find_starshine
|
10天前
|
关系型数据库 MySQL 数据库
数据库基础（mysql）
数据库基础（mysql）
find_starshine
19 0
LightOf
|
10天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化：如何发现SQL中的问题？
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划，关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列，以及区分度高的列。大数据表改结构需谨慎，可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例，如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量，可利用上一批的最大ID进行限制。
LightOf
37 3
技术蜜糖罐
|
22天前
|
SQL 数据可视化 关系型数据库
轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
技术蜜糖罐
105 0
技术蜜糖罐
|
22天前
|
存储 关系型数据库 MySQL
轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）
轻松入门MySQL：数据库设计之范式规范，优化企业管理系统效率（21）
技术蜜糖罐
44 0
技术蜜糖罐
|
22天前
|
关系型数据库 MySQL 数据库
轻松入门MySQL：精准查询，巧用WHERE与HAVING，数据库查询如虎添翼（7）
轻松入门MySQL：精准查询，巧用WHERE与HAVING，数据库查询如虎添翼（7）
技术蜜糖罐
30 0
风水道人
|
24天前
|
存储 关系型数据库 MySQL
数据库字符编码MySQL中使用UTF-8还是UTFB4
数据库字符编码MySQL中使用UTF-8还是UTFB4
风水道人
20 0

热门文章

最新文章

  • 1
    超1/3中国500强企业都在用的「汇联易」，为什么选用阿里云RDS？
  • 2
    MySQL全局库表查询准确定位字段
  • 3
    MySQL学习（三）
  • 4
    安装Docker&镜像容器操作&使用Docker安装部署MySQL，Redis，RabbitMQ，Nacos，Seata，Minio
  • 5
    MySQL学习（五）
  • 6
    MySQL学习（七）
  • 7
    mysql性能优化-慢查询分析、优化索引和配置
  • 8
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 9
    【分布式技术专题】「分布式技术架构」MySQL数据同步到Elasticsearch之N种方案解析，实现高效数据同步
  • 10
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 1
    一台MySQL数据库启动多个实例
    11
  • 2
    MongoDB数据恢复—MongoDB数据库文件被破坏的数据恢复案例
    15
  • 3
    数据库库表结构设计：原理、实例与最佳实践
    17
  • 4
    sql导入数据库命令
    12
  • 5
    Javaweb之数据库连接池以及lombok类库的详细解析
    18
  • 6
    数据库开发之图形化工具以及表操作的详细解析
    19
  • 7
    数据库恢复挂起之更换数据库
    15
  • 8
    数据库开发之mysql前言以及详细解析
    14
  • 9
    「杭州*康恩贝」4月26日PolarDB开源数据库沙龙，开启报名！
    14
  • 10
    Python与NoSQL数据库（MongoDB、Redis等）面试问答
    125

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）