How to perform MySQL backups using LVM and setup replication
8:57 PMMySQL backups using LVM and if needed setup replication
Connect to MySQL and run *FLUSH TABLES WITH READ LOCK* |
While holding connection open run: *lvcreate \-L16G \-s \-n dbbackup /dev/Main/Data* \- This will create snapshot named _dbbackup_ for Logical Volume *Main/Data* |
Now you have created logical volume and can unlock the tables, but before that you should probably record binary log position which is done by running *SHOW MASTER STATUS* \- This is binary log position you'll need to point your MySQL Slaves created from this snapshot. |
Snapshot created, now you want to let MySQL Server to continue, which is done by running *UNLOCK TABLES* |
Mount backup Filesystem: *mount /dev/Main/dbbackup /mnt/backup* |
Copy data to backup. Normally you can skip slow query logs and error log while taking backup. You also can skip most of binary logs - however if some of your slaves are far behind you might want to keep some of last binary logs just in case, or you can assume in case of recovery from the backup you will need to restore slaves as well and skip binary logs in your backup process. |
Unmount filesystem *umount /mnt/backup* |
Remove snapshot: *lvremove \-f /dev/Main/dbbackup* |
Remove snapshot: *lvremove \-f /dev/Main/dbbackup* |
If you want to create slave based on such snapshot you need to perform couple of more simple stepsi) Extract/Copy database to the slave database directory.ii)) Start MySQL Server. Wait for it to perform recovery.iii) Use *CHANGE MASTER TO* to point slave to saved binary log position:for eg: change master to master_host="master", master_user="user", master_password="password", master_log_file="saved_master_log_file", master_log_pos=save_master_log_pos;iv) Run *SLAVE START* to restart replication.
0 comments