Backing up a database quickly

[shamoon]

Hello all,

I'm using automysqlbackup to back up my entire database nightly.  It is being backed up from a different machine than on which the database resides.  However, during the time of backup, the server comes to a snore.  My slow log has stuff like:

# Query_time: 652  Lock_time: 0  Rows_sent: 4506217  Rows_examined: 4506217
SELECT /*!40001 SQL_NO_CACHE */ * FROM `books_to_categories`;

 

Is there any better way to do regular backups?  I've been told replication is the way to go and to run backups on the slave, but the problem is that I'm using a MyISAM storage engine and using "FullText" search capabilities.

 

Does anyone have a solution to this problem?

 

Thanks,

Shamoon

[fenway]

Depends what you mean by "backup" -- if it's just a MyISAM table, you can take the .frm, .myi and .myd if you FLUSH then LOCK TABLES.  Not the most pretty, but it works.  Then there are the mysqldump and mysqlhotcopy scripts.

[shamoon]

Wont those scripts lock the DB?

[fenway]

Not exactly... you should re-read the relevant refman pages.  Also, mysql toolkit (now ma'atkit) has some very useful backup tools.

[shamoon]

Seems that the ma'akit locks the table when doing a parallel dump.  My understanding is that if a table is locked, other people can't read from it?

[fenway]

It should be a WRITE LOCK, so that you get consistent reads... but I haven't explored it in details.