What is MySQL asynchronous replication? Today we will learn about MySQL asynchronous replication setup with detailed steps. Let’s go through it :
Pre Requisites :
- Source IP : 192.168.29.249 (Hostname : vm3)
- Target IP : 192.168.29.250 (Hostname : vm4)
- Port: 3306 (By Default)
- To Make this setup, we need two servers whose server id should be different and need to establish the connection between them
You can check the server id :
Source Server, serverid=1
Target Server, serverid=2
Check ping and telnet with the port to test the connection as below :
From Source Server : Telnet : [root@vm3 ~]# telnet 192.168.29.250 3306 Trying 192.168.29.250… Connected to 192.168.29.250. Escape character is '^]'. [root@vm3 ~]# ping 192.168.29.250 PING 192.168.29.250 (192.168.29.250) 56(84) bytes of data. 64 bytes from 192.168.29.250: icmp_seq=1 ttl=64 time=1.32 ms 64 bytes from 192.168.29.250: icmp_seq=2 ttl=64 time=0.669 ms ^C From Target Server : [root@vm4 ~]# telnet 192.168.29.249 3306 Trying 192.168.29.249... Connected to 192.168.29.249. Escape character is '^]'. [root@vm4 ~]$ ping 192.168.29.249 PING 192.168.29.249 (192.168.29.249) 56(84) bytes of data. 64 bytes from 192.168.29.249: icmp_seq=1 ttl=64 time=0.816 ms ^C
- Also, Mysql needs to be installed on both servers. You can get the detailed steps for MySQL installation from our other Article MySQL Installation in Redhat Linux Step-by-Step
Steps to Create Replica for MySQL Asynchronous Replication Setup :
1. To Setup the replica, we need to create a user :
Go on source server and issue below :
mysql> create user 'rep'@192.168.29.250 identified by 'Rep@987y'; Query OK, 0 rows affected (0.03 sec) mysql> select user,host from mysql.user where user='rep'; +------+----------------+ | user | host | +------+----------------+ | rep | 192.168.29.250| +------+----------------+ 1 row in set (0.00 sec)
2. Now provide all grants to that user and show privileges :
mysql> grant replication slave on *.* to 'rep'@192.168.29.250; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> show grants for 'rep'@192.168.29.250; +----------------------------------------------------------+ | Grants for rep@192.168.29.250 | +----------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `rep`@`192.168.29.250` | +----------------------------------------------------------+ 1 row in set (0.00 sec)
3. Now check login with this ‘rep‘ user from the target server :
[root@vm4 ~]# mysql -h 192.168.29.249 -u rep -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.35 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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> \s -------------- mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 12 Current database: Current user: rep@vm4 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.35 MySQL Community Server - GPL Protocol version: 10 Connection: 192.168.29.249 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port: 3306 Binary data as: Hexadecimal Uptime: -------------- mysql> select @@hostname; +------------+ | @@hostname | +------------+ | vm3 | +------------+ 1 row in set (0.01 sec) mysql> show grants; +----------------------------------------------------------+ | Grants for rep@192.168.29.250 | +----------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `rep`@`192.168.29.250` | +----------------------------------------------------------+ 1 row in set (0.00 sec)
4. Now check the current binlog and position from the source and note it down :
mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000019 | 1880 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
5. Issue the change master command from the target host :
mysql> change master to master_host='192.168.29.249',master_user='rep',master_password='Rep@987y', master_log_file='binlog.000019', master_log_pos=1880,master_port=3306; Query OK, 0 rows affected, 9 warnings (0.18 sec)
6. Check the Replica status :
At this moment you will see that Replica_IO and Replica_SQL is not running.
7. You have to issue the command start replica :
mysql> start replica;
Query OK, 0 rows affected (0.10 sec)
Now you will be able to see both Replica_IO and Replica_SQL is running. So, your MySQL asynchronous replication setup is ready. You can test it.
Test Replication:
Create a database and a table on the source Side :
Check on the Replica :
So, here are the detailed steps of MySQL Asynchronous Replication Setup. Hope this helps!! Also, you can get further reference from the below MSQL DOC.