如何在Centos7安装Mariadb Galera Cluster三主集群

安装前准备:
禁用SELinux
/usr/sbin/setenforce 0
sestatus
安装vim wget lrzsz
yum install vim wget lrzsz -y
yum remove libperl
rm -rf /usr/lib64/perl5/CORE/libperl.so
更新操作系统
yum upgrade -y
reboot
设置hosts
vi /etc/hosts
192.168.1.36 mariadb1
192.168.1.48 mariadb2
192.168.1.51 mariadb3
一. 删除旧的mariadb 和  galera
1、yum方式安装的MySQL
yum remove mariadb galera mysql mysql-server mysql-libs compat-mysql51
查看是否还有mysql软件:
rpm -qa|grep mysql
如果存在的话,继续删除即可,删除方式:yum remove + 【名字】。
2、rpm方式安装的mysql
rpm -qa | grep -i mysql
MySQL-server-5.6.17-1.el6.i686
MySQL-client-5.6.17-1.el6.i686
卸载mysql
rpm -e MySQL-server-5.6.17-1.el6.i686
rpm -e MySQL-client-5.6.17-1.el6.i686
查看mysql服务
systemctl list-unit-files
3删除分散mysql文件夹
用whereis mysql 或者 find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/usr/lib64/mysql
/usr/share/mysql
清空相关mysql的所有目录以及文件
cd  /etc/selinux/targeted/active/modules/100/mysql ;rm -rf ./*;
cd  /var/lib/mysql;rm -rf ./*;
cd  /usr/lib64/mysql;rm -rf ./*;
cd  /usr/share/mysql;rm -rf ./*;
cd //etc/my.cnf.d/;rm -rf ./*;
rm -f /etc/my.cnf
二.添加MariaDB YUM repository
vi  /etc/yum.repos.d/MariaDB.repo
增加内容:
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.0/centos7-amd64
gpgkey=http://yum.mariadb.org/RPM-GPG-KEY-MariaDB
enabled=1
gpgcheck=1
三.用yum安装Percona XtraBackup(2.4.8版本 )
1.安装Percona repository
2.测试repository
yum list | grep percona
3.安装percona-xtrabackup-24
yum install percona-xtrabackup-24
四.用YUM命令安装MariaDB Galera Cluster
yum install  MariaDB-server  MariaDB-client  socat  -y
yum clean all
yum makecache
五.安装后启动节点配置相关账号
sudo systemctl start mariadb
或 操作系统没有systemctl命令时用下面这句命令执行
sudo /etc/init.d/mysql start
本地登录:
mysql -uroot -p
给root用户授权远程登录:
GRANT  all privileges on *.* to ‘root’@’%’ IDENTIFIED BY ‘123456’;
flush privileges;
新增galerauser 用户,并设置密码为1qaz2wsx :
insert into mysql.user(Host,User,Password) values(“localhost”,”galerauser”,password(“123456”));
insert into mysql.user(Host,User,Password) values(“%”,”galerauser”,password(“123456”));
GRANT  all privileges on *.* to ‘galerauser’@’%’ IDENTIFIED BY ‘123456’;
GRANT  all privileges on *.* to ‘galerauser’@’localhost’ IDENTIFIED BY ‘123456’;
flush privileges;
六.配置数据库账号
所有节点都执行下面这句命令
/usr/bin/mysql_secure_installation
七.配置集群配置文件
第一个节点(配置为启动节点)
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://’    #第一个启动节点配置
wsrep_cluster_name=’mariadb_cluster’
wsrep_node_address=’192.168.1.36′   #本机IP地址
wsrep_node_name=’mariadb1′    #集群节点名称
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
lower_case_table_names = 1
第二个节点:
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://192.168.1.36,192.168.1.51′
wsrep_cluster_name=’mariadb_cluster’
wsrep_node_address=’192.168.1.48′
wsrep_node_name=’mariadb2′
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
lower_case_table_names = 1
第三个节点:
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=’gcomm://192.168.1.36,192.168.1.48′
wsrep_cluster_name=’mariadb_cluster’
wsrep_node_address=’192.168.1.51′
wsrep_node_name=’mariadb_node3′
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
lower_case_table_names = 1
注意:如果要启动Mariadb Galera Cluster ,首先选择node1节点为初始节点,将node1作为第一个启动节点,并且设置gcomm为空,否则无法启动。
wsrep_cluster_address=’gcomm://’
八 .启动一个新的集群
根据不同版在主节点执行(第一次初始化时使用,只执行一次,以后不需要执行这一句):
/usr/sbin/mysqld –wsrep-new-cluster –user=mysql &
警告:对一个已存在的集群添加新的节点时,不要使用引导的方式(bootstrap)启动这个节点,不能在一个集群中使用两个引导节点。
其它节点执行:systemctl start mariadb
查看日志: tail -f /var/log/messages
本次启动顺序为:mariadb1–>>mariadb3–>mariadb2 关的顺序为: mariadb2–>>mariadb3–>mariadb1
重启节点命令为:systemctl restart mariadb
查看节点状态: systemctl start mariadb
第一个节点
第二个节点
第三个节点
九执行命令:
mysqld –wsrep_cluster_address=gcomm://192.168.1.36  # 也可以用DNS名称
注意:ip为第一个启动的那个主节点
十.关闭所有节点后重启集群
——–未完待续

如何在Centos7.4中修改yum安装的mariadb主-主-主集群datadir路径

1.查询当前数据库存储路径
# mysql -uroot -p
MariaDB [(none)]> show variables like ‘datadir%’;
#其中datadir项就是现存储的路径
#退出数据库登录状态
MariaDB [(none)]> exit;
2.停止数据库
# systemctl stop mariadb
3.新建mariadb新存储的路径文件夹
# mkdir -p /home/mysql/mysqldata
# mkdir -p /home/mysql/mysqllog/binlog
4.进入home目录,给新的路径文件夹mysql用户权限
# cd /home
# chown -R mysql:mysql mysql
# chmod 755 -R mysql
5.修改mysql服务启动脚本中的datadir路径
默认为 datadir=/var/lib/mysql 注释掉,更换为 datadir=/home/mysql/mysqldata
如果还有相同的/var/lib/mysql路径,同样修改为/home/mysql/mysqldata
# vi /etc/init.d/mysql
6.如果还是报错,可以偿试下,修改以下两个文件的参数ProtectSystem和ProtectHome为false
# vi /etc/systemd/system/multi-user.target.wants/mariadb.service
# vi /usr/lib/systemd/system/find
# vi /usr/share/mysql/systemd/mariadb.service
把ProtectSystem=full改成ProtectSystem=false
把ProtectHome=true改成ProtectHome=false
再执行下面这一句
# systemctl daemon-reload
7.修改/etc/my.cnf.d/server.cnf把datadir,socket和log-bin参数路径
# vi /etc/my.cnf.d/server.cnf
[mysqld]
port        = 3306
datadir = /home/mysql/mysqldata
socket =   /var/lib/mysql/mysql.sock
log-bin = /home/mysql/mysqllog/binlog/mysql-bin
#完整server.cnf配置文件如下所示:
[mysqld]
port        = 3306
datadir =  /home/mysql/mysqldata
socket =   /var/lib/mysql/mysql.sock
log-bin = /home/mysql/mysqllog/binlog/mysql-bin
collation-server = utf8_general_ci
init-connect=’SET NAMES utf8′
character-set-server = utf8
open_files_limit = 65535
log_bin_trust_function_creators = 1
skip-external-locking
back_log=750
skip-name-resolve
event_scheduler=1
lower_case_table_names = 1
innodb-defragment=1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
performance_schema=0
net_read_timeout=30
net_write_timeout=60
key_buffer_size = 384M
max_allowed_packet = 10M
table_open_cache = 512
sort_buffer_size = 6M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
expire_logs_days=3
myisam_sort_buffer_size = 16M
thread_cache_size = 32
join_buffer_size = 4M
bulk_insert_buffer_size = 16M
delay_key_write=ON
delayed_insert_limit=4000
delayed_insert_timeout=60
delayed_queue_size=4000
tmp_table_size=128M
max_heap_table_size=128M
max_connections=3000
max_connect_errors=30
interactive_timeout=600
wait_timeout=60
aria_pagecache_buffer_size=32M
#slow_query_log
long_query_time = 0.1
log_slow_verbosity=query_plan
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
innodb_file_per_table=1
innodb_fast_shutdown=0
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 132000M
innodb_log_file_size = 100M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = on
innodb_flush_method=O_DIRECT
transaction-isolation=READ-COMMITTED
innodb_thread_concurrency=48
innodb_io_capacity=800
innodb_purge_threads=1
innodb_open_files=65535
innodb_read_io_threads = 8
innodb_write_io_threads = 12
innodb_stats_on_metadata = 0
thread_handling=pool-of-threads
thread_pool_oversubscribe=30
thread_pool_size=32
thread_pool_idle_timeout=60
thread_pool_max_threads=1000
[mysqldump]
quick
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 12M
read_buffer = 6M
write_buffer = 2M
[isamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
8.安全模式启动数据库后生成索引文件
执行到第6步,如果直接运行systemctl start mariadb命令,会报错:提示找不到mysql-bin.index文件;那是 修改了log-bin参数路径,由于权限问题,
执行下面命令:即用安全模式启动数据库后就会在/home/mysql/mysqllog/binlog/mysql-bin下生成索引文件
# /usr/bin/mysqld_safe  –user=mysql &
9.停掉mysql进程后
# pkill -u mysql
# ps -ef | grep mysql
# kill -9 mysql进程
9.复制/var/lib/mysql/* 到 /home/mysql/mysqldata下
# cd  /home/mysql/mysqldata
# rm -rf  *
# cp -rf /var/lib/mysql/* /home/mysql/mysqldata
# cd /home
# chown -R mysql:mysql mysql
# chmod -R 750 mysql
注意:原/var/lib/mysql数据文件夹还是保留,因为要用到socket=/var/lib/mysql/mysql.sock
10.重启mariadb服务
# systemctl  start  mariadb
11.打开另外一个节点,进入mysql当前控制台
# mysql -uroot -p123456
MariaDB [(none)]> SET GLOBAL wsrep_provider_options=’pc.bootstrap=YES’;
MariaDB [(none)]> show status like ‘wsrep%’;
12.再次重启mariadb服务
# systemctl  restart  mariadb
13.打开另外一个窗口,用来打印启动信息
# tail -f /var/log/messages
14.确认datadir是否改变
# mysql -uroot -p
MariaDB [(none)]> show variables like ‘datadir%’;
#其中datadir项就是现存储的路径
15.退出数据库登录状态
MariaDB [(none)]> exit;