We start from two MySQL servers.

For example:

Master: 134.170.185.46

Slave: 137.254.120.50

Let’s first try to connect to a database from Slave to Master server.

Create a database:

CREATE DATABASE demo DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Create a user for this database.

CREATE USER 'demo'@'%' IDENTIFIED BY '***';GRANT USAGE ON *.* TO 'demo'@'%' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

And we give it access to the demo database.

GRANT ALL PRIVILEGES ON `demo`.* TO 'demo'@'%'WITH GRANT OPTION;

We check that everything works.

mysql --user=demo --password=password demo

Now we create a simple table for testing:

CREATE TABLE `demo` (
       `demo_id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(200) NOT NULL,
       PRIMARY KEY (`demo_id`),
       KEY `demo_id` (`demo_id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;</code>

Were created user to give it permission to access from any server with ‘@’% ‘. Before proceeding we must ensure that you can access from the slave to the master server.

First on the master server comment the following line in the MySQL configuration file (/etc/mysql/my.cnf).

#bind-address=127.0.0.1

Restart MySQL.

/etc/init.d/mysql restart

And test from the slave server connection:

mysql --host=134.170.185.46 --user=demo --password=password demo

All right! If this works can continue.

We focus on the Master server.

We return to the MySQL configuration file and type:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db           = demo

Note: you must use the –server-id option to establish a unique replication ID in the range from 1 to 2^32-1.

Restart MySQL.

Create a user for replicas:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

To see that all goes well we can enter the shell and run:

USE demo;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

It will look something like this (we’ll use this information later):

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      348 | demo         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On the Slave server:

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='134.170.185.46', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=348;
START SLAVE;

Restart MySQL.

Let’s try.

The replicas are fine when we are working with large databases. It is also quite easy to configure. It is very important that the connection between the network servers works fine, If don’t you’ll have a lot of lag.