Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to backup mysql". In the daily operation, I believe many people have doubts about how to backup mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to backup mysql". Next, please follow the editor to study!

MySQL 5.5.47

Backup path / backup/

Install apt-get install percona-xtrabackup11 1, full backup root@iZu1dc59z8tZ:/var/lib# innobackupex-- defaults-file=/etc/mysql/my.cnf-- user=root-- password=123123 / backup/ InnoDB Backup Utility v1.5.1Muxtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources: http://www.percona.com/xb/ubuntu160217 11:17:53 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf Mysql_read_default_group=xtrabackup' as 'root' (using password: YES). 160217 11:17:53 innobackupex: Connected to MySQL server160217 11:17:53 innobackupex: Executing a version check against the server...160217 11:17:53 innobackupex: Done.IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!" .innobackupex: Using mysql server version 5.5.47-0ubuntu0.14.04.1innobackupex: Created backup directory / backup/2016-02-17011-17-53160217 11:17:53 innobackupex: Starting ibbackup with command: xtrabackup_55-- defaults-file= "/ etc/mysql/my.cnf"-- defaults-group= "mysqld"-- backup-- suspend-at-end-- target-dir=/backup/2016-02 -17 September 11-17-53-- tmpdir=/tmpinnobackupex: Waiting for ibbackup (pid=30486) to suspendinnobackupex: Suspend file'/ backup/2016-02-17 11-17-53/xtrabackup_suspended_2'xtrabackup_55 version 2.1.8 for Percona Server 5.5.35 Linux (x86 Linux 64) (revision id: undefined) xtrabackup: uses posix_fadvise (). Xtrabackup: cd to / var/lib/mysqlxtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir =. / xtrabackup: innodb _ data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir =. / xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880 > > log scanned up to (3233814) [01] Copying. / ibdata1 to / backup/2016-02-17 11-53/ibdata1 [01] .done > > log scanned up to (3233814) xtrabackup: Creating suspend file'/ backup/2016-02-17 11-17-53 / xtrabackup_suspended_2' with pid '30487 / 160217 11:17:55 innobackupex: Continuing after ibbackup has suspended160217 11:17:55 innobackupex: Starting to lock all tables...160217 11:17:55 innobackupex: All tables locked and flushed to disk160217 11:17:55 innobackupex: Starting to backup non-InnoDB tables and filesinnobackupex: in subdirectories of' / var/lib/mysql'innobackupex: Backing up files'/ var/lib/mysql/phpmyadmin/*. {frm Isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(37 files) > > log scanned up to (3233814) innobackupex: Backing up files'/ var/lib/mysql/mysql/*. {frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(72 files) innobackupex: Backing up files'/ var/lib/mysql/performance_schema/*. {frm,isl,MYD,MYI,MAD,MAI,MRG,TRG TRN,ARM,ARZ,CSM,CSV,opt,par}'(18 files) innobackupex: Backing up files'/ var/lib/mysql/blog/*. {frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt Par}'(29 files) 160217 11:17:56 innobackupex: Finished backing up non-InnoDB tables and files160217 11:17:56 innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '3233814'xtrabackup: Stopping log copying thread.. > > log scanned up to (3233814) xtrabackup: Creating suspend file' / backup/2016-02-17 October 11-17-53 Universe xtrabackupposed logically copided 'with pid' 30487'xtrabackup: Transaction log of lsn (3233814) to (3233814) was copied.160217 11:17 57 innobackupex: All tables unlockedinnobackupex: Backup created in directory'/ backup/2016-02-17-17-53-160217 11:17:57 innobackupex: Connection to database server closed160217 Okun 1234567891011121314151617181920212232425262829303132333435363739404142445464748495015354555657585960162656768121314151618192021223242627282930313334353638394041444464748495015354555557585960616366676812131415161719202122324262728303133343536383940414444647484949501535455565758596061636667681213141516171920212232426262829303133343536383940414444647484949501525354555657585960616466676812131415161618192021223242626282830313334353638394041424454647484849 525354555554545464748484950153545556575859606162666768121314151618192021223242626282930313334353638394041424454647484849501535455565758596061626667

Completed OK! Indicates that the backup was successful.

There are many other commonly used parameters.

-stream=tar compression type. Default is output to terminal. If saved as a file,

Innobackupex-- defaults-file=/etc/mysql/my.cnf-- user=root-- password=*** / backup/-- stream=tar > / backup/ `date + "% Y%m%d_%H%M%S" `.tar # or gzip compression innobackupex-- defaults-file=/etc/mysql/my.cnf-- user=root-- password=*** / backup/-- stream=tar | gzip > / backup/ `date + "% Y%m%d_%H%M%S" `. Tar.gz123123

-the number of parallel=4-throttle=400 parallelism, which is selected according to the host configuration. The default is 1, and multiple can speed up the backup.

2. Full recovery

To restore backup files, make sure that the mysql datadir file is empty, otherwise an error will be reported.

Test recovery service mysql stop # stop MySQLmv / var/lib/mysql/ / var/lib/mysql_bak # backup datadirmkdir / var/lib/mysql123123 recovery of the original mysql step 1: apply log

If compression is added, it needs to be decompressed.

Mkdir / backup/20160217_114521tar-ixvf / backup/20160217_114521.tar.gz-C / backup/20160217_1145211212innobackupex-- defaults-file=/etc/mysql/my.cnf-- apply-log / backup/20160217_11452111 restore step 2: copy files to MySQL datadirinnobackupex-- defaults-file=/etc/mysql/my.cnf-- copy-back / backup/20160217_11452111 restore step 3: modify file permissions and start mysql service. Chown mysql: / var/lib/mysql-Rservice mysql start1212 III. Incremental backup

Incremental backup needs to be based on full, so suppose we already have a full (/ backup/20160217_114521) and do incremental backup on the basis of the full table.

Insert a piece of data into the database for the first time and make an incremental backup on the whole

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123123-incremental-basedir=/backup/20160217_114521-incremental / backup/11

A complete directory of incremental-basedir=

-Directory of incremental= incremental backup

Insert a piece of data into the database for the second time and make an incremental backup on the first incremental backup

Innobackupex-- defaults-file=/etc/mysql/my.cnf-- user=root-- password=123123-- incremental-basedir=/backup/2016-02-17 incremental 12-15-30-- incremental / backup/11incremental-basedir= in the directory of the first incremental backup-- directory of the incremental= incremental backup 1212

In the backup directory, there is a file xtrabackup_checkpoints that records the backup information. The complete information is as follows:

Root@iZu1dc59z8tZ:/backup# cat 20160217_114521/xtrabackup_checkpoints backup_type = full-preparedfrom_lsn = 0to_lsn = 3233814last_lsn = 3233814compact = 0root@iZu1dc59z8tZ:/backup# cat 2016-02-17 years 12-15-30/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 3233814to_lsn = 3234430last_lsn = 3234430compact = 0root@iZu1dc59z8tZ:/backup# cat 2016-02-17 years 12-1/xtrabackup_checkpoints 2016-02-17 years 12-15-30 / 2016- 02-17 12-31 / root@iZu1dc59z8tZ:/backup# cat 2016-02-17 12-17-31/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 3234430to_lsn = 3234537last_lsn = 3234537compact = 012345678910111213141516171819201234567891011121314151617181920

As you can see from the above, the from_lsn of the incremental backup is exactly the same as the full to_lsn.

4. Incremental recovery / / Test recovery service mysql stop # stop MySQLmv / var/lib/mysql/ / var/lib/mysql_bak # backup datadirmkdir / var/lib/mysql12341234 recovery of the original mysql step 1: apply all incremental logs 1, apply full log innobackupex-- defaults-file=/etc/mysql/my.cnf-- apply-log-- redo-only / backup/20160217_114521112 Apply incremental logs / / first incremental innobackupex-- defaults-file=/etc/mysql/my.cnf-- apply-log-- redo-only / backup/20160217_114521-- incremental-dir=/backup/2016-02-17 incremental 12-15-30 / 1212

……

/ / Last incremental innobackupex-- defaults-file=/etc/mysql/my.cnf-- apply-log / backup/20160217_114521-- incremental-dir=/backup/2016-02-17 December 12-17-31 recovery step 2: copy files to MySQL datadirinnobackupex-- defaults-file=/etc/mysql/my.cnf-- copy-back / backup/20160217_11452111 restore step 3: modify file permissions and start mysql service. Chown mysql: / var/lib/mysql-Rservice mysql start at this point, the study on "how to back up mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.