MariaDB Replication
从master到slave的复制涉及到3个相关的线程，其中sql和i/o线程在slave端，另外一个i/o线程在master端
1.创建具有Replication相关权限的数据库用户(所有节点)
FLUSH PRIVILEGES;
db00: 192.168.8.100(master)
[mysqld]
band_address = 192.168.8.100
log-bin = mysql-bin
server-id = 100
relay_log =
db00-relay-binlog
db01: 192.168.8.101(slave)
[mysqld]
band_address = 192.168.8.101
log-bin = mysql-bin
server-id = 101
relay-log = db01-relay-binlog
read-only
db02: 192.168.8.102(slave)
[mysqld]
band_address = 192.168.8.102
log-bin = mysql-bin
server-id = 102
relay-log = db02-relay-binlog
read-only
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File
+------------------+----------+--------------+------------------+
|
mysql-bin.000002
|
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6.指定master节点(所有slave节点)
7.查看slave状态
确认Slave_IO_Running，Slave_SQL_Running都为Yes，说明slave端sql和i/o线程都正常运行
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Master_SSL_Verify_Server_Cert: No
1 row in set (0.00 sec)
8.测试主从同步
在master节点上创建数据库和表
CREATE DATABASE IF NOT EXISTS temp;
USE temp;
CREATE TABLE doctors (
INSERT INTO doctors VALUES
在slave端查询
MariaDB [(none)]> SELECT * FROM temp.doctors;
+----+-------------+-----------+------------+
| id |
given_names | surname
+----+-------------+-----------+------------+
|
|
|
|
+----+-------------+-----------+------------+
4 rows in set (0.00 sec)
9.更安全的Replication
[mysqld]
innodb_flush_logs_at_trx_commit
= 1
sync_binlog
= 1
尽可能快地将数据写入磁盘，减少数据损坏的可能性，但也失去了fsync的性能，mariadb开发团队已经内嵌了一个尽可能联合fsync的调优，让sync写磁盘的同时，最大提升性能。
10.开启relay log(slave节点, 可选)
默认情况下，slave只是应用master的bin-log,slave本地不存有log,
[mysqld]
log_slave_updates
参数可以让slave保存自己单独的log,称为relay log, 可以为启用了relay log的slave再配置其它的slave
11.Global transaction IDs
https://mariadb.com/kb/en/global-transaction-id/
mariadb10引进的新功能
在所有的slave节点上操作，
STOP
SLAVE;
CHANGE
MASTER TO MASTER_USE_GTID = SLAVE_POS;
START
SLAVE;
SHOW ALL SLAVES STATUS;
MariaDB [temp]> SHOW ALL SLAVES STATUS\G
*************************** 1. row ***************************
Master_SSL_Verify_Server_Cert: No
1 row in set (0.00 sec)
注意防火墙
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload
11.测试从库read-only
master节点
[root@db00 ~]# mysql -uroot -p
Enter
password:
Welcome to the MariaDB
monitor.
Your MariaDB connection id is 6
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE
DATABASE
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> GRANT ALL ON hello.* TO 'test_read_only'@'localhost' identified by 'hello';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> USE hello;
Database changed
MariaDB [hello]> CREATE TABLE doctors (
Query OK, 0 rows affected (0.02 sec)
MariaDB [hello]> INSERT INTO doctors
VALUES
Query OK, 4 rows affected (0.00 sec)
Records: 4
slave节点
[root@db01 ~]# mysql -utest_read_only -phello
Welcome to the MariaDB
monitor.
Your MariaDB connection id is 8
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE hello;
Database changed
MariaDB [hello]> SELECT * FROM doctors;
+----+-------------+-----------+------------+
| id | given_names | surname
+----+-------------+-----------+------------+
|
|
|
|
+----+-------------+-----------+------------+
4 rows in set (0.00 sec)
MariaDB [hello]> INSERT INTO doctors (given_names,surname,birthdate) VALUES ('test','test_read_only','2016-04-10');
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
MariaDB
[hello]>
1.创建具有Replication相关权限的数据库用户(所有节点)
db00: 192.168.8.100(master)
[mysqld]
band_address = 192.168.8.100
log-bin = mysql-bin
server-id = 100
relay_log =
db00-relay-binlog
db01: 192.168.8.101(master)
[mysqld]
band_address = 192.168.8.101
log-bin = mysql-bin
server-id = 101
relay-log = db01-relay-binlog
db02: 192.168.8.102(slave)
[mysqld]
band_address = 192.168.8.102
log-bin = mysql-bin
server-id = 102
relay-log = db02-relay-binlog
read-only
replicate_ignore_db =
mysql,information_schema,performance_schema
replicate-do-db
replicate-do-table
replicate-ignore-table
CHANGE MASTER 'db00' TO MASTER_HOST='192.168.8.100',
CHANGE MASTER 'db01' TO MASTER_HOST='192.168.8.101',
START ALL SLAVES;
SHOW ALL SLAVES STATUS;
可以单独对某个SLAVE进行相关处理，如：
STOP SLAVE 'db01';
SHOW SLAVE 'db01' STATUS;
START SLAVE 'db01;
MariaDB [(none)]> SHOW ALL SLAVES STATUS\G
*************************** 1. row ***************************
Master_SSL_Verify_Server_Cert: No
*************************** 2. row ***************************
Master_SSL_Verify_Server_Cert: No
2 rows in set (0.00 sec)
不巧了，同步失败，正好作为同步失败的案例处理，主要是因为slave读取了一个master不存在的Pos
解决办法，正确指定master的log_file文件，
STOP ALL SLAVES;
CHANGE MASTER 'db00' TO MASTER_HOST='192.168.8.100',
CHANGE MASTER 'db01' TO MASTER_HOST='192.168.8.101',
START ALL SLAVES;
注意:
MASTER_LOG_FILE可以在master端SHOW MASTER STATUS;查得
MASTER_LOG_POS需要通过mysqlbinlog工具来查看MASTER_LOG_FILE获取到正确的偏移量，如：
mysqlbinlog /opt/mariadb/data/mysql-bin.000003|grep pos bin_000003.log|tail -1|awk '{print $6"\t"$7}'
MariaDB [(none)]> SHOW ALL SLAVES STATUS\G
*************************** 1. row ***************************
Master_SSL_Verify_Server_Cert: No
*************************** 2. row ***************************
Master_SSL_Verify_Server_Cert: No
2 rows in set (0.00 sec)
6.测试
db00,db01分别建库，建表插入数据等操作查看，slave的同步情况
同步失败解决思路补充：
在Master上
update mysql.user set super_priv =
'Y' where user = 'root'; flush privileges;
在Slave上
停止slave: stop
slave;
设置跳过slave同步语句数： set global sql_slave_skip_counter =
1;
启动slave： start
slave;
确认同步是否正确： show slave status
\G
三.互为主从
[mysqld]
band_address = 192.168.8.101
log-bin = mysql-bin
server-id = 101
relay-log = db01-relay-binlog
replicate_ignore_db =
mysql,information_schema,performance_schema
[mysqld]
band_address = 192.168.8.101
log-bin = mysql-bin
server-id = 101
relay-log = db01-relay-binlog
replicate_ignore_db =
mysql,information_schema,performance_schema
2.启服务
systemctl start mysql
4.指定master
db01: 192.168.8.101
CHANGE
MASTER
START SLAVE;
db02: 192.168.8.102
CHANGE
MASTER
START SLAVE;
[root@db01 ~]# mysql -uroot -p
Enter
password:
Welcome to the MariaDB
monitor.
Your MariaDB connection id is 14
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Master_SSL_Verify_Server_Cert: No
1 row in set (0.00 sec)
[root@db02 ~]# mysql -uroot -p
Enter
password:
Welcome to the MariaDB
monitor.
Your MariaDB connection id is 18
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Master_SSL_Verify_Server_Cert: No
1 row in set (0.00 sec)
5.测试
db01: 192.168.8.101
CREATE DATABASE IF NOT EXISTS db01;
USE db01;
CREATE TABLE doctors (
INSERT INTO doctors VALUES
db02: 192.168.8.102
CREATE DATABASE IF NOT EXISTS db02;
USE db02;
CREATE TABLE doctors (
INSERT INTO doctors VALUES
MariaDB [db02]> SHOW DATABASES;
+--------------------+
|
Database
+--------------------+
| db01
| db02
| information_schema |
|
mysql
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [db02]> SELECT * FROM db01.doctors;
+----+-------------+-----------+------------+
| id |
given_names | surname
+----+-------------+-----------+------------+
|
|
|
|
+----+-------------+-----------+------------+
4 rows in set (0.00 sec)
MariaDB [db02]> SELECT * FROM db02.doctors;
+----+-------------+-----------+------------+
| id |
given_names | surname
+----+-------------+-----------+------------+
|
|
|
|
+----+-------------+-----------+------------+
4 rows in set (0.00 sec)
四.增强型bin-log row注释
mysqlbinlog /opt/mariadb/data/mysql-bin.000005
因为加入了注释，所以查找语句或者事件更为方便，当然，某些情况下，不希望看到这些注释时，可以加上--skip-annotate-row-events参数
[root@db01 ~]# mysqlbinlog /opt/mariadb/data/mysql-bin.000005 >annoate.sql
[root@db01 ~]# mysqlbinlog --skip-annotate-row-events /opt/mariadb/data/mysql-bin.000005 >no_annoate.sql
[root@db01 ~]# diff
no_annoate.sql annoate.sql
62a63,64
>
#160410
> #Q> INSERT INTO t1 VALUES ('a'),('b'),('c'),('d')
88a91,92
>
#160410
> #Q> INSERT INTO t2 VALUES ('a'),('b'),('c'),('d')
五.bin log event校验
MariaDB [(none)]> SHOW VARIABLES LIKE '%checksum';
+---------------------------+-------+
|
Variable_name
+---------------------------+-------+
|
aria_page_checksum
|
binlog_checksum
|
master_verify_checksum
|
slave_sql_verify_checksum | ON
+---------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'skip_replication';
+------------------+-------+
|
Variable_name
+------------------+-------+
|
skip_replication | ON
+------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
|
Database
+--------------------+
|
hello
| information_schema |
|
mysql
| pe
| performance_schema |
| temp
| test
| w
| wxyz
+--------------------+
9 rows in set (0.00 sec)
说明：
replicate_events_marked_for_skip
值为FILTER_ON_MASTER，FILTER_ON_SLAVE时结果都是不执行复制动作，唯一的区别是在SLAVE过滤时经过了网络传输
MariaDB [(none)]> SHOW VARIABLES LIKE 'skip_replication';
+------------------+-------+
|
Variable_name
+------------------+-------+
|
skip_replication | OFF
+------------------+-------+
1 row in set (0.00 sec)
