1. Setup Overview
Here is a quick setup of a simple MySQL master/slave replication using SLES 11.1 on two 64-bit x86 systems.
2. Common Setup on Master and Slave Servers
MySQL Server 5.0.67 comes pre-installed with the SLES-11.1 operating system.
Firstly, ensure that MySQL server service always runs on both servers in the desired run level. To verify this, execute the following command.
chkconfig --list | grep mysql
If this results in ‘off’ status for all the run levels, then turn on ‘mysql’ to run always in run level 3, 4, and 5, by executing the following command.
chkconfig -s mysql 345
Check if ‘/var/lib/mysql’ folder exists. This is the folder where all the databases will be stored. If yes, ensure that the ownership of this folder if to user ‘mysql’ and group ‘mysql’.
Change ownership of these folders for user ‘mysql’.
chown -Rf mysql:mysql /var/lib/mysql
Start the MySQL service by the running the following command.
/etc/init.d/mysql start
Set the MySQL administrator password for the first time.
mysqladmin -u root password 'mypasswd'
Before proceeding to server specific settings, ensure that the above common configuration is complete on both the servers.
3. MySQL Master Configuration
On the replication ‘master’, binary logging must be enabled and a unique server ID should be established. Edit the MySQL configuration file, ‘/etc/my.cnf’ to reflect the following changes under ‘[mysqld]’ section.
Uncomment line 53 to read as,
log-bin=mysql-bin
Ensure that the line containing ‘server-id’ is uncommented and equals to ‘1’.
server-id=1
After making the changes, restart the service.
/etc/init.d/mysql restart
MySQL ‘slave’ must connect to the ‘master’ using a MySQL user name and password, so there must be a user account on the ‘master’ that the ‘slave’ can use to connect. The account also needs the ‘REPLICATION SLAVE’ privilege, as it is solely used for replication purpose. Issue these statements on the ‘master’.
mysql -p
mysql> CREATE USER 'slvusr'@'%' IDENTIFIED BY 'myslvpwd';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slvusr'@'%';
To configure replication on the ‘slave’ we must determine the master's current coordinates within its binary log. We will need this information so that when the ‘slave’ starts the replication process, it is able to start processing events from the binary log at the correct point.
To obtain the ‘master’ binary log coordinates, execute the following commands at the 'mysql' prompt.
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 455 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
As it is a fresh MySQL setup that has the default set of databases, there is no need to take a backup or dump any database. We can now configure the ‘slave’ server.
4. MySQL Slave Configuration
On the replication slave, we must establish a unique server ID. Edit the MySQL configuration file, ‘/etc/my.cnf’ to reflect the following changes under ‘[mysqld]’ section.
Ensure that the line containing ‘server-id’ is uncommented and equals to ‘2’.
server-id=2
After making the changes, restart the MySQL service.
/etc/init.d/mysql restart
To set up the ‘slave’ to communicate with the ‘master’ for replication, we must tell the ‘slave’ the necessary connection information. To do this, execute the following statement on the ‘slave’.
mysql -p
mysql> CHANGE MASTER TO MASTER_HOST='master.mydomain.com', MASTER_USER='slvusr', MASTER_PASSWORD='myslvpwd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=455;
Notice that the ‘MASTER_LOG_FILE’ and ‘MASTER_LOG_POS’ values are obtained from the ‘master’ as shown in the steps above. Now, start the slave thread.
mysql> START SLAVE;
After this procedure is performed, the ‘slave’ should connect to the ‘master’ and catch up on any updates that have occurred since the snapshot was taken. To check the ‘slave’ status, run the following command from the ‘mysql’ prompt.
mysql> SHOW SLAVE STATUS;
+----------------------------------+----------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+----------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | master.mydomain.com | slvusr | 3306 | 60 | mysql-bin.000001 | 455 | mysqld-relay-bin.000004 | 235 | mysql-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 455 | 235 | None | | 0 | No | | | | | | 0 |
+----------------------------------+----------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)
For clear understanding, paste the above output in a text editor that supports UNIX type output. It will display a proper table structure with the status information.
5. Verifying the Master Slave Replication
Create a database on the ‘master’ server. We will verify if that database gets replicated over to the ‘slave’ automatically.
mysql -p
mysql> create database vivek;
Query OK, 1 row affected (0.00 sec)
mysql> use vivek;
Database changed
mysql> create table dummy(name varchar(20),id varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into dummy values('test','data');
Query OK, 1 row affected (0.00 sec)
mysql> select * from dummy;
+------+------+
| name | id |
+------+------+
| test | data |
+------+------+
1 row in set (0.00 sec)
Connect to the ‘slave’ server and verify whether the database created on ‘master’ has synchronized.
mysql -p
mysql> use vivek;
Database changed
mysql> select * from dummy;
+------+------+
| name | id |
+------+------+
| test | data |
+------+------+
1 row in set (0.00 sec)
Configuration of MySQL master slave automatic replication of database transactions is now complete.
