How to transfer a few databases from MariaDB master to another master I must transfer databases (everything without system's databases) from one replication server to another replication server.
Everything is on MariaDB (10.0.20) and i need to do it with minimal downtime.
On source servers I have master (serverA1), slave (serverA2), slave (serverA3) replication.
On destination server I have the same situation - master (ServerB1), slave (ServerB2), slave (ServerB3).
Now I must move databases from servers A to servers B, with minimal downtime and not to ruin replication.
Server A and B have different databases.
In future Server A, will be offlined.
The only thing that you can do is this: ServerB must become a slave to ServerA

Let's assume the following


ServerA1 has an IP of 10.20.30.40
ServerA1 has databases db1, db2, db3
ServerB1 has databases db4, db5, db6
ServerA2 is a Slave of ServerA1
ServerA3 is a Slave of ServerA1
ServerB2 is a Slave of ServerB1
ServerB3 is a Slave of ServerB1


Here are the Steps

STEP 01

Make sure ServerA and ServerB and their slave has unique server_id values

STEP 02

OPTION #1 : Dump from ServerA1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql','test','sys')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL} 2&gt;/dev/null"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --master-data=2"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} | gzip &gt; DataFromServerA.sql.gz


OPTION #2 : Dump From a Slave of ServerA1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql','test','sys')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL} 2&gt;/dev/null"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --dump-slave=2"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} | gzip &gt; DataFromServerA.sql.gz


Note: In option #2, you use --dump-slave. This tells a slave to publish its master's binary log file/pos to the output of mysqldump instead of its own.

STEP 03 : Get the Replication Log File and Log Position

less DataFromServerA.sql.gz | head -23 | tail -2


This will echo the CHANGE MASTER TO with the log file and position of the Master

For the sake of this example, say the output is

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.001236', MASTER_LOG_POS=102938;


STEP 04 : Transfer Dump into ServerB1

STEP 05 : Load Dump into ServerB1

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d &lt; DataFromServerA.sql.gz | mysql ${MYSQL_CONN}


STEP 06 : Create Replication User for the Cutover

On ServerA1, run

GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO 'cutover'@'%'
IDENTIFIED BY 'cutover';


STEP 07

On ServerB1, run

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='cutover',
master_password='cutover',
master_log_file='bin-log.001236',
master_log_pos=102938;
START SLAVE;
SLAVE SLAVE STATUS\G


NOTE: Please make sure there are no firewall issues

STEP 08 : Wait for Replication to Catch up

Keep running on ServerB1 SLAVE SLAVE STATUS\G until Seconds_Behind_Master is 0

STEP 09 : Stop your writes to ServerA1

After disabling all apps that write to ServerA1, run this on ServerA

SET GLOBAL read_only = 1;
FLUSH TABLES;
FLUSH LOGS;


STEP 10 : Point all the apps at ServerB1

STEP 11 : Disable MySQL Replication from ServerA1

On ServerB1, run

STOP SLAVE;
RESET SLAVE ALL;


STEP 12 : Decommission ServerA1, ServerA2, ServerA3

CAVEAT : Please try all this out on Staging Servers Before Doing So on Production

GIVE IT A TRY !!!