我有一个巨大的32 GB SQL转储,需要导入MySQL。我以前不必导入如此庞大的SQL转储。我照常做:

mysql -uroot dbname < dbname.sql


花了太长时间。一张桌子大约有3亿行,大约3个小时便达到了150万行。因此,似乎整个过程将花费600个小时(即24天),并且是不切实际的。所以我的问题是,有没有一种更快的方法?

其他信息/查找表


表都是InnoDB,并且没有定义外键。但是,索引很多。
我无权访问原始服务器和数据库,因此无法进行新备份或进行“热”复制等。
按此处建议的那样设置innodb_flush_log_at_trx_commit = 2
(从MySQL Workbench)导入期间的服务器统计信息:https://imgflip.com/gif/ed0c8。
MySQL版本是5.6.20社区。
innodb_buffer_pool_size = 16M,innodb_log_buffer_size = 8M。我需要增加这些吗?


评论

您可以向服务器添加更快的组件,即更多的RAM和SSD存储吗?

@Bert服务器具有8 GB的RAM,其中大部分未使用。也无法添加更多存储空间。那将有什么帮助?真的写操作这么慢吗?

有什么瓶颈?是否固定了CPU核心?

@ChrisS不,CPU使用率为3%到4%。我不确定瓶颈是什么。我在想那是索引。如何找到/确认瓶颈?

#1 楼

Percona的Vadim Tkachenko对InnoDB进行了精美的图形表示



您肯定需要更改以下内容

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0


为什么要进行这些设置?



innodb_buffer_pool_size将缓存频繁读取的数据

innodb_log_buffer_size:较大的缓冲区减少了对事务日志的写入I / O。

innodb_log_file_size:更大的日志文件减少了检查点并写入I / O
innodb_write_io_threads:对.ibd文件的服务写入操作。根据Configuring the Number of Background InnoDB I/O Threads上的MySQL文档,每个线程最多可以处理256个未决的I / O请求。对于Percona Server,MySQL的默认值为4、8。最大值为64。

innodb_flush_log_at_trx_commit


发生崩溃时,0和2都可能丢失一秒钟的数据。
折衷是0和2都提高了写入性能。
我选择0而不是2,因为0每秒一次将InnoDB日志缓冲区刷新到事务日志(ib_logfile0,ib_logfile1),无论提交与否。设置2仅在提交时刷新InnoDB日志缓冲区。前Percona讲师@jynus提到设置0还有其他好处。



像这样重新启动mysql

service mysql restart --innodb-doublewrite=0


这将禁用InnoDB双重写入缓冲区

导入您的数据。完成后,正常重启mysql

service mysql restart


这将重新启用InnoDB双重写入缓冲区

尝试一下! br />侧面说明:您应该升级到5.6.21,以获取最新的安全补丁。

评论


我为此制作了一个Linux bash脚本,降低了一些值以使vagrant在内存较低的gist中工作。github.com/OZZlE/57d550c3cc1c1ff17481e465e4f6d674

– OZZIE
18/12/14在9:12

没有像innodb_write_io_threads这样的参数名称。我要测试其他建议的更改

–MonsterMMORPG
2月14日8:38

@MonsterMMORPG innodb_write_io_threads从MySQL 5.1.38开始出现。如果您使用的MySQL 5.0最高版本为5.1.37,这就是为什么您看不到它的原因。参见dev.mysql.com/doc/refman/8.0/en/…

– RolandoMySQLDBA
2月14日15:08

@MonsterMMORPG出于完整性考虑,Percona在Percona Server 5.0的InnoDB / XtraDB中具有该选项,而MySQL通过InnoDB插件在5.1.38中可用。

– RolandoMySQLDBA
6月20日14:27

真的有人尝试过吗?更改这些标志后,平均性能提高了多少?

– Nebulastic
10月27日11:39

#2 楼

您是否真的需要还原整个数据库?如果没有,我的2c:

您可以提取特定的表以对“块”进行还原。这样的事情:

zcat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql


我做了一次,花了大约10分钟来提取我需要的表-我的完整还原花费了13〜14个小时,并拥有35GB的存储空间(已压缩)转储。

带有/pattern/,/pattern/p参数的-n在“模式之间”切片-包括它们。

无论如何,要还原35GB,我使用了AWS EC2机器(c3.8xlarge),通过yum(Centos)安装了Percona,并刚刚在my.cnf上添加/更改了以下几行: ,但适用于我的设置。

评论


这是2美分。 +1 !!!

– RolandoMySQLDBA
19年11月28日在18:16

没有参数名称为wait_timeout

–MonsterMMORPG
2月14日8:39

#3 楼

导入数据库的最快方法是将MyISAM文件(.frm,.MYD,.MYI)直接复制到/ var / lib / mysql /“数据库名称”。

可以尝试:mysql > use database_name; \. /path/to/file.sql

这是导入数据的另一种方法。

评论


这不称为“导入”,并且在MySQL版本之间也不安全。在选择所有其他选项之前,请不要执行此操作。

–tanaydin
8月4日9:15

#4 楼

一种有助于加快导入速度的方法是在导入时锁定表。在mysqldump上使用--add-locks选项。
mysqldump --add-drop-table --add-locks --database db > db.sql

,或者您可以使用--opt打开一些有用的参数,这将为转储打开一堆有用的东西。
mysqldump --opt --database db > db.sql

如果服务器上有另一台存储设备,请使用该设备-从一台设备复制到另一台设备是加快传输速度的一种方法。
您也可以过滤掉--ignore-不需要的表桌子

#5 楼

可以做到:

mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" -u root -p < Backup_Database.mysql


评论


我建议解释一下,为什么它使导入速度更快以及如何进行。

–peterh-恢复莫妮卡
19年11月28日在17:19



..以及这些设置可能导致什么问题。

–迈克尔·格林(Michael Green)
19年11月29日在0:39

这些已经包含在默认的mysqldump导出中。

– hackel
10月8日20:51