- \n
- Does the backup require shutting down MySQL? If not, what is the impact on the running server? Blocking, I\/O load, cache pollution, etc?<\/li>\n
- What technique is used for the backup? Is it mysqldump or a custom product that does something similar? Is it a filesystem copy?<\/li>\n
- Does the backup system understand that you cannot back up InnoDB by simply copying its files<\/em>?<\/li>\n
- Does the backup use FLUSH TABLES, LOCK TABLES, or FLUSH TABLES WITH READ LOCK? These all interrupt processing.<\/li>\n
- What other effects are there on MySQL? I\u2019ve seen systems that do a RESET MASTER, which immediately breaks replication. Are there any FLUSH commands at all, like FLUSH LOGS?<\/li>\n
- How does the system guarantee that you can perform point-in-time recovery?<\/li>\n
- How does the system guarantee consistency with the binary log, InnoDB logs, and replication?<\/li>\n
- Can you use the system to set up new MySQL replication slaves? How?<\/li>\n
- Does the system verify that the backup is restorable, e.g. does it run InnoDB recovery before declaring success?<\/li>\n
- Does anyone stand behind it with support, and guarantee working, recoverable backups? How strong is the legal guarantee of this and how much insurance do they have?<\/li>\n<\/ol>\n
<\/p>\n
BACKUP PROGRAMS<\/h3>\n
There are a few MySQL backup products out there as well. I have used the first two on this list.<\/p>\n
- \n
- AutoMySQLBackup<\/a> script (handy for making a rotating incremental backup of your MySQL databases).<\/li>\n
- Percona XtraBackup<\/a> (nice way to ensure InnoDB and MyISAM tables are backed up properly, also does it incrementally)<\/li>\n
- Zmanda<\/a> (seems to be similar to Percona’s set up)<\/li>\n<\/ul>\n
There’s probably a gazillion more out there. Google’s your friend in finding things you need.<\/p>\n
HOW TO DO IT<\/h3>\n
![\"\"](\"http:\/\/mossiso.com\/wp-content\/uploads\/2012\/07\/master-and-slave-300x125.png\" "\\"master-and-slave\\"")
<\/a>How to get a copy of the master to the slave?<\/figcaption><\/figure>\n There are several options. You could use a script above, or create a slave of the database (basically an exact copy of the production MySQL server – all changes that occur in the master are sent to the slave), or some combination. I’ll use a combination. I’ll replicate the production server onto the backup server, then run the incremental backups from there. This first part will walk through the process of setting up MySQL replication.<\/p>\n
To give proper credit, here are several other how to’s I found helpful.<\/p>\n
- \n
- http:\/\/www.badllama.com\/content\/mysql-backups-using-lvm-snapshots<\/a><\/li>\n
- http:\/\/www.mysqlperformanceblog.com\/2006\/08\/21\/using-lvm-for-mysql-backup-and-replication-setup\/<\/a><\/li>\n
- http:\/\/www.softwareprojects.com\/resources\/programming\/t-how-to-move-copy-a-live-mysql-database-and-what-1257.html<\/a><\/li>\n
- http:\/\/www.clusterdb.com\/mysql-cluster\/get-mysql-replication-up-and-running-in-5-minutes\/<\/a><\/li>\n<\/ul>\n
On the master server<\/h3>\n
Step 1.<\/strong> Edit the my.cnf file to include at least the following (if needed) lines. Note: you will have to restart MySQL for these changes to take affect.<\/p>\n
\n\r\n[mysqld]\r\nserver_id=1\r\ninnodb_flush_log_at_trx_commit=1\r\nlog_bin=mysql-bin.log\r\nsync_binlog=1\r\n<\/pre>\n<\/div>\n
Step 2.<\/strong> Make a MySQL user for the slave to use.<\/p>\n
In a MySQL session on the terminal, type in the command:<\/p>\nGRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'localhost' IDENTIFIED BY 'passwordhere';<\/pre>\n<\/div>\n
Step 3.<\/strong> Open a terminal session and log in to a MySQL prompt. Type the following command and hit enter.<\/p>\n
\nFLUSH TABLES WITH READ LOCK<\/pre>\n
Note: This will lock your database so that no changes can be made from any web applications or other programs. This session should remain open, and the database locked for the next few steps.<\/p>\n<\/div>\n
Step 4.<\/strong> After the FLUSH TABLES command finishes, run the following command and press enter.<\/p>\n
\nSHOW MASTER STATUS<\/pre>\n
Record the information under “File Name” and “Position”.<\/p>\n<\/div>\n
Step 5.\u00a0<\/strong> Make a copy of the database files.<\/p>\n
\n5.1 LVM Snapshot:<\/strong><\/p>\n
In another terminal session, run the following command to make an LVM snapshot of the database.<\/p>\n
lvcreate -L10G -s -n mysql-backup \/dev\/mapper\/dbases<\/pre>\n
This creates a snapshot of the database files very quickly. We can use the snapshot later to copy the data to the backup server without interfering with the original database files.<\/p>\n
After this command finishes, you can unlock the database as shown in the next step. Then you can mount the new LVM partition and copy the files to the backup server.<\/p>\n
mkdir -p \/mnt\/mysql-backup<\/pre>\n
mount -o nouuid \/dev\/mapper\/mysql-backup \/mnt\/mysql-backup<\/pre>\n
rsync -avz -e \"ssh -c blowfish\" \/mnt\/mysql-backup user@remote.host:\/backup\/location<\/pre>\n
5.2 RSYNC:<\/strong><\/p>\n
If you don’t have your database files on an LVM partition, you can just copy the files to the backup server now using rsync, scp or what have you. This will take significantly longer (depending on the size of your database), leaving the database in a locked state.<\/p>\n
rsync -avz -e \"ssh -c blowfish\" \/dbases\/mysql user@remote.host:\/backup\/location<\/pre>\n
5.3 MySQL Dump:<\/strong><\/p>\n
You could also take a mysqldump of the database and copy that SQL file to the other server.<\/p>\n
mysqldump -uuser -p --all-databases > mysql-backup.sql<\/pre>\n
scp mysql-backup.sql user@remote.host:\/backup\/location<\/pre>\n<\/div>\n
Step 6.<\/strong> Once the lvcreate command has finished, you can unlock the database.<\/p>\n
\nUNLOCK TABLES<\/pre>\n<\/div>\n
Step 7.<\/strong> If you haven’t already, copy the copy of the database files to the backup server.<\/p>\n
On the slave server<\/h3>\n
Step 1.<\/strong> Edit the my.cnf file to include at least the following (if needed) lines. Note: you will have to restart MySQL for these changes to take affect.<\/p>\n
\n\r\n[mysqld]\r\nserver_id=2\r\n<\/pre>\n<\/div>\n
Step 2.<\/strong> Start MySQL and run the following commands in a mysql session to start the MySQL slave.<\/p>\n
\n\r\nCHANGE MASTER TO\r\nMASTER_HOST = \"master.server.com\",\r\nMASTER_USER = \"rep_user\",\r\nMASTER_PASSWORD = \"passwordhere\",\r\nMASTER_LOG_FILE = \"mysql-bin.log\",\r\nMASTER_LOG_POS = 2341234;\r\n<\/pre>\n
The MASTER_HOST is the domain name or IP address of the master server. MASTER_USER, MASTER_PASSWORD were created on the master server in Step 2<\/strong>. MASTER_LOG_FILE and MASTER_LOG_POS were gathered in Step 4<\/strong>.Then, finally, to start the slave, issue the following command in mysql.<\/p>\n
START SLAVE;<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"I’m trying this new idea for backing up our production MySQL servers. I have a backup server that basically runs rdiff-backup in the morning across several servers, but then does nothing for the rest of the day. It’s a pretty decent machine, so I’d like to utilize some resources. Databases are a tough cookie to … Continue reading Backing up MySQL with Replication and Incremental Files – Part 1<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":1318,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[243,167,9],"tags":[242,63,244],"class_list":["post-1277","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-howto","category-technical","category-technology","tag-database-admin","tag-mysql","tag-server-admin"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/mossiso.com\/wp-content\/uploads\/2012\/07\/mysql-replication.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9wosP-kB","_links":{"self":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts\/1277"}],"collection":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/comments?post=1277"}],"version-history":[{"count":33,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts\/1277\/revisions"}],"predecessor-version":[{"id":1645,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts\/1277\/revisions\/1645"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/media\/1318"}],"wp:attachment":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/media?parent=1277"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/categories?post=1277"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/tags?post=1277"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- http:\/\/www.mysqlperformanceblog.com\/2006\/08\/21\/using-lvm-for-mysql-backup-and-replication-setup\/<\/a><\/li>\n
- Percona XtraBackup<\/a> (nice way to ensure InnoDB and MyISAM tables are backed up properly, also does it incrementally)<\/li>\n