mysql安装
1. 下载mysql安装包
官方下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads
2. 将安装包上传到指定位置
#mkdir /opt/mysql
#cd /opt/mysql
#ls
3. 解压安装包
4. 卸载SUSE自带的mariadb
用rpm -e卸载即可
5. 安装mysql-server服务
只需安装如下4个软件包，使用rpm -ivh 安装即可（需要按顺序安装，因为后面的服务依赖于前面的服务）
顺序：
rpm -ivh mysql-community-common-5.7.23-1.sles12.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.23-1.sles12.x86_64.rpm（报错）
yast2
安装部分依赖
rpm -ivh mysql-community-client-5.7.23-1.sles12.x86_64.rpm
rpm -ivh mysql-community-server-5.7.23-1.sles12.x86_64.rpm
即完成安装。
6. 初始化数据库
#mysqld --initialize
注：初始化后会在/var/log/mysql/mysqld.log中生成随机密码
7. 修改mysql数据库目录的属主属组，启动mysql数据库
#chown mysql:mysql /var/lib/mysql -R
启动：
#systemctl start mysqld.service
查看mysql状态
#systemctl status mysqld.service
8. 登录mysql并修改root用户的密码（这是系统强制要求的，否则不能启动mysql）
(1) 登录的初始密码在/var/log/mysql/mysqld.log中，先查看初始密码：
#grep 'temporary password' /var/log/mysql/mysqld.log
如
注1：初始密码一般会很复杂，复制比较稳妥；且一般会有特殊符号，会导致密码不对登录失败，两种方法可以解决：
方法1：
根据提示填写密码。
方法2：
添加单引号。
注2：查看初始密码，SUSE系统在/var/log/mysql/mysqld.log中
但是RHEL, Oracle Linux, CentOS, and Fedora 平台，在/var/log/mysqld.log中。
(2) 进入mysql以后，修改密码
9. 修改访问权限
修改访问权限可以让任何主机通过用户root 和密码123456连接到mysql数据库。
#mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
三、mysql主主同步
10. 修改两台服务器配置文件
修改主服务器配置文件，增加如下
#vi /etc/my.cnf
|
[mysqld] server-id=1 log-bin=mysql-bin log-slave-updates binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema replicate-wild-ignore-table = mysql.% replicate-wild-ignore-table = information_schema.% replicate-wild-ignore-table = performance_schema.% expire_logs_days=5 |
修改从服务器配置文件，增加如下
#vi /etc/my.cnf
|
[mysqld] log-bin=mysql-bin log-slave-updates binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema replicate-wild-ignore-table = mysql.% replicate-wild-ignore-table = information_schema.% replicate-wild-ignore-table = performance_schema.% expire_logs_days=5 |
11. 重启mysql服务（两台都需要重启）
#systemctl restart mysqld
12. 配置主主
(1) [FH-UMP1]mysql 为主库
FH-UMP1
|
#mysql -p（123456） mysql> show master status; |
FH-UMP2
|
#mysql -p(123456) mysql> show slave status; mysql> stop slave; mysql> change master to master_host='192.168.198.149',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154; mysql> start slave; mysql> show slave statusG |
详细信息如下：
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.183
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: FH-UMP2-relay-bin.000002
Relay_Log_Pos: 320
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: mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 529
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
Master_UUID: 3b9bc7b3-d6a0-11e8-9ee1-000c29a816df
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
(2) [FH-UMP2]mysql 为主库
类似的，可以配置FH-UMP2上的mysql为主库
FH-UMP2
|
#mysql -p（123456） mysql> show master status; |
FH-UMP1
|
#mysql -p(123456) mysql> show slave status; mysql> stop slave; mysql> change master to master_host='192.168.1.184',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154; mysql> start slave; mysql> show slave statusG |
13. 设置开机自启动（两台都需要）
#vim /etc/init.d/boot.local添加
systemctl start mysql.service
四、Keepalived实现高可用
14. Keepalive 安装
#tar –zxvf keepalived-1.3.4.tar.gz
#cd keepalive-1.3.4/
#./configure --prefix=/usr/local/keepalived
#make
#make install
UMP1,UMP2两台机器同样都要安装
15. 修改keepalive配置文件
(1) 修改UMP1的 keepalived配置文件
#mkdir /etc/keepalived/
#cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
#vim /etc/keepalived/keepalived.conf
|
! Configuration File for keepalived global_defs { router_id HA_MySQL_1 #标识，双主相同 } vrrp_script check_mysql #vrrp脚本命名 { script "/etc/keepalived/bin/mysql.sh" #要执行的脚本 interval 10 #脚本指定间隔，秒 weight -50 #优先级（如果脚本执行结果为0，并且weight配置的值大于0，则优先级相应的增加，如果脚本执行结果非0，并且weight配置的值小于0，则优先级相应的减少） fall 3 #尝试三次都成功才成功 rise 3 #尝试三次都失败才失败 } vrrp_instance VI_1 { state MASTER #注意，主从两端都配置成了MASTER 和BACKUP interface br0 virtual_router_id 51 #分组，主备相同 priority 100 #优先级，这个高一点则先把它作为master advert_int 1 nopreempt #不主动抢占资源，设置非抢占模式 authentication { auth_type PASS auth_pass 1111 } track_script { check_mysql } virtual_ipaddress { 192.168.1.180/24 #vip } } |
(2) 修改UMP2 keepalived配置文件
#mkdir /etc/keepalived/
#cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
#vim /etc/keepalived/keepalived.conf
|
! Configuration File for keepalived global_defs { router_id HA_MySQL_2 } vrrp_script check_mysql #vrrp脚本命名 { script "/etc/keepalived/bin/mysql.sh" #要执行的脚本 interval 10 #脚本指定间隔，秒 weight -50 #优先级（如果脚本执行结果为0，并且weight配置的值大于0，则优先级相应的增加，如果脚本执行结果非0，并且weight配置的值小于0，则优先级相应的减少） fall 3 #尝试三次都成功才成功 rise 3 #尝试三次都失败才失败 } vrrp_instance VI_1 { state BACKUP interface br0 virtual_router_id 51 priority 90 #优先级，这个低一点 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #虚拟IP (VIP) 192.168.1.180/24 } } |
16. mysql宕掉后，keepalived自动停止的脚本
vim /etc/keepalived/bin/mysql.sh
脚本内容如下：
|
#!/bin/bash MYSQL=/usr/bin/mysql #mysql的位置 MYSQL_HOST=localhost MYSQL_USER=root #mysql用户 MYSQL_PASSWORD=123456 #mysql密码 CHECK_TIME=3 #mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0 MYSQL_OK=1 function check_mysql_health (){ $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1 if [ $? = 0 ] ;then MYSQL_OK=1 else MYSQL_OK=0 fi return $MYSQL_OK } while [ $CHECK_TIME -ne 0 ] do let "CHECK_TIME -= 1" check_mysql_health if [ $MYSQL_OK = 1 ] ; then CHECK_TIME=0 exit 0 fi if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ] then pkill keepalived exit 1 fi sleep 1 done |
17. 启动keepalive服务
1）检查相关的内核参数
开启IP转发功能
#sysctl -a | grep net.ipv4.ip_forward
net.ipv4.ip_forward = 1
开启允许绑定非本机的IP
#sysctl -a | grep
net.ipv4.ip_nonlocal_bind = 1
注：如未开启，即：
net.ipv4.ip_forward = 0
net.ipv4.ip_nonlocal_bind = 0
开启的方法为：
#sysctl -w net.ipv4.ip_forward=1
#sysctl -w net.ipv4.ip_nonlocal_bind=1
如使用LVS的DR或者TUN模式结合Keepalived需要在后端服务器上，特别设置两个arp相关的参数。
#sysctl -w net.ipv4.conf.lo.arp_ignore=1
#sysctl -w net.ipv4.conf.lo.arp_announce=2
#sysctl -w net.ipv4.conf.all.arp_ignore=1
#sysctl -w net.ipv4.conf.all.arp_announce=2
2）启动keepalived
#/usr/local/keepalive/sbin/keepalived -D
日志在/var/log/messages，如有报错可以查看排查
3）测试keepalived的虚拟IP
连接虚拟ip ，先查看下虚拟ip有没有，在设置的priority值高，也就是主机的那台机器上，这里是UMP1机器，调用如下命令ip a
停止APP1机器的mysql会发现APP2上的br0网卡会出现虚拟IP 192.168.1.180
ps:需要重新启动mysql以及keepalived才可以切换，如下：
4）配置keepalived开机自启动
#vim /etc/init.d/boot.local添加
/usr/local/keepalived/sbin/keepalived
问题：ip_vs与内核升级后的问题