Remark
Even if appealing using compress option has a constraint when preparing the backup, as clearly stated by Percona:
Before can prepare the backup you'll need to uncompress all of the files with qpress.
The error message you are:
Xtrabackup: ########################################################
xtrabackup: #!! warning!! #
Xtrabackup: # The transaction log file is corrupted. #
Xtrabackup: # The log wasn't applied to the intended lsn! #
Xtrabackup: ########################################################
So I preferred the compress it to avoid fighting in installing qpress ...
Then, archive and transfer the file to your slave server (Server2 in my case):
[Mysql@server1 tmp]$ tar cvf backup.tar 2013-06-03_15-30-08
[mysql@server1 tmp]$ SCP Backup.tar server2:/tmp
[ Mysql@server2 tmp]$ tar xvf backup.tar
To restore it use:
[mysql@server1 ~] Innobackupex--user=root--password=secure_password--defaults-file=/mysql/software/mysql01/conf/my.cnf-- copy-back/tmp/2013-06-03_15-30-08 |
Reference:
MySQL Replication with Global Transaction Identifiers (gtid) hands-on Table of Contents Preamble Replication with Gtid prerequisites using xtrabackup using mysqldump semi-synchronous Replication i to remove R Eplication References
Preamble
My initial idea is more to test MySQL Replication switchover and failover but had a presentation of MySQL 5.6 new replica tion features and realized that I have never setup such environment. So decided to give a try and use Percona Backup tool (Xtrabackup) and classic mysqldump utility to create from scratch a s Lave instance.
Blog Post has been do using MySQL 5.6.11 (binary release i.e. Linux–generic glibc) (2.5, x86), 64-bit TAR Archive), Oracle Enterprise Linux 6.4, bits and Percona xtrabackup 2.1.3. In below server1.domain.com are my master server and server2.domain.com the slave one. They are both virtual machine using non IP routable.
Replication with Gtid Prerequisites
For better segregation I have decided to create a MySQL Replication dedicated account with minimum rights (using root Woul D not being a good idea):
GRANT on to ' repadmin ' @ '%' identified by ' secure_password ';
Query OK, 0 rows affected (0.04 sec) |
and create a test database and table:
CREATE DATABASE SET COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 sec) use
replicationdb
Database changed
C13>createTABLEintvarchar(m));
Query OK, 0 rows affected (0.23 sec) |
Table I load with something like:
DELIMITER $$
DROPPROCEDUREEXISTS fill_test1;
CREATE PROCEDURE fill_test1 ()
BEGIN
DECLAREINTDEFAULT 1;
Todo
INSERT into VALUES (Count,count);
SET count=count+1;
End While;
End;
$$
DELIMITER; |
You can check the master status and Gtid position with:
Mysql> ShowMasterStatus; +------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
Executed_gtid_set | +------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 191 | | |
770d3753-c6e4-11e2-8e78-080027d93e15:1-6 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 Row in Set(0.00 sec) mysql> Show binaryLogs +------------------+-----------+
| Log_name |
File_size | +------------------+-----------+
| mysql-bin.000001 | 1730 | | mysql-bin.000002 |
941 | +------------------+-----------+ 2 rows in Set(0.00 sec) mysql> ShowBinlog EVENTS in' mysql-bin.000002 '; +------------------+-----+----------------+-----------+-------------+------------------------------------------ -------------------------+
| Log_name | Pos | Event_type | server_id | End_log_pos |
Info | +------------------+-----+----------------+-----------+-------------+------------------------------------------ -------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver:5.6.11-log, Binlog ver:4 | | mysql-bin.000002 | 120 | Previous_gtids | 1 | 191 | 770d3753-c6e4-11e2-8e78-080027d93e15:1-6 | | mysql-bin.000002 | 191 | Gtid | 1 | 239 |SET@@ Session. gtid_next= ' 770d3753-c6e4-11e2-8e78-080027d93e15:7 ' | | mysql-bin.000002 | 239 | Query | 1 | 307 |BEGIN|
| mysql-bin.000002 | 307 | Table_map | 1 | 367 | table_id:153 (replicationdb.test1) | | mysql-bin.000002 | 367 | Write_rows | 1 | 410 | table_id:153 Flags:stmt_end_f | | mysql-bin.000002 | 410 | Xid | 1 | 441 |COMMIT/* xid=374 * | | mysql-bin.000002 | 441 | Gtid | 1 | 489 |SET@@ Session. gtid_next= ' 770d3753-c6e4-11e2-8e78-080027d93e15:8 ' | | mysql-bin.000002 | 489 | Query | 1 | 557 |BEGIN|
| mysql-bin.000002 | 557 | Table_map | 1 | 617 | table_id:153 (replicationdb.test1) | | mysql-bin.000002 | 617 | Write_rows | 1 | 660 | table_id:153 Flags:stmt_end_f | | mysql-bin.000002 | 660 | Xid | 1 | 691 |COMMIT/* xid=379 * | | mysql-bin.000002 | 691 | Gtid | 1 | 739 |SET@@ Session. gtid_next= ' 770d3753-c6e4-11e2-8e78-080027d93e15:9 ' | |