How to Set Up MySQL Replication

MySQL replication creates a copy of your database on another server for redundancy.

On the Primary Server

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

server-id = 1\nlog_bin = /var/log/mysql/mysql-bin.log\nbinlog_do_db = myapp

systemctl restart mysql

Create replication user:

CREATE USER 'repl'@'REPLICA_IP' IDENTIFIED BY 'ReplicaPassword!';\nGRANT REPLICATION SLAVE ON *.* TO 'repl'@'REPLICA_IP';\nFLUSH PRIVILEGES;\nSHOW MASTER STATUS;

On the Replica Server

server-id = 2\nrelay-log = /var/log/mysql/mysql-relay-bin.log

CHANGE MASTER TO\n    MASTER_HOST='PRIMARY_IP',\n    MASTER_USER='repl',\n    MASTER_PASSWORD='ReplicaPassword!',\n    MASTER_LOG_FILE='mysql-bin.000001',\n    MASTER_LOG_POS=154;\nSTART SLAVE;\nSHOW SLAVE STATUS\G