☰

如何高效地转储巨大的 MySQL innodb 数据库？

tag-icon • tag-icon innodb mysql
如何高效地转储巨大的 MySQL innodb 数据库？

我有一个 Ubuntu 10.04 生产 MySQL 数据库服务器，其中数据库的总大小为 260 GB，而存储数据库的根分区本身的大小为 300 GB，这基本上意味着 / 的约 96％ 已满，并且没有剩余空间来存储转储/备份等。到目前为止，没有其他磁盘连接到服务器。

我的任务是将这个数据库迁移到位于不同数据中心的其他服务器。问题是如何高效地完成这项工作并尽量减少停机时间？

我正在考虑以下问题：

  • 请求将额外的驱动器连接到服务器并在该驱动器中进行转储。[编辑：现在不可能。]
  • 将转储传输到新服务器，恢复它，并使新服务器成为现有服务器的从属服务器，以保持数据同步
  • 当需要迁移时，中断复制，更新从属配置以接受读/写请求，并使旧服务器只读，这样它就不会接受任何写请求，并告诉应用程序开发人员使用数据库的新 IP 地址更新配置。

您对改进这项任务有什么建议，或者有其他更好的方法吗？

答案1

如果您正在考虑迁移到具有完全相同 MySQL 版本的另一个数据库服务器，您可能需要从旧服务器rsync迁移datadir到新服务器。

无论 InnoDB 文件布局如何，甚至是否存在 MyISAM 表，这都可以起作用。

  1. 在 ServerB 上安装与 ServerA 相同版本的 mysql
  2. 在ServerA上，运行RESET MASTER;rsycn 进程前清除所有二进制日志。如果没有启用二进制日志，可以跳过此步骤。
  3. 在 ServerA 上，SET GLOBAL innodb_max_dirty_pages_pct = 0;从 mysql 运行大约 10 分钟（这会从 InnoDB 缓冲池中清除脏页。它还有助于更快地执行 mysql 关闭）如果您的数据库都是 MyISAM，则可以跳过此步骤。
  4. rsync 服务器 A 的 /var/lib/mysql 到 服务器 B 的 /var/lib/mysql
  5. 重复步骤 3，直到 rsync 耗时少于 1 分钟
  6. service mysql stop在服务器A上
  7. 再执行一次 rsync
  8. 将 ServerA:/etc/my.cnf scp 到 ServerB:/etc/。
  9. service mysql start在服务器B上
  10. 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）。

对于所有非 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'

相关内容