mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
258 lines
9.5 KiB
Text
258 lines
9.5 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
#
|
|
# New --dump-slave, --apply-slave-statements functionality
|
|
#
|
|
connection slave;
|
|
connection master;
|
|
use test;
|
|
connection slave;
|
|
/*M!999999\- enable the sandbox mode */
|
|
CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
-- SET GLOBAL gtid_slave_pos='';
|
|
/*M!999999\- enable the sandbox mode */
|
|
STOP ALL SLAVES;
|
|
CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
-- SET GLOBAL gtid_slave_pos='';
|
|
START ALL SLAVES;
|
|
/*M!999999\- enable the sandbox mode */
|
|
STOP ALL SLAVES;
|
|
CHANGE MASTER '' TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_MYPORT, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
-- SET GLOBAL gtid_slave_pos='';
|
|
START ALL SLAVES;
|
|
start slave;
|
|
Warnings:
|
|
Note 1254 Slave is already running
|
|
/*M!999999\- enable the sandbox mode */
|
|
CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
start slave;
|
|
Warnings:
|
|
Note 1254 Slave is already running
|
|
*** Test mysqldump --dump-slave GTID/non-gtid functionality.
|
|
connection master;
|
|
SET gtid_seq_no = 1000;
|
|
CREATE TABLE t1 (a INT PRIMARY KEY);
|
|
DROP TABLE t1;
|
|
connection slave;
|
|
connection slave;
|
|
CREATE TABLE t2 (a INT PRIMARY KEY);
|
|
DROP TABLE t2;
|
|
|
|
1. --dump-slave=1 --gtid
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
CHANGE MASTER '' TO MASTER_USE_GTID=slave_pos;
|
|
-- CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
SET GLOBAL gtid_slave_pos='0-1-1001';
|
|
|
|
1a. --dump-slave=1
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
-- SET GLOBAL gtid_slave_pos='0-1-1001';
|
|
|
|
2. --dump-slave=2 --gtid
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
-- CHANGE MASTER '' TO MASTER_USE_GTID=slave_pos;
|
|
-- CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
-- SET GLOBAL gtid_slave_pos='0-1-1001';
|
|
|
|
2. --dump-slave=2
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
-- CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
-- SET GLOBAL gtid_slave_pos='0-1-1001';
|
|
*** Test mysqldump --master-data GTID/non-gtid functionality.
|
|
|
|
1. --master-data=1 --gtid
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
CHANGE MASTER TO MASTER_USE_GTID=slave_pos;
|
|
-- CHANGE MASTER TO MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
SET GLOBAL gtid_slave_pos='0-2-1003';
|
|
|
|
1a. --master-data=1
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
CHANGE MASTER TO MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
-- SET GLOBAL gtid_slave_pos='0-2-1003';
|
|
|
|
2. --master-data=2 --gtid
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
-- CHANGE MASTER TO MASTER_USE_GTID=slave_pos;
|
|
-- CHANGE MASTER TO MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
-- SET GLOBAL gtid_slave_pos='0-2-1003';
|
|
|
|
2a. --master-data=2
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
-- CHANGE MASTER TO MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
-- SET GLOBAL gtid_slave_pos='0-2-1003';
|
|
|
|
3. --master-data --single-transaction --gtid
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
CHANGE MASTER TO MASTER_USE_GTID=slave_pos;
|
|
-- CHANGE MASTER TO MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
SET GLOBAL gtid_slave_pos='0-2-1003';
|
|
|
|
3a. --master-data --single-transaction
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
CHANGE MASTER TO MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
-- SET GLOBAL gtid_slave_pos='0-2-1003';
|
|
|
|
4. --master-data=2 --dump-slave=2 --single-transaction --gtid (MDEV-4827)
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
-- MariaDB dump--
|
|
-- Host: localhost Database: test
|
|
-- ------------------------------------------------------
|
|
-- Server version
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8mb4 */;
|
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
|
/*M!100616 SET @OLD_NOTE_VERBOSITY=@@NOTE_VERBOSITY, NOTE_VERBOSITY=0 */;
|
|
-- CHANGE MASTER TO MASTER_USE_GTID=slave_pos;
|
|
|
|
-- A corresponding to the above master-data CHANGE-MASTER settings to the slave gtid state is printed later in the file.
|
|
|
|
--
|
|
-- Alternately, following is the position of the binary logging from SHOW MASTER STATUS at point of backup.
|
|
-- Use this when creating a replica of the primary server where the backup was made.
|
|
-- The new server will be connecting to the primary server where the backup was taken.
|
|
--
|
|
|
|
-- CHANGE MASTER TO MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
--
|
|
-- The following is the SQL position of the replication taken from SHOW SLAVE STATUS at the time of backup.
|
|
-- Use this position when creating a clone of, or replacement server, from where the backup was taken.
|
|
-- This new server will connects to the same primary server(s).
|
|
--
|
|
|
|
-- A corresponding to the below dump-slave CHANGE-MASTER settings to the slave gtid state is printed later in the file.
|
|
|
|
-- Use only the MASTER_USE_GTID=slave_pos or MASTER_LOG_FILE/MASTER_LOG_POS in the statements below.
|
|
|
|
-- CHANGE MASTER '' TO MASTER_USE_GTID=slave_pos;
|
|
-- CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
|
|
-- The deferred gtid setting for slave corresponding to the master-data CHANGE-MASTER follows
|
|
|
|
-- Preferably use GTID to start replication from GTID position:
|
|
|
|
-- SET GLOBAL gtid_slave_pos='0-2-1003';
|
|
|
|
-- The deferred gtid setting for slave corresponding to the dump-slave CHANGE-MASTER follows
|
|
-- GTID position to start replication:
|
|
-- SET GLOBAL gtid_slave_pos='0-1-1001';
|
|
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
|
|
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
|
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
|
/*M!100616 SET NOTE_VERBOSITY=@OLD_NOTE_VERBOSITY */;
|
|
|
|
-- Dump completed
|
|
|
|
4a. --master-data=2 --dump-slave=2 --single-transaction (MDEV-4827)
|
|
|
|
/*M!999999\- enable the sandbox mode */
|
|
-- MariaDB dump--
|
|
-- Host: localhost Database: test
|
|
-- ------------------------------------------------------
|
|
-- Server version
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8mb4 */;
|
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
|
/*M!100616 SET @OLD_NOTE_VERBOSITY=@@NOTE_VERBOSITY, NOTE_VERBOSITY=0 */;
|
|
|
|
--
|
|
-- Alternately, following is the position of the binary logging from SHOW MASTER STATUS at point of backup.
|
|
-- Use this when creating a replica of the primary server where the backup was made.
|
|
-- The new server will be connecting to the primary server where the backup was taken.
|
|
--
|
|
|
|
-- CHANGE MASTER TO MASTER_LOG_FILE='slave-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
-- A corresponding to the above master-data CHANGE-MASTER settings to the slave gtid state is printed as comments later in the file.
|
|
|
|
--
|
|
-- The following is the SQL position of the replication taken from SHOW SLAVE STATUS at the time of backup.
|
|
-- Use this position when creating a clone of, or replacement server, from where the backup was taken.
|
|
-- This new server will connects to the same primary server(s).
|
|
--
|
|
|
|
-- A corresponding to the below dump-slave CHANGE-MASTER settings to the slave gtid state is printed later in the file.
|
|
-- CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START;
|
|
|
|
|
|
-- The deferred gtid setting for slave corresponding to the master-data CHANGE-MASTER follows
|
|
|
|
-- Preferably use GTID to start replication from GTID position:
|
|
|
|
-- SET GLOBAL gtid_slave_pos='0-2-1003';
|
|
|
|
-- The deferred gtid setting for slave corresponding to the dump-slave CHANGE-MASTER follows
|
|
-- GTID position to start replication:
|
|
-- SET GLOBAL gtid_slave_pos='0-1-1001';
|
|
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
|
|
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
|
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
|
/*M!100616 SET NOTE_VERBOSITY=@OLD_NOTE_VERBOSITY */;
|
|
|
|
-- Dump completed
|
|
connection master;
|
|
CREATE TABLE t (
|
|
id int
|
|
);
|
|
insert into t values (1);
|
|
insert into t values (2);
|
|
drop table t;
|
|
connection slave;
|
|
include/stop_slave.inc
|
|
change master to master_use_gtid=slave_pos;
|
|
connection master;
|
|
# Ensuring the binlog dump thread is killed on primary...
|
|
/*M!999999\- enable the sandbox mode */
|
|
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=BINLOG_START;
|
|
-- SET GLOBAL gtid_slave_pos='0-1-1005';
|
|
connection slave;
|
|
include/start_slave.inc
|
|
connection master;
|
|
connection slave;
|
|
connection master;
|
|
FOUND 1 matches in MDEV-33212.sql
|
|
include/rpl_end.inc
|