通过主从复制机制完成MySQL数据库服务迁移_MySQL-mysql チュートリアル-php.cn

0x00 背景

业务所在机房裁撤，原业务机器也已经过保，通过MySQL主从复制机制完成MySQL数据服务的无缝迁移。

0x01 准备

1.环境：

原则上搭建mysql主从复制最好是操作系统版本、环境，MySQL版本、配置保持一致，这样可以保证MySQL主从集群的稳定性，以及减少版本和环境造成的异常，便于排查和定位问题。

由于我们涉及迁移的机器往往是很久以前上线，而且也从未有相关系统和服务升级的机制，还好这次涉及的MySQL版本比较高，与MySQL 5.6的兼容性还是比较好的，谢天谢地，谢前任。

原机器环境：

IP:A（机器已回收）

系统版本：suse 11 linux x64

mysql版本：mysql 5.5.3

配置文件路径：无

程序启动方式：/bin/sh /usr/local/mysql/bin/mysqld_safe &

新机器环境：

IP:xxxxxx B

系统版本： tlinux 1.2 64bit(centos 6.2)

mysql版本：mysql-5.6.25

配置文件路径： /etc/my.cnf

程序启动方式： /etc/init.d/mysqld start

2.安装包准备

源码包下载：

cmake：yum install cmake （2.6.4）即可或下载 https://cmake.org/files/v3.3/cmake-3.3.2.tar.gz

MySQL :http://dev.mysql.com/downloads/mysql/5.6.html#downloads

0x02 MySQL安装与配置

1.安装(这里不是本文重点，如果出错了，需要根据错误分析解决)

原则上，运营环境一般推荐通过源码进行编译安装，这样才能充分利用当前机器的特性，但是由于我们以前在相同系统环境下编译安装并制作了相关部署包，所以真实安装过程就略过了。这里的安装过程是我们一般通用的安装过程：

a. yum 安装

yum install mysql mysql-server （推荐tlinux2.0，对应centos 7.0，fedora 20+）

很不幸，tilnux 1.2环境 yum安装的版本为mysql-5.1.61，老掉牙了。

b. 源码编译安装

cd mysql-5.6.25groupadd mysql

useradd -g mysql mysql -s /bin/false

mkdir -p /data/dbdata

chown mysql:mysql /data/dbdata

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/dbdata -DSYSCONFDIR=/etc/

make&&make install

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

cd /usr/local/

mv mysql mysql-5.6.25 && ln -s mysql-5.6.25 mysql

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

chkconfig --add mysqld

chkconfig mysqld on

初始化mysql：

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/dbdata

vi /etc/profile

增加：export PATH=$PATH:/usr/local/mysql/bin

/etc/init.d/mysqld start

2.配置

旧机器：

设置server id：

set gloabl server_id=2；select @@server_id；

开启binlog：

SET SQL_LOG_BIN=1；

SET GLOBAL binlog_format = 'MIXED'; ##表结构变更以statement模式来记录，update或者delete等修改数据的语句是记录所有行的变更。

mysql> select @@server_id;

+-------------+

| @@server_id |

+-------------+

| 2 |

+-------------+

1 row in set (0.00 sec)

新机器：

vi /etc/my.cnf

log-bin = /data/dbdata/binlog/mysql-binbinlog_format = MIXED

binlog_cache_size = 4M

max_binlog_cache_size = 1024M

max_binlog_size = 1024M

expire_logs_days = 5log-slave-updates

server-id = 20151109

mysql> select @@server_id;+-------------+

| @@server_id |+-------------+

| 20151109 |

+-------------+1 row in set (0.00 sec)

0x03 数据的导出与导入

1.数据导出：

涉及的DB不多，DB的读写不频繁，导出mysql数据我们选择使用mysqldump。

因为要添加主从信息，所以需要添加-master-data=1，附带锁表操作，当表的存储引擎为InnoDB时，加了 --single-transaction 可以减少锁表的影响，准确的说只会有短时间的全局读锁，比MyISAM的锁表情况要好得多。

mysqldump -u root --default-character-set=utf8 -Y -B --set-charset --single-transaction --master-data=1 hehehehhe > /data/backup/databases/hehehehhe20151109.sql

CHANGE MASTER

-Y, --all-tablespaces

Dump all the tablespaces.

-B, --databases Dump several databases. Note the difference in usage; in

if you dump many databases at once (using the option

--databases= or --all-databases), the logs will be

Locks all tables across all databases. This is achieved

--all-databases or --databases is given.

--master-data[=#] This causes the binary log position and filename to be

appended to the output. If equal to 1, will print it as a

CHANGE MASTER command; if equal to 2, that command will

be prefixed with a comment symbol. This option will turn

--lock-all-tables on, unless --single-transaction is

specified too (in which case a global read lock is only

taken a short time at the beginning of the dump; don't

forget to read about --single-transaction below). In all

cases, any action on logs will happen at the exact moment

of the dump. Option automatically turns --lock-tables

off.

--dump-slave[=#] This causes the binary log position and filename of the

master to be appended to the dumped data output. Setting

the value to 1, will printit as a CHANGE MASTER command

in the dumped data output; if equal to 2, that command

will be prefixed with a comment symbol. This option will

turn --lock-all-tables on, unless --single-transaction is

specified too (in which case a global read lock is only

taken a short time at the beginning of the dump - don't

forget to read about --single-transaction below). In all

cases any action on logs will happen at the exact moment

of the dump.Option automatically turns --lock-tables off.

--include-master-host-port

Adds 'MASTER_HOST=, MASTER_PORT=' to 'CHANGE

MASTER TO..' in dump produced with --dump-slave.

--single-transaction

Creates a consistent snapshot by dumping all tables in a

single transaction. Works ONLY for tables stored in

storage engines which support multiversioning (currently

only InnoDB does); the dump is NOT guaranteed to be

consistent for other storage engines. While a

--single-transaction dump is in process, to ensure a

valid dump file (correct table contents and binary log

position), no other connection should use the following

statements: ALTER TABLE, DROP TABLE, RENAME TABLE,

TRUNCATE TABLE, as consistent snapshot is not isolated

from them. Option automatically turns off --lock-tables.

--set-charset Add 'SET NAMES default_character_set' to the output.

(Defaults to on; use --skip-set-charset to disable.)

查看主从信息：

[root@WEBAPP_B_IP_HOST /data/backup/databases]#

自带切换主从同步点命令，需要注意的是添加此命令时需要将所有主从同步状态的数据库数据一同导出。

grep CHANGE /data/backup/databases/hehehehhe20151109.sql

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

2.数据导入：

新机器：

CREATE DATABASE `hehehehhe` /*!40100 DEFAULT CHARACTER SET utf8 */；

搭建主从同步后stop slave，直接通过mysql 直接导入数据，然后在start slave即可。

0x04 主从复制配置与数据的同步

1. MySQL binlog

binlog是MySQL主从复制的基础，MySQL通过binlog来记录数据库数据的变更，可用来搭建主从复制集群，也可以用mysqlbinlog来通过binlog恢复部分数据异常。

如果遇到灾难事件，应该用最近一次制作的完整备份恢复数据库，然后使用备份之后的日志文件把数据库恢复到最接近现在的可用状态。使用日志进行恢复时需要依次进行，即最早生成的日志文件要最先恢复。

常用binlog日志操作命令

1.查看所有binlog日志列表

mysql> show master logs;（新机器作为主时，binlog的信息）

[root@WEBAPP_B_IP_HOST /data/dbdata/binlog]# pwd/data/dbdata/binlog

[root@WEBAPP_B_IP_HOST /data/dbdata/binlog]# lltotal 884156-rw-rw---- 1 mysql admin 27317 Nov 9 12:41 mysql-bin.000001-rw-rw---- 1 mysql admin 1034478 Nov 9 12:41 mysql-bin.000002-rw-rw---- 1 mysql admin 531 Nov 9 12:42 mysql-bin.000003-rw-rw---- 1 mysql admin 903407219 Nov 12 00:10 mysql-bin.000004-rw-rw---- 1 mysql admin 148 Nov 9 15:08 mysql-bin.index

[root@WEBAPP_B_IP_HOST /data/dbdata/binlog]# mysql -e "show master logs;"+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 27317 |

| mysql-bin.000002 | 1034478 |

| mysql-bin.000003 | 531 |

| mysql-bin.000004 | 903407219 |

+------------------+-----------+

2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;（新机器作为从时，主服务器最新binlog的位置信息）

[root@WEBAPP_B_IP_HOST /data/dbdata/binlog]# mysql -e "show master status;"+------------------+-----------+--------------+------------------+-------------------+

+------------------+-----------+--------------+------------------+-------------------+

| mysql-bin.000004 | 903407219 | | | |

+------------------+-----------+--------------+------------------+-------------------+

3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 27317 |

| mysql-bin.000002 | 1034478 |

| mysql-bin.000003 | 531 |

| mysql-bin.000004 | 903407266 |

| mysql-bin.000005 | 120 |

+------------------+-----------+5 rows in set (0.00 sec)

注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4.重置(清空)所有binlog日志

mysql> reset master;

Query OK, 0 rows affected (0.08 sec)

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 120 |

+------------------+-----------+1 row in set (0.00 sec)

清空当前机器的binlog。

5.清理

清除binlog

PURGE {MASTER|BINARY} LOGS TO 'log_name' //log_name不会被清除

PURGE {MASTER|BINARY} LOGS BEFORE 'date' //date不会被清除

2. 主从复制配置

1）旧机器（主A_IP）上创建主从同步帐号：

grant replication slave on *.* to 'rep'@'B_IP' identified by 'heheheh';

2）查看当前旧机器（主A_IP）的binlog状态

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 120 | | | |

+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

3）在新机器（B_IP）上创建主从同步

mysql> change master to master_host='A_IP',

master_user='rep',

master_password='heheheh',

master_port=3306,

master_log_file='mysql-bin.000001',

master_log_pos=120,

master_connect_retry=10;

参数详解：

master_host:主服务器的IP。

master_user：配置主服务器时建立的用户名

master_password：用户密码

master_port：主服务器mysql端口，如果未曾修改，默认即可。

master_log_file：日志文件名称，填写查看master状态时显示的Filemaster_log_pos:日志位置，填写查看master状态时显示的Positionmaster_connect_retry：重连次数

4)启动进程

mysql> start slave;

查看主从同步情况：

主要需要关注Slave_IO_Running: YES； Slave_SQL_Running: YES；Seconds_Behind_Master: 0

mysql> show slave status \G;*************************** 1. row ***************************

Slave_IO_State:

Master_Host: A_IPMaster_User: rep

Master_Port: 3306Connect_Retry: 60Master_Log_File: Tencent64-bin.000164Read_Master_Log_Pos: 107Relay_Log_File: WEBAPP_B_IP_HOST-relay-bin.000006Relay_Log_Pos: 270Relay_Master_Log_File: Tencent64-bin.000164Slave_IO_Running: YES

Slave_SQL_Running: YES

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0Last_Error:

Skip_Counter: 0Exec_Master_Log_Pos: 107Relay_Log_Space: 786Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

主从信息查看的命令：

show slave hosts \G;主机上查看从机信息

show master status\G;主机上查看状态信息

show slave status \G;从机上查看主从状态信息

0x05 存储过程与权限的导入

1.存储过程和函数的导出

由于存储过程和数据库权限信息存储在mysql库中，通过mysqldump普通参数是不会导出的。

导出存储过程： mysqldump 加 -R （或 --routines）参数即可。

-R, --routines Dump stored routines (functions and procedures).

Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible @@GLOBAL.GTID_PURGED' is added to the output. If GTIDs

statements: ALTER TABLE, DROP TABLE, RENAME TABLE,

TRUNCATE TABLE, as consistent snapshot is not isolated

单独导出存储过程等内容：

mysqldump -uroot -n -d -t -R hehehehhe > procedure_name.sql

-- MySQL dump 10.10--

-- Host: localhost Database: hehehehhe

-- ------------------------------------------------------

-- Server version 5.5.3-m3-log

后期也发现，如果在导出时mysqldump指定 --databases 会自动将原DB的创建语句添加进去，但是有的时候我们希望自己指定DB创建语句，比如要指定默认字符集为utf8，而原来的用的是latin1，这个时候我们就不需要加这个参数了。

2.数据库权限的导出与导入

数据库的权限有针对全局的也有针对特定库和表的权限，无法直接导出再导入。

主要通过查出旧机器授权过的帐号和机器IP，在通过show grants语句来获取相应权限。

mysql> SELECT CONCAT("show grants for ",user,"@",host,";") from mysql.user;

+----------------------------------------------+

| CONCAT("show grants for ",user,"@",host,";") |

+----------------------------------------------+

| show grants for hehehehhe@10.136.12.216; || show grants for hehehehhe@10.166.129.173; || show grants for hehehehhe@10.166.129.174; || show grants for adbreader@10.166.129.226; || show grants for hehehehhe@10.166.129.226; || show grants for root@127.0.0.1; || show grants for root@::1; || show grants for root@TENCENT64.site; || show grants for root@localhost; |+----------------------------------------------+9 rows in set (0.00 sec)

然后将show grants语句放到脚本中，运行脚本获取到旧机器上相关授权信息。

最后，在新机器上运行之前获得的授权信息脚本，即可导入相关权限。

注意，这里可能出现报错，数据库不存在的情况下将相关权限导入，所以在导入前需要过滤下。

0x06 数据校验与业务验证

数据校验和业务验证不是我们要讲的重点，但是是数据库迁移不可缺少的一部分。

验证数据的方式有两种：

1.查看数据记录与主从同步状态，可以简单快速判断，但是不是最准确的。

2.通过跑脚本，校验数据库下每个表的checksum值，准确，可能会有些慢。

3.通过Percona Toolkit for MySQL 工具中pt-table-checksum来进行主从校验.

0x07 常见问题分析与解决

1. MyIsam存储引擎锁表导致服务中断，影响到业务。

之前与互娱的兄弟在RTX沟通，备份数据时MyISAM锁表时间过久导致服务出现异常，所以我们一般建议搭建专门的备机进行备份数据，以及尽量用INNODB存储引擎。