Tech Blog

Article

by Leif

This article describes how to take backup of MySQL databases without interrupting the MySQL service on your account dedicated hosting. Usually when you want to create a backup of MySQL, you have to stop or solve a problem MySQL MySQL table lock to create a valid backup, and if you do not like, you can get a backup correctly. For a good backup copy without interrupting MySQL, I use the following trick: I replicate my MySQL database on the second server, and the second MySQL server I use a cron job to create a regular backup of a replicated database > This document is provided without warranty. I want to say that this is not the only way to create such a system. There are many other ways to achieve this goal, but I chose this topic. I do not guarantee that it will work for you!

Opening Remarks

To follow this decision, you need a second server, MySQL (the slave), and you must install MySQL replication to your MySQL server first (a that you want to use copies of master) of knowledge. Installing MySQL replication is beyond the scope of this document.

All options I describe here, must be made on the MySQL slave server!

I tested this on a Debian system and it should work on other distributions, but it is quite possible that some paths will be different (in the / usr / local / sbin / mysqlbackup.sh) .

Implementation

database automated backup replicated to the slave server

Once you’ve created a MySQL replication work from master to slave, I guess you want to have automatic backups of the database leads / home / SQLBackup. First, you must create this directory:

mkdir / home / SQLBackup

Next we create a shell script / usr / local / sbin / mysqlbackup.sh which stops the knowledge base, empty SQL database MySQL set in / home / SQLBackup (SQL dump file name should look like this:. backup-20120226-18 SQL, which discharge obtained on February 26, 2012 18.00 pm) then restarts knowledge OBD (OBD makeup done everything that happened OBD background so no data was lost) and removes all SQL backups in / home / SQLBackup are more than two days:

vi / usr / local / sbin / mysqlbackup.sh

# ——– ——————— Mysqlbackup.sh- ——————-

#! / Bin / sh

given

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

/ usr / bin / mysqladmin – user = root – password = yourrootsqlpassword stop-slave

/ usr / bin / mysqldump – user = root – password = yourrootsqlpassword – lock-all-tables-all-databases> / home/sqlbackup/backup- $ (given). Sql

/ usr / bin / mysqladmin – user = root – password = yourrootsqlpassword start-slave

for the file in “$ (/ usr / bin / find / home / SQLBackup type f-mtime +2) “do / bin / rm-f $ filedone

output

0

# ————————– —————————————-

Now we need to make the script executable:

chmod 755 / usr / local / sbin / mysqlbackup.sh

crontab-e

0 * / 3 *** / usr / local / sbin / mysqlbackup.sh &> / dev / null

Although Sure, you can freely change the cron job to run with such frequency that you need.

About the Author

I am a webmaster and a writer who writes articles on various topics that include internet marketing, Core Banking, Hosting, Cloud Computing, Data Center and much more.

Use and distribution of this article is subject to our guidelines Editeurpar which the original author’s information and copyright must be included. Div