shamoon Posted December 5, 2007 Share Posted December 5, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/80335-backing-up-a-database-quickly/ Share on other sites More sharing options...
fenway Posted December 5, 2007 Share Posted December 5, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/80335-backing-up-a-database-quickly/#findComment-407263 Share on other sites More sharing options...
shamoon Posted December 6, 2007 Author Share Posted December 6, 2007 Wont those scripts lock the DB? Quote Link to comment https://forums.phpfreaks.com/topic/80335-backing-up-a-database-quickly/#findComment-407667 Share on other sites More sharing options...
fenway Posted December 6, 2007 Share Posted December 6, 2007 Not exactly... you should re-read the relevant refman pages. Also, mysql toolkit (now ma'atkit) has some very useful backup tools. Quote Link to comment https://forums.phpfreaks.com/topic/80335-backing-up-a-database-quickly/#findComment-407966 Share on other sites More sharing options...
shamoon Posted December 6, 2007 Author Share Posted December 6, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/80335-backing-up-a-database-quickly/#findComment-408225 Share on other sites More sharing options...
fenway Posted December 6, 2007 Share Posted December 6, 2007 It should be a WRITE LOCK, so that you get consistent reads... but I haven't explored it in details. Quote Link to comment https://forums.phpfreaks.com/topic/80335-backing-up-a-database-quickly/#findComment-408312 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.