3

I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack.

Or mysqldump will work without any problem?

Edit I am not looking for a very big file containing lot of SQL statements. Now, do I have more options? Like raw data export or mysqlhotcopy as I am looking for a quicker way because backing up MyISAM would acquire table locking?

And what are the limits of mysqldump and when it should be avoided?

Gary Lindahl
  • 443
  • 3
  • 12

2 Answers2

2

mysqldump should be just fine. Since you have only 3GB of data, I expect the mysqldump to be decently fast (1-2 min as a single output). However, since your database is comprised of 3 MyISAM tables expect all three to be locked during the backup. Please keep mind that since tables are locked one at a time, you will not have point-in-time consistency across all tables in the database.

I have some scripts I wrote and still use to perform

  • parallel backups of separate databases
  • parallel backups of separate tables

If you are concerned with consistent data, you may need to implement something from Option 4 of the link I provided which executes "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);" in a separate session to lock all writes to all tables, perform the mysqldump(s), and then kill that session holding the tables locks.

If you are afraid to try executing this, you should look into using MySQL Replication. A slave server with the same data can be used to perform mysqldumps without imposing anything disk I/O or overall server load on the master database. You basically do these things on the DB Slave:

  • STOP SLAVE;
  • mysqldump
  • START SLAVE;

Give it a Try !!!

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
1

Mysqldump would work like a charm and you'll not even notice any performance degrade because 3GB size can be handled easily and if you choose the first option of @Rolando script link then size would be greatly reduced very like that it would be a 500-600MB file.

There will always be 'lot' of sql statements in standard backup methods because that way when restoring you'll be telling MySQL to insert those data so it will be through SQL statements.

Now, other ways are to copy your data table or using mysqlhotcopy and it works good for MyISAM tables. But I would suggest easiest method that is using mysqldump because you don't have to worry about table level locking as it would be not noticed. This link maybe helpful to you Creating a Data Snapshot Using Raw Data Files and How to Restore MySQL Databases Without a mysqldump Backup.

Rick James
  • 1,271
  • 3
  • 15
  • 19