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。我需要增加这些吗?
#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
评论
您可以向服务器添加更快的组件,即更多的RAM和SSD存储吗?@Bert服务器具有8 GB的RAM,其中大部分未使用。也无法添加更多存储空间。那将有什么帮助?真的写操作这么慢吗?
有什么瓶颈?是否固定了CPU核心?
@ChrisS不,CPU使用率为3%到4%。我不确定瓶颈是什么。我在想那是索引。如何找到/确认瓶颈?