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

Let's start with a brief introduction to this tool: innobackupex

Innobackupex is more powerful than xtarbackup, it integrates xtrabackup and other functions, it can not only full backup / restore, but also time-based incremental backup and recovery.

Principle of innobackupex backup

Innobackupex first calls xtrabackup to back up innodb data files, and when xtrabackup is complete, innobackupex looks at the file xtrabackup_suspended, and then executes "FLUSH TABLES WITH READ LOCK" to back up other files

Innobackupex recovery principle

Innobackupex first reads the my.cnf, checks that the directory corresponding to the variable (datadir,innodb_data_home_dir,innodb_data_file_path,innodb_log_group_home_dir) exists, determines that the relevant directory exists, then copy myisam the table and index, and then in the copy innodb table, index and log.

Introduce the environment:

MySQL:5.6.19

Installation path: / u01/mysql

Data file: / u01/mysql/data

Backup source: / u02/backup

I am a different machine recovery, the same as this machine operation.

1. Full backup

Steps:

. / innobackupex-- user=root-- password=root-- host=172.17.210.112-- parallel=4-- throttle=400-- stream=tar / mysqlbak/innobackupex 2 > / mysqlbak/innobackupex/bak.log 1 > / mysqlbak/innobackupex/fullbak.tar

Comment on the commonly used parameters.

-- user name of user=root backup operation, which is usually the root user

-- password=root123 password

-- host=172.17.210.112 host ip, which can not be added locally

-- parallel=4-- the number of throttle=400 in parallel. Select the appropriate one according to the host configuration. The default is 1, and multiple can speed up the backup.

-- stream=tar compression type. Tar format is selected here, which can be added or not. With the addition of the file, it is smaller and has already been packed at the time of backup.

/ mysqlbak/innobackupex backup the directory where it is stored

2 > / mysqlbak/innobackupex/bak.log backup log to redirect the output information during backup to bak.log

1 > / name of the compressed mysqlbak/innobackupex/fullbak.tar backup file

Give an uncompressed complete set:

. / innobackupex-- user=root-- password=root-- host=172.17.210.112-- parallel=4-- throttle=400 / mysqlbak/innobackupex 2 > / mysqlbak/innobackupex/bak.log 1 > / mysqlbak/innobackupex/

Check the log information, and it will appear

141011 09:44:02 innobackupex: Executing FLUSH ENGINE LOGS...

141011 09:44:02 innobackupex: Waiting for log copying to finish

Xtrabackup: The latest check point (for incremental): '14275993522'

Xtrabackup: Stopping log copying thread.

Log scanned up to (14275993522)

Xtrabackup: Creating suspend file'/ tmp/xtrabackup_log_copied' with pid '19659'

141011 09:44:03 innobackupex: All tables unlocked

141011 09:44:03 innobackupex: Waiting for ibbackup (pid=19659) to finish

Xtrabackup: Transaction log of lsn (14275990028) to (14275993522) was copied.

Innobackupex: Backup created in directory'/ mysqlbak/innobackupex'

141011 09:44:04 innobackupex: Connection to database server closed

Innobackupex: You must use-I (--ignore-zeros) option for extraction of the tar stream.

141011 09:44:04 innobackupex: completed OK!

Indicates that the backup was successful.

2. Full recovery

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

[root@newbidb data] # innobackupex-- user=root / data/backup/

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

Prints "completed OK!".

Original data directory is not empty! At / usr/bin/innobackupex line 568.

The first step of recovery: apply the log.

[root@newbidb bin] #. / innobackupex-- user=root-- password=root1-- defaults-file=/etc/my.cnf-- apply-log / u02/backup/

Restore step 2: copy the file.

[root@newbidb bin] #. / innobackupex-- user=root-- password=root1-- defaults-file=/etc/my.cnf-- copy-back / u02/backup/

Comment on the commonly used parameters.

-- defaults-file=/etc/my.cnf recovery uses my.cnf files to restore the files to the location specified by my.cnf.

-- apply-log. This is the log generated during backup.

-- copy-back this is the backup source and the unzipped backup files.

The recovery takes a little time, and the following message indicates that the recovery is successful.

Innobackupex: Starting to copy InnoDB system tablespace

Innobackupex: in'/ u02Backup`

Innobackupex: back to original InnoDB data directory'/ u01amp MySQL _

Innobackupex: Copying'/ u02max backupUniverse ibdata1'to'/ u01Universe mysqlAccorde ibdata1'

Innobackupex: Starting to copy InnoDB undo tablespaces

Innobackupex: in'/ u02Backup`

Innobackupex: back to'/ u01amp MySQL _

Innobackupex: Starting to copy InnoDB log files

Innobackupex: in'/ u02Backup`

Innobackupex: back to original InnoDB log directory'/ u01amp MySQL _

Innobackupex: Copying'/ u02 Universe backupUniverse iblem logfile1'to'/ u01 UniUniverse MySQL Greater dataAccording to iblem logfile1'

Innobackupex: Copying'/ u02 Universe backupUniverse iblem logfile0' / u01Uniplex mysqlAccording to ibspur logfile0'

Innobackupex: Finished copying back files.

Restore step 3: modify file permissions.

Cd to data directory

Chown-R mysql.mysql data/

Possible errors:

1. Initialize mysql when the following error occurs

[root@newbidb support-files] #. / mysql.server start

Starting MySQL...The server quit without updating PID file (/ u01/mysql/data/newbidb.pid). [FAILED]

[root@newbidb script] # / mysql_install_db-- basedir=/u01/mysql-- no-defaults-- skip-name-resolve-- user=mysql-- datadir=/u01/mysql/data

2. Permissions: the root user should be restored, but the MySQL user is required to access the MySQL.

[root@newbidb support-files] #. / mysql.server restart

MySQL server PID file could not be found! [FAILED]

Starting MySQL.The server quit without updating PID file (/ u01/mysql/data/newbidb.pid). [FAILED]

3. When connecting to MySQL

[root@newbidb bin] #. / mysql-uroot-p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (2)

Without this file, touch mysql.sock and modify file permissions.

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.