Note: The below methods only apply when using LVM snapshot backups. MySQL masters are configured to use LVM backups and slaves can use mysqldump with local crons. This can be checked in the configuration in /var/backup/script/mysql-backup.conf.
LVM snapshot is an exact copy of an LVM partition that has all the data from the LVM volume from the time the snapshot was created. The advantages of this is that we get an instant hot backup with minimal stress to the MySQL Server. Normal backup using mysqldump or mysqlhotcopy will create a logical backup, which is usually expensive and CPU intensive.
The idea is like this:
1. Create a new logical volume in new hard disk
2. Mount the logical volume into MySQL data and log directory
3. Create LVM snapshot to the MySQL partition that hold MySQL data and log
4. Mount the LVM snapshot into the server
5. Create MySQL backup from that snapshot
6. Backup MySQL partition: /mysql_snap
Steps to setup:
-
We will use another hard disk to mount /data via logical volume. Lets create the partition first:
$ fdisk /dev/sdb
Sequence pressed on keyboard: n > p > 1 > Enter > Enter > w
-
You should see disk partition has been created as /dev/sdb1 as below:
$ fdisk -l /dev/sdb Disk /dev/sdb: 11.8 GB, 11811160064 bytes 255 heads, 63 sectors/track, 1435 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0xaa7ca5e3 Device Boot Start End Blocks Id System /dev/sdb1 1 1435 11526606 83 Linux -
Check the current physical volume, volume group and logical volume details:
$ pvs && vgs && lvs PV VG Fmt Attr PSize PFree /dev/sda2 VolGroup00 lvm2 a-- 49.88G 0 VG #PV #LV #SN Attr VSize VFree VolGroup00 1 2 0 wz--n- 49.88G 0 LV VG Attr LSize Origin Snap% Move Log Copy% Convert LogVol00 VolGroup00 -wi-ao 44.97G LogVol01 VolGroup00 -wi-ao 4.91G
You can see that this server has a volume group called VolGroup under /dev/sda2. Inside this volume group we have another 2 logical volume for root and swap (smaller one).
-
What we are going to do now is to use /dev/sdb1 (our new hard disk) to extend VolGroup00 and create another logical volume for mysql called lv_mysql:
$ pvcreate /dev/sdb1 $ vgextend VolGroup00 /dev/sdb1
Now volume VolGroup00 should be extended. You can check VFree value by using this command:
$ vgs
-
We will use 50G for MySQL and another 50G will be dedicated to the snapshot volume. Now lets create the mysql logical volume called lv_mysql:
$ lvcreate -L 50G -n lv_mysql VolGroup00 -
When you run following command, you should see lv_mysql has been created under VolGroup00 volume:
$ lvs LV VG Attr LSize Origin Snap% Move Log Copy% Convert lv_mysql VolGroup00 wi-a 50.00g -
Logical volume created. Lets format it with ext4 filesystem before we can mount them to /data directory:
#if not installed run $ yum install -y e4fsprogs $ mkfs.ext4 /dev/mapper/VolGroup00-lv_mysql $ mkdir /data -
Add the following line into /etc/fstab and mount the partition:
/dev/mapper/VolGroup00-lv_mysql /data ext4 defaults 0 0Mount the logical volume:$ mount -a -
Stop the MySQL service and copy over the data to newly mounted logical volume. We will using rsync to copy to keep the permissions, ownership and timestamp. Dont forget to change ownership for /data/mysql directory as well:
$ service mysql stop $ mkdir /data/mysql $ rsync -avzP /var/lib/mysql/ /data/mysql/ $ chown mysql:mysql /data/mysql -
Change following value in /etc/my.cnf to map the new directory:
datadir = /data/mysqlStart the MySQL server:$ service mysqld start -
MySQL should start and mapped to the new directory. We can use LVM snapshot from now onwards since MySQL already inside LVM partition. Now, we can start to create snapshot and I will dedicate 50 GB of space for this purpose:
$ lvcreate -L 50G --snapshot -n mysql_backup /dev/VolGroup00/lv_mysql -
Create snapshot is very fast. Once done, we can check the snapshot status as below:
$ lvs | grep mysql_backup mysql_backup VolGroup00 swi-a- 50.00g lv_mysql 31.32 -
Now lets mount the snapshot partition so we can see the backup data:
$ mkdir /data_snap $ mount /dev/mapper/VolGroup00-mysql_backup /data_snap -
Create the following directories on the client and server:
- /var/backup/scripts
- /var/log/mysql
-
Create /backup (ideally a mounted large drive) on the backup pull server.
-
Upload the client scripts to all machines, but only the server should ALSO have the server scripts (overwriting any client scripts).
-
Update conf files with values applicable to your environment.
-
Create ssh keys so that the server can log in with root priveleges without passwords to the clients. Instructions: [http://www.thegeekstuff.com/2008/11/3-steps-to-perform-ssh-login-without-password-using-ssh-keygen-ssh-copy-id/]http://www.thegeekstuff.com/2008/11/3-steps-to-perform-ssh-login-without-password-using-ssh-keygen-ssh-copy-id/
-
Test /var/backup/script/mysql-backup on each client manually to ensure snapshots are created successfully with the script.
On Production servers we want to sync bin logs as well.
-
While /data is mounted,
mkdir /data/binlog -
Add a new 75GB or so drive to the virtual machine.
mkfs.ext4 /dev/sdc1 -
Stop the mysqld service
-
Edit /etc/fstab to include:
/dev/sdc1 /data/binlog ext4 defaults 0 0 -
mount -a -
Move any existing bin logs and index files to /data/binlog from /var/log/mysql
mv /var/log/mysql/*.index /data/binlog/ mv /var/log/mysql/mysql-* /data/binlog/ -
Change permissions:
chown mysql:mysql /data/binlog/ -
Edit index and control files. You can find things that need to be changed by using grep.
cd /data/binlog grep /var/log * -
Start the mysql service.
-
On the mysql-backup server edit /var/backup/script/rsync-binlogs.sh by copying an existing line and changing it to the server just configured.
MySQL Backups are done using the MySQL Backup shell script in /var/backup/script on the client and the getLVMbackups.sh included in the package on the mysql-backup server.
runScheduledbackups.sh on the backup server manages the backup and logging. It can run from every half hour to every day and cycle through the servers in servers.conf. getLVMbackups logs into each of these servers, runs the mysql-backup.sh script on the server to create a snapshot and then rsyncs the snapshot to the backup server. It then runs delete-snapshot.sh to delete the snapshot on the client.
Once this is complete, the backup server compresses all of the backups, archives and deletes old backups and pushes the updates to TSM via the dsmcad service. This can be changed or removed depending on your backup strategy.
Server TSM settings:
nano /opt/tivoli/tsm/client/ba/bin/inclexcl
exclude /dev/*
include /backup/*.tar.gz RDBMS
exclude.dir /backup/*
Crontab:
16 */4 * * * /var/backup/script/runScheduledBackup.sh > /dev/null
5,35 * * * * /var/backup/script/rsync-binlogs.sh >> /var/log/mysql/backup.log
rsync-binlogs.sh is configured to rsync binary logs every half hour from production servers for point in time recovery. This can be configured by editing the script.
Binary logs are backed up to /backup/SERVERNAME/binlog with the rsync script.
The recommended restore method is to log into the server mysql-backup and restore the latest backup there by using it as a vanilla box. Note: These commands will wipe out the database on mysql-backup. Be sure you are logged into the correct server.
service mysqld stop
rm -rF /var/lib/mysql/*
tar -C /tmp -xzvf /backup/SERVERNAME.current.TIME.tar.gz
mv /tmp/SERVERNAME/mysql/* /var/lib/mysql/
service mysqld start
You now have an online copy of the database. At this point you can replay binary logs to do point in time or do a mysqldump of the data you need to recover and scp it to the server in question to restore.
mysqldump --triggers --routines -u root -p --databases x y z > x_y_z_DATE.sql
or you can do
mysqldump --triggers --routines -u root -p --all-databases > full_dump.sql
scp myfile.sql root@SERVERNAME:
ssh root@SERVERNAME
mysql -p`
`MYSQL> #may need to USE DATABASE;
MYSQL> source myfile.sql
MYSQL> exit
Backed up binary logs on the backup server are located in /backup/SERVERNAME/binlogs
http://blog.secaserver.com/2012/05/mysql-live-backup-lvm-snapshots/