![如何高效地转储巨大的 MySQL innodb 数据库?](https://linux22.com/image/599976/%E5%A6%82%E4%BD%95%E9%AB%98%E6%95%88%E5%9C%B0%E8%BD%AC%E5%82%A8%E5%B7%A8%E5%A4%A7%E7%9A%84%20MySQL%20innodb%20%E6%95%B0%E6%8D%AE%E5%BA%93%EF%BC%9F.png)
我有一个 Ubuntu 10.04 生产 MySQL 数据库服务器,其中数据库的总大小为 260 GB,而存储数据库的根分区本身的大小为 300 GB,这基本上意味着 / 的约 96% 已满,并且没有剩余空间来存储转储/备份等。到目前为止,没有其他磁盘连接到服务器。
我的任务是将这个数据库迁移到位于不同数据中心的其他服务器。问题是如何高效地完成这项工作并尽量减少停机时间?
我正在考虑以下问题:
- 请求将额外的驱动器连接到服务器并在该驱动器中进行转储。[编辑:现在不可能。]
- 将转储传输到新服务器,恢复它,并使新服务器成为现有服务器的从属服务器,以保持数据同步
- 当需要迁移时,中断复制,更新从属配置以接受读/写请求,并使旧服务器只读,这样它就不会接受任何写请求,并告诉应用程序开发人员使用数据库的新 IP 地址更新配置。
您对改进这项任务有什么建议,或者有其他更好的方法吗?
答案1
如果您正在考虑迁移到具有完全相同 MySQL 版本的另一个数据库服务器,您可能需要从旧服务器rsync
迁移datadir
到新服务器。
无论 InnoDB 文件布局如何,甚至是否存在 MyISAM 表,这都可以起作用。
- 在 ServerB 上安装与 ServerA 相同版本的 mysql
- 在ServerA上,运行
RESET MASTER;
rsycn 进程前清除所有二进制日志。如果没有启用二进制日志,可以跳过此步骤。 - 在 ServerA 上,
SET GLOBAL innodb_max_dirty_pages_pct = 0;
从 mysql 运行大约 10 分钟(这会从 InnoDB 缓冲池中清除脏页。它还有助于更快地执行 mysql 关闭)如果您的数据库都是 MyISAM,则可以跳过此步骤。 - rsync 服务器 A 的 /var/lib/mysql 到 服务器 B 的 /var/lib/mysql
- 重复步骤 3,直到 rsync 耗时少于 1 分钟
service mysql stop
在服务器A上- 再执行一次 rsync
- 将 ServerA:/etc/my.cnf scp 到 ServerB:/etc/。
service mysql start
在服务器B上service mysql start
在 ServerA 上(可选)
本质上,这样的脚本就是这样的
mysql -u... -p... -e"RESET MASTER;"
mysql -u... -p... -e"SET GLOBAL innodb_max_dirty_pages_pct = 0;"
RSYNCSTOTRY=10
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
X=`echo ${X}+1|bc`
rsync -r * targetserver:/var/lib/mysql/.
sleep 60
done
service mysql stop
rsync -r * targetserver:/var/lib/mysql/.
service mysql start
DBA StackExchange 的一位成员建议我远离FLUSH TABLES WITH READ LOCK;
基于 mysqlperformanceblog.com 上的一些内容
我通读后发现,在中间对 InnoDB 表执行 SELECTFLUSH TABLES WITH READ LOCK;
仍然会以某种方式允许写入。正如评论中指出的那样阿鲁金,LVM 可以FLUSH TABLES WITH READ LOCK
很好地与 InnoDB 配合使用(他的评论为+1)。
- http://www.mysqlperformanceblog.com/2012/03/23/how-flush-tables-with-read-lock-works-with-innodb-tables/
- http://forums.mysql.com/read.php?22,184590,184610
对于所有非 LVM 用户,使用全 MyISAM 数据库即可FLUSH TABLES WITH READ LOCK;
。对于 InnoDB,请坚持--single-tranaction
使用 mysqldumps 中的用法。
答案2
转储和恢复如此大小的数据库需要几个小时。我会,这取决于 mysql 的版本,只要版本号递增并且主要修订号没有跳跃。您应该能够获取 /var/lib/mysql 中的原始数据库文件并将它们放在新服务器上,设置权限并使用 --skip-grant-tables 开关启动服务器。为反映新 IP 地址的用户添加必要的授权,然后正常重启。
我会解决你的数据库的大小问题,因为它太大而效率低下。
答案3
您可以按照以下步骤迁移这个巨大的 InnoDB 数据库。
- 在生产服务器上安装SSHFS并挂载远程服务器的相关分区
- 使用 Percona XtraBackup 获取 InnoDB 数据库的热副本并将其直接保存到 SSHFS 挂载目录
此任务将花费几个小时。为了尽量减少热复制脚本对实时服务器的影响,请使用 renice 将其设置为低优先级
$ renice -n 5 -p <SCRIPT-PID>
- 确保两个服务器运行相同版本的 MySQL 服务器。
- 热复制完成后,您可以在新服务器中恢复它并启动复制过程
在此过程中,您可能会遇到速度缓慢的情况,但绝对不会出现停机。与 mysqldump 相比,Percona XtraBackup 将创建速度更快、资源消耗更少的热副本。这对于大型 InnoDB 数据库来说是理想的选择。
根据使用模式和统计数据,您可以在服务器流量最小时运行此过程。也许在周末这样做是个好主意?以上只是该过程的概述。您可能需要阅读 Percona XtraBackup 和 SSHFS 文档。
答案4
您可以将数据库直接转储到远程服务器......
$ mysqldump | ssh user@server 'cat - > dumpfile.sql.gz'
... SQL 应该压缩得很好,所以你应该用其中一个选项更快地完成这项工作,尽管这也取决于你在盒子里的 RAM 数量......
$ mysqldump | ssh -C user@server 'cat - > dumpfile.sql.gz'
$ mysqldump | gzip -c | ssh user@server 'cat - > dumpfile.sql.gz'