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

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

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