1、环境要求:
system:CetenOS 7
Server:
172.16.1.2 db1
172.16.1.3 db2
172.16.1.4 db3
模式:多主模式,通过wresp协议在全局实现底层数据片复制,任意节点可读写
2、开始配置
2.1 db1-3 创建三台服务器无秘钥登录
172.16.1.2 db1
vi /etc/hosts
–————编辑新增 i
172.16.1.2 db1
172.16.1.3 db2
172.16.1.4 db3
————保存 :wq
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub db1
ssh-copy-id -i /root/.ssh/id_rsa.pub db2
ssh-copy-id -i /root/.ssh/id_rsa.pub db3
172.16.1.3 db2
ssh db2
vi /etc/hosts
–————编辑新增 i
172.16.1.2 db1
172.16.1.3 db2
172.16.1.4 db3
————保存 :wq
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub db1
ssh-copy-id -i /root/.ssh/id_rsa.pub db2
ssh-copy-id -i /root/.ssh/id_rsa.pub db3
172.16.1.4 db3
ssh db3
vi /etc/hosts
–————编辑新增 i
172.16.1.2 db1
172.16.1.3 db2
172.16.1.4 db3
————保存 :wq
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub db1
ssh-copy-id -i /root/.ssh/id_rsa.pub db2
ssh-copy-id -i /root/.ssh/id_rsa.pub db3
ssh db1
2.3 DB1-3关闭slinux
vi /etc/selinux/config
修改SELINUX=disabled ,保存 然后重启服务器。
sestatus 查看状态
2.4 db1-3 卸载当前残留:
rpm -qa | grep mariadb
rpm -e –nodeps mariadb-libs-5.5.56-2.el7.x86_64
rpm -qa | grep mariadb
2.5 db1-3 同步时区
说明:执行tzselect命令–>选择Asia–>选择China–>选择 Beijing, Guangdong, Shanghai–>然后输入1 设置 并验证时间是否与本机相同
tzselect > 5 > 9 > 1 > 1
TZ=’Asia/Shanghai’; export TZ
rm -rf /etc/localtime
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
date
2.6 配置数据库源(172.16.1.2)
vi /etc/yum.repos.d/galera.repo
—————编辑
[galera]
name=galera
baseurl=http://yum.mariadb.org/5.5.57/centos7-amd64/
gpgcheck=0
—————保存
scp /etc/yum.repos.d/galera.repo 172.16.1.3:/etc/yum.repos.d/
scp /etc/yum.repos.d/galera.repo 172.16.1.4:/etc/yum.repos.d/
2.7 db1-3安装数据库
yum install MariaDB-Galera-server -y
mkdir /home/data
cp -R /var/lib/mysql/ /home/data/
chown -R mysql:mysql /home/data/mysql
chmod -R 777 /home/data/
2.8配置数据库 ssh db1(172.16.1.2)
rpm -ql galera | grep -i smm.so
/usr/lib64/galera/libgalera_smm.so
vi /etc/my.cnf.d/server.cnf
————————编辑
[mysqld]
datadir=/home/data/mysql
socket=/var/lib/mysql/mysql.sock
character_set_server=utf8
slow_query_log=on
slow_query_log_file=/home/data/show_query_log.log
long_query_time=2
lower_case_table_names=1[galera]
Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=”gcomm://172.16.1.2,172.16.1.3,172.16.1.4”
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name=”ysyj_galera_cluster”
—————–保存
scp /etc/my.cnf.d/server.cnf 172.16.1.34:/etc/my.cnf.d/server.cnf
scp /etc/my.cnf.d/server.cnf 172.16.1.36:/etc/my.cnf.d/server.cnf
/etc/init.d/mysql start –wsrep-new-cluster
db2-3:(172.16.1.3\172.16.1.4)
/etc/init.d/mysql start
数据同步测试:
Db1(172.16.1.2)
mysql
use mysql
UPDATE user SET password=password(‘passwd’) WHERE user=’root’;
flush privileges;
create database ceshi;
show databases;
exit
—————————-_查看db2 db3 是否同步了ceshi db
创建账号
create user ‘ceshi’@’localhost‘ identified by ‘ceshi001’
授权ceshi数据库的所有权限给该账号
grant all on ceshi.* to ceshi@’localhost’;
授权所有数据库的所有权限给该账号
grant all on . to ceshi@’localhost’;
导入数据看是否同步
mysql -uroot -pceshi001
create database ceshi;
use ceshi;
set names utf8;
source /home/ceshi.sql;
查看其他数据库的数据条数是否同步
select count(*) from ceshi; 查询数据库有多少条数据
查看集群状态
show status like ‘wsrep_%’;
Java代码配置链接方式
jdbc.url=jdbc:mysql:loadbalance://172.16.1.2:3306,172.16.1.3:3306,172.16.1.4:3306/ysyj_db?loadBalanceConnectionGroup=first&loadBalanceEnableJMX=true&useUnicode=true&characterEncoding=utf-8
jdbc.username=ceshi
jdbc.password=ceshi001
jdbc.initialSize=10
jdbc.maxActive=200
jdbc.minIdle=1
jdbc.maxWait=60000
datasource.proxy.interceptor=true #数据源切换拦截器使用标识,true:启用 false:停用