MySQL Master Slave Replication

I am not going to discuss the advantages and disadvantages of MySQL replication, for that I will write some other time, but some basics are needed

“MySQL Master” is a MySQL database server which we need to replicate to some other place. (you can say Source database server)

“MySQL Slave” is a MySQL database server on which we will replicate data from Master MySQL Server (you can say destination MySQL database server)

Master Server Configuration

First You need to create a directory for MySQL bin-log, I prefer to create in /var/log

#
mkdir /var/log/mysql
chmod 777 /var/log/mysql
#

edit my.cnf (find you my.cnf)

#
nano /etc/my.cnf
#

You my.cnf can be very different, i am using “my-small.cnf” as example. see the highlighted lines

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

server-id       = 1
#log-bin
binlog-do-db=MY_DATABASE_TO_REPLICATE,newdatabase
binlog-ignore-db=mysql
binlog-ignore-db=test
log_bin  = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
innodb_flush_log_at_trx_commit  =1
sync_binlog                     =1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

see the highlighted lines above. lets see what they are

binlog-do-db
the database(s) you want to replicate, you can give a COMA separated databases, multipal databases can also be given by adding binlog-do-db multipal times like

binlog-do-db = database1
binlog-do-db = database2
binlog-do-db = database3
OR
binlog-do-db = database1, database2, database3

I am not going to discuss the other parameters used, just google them 😉

save your my.cnf. and restart the MySQL. and login to MySQL to check things

#
service mysql restart
#

Login to Mysql as root check your master server status.

[root@mysql-master ~]#mysql -uroot -p
Enter password:
mysql>
mysql> show master status;
mysql>

and you will see something like this

mysql> show master status;
+------------------+----------+--------------------------------------+------------------+
| File             | Position | Binlog_Do_DB                         | Binlog_Ignore_DB |
+------------------+----------+--------------------------------------+------------------+
| mysql-bin.000011 |      107 | MY_DATABASE_TO_REPLICATE,newdatabase | mysql,test       |
+------------------+----------+--------------------------------------+------------------+
1 row in set (0.00 sec)

Save the master status, we need this information, and will be used while configuring slave

Now Create the MySQL user for replication, do not forget to replace the [SLAVE_SERVER_IP] with actual slave server IP address

#

CREATE USER 'replication'@'[SLAVE_SERVER_IP]' IDENTIFIED BY '[PASSWORD]';

grant replication slave on *.* to 'replication'@'[SLAVE_SERVER_IP]';

flush privileges;
#

SLAVE Server Configuration
edit the my.cnf for slave server and change the server ID.

nano /etc/my.cnf

find the “server-id=” and change it

server-id=2

save the file and restart the mysql

service mysql restart

Login to MySQL at Slave Server

[root@mysql-slave ~]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@mysql-slave ~]# mysql -uroot -p

once loged in mysql use following query to change the master host and bin-log


CHANGE MASTER TO
	MASTER_HOST='172.18.5.51',
	MASTER_USER='replication',
	MASTER_PASSWORD='[PASSWORD]',
	MASTER_LOG_FILE='mysql-bin.000011',
	MASTER_LOG_POS=107;

in above see the highlights lines, “MASTER_LOG_FILE” and MASTER_LOG_POS, Should be the one which we got from Master server status. (i told you to save the info above)

once the query execution is done,

Create databases which you want to replicate on slave server, replication do not automatically create databases on slave server. use mysqldump to copy the databases.

finally start the slave

mysql>
mysql> start slave;
mysql>

once slave is started, please check the slave status;

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 | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | 172.18.5.50 | replication |        3306 |            60 | mysql-bin.000011 |                 107 | mysql-slave-relay-bin.000047 |           253 | mysql-bin.000011      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 107 |             415 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)

in above output see these things.

Slave_IO_State = Waiting for master to send event
Slave_IO_Running = Yes
Slave_SQL_Running = Yes

we are good to go.

add some data in Master server and it should be immediately available in Slave server (depending upon data size and your server health. 🙂 )

next i will write how to do “Master Master replication”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.