Home VPN WorkPlace Web Sites/Apps Small Business Other Apps Contact Guides

MySQL Master - Master Replication (Cluster)

I write this mostly to remind myself, but I figured I would post it as it took hours of research and trial and error to succeed at.

Environment

If MySQL Server is installed prior to cloaning, you have to delete /var/lib/mysql/auto.cnf and then restart MySQL. If you do not, they will both have the same UUID. By deleting this file, MySQL Server will generate a new UUID on next start.
sudo rm /var/lib/mysql/auto.cnf
sudo systemctl restart mysql

Installation

sudo apt install mysql-server mysql-client -y
sudo mysql_secure_installation
Create Remote 'root' User (Do not expose your MySQL Server to the internet with this configuration.)
sudo mysql
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '[password]';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

MySQL Configuration Files

Server 1

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld] user = mysql key_buffer_size = 16M myisam-recover-options = BACKUP bind-address = [IP of Server 1] server_id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index log_slave_updates = 1 log_error = /var/log/mysql/error.log relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index auto_increment_increment = 2 auto_increment_offset = 1 max_binlog_size = 100M binlog_expire_logs_seconds = 864000 binlog-ignore-db = test binlog-ignore-db = information_schema binlog-ignore-db = mysql replicate-ignore-db = test replicate-ignore-db = information_schema replicate-ignore-db = mysql slave_transaction_retries = 100 net_retry_count = 100 slave_net_timeout = 60
log into MySQL
sudo mysql
Create replication user on Server 1 for Server 2.
CREATE USER 'replication'@'[Server 2 IP]' IDENTIFIED WITH mysql_native_password BY '[password]';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'[Server 2 IP]';

Server 2

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld] user = mysql key_buffer_size = 16M myisam-recover-options = BACKUP bind-address = [IP of Server 2] server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index log_slave_updates = 1 log_error = /var/log/mysql/error.log relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index auto_increment_increment = 2 auto_increment_offset = 2 max_binlog_size = 100M binlog_expire_logs_seconds = 864000 binlog-ignore-db = test binlog-ignore-db = information_schema binlog-ignore-db = mysql replicate-ignore-db = test replicate-ignore-db = information_schema replicate-ignore-db = mysql slave_transaction_retries = 100 net_retry_count = 100 slave_net_timeout = 60
log into MySQL
sudo mysql
Create replication user on Server 2 for Server 1.
CREATE USER 'replication'@'[Server 1 IP]' IDENTIFIED WITH mysql_native_password BY '[password]';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'[Server 1 IP]';

Server 1

SHOW MASTER STATUS;
Note File Value and Position

Server 2

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '[IP of Server 1]', MASTER_USER = 'replication', MASTER_PASSWORD = '[password]', MASTER_LOG_FILE = 'mysql-bin.[Number from File Value on Server 1]', MASTER_LOG_POS = [Position from Server 1];
START SLAVE;
SHOW SLAVE STATUS \G;
Confirm:
Slave_IO_Running: Yes Slave_SQL_Running: Yes
SHOW MASTER STATUS;
Note File Value and Position

Server 1

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '[IP of Server 2]', MASTER_USER = 'replication', MASTER_PASSWORD = '[password]', MASTER_LOG_FILE = 'mysql-bin.[Number from File Value on Server 2]', MASTER_LOG_POS = [Position from Server 1];
START SLAVE;
SHOW SLAVE STATUS \G;
Confirm:
Slave_IO_Running: Yes Slave_SQL_Running: Yes

Repair Broken Slave

Skip One Error
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SHOW SLAVE STATUS \G;
Skip All of a specific Error Code
Ex: Duplication Error Code: 1062
Add to /etc/mysql/mysql.conf.d/mysqld.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
slave-skip-errors = 1062