关于不停库不锁表在线主从配置和主从不同步解决思路

最新推荐文章于 2024-06-07 09:47:48 发布
最新推荐文章于 2024-06-07 09:47:48 发布
阅读量404 收藏 1
点赞数
文章标签： 数据库 运维
原文链接：https://my.oschina.net/u/3959687/blog/2989666
版权

不停库不锁表在线主从配置

1，Mysqldump对于10G以下的数据库或表，比较适用又快捷。当数据量达到100-500G的时候，mysql就力不从心了。

2，Percona-xtrabackup可以实现mysql在线热备工作。可进行全量，增量，单表备份和还原。

3，2.2版本的xtrabackup能对innoDB和XtraDB存储引擎的数据库非阻塞的备份，对myISAM的备份通过加表读锁的方式实现，2.3版本xtrabackup命令直接支持myISAM引擎。

 

Xtrabakup 优势：1，无需停止数据库进行InnoDB热备

               2，增量备份MySQL

               3， 热压缩到传输到其他服务器

               4， 能比较容易的创建主从同步

               5， 备份MySQL时不会增大服务器负载。

 

4，主从复制是为了实现读写分离，减轻主库负载，还可以数据备份，保证数据恢复，还可以主从切换高可用。

 

5，复制类型 

 ·  基于语句的复制：STATEMENT 在主服务器执行的SQL语句，在从上执行同样的语句，有可能由于执行上下文环境不同而导致数据不一致。（mysql在5.7.7以前默认采用STATEENT,在5.7.7及以后版本，默认改用row-based

· 基于行的复制：row ，把改变的内容复制过去，不是把命令在从上执行一遍。从mysql5.0开始支持，能够严格保证数据完整一致。（注：迁移时，从库改正了字段默认值定义，但数据在主库更改后，即使产生的新数据默认值正确。但基于行的复制依然用不正确的值字段全部更新）

· 混合类型的复制：MIXED ，默认采用基于语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制。

 

  1. 1 mysql 系统库mysql库里面的表的日志记录格式，在通过如INSERT,UPDATE、DELETE、TRUNCATE等方式直接修改数据的语句，使用binlog_format指定的方式记录，但使用GRANT、ALTER、CREATE、RENAME等改动的mysql库里数据的，会强制使用statement-based方式记录binlog。

在线修改二进制日志，需要SUPER权限（如SET SESSION binlog_format=MIXED）

6.2 复制类型分为异步复制和半同步复制。

   异步复制：通常没后，说明指的都是异步。在主库执行完Commit后，在主库写入binlog日志后即可成功返回客户端，不等binlog日志传送给从库，一旦主库宕机，可能会丢失日志。

   半同步复制；是等其中一个从库也接收到binlog事物并成功写入relay log之后，才返回commit操作成功给客户端。这样保证事务成功提交后至少有两份日志，一份在主库binlog上，另一份在从库relay log上。进一步保证数据完整性。

原理
(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2) slave将master的binary log events拷贝到它的中继日志(relay log)；
(3) slave重做中继日志中的事件，将改变反映它自己的数据。

 7，配置主从

 1，准备工作；主从版本一致—>主库授权复制帐号—>确保开启binlog及主从server_id唯一—>xtrabackup恢复到从库—>记录xtrabackup_binlog_info中binlog名称及偏移量—>从库change master to —>slave start—>检查两个yes

,2， 主库上创建复制账号

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_ali'@'192.168.5.%' IDENTIFIED BY 'slave_ali_pass';  

mysql> FLUSH PRIVILEGES;

3，使用percona-Xtrabackup恢复数据。

假设，全量备份，全量恢复，不涉及增量。

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'bkppass';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,PROCESS,SUPER ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

假设 MySQL 安装目录在/opt/mysql，my.cnf配置文件/opt/mysql/my.cnf，端口3306，数据目录/opt/mysql_data，sock位于/opt/mysql_data/mysql.sock。备份数据放在/data/backup/mysql/。

3.1全量备份（默认会以当天 日期+时间 戳命名备份目录，如 2015-09-16_00-00-02)

 

$ export BKP_PASS="bkppass"
$ innobackupex --defaults-file=/opt/mysql/my.cnf --host=localhost --port=3306 --user=bkpuser --password=${BKP_PASS} /data/backup/mysql

3.2 全量恢复(在恢复的数据库服务器（从库）上)

恢复准备
$ innobackupex --use-memory=16G --apply-log 2015-09-16_00-00-02

确认数据库是关闭的，并且datadir，目录下为空
$ innobackupex --defaults-file=/opt/mysql/my.cnf --use-memory=16G --copy-back 2015-09-16_00-00-02

第一步是恢复准备，apply-log应用全备时 log sequence number 之后的数据，完了后会输出类似 InnoDB: Last MySQL binlog file position 0 262484673, file name ./mysql-bin.000135 的信息，告诉我们了后面的从库应该从哪个地方开始复制。时间不会很长，但最好用screen之类的软件放到后台执行，以免终端断开，功亏一篑。

 

第二步使用新的my.cnf文件，将完整的mysql数据文件拷贝到datadir下。

  1. 做从库

上面恢复过程最后一步apply-log完成之后，会得到一个lsn position 和binlog文件名：262484673、mysql-bin.000135。下面开始从库制作。

一般在copy-back之后需要修改数据文件目录的属性：

# chown -R mysql.mysql /opt/mysql_data

4.1 my.cnf

从库的配置文件简单一点可以从主库拷贝过来。根据需要要注意以下几处。

1，server-id一定不能与主库相同，否则，会出现如下错误：Slave: received end packet FROM server, apparent master shutdown

2，从库一般作为只读库使用，所以为安全起见，设置只读 set global read_only=1;可以在从服务器的 my.cnf 里加入read-only参数来实现这一点，唯一需要注意的一点事read-only仅对没有super权限的用户有效。所以最好核对一下连接从服务器的用户，确保其没有super权限。

3，关于从库的事件
MYSQL Replication 可以很好的达到你的预期：从库的事件不会自己去执行，主库会把event执行的结果直接同步。在statement模式下，复制的是 event BODY 里的SQL，在row模式下是主库事件执行完成后影响的行精确复制。

4，从库 event_scheduler 参数是被忽略的，并且每个event 状态会是 SLAVESIDE_DISABLED ，但CREATE/ALTER EVENT等操作语句是会复制。主从切换后，从库事件状态会变成ENABLE。

5，参数调整
从库是不允许写入的，否则数据就不一致了。从库实例的配置可以不要主库那么高，比如原16G的buffer pool，根据用途，从库可以设到4-8G（当时前提是将来你也不打算把它切换为主库用）。相应的，read_buffer_size，sort_buffer_size, query_cache_size 这些读相关参数可以略微增大。

6，skip-slave-start
主从创建完成后，默认情况下次启动从库，会自动启动复制进程，一般这也正是我们需要的，但在维护阶段时你可能不想从库启动后立即开始复制，--skip-slave-start选项可以帮到你。

7，log-slave-updates
正常情况从库是不需要写回放日志产生的binlog，无形中增加服务器压力。但如果你想要实现级联复制即 A -> B -> C ，B同时是A的从库，也是C的主库，就需要开启 log-bin 和 log-slave-updates 。

另外，建议显示设置 log-bin=mysql-bin 确保主从正常切换。 show variables like 'log%' 查看当前值。

8，关于过滤表见mysql-replica-filter

9，sync_binlog
For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file.

上面的话同时也意味着性能最低。可以在这埋点，假如出现慢的情况，把两参数调成2。

4.2 启动从库

# /opt/mysql/bin/mysqld_safe --defaults-file=/opt/mysql/my.cnf &

提示：如果你不确定这个库是谁的从库，保守起见加上--skip-slave-start启动，兴许能防止数据不一致。

4.3 change master（从库上）

$ mysql -uslave_ali -p'slave_ali_pass' -S /opt/mysql_data/mysql.sock

mysql> change master to master_host=MASTER_HOST, master_port=3306, 

       master_user='slave_ali',master_password='slave_ali_pass',

       master_log_file='mysql-bin.000135', master_log_pos=262484673;

mysql> show slave status\G

mysql> start slave;

mysql> show slave status\G

4.4 验证同步延迟

从库执行 show slave status\G

     Slave_IO_State: Waiting for master to send event
      Master_Log_File: mysql-bin.000004
  Read_Master_Log_Pos: 931
       Relay_Log_File: slave1-relay-bin.000056
        Relay_Log_Pos: 950
Relay_Master_Log_File: mysql-bin.000004
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
   Exec_Master_Log_Pos: 931
      Relay_Log_Space: 408
Seconds_Behind_Master: 0

 Master_Log_File： I/O线程当前正在读取的主服务器二进制日志文件的名称

Read_Master_Log_Pos：本机I/O线程读取主服务器二进制日志位置
上面2各值，与在主库执行show master status;看到的值如果基本接近，说明从库IO线程已经赶上了主库的binlog。

Relay_Master_Log_File: 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称

Exec_Master_Log_Pos: SQL线程执行来自master的二进制日志最后一个事件位置
与上面的Relay_Master_Log_File一起，同Master_Log_File、Read_Master_Log_Pos比较，能看到SQL线程是否已经赶上从库本地的IO线程。

Slave_IO_Running：I/O线程是否启动并成功连接到主服务器上
一般和下面的Slave_IO_Running和Seconds_Behind_Master一起监控主从健康状态

    Slave_SQL_Running：SQL线程是否启动

Seconds_Behind_Master: 从属服务器“落后”多少秒

 

Mysql主从不同步解决方法

 

主从同步配置好后，运行一段时间，出现了不同步现象，

可以用命令检查，

msyq > show slave status \G;
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '149' for key 'PRIMARY'' on query. Default database: 'zabbix'. Query: 'insert into escalations (escalationid,actionid,status,triggerid,itemid,eventid,r_eventid) values (149,7,0,16272,null,3334811,null)'

看报错，应该是主MYSQL上建表时，主键有重复的值报错，造成从不能同步。
解决的办法是在从库上执行:
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
上面的方法可以解决问题，还有一种解决问题的办法是通过修改mysql的配置文件，让从库的同步线程忽略这个错误，

修改mysql配置文件 /etc/my.cnf 在 [mysqld]下加一行slave_skip_errors = 1062 ,保存重启mysql
mysql slave可以正常同步了.

MYSQL主从同步故障一例及解决过程！

nagios 报警，mysql_AB error，检查从库
show slave status \G; 果然 
Slave_IO_Running: Yes
Slave_SQL_Running: No
而且出现了1062错误，还提示 
Last_SQL_Error: Error 'Duplicate entry '1001-164761-0' for key 'PRIMARY'' on query. Default database: 'bug'. Query: 'insert into misdata (uid,mid,pid,state,mtime) values (164761,1001,0,-1,1262623560)'

tail -f mysql_error.log  查看错误日志

 [ERROR] Slave SQL: Error 'Duplicate entry '1007-443786-0' for key 'PRIMARY'' on query. Default database: 'ufo'. Query: 'insert into misdata (uid,mid,pid,sta
te,mtime) values (443786,1007,0,-1,1262598003)', Error_code: 1062
100104 17:39:05 [Warning] Slave: Duplicate entry '1007-443786-0' for key 'PRIMARY' Error_code: 1062
100104 17:39:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'ufolog.000058
8' position 55793296

解决思路：先停止同步，锁表，在从上重新配置

先手动同步一下，从库上首先 stop slave;停止同步
进入主库锁表，FLUSH TABLES WITH READ LOCK；
mysql> show master status;

进入从库，重新配置

mysql>change master to master_host='192.168.1.141', master_user='slave', 
master_password='xxx', 
master_port=3306, 
master_log_file='ufo.000063', 
master_log_pos=159164526;

完成后start slave;
回到主库
unlock tables; 解锁
回到从库 查看
show slave status \G

 

解决未果，尝试跳过错误的方法。

stop slave; 
set global sql_slave_skip_counter=1; （1是指跳过一个错误）
slave start;

再show slave status \G;查看

结果还是报错，由原来的164761变成其他数字。

/etc/my.cnf  修改从库的配置文件，在[mysqld]加入一行。

slave-skip-errors = 1062

mysql /etc/init.d/mysqld restart  重启下从库的

show slave status \G; 查看正常，这是数据可能已经不同步了。

tail -f mysql_error.log  再次查看错误日志

100106 16:54:21 [Warning] Statement may not be safe to log in statement format. Statement: delete from `system_message_1` where `to_uid` = 181464 ORDER BY `id` ASC LIMIT 1 （大概意思是statement格式不安全）

 

statement format 应该是 binlog的一种格式，进入从库查看一下
show global variables like 'binlog_format';
果然当前的格式为statement 

我需要把格式改为 mixed格式
修改从库的 my.cfg
在[mysqld]下面加入下面这行
binlog_format=mixed

 

转载于:https://my.oschina.net/u/3959687/blog/2989666

chyita2015
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql主从库不同步问题解决方法
09-11
以下是一些关于如何解决MySQL主从库不同步问题的详细步骤和相关知识点： 1. **错误识别**：当主从库不同步时，系统可能会抛出错误提示，例如“Last_IO_Error: Got fatal error 1236 from master when reading data ...
MYSQL主从库不同步故障一例解决方法
09-11
本文将详细解析一个具体的MySQL主从库不同步故障案例，并提供解决方案。 首先，当发现主从库不同步时，首先要确认的是同步是否在其他库中正常进行。在案例中，主库与从库首次同步完成后一切正常，但在后续操作中，...
Mysql之部分表主从搭建及新增表
月生的静心苑
08-10 1682
一般情况下我们配置mysql主从模式实现mysql整库的冗余或者高可用，实际应用场景中还会有只需要同步某库部分表。例如某库含有上百个表，总库大小几十上百G，我们在A/B两城之间搭建数据库主从，A/B两个数据中心之间是通过专线互联，带宽有限，部署在B城的应用实际上都只需要使用数据库的部分表，这种情况下我们就可以搭建mysql部分表主从来实现该需求。运行一段时间后随着业务的变化又需要新增部分表，总体结构还是部分表主从模式。博文实验环境如下：.........
mysql 主从同步 从库数据与主库数据不一致不锁表进行同步
qq_44868698的博客
08-04 537
《越人歌》 佚名 今夕何夕兮，搴舟中流。 今日何日兮，得与王子同舟。 蒙羞被好兮，不訾诟耻。 心几烦而不绝兮，得知王子。 山有木兮木有枝，心悦君兮君不知。 一、备份主库的数据 mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob -uroot -p --mast...
CentOS7 MySQL5.7.35主从 不停机搭建 以及配置
最新发布
天外有天，人外有人。 专注品质 不断创新 让精益求精成为习惯
06-07 563
CentOS7 MySQL5.7.35 主从搭建 以及配置
MySQL在不锁表情况下的主从同步配置
Mr_JWYang
05-07 1246
一般情况下，我们都是在新安装MySQL的同时配置主从同步，但是也许会遇到在业务跑了一段时间后，需要给数据库配置主从同步的情况。面对这种情况，作为菜鸟的我，会先再主库锁表，导出数据，传送到从库服务器，接着是导入从库，然后在从库change master。这显然很麻烦。不经意间听到大佬说，在不锁表的情况下也可以配置主从同步。 配置流程大致如下： 1、从主库导出已有数据 mysqldump --skip...
Xtrabackup备份
喝醉酒的小白
06-02 5811
mysql备份
MySQL备份工具之xtrabackup
阿干tkl的博客
05-14 4011
xtrabackup官网Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。简介说明：Xtrabackup2.4有两个主要的工具：xtrabackup（1）xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表（2）innobackupex则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，
MySQL主从同步和主主同步详细过程
08-13
超详细MySQL主从配置和主主同步总结。。。。。。。。。
mysql主从数据库不同步的2种解决方法
12-15
今天发现Mysql的主从数据库没有同步 先上Master库： mysql>show processlist; 查看下进程是否Sleep太多。发现很正常。 show master status; 也正常。 mysql> show master status; +——————-+———-+————–...
MySQL 社区开源备份工具 Xtrabackup 详解
分享既学习
11-14 9098
数据库备份恢复是 DBA 的 “保命” 技能，生产业务不仅要保证有合适的备份策略，也要定期验证备份的有效性和恢复演练流程，因为数据恢复和验证可能会涉及多方合作，演练可以让灾难真正发生时，多方配合有条不紊的将数据恢复，从而尽可能的减少 RTO 让业务快速恢复。Xtrabackup 是 MySQL 社区唯一一款开源物理热备工具，本篇文章将详细介绍 DBA 如何使用它，以及备份恢复的详细步骤。
MySQL不停机不锁表主从同步与读写分离配置
01-02
MySQL不停机不锁表主从同步与读写分离配置,1 .xtrabackup安装.2.核对mysql的版本.3.在master服务器上在线备份数据库.4.slave机器上操作
XtraBacup备份与恢复
weixin_45636702的博客
07-11 364
XtraBacup备份与恢复 一、XtraBacup简介 MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，XtraBacup有2个工具，分别是xtrabakup、innobakupe。
MySQL之 XtraBackup 备份
热门推荐
qq_40907977的博客
10-16 1万+
MySQL 系列连载之 XtraBackup 备份原理（1） 导读 在日常的linux运维工作中，大数据量备份与还原，始终是个难点。关于mysql的备份和恢复，比较传统的是用mysqldump工具，今天本文推荐另一个备份工具innobackupex。 Xtrabackup简介 innobackupex和mysqldump都可以对mysql进行热备份的，mysqldump对mysql的innodb的备份可以使用single-transaction参数来开启一个事务，利用innodb的mvcc来不进行锁表进行热
安装使用xtrabackup
jc_benben的专栏
10-22 1304
用的人都知道，不用多说这个工具的强大之处 1，安装  1.1，使用rpm安装时候提示缺少包 [root@centos7 soft]# rpm -ivh percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm warning: percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signa...
mysql 主从复制不一致，不停库不锁表恢复主从同步
weixin_30650039的博客
07-05 425
注意：进行此操作时，确认在之前已经开启了MySQL的bin-log日志，如果没有则无法实现 为了安全考虑，我们授权一个用户进行数据备份： [root@7con ] mysql -uroot -p mysql> grant all on *.* to 'skon'@'192.168.1.1' identified by 'user_passwd'; 备份主库上的数据库...
利用mycat实现基于mysql5.5主从复制的读写分离
reblue520的专栏
04-22 220
整体步骤： 1.准备好两台服务器，一台作为主数据库服务器，一台作为从服务器，并安装好mysql数据库，此处略 2.配置好主从同步 3.下载JDK配置mycat依赖的JAVA环境，mycat采用java语言开发运行依赖jre 4.配置mycat的相关文件 5.测试 一、配置mysql主从环境 MYSQL主从同步的作用 (1) 数据分布 (2) 负载平衡(load bala...
slave复制进程不随mysql启动而启动 skip-slave-start参数
aeoluspu的专栏
07-23 1万+
不管怎么说今天采坑了。有一套主从，前期my.cnf配置文件中指定只复制其中某些库，后来因为业务需求，要把未复制的库也开始复制。今天修改完my.cnf配置文件，然后重启数据库，悲催的事情发生了，slave跟着也开始起来了，然后开始复制了，然后造成数据不一致。关键是从库slave要进行reset slave。正确步骤如下：
通过 XtraBackup 实现不停机不锁表搭建主从同步
weixin_34414196的博客
03-03 169
简介 Xtrabackup是由 Percona 开发的一个开源软件，可实现对 InnoDB 的数据备份，支持在线热备份（备份时不影响数据读写）。备份时，Xtrabackup 会将 Master 的 binlog 信息记录在 xtrabackup_slave_info 文件中，通过此信息可以方便的搭建主从复制。 XtraBackup 有...
mysql主从复制和主主复制的配置文件
05-16
MySQL 的主从复制和主主复制的配置文件如下： 1. 主从复制的配置文件（一主一从） 主库 my.cnf 配置文件： ``` [mysqld] server-id=1 log-bin=mysql-bin binlog-do-db=mydatabase ``` 从库 my.cnf 配置文件： ``` [mysqld] server-id=2 relay-log=mysql-relay-bin read-only=1 ``` 2. 主主复制的配置文件（一主多从） 两个主库的 my.cnf 配置文件： ``` [mysqld] server-id=1 log-bin=mysql-bin binlog-do-db=mydatabase auto_increment_increment=2 auto_increment_offset=1 ``` ``` [mysqld] server-id=2 log-bin=mysql-bin binlog-do-db=mydatabase auto_increment_increment=2 auto_increment_offset=2 ``` 从库 my.cnf 配置文件： ``` [mysqld] server-id=3 relay-log=mysql-relay-bin read-only=1 ``` 在主主复制中，每个主库都必须有一个唯一的 server-id。auto_increment_increment 和 auto_increment_offset 的设置允许在两个主服务器之间自动分配唯一的主键值。从服务器只需要配置一个唯一的 server-id 即可。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值