Mysql replication in Virtual Machines

 

We already have ready two virtual machines with (debian or ubuntu), regognized as master and slave. At both we installed a new and same version of mysql or mariadb. At master we will setup a master mysql server and on slave we will setup a slave mysql server  that replicates from the master. For both vitual machines at their settings->networks we select the Bridged Adapter so the machines can be in the same network.

First we enter to the master vm. We edit the mysql configuration file with the minimum requirements:

sudo nano /etc/mysql/my.cnf

[mysqld]
server-id=1
log-bin
bind-address=xx.xx.xx.xx

The server-id is need for the mysql replication. With log-bin it will log the changes that will happen in databases for the slave to read. For bind-address we use the ip of the current virtual machine.

sudo service mysql restart

Now that we asked to log all changes, the server should be run like master

sudo mysql
mysql> SHOW MASTER STATUS;

We note the log file and log position to use them later at the slave server.
We create the user that will replicate the data from master to slave:

mysql> CREATE USER "replication"@"%" IDENTIFIED BY "replication";
mysql> GRANT REPLICATION SLAVE ON *.* TO "replication"@"%";
mysql> FLUSH PRIVILEGES;

Finally, we remove the debian's firewall for port 3306 (mysql's default)

# debian
iptables -I INPUT 1 -p tcp --dport 3306 -j ACCEPT
# ubuntu
sudo ufw allow XXXX/tcp

Now let's continue on slave. We edit the configuration file

sudo nano /etc/mysql/my.cnf

[mysqld]
server-id=2

Restart the server

sudo service mysql restart

Make it slave (with the previous data of the master)

sudo mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST="xx.xx.xx.xx",
-> MASTER_USER="replication",
-> MASTER_PASSWORD="replication",
-> MASTER_LOG_FILE="mysql-bin.00000x",
-> MASTER_LOG_POS=xxx;
mysql> START SLAVE;

Check that is connected

mysql> SHOW SLAVE STATUS;

If you need to repeat the command

mysql> STOP SLAVE;
mysql> CHANGE ....

If it connects successfully, restart the server

sudo service mysql restart

Now it will replicate all new changes from the master server.

Note: if the master has already a database or any before the replication and it logs a new table update at this database, the slave will report an error and wont continue excecuting the log changes. So it is important to have the master empty while you make this replication process as described here. If you want to replice a master with excisting databases, first of all you must dump all databases from master and import them on second server before it runs as slave.