How To Back Up MySQL Databases With mylvmbackup On Ubuntu 8.10 - Page 2

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#

4 Links

• mylvmbackup: http://lenz.homelinux.org/mylvmbackup/
• MySQL: http://www.mysql.com/
• Ubuntu: http://www.ubuntu.com/