You’ve finally done it: your MySQL database has gotten so large that it simply isn’t working on one machine anymore. You’ve tweaked it, you’ve optimized it, you’ve gotten it to the point where your system simply can’t read fast enough to accommodate the number of reads. Is it time to move to a NoSQL solution? Possibly, but even then, you’ll need a temporary solution to keep your site running smoothly during the transition phase! It’s time to add more MySQL servers to help handle the load. It’s time to use Replication!
Replication is a great way to increase the amount of load your MySQL server can handle; by having multiple nodes, the read performance of your database can be substantially increased. This article is here to give you an example setup, and show you step-by-step how to configure a MySQL server.
NOTE: This is MySQL replication, not sharding; they are two very different concepts. The sharding vs. replication debate is out of scope for this article, as is the tutorial about sharding itself; this tutorial is for master/slave replication-style setups only!
We are also assuming the databases are installed on Linux-based machines. Windows users, your mileage may vary!
Here we go!
Step 1: Install MySQL on both master and slave machines
This may seem a bit obvious, but make sure the MySQL server and client packages are installed; this is a quick package install command away on any apt or yum based systems, and you can always grab MySQL directly from the website if you have to!
Step 2: Configure the master my.cnf
Once MySQL has been installed on both of the servers, head on over to the master server and edit
my.cnf (usually located in
/etc/mysql). What’s in the
my.cnf may vary widely, depending on the server setup; at the very least, you’re going to want these lines in there, in the
You can change log-bin to whatever you want, but we’re going to use
sqlserverbin.log for the rest of the example. You’re also going to want to make sure the master is listening across addresses; to do that, make sure the
bind-address variable, if present, is commented out.
Step 3: Create replication user
Next up, you’re going to need to create a replication user on the master machine. This user is the one the slave machine will use to hook up to the master and get all the latest database updates. Log into the master mysql server via the command line, as root, and type in:
mysql> CREATE USER 'replicator'@'10.1.1.15'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.1.1.15' IDENTIFIED BY 'replicatorpassword'; mysql> FLUSH PRIVILEGES;
10.1.1.15 with the IP address of the slave machine.
Step 4: Configure slave my.cnf
Head on over (or SSH into) your slave machine, and edit the
my.cnf to include these lines in your
server-id=2 relay-log-index=relay-bin.index relay-log=relay-bin
It’s vital that the slave and the master each have different server IDs!
Once you’ve done that, restart mysqld on both servers. Log into the slave mysql and issue the following commands from the mysql prompt:
mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.14', -> MASTER_USER='replicator', -> MASTER_PASSWORD='replicatorpassword', -> MASTER_LOG_FILE='', -> MASTER_LOG_POS=4;
And there you have it! You’ve successfully begun replicating on your MySQL platform!
ADDENDUM: Replicating with Data
If your database is a production one and already has data on it, the process is slightly trickier. Read on for how to do it in this case!
Configure both the
my.cnf files the same way, and restart the mysqld services on both. Then, on the master server, input the following commands:
mysql> GRANT ALL ON *.* TO 'root'@'10.1.1.15'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.1.1.15' IDENTIFIED BY 'replicatorpassword'; mysql> FLUSH PRIVILEGES;
You’ll notice that this step is slightly different; we are granting privileges on the database to the root user on the slave server. We’ll need this later.
Next, on the slave server, type the following commands:
mysql -u root-p rootpw -h 10.1.1.14 -e "FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS" > mastertemp.txt; mysqldump -u root -p rootpw -h 10.1.1.14 -A --single-transaction | mysql -u root -p rootpw mysql -u root -p rootpw -h 10.1.1.14 -e "UNLOCK TABLES";
What this nifty code does is log into the mysql database remotely as root, flush the tables with a readlock, get some info necessary for later on, and clones the mysql database to the slave server. Nifty, no?
Our work’s not quite done, however. Remember that
mastertemp.txt file we created? We are going to need that; open it up and write down the log file name and the position. These will be important in just a second!
Next, open a mysql prompt on the slave server and put in the following:
mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.14', 'MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='replicatorpassword', MASTER_LOG_FILE='log_file_you_took_from_text_file', MASTER_LOG_POS='number_you_took_from_text_file'; START SLAVE;
And there you have it! Two ways to replicate your MySQL database; one if you’ve got no data and are starting from scratch, and the other if you’ve got some data you need to preserve. There are, of course, more involved scenarios, including multi-master configurations; they all stem from this setup, however, and with this you are well on your way to scaling out your MySQL database effectively!