Configuring Mysql Master and slave on centos 7
MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). It is usually used to spread read access on multiple servers for scalability, although it can also be used for other purposes such as for failover, or analyzing data on the slave in order not to overload the master.
In this tutorial we will configure mysql master and slave on centos 7 servers.
I am using server details as below.
Master MySQL Server: 192.168.0.10 (mysql Ver 8.0.18)
Slave MySQL Server: 192.168.0.192 (mysql Ver 8.0.18)
Prerequisites
You need to have MySQL Server installed on all servers before you can continue and you can refer the url Install Mysql on centos 7
otherwise follow below steps to install mysql server.
rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm
yum install mysql-server
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqldAlso Read -> How to Setup Syslog server in Centos 7
configure Master
open mysql configuration file like below
[root@lampblogs ~]# vi /etc/my.cnfAdd below lines under mysqld section.
server-id = 1
log-bin=mysql-bin.log
binlog_do_db=replicadb
server-id=1
sync_binlog=1
user=mysql
symbolic-links=0
binlog_format = ROW
max_binlog_size = 500
expire-logs-days = 7
slow_query_logRestart mysql service for changes to take effect.
[root@lampblogs ~]# systemctl restart mysqldCreate replication user on Master db server
mysql> create user 'replica'@'192.168.0.192' identified by 'Ak6$Ft4nIp';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'replica'@'192.168.0.192';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'replica'@'192.168.0.192';
+-------------------------------------------------------------+
| Grants for replica@192.168.0.192 |
+-------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `replica`@`192.168.0.192` |
+-------------------------------------------------------------+
1 row in set (0.00 sec)Also Read -> Installation of Apache MySQL and PHP in centos 7
create database and table and grant privileges
mysql> create database replicadb;
Query OK, 1 row affected (0.01 sec)
mysql> use replicadb;
Database changed
mysql> CREATE TABLE replicatb ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT ALL PRIVILEGES ON replicadb.* to 'replica'@'192.168.0.192';
Query OK, 0 rows affected (0.01 sec)
Lock db tables and backup database
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 937 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Also Read -> How to install Elasticsearch on centos 7
Backup database and copy to slave server
[root@lampblogs ~]# mysqldump -u root -p replicadb > replicadb.sql
Enter password:
[root@lampblogs ~]# rsync replicadb.sql 192.168.0.192:/root/.
root@192.168.0.192's password:
unlock tables in mysql shell
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Configure Slave server
configure slave server by modifying my.conf file
[mysqld]
server-id=2
relay-log=mysql-relay-bin.log
log_bin=mysql-bin.log
binlog_do_db=replicadb
user=mysql
symbolic-links=0
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500
expire-logs-days = 7
slow_query_log
read_only = 1 ---This sets the slave to read-only mode. Only users with super privilege and the replication slave thread will be able to modify data on it. This means there are no applications that can accidentally modify data on the slave instead of master.
log_bin = mysql-bin.log ---This enables binary logging. This is required for acting as a MASTER in a replication configuration. You also need the binary log if you need the ability to do point in time recovery from your latest backup.
Now restart mysql server as below in slave
systemctl restart mysqldRestore database in slave
you might need to create the database before importing the data
mysql> create database replicadb;
Query OK, 1 row affected (0.03 sec)import db with below command
cat replicadb.sql | mysql -u root -p replicadbInitialize Replication process
we are ready to start replication process in slave server
Login into mysql server with root and stop slave
mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)
Then configure Slave server with details obtained from the master status command.
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.10', MASTER_USER='replica', MASTER_PASSWORD='Ak6$Ft4nIp', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=937;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
Now start replication on the slave
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)Then check slave status as below
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.10
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 937
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 322
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: 937
Relay_Log_Space: 530
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 82588126-f96f-11e9-b214-0800278de12a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
In above file you can see Slave_IO_Running and Slave_SQL_Running are 'Yes'
Test Replication
In Master db
mysql> use replicadb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO replicatb ( id, name ) VALUES ( null, 'aa' );
Query OK, 1 row affected (0.05 sec)
mysql> select * from replicatb;
+----+------+
| id | name |
+----+------+
| 1 | aa |
+----+------+
1 row in set (0.00 sec)
Login into slave and check
mysql> use replicadb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from replicatb;
+----+------+
| id | name |
+----+------+
| 1 | aa |
+----+------+
1 row in set (0.00 sec)
That's it. Now we have successfully configured master and slave replication using mysql on centos 7 server.
Also Read -> How to Configure RAID1 on Centos 7
