innobackupex can not recover whole database

Hi all, Thank you all for support these great tool to hot backup the mysql. Then I met a problem. I am using innobackupex tools to hot backup mysql database, it’s very well in test environment. But it fail on the prod environment. The story is: I stop the mysql master server for do some change on my.cnf file. but the replication is doing something at the same time. Then it give me some warning: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: update xxxx(table name) …(sql command) I don’t care about it because I can do hot backup again after that. For safely master restart, I do ‘flush tables with read lock’, then stop the server. change the cnf file and start it. OK, I do the hot backup using command: innobackupex --user=root --password=xxxx --defaults-file=/etc/mysql/my.cnf /home/user/xtratest --no-timestamp run successful. then I recover them to another server to restore the database for mysql slave. the command is : innobackupex --user=root --password=xxxx --defaults-file=/etc/mysql/my.cnf --apply-log /home/user/xtratest innobackupex --user=root --password=xxxx --defaults-file=/etc/mysql/my.cnf --copy-back /home/user/xtratest start server;change master;start slave; All of them are so right without any error messages. Then I check the slave mysql server missing a database, which just have an empty table. missing a database, which just have some view tables missing two users. missing 10 view tables. There are two tables , I means master.A1 and slave.A1, master.B1 and slave.B1 which of them have almost 1w+ records different. The total recordes of them are only 10w+ records/per table. Can not sync by replication. What happen? Please help me about it! Thansk a lot!

Hi all,

Thank you all for support these great tool to hot backup the mysql.

Then I met a problem.

I am using innobackupex tools to hot backup mysql database, it’s very well in test environment.

But it fail on the prod environment.

The story is: I stop the mysql master server for do some change on my.cnf file.

but the replication is doing something at the same time.

Then it give me some warning:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: update xxxx(table name) …(sql command)

I don’t care about it because I can do hot backup again after that.

For safely master restart, I do ‘flush tables with read lock’, then stop the server. change the cnf file and start it.

OK, I do the hot backup using command:

innobackupex --user=root --password=xxxx --defaults-file=/etc/mysql/my.cnf /home/user/xtratest --no-timestamp

run successful.

then I recover them to another server to restore the database for mysql slave. the command is :

innobackupex --user=root --password=xxxx --defaults-file=/etc/mysql/my.cnf --apply-log /home/user/xtratest

innobackupex --user=root --password=xxxx --defaults-file=/etc/mysql/my.cnf --copy-back /home/user/xtratest

start server;change master;start slave; All of them are so right without any error messages.

Then I check the slave mysql server

  1. missing a database, which just have an empty table.
  2. missing a database, which just have some view tables
  3. missing two users. missing 10 view tables.
    4.There are two tables , I means master.A1 and slave.A1, master.B1 and slave.B1 which of them have almost 1w+ records different. The total recordes of them are only 10w+ records/per table.

Can not sync by replication.

What happen? Please help me about it!

Thansk a lot!