390 lines
11 KiB
Plaintext
390 lines
11 KiB
Plaintext
include/master-slave.inc
|
|
Warnings:
|
|
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
|
|
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
|
|
[connection master]
|
|
|
|
---Setup Section --
|
|
set timestamp=1000000000;
|
|
DROP TABLE IF EXISTS t1,t2,t3;
|
|
CREATE TABLE t1(word VARCHAR(20));
|
|
CREATE TABLE t2(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
|
|
CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5 FLOAT);
|
|
|
|
---Test1 check table load --
|
|
SELECT COUNT(*) from t1;
|
|
COUNT(*)
|
|
351
|
|
SELECT COUNT(*) from t2;
|
|
COUNT(*)
|
|
500
|
|
SELECT COUNT(*) from t3;
|
|
COUNT(*)
|
|
500
|
|
SELECT * FROM t1 ORDER BY word LIMIT 5;
|
|
word
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
SELECT * FROM t2 ORDER BY id LIMIT 5;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5;
|
|
c1 c3 c4 c5
|
|
1 2006-02-22 00:00:00 Tested in Texas 2.2
|
|
2 2006-02-22 00:00:00 Tested in Texas 4.4
|
|
3 2006-02-22 00:00:00 Tested in Texas 6.6
|
|
4 2006-02-22 00:00:00 Tested in Texas 8.8
|
|
5 2006-02-22 00:00:00 Tested in Texas 11
|
|
SELECT COUNT(*) from t1;
|
|
COUNT(*)
|
|
351
|
|
SELECT COUNT(*) from t2;
|
|
COUNT(*)
|
|
500
|
|
SELECT COUNT(*) from t3;
|
|
COUNT(*)
|
|
500
|
|
SELECT * FROM t1 ORDER BY word LIMIT 5;
|
|
word
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
SELECT * FROM t2 ORDER BY id LIMIT 5;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5;
|
|
c1 c3 c4 c5
|
|
1 2006-02-22 00:00:00 Tested in Texas 2.2
|
|
2 2006-02-22 00:00:00 Tested in Texas 4.4
|
|
3 2006-02-22 00:00:00 Tested in Texas 6.6
|
|
4 2006-02-22 00:00:00 Tested in Texas 8.8
|
|
5 2006-02-22 00:00:00 Tested in Texas 11
|
|
insert into t1 values ("Alas");
|
|
flush logs;
|
|
|
|
--- Test 1 Dump binlog to file --
|
|
|
|
--- Test 1 delete tables, clean master and slave --
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DROP TABLE t3;
|
|
stop slave;
|
|
include/wait_for_slave_to_stop.inc
|
|
reset master;
|
|
reset slave;
|
|
reset master;
|
|
start slave;
|
|
include/wait_for_slave_to_start.inc
|
|
|
|
--- Test 1 Load from Dump binlog file --
|
|
|
|
--- Test 1 Check Load Results --
|
|
SELECT COUNT(*) from t1;
|
|
COUNT(*)
|
|
352
|
|
SELECT COUNT(*) from t2;
|
|
COUNT(*)
|
|
500
|
|
SELECT COUNT(*) from t3;
|
|
COUNT(*)
|
|
500
|
|
SELECT * FROM t1 ORDER BY word LIMIT 5;
|
|
word
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
SELECT * FROM t2 ORDER BY id LIMIT 5;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5;
|
|
c1 c3 c4 c5
|
|
1 2006-02-22 00:00:00 Tested in Texas 2.2
|
|
2 2006-02-22 00:00:00 Tested in Texas 4.4
|
|
3 2006-02-22 00:00:00 Tested in Texas 6.6
|
|
4 2006-02-22 00:00:00 Tested in Texas 8.8
|
|
5 2006-02-22 00:00:00 Tested in Texas 11
|
|
SELECT COUNT(*) from t1;
|
|
COUNT(*)
|
|
352
|
|
SELECT COUNT(*) from t2;
|
|
COUNT(*)
|
|
500
|
|
SELECT COUNT(*) from t3;
|
|
COUNT(*)
|
|
500
|
|
SELECT * FROM t1 ORDER BY word LIMIT 5;
|
|
word
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
SELECT * FROM t2 ORDER BY id LIMIT 5;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5;
|
|
c1 c3 c4 c5
|
|
1 2006-02-22 00:00:00 Tested in Texas 2.2
|
|
2 2006-02-22 00:00:00 Tested in Texas 4.4
|
|
3 2006-02-22 00:00:00 Tested in Texas 6.6
|
|
4 2006-02-22 00:00:00 Tested in Texas 8.8
|
|
5 2006-02-22 00:00:00 Tested in Texas 11
|
|
|
|
--- Test 2 position test --
|
|
include/mysqlbinlog.inc
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
ROLLBACK/*!*/;
|
|
# original_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
# immediate_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
/*!80001 SET @@session.original_commit_timestamp= MICROSECONDS-FROM-EPOCH*//*!*/;
|
|
/*!80014 SET @@session.original_server_version= ORIGINAL_SERVER_VERSION*//*!*/;
|
|
/*!80014 SET @@session.immediate_server_version= IMMEDIATE_SERVER_VERSION*//*!*/;
|
|
SET @@SESSION.GTID_NEXT= '#'/*!*/;
|
|
use `test`/*!*/;
|
|
SET TIMESTAMP=#/*!*/;
|
|
SET @@session.pseudo_thread_id=#/*!*/;
|
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
|
|
SET @@session.sql_mode=1168113696/*!*/;
|
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
|
/*!\C utf8mb4 *//*!*/;
|
|
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
|
|
SET @@session.lc_time_names=0/*!*/;
|
|
SET @@session.collation_database=DEFAULT/*!*/;
|
|
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
|
|
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
|
|
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
|
|
CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5 FLOAT)
|
|
/*!*/;
|
|
SET @@SESSION.GTID_NEXT= '#' /* added by mysqlbinlog */ /*!*/;
|
|
DELIMITER ;
|
|
# End of log file
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
|
|
--- Test 3 First Remote test --
|
|
include/mysqlbinlog.inc
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
ROLLBACK/*!*/;
|
|
# [empty]
|
|
# original_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
# immediate_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
/*!80001 SET @@session.original_commit_timestamp= MICROSECONDS-FROM-EPOCH*//*!*/;
|
|
/*!80014 SET @@session.original_server_version= ORIGINAL_SERVER_VERSION*//*!*/;
|
|
/*!80014 SET @@session.immediate_server_version= IMMEDIATE_SERVER_VERSION*//*!*/;
|
|
SET @@SESSION.GTID_NEXT= '#'/*!*/;
|
|
use `test`/*!*/;
|
|
SET TIMESTAMP=#/*!*/;
|
|
SET @@session.pseudo_thread_id=#/*!*/;
|
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
|
|
SET @@session.sql_mode=1168113696/*!*/;
|
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
|
/*!\C utf8mb4 *//*!*/;
|
|
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
|
|
SET @@session.lc_time_names=0/*!*/;
|
|
SET @@session.collation_database=DEFAULT/*!*/;
|
|
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
|
|
DROP TABLE IF EXISTS `t1`,`t2`,`t3` /* generated by server */
|
|
/*!*/;
|
|
# original_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
# immediate_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
/*!80001 SET @@session.original_commit_timestamp= MICROSECONDS-FROM-EPOCH*//*!*/;
|
|
/*!80014 SET @@session.original_server_version= ORIGINAL_SERVER_VERSION*//*!*/;
|
|
/*!80014 SET @@session.immediate_server_version= IMMEDIATE_SERVER_VERSION*//*!*/;
|
|
SET @@SESSION.GTID_NEXT= '#'/*!*/;
|
|
SET TIMESTAMP=#/*!*/;
|
|
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
|
|
CREATE TABLE t1(word VARCHAR(20))
|
|
/*!*/;
|
|
# original_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
# immediate_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
/*!80001 SET @@session.original_commit_timestamp= MICROSECONDS-FROM-EPOCH*//*!*/;
|
|
/*!80014 SET @@session.original_server_version= ORIGINAL_SERVER_VERSION*//*!*/;
|
|
/*!80014 SET @@session.immediate_server_version= IMMEDIATE_SERVER_VERSION*//*!*/;
|
|
SET @@SESSION.GTID_NEXT= '#'/*!*/;
|
|
SET TIMESTAMP=#/*!*/;
|
|
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
|
|
CREATE TABLE t2(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY)
|
|
/*!*/;
|
|
# original_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
# immediate_commit_timestamp= MICROSECONDS-FROM-EPOCH (YYYY-MM-DD HOURS:MINUTES:SECONDS TZ)
|
|
/*!80001 SET @@session.original_commit_timestamp= MICROSECONDS-FROM-EPOCH*//*!*/;
|
|
/*!80014 SET @@session.original_server_version= ORIGINAL_SERVER_VERSION*//*!*/;
|
|
/*!80014 SET @@session.immediate_server_version= IMMEDIATE_SERVER_VERSION*//*!*/;
|
|
SET @@SESSION.GTID_NEXT= '#'/*!*/;
|
|
SET TIMESTAMP=#/*!*/;
|
|
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
|
|
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
|
|
CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5 FLOAT)
|
|
/*!*/;
|
|
SET @@SESSION.GTID_NEXT= '#' /* added by mysqlbinlog */ /*!*/;
|
|
DELIMITER ;
|
|
# End of log file
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
|
|
--- Test 4 Second Remote test --
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DROP TABLE t3;
|
|
stop slave;
|
|
include/wait_for_slave_to_stop.inc
|
|
reset master;
|
|
reset slave;
|
|
reset master;
|
|
start slave;
|
|
include/wait_for_slave_to_start.inc
|
|
SELECT COUNT(*) from t1;
|
|
COUNT(*)
|
|
352
|
|
SELECT COUNT(*) from t2;
|
|
COUNT(*)
|
|
500
|
|
SELECT COUNT(*) from t3;
|
|
COUNT(*)
|
|
500
|
|
SELECT * FROM t1 ORDER BY word LIMIT 5;
|
|
word
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
SELECT * FROM t2 ORDER BY id LIMIT 5;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5;
|
|
c1 c3 c4 c5
|
|
1 2006-02-22 00:00:00 Tested in Texas 2.2
|
|
2 2006-02-22 00:00:00 Tested in Texas 4.4
|
|
3 2006-02-22 00:00:00 Tested in Texas 6.6
|
|
4 2006-02-22 00:00:00 Tested in Texas 8.8
|
|
5 2006-02-22 00:00:00 Tested in Texas 11
|
|
SELECT COUNT(*) from t1;
|
|
COUNT(*)
|
|
352
|
|
SELECT COUNT(*) from t2;
|
|
COUNT(*)
|
|
500
|
|
SELECT COUNT(*) from t3;
|
|
COUNT(*)
|
|
500
|
|
SELECT * FROM t1 ORDER BY word LIMIT 5;
|
|
word
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
Aarhus
|
|
SELECT * FROM t2 ORDER BY id LIMIT 5;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
SELECT c1, c3, c4, c5 FROM t3 ORDER BY c1 LIMIT 5;
|
|
c1 c3 c4 c5
|
|
1 2006-02-22 00:00:00 Tested in Texas 2.2
|
|
2 2006-02-22 00:00:00 Tested in Texas 4.4
|
|
3 2006-02-22 00:00:00 Tested in Texas 6.6
|
|
4 2006-02-22 00:00:00 Tested in Texas 8.8
|
|
5 2006-02-22 00:00:00 Tested in Texas 11
|
|
|
|
--- Test 5 LOAD DATA --
|
|
include/mysqlbinlog.inc
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
SET @@SESSION.GTID_NEXT= '#' /* added by mysqlbinlog */ /*!*/;
|
|
DELIMITER ;
|
|
# End of log file
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
|
|
--- Test 6 reading stdin --
|
|
include/mysqlbinlog.inc
|
|
|
|
--- Test 7 reading stdin w/position --
|
|
include/mysqlbinlog.inc
|
|
|
|
--- Test 8 switch internal charset --
|
|
stop slave;
|
|
include/wait_for_slave_to_stop.inc
|
|
reset master;
|
|
reset slave;
|
|
reset master;
|
|
start slave;
|
|
include/wait_for_slave_to_start.inc
|
|
create table t4 (f text character set utf8);
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
create table t5 (f text character set cp932);
|
|
flush logs;
|
|
rename table t4 to t04, t5 to t05;
|
|
select HEX(f) from t04;
|
|
HEX(f)
|
|
E382BD
|
|
select HEX(f) from t4;
|
|
HEX(f)
|
|
E382BD
|
|
select HEX(f) from t05;
|
|
HEX(f)
|
|
835C
|
|
select HEX(f) from t5;
|
|
HEX(f)
|
|
835C
|
|
select HEX(f) from t04;
|
|
HEX(f)
|
|
E382BD
|
|
select HEX(f) from t4;
|
|
HEX(f)
|
|
E382BD
|
|
select HEX(f) from t05;
|
|
HEX(f)
|
|
835C
|
|
select HEX(f) from t5;
|
|
HEX(f)
|
|
835C
|
|
|
|
--- Test cleanup --
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a INT NOT NULL KEY, b INT);
|
|
INSERT INTO t1 VALUES(1,1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
FLUSH LOGS;
|
|
DROP TABLE IF EXISTS t1, t2, t3, t04, t05, t4, t5;
|
|
include/rpl_end.inc
|