学习linux的第四十四天

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

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

公司里有两个mysql服务器做主从同步，某天Nagios发来报警短信，mysqla is down…赶紧联系机房，机房的人反馈来的信息是 HARDWARE ERROR 后面信息省略，让机房记下错误信息后让他们帮忙重启下看是不是能正常起来，结果竟然正常起来了，赶紧导出所有数据。
问题又出现了，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)’
很显然，由于主库重启导致 从库数据不同步而且主键冲突。查看error 日志发现error日志文件变得好大，比以前大了将近好几倍，
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;
±------------------±----------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±------------------±----------±-------------±-----------------+
| ufo.000063 | 159164526 | | |
±------------------±----------±-------------±-----------------+
1 row in set (0.00 sec)
进入从库
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 变成了 165881，连续执行了几次后
除了上面的数值 在变，错误依然还在
郁闷了，看来只能先强制跳过 1062错误了，于是修改从库的/etc/my.cnf文件
在里面的[mysqld]下面加入了一行
slave-skip-errors = 1062 (忽略所有的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 格式不安全，用vim 打开他看了一下，发现好多这类警告，我说为什么错误日志怎么变这么大了呢！！
statement format 应该是 binlog的一种格式，进入从库查看一下
show global variables like ‘binlog_format’;
果然当前的格式为statement

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

然后重启mysql服务，发现错误日志里的 警告 都停止了。这回清静多了~~

我突然想起一件事，记得有朋友说过 RBR 模式可以解决很多因为主键冲突导致的主从无法同步情况，想到这里我就想要不要把 slave-skip-errors = 1062 去掉再试试，
于是就进入到my.cnf 里在注释掉了 slave-skip-errors = 1062
再次重新启动 mysql服务
进入从库
show slave status \G;
…
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…

恢复了！！！有观察了一段时间没有出现问题这才放心，

看来导致 mysql 主从复制出错的原因还真不少修复的办法也不止一个，binlog的格式也是其中之一。
希望遇到和这次一样问题的朋友看到这篇文章后会得到 一些启发和解决问题的方法~~

MySQL auto_increment_increment，auto_increment_offset 用法

auto_increment_increment控制列中的值的增量值，也就是步长。
auto_increment_offset确定AUTO_INCREMENT列值的起点，也就是初始值。
变量范围：可以在全局以及session级别设置这2个变量

–当前系统环境
root@localhost[(none)]> show variables like ‘version’;
±--------------±-----------+
| Variable_name | Value |
±--------------±-----------+
| version | 5.5.39-log |
±--------------±-----------+

root@localhost[mysql]> create database tempdb;

root@localhost[mysql]> use tempdb;

–查看变量auto_increment_increment与auto_increment_offset
root@localhost[tempdb]> show variables like ‘%auto_incre%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+
2、演示auto_increment_increment与auto_increment_offset
–创建演示表，使用auto_increment子句
root@localhost[tempdb]> create table t1(id int not null auto_increment primary key, col varchar(20));

–插入记录
root@localhost[tempdb]> insert into t1(col) values(‘robin’),(‘fred’),(‘jack’),(‘james’);

–下面可以看到id列起始值为1，增量为1
root@localhost[tempdb]> select * from t1;
±—±------+
| id | col |
±—±------+
| 1 | robin |
| 2 | fred |
| 3 | jack |
| 4 | james |
±—±------+

–设置步长为5
root@localhost[tempdb]> set session auto_increment_increment=5;

root@localhost[tempdb]> show variables like ‘%auto_incre%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 5 |
| auto_increment_offset | 1 |
±-------------------------±------+

–清空表t1
root@localhost[tempdb]> truncate table t1;

–再次插入记录
root@localhost[tempdb]> insert into t1(col) values(‘robin’),(‘fred’),(‘jack’),(‘james’);

–如下查询可以看到步长以5位基数发生变化
root@localhost[tempdb]> select * from t1;
±—±------+
| id | col |
±—±------+
| 1 | robin |
| 6 | fred |
| 11 | jack |
| 16 | james |
±—±------+

–设置初始值为5
root@localhost[tempdb]> set session auto_increment_offset=5;

root@localhost[tempdb]> show variables like ‘%auto_incre%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 5 |
| auto_increment_offset | 5 |
±-------------------------±------+

root@localhost[tempdb]> truncate table t1;

root@localhost[tempdb]> insert into t1(col) values(‘robin’),(‘fred’),(‘jack’),(‘james’);

–下面是新的结果
root@localhost[tempdb]> select * from t1;
±—±------+
| id | col |
±—±------+
| 5 | robin |
| 10 | fred |
| 15 | jack |
| 20 | james |
±—±------+

3、auto_increment_increment与auto_increment_offset取值范围
–将变量auto_increment_increment设置为0
root@localhost[tempdb]> set session auto_increment_increment=0;

–实际值变成了1
root@localhost[tempdb]> show variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 5 |
±-------------------------±------+

–同样将auto_increment_offset设置为0
root@localhost[tempdb]> set session auto_increment_offset=0;

–实际值也变成了1
root@localhost[tempdb]> show variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+

–下面尝试将2个变量设置为大于65535
root@localhost[tempdb]> set session auto_increment_increment=65537;

root@localhost[tempdb]> set session auto_increment_offset=65537;

–其实际的值都变成了65535
root@localhost[tempdb]> show variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 65535 |
| auto_increment_offset | 65535 |
±-------------------------±------+

–尝试为2个变量设置为负值
root@localhost[tempdb]> set session auto_increment_offset=-2;

root@localhost[tempdb]> set session auto_increment_increment=-5;

–下面的查询可以看出全部恢复到缺省值1
root@localhost[tempdb]> show variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+

由上可以看出2个变量只能设置为1至65535之间的整数值。
所有非正整数全部会置为缺省值1，大于65535的值会被自动置为65535。

4、全局与session级别的设置

–查看全局范围这2个变量的值
root@localhost[tempdb]> show global variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+

–下面分别设置session基本的值
root@localhost[tempdb]> set session auto_increment_increment=5;

root@localhost[tempdb]> set session auto_increment_offset=10;

–查看session级别的值
root@localhost[tempdb]> show session variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 5 |
| auto_increment_offset | 10 |
±-------------------------±------+

–查看全局级别的值
root@localhost[tempdb]> show global variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+

–设置全局级别的值
root@localhost[tempdb]> set global auto_increment_increment=2;

root@localhost[tempdb]> set global auto_increment_offset=3;

root@localhost[tempdb]> show global variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 2 |
| auto_increment_offset | 3 |
±-------------------------±------+

5、已有auto_increment列值任一变量变化的情形
root@localhost[tempdb]> truncate table t1;

root@localhost[tempdb]> show variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+

root@localhost[tempdb]> insert into t1(col) values(‘robin’),(‘fred’),(‘jack’);

root@localhost[tempdb]> select * from t1;
±—±------+
| id | col |
±—±------+
| 1 | robin |
| 2 | fred |
| 3 | jack |
±—±------+

root@localhost[tempdb]> set session auto_increment_increment=5;

root@localhost[tempdb]> show variables like ‘%auto_increment%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 5 |
| auto_increment_offset | 1 |
±-------------------------±------+

–Author: Leshami
–Blog : http://blog.csdn.net/leshami

root@localhost[tempdb]> insert into t1(col) values(‘david’),(‘tim’),(‘jerry’);

root@localhost[tempdb]> select * from t1;
±—±------+
| id | col |
±—±------+
| 1 | robin |
| 2 | fred |
| 3 | jack |
| 6 | david |
| 11 | tim |
| 16 | jerry |
±—±------+

New_value = auto_increment_offset+ N * auto_increment_increment
New_value1 = 1 + 1 * 5 = 6
New_value2 = 1 + 2 * 5 = 11

–下面是修改auto_increment_offset后的结果
root@localhost[tempdb]> set session auto_increment_offset=2;

root@localhost[tempdb]> insert into t1(col) values(‘lewis’),(‘ian’);

root@localhost[tempdb]> select * from t1;
±—±------+
| id | col |
±—±------+
| 1 | robin |
| 2 | fred |
| 3 | jack |
| 6 | david |
| 11 | tim |
| 16 | jerry |
| 22 | lewis |
| 27 | ian |
±—±------+

这个id为22，应该是这样推算来的：max(id)+(new_offset-old_offset)+increment
也就是说变化auto_increment_offset后的第一个值为max(id)+(new_offset-old_offset)+increment之后再按步长递增。

配置Mysql-proxy，实现读写分离

1、安装 mysql-proxy
实现读写分离是有 lua 脚本实现的，现在 mysql-proxy 里面已经集成，无需再安装
下载：http://dev.mysql.com/downloads/mysql-proxy/ 一定要下载对应的版本
[root@proxy ~]# rpm -ivh mysql-proxy-0.8.1-1.el6.x86_64.rpm

2、将rw-splitting.lua 脚本复制到/lib 下
[root@proxy ~]# cp rw-splitting.lua /lib

3、修改mysql-proxy主配置文件/etc/sysconfig/mysql-proxy

[root@proxy ~]# vim /etc/sysconfig/mysql-proxy
ADMIN_USER=“proxy” #主从 mysql 共有的用户
ADMIN_PASSWORD=“123456” #用户的密码
ADMIN_LUA_SCRIPT="/lib/rw-splitting.lua" #指定管理脚本
PROXY_USER=“root” #运行 mysql-proxy用户
#添加如下代码:[以下代码需写在一行内]
PROXY_OPTIONS="–proxy-address=172.25.17.7:4040

–proxy-read-only-backend-addresses=172.25.17.20:3306–proxy-read-only-backend-addresses=172.25.17.21:3306–proxy-backend-addresses=172.25.17.19:3306–proxy-lua-script=/lib/rw-splitting.lua
–daemon"
#mysql-proxy 运行 ip 和端口,不加端口,默认 4040
4、修改读写分离配置文件

[root@proxy ~]# vim /lib/rw-splitting.lua
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, #默认超过 4个连接数时,才开始读写分离,改为 1
max_idle_connections = 1, #默认 8,改为1
is_debug = false
}
end
#这里的 4 、8 是指定链接数，做测试时调整为 1
5、设置mysql-proxy 开机启动,并启动mysql-proxy

[root@proxy ~]# chkconfig mysql-proxy on
[root@proxy ~]# service mysql-proxy start
[root@proxy ~]# netstat -tnlp | grep mysql-proxy
tcp 0 0 172.25.17.7:4040 0.0.0.0:* LISTEN 27352/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 27352/mysql-proxy
6、在master数据库授权mysql-proxy访问
[root@master ~]# mysql -puplooking
grant all on . to ‘proxy’@‘172.25.17.7’ identified by ‘123456’;
grant all on . to ‘proxy’@‘rhel6’ identified by ‘123456’;
flush privileges;

7 、测试读写分离

测试读写分离,要让两台服务器数据有误差才行，所以暂停slave1(172.25.17.20)上的从服务:

mysql -puplooking > stop slave;

在 master(172.25.17.19) 服务器上修改数据:

mysql> create tabletest.t1(id int);

mysql> insert intotest.t1 values (1);mysql> insert into test.t1 values (3);

mysql> insert intotest.t1 values (2);mysql> insert into test.t1 values (4);

在 slave1(172.25.17.20) 服务器上修改数据:

mysql> create tabletest.t1(id int);

mysql> insert into test.t1values (5);

mysql> insert intotest.t1 values (6);

在 调度服务器mysql-proxy 上打开若干个终端远程访问数据库,读取数据:

[root@proxy ~]# yum -y install mysql #安装数据库添加mysql启动命令
[root@proxy ~]# mysql -uproxy -p123456 -h172.25.17.7 -P4040
#远程访问数据库
mysql> select * from test1.t1;
±-----+
| id |
±-----+
| 1 |
| 2 |
| 3 |
| 4 |
±-----+
4 rows in set (0.00 sec)

mysql> select * from test01.t1;
±-----+
| id |
±-----+
| 5 |
| 6 |
±-----+
2 rows in set (0.00 sec)
显示结果不一样，证明配置成功。多次尝试以上两行代码直到显示不同结果。

试验结束在slave1服务器上slave start恢复主从同步。

#####################################################

读写分离，延迟是个大问题
在 slave 服务器上执行 show slave status,
可以看到很多同步的参数,要注意的参数有：
Master_Log_File:slave 中的 I/O 线程当前正在读取的 master 服务器二进制式日志文件名.
Read_Master_Log_Pos:在当前的 master 服务器二进制日志中，slave 中的 I/O 线程已经读取的位置
Relay_Log_File:SQL 线程当前正在读取与执行中继日志文件的名称
Relay_Log_Pos:在当前的中继日志中，SQL 线程已读取和执行的位置
Relay_Master_Log_File:由 SQL 线程执行的包含多数近期事件的 master 二进制日志文件的名称
Slave_IO_Running:I/O 线程是否被启动并成功连接到 master
Slave_SQL_Running:SQL 线程是否被启动
Seconds_Behind_Master:slave 服务器 SQL 线程和从服务器 I/O 线程之间的差距，单位为秒计

slave 同步延迟情况出现：
1.Seconds_Behind_Master 不为了，这个值可能会很大
2.Relay_Master_Log_File 和 Master_Log_File 显示 bin-log 的编号相差很大，说明 bin-log 在 slave 上
没有及时同步，所以近期执行的 bin-log 和当前 I/O 线程所读的 bin-log 相差很大
3.mysql 的 slave 数据库目录下存在大量的 mysql-relay-log 日志，该日志同步完成之后就会被系统自动
删除，存在大量日志，说明主从同步延迟很厉害

mysql 主从同步原理
主库针对读写操作，顺序写 binlog，从库单线程去主库读"写操作的 binlog",从库取到 binlog 在本地原
样执行(随机写),来保证主从数据逻辑上一致.
mysql 的主从复制都是单线程的操作，主库对所有 DDL 和 DML 产生 binlog，binlog 是顺序写，所以
效率很高，slave 的 Slave_IO_Running 线程到主库取日志，效率比较高，下一步问题来 了，slave 的
slave_sql_running 线程将主库的 DDL 和 DML 操作在 slave 实施。DML，DDL 的 IO 操作是随即的，
不能顺序的，成本高很多，还有可能 slave 上的其他查询产生 lock，由于 slave_sql_running 也是单线
程的，所以 一个 DDL 卡住了，需求需求执行一段时间，那么所有之后的 DDL 会等待这个 DDL 执行完
才会继续执行，这就导致了延迟.由于 master 可以并发，Slave_sql_running 线程却不可以，所以主库执
行 DDL 需求一段时间，在 slave 执行相同的 DDL 时，就产生了延迟.

主从同步延迟产生原因
当主库的 TPS 并发较高时，产生的 DDL 数量超过 Slave 一个 sql 线程所能承受的范围，那么延迟就产
生了，当然还有就是可能与 slave 的大型 query 语句产生了锁等待
首要原因：数据库在业务上读写压力太大，CPU 计算负荷大，网卡负荷大，硬盘随机 IO 太高
次要原因：读写 binlog 带来的性能影响，网络传输延迟

主从同步延迟解决方案
架构方面
1.业务的持久化层的实现采用分库架构，mysql 服务可平行扩展分散压力
2.单个库读写分离，一主多从，主写从读，分散压力。
3.服务的基础架构在业务和 mysql 之间加放 cache 层
4.不同业务的 mysql 放在不同的机器
5.使用比主加更了的硬件设备作 slave
反正就是 mysql 压力变小，延迟自然会变小
硬件方面：
采用好的服务器
mysql 主从同步加速
1、sync_binlog 在 slave 端设置为 0
2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
3、直接禁用 slave 端的 binlog
4、slave 端，如果使用的存储引擎是 innodb，innodb_flush_log_at_trx_commit =2
从文件系统本身属性角度优化
master 端
修改 linux、Unix 文件系统中文件的 etime 属性， 由于每当读文件时 OS 都会将读取操作发生的时间回
写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响 I/O 性能。
可以 通过设置文件系统的 mount 属性，组织操作系统写 atime 信息，在 linux 上的操作为：
打开/etc/fstab，加上 noatime 参数
/dev/sdb1 /data reiserfs noatime 1 2
然后重新 mount 文件系统
#mount -oremount /data
主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设
置是需要的
而 slave 则不需要这么高的数据安全，完全可以讲 sync_binlog 设置为 0 或者关闭
binlog，innodb_flushlog 也可以设置为 0 来提高 sql 的执行效率
1、sync_binlog=1 o
MySQL 提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上去。
默认，sync_binlog=0，表示 MySQL 不控制 binlog 的刷新，由文件系统自己控制它的缓存的刷新。这
时候的性能是最好的，但是风险也是最大的。一旦系统 Crash，在 binlog_cache 中的所有 binlog 信息都
会被丢失。
如 果 sync_binlog>0，表示每 sync_binlog 次事务提交，MySQL 调用文件系统的刷新操作将缓存刷下
去。最安全的就是 sync_binlog=1 了，表示每次事务提交，MySQL 都会把 binlog 刷下去，是最安全但
是性能损耗最大的设置。这样的话，在数据库所在的主机 操作系统损坏或者突然掉电的情况下，系统才
有可能丢失 1 个事务的数据。
但是 binlog 虽然是顺序 IO，但是设置 sync_binlog=1，多个事务同时提交，同样很大的影响 MySQL
和 IO 性能。
虽然可以通过 group commit 的补丁缓解，但是刷新的频率过高对 IO 的影响也非常大。对于高并发事务
的系统来说，
“sync_binlog”设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。
所以很多 MySQL DBA 设置的 sync_binlog 并不是最安全的 1，而是 2 或者是 0。这样牺牲一定的一致
性，可以获得更高的并发和性能。
默 认情况下，并不是每次写入时都将 binlog 与硬盘同步。因此如果操作系统或机器(不仅仅是 MySQL
服务器)崩溃，有可能 binlog 中最后的语句丢失 了。要想防止这种情况，你可以使用 sync_binlog 全局
变量(1 是最安全的值，但也是最慢的)，使 binlog 在每 N 次 binlog 写入后与硬盘同 步。即使
sync_binlog 设置为 1,出现崩溃时，也有可能表内容和 binlog 内容之间存在不一致性。
2、innodb_flush_log_at_trx_commit （这个很管用）
抱怨 Innodb 比 MyISAM 慢 100 倍？那么你大概是忘了调整这个值。默认值 1 的意思是每一次事务提交
或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery
backed up cache）时。设成 2 对于很多运用，特别是从 MyISAM 表转过来的是可以的，它的意思是不
写入硬盘而是写入系统缓存。
日志仍然会每秒 flush 到硬 盘，所以你一般不会丢失超过 1-2 秒的更新。设成 0 会更快一点，但安全方
面比较差，即使 MySQL 挂了也可能会丢失事务的数据。而值 2 只会在整个操作系统 挂了时才可能丢数
据。

3.进行分库分表处理，这样减少数据量的复制同步操作