"Setting up a MySQL Replication Slave" Revisited for MySQL 4.1
Ah well, since my first article on this, MySQL
has had multiple version changes and I have gained more experience in using
MySQL. (Off topic...... I really like the fact that I am stuck in everything
from MySQL server config/support thru Apache thru WebObjects/Java development,
deployment and administration ...... never a dull day in the job!). Here is my
"new improved" process for setting up a replication master-slave configuration
using MySQL 4.1.21. These instructions will definitely not work for version
4.0.XX and earlier and may not work for some earlier versions of 4.1. Earlier
version incompatabilities are mostly related to the parameters used in
mysqldump.
This article assumes a basic knowledge of unix (cd, ssh, scp, mkdir, chown) and a basic knowledge of mysql (mysqld, mysql, mysqldump, mysqladmin)
This article assumes a basic knowledge of unix (cd, ssh, scp, mkdir, chown) and a basic knowledge of mysql (mysqld, mysql, mysqldump, mysqladmin)
Scenario
Master config:
MySQL 4.1.21 on XServe Dual G5 2.0GHz headless running OS 10.4.8 *client* (Why waste a good OS X Server license on a machine that runs MySQL exclusively!)
The master has multiple databases, some with InnoDB transaction engine tables and others with MyISAM tables.
Slave Config: MySQL 4.1.21 on non-dedicated XServe G4 1.33 GHz running OS X Server 10.3.9. It runs MySQL as a slave and does double duty on a few other inhouse server services.
These instructions involve restarting the master IF binary logging has to be established. If binary logging is already established, then a master restart is not necessary. Also, because we have a mix of MyISAM and InnoDB databases, the process involves taking a global read lock on all the master's databases for the duration of the dump, so all database writes will be blocked during that time. It might be 10 or more minutes to dump depending on the amount of data. Dumping to a separate physical drive such as an external firewire drive may be faster and more convenient since the drive can be used to move the dump to the slave rather than copying across the network.(
TIP for servers with all InnoDB databases: If all your databases are innodb and no changes are made to the mysql privileges (since the internal mysql db is MyISAM) during the dump, then the single-transaction parameter could be used for the dump instead of the global lock with a result that this could be done without interfering with live updates to the master.
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 pre-installed in OS X Server and NOT installed in /usr/local/mysql.
Solution Overview
1) Preparing the slave
2) Prepare the master
3) Taking the master data snapshot
4) Loading the master data snapshot into the slave
5) Starting replication
Instructions
Installing MySQL on the slave
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!
Decide right now on a special user and password for replication, let's say repluser and replpassword, and write these down.
Also decide right now on a special user and password for backup operations since we plan to perform rolling backup archives from the slave, let's say backuser and backpassword. Write these down too.
(You can substitute your own passwords!)
Next prepare the slave config file using pico and save it to /etc/my.cnf
Here is my slave config file
# /etc/my.cnf config for slave mysql server on XMain
[client]
# backup user needs the following privileges
# SELECT - creating the dump statements
# LOCK TABLES - needed by mysqldump I believe
# RELOAD - definitely needed for flush-XXX
# SHUTDOWN - needed to shutdown in case of binary file backups
# REPLICATION CLIENT - allows me to log in as default backup user and SHOW MASTER/SLAVE STATUS - good for automated email reports
# PROCESS - SHOW PROCESSLIST command
# SHOW DATABASES - list the databases
user = backuser
password = backpassword
[mysqld]
# ======= BEGIN GLOBAL SETTINGS ===================================================
# This variable should be as big as the biggest BLOB value we plan to use
max_allowed_packet = 16M
# No query cache
query_cache_type = 0
query_cache_size = 0
# New tables are innodb unless specified otherwise (Before MySQL 4.1.2 use default-table-type)
default-storage-engine=innodb
# ======= BEGIN REPLICATION SLAVE SETTINGS ===================================================
# http://dev.mysql.com/doc/refman/4.1/en/replication-options.html
# Using this machines IP for server-id
server-id = 243
# the name of the slave host
report-host=slaveserver.local
# allows only updates from slave threads and users with SUPER privilege
read-only
# This is the master details
master-host = masterserver.local
master-user = repluser
master-password = replpassword
master-port = 3306
# ======= BEGIN MYISAM SETTINGS ===================================================
# Buffer for REPAIR TABLE or CREATE INDEX operations on MyISAM tables
myisam_sort_buffer_size = 16M
# ======= BEGIN INNODB SETTINGS ===================================================
# Create a separate idb file per table
innodb_file_per_table
# Buffer for innodb index and table cache performance
innodb_buffer_pool_size = 64M
Finally lets launch mysql on the slave making sure that replication is off.
$ sudo echo
$ sudo mysqld_safe --user=mysql --skip-slave-start &
Our slave is now started, replication is off and ready for us to load the initial master data set.
Preparing the Master
Login to master as root. Let mysql server continue running for now.
If binary logging is not set up, then set it up by creating a directory with chown of mysql:wheel for the binary log, let's say
/var/db/repl
and then ensure your /etc/my.cnf have the following two parameters on the master server
# path to binary-log file
log-bin=/var/db/repl/binary-log
# A server-id unique to your master-slave replication partner machines
server-id = 1141
If you had to establish binary logging (the log-bin property), well now is the time to shutdown and restart mysqld on your master so that the config file takes effect.
$ mysqladmin -u root -p shutdown
$ sudo echo
$ sudo mysqld_safe --user=mysql &
Check that binary logging is running by logging into master and doing
> SHOW MASTER STATUS\G
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 SELECT, RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backuser'@'localhost' IDENTIFIED BY 'backpassword';
Taking the Master Snapshot
ssh into the master and cd into the dump destination directory
This is going to put a global read lock on all databases so no updates will take place for the duration of the dump. 2GB of data may take up to 10 minutes to dump on a typical G4/G5 XServe. Doing this at a known time when there is low update activity would be good ..... in any case, writes will be blocked during the dump.
$ mysqldump -u root -p --all-databases --extended-insert --add-locks --lock-all-tables --flush-logs --master-data=1 --add-drop-table --create-options --quick --disable-keys --quote-names > slavesetup.sql
The master will keep on running after the dump, but our --master-data option will have placed the master log name and position at the beginning of the dump file, so that the slave knows where to start replicating when we start it later.
Loading the master data dump into the slave
Copy the dump file to the slave.
Remember if this is a new slave installation, the root has no password, in which case just hit <return> key instead of entering a password
$ mysql -u root -p < slavesetup.sql
Important: Since we just replaced the mysql internal database on the slave with the one form the master, we need to flush the privileges so that the privileges on the slave are now identical to the master. Execute the following remembering that you still have the original slave root empty password in memory, so hit return key only when prompted for a password for this command
$ mysqladmin -u root -p flush-privileges
Starting Replication
Now we just log into the slave as root using the SAME password as root has on the master since we are now working on a copy of the master and start the slave
> START SLAVE;
That's it, we're done. The slave will catch up with the master.... actually the I/O thread will read the binary-log from the master pretty fast, so you can feel good about your data now being safe on another machine :-)
To check status on the slave, just do...
> SHOW SLAVE STATUS\G
.... also, showing the processlist will reveal two replication threads (user = system user), one for I/O of the binary log and one for performing SQL updates to the slave
>SHOW PROCESSLIST\G
Just great ..... now go take a tea or coffee break and think about your backup strategy. Remember replication provides protected against hardware failure. Rolling backups provide historical data backups or protection against catastrophic failures if your master and slave are colocated in the same physical building. The convenient thing about having replication though is that it does not put time or availability constraints on our backup strategy since we can stop/start slaves and do global locks on slaves without worrying about performance impact to applications.
For further details on replication and configuration of other useful replication setups, you should buy the excellent 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!!
Master config:
MySQL 4.1.21 on XServe Dual G5 2.0GHz headless running OS 10.4.8 *client* (Why waste a good OS X Server license on a machine that runs MySQL exclusively!)
The master has multiple databases, some with InnoDB transaction engine tables and others with MyISAM tables.
Slave Config: MySQL 4.1.21 on non-dedicated XServe G4 1.33 GHz running OS X Server 10.3.9. It runs MySQL as a slave and does double duty on a few other inhouse server services.
These instructions involve restarting the master IF binary logging has to be established. If binary logging is already established, then a master restart is not necessary. Also, because we have a mix of MyISAM and InnoDB databases, the process involves taking a global read lock on all the master's databases for the duration of the dump, so all database writes will be blocked during that time. It might be 10 or more minutes to dump depending on the amount of data. Dumping to a separate physical drive such as an external firewire drive may be faster and more convenient since the drive can be used to move the dump to the slave rather than copying across the network.(
TIP for servers with all InnoDB databases: If all your databases are innodb and no changes are made to the mysql privileges (since the internal mysql db is MyISAM) during the dump, then the single-transaction parameter could be used for the dump instead of the global lock with a result that this could be done without interfering with live updates to the master.
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 pre-installed in OS X Server and NOT installed in /usr/local/mysql.
Solution Overview
1) Preparing the slave
2) Prepare the master
3) Taking the master data snapshot
4) Loading the master data snapshot into the slave
5) Starting replication
Instructions
Installing MySQL on the slave
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!
Decide right now on a special user and password for replication, let's say repluser and replpassword, and write these down.
Also decide right now on a special user and password for backup operations since we plan to perform rolling backup archives from the slave, let's say backuser and backpassword. Write these down too.
(You can substitute your own passwords!)
Next prepare the slave config file using pico and save it to /etc/my.cnf
Here is my slave config file
# /etc/my.cnf config for slave mysql server on XMain
[client]
# backup user needs the following privileges
# SELECT - creating the dump statements
# LOCK TABLES - needed by mysqldump I believe
# RELOAD - definitely needed for flush-XXX
# SHUTDOWN - needed to shutdown in case of binary file backups
# REPLICATION CLIENT - allows me to log in as default backup user and SHOW MASTER/SLAVE STATUS - good for automated email reports
# PROCESS - SHOW PROCESSLIST command
# SHOW DATABASES - list the databases
user = backuser
password = backpassword
[mysqld]
# ======= BEGIN GLOBAL SETTINGS ===================================================
# This variable should be as big as the biggest BLOB value we plan to use
max_allowed_packet = 16M
# No query cache
query_cache_type = 0
query_cache_size = 0
# New tables are innodb unless specified otherwise (Before MySQL 4.1.2 use default-table-type)
default-storage-engine=innodb
# ======= BEGIN REPLICATION SLAVE SETTINGS ===================================================
# http://dev.mysql.com/doc/refman/4.1/en/replication-options.html
# Using this machines IP for server-id
server-id = 243
# the name of the slave host
report-host=slaveserver.local
# allows only updates from slave threads and users with SUPER privilege
read-only
# This is the master details
master-host = masterserver.local
master-user = repluser
master-password = replpassword
master-port = 3306
# ======= BEGIN MYISAM SETTINGS ===================================================
# Buffer for REPAIR TABLE or CREATE INDEX operations on MyISAM tables
myisam_sort_buffer_size = 16M
# ======= BEGIN INNODB SETTINGS ===================================================
# Create a separate idb file per table
innodb_file_per_table
# Buffer for innodb index and table cache performance
innodb_buffer_pool_size = 64M
Finally lets launch mysql on the slave making sure that replication is off.
$ sudo echo
$ sudo mysqld_safe --user=mysql --skip-slave-start &
Our slave is now started, replication is off and ready for us to load the initial master data set.
Preparing the Master
Login to master as root. Let mysql server continue running for now.
If binary logging is not set up, then set it up by creating a directory with chown of mysql:wheel for the binary log, let's say
/var/db/repl
and then ensure your /etc/my.cnf have the following two parameters on the master server
# path to binary-log file
log-bin=/var/db/repl/binary-log
# A server-id unique to your master-slave replication partner machines
server-id = 1141
If you had to establish binary logging (the log-bin property), well now is the time to shutdown and restart mysqld on your master so that the config file takes effect.
$ mysqladmin -u root -p shutdown
$ sudo echo
$ sudo mysqld_safe --user=mysql &
Check that binary logging is running by logging into master and doing
> SHOW MASTER STATUS\G
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 SELECT, RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backuser'@'localhost' IDENTIFIED BY 'backpassword';
Taking the Master Snapshot
ssh into the master and cd into the dump destination directory
This is going to put a global read lock on all databases so no updates will take place for the duration of the dump. 2GB of data may take up to 10 minutes to dump on a typical G4/G5 XServe. Doing this at a known time when there is low update activity would be good ..... in any case, writes will be blocked during the dump.
$ mysqldump -u root -p --all-databases --extended-insert --add-locks --lock-all-tables --flush-logs --master-data=1 --add-drop-table --create-options --quick --disable-keys --quote-names > slavesetup.sql
The master will keep on running after the dump, but our --master-data option will have placed the master log name and position at the beginning of the dump file, so that the slave knows where to start replicating when we start it later.
Loading the master data dump into the slave
Copy the dump file to the slave.
Remember if this is a new slave installation, the root has no password, in which case just hit <return> key instead of entering a password
$ mysql -u root -p < slavesetup.sql
Important: Since we just replaced the mysql internal database on the slave with the one form the master, we need to flush the privileges so that the privileges on the slave are now identical to the master. Execute the following remembering that you still have the original slave root empty password in memory, so hit return key only when prompted for a password for this command
$ mysqladmin -u root -p flush-privileges
Starting Replication
Now we just log into the slave as root using the SAME password as root has on the master since we are now working on a copy of the master and start the slave
> START SLAVE;
That's it, we're done. The slave will catch up with the master.... actually the I/O thread will read the binary-log from the master pretty fast, so you can feel good about your data now being safe on another machine :-)
To check status on the slave, just do...
> SHOW SLAVE STATUS\G
.... also, showing the processlist will reveal two replication threads (user = system user), one for I/O of the binary log and one for performing SQL updates to the slave
>SHOW PROCESSLIST\G
Just great ..... now go take a tea or coffee break and think about your backup strategy. Remember replication provides protected against hardware failure. Rolling backups provide historical data backups or protection against catastrophic failures if your master and slave are colocated in the same physical building. The convenient thing about having replication though is that it does not put time or availability constraints on our backup strategy since we can stop/start slaves and do global locks on slaves without worrying about performance impact to applications.
For further details on replication and configuration of other useful replication setups, you should buy the excellent 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!!
Posted: Friday - October 27, 2006 at 09:51 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