一、背景介绍
由于目前系统测试环境数据库部署在公司虚机上,已经部署使用一段时间了,由于虚机存在宕机数据丢失的风险,于是准备开启数据库主从复制备份机制,防止虚机宕机造成损失。
二、操作步骤
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.%.%';