环境：两台centos 7.4 最小化安装 mysql5.7.25 IP 规划： mysql01 192.168.189.77 mysql02 192.168.189.78 VIP地址：192.168.189.156 安装mysql 5.7.25 两台主机上都要安装，本次实验使用的是脚本安装 #!/bin/bash clear date MYVER=`cat /etc/redhat-release |awk '{print $4}'| awk -F [.] '{print $1}'` expr 1 + $MYVER &>/dev/null if [ $? -ne 0 ];then echo "请在 7 版本的系统里面运行该脚本....." exit fi if [ $UID -ne 0 ]; then echo "请用 root 用户运行该脚本...." exit fi echo '网络检测中.......................' ping -c 1 114.114.114.114 > /dev/null 2>&1 if [ $? -eq 0 ];then echo '网络正常，程序将继续运行。' else echo '正在进行第二次网络检测，请稍候.........' curl www.baidu.com >/dev/null 2>&1 if [ $? -ne 0 ]; then echo '检测到网络连接有异常，请检查您的网络设置.....' exit else echo '' echo '网络正常，程序将继续运行.' sleep 1 fi fi yum install wget -y >/tmp/a.txt echo "开始安装 Mysql 5.7.25........................................" sleep 2 PSMYSQL0=`ps -ef |grep mysql |grep -v grep | wc -l` if [ $PSMYSQL0 -ge 1 ]; then echo '已有mysql 进程运行，不需要再安装。' echo '' ps -ef |grep mysql | grep -v grep echo '' sleep 1 else if [ ! -f "/opt/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz" ];then echo "mysql5.7.25 二进制tar.gz 文件不存在，我们将在网上下载........" sleep 2 cd /opt wget https://dev.mysql.com//Downloads/MySQL-5.7/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz if [ $? -ne 0 ]; then echo '' echo "下载有问题，请检查......" exit fi fi echo "开始安装 Mysql 依赖包.........." yum -y install cmake bison git ncurses-devel libaio gcc gcc-c++ lrzsz wget vim >/tmp/mysql_yl.log yum remove mariadb* -y >/tmp/mariadb.log cd /opt echo "正在解压 mysql 5.7.25 .........." tar -zxf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /usr/local cd /usr/local mv mysql-5.7.25-linux-glibc2.12-x86_64 mysql-5.7.25 groupadd mysql useradd -r -g mysql -s /bin/false mysql echo "export PATH=$PATH:/usr/local/mysql-5.7.25/bin" >> /etc/profile source /etc/profile mkdir -p /usr/local/mysql-5.7.25/{data,log,etc,run} touch /usr/local/mysql-5.7.25/log/mysql_error.log chown -R mysql:mysql /usr/local/mysql-5.7.25 chmod -R 777 /usr/local/mysql-5.7.25/{data,log,etc,run} cat >/etc/my.cnf << EOF [mysqld] port = 3306 socket = /usr/local/mysql-5.7.25/run/mysql.sock pid_file = /usr/local/mysql-5.7.25/run/mysql.pid basedir = /usr/local/mysql-5.7.25 datadir = /usr/local/mysql-5.7.25/data default_storage_engine = InnoDB max_allowed_packet = 128M max_connections = 2048 max_connections = 2048 open_files_limit = 65535 skip-name-resolve lower_case_table_names=1 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' innodb_buffer_pool_size = 128M innodb_log_file_size = 128M innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 0 key_buffer_size = 16M log-error = /usr/local/mysql-5.7.25/log/mysql_error.log log-bin = /usr/local/mysql-5.7.25/log/mysql_bin.log slow_query_log = 1 slow_query_log_file = /usr/local/mysql-5.7.25/log/mysql_slow_query.log long_query_time = 3 tmp_table_size = 16M max_heap_table_size = 16M query_cache_size = 256M server-id=1 enforce_gtid_consistency = on gtid_mode = on [client] port = 3306 socket = /usr/local/mysql-5.7.25/run/mysql.sock EOF mysqld --initialize --user=mysql --basedir=/usr/local/mysql-5.7.25/ --datadir=/usr/local/mysql-5.7.25/data/ if [ $? -ne 0 ]; then echo "Mysql 初始化有问题，请检查....." exit fi mysql_ssl_rsa_setup --basedir=/usr/local/mysql-5.7.25 --datadir=/usr/local/mysql-5.7.25/data/ cat >/usr/lib/systemd/system/mysqld.service << EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql Type=forking PIDFile=/usr/local/mysql-5.7.25/run/mysql.pid TimeoutSec=0 PermissionsStartOnly=true ExecStart=/usr/local/mysql-5.7.25/bin/mysqld --daemonize --pid-file=/usr/local/mysql-5.7.25/run/mysql.pid EnvironmentFile=-/etc/sysconfig/mysql LimitNOFILE = 65535 Restart=on-failure RestartPreventExitStatus=1 PrivateTmp=false EOF systemctl daemon-reload systemctl enable mysqld.service systemctl start mysqld.service if [ $? -ne 0 ]; then echo "Mysql 启动有问题，请检查....." exit fi PASSW=`grep 'temporary password' /usr/local/mysql-5.7.25/log/mysql_error.log |awk '{print $11}'` cd /usr/local/mysql-5.7.25/bin/ ./mysqladmin -uroot -p"$PASSW" password "windows@123" mysql -uroot -pwindows@123 -e "create database abc charset=utf8;" if [ $? -ne 0 ]; then echo "Mysql 密码修改未成功，请检查....." exit else netstat -luntp exit fi fi 注意：脚本执行安装成后，还需要 source source /etc/profile ++++++++++++++++++++++++++++++++++++++++++++ 一、修改my.cnf------ mysql01上操作 cp /etc/my.cnf /etc/my.cnf.bak在这后面添加如下内容： report_host = 192.168.189.77 master_info_repository = TABLE relay_log_info_repository = TABLE sync_master_info = 1 slave_parallel_workers = 2 binlog_checksum = CRC32 master_verify_checksum = 1 slave_sql_verify_checksum = 1 binlog_rows_query_log_events = 1 slave_skip_errors = all auto_increment_increment=2 auto_increment_offset=1 replicate_ignore_db = mysql replicate_ignore_db = information_schema replicate_ignore_db = performance_schema replicate_ignore_db = sys 重启mysqld mysql02服务器的my.cnf
在 gtid_mode = on 后面添加如下内容： report_host = 192.168.189.78 master_info_repository = TABLE relay_log_info_repository = TABLE sync_master_info = 1 slave_parallel_workers = 2 binlog_checksum = CRC32 master_verify_checksum = 1 slave_sql_verify_checksum = 1 binlog_rows_query_log_events = 1 slave_skip_errors = all auto_increment_increment=2 auto_increment_offset=2 replicate_ignore_db = mysql replicate_ignore_db = information_schema replicate_ignore_db = performance_schema replicate_ignore_db = sys 重启mysqld 服务 二、配置双主 在mysql01 mysql02都添加相同的同步数据的帐户 grant replication slave on *.* to 'tb'@'192.168.189.%' identified by 'abc123'; flush privileges; 先在mysql02服务器上运行： 登录数据mysql stop slave; change master to master_host='192.168.189.77',master_user='tb',master_password='abc123',master_auto_position=1; start slave; show slave status\G
两个IO线程为Yes 表示，主从关系正常。 再在mysql01服务器上操作： 登录mysql stop slave; change master to master_host='192.168.189.78',master_user='tb',master_password='abc123',master_auto_position=1; start slave; show slave status\G
+++++++++++++++++++++++++++ 三、安装keepalived 2.0.8 在mysql01 mysql02上都要安装 安装依赖包： yum install -y gcc gcc-c++ openssl openssl-devel libnl libnl-devel libnfnetlink-devel cd /opt wget http://www.keepalived.org/software/keepalived-2.0.8.tar.gz tar zxvf keepalived-2.0.8.tar.gz cd keepalived-2.0.8/ ./configure --prefix=/usr/local/keepalived make && make install mkdir -p /etc/keepalived cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ 启动keepalived systemctl start keepalived systemctl enable keepalived modprobe ip_vs （两台服务器上都运行） 四、配置keepalived mysql01 192.168.189.77 cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak vim /etc/keepalived/keepalived.conf !Configuration File for keepalived global_defs { notification_email { admin@localhost.com } notification_email_from admin@localhost.com smtp_server 127.0.0.1 smtp_connect_timeout 30 } vrrp_script chk_mysql { script "/etc/keepalived/check_mysql.sh" interval 3 } vrrp_instance VI_1 { state MASTER interface ens33 #lvs_sync_daemon_inteface ens33 virtual_router_id 11 priority 100 advert_int 5 nopreempt authentication { auth_type PASS auth_pass 2222 } track_script { chk_mysql } virtual_ipaddress { 192.168.189.156 } } mysql02 192.168.189.78 直接复制mysql01上面的keepalived.conf 进行修改 配置文件：state 这里也设置为 BACKUP，其他所有都相同。 脚本文件内容： vim /etc/keepalived/check_mysql.sh #!/bin/bash Mysql=`ps -ef |grep mysqld |grep -v grep|wc -l` if [ $Mysql -eq 0 ];then pkill keepalived fi 保存退出。chmod +x /etc/keepalived/check_mysql.sh 这个脚本文件，要放在两台服务器上的/etc/keepalived 下面。 两台服务器都重启keepalived 在mysql01 上查看VIP 是否有生成
++++++++++++++ 测试VIP飘移： 在mysql01 上面停止mysqld 在mysql02 上查看vip 是否能飘移过去
+++++++++++++ 这时，把mysql01 上面的mysqld keepalived 启动起来，看VIP是否能飘移过来，应该是不能，因为我们的keepalived 配置文件里面，优先级都一样，并且都设置了 不抢占，所以，当mysql01上面的 mysqld keepalived 正常时，并不会抢占VIP，这样避免VIP来回切换，保证了数据的稳定。
+++++++++++++ 在mysql02上，停止mysqld 服务查看VIP 是否正常飘移到 mysql01 上面。
查看mysql01 上面
