MYSQL: HowTo Set Up Master Slave Replication
Through MySQL online replication process we could maintain a multiple copy of Master MySQL database machine save in salves MySQL database Machine automatically. This prevent long delay of restoring backup in case of Master MySQL database machine crash. In this post we will see HowTo Set Up Master Slave Replication.
Below are setup details for this Post.
We are using two Hosts srv7-master and srv7-sec
srv7-master — 192.168.122.109
srv7-sec — 192.168.122.121
Master MySQL Server details
[root@srv7-master ~]# cat /etc/redhat-release CentOS Linux release 7.3.1611 (Core) [root@srv7-master ~]# uname -r 3.10.0-514.26.2.el7.x86_64 [root@srv7-master ~]# mysqlcheck --version mysqlcheck Ver 2.5.1 Distrib 5.7.19, for Linux (x86_64)
Slave MySQL Server details
[root@srv7-sec ~]# cat /etc/redhat-release CentOS Linux release 7.3.1611 (Core) [root@srv7-sec ~]# uname -r 3.10.0-514.26.2.el7.x86_64 [root@srv7-sec ~]# mysqlcheck --version mysqlcheck Ver 2.5.1 Distrib 5.7.19, for Linux (x86_64)
In our earlier post we saw how can we update MySQL on CentOS/RHEL 7 from MySQL yum repository. So we are working on latest version of MySQL for this post.
Configure Master Server
For configure Master MySQL Server, we need to edit my.cnf file, this is configuration file of MySQL. Open /etc/my.cnf file in your editor.
bind-address=0.0.0.0 server-id=1 log_bin=/var/lib/mysql/mysql-bin.log binlog_do_db=zabbix
In above Derivatives, we tried to implement required parameters for Master MySQL Server. Let’s discuss it.
"bind-address -- 0.0.0.0" we bind MySQL process to all Internet protocol address assigned on machine. "Server-id -- 1" denote that this machine will act as Master Server in replication. "Log_bin -- /var/log/mysql/mysql-bin.log" mentioned path and prefix for bin log files. These binary log contains all sql query which change database. "binlog_do_db -- zabbix" contain database name that should cover for bin log.
Now we need to restart MySQL Server on Master MySQL machine. You would see log-bin files in mentioned location after this.
[root@srv7-master mysql]# pwd /var/lib/mysql [root@srv7-master mysql]# ls -lh mysql-bin.* -rw-r----- 1 mysql mysql 808K Aug 18 12:57 mysql-bin.000001 -rw-r----- 1 mysql mysql 32 Aug 18 12:22 mysql-bin.index
Now we need to create MySQL user for Salve machine that would send data towards Slave MySQL machine to replicate data on that database.
[root@srv7-master mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 617 Server version: 5.7.19-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.122.121' IDENTIFIED BY 'Passw0rd'; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.02 sec) mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 389203 | zabbix | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
Don’t exit above MySQL console, Exit it once MySQL Slave configuration is complete .
Now take backup of database from another console of Master Server, so we could restore it in Slave MySQL Machine. Online MySQL replication work only to execute change in table of respective database. It Will not create any database and tables created before or while replication, we have to create same replica of database and tables.
[root@srv7-master ~]# mysqldump -u root -p zabbix > zabbix.sql Enter password: [root@srv7-master ~]# ls -lh zabbix.sql -rw-r--r-- 1 root root 12M Aug 18 17:46 zabbix.sql
Configure Slave Server
As of now we have configured Master MySQL Server, Now we need to configure Slave Server for replication. First of all we need to create database on Slave MySQL machine so that we could save changes that replicate from Master Server.
mysql> create database zabbix;
Now we need to edit Slave my.cnf file in such way that we did in Master MySQL server.
server-id=2 relay-log=/var/lib/mysql/mysql-relay-bin.log log_bin=/var/lib/mysql/mysql-bin.log binlog_do_db=zabbix
Restore database backup which we had taken above.
[root@srv7-sec ~]#mysql -u root zabbix -p < zabbix.sql Enter password:
Now restart MySQL process in MySQL Slave server.
[root@srv7-sec ~]# systemctl restart mysql
Now we have to provide user information of replication user that we have created on Master MySQL Server.
Login on Slave mySQL Server
mysql >CHANGE MASTER TO MASTER_HOST='192.168.122.109',MASTER_USER='replicator', MASTER_PASSWORD='Passw0rd', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=389203;
Now start Slave process
mysql> start slave
Now exit from Master Server MySQL console where we out read lock and check Master status, it should increase position as usual.
Now we should also check Slave MySQL status as well like below
root@srv7-sec ~]# watch -n 0.1 'mysql -e "SHOW SLAVE STATUS\G"'
Below values woudl continually change
Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 3578115 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 653020 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_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: 3578115 Relay_Log_Space: 3189605
MySQL replication has lot more option, this is just brief overview on same.we will going to post some other post on same covering other options with details for MySQL replication.