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

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 :)