一, 数据 mariadb 集群搭建分为两个内容
1, 负载均衡搭建
2,mariadb galera cluster
二, 负载均衡搭建
负载均衡集群是 load balance 集群的简写, 翻译成中文就是负载均衡集群. 常用的负载均衡开源软件有 nginx,lvs,haproxy, 商业的硬件负载均衡设备 F5,Netscale. 这里主要是学习 LVS 并对其进行了详细的总结记录. 本次采用 lvs 的 DR 模式
2.1 安装 keepalive 和 lvs 安装包
- yum install -y ##### keepalived 安装 keepalive
- yum install -y ipvsadm ##### 安装装 lvs
2.2, 编辑 keepalive 的配置, keepalive 与网络的 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. 开启 keepalive,ipvsam 服务
- systemctl start keepalived
- systemctl start ipvsam
2.4, 验证
ipvsam 查看负载情况
三, mariadb 集群
3.1 数据库安装
分别在三台主机安装 mariadb 的数据, 本次安装 10.3.14 版本的数据, 较为稳定的版本
- 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
启动 mariadb 服务程序并添加到开机启动项中:
- [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'
为了保证数据库的安全性, 一定要进行初始化工作:
第 1 步: 设定 root 用户密码.
第 2 步: 删除匿名帐号.
第 3 步: 禁止 root 用户从远程登陆.
第 4 步: 删除 test 数据库并取消对其的访问权限.
第 5 步: 刷新授权表, 让初始化后的设定立即生效.
初始化数据库服务程序:
- [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): 当前数据库密码为空, 直接敲击回车.
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 用户设置的数据库密码.
Re-enter new password: 重复再输入一次密码.
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(删除匿名帐号)
- ... 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 用户从远程登陆)
- ... 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 数据库并取消对其的访问权限)
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(刷新授权表, 让初始化后的设定立即生效)
- ... 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 用户登陆到数据库中:
[root@localhost /]# MySQL -u root -p
Enter password: 此处输入 root 用户在数据库中的密码.
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)]>
查看当前已有的数据库:
- 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" ##### 其他节点需要修改
- wsrep_node_name="Node1" ##### 其他节点需要修改
第 1 个节点先启动 使用 galera_new_cluster
其他节点使用 systemctl start mariadb
3.4 安装 zabbix-server-MySQL
为了将 zabbix 的数据文件导入 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;
导入初始架构和数据, 系统将提示您输入新创建的密码.
zcat /usr/share/doc/zabbix-server-MySQL*/create.sql.gz | MySQL -uzabbix -p zabbix
五, 配置 vip
因为负载均衡 lvs 的 DR 原理, 每台数据库都必须配置 vip 地址, 并不对外进行广播
- #!/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
最后运行 shell 脚本
常见故障
搭建 galera cluster 的时候设置 sst 为 xtrabackup, 启动 node1 报错.
[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.
需要把该文件删除 重新启动即可.
来源: http://www.jianshu.com/p/027038c63045