Finally, let’s adjust the replication itself. For that, you’ll need to execute some commands with the help of phpMyAdmin panel.
1. Click Open in browser for the MySQL server in the first environment (master-mysql-1).
2. You’ll be redirected to the opened administrator panel. Log in to it with the credentials for root user you’ve received via email during environment creation and navigate to the SQL tab. In the window opened, execute the following commands:create user 'replicator'@'%' identified by 'password';
grant replication slave on *.* to 'replicator'@'%';
Note: password value should be substituted with your own one. DO NOT use the default value - it’s not secure.
In such a way, we’ve created a new replicator user and granted him global permissions for performing the replication operations.
Remember the credentials you’ve specified and click the Go button below.
3. In order to check the configuration is OK so far, execute this line:show master status;
You should get a result similar to the following:
Remember the binlog file name and its position. We’ll use them in the further configurations.
4. Repeat the 1-3 steps for the second MySQL server, creating the same-named user and remembering the log file parameters.
In case you’ve used cloning for the second environment creation, credentials for its MySQL server will remain the same as for the first one.
5. Now it’s time to activate the replication. Open the SQL tab in phpMyAdmin panel of the second MySQL server (the one that is included to the master-mysql-2 environment) and run the following:STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'first_server_IP', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'binlog_file_name', MASTER_LOG_POS = binlog_file_position;
first_server_IP - internal IP address of the MySQL server in the first environment. In case you would like to configure the replication between servers of different hosting providers, you should attach the external IP addresses to both of your DB nodes and specify the first server’s one in this parameter.
Internal/external IP address can be seen by pressing the Info button next to the appropriate server:
password - the one you’ve specified while creating the DB user for the first MySQL server
binlog_file_name - value in the File column of the first server’s status table
binlog_file_position - position of the binlog file from the same table
6. In order to check whether the commands were successfully executed or not, run the following:show slave status;
7. Open the administrator panel for MySQL node of the master-mysql-1 environment and run the same lines substituting the parameters’ values with the data of the second DB server:STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'second_server_IP', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'binlog_file_name', MASTER_LOG_POS = binlog_file_position;
8. Check if all was configured properly in the same way:show slave status;
Congrats! Replication has been successfully enabled on both servers.