Saturday, September 15, 2018

MySQL backup and replication tools

MySQL backup and replication tools


Percona XtraBackup

Percona MySQL-AutoXtraBackup


# mysqlbinlog --result-file=test.log /var/log/mysql/mysql-bin.000001

Note: You may edit the result file and delete any statements you don't want to execut. Then, do mysql -u root -p < test.log

# mysqlbinlog --result-file=test.log --base64-output=DECODE-ROWS /var/log/mysql/mysql-bin.000001
# mysqlbinlog --result-file=test.log -v /var/log/mysql/mysql-bin.000001
# mysqlbinlog --result-file=test.log -vv /var/log/mysql/mysql-bin.000001

Note: Do more research on global transaction identifier (GTID) vs binary log position

Get binary log from the remote server:

# mysqlbinlog --read-from-remote-server --host= -p mysqld-bin.000001


The output of mysqlbinlog can be used as the input of the mysql client to redo the statements contained in the binary log:

# cd /var/log/mysql
# mysqlbinlog mysql-bin.000001 | mysql -u root -p


Pingcap syncer:

No comments: