Setup MariaDB Slave
I managed to setup MariaDB slave master replication. Slave host is running CentOS Atomic Host.
-bash-4.2# df -kh Filesystem Size Used Avail Use% Mounted on /dev/mapper/cah_atomic-root 3.0G 1.5G 1.6G 49% / devtmpfs 478M 0 478M 0% /dev tmpfs 497M 0 497M 0% /dev/shm tmpfs 497M 460K 497M 1% /run tmpfs 497M 0 497M 0% /sys/fs/cgroup /dev/mapper/vg_bierochs_atomic-lv_storage 10G 3.3G 6.8G 33% /var/storage /dev/vda1 297M 146M 152M 49% /boot tmpfs 100M 0 100M 0% /run/user/1000 -bash-4.2# ls -lasZ /var/storage/mariadb/* /var/storage/mariadb/data: total 29764 drwxrwxrwx. root root unconfined_u:object_r:svirt_sandbox_file_t:s0 . drwxr-xr-x. root root unconfined_u:object_r:svirt_sandbox_file_t:s0 .. /var/storage/mariadb/my.cnf.d: total 4 drwxr-xr-x. root root unconfined_u:object_r:svirt_sandbox_file_t:s0 . drwxr-xr-x. root root unconfined_u:object_r:svirt_sandbox_file_t:s0 .. -rw-r--r--. root root unconfined_u:object_r:svirt_sandbox_file_t:s0 replication_slave.cnf -bash-4.2# cat /var/storage/mariadb/my.cnf.d/replication_slave.cnf [mysqld] log-bin=mysql-bin server-id=2 read_only=1 report-host=db-replica.bierochs.org -bash-4.2# docker run --name=mariadb -d -p 3306:3306 -v /var/storage/mariadb/my.cnf.d:/etc/my.cnf.d -v /var/storage/mariadb/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=ayNVFrna centos/mariadb55-centos7 c4e3c98257c8386b2e2651b0a655b7c1b08ac2de187608c44a7b86cea95a036d
[root@fiber ~]# cat /etc/my.cnf.d/replication_master.cnf [mysqld] log-basename=master log-bin=mysql-bin binlog-format=row server-id=1 [root@fiber ~]# tar -zcvf mysql_dump.sql.tar.gz mysql_dump.sql mysql_dump.sql [root@fiber ~]# mysql -h 192.168.1.40 -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.44-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> Select @@global.max_allowed_packet; +-----------------------------+ | @@global.max_allowed_packet | +-----------------------------+ | 1048576 | +-----------------------------+ 1 row in set (0.01 sec) MariaDB [(none)]> SET GLOBAL max_allowed_packet=500000000; Query OK, 0 rows affected, 1 warning (0.03 sec) MariaDB [(none)]> Select @@global.max_allowed_packet; +-----------------------------+ | @@global.max_allowed_packet | +-----------------------------+ | 499999744 | +-----------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> exit Bye
[root@fiber ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1727 Server version: 5.5.44-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> grant replication slave on *.* to replica@'%' identified by 'password'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush tables with read lock; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 18215811 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> exit Bye [root@fiber ~]# mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql Enter password: [root@fiber ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1812 Server version: 5.5.44-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> unlock tables; Query OK, 0 rows affected (0.17 sec) MariaDB [(none)]> exit Bye
[root@fiber ~]# mysql -h 192.168.1.40 -u root -p < mysql_dump.sql Enter password: [root@fiber ~]# mysql -h 192.168.1.40 -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.44-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> change master to master_host='192.168.1.72', master_user='replica', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=18215811; Query OK, 0 rows affected (0.54 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.72 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 25290291 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 44951 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Could not execute Write_rows event on table jiradb.rundetails; Duplicate entry '3906574' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 18260473 Skip_Counter: 0 Exec_Master_Log_Pos: 18260233 Relay_Log_Space: 7075305 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows event on table jiradb.rundetails; Duplicate entry '3906574' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 18260473 Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.01 sec)
