Mysql – Strategy for setting up a 60gb MySQL database from a dump

MySQLmysqldump

I have been trying to set up a 60gb MySQL database from a dump. I loaded it into MySQL Workbench on my standard-issue dual-core 2.4 Ghz, 4gb RAM, 2011 Macbook Pro. After roughly 24h, it had only been able to load a few percent. During other attempts MySQL Workbench crashed completely. Doing the same thing on the command line generates roughly the same results.

What is a viable strategy for loading a 60gb MySQL database? Do I need to buy a very powerful computer? How powerful? Is there a way of loading it onto an Amazon Web Services instance that is feasible?

Best Answer

To speed up load time of InnoDB you can tune some variables.

innodb_buffer_pool_size. Kind of the most important one. Allocate as much as your system allows, but make sure MySQL doesn't use swap. 4GB RAM is desperately little, try buffer pool around 2G. If you can install more memory that will be more helpful than anything below.
innodb_log_file_size. The larger REDO log the less random IO InnoDB does. It doesn't make to make it larger than the buffer pool. So, 512M - 1G should be OK.
Reduce InnoDB durability with innodb_flush_log_at_trx_commit=0
A bit risky, but you can also disable innodb_doublewrite
Make sure you disable all unnecessary logs (binlog, slow log, general log).

DISCLAIMER: All recommendations for the load process only. For normal operation configuration must be different.

Related Solutions

Mysql – How to open a MySQL Database Dump

Although there are a number of tools out there, I prefer the command line for tasks such as this. Better performance, and just simpler.

Make sure you're logged into the mysql client:

mysql -u <username> -p

The first step is to create a database in mysql that you want the data in:

CREATE DATABASE <database name>;

Then you need to open--or use--that new database, which is empty:

USE <database name>;

All that's left is to do the import:

\. /path/to/file (or if in Windows, \. C:\PATH\TO\FILE)

If all goes well, you'll see a bunch of output about "Query successful, N rows affected" and so on.

MySQL Dump – Taking MySQL Dump from Another Machine

In General if you have the credentials for accessing MySql from Machine B.

You should have MySQL user name , host name and Password.Then You can write the Command for taking backup

On Machine B Write

mysqldump -h Your_host_name -u user_name -p password --all-databases > backup.sql

If You Face issues there may be some network issue try some things as

Use ping with ip address to check that DNS isn't broken. eg. ping Machine A
Use mysql client to connect from B to A. eg mysql -u user -pPASS --host=Host_Name --port=3306 (substitute whatever port you are connecting to the master on)

Best Answer

Related Solutions

Mysql – How to open a MySQL Database Dump

MySQL Dump – Taking MySQL Dump from Another Machine

Related Question