How do you set up master-slave replication in MySQL


Before we go into how to set up master-slave replication in MySQL, let us talk about some of the reasons we have set up master-slave replication using MySQL.

Security
You can have replicated servers that you can take backups without messing around with the production ones.

Performance
MySQL allows slave nodes to have read access on the replicated databases. This means, that you can load balance the requests so that the master will handle the write requests while the slave(s) will manage the reading.

Here is a quick setup of a simple MySQL master/slave replication.

Master Node Setup
When the slave connects to the master, it must authenticate itself just like any other MySQL client, so it needs a username and password. So, let’s create the account and give it only the minimum privileges necessary for replication: REPLICATION SLAVE and REPLICATION CLIENT.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'myslaveuser'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

The next step create one database and table for test.

Now we jump to the configuration file located at ‘/etc/my.cnf’ and insert the following lines after the ‘mysqld’ tag.

[mysqld]
log-bin=mysqlbin
binlog-do-db=dbname
server-id=1

The first one (log-bin) will enable the binary logging feature and use the provided name for it. The second one is used to name the database that the log will monitor. And the last one to identify this server with an ID during the MySQL communication between the two (or more) nodes. After doing this, restart the service…

And execute the following command

mysql> SHOW MASTER STATUS;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysqlbin.000004 |       98 | dbname       |                  | 
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

That shows the binary log file name as well as what databases are affected by it and what is the master’s current position in that file. Now we are pretty much done with the master setup. We can move to the slave node now…

Slave Node Setup
Copy the master database to Slave. And you reconfigure the ‘/etc/my.cnf’ file to include the following information:

[mysqld]
log-bin
replicate-ignore-db=mysql
log-slave-updates
log-warnings

report-host=192.168.0.2     # Slave's IP or hostname
server-id=2
master-host=192.168.0.1    # Master's IP or hostname
master-user=myslaveuser    # Slave user created on master node
master-password=password   # Slave user's password on master node
master-connect-retry=60
master-port-3306           # Master's MySQL listening port
replicate-do-db=dbname # Which database to replicate from the master

Now restart the service and login to the slave’s MySQL shell and issue the following commands…

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000004', MASTER_LOG_POS=98;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

So, you basically done at this point. You can also use the next command to have a brief overview of what’s going on on the slave node…

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.1
                Master_User: myslaveuser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqlbin.000004
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000005
              Relay_Log_Pos: 234
      Relay_Master_Log_File: mysqlbin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: example_db
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 234
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

The two most important fields are Slave_IO_Running and Slave_SQL_Running. They tell you if the IO and slave threads are running.

If we jump back to the master node now and login to its MySQL shell we can see the connected slave like this:

mysql> SHOW SLAVE HOSTS;
+-----------+-------------+------+-------------------+-----------+
| Server_id | Host        | Port | Rpl_recovery_rank | Master_id |
+-----------+-------------+------+-------------------+-----------+
|         2 | 192.168.0.2 | 3306 |                 0 |         1 | 
+-----------+-------------+------+-------------------+-----------+
1 row in set (0.00 sec)

So, that was it. You can now use the master node as a common MySQL database server and see the replicated records on the slave.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s