1

I don't normally work with MySQL but with MS-SQL and am having issues restoring a dump backup of a 9 GB database. I converted it to MS-SQL and it takes a grand total of 4 minutes to restore but the MySQL DB takes over an hour on the same server. The MySQL database is using InnoDB but is there an alternative to speeding up the restores? Both databases are on the same machine, Windows 2008R2 in a VM with a dymanic SANs.

Correction - it takes MS-SQL 1 minute to restore, 1 hour to restore the same database in MySQL

EDIT: mysql.ini (with commented lines removed):

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:\Program Files\MySQL\MySQL Server 5.5\"
datadir="C:\ProgramData\MySQL\MySQL Server 5.5\data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="CM999-SV510.log"
slow-query-log=0
slow_query_log_file="CM999-SV510-slow.log"
long_query_time=10
log-error="CM999-SV510.err"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=22M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=43M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=124M
innodb_log_file_size=63M
innodb_thread_concurrency=9
5
  • Please post your my.ini file for the MySQL Instance Oct 5, 2012 at 1:52
  • How did you convert to SQL Server? Oct 5, 2012 at 10:17
  • I created a linked server and copied the data in. Then I scripted out the indexes in MS-SQL Oct 5, 2012 at 14:41
  • 1
    @user1207758 That way you try to compare two completely different systems. This will not yield too much useful information, even less about why the restore takes so long on MySQL. And a question: what options did you pass to mysqldump? Oct 5, 2012 at 19:28
  • How do you backup mysql ? Do you use mysqldump? Are you running mysqldump of all database to a single file ? Jun 3, 2013 at 18:59

1 Answer 1

-2

Try adding this setting to your configuration:

innodb_flush_log_at_trx_commit=0
1
  • 1
    Why do you think this is a good recommendation? What does it do that it improves restores?
    – Marian
    Mar 5, 2013 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.