Restore MySQL database
from percona backup and binary logs
Step 1: Copy the backup files and binary log from backup
device
Here the backup files folder is /home/mysqlbackup/2014-07-12_20_00_02 and the binary log file is log-bin.000071, the backup stop at the position 1039080535.
Step 2: Restore full backup from innobackup folders
Stop mysql service, backup old data folder and create a new
folder named as data.
Restore mysql database from innobackup files.
[root@localhost
mysql]# innobackupex --apply-log /home/mysqlbackup/2014-07-12_20-00-02
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003,
2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
140715 17:42:35
innobackupex: Starting ibbackup with command: xtrabackup_56
--defaults-file="/home/mysqlbackup/2014-07-12_20-00-02/backup-my.cnf" --defaults-group="mysqld" --prepare
--target-dir=/home/mysqlbackup/2014-07-12_20-00-02 --tmpdir=/tmp
xtrabackup_56 version 2.1.8 for MySQL server 5.6.15 Linux
(x86_64) (revision id: 733)
xtrabackup: cd to /home/mysqlbackup/2014-07-12_20-00-02
xtrabackup: This target seems to be not prepared yet.
…….
[notice (again)]
If you use binary
log and don't use any hack of group commit,
the binary log
position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 121618500768
140715 17:42:46
innobackupex: completed OK!
[root@localhost mysql]#innobackupex
--copy-back --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock /home/mysqlbackup/2014-07-12_20-00-02
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003,
2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
innobackupex: Starting to copy files in
'/home/mysqlbackup/2014-07-12_20-00-02'
innobackupex: back to original data directory
'/usr/local/mysql/data'
……
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/mysqlbackup/2014-07-12_20-00-02'
innobackupex: back to original InnoDB log directory
'/usr/local/mysql/data'
innobackupex: Copying
'/home/mysqlbackup/2014-07-12_20-00-02/ib_logfile1' to
'/usr/local/mysql/data/ib_logfile1'
innobackupex: Copying '/home/mysqlbackup/2014-07-12_20-00-02/ib_logfile0'
to '/usr/local/mysql/data/ib_logfile0'
innobackupex: Finished copying back files.
140715 18:28:13
innobackupex: completed OK!
[root@localhost mysql]#
Change the data folder own user and start mysql service
[root@localhost mysql]# chown -R mysql .
[root@localhost mysql]# chgrp -R mysql .
[root@localhost mysql]# service mysql start
Starting MySQL..................................... [
OK ]
[root@localhost mysql]#
Recovery data from
binlog
[root@localhost 2014-07-12_20-00-02]# cat
xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000071',
MASTER_LOG_POS=1039080535
[root@localhost 2014-07-12_20-00-02]# cd ..
[root@localhost mysqlbackup]# ls -lah
total 2.2G
drwxr-xr-x. 3 root
root 4.0K Jul 16 11:13 .
drwxr-xr-x. 11 root root 4.0K Jul 10 11:35 ..
-rw-r--r--. 1 root
root 130M Jul 16 11:14 1.sql
drwxr-xr-x. 12 root root 4.0K Jul 16 11:11
2014-07-12_20-00-02
-rw-r-----. 1 root
root 1.1G Jul 10 18:13 log-bin.000070
-rw-r-----. 1 root
root 1.1G Jul 15 17:03 log-bin.000071
[root@localhost mysqlbackup]# mysqlbinlog
--start-position=1039080535 log-bin.000071 | mysql -uroot -p
Enter password:
ERROR 1666 (HY000) at line 31: Cannot execute statement:
impossible to write to binary log since statement is in row format and
BINLOG_FORMAT = STATEMENT.
[root@localhost mysqlbackup]# mysqlbinlog -v
--start-position=1039080535 log-bin.000071 | mysql -uroot -p
Enter password:
ERROR 1666 (HY000) at line 31: Cannot execute statement:
impossible to write to binary log since statement is in row format and
BINLOG_FORMAT = STATEMENT.
[root@localhost mysqlbackup]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All
rights reserved.
Oracle is a registered trademark of Oracle Corporation
and/or its
affiliates. Other names may be trademarks of their
respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the
current input statement.
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value
|
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> set global binlog_format='row';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@localhost mysqlbackup]# mysqlbinlog -v --start-position=1039080535
log-bin.000071 | mysql -uroot -p
Enter password:
Set back the binlog_format
[root@localhost mysqlbackup]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All
rights reserved.
Oracle is a registered trademark of Oracle Corporation
and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the
current input statement.
mysql> set global binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)
