https://www.youtube.com/watch?v=JXDuVypcHNA
Go to your Master DB server
Add the following into your my.conf file:
sudo nano /etc/mysql/my.conf
[mariadb]
log-bin
server_id=101
log-basename=master1
sudo service mysql restart
Note: The server id is a unique number for each MariaDB/MySQL server in your network.
Update and uncomment the following:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = <master_ip_address>
server-id = <server_id>
log_bin = /var/log/mysql/mysql-bin.log
E.g.
bind-address = 192.168.254.101
server-id = 101
log_bin = /var/log/mysql/mysql-bin.log
sudo service mysql restart
Create a user for replication
sudo mysql -u root -p
CREATE USER '<user>'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO '<user>'@'%';
E.g.
CREATE USER 'replica'@'%' IDENTIFIED BY 'bigs3cret';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
Create a data snapshot/backup
sudo mysqldump -u root -p --all-databases --master-data > master_mysql_dump.sql
Get and record the log file and log position
grep CHANGE master_mysql_dump.sql | head -1
Note: The last 2 commands are important because we need the Slave server to have the current data of the Master server, for our Slave server work properly.
Copy the MySQL dump file to our Slave server
sudo scp master_mysql_dump.sql <username>@<slave_ip_address>:/tmp/
E.g.
sudo scp master_mysql_dump.sql root@192.168.254.102:/tmp/
Go to your Slave DB server
Add the following into your my.conf file:
sudo nano /etc/mysql/my.conf
[mariadb]
log-bin
server_id=102
sudo service mysql restart
Note: The server id is a unique number for each MariaDB/MySQL server in your network.
Update and uncomment the following:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = <slave_ip_address>
server-id = <server_id>
E.g.
bind-address = 192.168.254.102
server-id = 102
sudo service mysql restart
Tell the Slave server about the Master server information
sudo mysql -u root -p
CHANGE MASTER TO
MASTER_HOST='192.168.254.101',
MASTER_USER='replica',
MASTER_PASSWORD='bigs3cret';
Restore the data from the Master server
sudo mysql -u root -p < /tmp/master_mysql_dump.sql
Finally, start the slave
sudo mysql -u root -p
START SLAVE;
Show the slave status
sudo mysql -u root -p
SHOW SLAVE STATUS\G;
Note: Check the value of Slave_IO_Running and Slave_SQL_Running. Both should be Yes.