Overview
Need to clone a MySQL server with large amounts of data? So do we and like you we do not have the time to perform a mysqldump and restore. Fortunately, MySQL does permit copying the raw data files. In this article we will be doing a complete clone - a clone of everything - except for a few files that are specific to each MySQL instance.You should read the MySQL article 17.1.1.6 Creating a Data Snapshot Using Raw Data Files as these steps are composed from the MySQL article plus some experience thrown in.
We are a Linux shop therefore this article will be using Linux features and commands. Other Linux/Unix flavors should be adaptable. Microsoft Windows Server is not a real server and won't be mentioned again.
This article also assumes you are familiar with MySQL 5.6 GTID replication.
Gotchas!
Incompatibilities
There is one specific gotcha that you should be aware of. This pertains to advance MySQL configurations which is mentioned in the above MySQL article:"This method also does not work reliably if the master and slave have different values for ft_stopword_file, ft_min_word_len, or ft_max_word_len and you are copying tables having full-text indexes."
Service Shutdown / Cold Backup
The steps utilized in this article require performing a cold backup from the source server. That means you will need to shutdown the MySQL service on the source server. This is detailed below. This article does NOT consider performing warm or hot backups.Backup your GTID (auto.cnf file)!
Before doing any of the steps outlined in this article I strongly recommend you backup your auto.cnf file located in your MySQL data directory. Backup this file to a directory outside of the MySQL data directory structure. This file contains the local servers unique GTID used for GTID replication. This file cannot be duplicated in replication or severe grief it may cause you. Yes, I do know there are instances where it can be duplicated but that is beyond the scope of this article.Our Environment
We are using MySQL Server version 5.6.28 (from the MySQL apt repository) deployed on Ubuntu 12.04 and 14.04 servers. We use GTID replication and have four master servers configured in a circular (aka looped) replication. Two master servers are in our primary data center and two are in our DR (disaster-recovery) data center. There are several read-only slaves in each data center. It is akin to a Hub and Spoke: The four master servers are the hub and the slaves are the spokes.All of our servers are configured identically. The only difference is the number of CPUs and the amount of RAM. All MySQL data files are stored using the same directory structure.
Source Server Steps
The source server is the server you will copy the raw data files from. In a GTID replication environment this can be a master or slave server! Gotta love GTIDs!- Replication: Stop the slave IO_THREAD
If you are using replication, you will need to stop the IO_THREAD so the server doesn't download anymore binlog entries from its master.
NOTE: You cannot change the slave status once the server is in read-only mode!
mysql> STOP SLAVE IO_THREAD; - Replication: Wait for server to apply all replicated SQL.
Monitor the source server and wait until all EXECUTED GTIDs match the RETRIEVED GTIDs:
mysql> SHOW SLAVE STATUS;
NOTE: This is where our gtidmonitor tool comes in real handy. - Replication: Stop the slave completely.
Once the SQL_THREAD has finished, stop the slave completely:
mysql> STOP SLAVE; - Make the server READ ONLY:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON; - Configure for slow shutdown.
mysql> SET GLOBAL innodb_fast_shutdown=0; - Replication: Copy the EXECUTED GTIDs.
mysql> SHOW SLAVE STATUS;
Copy the data from the Executed_Gtid_Set column.You will need these when performing the CHANGE MASTER on the replica. In our case it looks like this (do not use mine, use yours!!!):
69cf02cd-1731-11e3-9a19-002590854928:1-298619422,
708bb615-d393-11e3-a682-003048c3ab22:1-130474639,
819c985c-d384-11e3-a621-00259002979a:1-122773968,
9204e764-d379-11e3-a5d9-0013726268ea:1-76880478 - Shutdown the MySQL service.
Since we use Ubuntu, our command is like so:
shell> sudo service mysql stop - Copy the entire MySQL data directory to a new location.
Local copy: If you have the space on the same server I recommend that you duplicate the data directory on the same server for speed sake. This way you can restart the MySQL server quickly. Example:
shell> sudo cp -rp /d01/mysql/data /d01/mysql/coldbu
Network copy: If you don't have the space and want/need to copy directly to the target server then you should review the target server steps 1 and 2 BEFORE you start your copy!
shell> sudo scp -Crp /d01/mysql/data target:/d01/mysql/data
Gotchas!
A) The MySQL server service cannot be running on either the SOURCE or TARGET server!
B) DO NOT COPY the AUTO.CNF located in the data directory to the target server (or backup the target server auto.cnf first)!
C) DO NOT COPY the MASTER or SLAVE relay logs. If you do copy them to the target server then just delete them from the TARGET server. - Restart the MySQL server service.
Once the copy is complete then restart the source server MySQL service:
shell> sudo service mysql start - Cleanup the cold backup directory.
If you performed a local copy of the MySQL data directory then I recommend cleaning it up before copying the directory to the target server.
shell> cd /d01/mysql/coldbu
A) Remove any master binary log files.
B) Remove all slave relay files.
C) Remove the auto.cnf file.
Target Server Steps
The target server is the server you will copy the raw data files to.
- Shutdown the MySQL service.
Since we use Ubuntu, our command is like so:
shell> sudo service mysql stop - Backup your auto.cnf file!
Backup this file to a directory outside of the MySQL data directory structure. - Copy the cold backup data directory.
If you haven't already, copy the cold backup directory to the MySQL data directory. - Ensure the auto.cnf file wasn't over-written or changed!
If it was then restore your backup. - Ensure the proper owner and permissions are set.
shell> cd /d01/mysql/data
shell> find . -type d -exec chmod 755 {} \;
shell> find . -type f -exec chmod 644 {} \;
shell> chown -R mysql:mysql * - Replication: Disable slave startup.
We do this to ensure the slave doesn't try to start in case you are using TABLE for replication purposes. Edit the my.cnf (mysql server configuration file) and add/set:
skip-slave-start = 1 - Start the MySQL server service.
In Ubuntu, we do:
shell> sudo service msyql start - Log into the MySQL server as root (or root equivalent).
- Perform a complete slave reset.
mysql> RESET SLAVE ALL; - Perform a complete master reset.
mysql> RESET MASTER; - Replication: Set the GTID_PURGED:
You will need your EXECUTED GTIDs from source server step 6.
mysql> RESET MASTER;
mysql> SET GLOBAL.GTID_PURGED ='{your GTIDs}'
In our case:
mysql> SET GLOBAL.GTID_PURGED ='69cf02cd-1731-11e3-9a19-002590854928:1-298619422,
708bb615-d393-11e3-a682-003048c3ab22:1-130474639,
819c985c-d384-11e3-a621-00259002979a:1-122773968,
9204e764-d379-11e3-a5d9-0013726268ea:1-76880478' - Replication: Set the master server:
mysql> CHANGE MASTER TO
master_host = "masterserver",
master_port = 3306,
master_user = "mysql_user",
master_password = "mysql_user_password",
master_auto_position = 1; - Replication: Start the slave.
mysql> START SLAVE; - Replication: Enable slave startup.
Edit the my.cnf (mysql server configuration file) and set:
skip-slave-start = 0
No comments:
Post a Comment