This assumes you already have a single MySQL server (MYSQL01) set up and running, with a freshly installed MySQL server set up on another host (MYSQL02).
1. Add a replication user (used later for….. replication)
$ mysql -h MYSQL01 -u root -p [root_password]
CREATE USER 'replicator'@'%' IDENTIFIED BY '[replicator_password]';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY '[replicator_password]';
2. Stop the current server and transfer all MySQL data files to your empty secondary server (MYSQL02)
# service mysqld stop
# scp -r /var/lib/mysql root@MYSQL02:/var/lib/mysql
# chown -R mysql:mysql /var/lib/mysql
3. Remove the UUID from the auto.cnf on MYSQL02
This is randomly generated if missing. Both servers require unique UUIDs.
# nano /var/lib/mysql/auto.cnf
Remove the line that looks something like:
server-uuid=a3359874-5226-11eb-8cdc-0050569b1f00
4. Start the MySQL service on both hosts
# service mysqld start
5. Check that all users/databases are identical in both servers
$ mysql -h MYSQL01 -u root -p [root_password]
SHOW DATABASES;
select host, user from mysql.user;
and
$ mysql -h MYSQL02 -u root -p [root_password]
SHOW DATABASES;
select host, user from mysql.user;
6. Get some Master info from MYSQL01
$ mysql -h MYSQL01 -u root -p [root_password]
SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000013 | 45139092 | | | |
+---------------+----------+--------------+------------------+-------------------+
7. Make MYSQL02 a slave of that MYSQL01
$ mysql -h MYSQL02 -u root -p [root_password]
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '[MYSQL01 IP address]', MASTER_USER = 'replicator', MASTER_PASSWORD = '[replicator_password]', MASTER_LOG_FILE = 'binlog.000013', MASTER_LOG_POS = 45139092;
START SLAVE;
8. Get some Master info from MYSQL02
$ mysql -h MYSQL02 -u root -p [root_password]
SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.00009 | 345 | | | |
+---------------+----------+--------------+------------------+-------------------+
9. Make MYSQL01 a slave of that MYSQL02
$ mysql -h MYSQL01 -u root -p [root_password]
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '[MYSQL02 IP address]', MASTER_USER = 'replicator', MASTER_PASSWORD = '[replicator_password]', MASTER_LOG_FILE = 'binlog.00009, MASTER_LOG_POS = 345;
START SLAVE;
10. Create a test database on MYSQL01 and check if it is created on MYSQL02
$ mysql -h MYSQL01 -u root -p [root_password]
CREATE DATABASE tester;
and
$ mysql -h MYSQL02 -u root -p [root_password]
SHOW DATABASES LIKE 'tester';
+-------------------+
| Database (tester) |
+-------------------+
| tester |
+-------------------+
1 row in set (0.00 sec)
11. Delete the database in MYSQL02 and check if it is deleted in MYSQL01
$ mysql -h MYSQL02 -u root -p [root_password]
DROP DATABASE tester;
$ mysql -h MYSQL01 -u root -p [root_password]
SHOW DATABASES LIKE 'tester';
Empty set (0.00 sec)