This is a plain list of actions I did when creating a MariaDB database master-slave replication setup. It is assumed that you already know why you want to create such a configuration and how you can use it in your case. Some links anyway:
Global Transaction ID (mariadb.com): “[…] it is generally recommended to use global transaction ID for any replication setups based on MariaDB 10.0.2 or later.“
Starting situation:
- Zabbix server 4.4 has been installed and configured according to the Zabbix instructions, on Debian Linux 10 (Buster), using MariaDB 10.3.18 from Debian packages (including
mariadb-backup) mysql_secure_installationhas been run- The Zabbix database name is
zabbix - The server name is
zabbixtest, IP address is 10.10.10.170 - (Note that this replication setup is by no means specific to Zabbix. Zabbix is only used here as an example of an application using the database.)
New data is coming in:
root@zabbixtest:~# mysql zabbix ... MariaDB [zabbix]> select count(*) from history; +----------+ | count(*) | +----------+ | 192 | +----------+ 1 row in set (0.000 sec) MariaDB [zabbix]> (after a small wait) MariaDB [zabbix]> select count(*) from history; +----------+ | count(*) | +----------+ | 209 | +----------+ 1 row in set (0.000 sec) MariaDB [zabbix]> quit; Bye root@zabbixtest:~#
The second server has also been installed:
- MariaDB 10.3.18 from Debian (including
mariadb-backup) -
mysql_secure_installationhas been run - Server name is
dbtest, IP address is 10.10.10.171
Zabbixtest server will be our replication master, and dbtest will be the replication slave.
Prepare the master for replication
On the master server (zabbixtest):
root@zabbixtest:~# mysql ... MariaDB [(none)]> create user 'replication'@'10.10.10.171' identified by 'replpassword'; Query OK, 0 rows affected (0.006 sec) MariaDB [(none)]> grant replication slave on *.* to 'replication'@'10.10.10.171'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> quit; Bye root@zabbixtest:~#
Configure the master server appropriately, in this setup a configuration file /etc/mysql/mariadb.conf.d/90-custom.cnf is used with these contents:
[mysqld] skip_name_resolve bind_address = 0.0.0.0 server_id = 1 log_basename = server1 log_bin = server1-bin max_binlog_size = 200M expire_logs_days = 3
MariaDB server needs to be restarted after the changes:
root@zabbixtest:~# systemctl restart mariadb root@zabbixtest:~#
Backup the databases and copy the files to the new slave server
Here the instructions from https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/ are used.
Create a database user for backup tasks:
root@zabbixtest:~# mysql ... MariaDB [(none)]> create user 'backup'@'localhost' identified by 'backuppassword'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> grant reload, process, lock tables, replication client on *.* to 'backup'@'localhost'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> quit; Bye root@zabbixtest:~#
Run the backup:
root@zabbixtest:~# mariabackup --backup --target-dir=/var/tmp/mariabackup --user=backup --password=backuppassword [00] 2019-12-26 16:21:05 Connecting to MySQL server host: localhost, user: backup, password: set, port: not set, socket: /var/run/mysqld/mysqld.sock [00] 2019-12-26 16:21:05 Using server version 10.3.18-MariaDB-0+deb10u1-log mariabackup based on MariaDB server 10.3.18-MariaDB debian-linux-gnu (x86_64) [00] 2019-12-26 16:21:05 uses posix_fadvise(). [00] 2019-12-26 16:21:05 cd to /var/lib/mysql/ [00] 2019-12-26 16:21:05 open files limit requested 0, set to 1024 [00] 2019-12-26 16:21:05 mariabackup: using the following InnoDB configuration: [00] 2019-12-26 16:21:05 innodb_data_home_dir = [00] 2019-12-26 16:21:05 innodb_data_file_path = ibdata1:12M:autoextend [00] 2019-12-26 16:21:05 innodb_log_group_home_dir = ./ [00] 2019-12-26 16:21:05 InnoDB: Using Linux native AIO 2019-12-26 16:21:05 0 [Note] InnoDB: Number of pools: 1 [00] 2019-12-26 16:21:05 mariabackup: Generating a list of tablespaces 2019-12-26 16:21:05 0 [Warning] InnoDB: Allocated tablespace ID 175 for zabbix/slides, old maximum was 0 [00] 2019-12-26 16:21:05 >> log scanned up to (18528664) [01] 2019-12-26 16:21:05 Copying ibdata1 to /var/tmp/mariabackup/ibdata1 [01] 2019-12-26 16:21:05 ...done ... [00] 2019-12-26 16:21:08 completed OK! root@zabbixtest:~#
Prepare the backup and create a compressed archive for transferring it:
root@zabbixtest:~# mariabackup --prepare --target-dir=/var/tmp/mariabackup mariabackup based on MariaDB server 10.3.18-MariaDB debian-linux-gnu (x86_64) [00] 2019-12-26 16:22:50 cd to /var/tmp/mariabackup/ [00] 2019-12-26 16:22:50 This target seems to be not prepared yet. [00] 2019-12-26 16:22:50 mariabackup: using the following InnoDB configuration for recovery: [00] 2019-12-26 16:22:50 innodb_data_home_dir = . [00] 2019-12-26 16:22:50 innodb_data_file_path = ibdata1:12M:autoextend [00] 2019-12-26 16:22:50 innodb_log_group_home_dir = . [00] 2019-12-26 16:22:50 InnoDB: Using Linux native AIO [00] 2019-12-26 16:22:50 Starting InnoDB instance for recovery. [00] 2019-12-26 16:22:50 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 2019-12-26 16:22:50 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2019-12-26 16:22:50 0 [Note] InnoDB: Uses event mutexes 2019-12-26 16:22:50 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2019-12-26 16:22:50 0 [Note] InnoDB: Number of pools: 1 2019-12-26 16:22:50 0 [Note] InnoDB: Using SSE2 crc32 instructions 2019-12-26 16:22:50 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M 2019-12-26 16:22:50 0 [Note] InnoDB: Completed initialization of buffer pool 2019-12-26 16:22:50 0 [Note] InnoDB: page_cleaner coordinator priority: -20 2019-12-26 16:22:50 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=18526539 2019-12-26 16:22:50 0 [Note] InnoDB: Starting final batch to recover 13 pages from redo log. 2019-12-26 16:22:51 0 [Note] InnoDB: Last binlog file './server1-bin.000002', position 20641 [00] 2019-12-26 16:22:51 Last binlog file ./server1-bin.000002, position 20641 [00] 2019-12-26 16:22:51 completed OK! root@zabbixtest:~# tar cfvz /var/tmp/backup.tar.gz -C /var/tmp mariabackup mariabackup/ mariabackup/aria_log_control ... mariabackup/backup-my.cnf mariabackup/ib_logfile0 root@zabbixtest:~#
Copy the archive to dbtest server:
root@zabbixtest:~# ls -l /var/tmp/backup.tar.gz -rw-r--r-- 1 root root 2882180 Dec 26 16:23 /var/tmp/backup.tar.gz root@zabbixtest:~# scp /var/tmp/backup.tar.gz markku@10.10.10.171: markku@10.10.10.171's password: backup.tar.gz 100% 2919KB 106.0MB/s 00:00 root@zabbixtest:~#
Import the backup in the new server
On dbtest server:
markku@dbtest:~$ ls -l total 2920 -rw-r--r-- 1 markku markku 2882180 Dec 26 16:24 backup.tar.gz markku@dbtest:~$ tar xzfv backup.tar.gz mariabackup/ mariabackup/aria_log_control mariabackup/xtrabackup_info ... mariabackup/backup-my.cnf mariabackup/ib_logfile0 markku@dbtest:~$
Stop the MariaDB server, delete the existing data (yes, this will delete all the existing data on the slave database server), and import the backup data from master:
markku@dbtest:~$ sudo -i [sudo] password for markku: root@dbtest:~# systemctl stop mariadb root@dbtest:~# rm -rf /var/lib/mysql/* root@dbtest:~# mariabackup --copy-back --target-dir=/home/markku/mariabackup mariabackup based on MariaDB server 10.3.18-MariaDB debian-linux-gnu (x86_64) [01] 2019-12-26 16:25:38 Copying ibdata1 to /var/lib/mysql/ibdata1 [01] 2019-12-26 16:25:39 ...done ... [00] 2019-12-26 16:25:43 completed OK! root@dbtest:~#
Set the server file ownerships:
root@dbtest:~# chown -R mysql:mysql /var/lib/mysql root@dbtest:~#
Set the slave configurations
On the slave server, configure the database appropriately, here /etc/mysql/mariadb.conf.d/90-custom.cnf is used with these options:
[mysqld] skip-name-resolve server_id = 2 # Must be different on each server log_basename = server2
Start the MariaDB server on the slave:
root@dbtest:~# systemctl start mariadb root@dbtest:~#
Get the GTID value recorded on the backup files:
root@dbtest:~# cat /home/markku/mariabackup/xtrabackup_binlog_info server1-bin.000002 20641 0-1-1048 root@dbtest:~#
Here the value is “0-1-1048”.
Now set up the replication, using the GTID value in your binlog info file above, and start slave:
root@dbtest:~# mysql ... MariaDB [(none)]> set global gtid_slave_pos = "0-1-1048"; Query OK, 0 rows affected (0.009 sec) MariaDB [(none)]> change master to master_host="10.10.10.170", master_connect_retry=10, master_user="replication", master_password="replpassword", master_use_gtid=slave_pos; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.004 sec) MariaDB [(none)]>
Now the replication should be running. Check the slave status:
MariaDB [(none)]> show slave status\G
********************* 1. row ***********************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.170
Master_User: replication
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: server1-bin.000002
Read_Master_Log_Pos: 195903
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 175950
Relay_Master_Log_File: server1-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 195903
Relay_Log_Space: 176261
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-1650
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 602
1 row in set (0.000 sec)
MariaDB [(none)]>
In the output above, the Seconds_Behind_Master value should be 0 if the replication has been correctly caught up with the master.
You can also check the data: On the master server:
root@zabbixtest:~# mysql zabbix ... MariaDB [zabbix]> select count(*) from history; +----------+ | count(*) | +----------+ | 1801 | +----------+ 1 row in set (0.001 sec)
And on the slave server:
root@dbtest:~# mysql zabbix ... MariaDB [zabbix]> select count(*) from history; +----------+ | count(*) | +----------+ | 1801 | +----------+ 1 row in set (0.001 sec)
The output counts should match, given that you check them fast enough.
Some additional configuration options
Be sure to read the documentation before adjusting the configuration options.
binlog_cache_size: Consider increasing the configuration value if the cache disk usage is increasing. In this testing setup:
root@zabbixtest:~# mysql ... MariaDB [(none)]> show variables like 'binlog_cache_size'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | binlog_cache_size | 32768 | +-------------------+-------+ 1 row in set (0.001 sec) MariaDB [(none)]> show status like 'binlog_cache%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 59641 | +-----------------------+-------+ 2 rows in set (0.001 sec)
innodb_buffer_pool_size, innodb_buffer_pool_instances: Currently the default buffer is 128 MB (in one instance), which is usually unnecessarily small. The usual recommendation for the buffer pool size is 70-80% of the system RAM on a dedicated database server. The number of pool instances should be set so that each instance is about 1 GB in size (i.e. if buffer size is set to 8 GB, instances should be set to 8).
innodb_flush_log_at_trx_commit: Default value is 1 (log is flushed to disk after each transaction). More performance can be obtained with values of 0 (write and flush once a second) or 2 (write after each commit but flush once a second).
innodb_flush_method: Default for MariaDB 10.3.7+ is fsync. In some cases O_DIRECT is recommended.
max_allowed_packet: Default is 16 MB. You can increase it if you get related error messages.