MariaDB集群环境搭建

2.2 打开iptables服务:
vim /etc/sysconfig/iptables
添加一行：
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT/sbin/iptables -I INPUT -p vrrp -j ACCEPT
然后重启服务：service iptables restart

2.3 关闭selinux功能及设置内核参数：
vim /etc/sysconfig/selinux
设置SELINUX=disabled
Setenforce 0临时关闭selinux
使用getenforce 查看selinux的状态。

echo "1" >/proc/sys/net/ipv4/ip_forward
/sbin/sysctl -p

2.4 卸载mysql的软件包。
rpm -e mysql-libs --nodeps

2.5 设置MariaDB的源。
cd /etc/yum.repos.d/
新增一个文件，内容如下：
vim /etc/yum.repos.d/MariaDB.repo
[mariadb]
name=MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1



[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.0/centos6-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

#centOS7
[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.0/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1


yum clean all
yum makecache


2.6安装MariaDB的软件包。
yum -y install MariaDB-server mariadb-server-galera

修改Mariadb配置文件：
vim /etc/my.cnf.d/server.cnf
在[mysqld]行下加入以下内容：
skip-name-resolve
lower_case_table_names=1
max_allowed_packet=32M
character-set-server=utf8
max_connections = 1000
group_concat_max_len = 102400
innodb_file_per_table
innodb_file_format = Barracuda
innodb_strict_mode = 0


集群配置：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-name-resolve
lower_case_table_names=1
max_allowed_packet=2048M
max_connections = 1000
group_concat_max_len = 102400
default-storage-engine = innodb
innodb_file_per_table
innodb_file_format = Barracuda
innodb_strict_mode = 0
expire_logs_days=2

#
# * Galera-related settings
#
[galera]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='reach'
wsrep_cluster_address = "gcomm://172.16.6.110,172.16.6.227"
wsrep_node_name = x1
wsrep_node_address=172.16.6.227
wsrep_on=ON
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
wsrep_sst_method=rsync
wsrep_sst_auth=root:Jjjt@china
bind-address=0.0.0.0


启动第一个集群节点：
galera_new_cluster
其它节点启动：
systemctl start mariadb

集群重启时要从最后一个结点开始
如果都启动不了，需要在最后一个节点修改data目录下的文件：
vim /var/lib/mysql/grastate.dat
修改safe_to_bootsrrap值为1；
其余节点删除data目录下的两个文件：
rm -f galera.cache gvwstate.dat

在MariaDB1上执行语句：
grant all privileges on *.* to sa@'%' identified by 'sa';
flush privileges;
在MariaDB2上执行语句：
grant all privileges on *.* to sa@'%' identified by 'pass$123';
flush privileges;

mariadb配置Jemalloc内存管理：
Jemalloc安装：wget https://github.com/jemalloc/jemalloc/releases/download/5.2.1/jemalloc-5.2.1.tar.bz2
tar -jxvf jemalloc-5.2.1.tar.bz2
cd jemalloc-5.2.1
yum -y install gcc-c++  pcre pcre-devel zlib zlib-devel openssl openssl-devel
./configure -prefix=/usr/local/jemalloc
make
make install
vim /usr/lib/systemd/system/mariadb.service
在[server]下加入以下内容：
Environment="LD_PRELOAD=/usr/local/lib/libjemalloc.so"
systemctl daemon-reload
重启 mariadb

#proxsql安装(安装前需要先安装mysql客户端)：
/etc/yum.repos.d/proxysql.repo
输入以下内容后保存：
[proxysql_repo]
name= ProxySQL
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.3.x/centos/8
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

执行：
yum -y install proxysql

systemctl start proxysql
mysql -uadmin -padmin -h127.0.0.1 -P6032

#配置
set mysql-default_charset='utf8mb4';
set mysql-default_collation_connection='utf8mb4_general_ci';
set mysql-max_allowed_packet=67108864; 
set mysql-max_connections=10000;   

load mysql variables to runtime;
save mysql variables to disk;

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'0.0.0.0',3306,1,'Write Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'0.0.0.0',3306,1,'Read Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'0.0.0.0',3306,1,'Read Group');

load mysql servers to runtime;
save mysql servers to disk;
select * from mysql_servers;


insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('sydw','88888888',10,1);

load mysql users to runtime;
save mysql users to disk;
select * from mysql_users;

#主数据库端用root用户本机执行
CREATE USER 'monitor'@'*' IDENTIFIED BY 'monitor';

GRANT SELECT ON *.* TO 'monitor'@'*' WITH GRANT OPTION;

FLUSH PRIVILEGES;

#proxysql 6032端口执行
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';

load mysql variables to runtime;
save mysql variables to disk;



insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(2,1,'^SELECT',20,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(1,1,'^SELECT.*FOR UPDATE$',10,1);
load mysql query rules to runtime;
save mysql query rules to disk;

#查询写入情况
select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;


set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;


#修改proxysql端口（修改后需要重启proxysql）：
set mysql-interfaces="0.0.0.0:6033";
save mysql variables to disk;

#修改mysql版本
update global_variables set variable_value="8.0.29" where variable_name='mysql-server_version';

#修改后，需要加载到RUNTIME，并保存到disk
load mysql variables to runtime;
save mysql variables to disk;
# 查询
select * from global_variables where variable_name='mysql-server_version';