Home » Computer & Internet » Websites » How to Backup MySQL Databases with mylvmbackup on Debian Squeeze

How to Backup MySQL Databases with mylvmbackup on Debian Squeeze

Backup MySQL Databases with mylvmbackup on Debian Squeeze

mylvmbackup is a Perl script that allows you to instantly create MySQL backups. The whole process is done with the help of LVM’s snapshot feature. In order to make a backup, mylvmbackup fetches a read lock that can be found on all tables. Besides that, all server caches in disk are flushed by it. It then set up a snapshot of the volume which has the MySQL data directory, and also unlocks the tables for another time. This step by step guide helps you to use it on a Debian Squeeze server.

Note: Make sure you already have MySQL on your system. The system is required to use LVM. Besides, the MySQL data directory should have an LVM partition of its own (this is optional).

Others are Reading

Instructions

  • 1

    Preparing /dev/sdb

    Before making snapshots on /dev/sdb, you have to make partition of it and then add it to your volume group.

    Now you have to make the partition /dev/sdb1 and add it to the server1 volume group:

    fdisk /dev/sdb

    server1:~# fdisk /dev/sdb

    Device does not have a valid DOS partition table. In addition, it hasn't Sun, SGI or OSF disklab. Building a new DOS disklabel. Changes will be still in the memory, unless you decide them to write.

    The disk which contains the total amount of cylinders has been set to 1305. It is completely right, but it is quite bigger than 1024, and create some problems in certain setups 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

    All done. You don’t need to make any more volumes on it. It will be processed by mylvmbackup automatically.

  • 2

    Installing and using mylvmbackup

    Debian Squeeze offers a complete package for mylvmbackup, and you just have to install it according to the given procedure;

    apt-get install mylvmbackup

    Follow these instructions

    man mylvmbackup

    The mylvmbackup configuration file is /etc/mylvmbackup.conf, so you have two choices. You can write the options on the command line or inside the file.

    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

    You have to type the correct password, volume group name (server1 here), and also the name of the volume that has MySQL data.

    If 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
    20120416 19:16:58 Info: Connecting to database...
    20120416 19:16:58 Info: Flushing tables with read lock...
    20120416 19:16:58 Info: Taking position record...
    20120416 19:16:58 Info: Taking snapshot...
    File descriptor 3 left open
    Logical volume "mysql_snapshot" created
    20120416 19:16:58 Info: Unlocking tables...
    20120416 19:16:58 Info: Disconnecting from database...
    20120416 19:16:58 Info: Mounting snapshot...
    20120416 19:16:59 Info: Copying my.cnf...
    20120416 19:16:59 Info: Taking actual backup...
    20120416 19:16:59 Info: Creating tar archive /var/cache/mylvmbackup/backup/backup-20120416_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-20120416_191658_mysql.pos
    backup-pos/backup-20120416_191658_my.cnf
    20120416 19:17:00 Info: DONE
    20120416 19:17:00 Info: Cleaning up...
    20120416 19:17:00 Info: LVM Usage stats:
    20120416 19:17:00 Info:   LV             VG      Attr   LSize Origin Snap%  Move Log Copy%  Convert
    20120416 19:17:00 Info:   mysql_snapshot server1 swi-a- 5.00G mysql    0.00
    Logical volume "mysql_snapshot" successfully removed
    root@server1:~#

  • 3

    After that you can 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 2012-04-16 19:17 backup-20120416_191658_mysql.tar.gz

    root@server1:~#

    There are two directories in the tar.gz file. In addition, there is a backup and you just need to copy backup 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-20120416_191658_mysql.tar.gz
    ls –l

    root@server1:/var/cache/mylvmbackup/backup# ls -l
    total 256
    drwxr-xr-x 5 mysql mysql   4096 2012-04-16 19:10 backup
    -rw-r--r-- 1 root  root  246847 2012-04-16 19:17 backup-20120416_191658_mysql.tar.gz
    drwxr-xr-x 2 root  root    4096 2012-04-16 19:24 backup-pos
    root@server1:/var/cache/mylvmbackup/backup#


Posted by chris_ellsbury in Websites

People Who Read This Also Read:

Leave a Reply

Your email address will not be published. Required fields are marked *


three − 2 =