一瘪撇、數(shù)據(jù)mariadb集群搭建分為兩個(gè)內(nèi)容
1、負(fù)載均衡搭建
2倔既、mariadb galera cluster
二、負(fù)載均衡搭建
負(fù)載均衡集群是 load balance 集群的簡(jiǎn)寫(xiě)佩谣，翻譯成中文就是負(fù)載均衡集群歼捏。常用的負(fù)載均衡開(kāi)源軟件有nginx笨篷、lvs、haproxy练俐，商業(yè)的硬件負(fù)載均衡設(shè)備F5冕臭、Netscale。這里主要是學(xué)習(xí) LVS 并對(duì)其進(jìn)行了詳細(xì)的總結(jié)記錄悯蝉。本次采用lvs的DR模式
2.1 安裝keepalive和lvs安裝包
yum install -y ##### keepalived 安裝keepalive
yum install -y ipvsadm ##### 安裝裝lvs
2.2、編輯keepalive的配置托慨，keepalive與網(wǎng)絡(luò)的vrrp原理一樣
vi /etc/keepalive/keepalived.conf
vrrp_instance VI_1 {
state MASTER
! nopreempt
interface chkconfig keepalived on
garp_master_delay 10
virtual_router_id 50
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass zabbix
}
virtual_ipaddress {
192.168.1.157
}
}
virtual_server 192.168.1.157 3306 {
delay_loop 6
lb_algo wrr
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server192.168.1.1543306{weight1TCP_CHECK {connect_port3306connect_timeout3nb_get_retry3delay_before_retry3}}real_server192.168.1.1553306{weight1TCP_CHECK {connect_port3306connect_timeout3nb_get_retry3delay_before_retry3}}
real_server 192.168.1 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
2.3.開(kāi)啟keepalive鼻由、ipvsam服務(wù)
systemctl start keepalived
systemctl start ipvsam
2.4、驗(yàn)證
ipvsam 查看負(fù)載情況
三厚棵、mariadb 集群
3.1 數(shù)據(jù)庫(kù)安裝?
分別在三臺(tái)主機(jī)安裝mariadb的數(shù)據(jù)蕉世，本次安裝10.3.14版本的數(shù)據(jù)，較為穩(wěn)定的版本
vi /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB-10.3.14
baseurl=http://yum.mariadb.org/10.3.14/centos7-amd64
alternative: baseurl=http://archive.mariadb.org/mariadb-10.3.14/yum/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@localhost /]# yum install mariadb-server -y
Loaded plugins: langpacks, product-id, subscription-manager
Complete!
[root@localhost /]# rpm -qa | grep mariadb
mariadb-server-5.5.50-1.el7_2.x86_64
mariadb-libs-5.5.50-1.el7_2.x86_64
mariadb-5.5.50-1.el7_2.x86_64
mariadb-devel-5.5.50-1.el7_2.x86_64
啟動(dòng)mariadb服務(wù)程序并添加到開(kāi)機(jī)啟動(dòng)項(xiàng)中：
[root@localhost /]# systemctl start mariadb
[root@localhost /]# systemctl enable mariadb
[root@localhost /]# netstat -anpt | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 22418/mysqld
[root@localhost /]# ps -ef | grep mariadb
mysql 22418 22259 0 00:39 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 22459 1092 0 00:40 pts/0 00:00:00 grep --color=auto mariadb
[root@localhost /]# ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'
為了保證數(shù)據(jù)庫(kù)的安全性婆硬，一定要進(jìn)行初始化工作：
第1步：設(shè)定root用戶密碼狠轻。
第2步：刪除匿名帳號(hào)。
第3步：禁止root用戶從遠(yuǎn)程登陸向楼。
第4步：刪除test數(shù)據(jù)庫(kù)并取消對(duì)其的訪問(wèn)權(quán)限查吊。
第5步：刷新授權(quán)表，讓初始化后的設(shè)定立即生效蜜自。
初始化數(shù)據(jù)庫(kù)服務(wù)程序：
[root@localhost /]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 當(dāng)前數(shù)據(jù)庫(kù)密碼為空菩貌，直接敲擊回車(chē)。
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: 輸入要為root用戶設(shè)置的數(shù)據(jù)庫(kù)密碼重荠。
Re-enter new password: 重復(fù)再輸入一次密碼箭阶。
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y（刪除匿名帳號(hào)）
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y(禁止root用戶從遠(yuǎn)程登陸)
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y(刪除test數(shù)據(jù)庫(kù)并取消對(duì)其的訪問(wèn)權(quán)限)
Dropping test database...
... Success!
Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y(刷新授權(quán)表，讓初始化后的設(shè)定立即生效)
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
使用root用戶登陸到數(shù)據(jù)庫(kù)中：
[root@localhost /]# mysql -u root -p
Enter password: 此處輸入root用戶在數(shù)據(jù)庫(kù)中的密碼戈鲁。
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
查看當(dāng)前已有的數(shù)據(jù)庫(kù)：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)
3.2 配置集群
vi /etc/my.conf
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.1.192.168.1.155,192.168.1.156,"
Galera Synchronization Configuration
wsrep_sst_method=rsync
Galera Node Configuration
wsrep_node_address="192.168.1.156" #####其他節(jié)點(diǎn)需要修改
wsrep_node_name="Node1" #####其他節(jié)點(diǎn)需要修改
第1個(gè)節(jié)點(diǎn)先啟動(dòng) 使用galera_new_cluster
其他節(jié)點(diǎn)使用 systemctl start mariadb
3.4 安裝zabbix-server-mysql
為了將zabbix的數(shù)據(jù)文件導(dǎo)入mysql中
mysql -uroot -p
password
mysql> create database zabbix character set utf8 collate utf8_bin;
mysql> grant all privileges on zabbix.* to zabbix@localhost identified by 'password';
mysql> quit;
導(dǎo)入初始架構(gòu)和數(shù)據(jù)仇参，系統(tǒng)將提示您輸入新創(chuàng)建的密碼。
zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p zabbix
五婆殿、配置vip
因?yàn)樨?fù)載均衡lvs的DR原理诈乒，每臺(tái)數(shù)據(jù)庫(kù)都必須配置vip地址，并不對(duì)外進(jìn)行廣播
#!/bin/bash
#description:start realserver
vip1=192.168.1.157
case $1 in
start)
echo "Start Realserver"
/sbin/ifconfig lo:0 $vip1 broadcast $vip1 netmask 255.255.255.255 up
echo"1">/proc/sys/net/ipv4/conf/lo/arp_ignore? ? echo"2">/proc/sys/net/ipv4/conf/lo/arp_announce? ? echo"1">/proc/sys/net/ipv4/conf/all/arp_ignore? ? echo"2">/proc/sys/net/ipv4/conf/all/arp_announce
;;
stop)
echo "Stop Realserver"
/sbin/ifconfig lo:0 down
echo "0" > /proc/sys/net/ipv4/conf/lo/arp_ignore
echo "0" > /proc/sys/net/ipv4/conf/lo/arp_announce
echo "0" > /proc/sys/net/ipv4/conf/all/arp_ignore
echo "0" > /proc/sys/net/ipv4/conf/all/arp_announce
;;
*)
echo "Usage: $0 (start | stop)"
exit 1
esac
最后運(yùn)行shell腳本
常見(jiàn)故障
搭建galera cluster的時(shí)候設(shè)置sst為xtrabackup婆芦，啟動(dòng)node1報(bào)錯(cuò)怕磨。
[ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .
解決方式：
grastate.dat file of the node you intend to use as the first node.
需要把該文件刪除 重新啟動(dòng)即可。
