MySQL数据迁移

发表于 | | 阅读次数: |

数据库的导入导出

我们此次准备迁移的共有五个数据库，分布在三台机器上：

1
2
3
4
5
$HOSTNAME_0 $DATABASE_0
$HOSTNAME_1 $DATABASE_1
$HOSTNAME_2 $DATABASE_2
$HOSTNAME_2 $DATABASE_3
$HOSTNAME_2 $DATABASE_4

首先我们需要将这些数据库通过 mysqldump 的方式导出，其用法如下：

1
mysqldump –h$HOSTNAME –u$USER –p$PASSWD $DATABASE > $DATABASE.sql

通过这种方式就可以将 $DATABASE 的数据以 SQL 语句的形式保存在文件中。有一些非常有用的选项，可以帮助我们更好的操作数据库：

  • -t 表示仅导出数据库中的数据，不包括表结构
  • -d 表示仅导出数据库的表结构，不包含数据

问题：mysqldump 备份数据出现段错误

可能原因:是待备份的数据库中包含视图

解决办法：备份的时候跳过这些视图，–skip-tables=$TABLE_VIEW

搭建MySQL主从备份

准备工作

以 $DATABASE_0 数据库为例，我们目标是以 $HOSTNAME_0 为 master，搭建两台 slave 以同步 master 数据库的数据，从而实现主从备份。其中两台 slave 主机如下：

1
2
$HOSTNAME_SLAVE_0
$HOSTNAME_SLAVE_1

为了保证主从同步正常进行，我们需要确保 slave 主机的 MySQL 环境要与 master 主机的 MySQL 环境一模一样，并且要在配置文件 my.cnf 中添加log-bin=mysql-bin选项。由于我们机器上的 MySQL 都是通过公司内部的 jumbo 安装的，所以确保环境配置相同的问题其实不用考虑的，唯一需要确保的是 slave 的 my.cnf 中的 server-id 要与 master 的 my.cnf 中的 server-id不相同，否则同步出错

数据导入

首先我们在 slave 数据库中建立 $DATABASE_\0 这个数据库，然后通过之前 mysqldump 出来的数据导入到这个数据库中，操作如下：

1
mysql –h$HOSTNAME –u$USER –p$PASSWD $DATABASE < $DATABASE.sql

为了更好的控制数据库的导入，有一些选项非常有用：

  • -f ：插入过程中发生错误，仍会持续数据导入过程。比如导入$DATABASE.sql的过程中，如果发生主键冲突，我们仍能进行后续数据插入的过程，我们通过这种方式实现数据的覆盖，以及确保数据的一致性问题

主从数据库的搭建

1、在 master数据库上建立帐户并授权slave:

1
GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'root';

2、然后查询 master 的状态：

1
show master status;

3、配置 slave 数据库：

1
2
mysql>change master to master_host=$MASTER_IP,master_user='mysync',master_password='root', master_log_file='mysql-bin.000004',master_log_pos=308;
mysql>start slave;

4、检查从数据库的同步状态

1
mysql>show slave status;

要确保

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两项必须为Yes状态，然后可以在 master 数据库上新增加一个数据库，在 slave 数据库上看是否能同步，如果同步了那么说明主从数据库搭建成功了。

检查迁移后的数据库是否一致

由于我们进行的是热备份，也就是在备份的过程中 master 仍然处于工作状态，所以在备份的过程中可能会发生主从数据库数据的不一致的情况。在这里我们通过比较主从数据库的每个表的记录数，来检查主从数据库是否达到一致。其脚本如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/bin/bash
USER=root
PASSWORD=root

HOSTNAME_1=$HOSTNAME_0
HOSTNAME_2=$HOSTNAME_SLAVE_0
HOSTNAME_3=$HOSTNAME_SLAVE_0
DATABASE=$DATABASE_0

if [ $# -ne 1 ]; then
        echo "$0: tables.list"
        exit
fi

function query_count() {
        if [ $# -ne 2 ]; then
                echo "Two Arg: Hostname and Table Name"
                return
        fi  

        hostname=$1
        tablename=$2

        mysql -h$hostname -u$USER -p$PASSWORD $DATABASE \
                -e"SELECT count(*) FROM $tablename" \
                | grep ^[0-9]*$
}

for table in $(cat $1)
do
        sql="SELECT count(*) FROM $table;"
        count1=$(query_count $HOSTNAME_1 $table)
        count2=$(query_count $HOSTNAME_2 $table)
        count3=$(query_count $HOSTNAME_3 $table)
        echo "count1: $count1  count2:$count2 count3: $count3"

        if [ "$count1" != "$count2" ] && [ "$count1" != "$count3" ]; then
                echo "$table is different"
        fi
done

这个脚本可以检查两边数据库中记录项的不一致的情况，并打印哪些表出现了不一致的情况。
如何确保迁移后数据的一致性
如果发生了不一致，我们就需要弥补这些不一致的情况。具体做法如下：

1、根据数据一致性检查脚本判断哪些表名出现了不一致的情况，比如$TABLE出现了不一致的情况

2、登到 master 数据库，导出 $TABLE 这一天的数据到外部文件中，操作如下所示：

1
2
select * from $TABLE where id>8709218
 into outfile '/home/work/liuchang/$TABLE.sql' fields terminated by ',' lines terminated by '\n';

因为我们数据库表中的id字段是递增的，通过限制条件 id > 8709218 选出了今天的数据，并把这些数据导入到文件中。

3、登陆 slave 数据库，从外部文件中导入 $TABLE 这一天的数据，操作如下所示：

1
oad data local infile "/home/work/liuchang/SQL/$TABLE.sql" into table $TABLE fields terminated by ',' lines terminated by '\n';

4、再检查两边的数据库中的一致性情况

最后

对于规模很大的数据库的备份，不建议使用 mysqldump 的形式进行备份，因为通过插入 SQL 语句恢复数据库的过程会十分缓慢。可以通过暂停 master 数据库，复制数据文件目录到 slave 数据库的数据目录下面，然后启动主从备份过程，这样的做法比较快速。

不过在备份 nlpc_stat（mysqldump了大约34G的SQL文件）的过程中，我们是通过先插入表的结构，然后开始了主从同步。之后再使用 mysql 导入之前mysqldump备份的文件和使用 load file 导入今天刚插入的记录，让它慢慢恢复到与 master 数据库的一致性。