Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | 云栖社区 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Github | Search | About

21.5. Backup and Recovery

21.5.1. Import / Export Export(Backup)

mysqldump -hlocalhost -proot -p**** mydb > mydb.sql


mysqldump -hlocalhost -proot -p**** mydb | gzip > mydb.sql.gz Import(Recovery)

mysql -hlocalhost -proot -p**** mydb < mydb.sql


gunzip mydb.sql.gz -c | mysql -hlocalhost -proot -p**** mydb xml

export xml

$ mysqldump -uusrname -ppasswd -X -t database table -r filename.xml 备份表数据

SELECT * INTO OUTFILE 'file_name' FROM tbl_name
LOAD DATA INFILE 'file_name' REPLACE INTO TABLE tbl_name source

mysql> use your_db
mysql> SOURCE database.sql 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库

shell> mysqlhotcopy db_name /path/to/some/dir
mysql:~# mysqlhotcopy --user=neo --password=chen shop /tmp/backup
Locked 100 tables in 0 seconds.
Flushed tables (`shop`.`account_log`, `shop`.`ad`, `shop`.`ad_custom`, `shop`.`ad_position`, `shop`.`admin_action`,
`shop`.`admin_log`, `shop`.`admin_message`, `shop`.`admin_user`, `shop`.`adsense`, `shop`.`affiliate_log`,
`shop`.`user_rank`, `shop`.`users`, `shop`.`virtual_card`, `shop`.`volume_price`, `shop`.`vote`, `shop`.`vote_log`,
`shop`.`vote_option`, `shop`.`wholesale`) in 0 seconds.
Copying 299 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 100 tables (299 files) in 0 seconds (0 seconds overall). AutoMySQLBackup

https://sourceforge.net/projects/automysqlbackup/ xtrabackup - Open source backup tool for InnoDB and XtraDB.

https://launchpad.net/percona-xtrabackup Percona yum Repository
$ rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum search xtrabackup
================================================================ N/S Matched: XtraBackup =================================================================
holland-xtrabackup.noarch : Xtrabackup plugin for Holland
percona-xtrabackup.x86_64 : XtraBackup online backup for MySQL / InnoDB
percona-xtrabackup-debuginfo.x86_64 : Debug information for package percona-xtrabackup
percona-xtrabackup-test.x86_64 : Test suite for Percona Xtrabackup
# yum install percona-xtrabackup Creating an Incremental Backup
xtrabackup --backup --target-dir=/data/backups/base --datadir=/var/lib/mysql/

21.5.2. Snapshot Backup LVM Snapshot

# mysql –uroot –pmysql
mysql> flush tables with read lock;
mysql>flush logs;
mysql>system lvcreate -L1024M -s -n snap0 /dev/vg00/lvol00
mysql>show master status;
mysql>unlock tables;
mysql>quit Btrfs Snapshot

# btrfs subvolume snapshot /data /data/backup_2013-03-20
Create a snapshot of '/data' in '/data/backup_2013-03-20'

btrfs subvolume list /data
ID 315 gen 172 top level 5 path backup_2013-03-10
ID 320 gen 178 top level 5 path backup_2013-03-20