Mysql 开启主从复制实战笔记

一、背景介绍
     由于目前系统测试环境数据库部署在公司虚机上，已经部署使用一段时间了，由于虚机存在宕机数据丢失的风险，于是准备开启数据库主从复制备份机制，防止虚机宕机造成损失。

二、操作步骤
     1)数据迁移
        由于测试环境数据库已经使用一段时间了，所以需要将现有数据库结构和数据迁移到从机器数据库中，可以使用以下两种方式同步：
方法一：

use my_data; //使用当前数据库
lock tables; //锁住数据库所有表(可选)
//导出数据库 schema sql
mysqldump -uroot -p  -h127.0.0.1 -P3306 my_data >my_data.sql
//同时导出多个数据
mysqldump -uroot -p -h127.0.0.1 -P3306 --databases my_data1 my_data2 > my_data.sql
unlock tables; //解锁数据库所有表

  接下来可以将my_data.sql 文件通过scp或者rz/sz方式复制到从数据库虚机上，执行如下操作：

mysql -uroot -p -h127.0.0.1 -P3306  //登录从数据库
source  /home/root/my_data.sql;   //执行SQL备份文件

方法二：
     借助Navicat客户端工具，Navicat-工具-结构同步 和 Navicat-工具-数据同步功能，可简单实现两个数据库间主备同步，前提是从数据库中需要手动创建database；

      2)修改主数据库配置(my.cnf )

ps aux|grep mysql   //找到mysql的安装路径   例:安装目录为/export/services/mysql
ps aux|grep mysql|grep 'my.cnf'   //查找mysql是否有指定my.cnf配置文件路径

//如果没有找到mysql手动指定my.cnf配置，mysql会默认使用/export/services/mysql/my.cnf文件，
//如果/export/services/mysql目录下没有my.cnf文件，会使用/export/services/mysql/etc/my.cnf文件

     需要修改主数据库my.cnf 文件内容如下所示：

#--- 确认bin-log是打开状态(值mysql-bin是日志的基本名或前缀名) 默认关闭---#
log-bin = mysql-bin
#--- 二进制日志的格式(statement/row/mixed) ---#
binlog_format           = mixed
#--- 数据库唯一ID，非必须修改，只要保证主数据库和从数据库ID不相同即可 ---#
server-id=101

   修改完主数据库my.cnf文件时需要对数据库进行重启，重启命令如下所示：

service mysqld restart; //重启
service mysqld stop;   //关闭数据库
service mysqld start; 开启数据库

//或者
./export/servers/mysql/bin/mysqld restart ;//export/servers/mysql 为mysql的安装目录
./export/servers/mysql/bin/mysqld stop ;
./export/servers/mysql/bin/mysqld start;

    3)在主数据库为从数据库添加操作用户

mysql -uroot -p -h127.0.0.1 -P3306 //登录主数据库
//添加一个myread/myread 用户，并授予这个用户具有读取的权限，并且只能被IP：192.168.1.184使用
GRANT REPLICATION SLAVE ON *.* TO 'myread'@'192.168.1.184' IDENTIFIED BY 'myread';
//刷新系统权限
flush privileges;

    4)修改从数据库配置(my.cnf )
         从数据库my.cnf不需要做过多修改，只需要保证从数据库my.cnf中的server-id与主数据库的不一致就可以，否则会出现如下错误：(修改完需要对从数据库进行重启)

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

    5)查找主数据库当前bin日志信息

mysql -uroot -p -h127.0.0.1 -P3306   //链接主数据库
show master status\G; //查看主数据库binlog信息，记下File和Position

  
   6)配置从数据库

mysql -uroot -p -h127.0.0.1 -P3306   //登录从数据库
stop slave;   //关闭主从复制，怕以前配置过，先关一次
//执行关联主数据库，master_log_file和master_log_pos 两个配置项来源于主数据库，当master_log_pos设置为0时，默认取主数据库最后一次事件操作结束点
change master to master_host='192.168.1.183', MASTER_PORT=3306, master_user='myread',   master_password='myread',master_log_file='mysql-bin.000004', master_log_pos=27015301;

start slave; //开启主从复制
show slave status\G; //查看开启主从复制是否开启成功

  如果 show slave status\G; 命令结果如下所示：

 当Slave_IO_Running 和Slave_SQL_Running 都为Yes时表示主从配置重新，接下来就可以在主数据库中执行个SQL验证一下，看是否能正常同步到从数据库。

三、遇到的问题
   1) Slave_IO_Running: Connecting
    出现此问题的原因在于主从机器无法正常通话，可通过以下方式排查此问题：

• 主从数据库不在一个网段导致网路不通
• 从数据库change master时，配置主数据库的master_user 和master_password 配置错误
• 从数据库change master时，配置主数据库的IP或端口配置错误

   2) Slave_IO_Running: No
     出现此问题的原因从数据库配置向主数据库进行读取时出现问题，需要关注show slave status\G; 命令结果中的Last_IO_Errno和Last_IO_Error 来排查问题，遇到到从数据库的server-id 和主数据库的配置相同了，出现了如下的错误：(修改从数据库server-id后问题恢复正常)

Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

   3) Slave_SQL_Running: No
       出现此问题时在于从主数据库抓取到bin-log日志后，执行时出现错误，需要关注show slave status\G; 命令结果中的Last_IO_Errno和Last_IO_Error 来排查问题，曾遇到过从数据库和主数据库binlaog事件结束节点不一致，导致无法正常同步，出现问题如下所示：

Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table xxxxx.xxxxxxxxx; Can't find record in 'xxxxxxx', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 26604323

解决办法需要在从数据库中执行如下命令：

mysql -uroot -p -h127.0.0.1 -P3306  //登录从数据库
stop slave;  //停止主从复制
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=26604323;//补录
start slave; //开启主从复制
show slave status\G; //查看同步状态

注：需要定时观察主从状态show slave status\G; 来判断主从同步情况

四、常用命令

SET PASSWORD = PASSWORD('root');  //设置当前用户密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root'); //设置指定用户密码
//在192.168.%.%开头的机器root用户远程访问时，所有数据库，开启全部权限
grant all on *.* to 'root'@'192.168.%.%';