Setting up a MySQL Replication Slave
For the last year I had avoided this because I
expected it would be hard. But replication is really is not that hard after all
.... and it makes backing up very easy avoiding special scripts, sql dumps, etc.
as well as providing peace of mind for unrecoverable hard drive failure of your
master server knowing that you have a perfect recent if not exact copy of ALL
databases on the slave.
Scenario
Hardware/software scenario for these instructions was Apple XServes running OS X Server 10.3.4 (Darwin Unix version 7.4.0) and MySQL 4.0.20 standard binary installation. MySQL resides at /usr/local/mysql and the global my.cnf file is at /etc/my.cnf. I use the default (bash) shell.
The master has been running happily on its own dedicated XServe (serving mostly WebObjects applications) and needs a backup solution that takes an exact copy once per night of the master server without ever shutting down the master. Another XServe that acts as a fileserver has plenty of capacity to become a MySQL slave.
These instructions involve shutting down the maser one time long enough to copy the contents of the mysql/data directory across the network to the slave. This was quick in my case since all the servers share the same gigabit subnet and the databases were not too large. You also need root privileges on both mysql and the servers themselves. All command line args beginning with # below signify that server root user is logged in. If not logged in as root, you need to constantly do sudo and enter password which adds unnecessary fluff to these instructions. But be careful ..... root has "no questions asked' power!
IMPORTANT: This also assumes that /usr/local/mysql/bin is the leftmost path in your shell PATH variable. This is required to make sure your mysql commands work on the binary installation and not the "bundled" mysql that ships preinstalled in Darwin and NOT installed in /usr/local/mysql.
Solution Overview
1) Preparing the slave
2) Prepare the master
3) Shut down the master MySQL
4) Copy the data directory from master to slave
5) Restart the master and verify the creation of a binary log
6) Finish configuring the slave
7) Start the slave and verify replication
Instructions
Installing MySQL
Simply download the binary installer package and run the installer for mysql and then run the installer for the Startup Item. DO NOT configure or startup mysqld yet!
Login to slave as root
% su root (locally) or % ssh root @slave-ip-address (remotely)
If necessary, edit /etc/profile so that your PATH variable begins with /usr/local/mysql/bin and then log out and in again
Delete the mysql newly installed data directory since we will be copying over the master's data directory
# cd /usr/local/mysql
# rm -r ./data
Decide right now on a special user and password for replication, let's say repluser and replpassword.
Also decide right now on a special user and password for backup shutdown/startup, let's say backuser and backpassword
(You can substitute your own passwords!)
Next prepare the slave config file
# pico /etc/my.cnf
Enter the following slave configuration parameters
[client]
# this default slave mysql user only has SHUTDOWN privilege allowing the backup script on the
# slave to shutdown mysqld without providing a username and password
user = backuser
password = backpassword
[mysqld]
# I use the IP address of the server for server-id
#log-bin = /var/db/repl/binary-log
# Using last portion of this machines IP for server-id
server-id = 143
# This is the master details (NOTE master-host is MASTER IP address)
master-host = 192.168.1.241
master-user = repluser
master-password = replpassword
master-port = 3306
Next save file and close pico
<ctrl-o> and <ctrl-x>
.... and that's it for now on the slave. We will come back later to finish with the slave.
Preparing the Master
Login to master as root. Let mysql server continue running for now.
Next create a directory owned by mysql user for storing the master binary log (we don't want to have it in the default location of the data directory.
# cd /var/db
# mkdir repl
# chown -R mysql:wheel repl
Next update the master my.cnf file using pico text editor
# pico /etc/my.cnf
Now add these lines to the [mysqld] parameters
[mysqld]
# This turns on binary logging and determines the pathname of the log
log-bin = /var/db/repl/binary-log
# server-id should be a unique id between 1 and 2^32 - 1
# I used the last portion of the IP address of this server
server-id = 241
Next save file and close pico
<ctrl-o> and <ctrl-x>
DO NOT restart the master mysqld yet! We want this my.cnf to be read only after we stop and copy the master data to the slave so that replication begins on identical copies of the databases.
Now log into mysql to add the repluser and backuser. Note that while we are creating these two users on the master, they will really be used on the slave ..... but remember that the master will soon be copied to the slave just before we begin replicating and these users and privileges will be mirrored on the slave after we copy over.
Note the following GRANT statements assume your subnet with the mysql servers have IP addresses beginning with 192.168.1. Change as appropriate for your situation.
# mysql -u root -p
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY 'replpassword';
mysql> GRANT SHUTDOWN ON *.* TO 'backuser'@'192.168.1.%' IDENTIFIED BY 'backpassword';
Check connections and decide when to shutdown the mysql server
mysql> SHOW PROCESSLIST;
Shutting Down the Master
When ready to shutdown...
mysql> EXIT;
# mysqladmin -u root -p shutdown
Copying the mysql data directory to the slave
When mysqld has ended we will use scp to copy the data folder to the slave
# scp -r /usr/local/mysql/data root@slave-ip-address:/usr/local/mysql
When finished copying we can restart the master. And don't worry about the slave which is still not started. If the master is binary logging after we restart, the slave will read the log and catch up to synchronize.
Restarting the Master
# echo
# mysqld_safe &
<press return key>
Now check if binary logging is working
# cd /var/db/repl
# ls -al
You should see a file named binary-log.001 .... if not you have got to troubleshoot it and fix it and then delete the slave data directory and shutdown the master and copy over the data directory again before restarting. The only problem I had when I first did this was that I had a binary log name in my cnf file that mysql just did not like, so initially use "binary-log" which is sure to work.
drwxr-xr-x 5 mysql wheel 170 24 Jun 09:32 .
drwxr-xr-x 23 root wheel 782 24 Jun 09:32 ..
-rw-rw---- 1 mysql wheel 20041 23 Jun 10:35 binary-log.001
-rw-rw---- 1 mysql wheel 56 24 Jun 09:33 binary-log.index
You can examine the file like this:
# mysqlbinlog binary-log.001
If you wish log into mysql and create a test database, add a table and add a record. then log out and examine the binary log and you will see the SQL commands in there ready for the slave to execute.
If binary logging is working its time to finish with the slave
Finish configuring the slave
First fix privileges on the data folder that we copied over
# cd /usr/local/mysql
# chown -R mysql:wheel data
Verify privileges if you wish...
# ls -al ./data
Now start the slave...
# echo
# mysqld_safe &
<press return>
When the slave has started, log into it and check that the test SQL stuff you did on the master has replicated. BUT DO NOT run SQL statements on the slave yourself that would jeopardize the integrity of the slave being an exact copy. If you wish create a read only user on the MASTER and then log into the slave using the read only user to verify replication.
In addition you can go into the /usr/local/mysql/data directory and you will see the relay log. Also check out the online MASTER and SLAVE SQL commands for checking status etc.
If it's working then congratulations! if not .... then troubleshoot it or email the mysql list.
For further details on replication and configuration of other useful replication setups, you should buy the excellent new book by Jeremy Zawodny and see Chapter 7.... and don't say you cannot afford $27 for a book that can save you 100's of manhours trying to figure out advanced topics like replication in MySQL!!
FYI, for backing up the slave, simply put
mysqladmin shutdown
at the beginning of your backup script (which should copy the mysql data directory somewhere and archive it or whatever) and put
mysqld_safe > /dev/null &
at the end of the backup script and have cron execute the task at night.
Hardware/software scenario for these instructions was Apple XServes running OS X Server 10.3.4 (Darwin Unix version 7.4.0) and MySQL 4.0.20 standard binary installation. MySQL resides at /usr/local/mysql and the global my.cnf file is at /etc/my.cnf. I use the default (bash) shell.
The master has been running happily on its own dedicated XServe (serving mostly WebObjects applications) and needs a backup solution that takes an exact copy once per night of the master server without ever shutting down the master. Another XServe that acts as a fileserver has plenty of capacity to become a MySQL slave.
These instructions involve shutting down the maser one time long enough to copy the contents of the mysql/data directory across the network to the slave. This was quick in my case since all the servers share the same gigabit subnet and the databases were not too large. You also need root privileges on both mysql and the servers themselves. All command line args beginning with # below signify that server root user is logged in. If not logged in as root, you need to constantly do sudo and enter password which adds unnecessary fluff to these instructions. But be careful ..... root has "no questions asked' power!
IMPORTANT: This also assumes that /usr/local/mysql/bin is the leftmost path in your shell PATH variable. This is required to make sure your mysql commands work on the binary installation and not the "bundled" mysql that ships preinstalled in Darwin and NOT installed in /usr/local/mysql.
Solution Overview
1) Preparing the slave
2) Prepare the master
3) Shut down the master MySQL
4) Copy the data directory from master to slave
5) Restart the master and verify the creation of a binary log
6) Finish configuring the slave
7) Start the slave and verify replication
Instructions
Installing MySQL
Simply download the binary installer package and run the installer for mysql and then run the installer for the Startup Item. DO NOT configure or startup mysqld yet!
Login to slave as root
% su root (locally) or % ssh root @slave-ip-address (remotely)
If necessary, edit /etc/profile so that your PATH variable begins with /usr/local/mysql/bin and then log out and in again
Delete the mysql newly installed data directory since we will be copying over the master's data directory
# cd /usr/local/mysql
# rm -r ./data
Decide right now on a special user and password for replication, let's say repluser and replpassword.
Also decide right now on a special user and password for backup shutdown/startup, let's say backuser and backpassword
(You can substitute your own passwords!)
Next prepare the slave config file
# pico /etc/my.cnf
Enter the following slave configuration parameters
[client]
# this default slave mysql user only has SHUTDOWN privilege allowing the backup script on the
# slave to shutdown mysqld without providing a username and password
user = backuser
password = backpassword
[mysqld]
# I use the IP address of the server for server-id
#log-bin = /var/db/repl/binary-log
# Using last portion of this machines IP for server-id
server-id = 143
# This is the master details (NOTE master-host is MASTER IP address)
master-host = 192.168.1.241
master-user = repluser
master-password = replpassword
master-port = 3306
Next save file and close pico
<ctrl-o> and <ctrl-x>
.... and that's it for now on the slave. We will come back later to finish with the slave.
Preparing the Master
Login to master as root. Let mysql server continue running for now.
Next create a directory owned by mysql user for storing the master binary log (we don't want to have it in the default location of the data directory.
# cd /var/db
# mkdir repl
# chown -R mysql:wheel repl
Next update the master my.cnf file using pico text editor
# pico /etc/my.cnf
Now add these lines to the [mysqld] parameters
[mysqld]
# This turns on binary logging and determines the pathname of the log
log-bin = /var/db/repl/binary-log
# server-id should be a unique id between 1 and 2^32 - 1
# I used the last portion of the IP address of this server
server-id = 241
Next save file and close pico
<ctrl-o> and <ctrl-x>
DO NOT restart the master mysqld yet! We want this my.cnf to be read only after we stop and copy the master data to the slave so that replication begins on identical copies of the databases.
Now log into mysql to add the repluser and backuser. Note that while we are creating these two users on the master, they will really be used on the slave ..... but remember that the master will soon be copied to the slave just before we begin replicating and these users and privileges will be mirrored on the slave after we copy over.
Note the following GRANT statements assume your subnet with the mysql servers have IP addresses beginning with 192.168.1. Change as appropriate for your situation.
# mysql -u root -p
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY 'replpassword';
mysql> GRANT SHUTDOWN ON *.* TO 'backuser'@'192.168.1.%' IDENTIFIED BY 'backpassword';
Check connections and decide when to shutdown the mysql server
mysql> SHOW PROCESSLIST;
Shutting Down the Master
When ready to shutdown...
mysql> EXIT;
# mysqladmin -u root -p shutdown
Copying the mysql data directory to the slave
When mysqld has ended we will use scp to copy the data folder to the slave
# scp -r /usr/local/mysql/data root@slave-ip-address:/usr/local/mysql
When finished copying we can restart the master. And don't worry about the slave which is still not started. If the master is binary logging after we restart, the slave will read the log and catch up to synchronize.
Restarting the Master
# echo
# mysqld_safe &
<press return key>
Now check if binary logging is working
# cd /var/db/repl
# ls -al
You should see a file named binary-log.001 .... if not you have got to troubleshoot it and fix it and then delete the slave data directory and shutdown the master and copy over the data directory again before restarting. The only problem I had when I first did this was that I had a binary log name in my cnf file that mysql just did not like, so initially use "binary-log" which is sure to work.
drwxr-xr-x 5 mysql wheel 170 24 Jun 09:32 .
drwxr-xr-x 23 root wheel 782 24 Jun 09:32 ..
-rw-rw---- 1 mysql wheel 20041 23 Jun 10:35 binary-log.001
-rw-rw---- 1 mysql wheel 56 24 Jun 09:33 binary-log.index
You can examine the file like this:
# mysqlbinlog binary-log.001
If you wish log into mysql and create a test database, add a table and add a record. then log out and examine the binary log and you will see the SQL commands in there ready for the slave to execute.
If binary logging is working its time to finish with the slave
Finish configuring the slave
First fix privileges on the data folder that we copied over
# cd /usr/local/mysql
# chown -R mysql:wheel data
Verify privileges if you wish...
# ls -al ./data
Now start the slave...
# echo
# mysqld_safe &
<press return>
When the slave has started, log into it and check that the test SQL stuff you did on the master has replicated. BUT DO NOT run SQL statements on the slave yourself that would jeopardize the integrity of the slave being an exact copy. If you wish create a read only user on the MASTER and then log into the slave using the read only user to verify replication.
In addition you can go into the /usr/local/mysql/data directory and you will see the relay log. Also check out the online MASTER and SLAVE SQL commands for checking status etc.
If it's working then congratulations! if not .... then troubleshoot it or email the mysql list.
For further details on replication and configuration of other useful replication setups, you should buy the excellent new book by Jeremy Zawodny and see Chapter 7.... and don't say you cannot afford $27 for a book that can save you 100's of manhours trying to figure out advanced topics like replication in MySQL!!
FYI, for backing up the slave, simply put
mysqladmin shutdown
at the beginning of your backup script (which should copy the mysql data directory somewhere and archive it or whatever) and put
mysqld_safe > /dev/null &
at the end of the backup script and have cron execute the task at night.
Posted: Thursday - June 24, 2004 at 03:25 PM
Quick Links
Statistics
Total entries in this blog:
Total entries in this category:
Published On: Dec 29, 2008 02:27 PM
Total entries in this category:
Published On: Dec 29, 2008 02:27 PM