Configure Master Slave Replication in MySQL

How to configure master slave replication in MySQL? 

Master Server : MySQLServer (192.168.1.10)

Slave Server : mysqlrep01 (192.168.1.11)

Tips: (Commands for Master)

• show processlist;
• select @@slave_parallel_workers;
• show master status;

Following are the steps to configure the replication in MySQL.

**On Master Server:

A. Port Access

root@MySQLServer:~# apt-get install net-tools

Login into mysql and check for the bind address.

mysql> select @@bind_address;
+----------------+
| @@bind_address |
+----------------+
| * |
+----------------+
1 row in set (0.01 sec)

mysql>

Add bind-address and server-id parameters in the my.cnf file.

root@MySQLServer:~# cat /etc/my.cnf
[mysqld]
basedir=/u01/mysql/base
datadir=/u01/mysql/data
log_error=/u01/mysql/logs/error.log
port=3306
socket=/u01/mysql/data/mysql.sock
bind-address=0.0.0.0 
server-id=100 
root@MySQLServer:~#

Check for the port access.

root@MySQLServer:~# netstat -tulnp |grep 3306
tcp6 0 0 :::3306 :::* LISTEN 57019/mysqld
tcp6 0 0 :::33060 :::* LISTEN 57019/mysqld
root@MySQLServer:~#
root@MySQLServer:~#
root@MySQLServer:~# systemctl restart mysql
root@MySQLServer:~#
root@MySQLServer:~# netstat -tulnp |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 57725/mysqld
tcp6 0 0 :::33060 :::* LISTEN 57725/mysqld
root@MySQLServer:~#

Now add the server ip as the bind-address in the my.cnf file.
bind-address=192.168.1.10 

root@MySQLServer:~# cat /etc/my.cnf
[mysqld]
basedir=/u01/mysql/base
datadir=/u01/mysql/data
log_error=/u01/mysql/logs/error.log
port=3306
socket=/u01/mysql/data/mysql.sock
bind-address=192.168.1.10 
server-id=100

root@MySQLServer:~# netstat -tulnp |grep 3306
tcp 0 0 192.168.1.10:3306 0.0.0.0:* LISTEN 58031/mysqld
tcp6 0 0 :::33060 :::* LISTEN 58031/mysqld
root@MySQLServer:~#

B. Create db user in Master for replication.

mysql> create user repl@'%' identified by 'repl';
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> grant replication client, replication slave on *.* to repl;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

C. Test user login from Slave/ Target.

root@Server:~# mysql -h192.168.1.10 -urepl -p
Enter password:

mysql> select @@hostname;
+-------------+
| @@hostname |
+-------------+
| MySQLServer |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> select user();
+----------------------+
| user() |
+----------------------+
| repl@192.168.1.11 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql> show grants;
+------------------------------------------------------------------+
| Grants for repl@% |
+------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%` |
+------------------------------------------------------------------+
1 row in set (0.01 sec)

mysq

==> Set server-id in my.cnf on both Master and Slave.

**On Slave Server

mysql> change master to master_user='repl',master_password='repl',master_host='192.168.1.10',master_port=3306,master_log_file='binlog.000009',master_log_pos=157,get_master_public_key=1;

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000009
Read_Master_Log_Pos: 157
Relay_Log_File: Server-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000009
Slave_IO_Running: No
Slave_SQL_Running: No


mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000009
Read_Master_Log_Pos: 157
Relay_Log_File: Server-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

**We can make Slave as READ-ONLY in case use for reporting.

1. Check status:

mysql> select @@read_only, @@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
| 0 | 0 |
+-------------+-------------------+
1 row in set (0.00 sec)

mysql>

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global super_read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select @@read_only, @@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
| 1 | 1 |
+-------------+-------------------+
1 row in set (0.01 sec)

mysql>

==> Monitor Replication on SLAVE:

mysql> use performance_schema
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>
mysql> show tables like 'repl%';
+------------------------------------------------------+
| Tables_in_performance_schema (repl%) |
+------------------------------------------------------+
| replication_applier_configuration |
| replication_applier_filters |
| replication_applier_global_filters |
| replication_applier_status |
| replication_applier_status_by_coordinator |
| replication_applier_status_by_worker |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_connection_configuration |
| replication_connection_status |
| replication_group_member_stats |
| replication_group_members |
+------------------------------------------------------+
12 rows in set (0.02 sec)

mysql>