mysql + keepalived环境搭建 1.环境准备 系统：Centenos7.4 mysql版本 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz keepalived版本 keepalived-2.0.13.tar.gz node1 ：192.168.5.235 node2 ：192.168.5.236 vip： 192.168.5.58 （最好是同一ip段，不然肯定会出现网络问题） 主机用户：具有sudo权限的test 2.mysql主主搭建 由于分配的虚拟机只有一个系统需要安装各种系统软件，磁盘也需要自己挂在 2.1磁盘分区 [test@host-192-168-5-235 ~]$sudo fdisk -l Disk /dev/vda: 21.5 GB, 21474836480 bytes, 41943040 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk label type: dos Disk identifier: 0x000adb11 Device Boot Start End Blocks Id System /dev/vda1 * 2048 1026047 512000 83 Linux /dev/vda2 1026048 9414655 4194304 82 Linux swap / Solaris /dev/vda3 9414656 41943039 16264192 83 Linux Disk /dev/vdb: 536.9 GB, 536870912000 bytes, 1048576000 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes [test@host-192-168-5-236 ~]$ sudo fdisk /dev/vdb Welcome to fdisk (util-linux 2.23.2). Changes will remain in memory only, until you decide to write them. Be careful before using the write command. Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0xf2a1312e. Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): p Partition number (1-4, default 1): First sector (2048-1048575999, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-1048575999, default 1048575999): Using default value 1048575999 Partition 1 of type Linux and of size 500 GiB is set Command (m for help): w The partition table has been altered! Calling ioctl() to re-read partition table. Syncing disks. 分区格式化 [test@host-192-168-5-235 /]$ sudo mkfs -t ext3 /dev/vdb1 mke2fs 1.42.9 (28-Dec-2013) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 32768000 inodes, 131071744 blocks 6553587 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=4294967296 4000 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968, 102400000 Allocating group tables: done Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done 创建目录并挂载 [test@host-192-168-5-235 /]$ sudo mkdir /data [test@host-192-168-5-235 ~]$ sudo mount /dev/vdb1 /data 开机自动挂载按照里边的格式增加 sudo vi /etc/fstab 2.2 mysql配置node1和node2相同的操作 查看 rpm -qa | grep -i mysql rpm -qa | grep mariadb 删除（查出来的一个个全删了） sudo rpm -e mariadb-libs-5.5.56-2.el7.x86_64 –nodeps 解压mysql安装包 [test@host-192-168-5-236 ~]$ tar -xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 将目录属主和组更改为test为了方便并赋予777权限 sudo chown test:test -R /data/ chmod 777 /data/ cd /data/ mkdir mysql cd /data/mysql/ 创建数据目录，日志目录，pid目录 mkdir data logs run 将mysql软件放在/usr/local/下 cd /usr/local/ sudo mkdir mysql sudo chown test:test ./mysql/ cd /data mv mysql-5.7.26-linux-glibc2.12-x86_64/* /usr/local/mysql/ 删除空目录 rm mysql-5.7.26-linux-glibc2.12-x86_64/ 编辑mysql配置文件node1 sudo vi /etc/my.cnf [mysqld] port=9060 datadir=/data/mysql/data socket=/data/mysql/data/mysql.sock server-id=1 log-bin=mysql-bin symbolic-links=0 [mysqld_safe] log-error=/data/mysql/logs/mysql.log pid-file=/data/mysql/run/mysql.pid [client] default-character-set=utf8 socket=/data/mysql/data/mysql.sock [mysql] default-character-set=utf8 socket=/data/mysql/data/mysql.sock vi .bash_profile PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/mysql/bin export PATH source .bash_profile 初始化 mysqld --initialize --user=test --datadir=/data/mysql/data 安全启动: mysqld_safe --user=test & 用初始化生成的root密码登录并更改密码 mysql -uroot -p set password=password("123456"); flush privileges; 编辑mysql配置文件node2 sudo vi /etc/my.cnf [mysqld] port=9060 datadir=/data/mysql/data socket=/data/mysql/data/mysql.sock server-id=2 log-bin=mysql-bin symbolic-links=0 [mysqld_safe] log-error=/data/mysql/logs/mysql.log pid-file=/data/mysql/run/mysql.pid [client] default-character-set=utf8 socket=/data/mysql/data/mysql.sock [mysql] default-character-set=utf8 socket=/data/mysql/data/mysql.sock vi .bash_profile PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/mysql/bin export PATH source .bash_profile 初始化 mysqld --initialize --user=test --datadir=/data/mysql/data 安全启动: mysqld_safe --user=test & 用初始化生成的root密码登录并更改密码 mysql -uroot -p set password=password("123456"); flush privileges; 配置主从 主节点（192.168.5.235） 创建同步用户 CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY 'sync@123456'; GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%'; flush privileges; show master status; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 997 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 备节点（192.168.5.236） CHANGE MASTER TO MASTER_HOST='192.168.5.235', MASTER_USER='sync', MASTER_PASSWORD='sync@2019#Zy',MASTER_PORT=9060,MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=997; start slave; # 停止 stop slave # 重置 reset slave mysql> show slave status\G 反过来配置一遍 原备节点（192.168.5.236） 创建同步用户 CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY 'sync@123456'; GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%'; flush privileges; show master status; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 997 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 原主节点（192.168.5.235） CHANGE MASTER TO MASTER_HOST='192.168.5.236', MASTER_USER='sync', MASTER_PASSWORD='sync@2019#Zy',MASTER_PORT=9060,MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=997; start slave; # 停止 stop slave # 重置 reset slave mysql> show slave status\G 看到两个YES，代表主主成功 Slave_IO_Running: Yes Slave_SQL_Running: Yes 赋予root用户远程访问（为了远程访问root用户） GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by 'otn@2019#zy'; flush privileges; 测试： 创建数据库 create database test; 创建普通用户 CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; grant all privileges on test.* to 'test'@'%'; grant all privileges on mysql.* to 'test'@'%'; flush privileges; 查看数据库 show databases; 查看用户 select user,host from mysql.user; 创建表 create table testa( Id varchar(100)); 两边都能看到testa表 show tables; 插入语句 insert into testa values('1231'); insert into testa values('4567'); insert into testa values('5464'); 另一个数据库都能看到 select * from testa; delete from testa where Id='1231'; 另一个数据库数据显也被删除 至此，mysql主主已经完全配置成功。 3.Keepalived安装 安装相关的系统环境（必须是root用户或者sudo用户） yum -y install gcc openssl-devel openssl ipvsadm yum -y install libnl libnl-devel （支持ipv6） [test@host-192-168-5-235 ~]$ tar -xvf keepalived-2.0.13.tar.gz cd keepalived-2.0.13 sudo ./configure --prefix=/usr/local/keepalived --安装到/usr/local/keepalived sudo make && sudo make install cd /etc mkdir keepalived sudo cp -r /data/keepalived-2.0.13/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf sudo cp -r /data/keepalived-2.0.13/keepalived/etc/init.d /etc/rc.d/init.d/keepalived sudo cp /data/keepalived-2.0.13/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived keepalived.conf 配置内容：＃清空默认内容，直接采用下面配置 vi /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { #全局配置标识，表明这个区域{}是全局配置 notification_email { xxx@xxx #表示发送通知邮件时邮件源地址是谁 } notification_email_from xxx@xxx #表示keepalived在发生诸如切换操作时需要发送email通知，以及email发送给哪些邮件地址，邮件地址可以多个，每行一个notification_email_from xxx@xxx smtp_server 127.0.0.1 #表示发送email时使用的smtp服务器地址，这里可以用本地的sendmail来实现 smtp_connect_timeout 30 #连接smtp连接超时时间 router_id host-192-168-5-235 #机器标识 vrrp_skip_check_adv_addr vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script chk_mysql_port { #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等 script "/root/chk_mysql.sh" #这里通过脚本监测 interval 2 #脚本执行间隔，每2s检测一次 weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5 fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间） rise 1 #检测1次成功就算成功。但不修改优先级 } vrrp_instance VI_1 { state MASTER interface eth0 #主机网卡 mcast_src_ip 192.168.5.235 #主机ip virtual_router_id 35 #路由器标识，MASTER和BACKUP必须是一致的 priority 101 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.5.58 } track_script { chk_mysql_port } } bakcup主机上的keepalived配置 vi /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { notification_email { xxx@xxxx } notification_email_from xxx@xxxx smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id host-192-168-5-236 vrrp_skip_check_adv_addr vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script chk_mysql_port { script "/root/chk_mysql.sh" interval 2 weight -5 fall 2 rise 1 } vrrp_instance VI_1 { state BACKUP interface eth0 mcast_src_ip 192.168.5.236 virtual_router_id 35 priority 99 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.5.58 } track_script { chk_mysql_port } } [test@host-192-168-5-227 data]$ cd /root [test@host-192-168-5-227 root]$ vi chk_mysql.sh #!/bin/bash counter=$(netstat -na|grep "LISTEN"|grep "9060"|wc -l) if [ "${counter}" -eq 0 ]; then service keepalived stop fi 启动 shell> sudo systemctl enable keepalived.service #设置开机自动启动 shell> sudo service keepalived start #启动服务 shell> sudo service keepalived stop #停止服务 shell> sudo service keepalived restart #重启服务 验证登录 mysql -h192.168.5.58 -P9060 -uroot -p123456 可以登录 mysql -h192.168.5.235 -P9060 -uroot -p123456 mysql -h192.168.5.236 -P9060 -uroot -p123456
MySQL启动与关闭
1、查看mysql服务的两种方式
[root@localhost bin]ps -ef|grep mysql
[root@localhost bin]netstat -nlp
2、启动服务的两种方式
命令行方式
[root@localhost bin]cd /usr/bin
[root@localhost bin]./mysqld_safe &
服务方式
[root@localhost ~]service mysql start
如果服务在启动状态，直接重启服务用以下命令：
[root@localhost ~]service mysql restart
3、关闭服务的两种方式
命令行方式：
[ root@localhost ~]mysqladmin -u root shutdown
服务方式：
[root@localhost ~]service mysql stop 遇到的问题 此时发现问题使用vip在主节点可以但是再备节点和其他同段的主机不能正常访问， 在备节点（或其他节点）ping vip 只能ping通9次 初步怀疑是网络问题，但是网络侧那边说限制放开了 我测试ping通9次，vip对应hwaddress没有获取我手动添加就能ping通，理论上应该是自动获取的， 手动添加没有意义，如果vip漂移了那还是不能访问了 最后折腾了两周在自己虚拟机同样的配置访问都没有问题， 这次找到虚拟化的同事咨询这个问题，他们从底层放开网络限制好了
