CODEWITHSUNDEEP
mysqldatabase
Abhishek Jaiswal
Abhishek Jaiswal

Reputation: 2684

how to take mysql backup through mysqldump command with affecting production server?

i have written a shell script take daily backups but whenever it starts mysql it locks the database and site goes down.

i want not to affect live server as site can acces over different timezone so cant take back ups at night

Upvotes: 0

Views: 438

Answers (4)

himanshu
himanshu

Reputation: 1

You can use :

mysqldump -u [user] -p[password] [dbname] > dumpfile.sql

Upvotes: 0

Abhishek Jaiswal
Abhishek Jaiswal

Reputation: 2684

solves the problem

mysqldump -u[user] -p[password] dbname --skip-lock-tables | gzip > soohoo_backup.sql.gz

Upvotes: 0

Raad
Raad

Reputation: 4658

One approach you can take to avoid this problem is to set up MySQL replication and use the slave database to do the backups from.

The pros are you get complete replication of your database and can effectively do hot backups, regardless of the type of database. However, you do need another db server, and effectively double the storage capacity (as you are maintaining another copy of the db)

Not everyone's cup of tea, but an option nonetheless.

Upvotes: 1

sjdaws
sjdaws

Reputation: 3526

If you're using MyISAM you can inform MySQL you don't want to lock the tables by using --lock-tables=false. If you're using InnoDB you can use --single-transaction to avoid locks.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Upvotes: 0

Related Questions