Set Up MySQL Replication

linux

How do I set up MySQL Replication?

To set up a server as a master add the following to the [server] section of the config file

The config file for Hestia is in /etc/mysql/mariadb.conf.d/50-server.cnf

server-id = <unique server id (good idea is to use current unix timestamp)>

server-id              = 1703241505

set the location of the server log_bin files

log_bin = /var/log/mysql/mysql-bin.log

then restart the MySQL server

Master Backup

To do the Master backup Open 2 ssh sessions to the master.

In the first session log into mysql as root and flush tables with read lock then show master position

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

In the second terminal execute the mysqldump command 

mysqldump --add-drop-table --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 --databases <List your databases here, seperated by spaces> | gzip > dump.gz

Or for a full backup of all databses

mysqldump -uroot -p -A --triggers --single-transaction | gzip > full_backup.sql.gz

When its finished go back to first terminal then execute unlock tables.

UNLOCK TABLES;

You now have an sql file with all databases to be imported into other server.

Create a replication user for the slave to connect to

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

my.ini (my.cnf) changes to make a slave also log master queries

log-slave-updates = 1
slave-skip-errors=1062
skip-name-resolve=1
sync_binlog=1
expire-logs-days=30

STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.003102',MASTER_LOG_POS=22397931; START SLAVE;

CHANGE MASTER TO MASTER_HOST='10.0.10.50', MASTER_USER='20221111replication_user', MASTER_PASSWORD='jnt8u*tVRG', MASTER_LOG_FILE='mysql-bin.???', MASTER_LOG_POS=???;;

with ssl

CHANGE MASTER TO MASTER_HOST='Server IP', MASTER_USER='replication user', MASTER_PASSWORD='Replication Password', MASTER_SSL=1, MASTER_SSL_CIPHER=''; MASTER_SSL_CAPATH = '/var/lib/mysql', MASTER_SSL_CA = 'ca-cert.pem', MASTER_SSL_CERT = 'client-cert.pem', MASTER_SSL_KEY = 'client-key.pem';

CHANGE MASTER TO MASTER_SSL=0,MASTER_SSL_CIPHER='',MASTER_SSL_CAPATH = '',MASTER_SSL_CA = '',MASTER_SSL_CERT = '',MASTER_SSL_KEY = '';

SHOW SLAVE STATUS \G

mysqldump --add-drop-table --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --all-databases > c:/Users/username/dump.sql

STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE; Process for Huge Databases - copying files

mysqladmin flush-hosts SHOW VARIABLES LIKE '%error%'; SHOW VARIABLES LIKE '%max_connections%'; show status like '%onn%'; SHOW STATUS WHERE variablename LIKE "Threads%" OR variable_name = "Connections" show processlist;

CHECK SSL CERTIFICATES openssl x509 -in /path/to/cert/server-cert.pem -text

Previous Post Next Post