mariadb 10 multi-master multi-database replication

Unlike mysqld 5.X, mariadb 10.X offers multi-master multi-database replication. That means it can use a single mariadb-server 10.X instance as a replication slave for all the other mysql-servers we need. Unlike supporting this combination, the very installation and set-up is rather straight-forward:

1. Create CA-related files:

openssl genrsa 2048 > ca.key
openssl req -new -x509 -nodes -days 3650 -key ‘ca.key’ > ‘ca.crt’

2. Use those for all of the certificates you are going to need:

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ‘node001.key’ > ‘node001.csr’
openssl x509 -req -in ‘node001.csr’ -days 3600 -CA ‘ca.crt’ -CAkey ‘ca.key’ -set_serial 01 > ‘node001.crt’

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ‘node007.key’ > ‘node007.csr’
openssl x509 -req -in ‘node007.csr’ -days 3600 -CA ‘ca.crt’ -CAkey ‘ca.key’ -set_serial 07 > ‘node007.crt’

As seen above, repeat this for all servers/clients you need, keep ca.key and ca.crt safe, increase serial for compatibility requirements, do not repeat, do not use wild-cards.

3. Log into mysqld, check for SSL capability – looking for “DISABLED” here; if “NOT” is listed, SSL is not compiled:

mysql> show variables like ‘%ssl%’;
+—————+———-+
| Variable_name | Value |
+—————+———-+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+—————+———-+
7 rows in set (0.05 sec)

mysql> \s
————–
mysql Ver 14.14 Distrib 5.1.73, for unknown-openbsd5.6 (x86_64) using readline 4.3

Connection id: 15078
Current database:
Current user: vanja@node001
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.1.73-log OpenBSD port: mysql-server-5.1.73p0v0
Protocol version: 10
Connection: XX.XX.XX.XX via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 27 days 58 min 49 sec

Threads: 5 Questions: 796986 Slow queries: 0 Opens: 109 Flush tables: 2 Open tables: 64 Queries per second avg: 0.341

mysql> quit;
Bye

4. Add node’s cert to config, the best place depends on the version of mysql/mariadb, but, in general, /etc/my.cnf is always read:

node001 # ls -l /etc/my.cnf.d/certs/
total 20
-r——– 1 _mysql _mysql 1322 Apr 18 11:40 node001.crt
-r——– 1 _mysql _mysql 1070 Apr 18 11:40 node001.csr
-r——– 1 _mysql _mysql 1704 Apr 18 11:40 node001.key
-r——– 1 _mysql _mysql 1448 Apr 18 12:12 ca.crt
-r——– 1 _mysql _mysql 1675 Apr 18 12:12 ca.key

5. Create place for binary logs:

node001 # mkdir /var/log/mysql
node001 # chown -R _mysql:_mysql /var/log/mysql/

6. under [server] section of my.cnf or in another file, add:

ssl-ca = /etc/my.cnf.d/certs/ca.crt
ssl-cert = /etc/my.cnf.d/certs/node001.crt
ssl-key = /etc/my.cnf.d/certs/node001.key

Can put the same under [client] section, too, so client tools can use SSL, too. Under [server] section we also need:

server-id = 11

log_bin = /var/log/mysql/mysql-binary-log.log
expire_logs_days = 20
max_binlog_size = 100M
binlog_do_db = database1

A warning – server-id has to be unique on your Net, or dragons will burn your replication.

7. Re-read your server confs, and check your SSL first; it should say something like:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 203
Server version: 5.1.73-log OpenBSD port: mysql-server-5.1.73p0v0

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> \s
————–
mysql Ver 14.14 Distrib 5.1.73, for unknown-openbsd5.6 (x86_64) using readline 4.3

Connection id: 203
Current database:
Current user: vanja@node001
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.1.73-log OpenBSD port: mysql-server-5.1.73p0v0
Protocol version: 10
Connection: XX.XX.XX.XX via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 34 min 14 sec

Threads: 3 Questions: 7446 Slow queries: 0 Opens: 170 Flush tables: 3 Open tables: 64 Queries per second avg: 3.625
————–

mysql> show variables like ‘%ssl%’;
+—————+———————————————+
| Variable_name | Value |
+—————+———————————————+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/my.cnf.d/certs/ca.crt |
| ssl_capath | |
| ssl_cert | /etc/my.cnf.d/certs/node001.crt |
| ssl_cipher | |
| ssl_key | /etc/my.cnf.d/certs/node001.key |
+—————+———————————————+
7 rows in set (0.00 sec)

Don’t go further if SSL is not working – you have to sort that first, the replication has to be encrypted over the big bad Internet.

8. Once that is confirmed, add a user for replication. Make sure you add REQUIRE SSL at the end, otherwise the user will be able to choose between non-SSL and SSL session, and we never want an unencrypted session:

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘thename’@’%’ IDENTIFIED BY ‘thepassword’ REQUIRE SSL;

9. Pick the database you want to replicate, database1 on this server, in this example:

mysql> USE database1;
mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS;

+———————————+———-+——————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+———————————+———-+——————+——————+
| mysql-binary-log.000001 | 106 | database1 | |
+———————————+———-+——————+——————+

10. In another tty, dump the database1 for slave, and scp that to slave:

mysqldump -u root -p$rootspass -h node001 –ssl=1 database1 > database1.sql
scp ./database1.sql root@replikant:/tmp/

And then you can unlock tables with “mysql> UNLOCK TABLES;”

11. On mariadb 10.X node, “replikant”, check your [client] certs (and you can use SSL for  [server]  too, it won’t hurt) you previously made, just like for node001, node002…

replikant:/tmp # cat /etc/my.cnf.d/client.cnf
[client]
ssl-ca=/etc/my.cnf.d/certs/ca.crt
ssl-cert=/etc/my.cnf.d/certs/replikant.crt
ssl-key=/etc/my.cnf.d/certs/replikant.key

Be tidy, MySQL is very picky about SSL details, unlike Apache, or Nginx that would happily serve as many virtual nodes under same IP as you want under a wild-card cert. MySQL bites for every single tiny detail that is in some conflict with its order. Check SSL works, like for node001, and then add in your *.cnf the replicate-do-db directive:

replicate-do-db=database1

Later, when you want more databases from same master, mariadb 10.X can do that in comma separated line, too, like replicate-do-db=database1,database2… while in mysqld you need a separate replicate-do-db and binary-do-db lines for each database.

12. Create the database, and load data:

MariaDB [(none)]> create database database1;

And from sql file you’ve scp’d:

mysql -u root -p$rootspass –ssl=1 database1 < database1.sql

You are ready for final touch:

MariaDB [(none)]> CHANGE MASTER ‘node001′ to MASTER_HOST=’node001.example.org’, MASTER_USER=’thename’, MASTER_PASSWORD=’thepassword’, MASTER_LOG_FILE=’mysql-binary-log.000001′, MASTER_LOG_POS=106, MASTER_SSL=1, MASTER_SSL_CA = ‘/etc/my.cnf.d/certs/ca.crt’, MASTER_SSL_CERT = ‘/etc/my.cnf.d/certs/replikant.crt’, MASTER_SSL_KEY = ‘/etc/my.cnf.d/certs/replikant.key’;

13. Note the ability to address exact node with mariadb

MariaDB [(none)]> start slave ‘node001’;

14. there are a few other options for mariadb’s multi-master replication, check them out at mariadb’s pages.

15. Now the best part – add as many databases, from as many master-servers as you like ( ok, 64 for the start, but that can be sorted, too ) and you are ready to go with this el-cheapo, but very functional replication system! Have fun!

Leave a Reply

Your email address will not be published. Required fields are marked *