mariadb レプリケーション

【マスター側】

# vi /etc/my.cnf.d/server.cnf

[mysqld]

    server-id=1    ←追記

    log-bin             ←追記

# systemctl restart mariadb.service

再起動→バイナリログが有効になる。

MariaDB [(none)]> show global variables like 'log_bin';
+--------------------+--------+
| Variable_name | Value |
+--------------------+--------+
| log_bin                 | ON     |
+--------------------+--------+
1 row in set (0.001 sec)
MariaDB [(none)]> show binary logs;
+------------------------+------------+
| Log_name                | File_size |
+------------------------+------------+
| dbsv1-bin.000001 | 328            |
+------------------------+------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show variables like 'binlog_format';
+----------------------+---------+
| Variable_name    | Value  |
+----------------------+---------+
| binlog_format     | MIXED |
+----------------------+---------+
1 row in set (0.000 sec)

ariaDB [(none)]> SHOW MASTER STATUS;

+--------------------------+------------+--------------------+-------------------------+

| File                                  | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+--------------------------+------------+--------------------+-------------------------+

| dbsv1-bin.000003  |        385    |                                |                                        |

+--------------------------+------------+--------------------+-------------------------+

1 row in set (0.000 sec)

# mysql -u root -p
MariaDB [(none)]> create user 'repl'@'%'  identified by 'repl';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.002 sec)

# mysqldump -u root -ppassword --all-databases  --flush-logs --single-transaction --master-data=2 > master.db

# scp master.db   root@192.168.100.202:/tmp    ← スレーブに転送

# firewall-cmd --add-service=mysql --zone=public --permanent

# firewall-cmd --reload

【スレーブ側】

# vi /etc/my.cnf.d/server.cnf

[mysqld]

    server-id=2    ←追記

    log-bin             ←追記

    read_only      ←追記

# systemctl restart mariadb.service

再起動→バイナリログが有効になる。

# grep "CHANGE MASTER TO MASTER_LOG_FILE" master.db

-- CHANGE MASTER TO MASTER_LOG_FILE='dbsv1-bin.000003', MASTER_LOG_POS=385;

# mysql -u root -ppassword < master.db

# mysql -u root -p
MariaDB [(none)]> show slave status;
Empty set (0.000 sec)

※ Empty でなければ、

MariaDB [(none)]> stop slave;

MariaDB [(none)]> reset slave;

MariaDB [(none)]> quit

# systemctl restart mariadb.service

MariaDB [(none)]> change master to

   --> master_host='192.168.100.201',

   --> master_user='repl',

   -->  master_password='repl',

   -->  master_log_file='dbsv1-bin.000003',

   -->master_log_pos=385;

MariaDB [(none)]> start slave;

# firewall-cmd --add-service=mysql --zone=public --permanent

# firewall-cmd --reload