MySQL Replication

Creating a MySQL cluster with real time replication is simple than many think. It has very few config changes and the result is magnificent. You can add as many MySQL slaves as you want to the Master MySQL Server. You have the choice of doing real time replication on a few slaves while you can delay replication on a few slaves. The slaves with delayed replication can act as backup databases in case the master databases corrupt or for some reason you want to know how the database looked a few hours ago. Below is a quick how to on setting up a MySQL cluster in a master-slave configuration

Pre-requisites

  1. Minimum two Ubuntu LTS Servers fully patched with at least 512 GB RAM and 1 core processor. This is a test environment just to showcase how to setup the replication. Actual configuration of the MySQL servers will be dependent on your server sizing and application requirements
  2. Software packages to be installed : sudo apt-get install apache2 php5 phpmyadmin mysql-server-5.6
  3. Once above gets executed, follow it up by running: sudo mysql_secure_installation and follow on screen instructions.Remove test databases, anonymous users, diable remote root logins, etc.
  4. IP Address setup: Static recommended
  5. SSH enabled so that both the database servers are accessible remotely using the terminal or putty (I hate putty)
  6. Ensure you have both database servers with exact same software and patch levels. Generally you should not have an issue, but by ensuring you have same OS version, same kernels, same db version and phpmyadmin version, it becomes simpler to debug issues if you get into trouble.
  7. Server Details
    1. Master server
      1. Hostname: db-master
      2. IP Address: 10.1.5.14
    2. Slave Server
      1. Hostname: db-slave
      2. IP Address: 10.1.5.15
  8. All mysql configurations are made in my.cnf file. Make a copy of that file in case you mess up the file and mysql refuses to start. You should be able to restore mysql to default configuration. The file is located at /etc/mysql

Configuration – Step 1 – Making change in my.cnf file through ssh

Master Server – ssh into the server

sudo nano /etc/mysql/my.cnf
#bind-address       = 127.0.0.1
general_log_file     = /var/log/mysql/mysql.log
#please note if you enable general_log below, you will have mysql.log file created in /var/log/mysql/ and the size of this file will keep increasing as every event gets logged. Enable this only to trouble shoot.
general_log           = 1
log_error               = /var/log/mysql/error.log
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

^O ^X (Save the file and exit)
`sudo service mysql restart’

Slave Server – ssh into the server

sudo nano /etc/mysql/my.cnf
#bind-address       = 127.0.0.1
general_log_file     = /var/log/mysql/mysql.log
#please note if you enable general_log below, you will have mysql.log file created in /var/log/mysql/ and the size of this file will keep increasing as every event gets logged. Enable this only to trouble shoot.
general_log           = 1
log_error               = /var/log/mysql/error.log
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log

^O ^X (Save the file and exit)
`sudo service mysql restart’

Configuration – Step 2 – Making change in phpmyadmin through web browser

Master Server

  • Open browser and type in the address field: http://10.1.5.14/phpmyadmin
  • Login using root as the user name and the password you set when installing mysql server
  • Click on Replication tab on the top and under the master section click on Create a replication user
  • Type a user (typically repl), keep the host as ‘Any Host’, enter password and click on create
  • On the next screen do not make any change and click on Go on the right
  • You have configured the master server

Note: when you login in the phpmyadmin through the webserver, at the bottom you will see an error /warning in pink color regarding php-mcrypt. This module is not enabled by default. in the terminal type the below code

sudo php5enmod mcrypt
sudo service apache2 restart

Slave Server

  • Open browser and type in the address field: http://10.1.5.15/phpmyadmin
  • Login using root as the user name and the password you set when installing mysql server
  • Click on Replication tab on the top and under the slave section click ‘configure’

replication1

 

  • Type the details for the master server and click on Go on the bottom right

replication2

  • The Slave has been configured. In the above picture, ignore the server id as we have already put in the server id in my.cnf file as 2. If you have more slaves, increase the server id by 1 for each slave
  • Refresh the browser and you will see errors as shown below

Click on Control slave option and click on Full Start. Here you will find that the browser window has hung. Refresh the browser window after 5 seconds and you will see that the errors have gone. Click on the See Slave Status table and you should see the below.

Master-rep9.png

The servers are now in clustered mode and real time replication has been enabled between the two servers. Now create a database in the master server and see if it gets replicated in the slave server. You have to follow the same process for adding more slave servers with increasing server-id by one. In the master server phpmyadmin, under replication tab, you will be able to see connected slaves under the master section.

Delaying Replication
For delaying the replication on the slave server by 300 seconds enter the following code on the slave server

sudo mysql -u root -p'
'STOP SLAVE;

CHANGE MASTER TO MASTER_DELAY = 300;
START SLAVE;
sudo service mysql restart

my.cnf configuration file – master

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#bind-address           = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover          = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
log_error = /var/log/mysql/error.log
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[mysql]
[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/

my.cnf configuration file – slave

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir            = /usr/share/mysql
skip-external-locking
#bind-address               = 127.0.0.1
key_buffer                     = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover          = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
general_log_file       = /var/log/mysql/mysql.log
general_log             = 1
log_error                 = /var/log/mysql/error.log
server-id                 = 3
log_bin                   = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[mysql]
[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/

Difference in both files is just the server-id. Everything else is same.

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