MySQL: Replication

Oct 28, 2022

—

#1. On the master server, edit /etc/mysql/my.cnf.

[mariadb]
log-bin
server_id=1
log-basename=master1
binlog-format=mixed

#2. Restart/reload the master server.

#3. On the master server add user and grant replication privileges.

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

#4. Flush privileges and lock the tables so that the log index does not move.

FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;

#5. On the master server, get posision of the log index.

SHOW MASTER STATUS;

+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 |     3614 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

#6. On the slave server, edit /etc/mysql/my.cnf.

[mariadb]
server_id=2
read-only
replicate-do-table=test.users

#7. Restart/reload the slave server.

#8. Set the slave server to the master server

CHANGE MASTER TO
  MASTER_HOST='master.domain.com',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master1-bin.000096',
  MASTER_LOG_POS=3614,
  MASTER_CONNECT_RETRY=10;

#9. Start syncing slave server to the master server.

START SLAVE;

#10. Check the status of the slave.

SHOW SLAVE STATUS\G

If everything works well it will show:

...
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
...

References:

MySQL: Replication

Comments

Leave a Reply Cancel reply