warning The transaction log file is corrupted.

最新推荐文章于 2024-03-28 14:17:28 发布

「已注销」

最新推荐文章于 2024-03-28 14:17:28 发布

阅读量1.7k

点赞数

分类专栏： Mysql故障排除

本文链接：https://blog.csdn.net/u010098331/article/details/50907063

版权

Mysql故障排除专栏收录该内容

53 篇文章 1 订阅

订阅专栏

Remark
Even if appealing using compress option has a constraint when preparing the backup, as clearly stated by Percona:

Before you can prepare the backup you’ll need to uncompress all the files with qpress.

The error message you get is :

xtrabackup: ########################################################
xtrabackup: # !!WARNING!!                                          #
xtrabackup: # The transaction log file is corrupted.               #
xtrabackup: # The log was not applied to the intended LSN!         #
xtrabackup: ########################################################

So I preferred not to 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

参考：

MySQL Replication with Global Transaction Identifiers (GTID) hands-on

Preamble

My initial idea was more to test MySQL Replication switchover and failover but had a presentation of MySQL 5.6 new replication features and realized that I have never setup such environment myself. So decided to give a try and use Percona backup tool (XtraBackup) and classic mysqldump utility to create from scratch a slave instance.

Blog post has been done using MySQL 5.6.11 (binary release i.e. Linux – Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive), Oracle Enterprise Linux 6.4 64 bits and Percona XtraBackup 2.1.3. In below server1.domain.com is my master server and server2.domain.com the slave one. They are both virtual machine using non routable IP adresses.

Replication with GTID prerequisites

For better segregation I have decided to create a MySQL Replication dedicated account with minimum rights (using root would not be a good idea):

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repadmin'@'%' IDENTIFIED BY 'secure_password';
Query OK, 0 rows affected (0.04 sec)

And create a test database and table:

mysql> CREATE DATABASE replicationdb CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 sec)
 
mysql> use replicationdb
Database changed
 
mysql> CREATE TABLE test1(val int, descr varchar(50));
Query OK, 0 rows affected (0.23 sec)

Table I load with something like:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS fill_test1;
 
CREATE PROCEDURE fill_test1()
BEGIN
  DECLARE count INT DEFAULT 1;
 
  WHILE count <= 20 DO
    INSERT INTO test1 VALUES(count,count);
    SET count=count+1;
  END WHILE;
END;
$$
 
DELIMITER ;

You can check master status and GTID position with:

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| 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 binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1730 |
| mysql-bin.000002 |       941 |
+------------------+-----------+
2 rows in set (0.00 sec)
 
mysql> SHOW BINLOG 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' |
| mysql-bin.000002 | 739 | Query          |         1 |         807 | BEGIN                                                             |
| mysql-bin.000002 | 807 | Table_map      |         1 |         867 | table_id: 153 (replicationdb.test1)                               |
| mysql-bin.000002 | 867 | Write_rows     |         1 |         910 | table_id: 153 flags: STMT_END_F                                   |
| mysql-bin.000002 | 910 | Xid            |         1 |         941 | COMMIT /* xid=1867 */                                             |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
17 rows in set (0.00 sec)

The typical variables to setup for replication are (when using the new GTID functionality), I use non-default binlog_format = row as MySQL 5.6 has apparently been drastically improved for this most used replication format (as Oracle say). In below whether you are on server1 or server2 you must adapt server_id and report_host variables:

log-bin = /mysql/logs/mysql01/mysql-bin
server-id = 1 | 2
relay_log =  /mysql/logs/mysql01/relay-bin
binlog_format = row
gtid_mode = on
log_slave_updates = true
enforce_gtid_consistency = true
master_info_repository = table
relay_log_info_repository = table
sync_master_info = 1 # Never do this on a production server, default value = 10000
master-verify-checksum = on
slave-sql-verify-checksum = on
report_host = server1.domain.com | server2.domain.com
report_port = 3326

To ease testing I’m adding the three alias in profile of my mysql Linux account:

alias start_mysql01='cd /mysql/software/mysql01/; ./bin/mysqld_safe --defaults-file=/mysql/software/mysql01/conf/my.cnf &'
alias stop_mysql01="/mysql/software/mysql01/bin/mysqladmin --defaults-file=/mysql/software/mysql01/conf/my.cnf --user=root --password=`cat ~mysql/.root_password` shutdown"
alias mysql01='/mysql/software/mysql01/bin/mysql --defaults-file=/mysql/software/mysql01/conf/my.cnf --user=root --password=`cat ~mysql/.root_password`'

The .root_password file is in home directory of mysql Linux account:

[mysql@server1 ~]$ ll .root_password
-r-------- 1 mysql dba 16 May 31 17:11 .root_password

As a reminder I personally use the below MySQL directory naming convention:

Directory	Used for
/mysql/data01/mysql01	Strore MyISAM and InnoDB files, dataxx directories can also be created to spread I/O
/mysql/dump/mysql01	All log files (slow log, error log, general log, …)
/mysql/logs/mysql01	All binary logs (log-bin, relay_log)
/mysql/software/mysql01	MySQL binaries (the my.cnf file is then stored in a conf subdirectory, as well as socket and pid files)

This MySQL directories naming convention should allow you to have multiple MySQL instance running on same server (mysql01, mysql02 and so on). Please note it is slightly different from what has been nicely presented by George Trujillo in hisInstalling MySQL 5.1 on Solaris 10 using MOCA post. MOCA stands for MySQL Optimal Configuration Architecture (MOCA) and I like this name…