Comments on How To Back Up MySQL Databases Without Interrupting MySQL

How To Back Up MySQL Databases Without Interrupting MySQL This article describes how you can back up MySQL databases without interrupting the MySQL service. Normally, when you want to create a MySQL backup, you either have to stop MySQL or issue a read lock on your MySQL tables in order to get a correct backup; if you don't do it this way, you can end up with an inconsistent backup. To get consistent backups without interrupting MySQL, I use a little trick: I replicate my MySQL database to a second MySQL server, and on the second MySQL server I use a cron job that creates regular backups of the replicated database.

10 Comment(s)

Add comment

Please register in our forum first to comment.

Comments

By:
Reply  

There's also a script mysqlhotcopy that is part of the MySQL distribution (at least on Debian) which can backup a live server with cp or scp.

http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html.

By:
Reply  

Here's a script that can transfer all mysql databases to another server.

By: Anonymous
Reply  

works great with mysql 5.0. Thanks.

By: Rudolf Pietersma
Reply  

I get this error when running the script:

root@web01://usr/local/ispconfig/interface/web/themes/default/images# /usr/local/sbin/mysqlbackup.sh
Slave stopped
/usr/bin/mysqladmin: Error starting slave: The server is not configured as slave; fix in config file or with CHANGE MASTER TO

By: Anonymous
Reply  

when i start the task this problem is occured

/usr/local/sbin/Mysqlbackup.sh: line 4: /home/Mysqlbackup/backup-/bin/date +%Y%m%d-%H.sql: No such file or directory

how can i fix this problem?

thnx

By: Anonymous
Reply  

It appears that you did not use back-tics when specifying datum:

datum=`/bin/date +%Y%m%d-%H`

By: Vlatko Šurlan
Reply  

The script that I am sharing backs up the files along with MySQL data but does it live and directly into an ssh connection so it might be of interest to many here: Live files and MySQL data backup.

By: Anonymous
Reply  

Hi, If your tables are in innodb you can use the --single-transaction option. You can do your dump with your slave active with this. --single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. Option automatically turns off --lock-tables.I use kls backup for backing up my system.

By: User
Reply  

I added this line after I started the slave back up. Saves space in the future if you have several DBs or they are large.

/bin/gzip /home/sqlbackup/backup-${datum}.sql

By: [email protected]
Reply  

Hi Falko.

I need small help on how to take backups for master/slave replication databases.please let me know  using manaul Mysqldump .please guide me as my client wants to apply patch on the application end.just want to be on the safer side to restore the databases.we are having full VM backups .Do you want to us to take VMbackup before the process of both master/slave so that we can restore.

 

thanks

Goutham