1

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.

  • Server A and B have different databases. What does that mean? You're trying to move the databases from A onto B, while B is already a working system? – Michael - sqlbot Sep 21 '16 at 20:51
  • Unfortunately yes. Server A is already working system and B also, I will soon turn off the server A. And now all the databases with A I have to move to B, where it already working another system. – 2mec Sep 21 '16 at 21:01
  • Are you actively writing to A1? And you need to copy the data, then catchup on what is missed during the copy? – Rick James Sep 27 '16 at 5:30
  • @RickJames - Yes – 2mec Sep 28 '16 at 13:45
  • Check out Percona's Xtrabackup. – Rick James Sep 28 '16 at 15:02
1

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>/dev/null"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --master-data=2"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} | gzip > 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>/dev/null"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --dump-slave=2"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} | gzip > 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 < 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 !!!

  • Thx Rolando everything works fine. DO you know other mechanism that works faster than mysqldump. when database size is bigger then 10 GB. – 2mec Sep 28 '16 at 13:51
  • I read about innobackupex, but i don't know how restoring one database. – 2mec Sep 28 '16 at 13:56
0

Use "multi-source replication", which is available on your servers (as of 10.0.1). See https://mariadb.com/kb/en/mariadb/multi-source-replication/ for details.

It would go something like this:

  1. Set up the replication from A1 to B1, but not "turn it on".
  2. Take some form of dump (mysqldump, xtrabackup, etc) of the databases of A1 you want to copy. Be sure to grab the position in the binlog.
  3. Load them on B1.
  4. Turn on replication so that it will catchup.
  5. Retire A1/2/3.

(As Rolando say, try it out before doing it in Production.)

  • Thx, do know how to restoring one database by the xtrabackup ? – 2mec Sep 28 '16 at 13:57
  • Sorry, I don't have a good summary to give you here. – Rick James Sep 28 '16 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.