MYSQL数据库集群实战
MYSQL 优化从以下几个方向:高可用、主从复制、读写分离、拆分库、拆分表来进行优化。
MYSQL主从复制的目的是实现数据库冗余备份,将Master数据库数据定时同步至Slave库中,一旦Master 数据库宕机,可以将WEB应用数据库配置快速切换至Slave数据库,确保WEB应用较高的可用率。
MYSQL主从复制集群至少需要2台机器,其中一台为数据库master服务器,另外一台为slave服务器,MYSQL主从数据同步是一个异步复制的过程,要实现复制首先需要在master上开启bin-log日志功能,bin-log日志用于记录在Master库中执行的增、删、修改、更新操作的sql语句,整个过程需要开启3个线程,分别是Master开启IO线程,Slave开启IO线程和SQL线程
MYSQL数据库复制能够解决的问题
1、数据分布
2、负载平衡
3、数据备份、保证数据安全
4、高可用性和容错性
5、实现读写分离,缓解数据库压力
MYSQL复制的模式
1、主从复制:主库授权从库远程连接,读取binlog日志并更新到本地数据库的过程;主库写数据后,从库会自动同步过来(从库跟着主库变)
2、住住复制主从相互授权连接,读取对方斌binlog日志并更新到本地数据库的过程;只要对方数据改变,自己就跟着改变;
MYSQL主从复制实战
环境:
主服务器:centos 7.8 MariaDB 5.5.65-IP:172.28.1.27
从服务器:centos 7.8 MariaDB 5.5.65- IP:172.28.1.28
vip:172.28.1.29
tomcat8
主服务器opst数据库
mysql默认配置文件,如不特殊指定默认为/etc/my.cnf
配置:
一、主服务器
1.1、创建一个复制用户,具有replication slave 权限。
MariaDB [(none)]> grant replication slave on *.* to "master"@'172.28.1.28' identified by "Aa123456";
1.2、编辑my.cnf文件
vi /etc/my.cnf
添加
server-id=1log-bin=mysql-bin 并开启log-bin二进制日志文件(Mysql需要有/var/lib/mysql/目录的读写权限【可通过chown -R mysql:mysql /var/lib/mysql命令进行更改】)
binlog-do-db=mysql1 #需要备份的数据库名,如果备份多个数据库,重复设置这个选项 即可
binlog-ignore-db=mysql2 #不需要备份的数据库名,如果备份多个数据库,重复设置这 个选项即可
log-slave-updates=1#这个参数一定要加上,否则不会给更新的记录些到二进制文件 里
slave-skip-errors=1 #是跳过错误,继续执行复制操作(可选)
1.3、重启mysql数据库
systemctl restart mariadb
1.4、设置读锁
flush tables with read lock;
1.5、得到binlog日志文件名和偏移量(此处记住File名称和Position值,后面slave服务器配置时需要用到)
show master status;+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 713 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
1.6、备份要同步的数据库
mysqldump -uroot -p ops > ops.sql
1.7、解锁
unlock tables;
1.8、关闭主服务器mysql(关停Master服务器,将Master中的数据拷贝到B服务器中,使得Master和slave中的数据同步,并且确保在全部设置操作结束前,禁止在Master和slave服务器中进行写操作,使得两数据库中的数据一定要相同!假如是你完全新安装mysql主从服务器,这个一步就不需要)
systemctl stop mariadb
添加新slave服务器
假如master已经运行很久了,想对新安装的slave进行数据同步,甚至它没有master的数据。
此时,有几种方法可以使slave从另一个服务开始,例如,从master拷贝数据,从另一个slave克隆,从最近的备份开始一个slave。Slave与master同步时,需要三样东西:
(1)master的某个时刻的数据快照;
(2)master当前的日志文件、以及生成快照时的字节偏移。这两个值可以叫做日志文件坐标(log file coordinate),因为它们确定了一个二进制日志的位置,你可以用SHOW MASTER STATUS命令找到日志文件的坐标;
(3)master的二进制日志文件。
可以通过以下几中方法来克隆一个slave:
(1) 冷拷贝(cold copy)
停止master,将master的文件拷贝到slave;然后重启master。缺点很明显。
(2) 热拷贝(warm copy)
如果你仅使用MyISAM表,你可以使用mysqlhotcopy拷贝,即使服务器正在运行。
(3) 使用mysqldump
使用mysqldump来得到一个数据快照可分为以下几步:
<1>锁表:如果你还没有锁表,你应该对表加锁,防止其它连接修改数据库,否则,你得到的数据可以是不一致的。如下:
mysql> FLUSH TABLES WITH READ LOCK;
<2>在另一个连接用mysqldump创建一个你想进行复制的数据库的转储:
shell> mysqldump --all-databases --lock-all-tables >dbdump.db
<3>对表释放锁。
mysql> UNLOCK TABLES;
将步骤1.6中的ops.sql文件copy到slave
scp ops.sql root@172.28.1.28:/root/
二、从服务器(172.28.1.27)
将master(172.28.1.27)备份的数据库数据恢复到slave从服务器(172.28.1.28)
mysql -u root -p < /root/ops.sql
2.1、编辑my.cnf文件
添加
server-id=2
2.2、重启从数据库
systemctl restart mariadb
2.3、对从数据库进行相应设置
此处要注意logfile的名称和position的值,其余host、user和password为主数据库设置的账号和密码
>stop slave;>change master to-> master_host='172.28.1.27',-> master_user='master',-> master_password='Aa123456',-> master_log_file='mysql-bin.000001',-> master_log_pos=713;
> start slave;
Query OK, 0 rows affected (0.00 sec)
> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.28.1.27
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 217137
Relay_Log_File: mariadb-relay-bin.000004
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 217137
Relay_Log_Space: 218001
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
在这里主要是看:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
如果出现Slave_IO_Running: No或Slave_SQL_Running: NO,需要重做2.3、对从数据库进行相应设置
三、 负载服务器配置
这里只叙述如何安装配置keepalived,至于java以及tomcat的安装及配置,这里不赘述。
3.1 安装keepalived
源码安装tar -zxvf keepalived-1.2.19.tar.gz
cd keepalived-1.2.19./configure --prefix=/usr/local/keepalived --disable-fwmark
#(如果直接输入./configure有可能报错:configure:error: No SO_MARK declaration inheaders)
[keepalived-1.2.19] make && make install
3.2 配置keepalived服务
[keepalived-1.2.19] cp /usr/local/keepalived/sbin/keepalived /usr/sbin/[keepalived-1.2.19] cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/[keepalived-1.2.19] cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/[keepalived-1.2.19] mkdir /etc/keepalived
[keepalived-1.2.19] cp /usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/[keepalived-1.2.19] chkconfig --add keepalived
[keepalived-1.2.19] chkconfig keepalived on
可以连网的可直接通过YUM 安装 yum install keepalived -y
重启\启动\关闭\查看状态keepalived
service keepalived restart
service keepalived start
service keepalived stop
service keepalived status
3.3 配置keepalived.conf文件
[~]mv /etc/keepalived/keepalived.conf/etc/keepalived/keepalived.conf.bak
[~]vi /etc/keepalived//keepalived.conf
3.4主备机的keepalived.conf文件大致相同。
主机配置:
global_defs {
# 路由器标识,一般不用改,也可以写成每个主机自己的主机名
router_id NodeA
}
# 定义用于实例执行的脚本内容,比如可以在线降低优先级,用于强制切换
vrrp_script chk_http_port {
script"/opt/tomcat.sh"interval5weight2}
#一个vrrp_instance就是定义一个虚拟路由器的,实例名称
vrrp_instance VI_1 {
# 定义初始状态,可以是MASTER或者BACKUP
state MASTER
# 工作接口,通告选举使用哪个接口进行
interface eth0
# 虚拟路由ID,如果是一组虚拟路由就定义一个ID,如果是多组就要定义多个,而且这个虚拟
# ID还是虚拟MAC最后一段地址的信息,取值范围0-255
virtual_router_id52
# 监控本机上的哪个网卡,网卡一旦故障则需要把VIP转移出去
track_interface {
eth0
}
# 如果你上面定义了MASTER,这里的优先级就需要定义的比其他的高priority150# 通告频率,单位为秒
advert_int1# 通信认证机制,这里是明文认证还有一种是加密认证
authentication {
auth_type PASS
auth_pass2222}
track_script {
chk_http_port
}
# 设置虚拟VIP地址,一般就设置一个,在LVS中这个就是为LVS主机设置VIP
virtual_ipaddress {172.28.1.29}
}
备机配置:
global_defs {
router_id NodeB
}
vrrp_script chk_http_port {
script"/opt/tomcat.sh"interval5weight2}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id52priority100advert_int1authentication {
auth_type PASS
auth_pass2222}
track_script {
chk_http_port
}
virtual_ipaddress {172.28.1.29}
}
3.5配置/opt/tomcat.sh
#!/bin/bash
#description: check tomcat service anddecide whether stop the keepalived or not
CATALINA_HOME=/usr/local/apache-tomcat-8.5.35JAVA_HOME=/usr/local/java/jdk1.8.0_191
export CATALINA_HOME
export JAVA_HOMEps ax --width=1000 | grep "org.apache.catalina.startup.Bootstrap start" | grep -v "grep" | awk '{printf $1 " "}' | wc | awk '{print $2}' >tomcat_process_count.txt
read line
start_tomcat=$CATALINA_HOME/bin/startup.sh
if [ ${line} -lt 1]then
echo -n "===Starting tomcat===:"${start_tomcat}
# :sudoservice tomcat startecho "===tomcat start ok.==="
sleep 3# check the tomcat status.ps ax --width=1000 | grep "org.apache.catalina.startup.Bootstrap start" | grep -v "grep" | awk '{printf $1 " "}' | wc |awk '{print $2}' >tomcat_process_count.txt
read line2
fi
rmtomcat_process_count.txt
#shell end.
4. 日志查看
4.1 正常启动
输入tail -f /var/log/messages查看启动keepalived日志:
(MASTER:172.28.1.27)
Sep 29 15:49:17 shr Keepalived_vrrp[5539]:VRRP_Instance(VI_1) Transition to MASTER STATE
Sep29 15:49:17 shr Keepalived_vrrp[5539]:VRRP_Instance(VI_1) Received lower prio advert, forcing new election
Sep29 15:49:18 shr Keepalived_vrrp[5539]:VRRP_Instance(VI_1) Entering MASTER STATE
Sep29 15:49:18 shr Keepalived_vrrp[5539]:VRRP_Instance(VI_1) setting protocol VIPs.
Sep29 15:49:18 shr Keepalived_vrrp[5539]:VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.28.1.29Sep29 15:49:18 shr Keepalived_vrrp[5539]:Netlink reflector reports IP 172.28.1.29added
Sep29 15:49:18 shrKeepalived_healthcheckers[5538]: Netlink reflector reports IP 172.28.1.29added
Sep29 15:49:23 shr Keepalived_vrrp[5539]:VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.28.1.29
(BACKUP:172.28.1.28)
Sep 29 15:46:25 server1 Keepalived_vrrp[18221]:Configuration is using : 36651Bytes
Sep29 15:46:25 server1Keepalived_vrrp[18221]: Using LinkWatch kernel netlink reflector...
Sep29 15:46:25 server1Keepalived_vrrp[18221]: VRRP_Instance(VI_1) Entering BACKUP STATE
Sep29 15:46:25 server1 Keepalived_vrrp[18221]:VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
4.2 主备切换
l 当在主机(MASTER:172.28.1.27)中输入service keepalived stop,此时就会进行主备切换,主机切换成备机。
主机(MASTER:172.28.1.27)输出如下
Oct 14 13:25:09 shr Keepalived_vrrp[26683]:VRRP_Instance(VI_1) sending 0 priority
Oct 14 13:25:09 shr Keepalived_vrrp[26683]:VRRP_Instance(VI_1) removing protocol VIPs.
Oct 14 13:25:09 shrKeepalived_healthcheckers[26682]: Netlink reflector reports IP 172.28.1.29removed
Oct 14 13:25:09 shr Keepalived[26681]:Stopping Keepalived v1.2.19 (09/21,2020)
备机(BACKUP:172.28.1.28)输出如下
Oct 14 13:19:58 server1Keepalived_vrrp[30890]: VRRP_Instance(VI_1) Transition to MASTER STATE
Oct14 13:19:59 server1Keepalived_vrrp[30890]: VRRP_Instance(VI_1) Entering MASTER STATE
Oct14 13:19:59 server1Keepalived_vrrp[30890]: VRRP_Instance(VI_1) setting protocol VIPs.
Oct14 13:19:59 server1Keepalived_vrrp[30890]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for172.28.1.29Oct14 13:19:59 server1 Keepalived_healthcheckers[30889]:Netlink reflector reports IP 172.28.1.29added
Oct14 13:19:59 server1Keepalived_vrrp[30890]: Netlink reflector reports IP 172.28.1.29added
Oct14 13:20:04 server1Keepalived_vrrp[30890]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for172.28.1.29
l 当在主机(MASTER:172.28.1.27)中输入service keepalived start,此时就会切换成主机。备机(BACKUP:172.28.1.28)输出如下信息:
Oct 14 13:25:11 server1Keepalived_vrrp[30890]: VRRP_Instance(VI_1) Received higher prio advert
Oct14 13:25:11 server1Keepalived_vrrp[30890]: VRRP_Instance(VI_1) Entering BACKUP STATE
Oct14 13:25:11 server1Keepalived_vrrp[30890]: VRRP_Instance(VI_1) removing protocol VIPs.
Oct14 13:25:11 server1Keepalived_healthcheckers[30889]: Netlink reflector reports IP 172.28.1.29removed
Oct14 13:25:11 server1Keepalived_vrrp[30890]: Netlink reflector reports IP 172.28.1.29 removed
5. 查看虚拟ip
可以通过ip add show命令查看添加的虚拟ip:
[root@ opt]# ip addr show eth02: eth0: mtu 1500 qdisc mq state UP group default qlen 1000link/ether 00:15:5d:00:51:05brd ff:ff:ff:ff:ff:ff
inet172.28.1.27/27 brd 172.28.1.31scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet172.28.1.29/32scope global eth0
valid_lft forever preferred_lft forever