Restore MySQL database from percona backup and binary logs

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)