I do not issue any guarantee that this will work for you!
1 Preliminary Note
I'm assuming that MySQL is already set up and running on your system. The system must use LVM, and the MySQL data directory (/var/lib/mysql) should have an LVM partition of its own (althouth that is optional).
If you have read Back Up (And Restore) LVM Partitions With LVM Snapshots you know that LVM snapshots require some unused LVM partition for the snapshot. My test system has a second, currently unused hard drive /dev/sdb that will be used by mylvmbackup to create a temporary logical volume for the backup.
This is my current situation:
root@server1:~# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/server1-root
20G 808M 18G 5% /
tmpfs 252M 0 252M 0% /lib/init/rw
varrun 252M 56K 251M 1% /var/run
varlock 252M 0 252M 0% /var/lock
udev 252M 2.6M 249M 2% /dev
tmpfs 252M 0 252M 0% /dev/shm
/dev/sda1 471M 23M 425M 6% /boot
/dev/mapper/server1-mysql
8.9G 170M 8.3G 2% /var/lib/mysql
root@server1:~#
As you see, I have two LVM partitions, / and /var/lib/mysql (plus an LVM swap partition not shown here). The volume group is named server1, and the volumes are named swap, root, and mysql:
root@server1:~# pvdisplay
--- Physical volume ---
PV Name /dev/sda5
VG Name server1
PV Size 29.52 GB / not usable 3.66 MB
Allocatable yes (but full)
PE Size (KByte) 4096
Total PE 7557
Free PE 0
Allocated PE 7557
PV UUID 0gCmpE-FGel-9ayg-E2yg-kkEu-B72X-kFvaye
root@server1:~#
root@server1:~# vgdisplay
--- Volume group ---
VG Name server1
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 4
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 3
Open LV 3
Max PV 0
Cur PV 1
Act PV 1
VG Size 29.52 GB
PE Size 4.00 MB
Total PE 7557
Alloc PE / Size 7557 / 29.52 GB
Free PE / Size 0 / 0
VG UUID PH5Hpc-jqeP-BFYs-wWlA-hu03-qwuQ-0cNIu3
root@server1:~#
root@server1:~# lvdisplay
--- Logical volume ---
LV Name /dev/server1/swap
VG Name server1
LV UUID RCeLCK-MO5p-xoMq-SwTT-n2NV-GaP6-GaemDp
LV Write Access read/write
LV Status available
# open 2
LV Size 1.00 GB
Current LE 256
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 254:0
--- Logical volume ---
LV Name /dev/server1/root
VG Name server1
LV UUID 5Wen7n-xYmh-MQz1-fKH5-0XXa-1y2t-V3PYbb
LV Write Access read/write
LV Status available
# open 1
LV Size 19.53 GB
Current LE 5000
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 254:1
--- Logical volume ---
LV Name /dev/server1/mysql
VG Name server1
LV UUID wk8yb6-fDl8-4tg3-tneT-1dDe-wWdy-AfGZ5I
LV Write Access read/write
LV Status available
# open 1
LV Size 8.99 GB
Current LE 2301
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 254:2
root@server1:~#
Here's an overview of my two hard drives:
root@server1:~# fdisk -l
Disk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0x0009353f
Device Boot Start End Blocks Id System
/dev/sda1 * 1 62 497983+ 83 Linux
/dev/sda2 63 3916 30957255 5 Extended
/dev/sda5 63 3916 30957223+ 8e Linux LVM
Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0x00000000
Disk /dev/sdb doesn't contain a valid partition table
root@server1:~#
2 Preparing /dev/sdbDisk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0x0009353f
Device Boot Start End Blocks Id System
/dev/sda1 * 1 62 497983+ 83 Linux
/dev/sda2 63 3916 30957255 5 Extended
/dev/sda5 63 3916 30957223+ 8e Linux LVM
Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0x00000000
Disk /dev/sdb doesn't contain a valid partition table
root@server1:~#
Before we can create snapshots on /dev/sdb, we must partition it (Linux LVM) and add it to our volume group (server1).
I will now create the partition /dev/sdb1 and add it to the server1 volume group:
fdisk /dev/sdb
server1:~# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): <-- n
Command action
e extended
p primary partition (1-4)
<-- p
Partition number (1-4): <-- 1
First cylinder (1-1305, default 1): <-- [ENTER]
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305): <-- [ENTER]
Using default value 1305
Command (m for help): <-- t
Selected partition 1
Hex code (type L to list codes): <-- 8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help): <-- w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
pvcreate /dev/sdb1
vgextend server1 /dev/sdb1
That's it - we don't need to create any volumes on it - this will be done by mylvmbackup automatically.
3 Installing And Using mylvmbackup
Ubuntu 8.10 provides a package for mylvmbackup, therefore we can simply install it as follows:
apt-get install mylvmbackup
Take a look at
man mylvmbackup
to learn how to use it (read the part about InnoDB tables carefully if you're using InnoDB).
The mylvmbackup configuration file is /etc/mylvmbackup.conf, so you can either specify your options on the command line or in that file (command line options will override the options in /etc/mylvmbackup.conf).
The default backup directory is /var/cache/mylvmbackup/backup (unless you specify another location).
A sample command for backing up MyISAM tables would be:
mylvmbackup --user=root --password=yourrootsqlpassword --mycnf=/etc/mysql/my.cnf --vgname=server1 --lvname=mysql --backuptype=tar
And for InnoDB:
mylvmbackup --user=root --password=yourrootsqlpassword --innodb_recover --skip_flush_tables --mycnf=/etc/mysql/my.cnf --vgname=server1 --lvname=mysql --backuptype=tar
Make sure you fill in the right password, volume group name (server1 here) and the volume name of the volume that contains the MySQL data (the volume is /dev/server1/mysql, therefore the name is mysql).
I everything goes well, you should see lots of output:
root@server1:~# mylvmbackup --user=root --password=yourrootsqlpassword --mycnf=/etc/mysql/my.cnf --vgname=server1 --lvname=mysql --backuptype=tar
20081204 19:16:58 Info: Connecting to database...
20081204 19:16:58 Info: Flushing tables with read lock...
20081204 19:16:58 Info: Taking position record...
20081204 19:16:58 Info: Taking snapshot...
File descriptor 3 left open
Logical volume "mysql_snapshot" created
20081204 19:16:58 Info: Unlocking tables...
20081204 19:16:58 Info: Disconnecting from database...
20081204 19:16:58 Info: Mounting snapshot...
20081204 19:16:59 Info: Copying my.cnf...
20081204 19:16:59 Info: Taking actual backup...
20081204 19:16:59 Info: Creating tar archive /var/cache/mylvmbackup/backup/backup-20081204_191658_mysql.tar.gz
backup/
backup/mydb/
backup/mydb/sys_modules.MYI
backup/mydb/dns_a.frm
backup/mydb/isp_dienste.MYD
backup/mydb/isp_server_ip.frm
backup/mydb/dns_spf.frm
backup/mydb/dns_a.MYI
backup/mydb/isp_fakt_dep.frm
backup/mydb/multidoc_dep.frm
backup/mydb/isp_isp_web_template.MYI
backup/mydb/sys_nodes.MYD
backup/mydb/listtype.MYD
backup/mydb/help_documents.MYD
backup/mydb/help_tickets.MYI
backup/mydb/doctype.frm
backup/mydb/login.MYI
backup/mydb/isp_com.frm
backup/mydb/help_documents.MYI
backup/mydb/isp_dep.MYD
backup/mydb/help_documents.frm
backup/mydb/isp_server.MYD
backup/mydb/isp_fakt_nodes.MYD
backup/mydb/sys_config.MYD
backup/mydb/dns_nodes.MYI
backup/mydb/sys_config.MYI
backup/mydb/isp_monitor.frm
backup/mydb/isp_server_ip.MYI
backup/mydb/isp_isp_datenbank.frm
backup/mydb/dns_secondary.frm
backup/mydb/isp_nodes.MYI
backup/mydb/dns_isp_dns.MYI
backup/mydb/help_nodes.frm
backup/mydb/isp_fakt_nodes.frm
backup/mydb/isp_server.MYI
backup/mydb/isp_isp_domain.frm
backup/mydb/dns_dep.frm
backup/mydb/session.frm
backup/mydb/isp_isp_cron.MYD
backup/mydb/isp_fakt_record.MYI
backup/mydb/isp_monitor.MYI
backup/mydb/isp_fakt_rechnung.MYI
backup/mydb/listtype.MYI
backup/mydb/isp_fakt_rechnung.MYD
backup/mydb/isp_traffic.frm
backup/mydb/isp_fakt_dep.MYI
backup/mydb/user_groups.frm
backup/mydb/isp_fakt_record.frm
backup/mydb/isp_fakt_artikel.MYD
backup/mydb/isp_htaccess.MYD
backup/mydb/sys_nodes.frm
backup/mydb/groups.frm
backup/mydb/login.MYD
backup/mydb/isp_firewall.MYD
backup/mydb/isp_server.frm
backup/mydb/help_tickets.frm
backup/mydb/multidoc_dep.MYD
backup/mydb/dns_nodes.frm
backup/mydb/dns_a.MYD
backup/mydb/sys_config.frm
backup/mydb/dns_isp_dns.frm
backup/mydb/dns_mx.MYI
backup/mydb/isp_isp_web.MYD
backup/mydb/isp_serverstatus.MYI
backup/mydb/isp_serverstatus.MYD
backup/mydb/sys_dep.MYD
backup/mydb/isp_isp_cron.MYI
backup/mydb/session.MYD
backup/mydb/isp_isp_admin.MYD
backup/mydb/dns_ptr.frm
backup/mydb/dns_mx.frm
backup/mydb/isp_isp_domain.MYD
backup/mydb/sys_dep.MYI
backup/mydb/dns_spf.MYD
backup/mydb/user_groups.MYD
backup/mydb/sys_news.frm
backup/mydb/isp_isp_actions.MYI
backup/mydb/doctype.MYD
backup/mydb/multidoc_nodes.frm
backup/mydb/isp_fakt_artikel.frm
backup/mydb/sys_news.MYD
backup/mydb/isp_traffic.MYD
backup/mydb/user_groups.MYI
backup/mydb/sys_news.MYI
backup/mydb/listtype.frm
backup/mydb/del_status.frm
backup/mydb/isp_fakt_nodes.MYI
backup/mydb/isp_isp_kunde.MYD
backup/mydb/isp_dienste.frm
backup/mydb/dns_mx.MYD
backup/mydb/doctype.MYI
backup/mydb/help_tickets.MYD
backup/mydb/dns_secondary.MYI
backup/mydb/dns_ptr.MYD
backup/mydb/isp_isp_reseller.frm
backup/mydb/isp_dienste.MYI
backup/mydb/isp_isp_datenbank.MYD
backup/mydb/isp_isp_actions.MYD
backup/mydb/isp_isp_web.frm
backup/mydb/db.opt
backup/mydb/isp_server_ip.MYD
backup/mydb/multidoc_nodes.MYI
backup/mydb/dns_nodes.MYD
backup/mydb/isp_fakt_rechnung.frm
backup/mydb/isp_isp_reseller.MYI
backup/mydb/isp_nodes.MYD
backup/mydb/isp_htaccess.MYI
backup/mydb/isp_isp_web_template.frm
backup/mydb/isp_isp_domain.MYI
backup/mydb/dns_secondary.MYD
backup/mydb/dns_dep.MYD
backup/mydb/isp_firewall.MYI
backup/mydb/help_nodes.MYI
backup/mydb/isp_isp_admin.frm
backup/mydb/isp_isp_cron.frm
backup/mydb/isp_isp_datenbank.MYI
backup/mydb/isp_traffic_ip.frm
backup/mydb/isp_fakt_dep.MYD
backup/mydb/isp_dep.MYI
backup/mydb/dns_dep.MYI
backup/mydb/isp_isp_reseller.MYD
backup/mydb/dns_isp_dns.MYD
backup/mydb/isp_fakt_artikel.MYI
backup/mydb/multidoc_dep.MYI
backup/mydb/multidoc_nodes.MYD
backup/mydb/del_status.MYD
backup/mydb/groups.MYD
backup/mydb/isp_isp_web_template.MYD
backup/mydb/isp_htaccess.frm
backup/mydb/isp_dep.frm
backup/mydb/isp_isp_web.MYI
backup/mydb/isp_isp_user.frm
backup/mydb/session.MYI
backup/mydb/isp_isp_admin.MYI
backup/mydb/isp_isp_kunde.MYI
backup/mydb/isp_isp_user.MYI
backup/mydb/isp_fakt_record.MYD
backup/mydb/isp_nodes.frm
backup/mydb/groups.MYI
backup/mydb/del_status.MYI
backup/mydb/dns_spf.MYI
backup/mydb/isp_com.MYD
backup/mydb/isp_isp_user.MYD
backup/mydb/dns_cname.frm
backup/mydb/isp_com.MYI
backup/mydb/dns_cname.MYD
backup/mydb/sys_modules.MYD
backup/mydb/isp_traffic_ip.MYI
backup/mydb/help_nodes.MYD
backup/mydb/sys_user.frm
backup/mydb/isp_traffic_ip.MYD
backup/mydb/sys_user.MYD
backup/mydb/sys_modules.frm
backup/mydb/isp_serverstatus.frm
backup/mydb/sys_dep.frm
backup/mydb/isp_firewall.frm
backup/mydb/isp_monitor.MYD
backup/mydb/isp_isp_kunde.frm
backup/mydb/dns_cname.MYI
backup/mydb/isp_isp_actions.frm
backup/mydb/sys_user.MYI
backup/mydb/sys_nodes.MYI
backup/mydb/dns_ptr.MYI
backup/mydb/isp_traffic.MYI
backup/mydb/login.frm
backup/ib_logfile0
backup/mysql_upgrade_info
backup/debian-5.0.flag
backup/mysql/
backup/mysql/host.MYD
backup/mysql/procs_priv.MYD
backup/mysql/time_zone_transition.MYD
backup/mysql/proc.MYI
backup/mysql/time_zone_name.frm
backup/mysql/time_zone_name.MYD
backup/mysql/help_relation.MYI
backup/mysql/user.MYD
backup/mysql/help_category.MYI
backup/mysql/time_zone.frm
backup/mysql/func.MYD
backup/mysql/help_category.MYD
backup/mysql/time_zone_transition.frm
backup/mysql/time_zone_name.MYI
backup/mysql/help_category.frm
backup/mysql/time_zone_leap_second.frm
backup/mysql/time_zone_transition.MYI
backup/mysql/help_relation.MYD
backup/mysql/host.frm
backup/mysql/db.frm
backup/mysql/db.MYI
backup/mysql/columns_priv.frm
backup/mysql/time_zone.MYI
backup/mysql/time_zone_leap_second.MYD
backup/mysql/func.frm
backup/mysql/columns_priv.MYI
backup/mysql/help_topic.MYD
backup/mysql/host.MYI
backup/mysql/proc.frm
backup/mysql/user.MYI
backup/mysql/help_topic.MYI
backup/mysql/help_relation.frm
backup/mysql/tables_priv.frm
backup/mysql/help_keyword.frm
backup/mysql/user.frm
backup/mysql/time_zone_transition_type.MYI
backup/mysql/procs_priv.frm
backup/mysql/help_topic.frm
backup/mysql/procs_priv.MYI
backup/mysql/time_zone_transition_type.MYD
backup/mysql/func.MYI
backup/mysql/proc.MYD
backup/mysql/tables_priv.MYD
backup/mysql/help_keyword.MYI
backup/mysql/help_keyword.MYD
backup/mysql/time_zone_leap_second.MYI
backup/mysql/tables_priv.MYI
backup/mysql/db.MYD
backup/mysql/time_zone_transition_type.frm
backup/mysql/time_zone.MYD
backup/mysql/columns_priv.MYD
backup/lost+found/
backup/ibdata1
backup/ib_logfile1
backup-pos/backup-20081204_191658_mysql.pos
backup-pos/backup-20081204_191658_my.cnf
20081204 19:17:00 Info: DONE
20081204 19:17:00 Info: Cleaning up...
20081204 19:17:00 Info: LVM Usage stats:
20081204 19:17:00 Info: LV VG Attr LSize Origin Snap% Move Log Copy% Convert
20081204 19:17:00 Info: mysql_snapshot server1 swi-a- 5.00G mysql 0.00
Logical volume "mysql_snapshot" successfully removed
root@server1:~#
Afterwards you can find the backup in the /var/cache/mylvmbackup/backup directory (unless you have specified another location):
ls -l /var/cache/mylvmbackup/backup
root@server1:~# ls -l /var/cache/mylvmbackup/backup
total 248
-rw-r--r-- 1 root root 246847 2008-12-04 19:17 backup-20081204_191658_mysql.tar.gz
root@server1:~#
The tar.gz file contains two directories, backup (with the databases and tables from /var/lib/mysql which you can simply copy back after a database crash - the database should be stopped when you do this) and backup-pos which contains your my.cnf file (a backup of /etc/mysql/my.cnf):
cd /var/cache/mylvmbackup/backup
tar xvfz backup-20081204_191658_mysql.tar.gz
ls -l
root@server1:/var/cache/mylvmbackup/backup# ls -l
total 256
drwxr-xr-x 5 mysql mysql 4096 2008-12-04 19:10 backup
-rw-r--r-- 1 root root 246847 2008-12-04 19:17 backup-20081204_191658_mysql.tar.gz
drwxr-xr-x 2 root root 4096 2008-12-04 19:24 backup-pos
root@server1:/var/cache/mylvmbackup/backup#
Source (howtoforge)
RSS Feed (xml)
No comments:
Post a Comment