MySQL backup and recovery under Linux

Reasons for MySQL Backup

1. Disaster Recovery 2. Audit 3. Test 1234512345

Backup type for MySQL

1. Based on the server's online status: Hot spare: The server is in a running state cold: The server goes out of state Win Bei: The server is in a semi-offline state and can read only, but cannot write 2. Depending on the data set backed up: Full backup: Back up the entire database part backup: Back up a table 3. Depending on the interface at the time of backup: physical backup: Copy the database file directly from the disk logical backup: Extract the data from the database and back it up as a text file 4. Backup based on data volume: Full backup: Backup data incremental backup from database creation to date: Backup data differential backup from the end of the last full backup to date: 1234567891011121314151612345678910111213141516

MySQL Backup object

Data + profile + code (stored procedure, etc.) +os related configuration files (such as crontab configuration plan and scripts) 11

Data backup and recovery using the Mysqldump tool

 1. mysqldump command format     a. Back up multiple or one database     mysqldump [option ] --database db_name1 db_name2...    b. Backing up a table in a database      Mysqldump [option] db_anme table1 tables2 ...    c. Backing up all databases      mysqldump [option] --alldatabases 2. option    -u  user: Specifies the user's identity for the backup     -p password: Specify the user's password     -h  Host: Specifies the backed up database     --local-all-tables: Requests to lock all backed up tables     -- Local-tables: Back up that watch, lock that watch.     --single-transaction: Capable of hot provisioning of INNODB storage engines. (infrequently used)     --events: Backup Event Scheduler     --routines: Backup stored procedures and storage functions      --triggers: Backup trigger     --flush-log: Before backup, request to lock scroll log  3.  common backup format      mysqldump -uroot -p -h --database|db_name|--all-databases db_name|table --local-tables --flush-log 4.  Restore Backup      a. Closing the sql_log_bin    b.mysql<source /path/*.sql   of the current session     C. Opening the binary file log 1234567891011121314151617181920212223242526272829303112345678910111213141516171819202122232425262728293031

mysqldump backup test

Back Up Database list of tables in the WordPress library mysql> show tables;+-----------------------+| 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) 1234567891011121314151617181920212212345678910111213141516171819202122

2. Back up the entire database
   650) this.width=650; "Src=" http://img.blog.csdn.net/20170719165044960?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>
   650) this.width=650;" Src= "http://img.blog.csdn.net/20170719165155360? watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast "alt=" here to write a picture describing "title=" "/>

4. Restore the database you just backed up
   650) this.width=650; "Src=" http://img.blog.csdn.net/20170719165605826?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>
   650) this.width=650; "Src=" http://img.blog.csdn.net/20170719165644595?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

Backing up using the Xtrabackup tool

 1.  Download Install xtrabackup     [[email protected] ~]# 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     [[email protected] ~]#  wget http://dl.fedoraproject.org/pub/epel/6/x86_64//libev-4.03-3.el6.x86_64.rpm      [[email protected] ~]# yum install libev-4.03-3.el6.x86_64.rpm       [[email protected] ~]# yum install --skip-broken  percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm  2.  files generated after backup     xtrabackup_ Checkpoints: Records information such as backup type, backup status, LSN (log sequence number), and so on.     xtrabackup--binary: The xtrabackup executable file used in the backup     xtrabackup_binlog_ Info: The current server is using the binary log file and the time position of the backup path this moment     xtrabackup_binlog_pos_innodb: But the former position  of the binary file    BACKUP_MY.CNF: 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/results: Completes ok!1212

test the full backup database
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720181300969?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720183811395?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720183953754?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

Test recovery

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

650) this.width=650; "Src=" http://img.blog.csdn.net/20170720190703621?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

Create a new file tell the original database to move all to the directory
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720191202318?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>
Recovery
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720191326439?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720191515471?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

Incremental backup

 format:     #innobackupex  --incremental /path/to/back_dir  --incremental-basedir= restore with that backup file Pseudo-base: Full backup of incremental backup consolidation path      prepare full backup first:          #innobackupex  --apply-log --redo-only    Full backup path only (base-dir )      in preparation for the first incremental backup:         #innobackupex  -- apply-log --redo-only  base-dir --incremental-dir=one_base_dir     In preparation for the second incremental backup:         #innobackupex  --apply-log --redo-only   BASE-DIR --INCREMENTAL-DIR=TWO_BASE_DIR       &NBSP, ....        &NBSP, ....       . Recovery:    >innobackupex --copy-back  base-dir12345678910111213141234567891011121314 

A. First full backup
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720192131320?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

Operational Database Add data
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720193529398?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

incremental backup for the first time
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720194459251?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720194544060?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

prepare a full backup
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720194813304?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

prepare the first incremental backup file
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720195037020?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

Recovery
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720195442263?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

Results
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720200308191?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>
650) this.width=650; "Src=" http://img.blog.csdn.net/20170720200333510?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "/>

Xtrabackup Import and export of tables

When you export a table, you need to enable the Innodb_file_per_table option for the server. When you import a table, you need to have the server-enabled innodb_file_per_table and Innodb_expand_import options export table Innobackupex--apply-log--export/path/base_dir This    The command creates a. Exp end file for each InnoDB table space, which can be imported into another Server import table: 1, creating a table with the same structure on the server. 2, and then delete the tablespace for this table: ALTER TABLE table discard tablespace; 3, copy the. exp file to the database server's data directory, and then make the command: ALTER TABLE import t Ablespace;

MySQL backup and recovery under Linux