如何监控MySQL主从同步情况

一、如何监控MySQL主从同步情况

1、用 pt-table-checksum时，会不会影响业务性能？

2、实验开始前，给大家分享一个小经验：任何性能评估，不要相信别人的评测结果，要在自己的环境上测试，并（大概）知晓原理。

3、然后用 mysqlslap跑一个持续的压力：

4、开另外一个会话，将 master上的 general log打开：

5、然后通过 pt-table-checksum进行一次比较：

6、查看 master的 general log，由于 mysqlslap的影响，general log中有很多内容，我们找到与 pt-table-checksum相关的线程：

7、操作比较多，我们一点一点来说明：

8、这里工具调小了 innodb锁等待时间。使得之后的操作，只要在 innodb上稍微有锁等待，就会马上放弃操作，对业务影响很小。

9、另外工具调小了 wait_timeout时间，倒是没有特别的作用。

10、工具将隔离级别调整为了 RR级别，事务的维护代价会比 RC要高，不过后面我们会看到工具使用的每个事务都很小，加上之前提到 innodb锁等待时间调到很小，对线上业务产生的成本比较小。

11、工具通过一系列操作，了解表的概况。工具是一个数据块一个数据块进行校验，这里获取了第一个数据块的下边界。

12、接下来工具获取了下一个数据块的下边界，每个 SQL前都会 EXPLAIN一下，看一下执行成本，非常小心翼翼。

13、之后工具获取了一个数据块的 checksum，这个数据块不大，如果跟业务流量有冲突，会马上出发 innodb的锁超时，立刻退让。

14、以上是 pt-table-checksum的一些设计，可以看到这几处都是精心维护了业务流量不受影响。

15、工具还设计了其他的一些机制保障业务流量，比如参数--max-load和--pause-file等，还有精心设计的数据块划分方法，索引选择方法等。大家根据自己的情况配合使用即可达到很好的效果。

16、本期我们介绍了简单分析 pt-table-checksum是否会影响业务流量，坊间会流传工具的各种参数建议或者不建议使用，算命的情况比较多，大家都可以用简单的实验来分析其中机制。

17、还是那个观点，性能测试不能相信道听途说，得通过实验去分析。

二、配置mysql主从 数据库怎么同步过来

MySQL从3.23.15版本以后提供数据库复制（replication）功能，利用该功能可以实现两个数据库同步、主从模式、互相备份模式的功能。本文档主要阐述了如何在linux系统中利用mysql的replication进行双机热备的配置。

操作系统：Linux 2.6.23.1-42.fc8# SMP（不安装XEN）

设备环境：PC（或者虚拟机）两台

数据库同步复制功能的设置都在MySQL的配置文件中体现，MySQL的配置文件（一般是my.cnf）：在本环境下为/etc/my.cnf。

在IP设置完成以后，需要确定两主机的防火墙确实已经关闭。可以使用命令service iptables status查看防火墙状态。如果防火墙状态为仍在运行。使用service iptables stop来停用防火墙。如果想启动关闭防火墙，可以使用setup命令来禁用或定制。

最终以两台主机可以相互ping通为佳。

3.2配置A主(master) B从(slave)模式

、增加一个用户同步使用的帐号：

GRANT FILE ON*.* TO‘backup’@'10.10.8.112' IDENTIFIED BY‘1234’;

GRANTREPLICATION SLAVE ON*.* TO‘backup’@'10.10.8.112' IDENTIFIED BY‘1234’;

赋予10.10.8.112也就是Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。

、增加一个数据库作为同步数据库：

create table mytest(username varchar(20),password varchar(20));

修改A的/etc/my.cnf文件，在my.cnf配置项中加入下面配置：

binlog-do-db=test#指定需要日志的数据库

show variable like‘server_id’；

mysql> show variables like'server_id';

、用show master status/G命令看日志情况。

mysql> show master status/G

*************************** 1. row***************************

、增加一个数据库作为同步数据库：

create table mytest(username varchar(20),password varchar(20));

修改B的/etc/my.cnf文件，在my.cnf配置项中加入下面配置：

master-user=backup#同步用户帐号

master-connect-retry=60#预设重试间隔秒

replicate-do-db=test#告诉slave只做backup数据库的更新

show variables like‘server_id’；

mysql> show variables like'server_id';

、用show slave status/G命令看日志情况。

*************************** 1. row***************************

Slave_IO_State: Waiting for master to send event

Master_Log_File: mysqld-bin.000001

Relay_Log_File: mysqld-relay-bin.000003

Relay_Master_Log_File: mysqld-bin.000001

分别使用insert, delete, update在A主机进行增删改查数据库；查看B主机的数据库是否与A主机一致；若一致，则配置成功。

如果在A主机加入slave设置，在B主机加入master设置，则可以做B->A的同步。

、在A主机的配置文件中 mysqld配置项加入以下设置：

、在B的配置文件中 mysqld配置项加入以下设置：

注意：当有错误产生时，*.err日志文件同步的线程退出，当纠正错误后，要让同步机制进行工作，运行slave start。

重起A、B机器，则可以实现双向的热备份。

、Slave机器的权限问题，不但要给slave机器File权限，还要给它REPLICATION SLAVE的权限。

、在修改完Slave机器/etc/my.cnf之后，slave机器的mysql服务启动之前，记得要删除掉master.info

、在show master status或着show slave status不正常时，看看.err是怎样说的。

、Slave上Mysql的Replication工作有两个线程, I/O thread和SQL thread。I/O的作用是从master 3306端口上把它的binlog取过来(master在被修改了任何内容之后,就会把修改了什么写到自己的binlog等待slave更新),然后写到本地的relay-log,而SQL thread则是去读本地的relay-log,再把它转换成本Mysql所能理解的语句，于是同步就这样一步一步的完成.决定I/O thread的是/var/lib/mysql/master.info,而决定SQL thread的是/var/lib/mysql/relay-log.info.

、启动slave，命令用start slave；重新启动用restart slave

三、mysql 主从如何设置同步

同步介绍 mysql的主从同步是一个异步的复制过程，从一个 Master复制到另一个 Slave上。在 Master与 Slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 Slave端，另外一个线程(IO线程)在 Master端。要实现 MySQL的主从同步，首先必须打开 Master端的BinaryLog(mysql-bin)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。打开 MySQL的 Binary Log可以通过在启动 MySQL Server的过程中使用“—log-bin”参数选项，或者在 my.cnf配置文件中的 mysqld参数组([mysqld]标识后的参数部分)增加“log-bin”参数项。

Mysql主从服务器配置主服务器IP:192.168.1.60从服务IP:192.168.1.61 Linux:centOS Mysql版本：5.1.58以下配置皆在此服务上进行配置

查看主库服务器的my.cnf配置文件，根据mysql安装包、版本不同，相关的路径和配置文件路径会大致不一样，不过大多情况下是一样的。该文件在 etc/目录下。Mysql服务也提供了对不同服务器配置使用不同的配置文件，要使用它们的时候，只需用它们任一一个替换上面的my.cnf文件即可。注：替换文件名必须为my.cnf。 cnf配置文件一般会有四个，可以根据命令# ll/usr/share/mysql/*.cnf查看； my-small.cnf内存少于或等于64M，只提供很少的的数据库服务； my-medium.cnf内存在32M--64M之间而且和其他服务一起使用，例如web； my-large.cnf内存有512M主要提供数据库服务； my-huge.cnf内存有1G到2G，主要提供数据库服务； my-innodb-heavy-4G.cnf内存有4G，主要提供较大负载数据库服务（一般服务器都使用这个）；可以根据服务器配置的不同选择不同的cnf配置文件。

配置my.cnf文件采用系统默认的etc/my.cnf配置文件进行配置，对主服务器进行配置主要需要在配置文件中添加以下几项（在添加的时候要确保它们不在文件中存在，因为如选择上面几种不同的配置文件，配置里的内容会不一样,如果没有相应的项，需要添加）。用vi etc/my.cnf打开文件，对文件进行修改，在[mysqld]下面进行添加修改： Server-id= 1#这是数据库ID,此ID是唯一的，主库默认为1，其他从库以此ID进行递增，ID值不能重复，否则会同步出错； log-bin= mysql-bin二进制日志文件，此项为必填项，否则不能同步数据； binlog-do-db= testcreate#需要同步的数据库，如果需要同步多个数据库；则继续添加此项。 binlog-do-db= testcreate1 binlog-do-db= testcreate2 binlog-ignore-db= mysql不需要同步的数据库；至此主服务器配置my.cnf配置完成，保存退出文件。现在对Mysql服务器进行重启：#servicemysqld restart如果重启失败，则证明配置文件错误，需重新检查。建议：对my.cnf修改之前最好对其进行备份，以免配置文件修改失败不能及时重启服务器。

配置完成后需要建立同步用的数据库账户主库需要提供一个账户让从库对起连接并同步，用命令mysql服务器 mysqlgrantreplication slave on*.* to‘testcreate’@’192.168.1.61’ identified by‘123456’;说明：’testcreate’：同步的数据库；‘192.168.1.61’：同步的数据库地址；‘123456’：同步的数据库密码，在从库配置时需要设置。

显示主库信息 mysqlshowmaster status;执行上述命令显示：看到上述信息则表示主库建立成功。

备份数据库(如果从服务器也为新建库，则不需要此操作)备份数据库的方法有很多种：如果在同一局域网可以直接复制数据库，也可以用工具直接进行数据导入。复制数据库用打包的方法：# cd/data转到存放Mysql数据库的路径下# tarcvfz testcreate.tar.gz db/再从数据库中进行复制解压# cd/data# scp192.168.1.61:/data/testcreate.tar.gz#tarxvfz testcreate.tar.gz数据复制完毕；

从库配置从服务器的配置基本与主库配置差不多，选择的从库服务器为：192.168.1.61 1.配置从库服务my.cnf文件# vietc/my.cnf对其进行如下修改： Server-id= 2#这里ID改为2因为主库为1; log-bin= mysql-bin必填项，用于数据同步; master-host= 192.168.1.60主库IP; master-user= test同步用的账户; master-password= 123456同步账户密码，主库时的设置; master-port= 3306同步数据库的端口号。

重启从库服务器#service mysqld restart如果重启失败则配置文件有问题，需重新检查配置。

调整复制过来的数据库的权限（如果有从主库复制过来的数据库）主库复制过来的数据库权限都为root权限，需要修改为mysql权限#chown–R mysql:mysql/var/lib/mysql如果从库是新建的库就不需要进行此步骤

显示从服务器状态进行mysql服务器： mysqlshowslave status\G;显示如图：

如果slave_io_running和slave_sql_running都为yes则表示从服务器配置成功。如果遇到关于position相关的错误，就需要再手动设置以下File/Postion的信息 mysqlChangemaster to master_host=‘192.168.1.60’ master_user=‘test’ master_password=’123456’ master_log_file=’mysql-bin.000001’,主库的文件信息 master_log_pos=’106’;主库的Position信息