mariadb 主从配置、数据库切片、多主多从加汇总

[root@node1 ~]# vim /etc/yum.repos.d/MariaDB.repo 
#MariaDB 10.1 CentOS repository list - created 2016-05-18 02:33 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

[root@node1 ~]# yum -y install MariaDB-server MariaDB-client

master1配置如下(192.168.10.30)
[mysqld]
# * Galera-related settings
server-id =30
log_bin =/var/log/mysql/mysql-bin
binlog_format = row
log-slave-updates
sync_binlog =1

slave1配置如下(192.168.10.31)
[mysqld]
# * Galera-related settings
server-id =31
log-bin=mysql-bin
log-slave-updates
relay-log=relay-log-bin

master2配置如下(192.168.10.32)（注意server-id 不一样）
[mysqld]
# * Galera-related settings
server-id =32
log-bin=/var/log/mysql/mysql-bin
binlog_format = row
log-slave-updates
sync_binlog =1

slave2配置如下(192.168.10.33)
[mysqld]
# * Galera-related settings
server-id =33
log-bin=mysql-bin
log-slave-updates
relay-log=relay-log-bin

1. 汇总slave库配置，即当从主也当从（192.168.10.34）

# * Galera-related settings
server-id =34
log-bin=mysql-bin
log-slave-updates
relay-log=relay-log-bin

# * 过滤掉不需要同步的库
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = test.%
replicate-wild-ignore-table = information_schema.%
replicate-wild-ignore-table = performance_schema.%
replicate-wild-ignore-table = sync_test.%     //过滤库
replicate-wild-ignore-table = %.table_test    //过滤表

2. 添加映射

[root@node5 ~]# vim /etc/my.conf.d/test.conf
[mysqld]
replicate-rewrite-db=test0->test 
replicate-rewrite-db=test1->test
replicate-rewrite-db=test2->test
replicate-rewrite-db=test3->test
replicate-rewrite-db=test4->test
replicate-rewrite-db=test5->test

为了方便写个简单的shell脚步

#!/bin/bash
echo "[mysqld]" >> yc_order.cnf;
for((i=0; $i<1024; i++)); do
 echo "replicate-rewrite-db=test${i}->test" >> test.cnf;
done

3. 汇总库slave1配置(192.168.10.35)

# * Galera-related settings
server-id =35
log-bin=mysql-bin
log-slave-updates
relay-log=relay-log-bin

1. master1（192.168.10.30）

mysql> grant replication slave,replication client on *.* to 'root''192.168.10.34' identified by '123456'; 
mysql> grant replication slave,replication client on *.* to 'root''192.168.10.31' identified by '123456';

2. slave1 (192.168.10.31)

mysql> change master to master_host='192.168.10.30',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1;
mysql> start slaves;

3. master2（192.168.10.32）

mysql> grant replication slave,replication client on *.* to 'root''192.168.10.34' identified by '123456';
mysql> grant replication slave,replication client on *.* to 'root''192.168.10.33' identified by '123456';

4. slave2 (192.168.10.33)

mysql> change master to master_host='192.168.10.32',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1;
mysql> start salves;

5. 汇总slave库（192.168.10.34）

mysql> grant replication slave,replication client on *.* to 'root''192.168.10.35' identified by '123456';

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 110459872 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

把master1和master2上的File、Position分别填在master_log_file、master_log_pos上

登录汇总库数据库分别执行以下语句

mysql> change master 'r1' to master_host='192.168.10.30',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1;
mysql> change master 'r2' to master_host='192.168.10.32',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1;

开始同步数据

mysql> start all slaves;

检查数据是否同步

mysql> show all slaves status\G

6. 汇总slave1库（192.168.10.35）

mysql> change master to master_host='192.168.10.34',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1;
mysql> start salves;