Shulou(Shulou.com)06/01 Report--

The following mainly brings you how to back up and restore mysql database. I hope these contents can bring you practical use. This is also the main purpose of this article that I edit how to back up and restore mysql database. All right, don't talk too much nonsense, let's just read the following.

Database backup hot backup: does not affect the normal read and write business warm backup: lock the table, only allow read operations on the database cold backup: business pause physical backup: backup mysql-related data file logical backup: export data from mysql Generally, it is a full backup of sql files (no indexed data): full backup incremental backup: overlay differential backup for the last backup: select differential backup for the last full backup and use mysqldump backup

Logical backup, single process (slow), locking table (usually backing up from the library)

You can use the mysqldumper tool to perform multi-process backups.

Backup example: # mysqldump-- flush-logs cacti plugin_thold_contacts plugin_thold_log plugin_thold_template_contact plugin_thold_threshold_contact thold_data thold_template > cacti_thold.sql / / cacti library multi-table backup # mysqldump-h rds.amazonaws.com-u root-p production_sts pubkey_change_logs-- where= "created / data1/xtrabackup/zabbix/0207_1737.tgz" / / backup and send to remote CVM for data recovery:

1. Prepare the CVM: install the same version of mysql. After initialization, shut down the mysql service and clear the data directory of mysql.

two。 Get backup: you can also save it remotely at the time of backup

3. Restore command:

# tar-I-xf 0207_1737.tgz / / if it is packaged, unpack log file recovery (full backup): complete transactions, synchronize data and other operations to make data files consistent # innobackupex-- defaults-file=/usr/local/mysql/my.cnf-- apply-log-- redo-only-u root-pxxxx / tmp/mysql/fullbackup incremental log file recovery (if it is multiple increments Restore in order) You need to specify lsn# innobackupex-- defaults-file=/usr/local/mysql/my.cnf-- apply-log-- redo-only-u root-pxxxxx / tmp/mysql/incre-1337-- incremental-dir=/tmp/mysql/incre/1337# innobackupex-- defaults-file=/usr/local/mysql/my.cnf-- apply-log-u root-pxxxx / tmp/mysql/2016-04-26 backup 13-48-39 / / merge incremental and full backups for the last time Rollback uncommitted things, but you don't need the-- redo-only parameter. Data recovery: make sure that the original data directory is empty (if there are other libraries, move it to another directory first and then move it back later) # innobackupex-defaults-file=/usr/local/mysql/my.cnf-- copy-back-u root-pxxxxx / tmp/mysql/2016-04-26 data 13-48-39 finally re-authorize the mysql_data directory and start the mysql service. Use the incremental backup script #! / bin/bashhour= `date +% H`day = `date +% Flying% H`passwd = "mysqlpass" function backup () {case $1 infull) / usr/bin/innobackupex-u root-p$ {passwd}-- no-timestamp-- extra-lsndir=/data1/innobackup/$ {day} / lsn_$ {now} /-- stream=tar / data1/innobackup/$ {day} / | gzip > / data1/innobackup/$ {day} / ${now} .tgz online Incre) if [- f / data1/innobackup/$ {day} / lsn_$ {last} / xtrabackup_checkpoints] Then / usr/bin/innobackupex-u root-p$ {passwd}-- no-timestamp-- incremental-- incremental_basedir=/data1/innobackup/$ {day} / lsn_$ {last} /-- extra-lsndir=/data1/innobackup/$ {day} / lsn_$ {now} /-- stream=tar / data1/innobackup/$ {day} | gzip > / data1/innobackup/$ {day} / ${now} .tgz else echo "incremental_basedir not found Do a full backup "/ usr/bin/innobackupex-u root-p ${passwd}-- no-timestamp-- extra-lsndir=/data1/innobackup/$ {day} / lsn_$ {now} /-- stream=tar / data1/innobackup/$ {day} / | gzip > / data1/innobackup/$ {day} / ${now} .tgz fi ; *) echo "nothing todo";; esac} case $hour in01) last= `date +% F-d-yesterday`find / data1/innobackup/-name "${last} *"-type d-exec rm-r "{}"\; backup full;; 10) last= `date +% favored% H-d-9hours` backup incre 14 | 18 | 22) last= `date +% qualified% H-d-4hours` backup incre *) echo "not backup time" esac complex application examples: packaging (tar, xbstream), compression, remote backup, incremental backup, Restore backup: # innobackupex-- no-timestamp-- extra-lsndir=/backup/lsn/2304 / backup/2304_full_remot-- stream=tar | gzip | sshpass-p "sshpass" ssh root@192.168.183.130 "cat-> / backup/remote/2304_full.tgz" # innobackupex-no-timestamp-extra-lsndir=/backup/lsn/0000-incremental-incremental_basedir=/backup/lsn/2304 / backup/0000_full_remot-stream=xbstream | gzip | sshpass-p "sshpass" ssh root@ 192.168.183.130 "cat-> / backup/remote/0000_incr.xbstream.gz" # innobackupex-- no-timestamp-- extra-lsndir=/backup/lsn/0006-- incremental-- incremental_basedir=/backup/lsn/0000 / backup/0006_full_remot-- stream=xbstream | gzip | sshpass-p "sshpass" ssh root@192.168.183.130 "cat-> / backup/remote/0006_incr.xbstream.gz" restore: # cd / backup/remote/# tar-I-xf 2304 _ Full.tgz# innobackupex-- apply-log-- redo-only / backup/remote/# gunzip-c 0000_incr.xbstream.gz > 0000_incr/0000_incr.xbstream# xbstream-x-C 0000_incr/

0006_incr/0006_incr.xbstream# xbstream-x-C 0006_incr/ < 0006_incr/0006_incr.xbstream# innobackupex-apply-log-- redo-only-- incremental / backup/remote/-- incremental-dir=/backup/remote/0006_incr/

# innobackupex-- copy-back / backup/remote/

For the above about how to back up and restore the mysql database, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.