mysqldump vs select into outfile

Question

I use select * into outfile option in mysql to backup the data into text files in tab separated format. i call this statement against each table.

And I use load data infile to import data into mysql for each table.

I have not yet done any lock or disable keys while i perform this operation

Now I face some issues:

1. While it is taking backup the other, updates and selects are getting slow.
2. It takes too much time to import data for huge tables.

How can I improve the method to solve the above issues?

Is mysqldump an option? I see that it uses insert statements, so before I try it, I wanted to request advice.

Does using locks and disable keys before each "load data" improve speed in import?

Answer 1

Using mysqldump is important if you want your data backup to be consistent. That is, the data dumped from all tables represents the same instant in time.

If you dump tables one by one, they are not in sync, so you could have data for one table that references rows in another table that aren't included in the second table's backup. When you restore, it won't be pretty.

For performance, I'm using:

mysqldump --single-transaction --tab mydatabase

This dumps for each table, one .sql file for table definition, and one .txt file for data.

Then when I import, I run the .sql files to define tables:

mysqladmin create mydatabase
cat *.sql | mysql mydatabase

Then I import all the data files:

mysqlimport --local --use-threads=4  mydatabase  *.txt

In general, running mysqlimport is faster than running the insert statements output by default by mysqldump. And running mysqlimport with multiple threads should be faster too, as long as you have the CPU resources to spare.

Using locks when you restore does not help performance.

The disable keys is intended to defer index creation until after the data is fully loaded and keys are re-enabled, but this helps only for non-unique indexes in MyISAM tables. But you shouldn't use MyISAM tables.

For more information, read:

• https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
• https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html

Answer 2

If you have a lot of databases/tables, it will definitely be much easier for you to use mysqldump, since you only need to run it once per database (or even once for all databases, if you do a full backup of your system). Also, it has the advantage that it also backs up your table structure (something you cannot do using only select *).

The speed is probably similar, but it would be best to test both and see which one works best in your case.

Someone here tested the options, and mysqldump proved to be faster in his case. But again, YMMV.

If you're concerned about speed, also take a look at the mysqldump/mysqlimport combination. As mentioned here, it is faster than mysqldump alone.

As for locks and disable keys, I am not sure, so I will let someone else answer that part :)