MySQL主从同步（二）

数据库 主从同步

---

一、生产场景快速配置MySQL主从复制方案

场景情况：主库在线上运行，需要快速配置一台从库，并建立主从同步。

1. 安装好需要配置成为从库的数据库，配置好log-bin以及server-id参数；
2. 无需配置主库my.cnf文件（因为当前的主库正在线上运行着），主库的log-bin以及server-id参数都已经是配置好的；
3. 登录主库，创建从库用来连接主库的同步账号，并授予同步复制权限（replication slave）；
4. 使用夜间通过mysqldump --master-data=1备份的全备文件导入到从库；
5. 在从库上执行相应的CHANGE MASTER TO语句，而无需提供Binlog文件名及位置点（因为--master-data=1参数已经使全备文件自动携带了Binlog日志文件名及位置点了）；
6. 在从库上开启同步开关start slave；
7. 在从库上运行show slave status语句，查看同步状态信息，如有必要可进行同步测试。

二、生产环境主从同步配置注意事项

1、首次主从同步如何操作

第一步：申请设备资源，用来作为从库服务器（硬件配置尽量和主库相当）；
第二步：撰写方案文档和实施步骤
假如目前只有主库服务器在线上运行（也就是没有配置主从同步），现在由于业务需求，要求配置主从同步，此时可能需要和公司申请停机维护时间（要确认这个时间段内可以完成一次全备，且该时间一般都是在凌晨），即在用户访问量最小，且不影响内部其他业务运转的时间段进行停机（包括锁表），并配置主从同步。注意，停机（锁表，停库）的最小时间段，为锁表后进行全备的时间，也就是说无需等待主从同步完全配置完毕。
当然，也可以不申请停机，在定时任务中设置每天深夜进行一次全备，而进行全备时做一些相应的措施即可（见下文）。（提示，虽然可以不停机，但是在备份期间的数据库访问多少都会受到影响，需要和领导以及其他相关部门充分沟通）

三、无需熬夜，在工作时间轻松配置从库

当然了，也可以不申请停机，如上所述，在定时任务中配置每天夜里进行全备，同时模拟主从同步的步骤，获取到全备及全备过程中Binlog的位置信息，或者直接使用--master-data选项。

四、不停主库，批量创建从库

创建从库的有两点最为关键：导入全备文件，并依据进行全备时的主库Binlog日志及其位置点执行CHANGE MASTER TO语句。
有两种方法获得日志文件及位置点信息：
1、停库，进行全备，并记录show master status显示的位置信息；
2、在执行全备指令时通过使用mys--master-data=1选项使得备份数据中携带了备份时的Binlog日志文件名及位置点。

首先通过批量分发将全备文件分发到各个从库上去，然后制作脚本

五、查看线程状态信息

MySQL服务器的线程数需要在一个合理的范围之内，这样才能保证MySQL服务器健康平稳地运行。查看线程数量可以通过下面的语句：

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 15    |
| Threads_running   | 1     |
+-------------------+-------+

查看主库或从库上有哪些线程在运行，需要执行如下指令进行查看：

mysql> show processlist;
#该指令只能列出前100条，如果想全列出请使用FULL关键字：
mysql> show full processlist；
#这里只截取一段内容进行解析。
*************************** 1. row ***************************
     Id: 13    #连接标识
   User: system user    #MySQL用户。
   Host:     #显示这个语句是从哪个IP的哪个端口上发出的，可以用来追踪出问题语句的用户。
     db: NULL       #默认的数据库，即这个线程目前连接的是哪个数据库 。如果没有则显示NULL。
Command: Connect    #该线程正在执行的指令类型。一般就是休眠（Sleep），查询（Query）以及连接（Connect）。
   Time: 52687      #状态持续的时间，单位是秒。
  State: Waiting for master to send event
   Info: NULL       #显示该SQL语句，因长度有限，所以长的语句就显示不全，但这是一个判断问题语句的重要依据。
#这个线程其实是从库上的IO线程，所以许多字段没有信息。还可以参考下面的示例。

当执行SHOW PROCESSLIST语句后往往会有很多行输出，每行输出对应一个MySQL连接。那么怎么判断发起连接的是哪个线程、它当前正在进行什么操作呢？
首先，需要通过TCP/IP方式而不是Unix Socket连接MySQL，这样在语句的输出中就会有来源端口号。如下所示，

[root@MySQL-E ~]# mysql -uroot -h 127.0.0.1
mysql> show processlist\G
*************************** 4. row ***************************
     Id: 18
   User: root
   Host: localhost:38016    #该链接的来源IP与端口号。
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist   #所执行的SQL语句。
#在Host列有来源IP和端口号，然后我们从该客户端主机上查看端口号是谁打开的：
[root@MySQL-E ~]# netstat -ntp|grep 38016
tcp        0      0 127.0.0.1:38016             127.0.0.1:3306              TIME_WAIT   -

官方文档：http://dev.mysql.com/doc/refman/5.5/en/show-processlist.html

当然，也可以使用mysqladmin指令得到同样的信息：

[root@MySQL-B ~]# mysqladmin -uroot -h 127.0.0.1 processlist

如果具备SUPER权限，则可以看到所有线程。否则，就只能看到当前用户自己的线程（也就是，与当前用户正在使用的MySQL账户相关的线程）。

具体的状态信息及其表示的意思，请参见官方手册。
通过查看线程的状态，我们可以检查主库与从库之间是否完成主从复制，用于主库宕机或者人工进行数据库主从切换迁移等。
主库如果出现故障，那么需要提升同步速度最快的从库成为新的主库，