MySQL Master-Slave Replication on different AWS instances.
In this article i want show how to configure master-slave replication in two different machines.
What is Replication ?
Replication is the continuous copying of data changes from one database (MASTER) to another database (SLAVE ).
The two databases are generally located on a different physical servers, resulting in a load balancing framework by distributing assorted database queries and providing fail-over capability.
The server for the SLAVE database may be configured as a backup in the event of failure of the server for the MASTER database.
What is MySQL Replication ?
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 fail-over, or analyzing data on the slave in order not to overload the master.
As the master-slave replication is a one-way replication (from master to slave), only the master database is used for the write operations, while read operations may be spread on multiple slave databases. What this means is that if master-slave replication is used as the scale-out solution, you need to have at least two data sources defined, one for write operations and the second for read operations.
In MySQL , How replication will work internally ?
Important lines in MySQL Replication :
–>The slave server always connects with Master server.
–>MySQL Replication works based on Three types threads
Binary thread
I/O thread
Slave SQL thread
Binary thread :
The Binary thread is running on the master side and it will sending those binary logs to slave.
–>On slave side we have two thread
I/O thread
Slave SQL thread
–>i/o thread will connecting to master and getting those binary logs and Slave sql thread will execute those binary logs to slave machine.
At slave side , Relays will be created
Relay logs :
The relay log is a set of log files created by a slave during replication. It’s the same format as the binary log, containing a record of events that affect the data or structure; thus, mysqlbinlog can be used to display its contents.
Prerequisite for MySQL replication :
- Both master and slave should be running with different server id.
- Master server should be enabled with binary logs.
Step : 1
Make sure the id of both the servers should be different.
Master Server :
Check server id need to connect mysql and issue following command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> show variables like '%server%' -> ; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | character_set_server | latin1 | | collation_server | latin1_swedish_ci | | innodb_ft_server_stopword_table | | | server_id | 1 | | server_id_bits | 32 | | server_uuid | 5e9e8395-f703-11e8-95f0-0e50a4fa291a | +---------------------------------+--------------------------------------+ 6 rows in set (0.00 sec) |
For master server the id is 1.
Slave Server :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show variables like '%server%'; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | character_set_server | latin1 | | collation_server | latin1_swedish_ci | | innodb_ft_server_stopword_table | | | server_id | 1 | | server_id_bits | 32 | | server_uuid | 64dc54ef-f703-11e8-9440-0e74861bbd76 | +---------------------------------+--------------------------------------+ 6 rows in set (0.00 sec) |
For slave process also the id is same.
If server id is same replication is not possible, so we need change the server id.
How to change sever id :
Open my.cnf file and write
|
1 |
server-id=2 |
Restart the service and check server id.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show variables like '%server%'; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | character_set_server | latin1 | | collation_server | latin1_swedish_ci | | innodb_ft_server_stopword_table | | | server_id | 2 | | server_id_bits | 32 | | server_uuid | 64dc54ef-f703-11e8-9440-0e74861bbd76 | +---------------------------------+--------------------------------------+ 6 rows in set (0.00 sec) |
There serverid & server uuid will be written for every sql statement in binary logs :
Step 2 :
Check binary log is enabled or not on both side .
Master Server :
|
1 2 3 4 5 6 7 |
mysql> show binary logs; +----------------------------+-----------+ | Log_name | File_size | +----------------------------+-----------+ | ip-172-31-47-50-bin.000002 | 158 | +----------------------------+-----------+ 1 row in set (0.00 sec) |
Slave Server :
|
1 2 3 4 5 6 7 |
mysql> show binary logs; +----------------------------+-----------+ | Log_name | File_size | +----------------------------+-----------+ | ip-172-31-47-50-bin.000002 | 158 | +----------------------------+-----------+ 1 row in set (0.00 sec) |
Step 3 :
In Master side we need to create a user with required privileges.
|
1 2 |
mysql> grant replication slave ,replication client on *.* to replica@'172.31.35.216' identified by 'Replica@123'; Query OK, 0 rows affected, 1 warning (0.01 sec) |
Here i have created a user called replica with privileges called replication slave , replication client.
When we are performing changes to server , content will be added to binary logs.
check binary logs on master.
|
1 2 3 4 5 6 7 |
mysql> show binary logs; +----------------------------+-----------+ | Log_name | File_size | +----------------------------+-----------+ | ip-172-31-47-50-bin.000002 | 472 | +----------------------------+-----------+ 1 row in set (0.00 sec) |
Step 3 :
Check ip address of master .
|
1 2 3 |
mysql> \! ip r default via 172.31.32.1 dev eth0 proto dhcp metric 100 172.31.32.0/20 dev eth0 proto kernel scope link src 172.31.47.50 metric 100 |
The Master ip address will be given to server to get binary to slave side.
Slave Server :
|
1 2 |
mysql> change master to master_host='172.31.47.50',master_port=3306,master_user='replica',master_password='Replica@123',master_log_file='ip-172-31-47-50-bin.000002',master_log_pos=472; Query OK, 0 rows affected, 2 warnings (0.01 sec) |
What is the use of each variable ?
Master_host –> Need to give master host address
master_port –> Need to write Master mysql db running port number
master_user –> The user which is created for replication and privs
master_password –> Password of that user
master_log_file –> Binary logfile which currently running at master side
master_log_pos –> Binary logfile master log postion
Step 4 :
Check status of slave process at slave side :
The \G rearranges the text to make it more readable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.31.47.50 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: ip-172-31-47-50-bin.000002 Read_Master_Log_Pos: 472 Relay_Log_File: ip-172-31-35-216-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: ip-172-31-47-50-bin.000002 Slave_IO_Running: No Slave_SQL_Running: No 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: 472 Relay_Log_Space: 154 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: NULL 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: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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: 1 row in set (0.00 sec) |
In above example, the below two variables are showing NO.
|
1 2 |
Slave_IO_Running: No Slave_SQL_Running: No |
Once we start the slave process, the above two variable will show as yes.
Activate the slave server:
|
1 2 |
mysql> start slave ; Query OK, 0 rows affected (0.00 sec) |
Now check the status again.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.31.47.50 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: ip-172-31-47-50-bin.000002 Read_Master_Log_Pos: 472 Relay_Log_File: ip-172-31-35-216-relay-bin.000002 Relay_Log_Pos: 330 Relay_Master_Log_File: ip-172-31-47-50-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: 472 Relay_Log_Space: 548 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: 5e9e8395-f703-11e8-95f0-0e50a4fa291a Master_Info_File: /var/lib/mysql/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: 1 row in set (0.00 sec) |
In above example, the below two variables are changes to yes.
|
1 2 |
Slave_IO_Running: Yes Slave_SQL_Running: Yes |
Step 5 :
Now check Replication is happening master to slave or not.
Master server :
Check database list and create new database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> create database crm ; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | crm | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) |
Use the database crm and create a table.
|
1 2 3 4 5 6 |
mysql> use crm ; Database changed mysql> craete table tab_crm_data(id int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'craete table tab_crm_data(id int)' at line 1 mysql> create table tab_crm_data(id int); Query OK, 0 rows affected (0.02 sec) |
Now insert the records and check at slave side.
|
1 2 3 4 5 6 7 8 |
mysql> insert into tab_crm_data values(10); Query OK, 1 row affected (0.01 sec) mysql> insert into tab_crm_data values(11); Query OK, 1 row affected (0.01 sec) mysql> insert into tab_crm_data values(12); Query OK, 1 row affected (0.01 sec) |
Step 6:
Slave Side :
IN Slave Sever and check records.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | crm | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use crm; 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 tab_crm_data; +------+ | id | +------+ | 10 | | 11 | | 12 | +------+ 3 rows in set (0.00 sec) |
Check Relay logs at slave side :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
mysql> show variables like '%data%' -> ; +---------------------------------------+------------------------+ | Variable_name | Value | +---------------------------------------+------------------------+ | character_set_database | latin1 | | collation_database | latin1_swedish_ci | | datadir | /var/lib/mysql/ | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | | innodb_stats_on_metadata | OFF | | innodb_temp_data_file_path | ibtmp1:12M:autoextend | | max_length_for_sort_data | 1024 | | metadata_locks_cache_size | 1024 | | metadata_locks_hash_instances | 8 | | myisam_data_pointer_size | 6 | | performance_schema_max_metadata_locks | -1 | | skip_show_database | OFF | | updatable_views_with_limit | YES | +---------------------------------------+------------------------+ 14 rows in set (0.00 sec) mysql> exit Bye [root@ip-172-31-35-216 ~]$ cd /var/lib/mysql [root@ip-172-31-35-216 mysql]# ls -ltr total 122980 -rw-r-----. 1 mysql mysql 50331648 Dec 3 13:57 ib_logfile1 -rw-r-----. 1 mysql mysql 56 Dec 3 13:57 auto.cnf drwxr-x---. 2 mysql mysql 8192 Dec 3 13:57 performance_schema drwxr-x---. 2 mysql mysql 4096 Dec 3 13:57 mysql drwxr-x---. 2 mysql mysql 8192 Dec 3 13:57 sys -rw-------. 1 mysql mysql 1675 Dec 3 13:57 ca-key.pem -rw-r--r--. 1 mysql mysql 1107 Dec 3 13:57 ca.pem -rw-------. 1 mysql mysql 1675 Dec 3 13:57 server-key.pem -rw-r--r--. 1 mysql mysql 1107 Dec 3 13:57 server-cert.pem -rw-------. 1 mysql mysql 1679 Dec 3 13:57 client-key.pem -rw-r--r--. 1 mysql mysql 1107 Dec 3 13:57 client-cert.pem -rw-------. 1 mysql mysql 1675 Dec 3 13:57 private_key.pem -rw-r--r--. 1 mysql mysql 451 Dec 3 13:57 public_key.pem -rw-r-----. 1 mysql mysql 350 Dec 3 14:12 ib_buffer_pool -rw-r-----. 1 mysql mysql 30 Dec 3 14:12 ip-172-31-35-216-bin.index -rw-r-----. 1 mysql mysql 154 Dec 3 14:12 ip-172-31-35-216-bin.000001 -rw-------. 1 mysql mysql 5 Dec 3 14:12 mysql.sock.lock srwxrwxrwx. 1 mysql mysql 0 Dec 3 14:12 mysql.sock -rw-r-----. 1 mysql mysql 12582912 Dec 3 14:12 ibtmp1 -rw-r-----. 1 mysql mysql 218 Dec 3 15:07 ip-172-31-35-216-relay-bin.000001 -rw-r-----. 1 mysql mysql 72 Dec 3 15:07 ip-172-31-35-216-relay-bin.index drwxr-x---. 2 mysql mysql 68 Dec 3 15:09 crm -rw-r-----. 1 mysql mysql 1439 Dec 3 15:09 ip-172-31-35-216-relay-bin.000002 -rw-r-----. 1 mysql mysql 82 Dec 3 15:09 relay-log.info -rw-r-----. 1 mysql mysql 12582912 Dec 3 15:09 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Dec 3 15:09 ib_logfile0 -rw-r-----. 1 mysql mysql 144 Dec 11 15:09 master.info |
In above example we are able to see relay logs with contents and also relay-log.info and master.info files.
Loading...
maheshsrikakula
Nice Article with clear steps for setting mySQL replication