Automatic replication of a mysql/maria database using the Master - Slave mechanism

Sources: https://mariadb.com/kb/en/library/setting-up-replication/, /usr/share/doc/mariadb-server-10.1/README.Debian.gz

Configure the master

1. Allow network access to the server

[mysqld]
#bind-address		= 127.0.0.1
bind-address		= 0.0.0.0

2. Set the server-id; specify that only database twitter show be replicated in the log; enable the binary log. The best format appears to be MIXED (https://mariadb.com/kb/en/library/binary-log-formats/).

If binlog-do-db is not specified, all changes is recorded, which is not what I wanted here (since the slave has other stuff as well).

Again, edit /etc/mysql/mariadb.conf.d/50-server.cnf

[mariadb]
server-id		= 1
log-basename            = pimaster1
log_bin
# Keep bin logs until the binlog reaches it maximum allowed value
expire_logs_days	= 0
## Allow for 1GB of binlogs
max_binlog_size        = 1024M
binlog_format          = MIXED
binlog-do-db           = twitter

3. Grant access for the slave(s), by issuing a SQL query like this as root user on the master.

GRANT REPLICATION SLAVE ON *.* TO replication_user IDENTIFIED BY 'really good password here';

Configure the slave

4. Set the server-id to something unique. On the slave, edit /etc/mysql/mariadb.conf.d/50-server.cnf

[mariadb] 1 is pi (master), we are 2 and slave server-id = 2

Make the slave current

Backup the master

FLUSH TABLES WITH READ LOCK

SHOW MASTER STATUS;

$ mysqldump -p --databases twitter > twitter.sql

Restore the backup on the slave

$ mysql -p < twitter.sql

Configure the slave

Issue this query (change the data to fit your situation) on the slave

CHANGE MASTER TO
  MASTER_HOST='master.hostname',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000096',
  MASTER_LOG_POS=568,
  MASTER_CONNECT_RETRY=10;

Start the slave

Issue this query on the slave

START SLAVE;

Query the status of the replication

On the slave

SHOW SLAVE STATUS\G

On the master

SHOW MASTER STATUS;

SSL

This section is based on http://llawlor.github.io/2016/03/14/mysql-ssl-replication.html

On the master, create the SSL-keys:

mkdir /etc/ssl/certs/mysql
chown mysql /etc/ssl/certs/mysql
cd /etc/ssl/certs/mysql
openssl req -x509 -newkey rsa:2048 -keyout master-private.pem -out master-public.pem -nodes -days 3650

# convert the key to the correct format for mysql
openssl rsa -in master-private.pem -out master-private.pem

cp master-public.pem ca-cert.pem
chown mysql:mysql *

On the slave, add the SSL keys:

mkdir /etc/ssl/certs/mysql
chown mysql /etc/ssl/certs/mysql
cd /etc/ssl/certs/mysql

# transfer the certificate from the master database server to the slave database server
scp MASTER_USER@MASTER_IP_ADDRESS:/etc/ssl/certs/mysql/ca-cert.pem .

# create the slave's ssl keys
openssl req -x509 -newkey rsa:2048 -keyout slave-private.pem -out slave-public.pem -nodes -days 3650

# convert the key to the correct format for mysql
openssl rsa -in slave-private.pem -out slave-private.pem

# append the public key to the certificate
bash -c "cat slave-public.pem >> ca-cert.pem"

# change file ownerships back to mysql
chown mysql:mysql /etc/ssl/certs/mysql -R

# copy back file to master
scp ca-cert.pem foo.bar:/etc/ssl/certs/mysql/

At the master, change file ownership of the returned file

chown mysql:mysql ca-cert.pem

Update the configuration file /etc/mysql/maria.conf.d/50-server on the master to use SSL

[mysqld]
ssl-ca=/etc/ssl/certs/mysql/ca-cert.pem
ssl-cert=/etc/ssl/certs/mysql/master-public.pem
ssl-key=/etc/ssl/certs/mysql/master-private.pem

# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
# ssl-cipher=TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
ssl=on

Restart the master

systemctl restart mysql

On the slave give mysql permission to read a certificate

nano /etc/apparmor.d/usr.sbin.mysqld

Add this line:

/etc/ssl/certs/mysql/*.pem r,

Update the configuration file on the slave /etc/mysql/maria.conf.d/50-server to use SSL

ssl-ca=/etc/ssl/certs/mysql/ca-cert.pem
ssl-cert=/etc/ssl/certs/mysql/slave-public.pem
ssl-key=/etc/ssl/certs/mysql/slave-private.pem

# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
# ssl-cipher=TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
ssl=on

Restart the slave

systemctl restart mysql

Active the replication by issuing this on the slave

CHANGE MASTER TO
  MASTER_HOST='hansekbrand.se',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='really good password here',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='pimaster1-bin.000021',
  MASTER_LOG_POS=1487,
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/ssl/certs/mysql/ca-cert.pem',
  MASTER_CONNECT_RETRY=10;

If the slave gets out of sync

Diagnosis

mysql> SHOW SLAVE STATUS \G
...
Slave_SQL_Running: No
...

Stop the slave

mysql> STOP SLAVE;

Configure the slave to skip the failed step

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Start the slave

mysql> START SLAVE;

Confirm the success

mysql> SHOW SLAVE STATUS \G
...
Slave_SQL_Running: Yes
...

comments powered by Disqus

Back to the index

Blog roll

R-bloggers, Debian Weekly
Valid XHTML 1.0 Strict [Valid RSS] Valid CSS! Emacs Muse Last modified: maj 30, 2020