MySQL Replication in OpenBSD

Introduction

I am considering to do master-slave replication on my MySQL database to a remote or local server. Currently I do regular MySQL backups using mysqldump. But this creates large backup files which isn't very efficient if you want to replicate over internet frequently. So I am looking into MySQL bin log replication.

First install MariaDB and make basic MySQL setup.

pkg_add mariadb-server
less /usr/local/share/doc/pkg-readmes/mariadb-server-10.0.20p0v0
# follow instructions ...
rcctl start mysqld

The system I used was versioned as follows.

$ uname -a
OpenBSD arp.my.domain 5.8 GENERIC#1170 amd64
$ pkg_info | grep maria
mariadb-client-10.0.20p0v0 multithreaded SQL database (client)
mariadb-server-10.0.20p0v0 multithreaded SQL database (server)
$ mysql --version
mysql  Ver 15.1 Distrib 10.0.20-MariaDB, for OpenBSD (amd64) using readline 4.3

Setup Master Server

On OpenBSD the bin log is enabled by default in the mysql-server package.

The following items in /etc/my.cnf are relevant for binary logging.

# Replication Master Server (default)
# binary logging is required for replication
log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

gtid-domain-id = 0

# binary logging format - mixed recommended
binlog_format = mixed

skip-networking= 0
bind_address = 0.0.0.0

This means that you can find binary log files in /var/mysql/.

/var/mysql/mysql-bin.000001
/var/mysql/mysql-bin.000002
...

You can show the contents of a binary log by using the mysqlbinlog tool.

mysqlbinlog -v /var/mysql/mysql-bin.000001

Setup Slave

Slave must be setup to sync with master. Start mysqladmin and make the following.

mysqladmin -u root password
mysqlz CHANGE MASTER TO master_host="127.0.0.1", master_port=3310, master_user="root", master_use_gtid=current_pos;
START SLAVE;

You can check the status.

show slave status\G
show master status\G
...
error connecting to master 'root@174.136.105.178:3310' - retry-time: 60  retries: 86400  message: Can't connect to MySQL server on '174.136.105.178' (61 "Connection refused")

Try connectivity ...

tcpdump -i em0 'port 3306'
telnet arp 3306

https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/

Check if users has been synched. No doesn't seem so.

> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
+------+---------------+
| User | Host          |
+------+---------------+
| root | 127.0.0.1     |
| root | ::1           |
|      | arp.my.domain |
| root | arp.my.domain |
+------+---------------+

Check a table from the synched database.

select * from ops_production.players;

Check log.

sudo cat /var/mysql/think.lounge.se.err

160326 21:48:27 [Note] Error reading relay log event: slave SQL thread was killed
160326 21:48:27 [Note] Slave I/O thread killed while connecting to master
160326 21:48:27 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4; GTID position 
160326 21:49:03 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='174.136.105.178',         master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='174.136.105.178', master_port='3306', master_log_file='', master_log_pos='4'.
160326 21:49:03 [Note] Previous Using_Gtid=Slave_Pos. New Using_Gtid=Slave_Pos
160326 21:49:13 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 4, relay log './think-relay-bin.000001' position: 4; GTID position ''
160326 21:49:14 [ERROR] Slave I/O: error connecting to master 'root@174.136.105.178:3306' - retry-time: 60  retries: 86400  message: Host 'user199.82-197-239.netatonce.net' is not allowed to connect to this MariaDB server, Internal MariaDB error code: 1130

Finally I realized that permissions on master was not sufficient.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

160326 21:57:17 [Note] Slave I/O thread: connected to master 'root@174.136.105.178:3306',replication starts at GTID position ''
160326 21:57:20 [Note] Slave I/O thread stops because it reached its UNTIL master_gtid_pos 1-1-19,0-1-36
160326 21:57:20 [Note] Slave I/O thread exiting, read up to log 'arp-bin.000006', position 9752; GTID position 1-1-19,0-1-36
160326 21:57:20 [Note] Slave SQL thread stops because it reached its UNTIL master_gtid_pos 1-1-19,0-1-36
160326 21:57:20 [Note] Slave SQL thread exiting, replication stopped in log 'arp-bin.000006' at position 9752; GTID position '1-1-19,0-1-36'

Now I can access the data from the client ...

mysql> select * from ops_production.players;
...

References