MySQL数据迁移

数据库的导入导出

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

$HOSTNAME_0 $DATABASE_0
$HOSTNAME_1 $DATABASE_1
$HOSTNAME_2 $DATABASE_2
$HOSTNAME_2 $DATABASE_3
$HOSTNAME_2 $DATABASE_4

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

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 主机如下:

$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 出来的数据导入到这个数据库中,操作如下:

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

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

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

主从数据库的搭建

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

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

2、然后查询 master 的状态:

show master status;

3、配置 slave 数据库:

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、检查从数据库的同步状态

mysql>show slave status;

要确保

Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

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

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

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


#!/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 这一天的数据到外部文件中,操作如下所示

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 这一天的数据,操作如下所示

load 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 数据库的一致性。

comments powered by Disqus