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

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

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_FileRead_Master_Log_Pos比较,能看到SQL线程是否已经赶上从库本地的IO线程。

Slave_IO_Running:I/O线程是否启动并成功连接到主服务器上
一般和下面的Slave_IO_RunningSeconds_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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值