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

一、添加yum源
[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
二、安装
说明:安装前如果之前安装mysql最后先卸载掉Mysql再安装( rpm -qa |grep mysql)
[root@node1 ~]# yum -y install MariaDB-server MariaDB-client
三、更改配置
配置文件路径:/etc/my.cnf.d/server.cnf
A. 多主多从配置(数据切片)
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
B. 数据汇总配置
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';
在master1和master2上数据库分别执行show master status;可以看到类似下面的表格
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;

您可以选择一种方式赞助本站

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: