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

Reasons for MySQL backup

1. Disaster recovery 2. Audit 3. Test 1234512345

Backup type of mysql

1. According to the online status of the server: hot backup: server in running state cold backup: server out stop state warm standby: server in semi-offline state, can only read, but can not write 2. According to the backup dataset: full backup: backup of the entire database part backup: backup of a table 3. According to the backup interface: physical backup: directly copy the database file from the disk logical backup: extract the data from the database and back up into a text file 4. Backup according to the amount of data: full backup: backup from the creation of the database to the current data incremental backup: backup from the end of the last full backup to the end of the last data differential backup: 12345678910111213141516

MySQL backup object

Data + configuration files + code (stored procedures, etc.) + os-related configuration files (such as crontab configuration plans and scripts) 11

Using mysqldump tools for data backup and recovery

1. Mysqldump command format a. Back up multiple or one database mysqldump [option]-- database db_name1 db_name2... b. Back up a table in a database mysqldump [option] db_anme table1 tables2... c. Backup all databases mysqldump [option]-- alldatabases 2. Option-u user: specify which user to backup-p password: specify the user's password-h host: specify the backup database-- local-all-tables: request to lock all backup tables-- local-tables: backup that table lock that table-- single-transaction: can implement hot backup for InnoDB storage engine. (rarely used)-- events: backup event Scheduler-- routines: backup stored procedures and stored functions-- triggers: backup trigger-- flush-log: scrolling log 3 before backup, after request to lock. Common backup format mysqldump-uroot-p-h-- database | db_name |-- all-databases db_name | table-- local-tables-- flush-log 4. Restore backup a. Close the sql_log_bin b.mysql show tables of the current session +-- + | Tables_in_wordpress | +-- + | wp_commentmeta | | wp_comments | | wp_links | | wp_options | | wp_postmeta | | wp_posts | | wp_term_relationships | | wp_term_ | Taxonomy | | wp_termmeta | | wp_terms | | wp_usermeta | | wp_users | +-- + 12 rows in set (0.00 sec) 1234567891011121314151617181920212345678910111213141516171213141516171819202122

Back up the entire database

Restore the database you just backed up

Use the xtrabackup tool for backup

1. Download and install xtrabackup [root@www ~] # wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm [root@www ~] # wget http://dl.fedoraproject.org/pub/epel/6/x86_64//libev-4.03-3.el6.x86_64.rpm [root @ www ~] # yum install libev-4.03-3.el6.x86_64.rpm [root@www ~] # yum install-skip-broken percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm 2. File xtrabackup_checkpoints generated after backup: record backup type, backup status, lsn (log serial number) and other information. Xtrabackup--binary: the executable file of xtrabackup used in the backup xtrabackup_binlog_info: the binary log file being used by the current server and the time location of the backup channel at this moment xtrabackup_binlog_pos_innodb: the previous position backup_my.cnf of the binary file: the configuration option information used by the backup command. 12345678910111213141234567891011121314

1. Full backup and recovery

Format: > innobackupex-- user=-- password=-- apply-log / path/to/back_dir/ result: completes okstore 1212

Test the full backup database

Test recovery

Format: > innobackupex-user=-password=-copy-back / path/to/back_dir/1212

To create a new file, move all the original databases to the directory.

Restore

Incremental backup

Format: # innobackupex-- incremental / path/to/back_dir-- incremental-basedir= restore on the pseudo basis of that backup file: integrate incremental backup into full backup and prepare full backup first: # innobackupex-- apply-log-- redo-only full backup path only (base-dir) before preparing the first incremental backup: # innobackupex-- apply-log-- redo-only base-dir -- incremental-dir=one_base_dir is preparing for a second incremental backup: # innobackupex-- apply-log-- redo-only base-dir-- incremental-dir=two_base_dir. .. . Restore: > innobackupex-- copy-back base-dir12345678910111213141234567891011121314

a. First full backup

Manipulate the database to add data

First incremental backup

Prepare for full backup

Prepare for the first incremental backup of files

Restore

Result

Xtrabackup imports and exports tables

When you export the table, you need to enable the server's innodb_file_per_table option. When importing tables, you need to have the server-enabled innodb_file_per_table and innodb_expand_import options to export the table innobackupex-- apply-log-- export / path/base_dir this command creates a file ending with .exp for each innodb tablespace, which can be imported into other server import tables: 1, create a table with the same structure on the server. 2, then delete the tablespace of this table: alter table table discard tablespace; 3, copy the .exp file to the data directory of the database server, and then make the following command: alter table table import tablespace

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.