247 lines
10 KiB
Plaintext
247 lines
10 KiB
Plaintext
# ==== Purpose ====
|
|
#
|
|
# Verify that mysqlbinlog produces the correct output for combinations
|
|
# of include-gtids, exclude-gtids, skip-gtids,
|
|
# read-from-remote-server=X, and raw.
|
|
#
|
|
# Currently, the following combinations are implemented:
|
|
#
|
|
# - include-gtids
|
|
# - exclude-gtids
|
|
# - exclude-gtids + skip-gtids
|
|
# - skip-gtids
|
|
# - read-from-remote-server
|
|
# - read-from-remote-master=BINLOG-DUMP-GTID
|
|
# - read-from-remote-master=BINLOG-DUMP-NON-GTID
|
|
# - read-from-remote-server + raw
|
|
# - read-from-remote-master=BINLOG-DUMP-GTID + raw
|
|
# - read-from-remote-master=BINLOG-DUMP-NON-GTID + raw
|
|
# - include-gtids + read-from-remote-master=BINLOG-DUMP-GTID
|
|
# - include-gtids + read-from-remote-master=BINLOG-DUMP-NON-GTID
|
|
# - exclude-gtids + read-from-remote-maste=BINLOG-DUMP-GTID + raw
|
|
# - exclude-gtids + read-from-remote-master=BINLOG-DUMP-NON-GTID + raw
|
|
#
|
|
# TODO: implement all combinations and make it more uniform and
|
|
# maintainable, like:
|
|
#
|
|
# for opt1 in (include-gtids, exclude-gtids, ''):
|
|
# for opt2 in (skip-gtids, ''):
|
|
# for opt3 in (read-from-remote-server,
|
|
# read-from-remote-master=BINLOG-DUMP-GTID,
|
|
# read-from-remote-master=BINLOG-DUMP-NON-GTID,
|
|
# ''):
|
|
# for opt4 in (raw, ''):
|
|
# mysqlbinlog $opt1 $opt2 $opt3 $opt4
|
|
#
|
|
# ==== References ====
|
|
#
|
|
# WL#3584 Global Transaction Identifiers (GTIDs)
|
|
|
|
# Output from mysqlbinlog is replayed on the slave server.
|
|
--source include/master-slave.inc
|
|
--source include/have_binlog_format_mixed.inc
|
|
|
|
--connection slave
|
|
--source include/stop_slave.inc
|
|
|
|
--connection master
|
|
### Generate some data on the master ###
|
|
|
|
# example of a DDL
|
|
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) engine=Innodb;
|
|
|
|
# Single-statement transaction
|
|
SET @@SESSION.AUTOCOMMIT= ON;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
|
|
# Multi-statement transaction
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
COMMIT;
|
|
|
|
#
|
|
# Verification actions include
|
|
# restoring t1 from binlog and checking data consistency
|
|
#
|
|
|
|
--let $server_uuid= `SELECT @@GLOBAL.SERVER_UUID`
|
|
--let $datadir= `SELECT @@datadir`
|
|
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
|
|
|
|
--echo ==== FULL FILE ====
|
|
--exec $MYSQL_BINLOG $datadir/$binlog_file | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
|
|
--echo ==== --include-gtids ====
|
|
|
|
INSERT INTO t1 VALUES (NULL) /* 4 */;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
|
|
--exec $MYSQL_BINLOG --include-gtids=$server_uuid:4-6 $datadir/$binlog_file | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
|
|
--echo ==== --skip-gtids + --exclude-gtids ====
|
|
|
|
INSERT INTO t1 VALUES (NULL) /* 7 */;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
|
|
--exec $MYSQL_BINLOG --skip-gtids --exclude-gtids=$server_uuid:1-6 $datadir/$binlog_file | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
--echo ==== --exclude-gtids ====
|
|
|
|
INSERT INTO t1 VALUES (NULL) /* 9 */;
|
|
|
|
--exec $MYSQL_BINLOG --exclude-gtids=$server_uuid:1-8 $datadir/$binlog_file | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
--echo ==== --skip-gtids ====
|
|
|
|
FLUSH LOGS;
|
|
--let $server_uuid= `SELECT @@GLOBAL.SERVER_UUID`
|
|
--let $datadir= `SELECT @@datadir`
|
|
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
|
|
|
|
INSERT INTO t1 VALUES (NULL) /* 10 */;
|
|
|
|
--exec $MYSQL_BINLOG --skip-gtids $datadir/$binlog_file | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT
|
|
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
#
|
|
# In what follows we are going to check if a dump file created with
|
|
# --read-from-remote-master={BINLOG-DUMP-NON-GTID, BINLOG-DUMP-GTID}
|
|
# is equal to a dump file created with --read-from-remote-server or
|
|
# an empty option (i.e. without neiter --read-from-remote-master nor
|
|
# --read-from-remote-server).
|
|
#
|
|
# The --raw option is combined with the aforementioned options to
|
|
# create additional dump files.
|
|
#
|
|
# Finally we use the options --include-gtids and --exclude-gtids to
|
|
# also create additional files.
|
|
#
|
|
|
|
--echo ==== --read-from-remote-master ====
|
|
--connection master
|
|
FLUSH LOGS;
|
|
|
|
let $MYSQLD_DATADIR= `SELECT @@DATADIR`;
|
|
let $UUID= `SELECT @@SERVER_UUID`;
|
|
|
|
--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000001
|
|
|
|
# read-from-remote-server
|
|
--exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000002
|
|
|
|
# read-from-remote-server=BINLOG-DUMP-NON-GTID
|
|
--exec $MYSQL_BINLOG --read-from-remote-master=BINLOG-DUMP-NON-GTID --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000003
|
|
|
|
# read-from-remote-server=BINLOG-DUMP-GTID
|
|
--exec $MYSQL_BINLOG --read-from-remote-master=BINLOG-DUMP-GTID --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000004
|
|
|
|
# read-from-remote-server + raw
|
|
--exec $MYSQL_BINLOG --raw --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/
|
|
--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/tmp/$binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000005
|
|
|
|
# read-from-remote-server=BINLOG-DUMP-NON-GTID + raw
|
|
--exec $MYSQL_BINLOG --raw --read-from-remote-master=BINLOG-DUMP-NON-GTID --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/
|
|
--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/tmp/$binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000006
|
|
|
|
# read-from-remote-server=BINLOG-DUMP-GTID + raw
|
|
--exec $MYSQL_BINLOG --raw --read-from-remote-master=BINLOG-DUMP-GTID --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/
|
|
--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/tmp/$binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000007
|
|
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000002 $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000003 $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000004 $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000005 $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000006 $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000007 $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000002
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000003
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000004
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000005
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000006
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000007
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/$binlog_file
|
|
|
|
--echo ==== --read-from-remote-master with --exclude-gtids ====
|
|
|
|
--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file --exclude-gtids=$UUID:1-3 --result-file=$MYSQLTEST_VARDIR/tmp/output.000001
|
|
--exec $MYSQL_BINLOG --read-from-remote-master=BINLOG-DUMP-NON-GTID --exclude-gtids=$UUID:1-3 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000002
|
|
|
|
--exec $MYSQL_BINLOG --read-from-remote-master=BINLOG-DUMP-GTID --exclude-gtids=$UUID:1-3 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000003
|
|
--error 1
|
|
--exec $MYSQL_BINLOG --raw --read-from-remote-master=BINLOG-DUMP-NON-GTID --exclude-gtids=$UUID:1-3 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/
|
|
--exec $MYSQL_BINLOG --raw --read-from-remote-master=BINLOG-DUMP-GTID --exclude-gtids=$UUID:1-3 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/
|
|
--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/tmp/$binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000004
|
|
|
|
#
|
|
# Strange but filtering on master and on the client procudes different positions.
|
|
# For that reason, we do not compare all outputs.
|
|
#
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000001 $MYSQLTEST_VARDIR/tmp/output.000002
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000003 $MYSQLTEST_VARDIR/tmp/output.000004
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000002
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000003
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000004
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/$binlog_file
|
|
|
|
--echo ==== --read-from-remote-master with --include-gtids ====
|
|
|
|
--exec $MYSQL_BINLOG --read-from-remote-master=BINLOG-DUMP-NON-GTID --include-gtids=$UUID:1-3 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000001
|
|
--exec $MYSQL_BINLOG --read-from-remote-master=BINLOG-DUMP-GTID --include-gtids=$UUID:1-3 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/output.000002
|
|
--error 1
|
|
--exec $MYSQL_BINLOG --raw --read-from-remote-master=BINLOG-DUMP-NON-GTID --include-gtids=$UUID:1-3 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/
|
|
--error 1
|
|
--exec $MYSQL_BINLOG --raw --read-from-remote-master=BINLOG-DUMP-GTID --include-gtids=$UUID:1-3 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT $binlog_file --result-file=$MYSQLTEST_VARDIR/tmp/
|
|
--diff_files $MYSQLTEST_VARDIR/tmp/output.000002 $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000001
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/output.000002
|
|
|
|
--echo ==== Error cases ====
|
|
|
|
--error 1
|
|
--exec $MYSQL_BINLOG --read-from-remote-server --read-from-remote-master=BINLOG-DUMP-GTID $binlog_file 2>&1
|
|
|
|
--error 1
|
|
--exec $MYSQL_BINLOG --raw $binlog_file 2>&1
|
|
|
|
--error 1
|
|
--exec $MYSQL_BINLOG --read-from-remote-server --raw --exclude-gtids= $binlog_file 2>&1
|
|
|
|
--error 1
|
|
--exec $MYSQL_BINLOG --read-from-remote-master=BINLOG-DUMP-NON-GTID --raw --exclude-gtids= $binlog_file 2>&1
|
|
|
|
--error 1
|
|
--exec $MYSQL_BINLOG --read-from-remote-server --raw --include-gtids= $binlog_file 2>&1
|
|
|
|
#
|
|
# cleanup
|
|
#
|
|
|
|
--connection master
|
|
|
|
FLUSH LOGS;
|
|
DROP TABLE t1;
|
|
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
|
|
--exec $MYSQL_BINLOG $datadir/$binlog_file | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT
|
|
connection slave;
|
|
CHANGE MASTER TO MASTER_AUTO_POSITION=0;
|