HOW TO CONFIGURE MYSQL 8.0 MASTER SLAVE REPLICATIN RHEL 8.
Step 1: Configure the Master Server
The first configuration change to make is setting Server ID for the master database:
A complete simple configuration looks like below:
+++++++++++++++++++++++++++++++++++++++++++++++++++
[root@master ~]# vi /etc/my.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log
save and Restart mysql service for changes to take effect:
[root@master ~]# systemctl restart mysqld.service
Step 2: Create Replication user on Master database server
We now need to create a database user to be used by slaves when connecting. Login to MySQL database as root user and create the user:
[root@master ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.13 Source distribution
Copyright (c) 2000, 2018, 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>create user rpl_user@192.168.142.152 identified by 'pankaj';
Query OK, 0 rows affected (0.08 sec)
Grant the user REPLICATION SLAVE privileges:
mysql> grant replication slave on *.* to rpl_user@192.168.142.152;
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Confirm grants for created user:
mysql> select DISTINCT User FROM mysql.user;
+------------------+
| User |
+------------------+
| slave_user |
| pankaj |
| rpl_user |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
7 rows in set (0.08 sec)
mysql> show grants for rpl_user@192.168.142.152;
+----------------------------------------------------------------+
| Grants for rpl_user@192.168.142.152 |
+----------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rpl_user`@`192.168.142.152` |
+----------------------------------------------------------------+
1 row in set (0.05 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 155 | pankaj | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 155
Binlog_Do_DB: pankaj
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Take a note of current Master log file and position. Then configure Slave server with details obtained from the master status command:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 3: Install and Configure Slave Server
Install MySQL Server 8.0 on Slave server in a similar process used for the Master server. You can follow steps in the guide How to Install MySQL 8.0 on Ubuntu 18.04 / 16.04
When done with the installation, configure slave by editing the file:
[root@slave ~]# vi /etc/my.cnf
log_bin = /var/log/mysql/mysql-bin.log
server-id = 2
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log = 1
read_only = 1: This sets the slave to read-only mode. Only users with the SUPER privilege and the replication slave thread will be able to modify data on it. This ensures there are no applications that can accidentally modify data on the slave instead of master.
server-id = 2: This is a Unique server identification number. It will default to 1 if “master-host” is not set.
log_bin = /var/log/mysql/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.
Restart mysql server after you’ve finished making changes:
[root@slave ~]# systemctl restart mysqld.service
Step 4: Initialize Replication process
We should be ready to start Replication process on the slave server. Start by checking Status on the master:
[root@slave ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.13 Source distribution
Copyright (c) 2000, 2018, 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>
mysql> CHANGE MASTER TO MASTER_HOST='192.168.142.153',
-> MASTER_USER='rpl_user',
-> MASTER_PASSWORD='pankaj',
-> MASTER_LOG_FILE='mysql-bin.000007',
-> MASTER_LOG_POS=155;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
Then start replication on the slave:
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
To check slave status, use:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.131.74.92
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 550
Relay_Log_File: node-02-relay-bin.000002
Relay_Log_Pos: 717
Relay_Master_Log_File: mysql-bin.000002
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: 550
Relay_Log_Space: 927
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: d62cd5d2-784a-11e8-9768-eacea5a1be5e
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
1 row in set (0.00 sec)
Slave IO and SQL should indicate running state:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Step 1: Configure the Master Server
The first configuration change to make is setting Server ID for the master database:
A complete simple configuration looks like below:
+++++++++++++++++++++++++++++++++++++++++++++++++++
[root@master ~]# vi /etc/my.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log
save and Restart mysql service for changes to take effect:
[root@master ~]# systemctl restart mysqld.service
Step 2: Create Replication user on Master database server
We now need to create a database user to be used by slaves when connecting. Login to MySQL database as root user and create the user:
[root@master ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.13 Source distribution
Copyright (c) 2000, 2018, 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>create user rpl_user@192.168.142.152 identified by 'pankaj';
Query OK, 0 rows affected (0.08 sec)
Grant the user REPLICATION SLAVE privileges:
mysql> grant replication slave on *.* to rpl_user@192.168.142.152;
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Confirm grants for created user:
mysql> select DISTINCT User FROM mysql.user;
+------------------+
| User |
+------------------+
| slave_user |
| pankaj |
| rpl_user |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
7 rows in set (0.08 sec)
mysql> show grants for rpl_user@192.168.142.152;
+----------------------------------------------------------------+
| Grants for rpl_user@192.168.142.152 |
+----------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rpl_user`@`192.168.142.152` |
+----------------------------------------------------------------+
1 row in set (0.05 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 155 | pankaj | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 155
Binlog_Do_DB: pankaj
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Take a note of current Master log file and position. Then configure Slave server with details obtained from the master status command:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 3: Install and Configure Slave Server
Install MySQL Server 8.0 on Slave server in a similar process used for the Master server. You can follow steps in the guide How to Install MySQL 8.0 on Ubuntu 18.04 / 16.04
When done with the installation, configure slave by editing the file:
[root@slave ~]# vi /etc/my.cnf
log_bin = /var/log/mysql/mysql-bin.log
server-id = 2
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log = 1
read_only = 1: This sets the slave to read-only mode. Only users with the SUPER privilege and the replication slave thread will be able to modify data on it. This ensures there are no applications that can accidentally modify data on the slave instead of master.
server-id = 2: This is a Unique server identification number. It will default to 1 if “master-host” is not set.
log_bin = /var/log/mysql/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.
Restart mysql server after you’ve finished making changes:
[root@slave ~]# systemctl restart mysqld.service
Step 4: Initialize Replication process
We should be ready to start Replication process on the slave server. Start by checking Status on the master:
[root@slave ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.13 Source distribution
Copyright (c) 2000, 2018, 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>
mysql> CHANGE MASTER TO MASTER_HOST='192.168.142.153',
-> MASTER_USER='rpl_user',
-> MASTER_PASSWORD='pankaj',
-> MASTER_LOG_FILE='mysql-bin.000007',
-> MASTER_LOG_POS=155;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
Then start replication on the slave:
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
To check slave status, use:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.131.74.92
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 550
Relay_Log_File: node-02-relay-bin.000002
Relay_Log_Pos: 717
Relay_Master_Log_File: mysql-bin.000002
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: 550
Relay_Log_Space: 927
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: d62cd5d2-784a-11e8-9768-eacea5a1be5e
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
1 row in set (0.00 sec)
Slave IO and SQL should indicate running state:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
No comments:
Post a Comment