Configuring replication
- Master IP: 206.189.129.180
- Slave IP: 206.189.137.107
Install MySQL
sudo apt-get install mysql-server mysql-client -y
Insert some data
mysql -u root -p
CREATE DATABASE newdatabase;
USE newdatabase;
CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));
INSERT INTO authors (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
INSERT INTO authors (id,name,email) VALUES(2,"Priya","p@gmail.com");
EXIT;
Configure the Master Database
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
We need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.
binlog_do_db = newdatabase
Save the configuration file and restart MySQL.
sudo service mysql restart
Open up the MySQL shell.
mysql -u root -p
We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
Follow up with:
FLUSH PRIVILEGES;
Following that, lock the database to prevent any new changes:
USE newdatabase;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 595 | newdatabase | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.
If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.
Proceeding the with the database still locked, export your database using mysqldump in the new window.
mysqldump -u root -p --opt newdatabase > newdatabase.sql
Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.
UNLOCK TABLES;
QUIT;
Configure the Slave Database
Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master:
mysql -u root -p
CREATE DATABASE newdatabase;
EXIT;
Import the database that you previously exported from the master database.
mysql -u root -p newdatabase < /path/to/newdatabase.sql
Now we need to configure the slave configuration in the same way as we did the master:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
server-id needs to be unique. You will need to add in the relay-log line: it is not there by default.
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_do_db = newdatabase
Restart MySQL
sudo service mysql restart
The next step is to enable the replication from within the MySQL shell.
mysql -u root -p
Replace the values to match your information:
CHANGE MASTER TO MASTER_HOST='206.189.129.180',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 595;
This command accomplishes several things at the same time:
- It designates the current server as the slave of our master server.
- It provides the server the correct login credentials
- Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.
Activate the slave server:
START SLAVE;
See the details of the slave replication. The \G rearranges the text to make it more readable.
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 206.189.129.180
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 595
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
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: 595
Relay_Log_Space: 527
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: ec1b6e06-b58e-11e8-a2f7-6a976e50a498
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)
USE newdatabase;
SELECT * FROM authors;
+------+-------+-------------+
| id | name | email |
+------+-------+-------------+
| 1 | Vivek | xuz@abc.com |
| 2 | Priya | p@gmail.com |
+------+-------+-------------+
2 rows in set (0.00 sec)
Insert some data in Master
USE newdatabase;
INSERT INTO authors (id,name,email) VALUES(3,"Tom","tom@yahoo.com");
Verify replication in Slave
SELECT * FROM authors;
+------+-------+---------------+
| id | name | email |
+------+-------+---------------+
| 1 | Vivek | xuz@abc.com |
| 2 | Priya | p@gmail.com |
| 3 | Tom | tom@yahoo.com |
+------+-------+---------------+
3 rows in set (0.00 sec)
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 206.189.129.180
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 891
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 616
Relay_Master_Log_File: mysql-bin.000001
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: 891
Relay_Log_Space: 823
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: ec1b6e06-b58e-11e8-a2f7-6a976e50a498
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)