Come Eseguire il Backup dei Database MySQL Con mylvmbackup Su Debian Squeeze

mylvmbackup è uno script Perl per creare rapidamente backup di MySQL. Utilizza la funzione snapshot di LVM per farlo. Per eseguire un backup, mylvmbackup ottiene un blocco di lettura su tutte le tabelle e svuota tutte le cache del server su disco, crea uno snapshot del volume contenente la directory dei dati di MySQL e sblocca di nuovo le tabelle. Questo articolo mostra come usarlo su un server Debian Squeeze.

Non rilascio alcuna garanzia che questo funzionerà per te!

1 Nota Preliminare

Presumo che MySQL sia già configurato e in esecuzione sul tuo sistema. Il sistema deve utilizzare LVM e la directory dei dati di MySQL (/var/lib/mysql) dovrebbe avere una propria partizione LVM (anche se questo è facoltativo).

Se hai letto “Eseguire il Backup (E Ripristinare) le Partizioni LVM Con gli Snapshot LVM”, sai che gli snapshot LVM richiedono una partizione LVM non utilizzata per lo snapshot. Il mio sistema di test ha un secondo disco rigido, attualmente non utilizzato, /dev/sdb che sarà utilizzato da mylvmbackup per creare un volume logico temporaneo per il backup.

Questa è la mia situazione attuale:

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:~#

Come vedi, ho due partizioni LVM, / e /var/lib/mysql (più una partizione swap LVM non mostrata qui). Il gruppo di volumi si chiama server1 e i volumi si chiamano swap, root e 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:~#

Ecco una panoramica dei miei due dischi rigidi:

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 Preparazione di /dev/sdb

Prima di poter creare snapshot su /dev/sdb, dobbiamo partizionarlo (Linux LVM) e aggiungerlo al nostro gruppo di volumi (server1).

Ora creerò la partizione /dev/sdb1 e la aggiungerò al gruppo di volumi server1:

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:

software that runs at boot time (e.g., old versions of LILO)
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

Questo è tutto - non abbiamo bisogno di creare alcun volume su di esso - questo sarà fatto automaticamente da mylvmbackup.

3 Installazione e Utilizzo di mylvmbackup

Debian Squeeze fornisce un pacchetto per mylvmbackup, quindi possiamo semplicemente installarlo come segue:

apt-get install mylvmbackup

Dai un’occhiata a

man mylvmbackup

per imparare come usarlo (leggi attentamente la parte riguardante le tabelle InnoDB se stai usando InnoDB).

Il file di configurazione di mylvmbackup è /etc/mylvmbackup.conf, quindi puoi specificare le tue opzioni sulla riga di comando o in quel file (le opzioni della riga di comando sovrascriveranno le opzioni in /etc/mylvmbackup.conf).

La directory di backup predefinita è /var/cache/mylvmbackup/backup (a meno che tu non specifichi un’altra posizione).

Un comando di esempio per eseguire il backup delle tabelle MyISAM sarebbe:

mylvmbackup --user=root --password=yourrootsqlpassword --mycnf=/etc/mysql/my.cnf --vgname=server1 --lvname=mysql --backuptype=tar

E per InnoDB:

mylvmbackup --user=root --password=yourrootsqlpassword --innodb_recover --skip_flush_tables --mycnf=/etc/mysql/my.cnf --vgname=server1 --lvname=mysql --backuptype=tar

Assicurati di inserire la password corretta, il nome del gruppo di volumi (server1 qui) e il nome del volume che contiene i dati di MySQL (il volume è /dev/server1/mysql, quindi il nome è mysql).

Se tutto va bene, dovresti vedere molti 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.MYI  
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:~#

Successivamente puoi trovare il backup nella directory /var/cache/mylvmbackup/backup (a meno che tu non abbia specificato un’altra posizione):

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:~#

Il file tar.gz contiene due directory, backup (con i database e le tabelle da /var/lib/mysql che puoi semplicemente copiare di nuovo dopo un crash del database - il database dovrebbe essere fermato quando lo fai) e backup-pos che contiene il tuo file my.cnf (un backup di /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#

4 Link

mylvmbackup: http://www.lenzg.net/mylvmbackup/
MySQL: http://www.mysql.com/
Debian: http://www.debian.org/

1 Nota Preliminare

2 Preparazione di /dev/sdb

3 Installazione e Utilizzo di mylvmbackup

4 Link

Ricevi i nuovi post nella tua casella di posta.