mysql5.7.20(主主集群)+keepalived1.3.6高可用

基本设置
sed -i 's/SELINUX=enforcing/SELinux=disabled/g' /etc/selinux/config
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --in-interface eno16777984 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload

 

MYSQL5.7编译安装

 

tar xvf  mysql-5.7.20.tar.gz
tar xvf boost_1_59_0.tar.gz

 

yum -y install gcc gcc-c++ ncurses ncurses-devel bison libgcrypt perl make cmake

mkdir /usr/local/boost
cp -r boost_1_59_0 /usr/local/boost/

groupadd mysql
useradd -r -g mysql -s /bin/false -M mysql

mkdir -p /usr/local/mysql /usr/local/mysql/{data,logs,pids}
chown -R mysql:mysql /usr/local/mysql/

cd  /opt/mysql-5.7.20
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost
make
make install

echo "export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH" >> /etc/profile
source /etc/profile

初始化数据库
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
###     --initialize-insecure 表示不生成密码

开机启动项设置
cp /opt/mysql-5.7.20/support-files/mysql.server /etc/init.d/mysqld
chmod a=x /etc/init.d/mysql

chkconfig --add mysqld
chkconfig mysqld on

vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
symbolic-links=0

[mysqld_safe]
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/pids/mysqld.pid

[client]
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock

[mysql]
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock
!includedir /etc/my.cnf.d

touch /usr/local/mysql/logs/mysqld.log
touch /usr/local/mysql/pids/mysqld.pid
chown mysql.mysql -R /usr/local/mysql/

注:报错处理{/var/lock/subsys/mysql[FAILED]} 解决方法：rm -rf /var/lock/subsys/mysql

连接mysql
mysql -u root
注：此行如果报错 ，请查看 /etc/my.cnf是否配置正确

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
mysql> select host,user,authentication_string from user;
#5.7以后password 已经没有了，password改成了authentication_string

+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          |                                           |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

设置密码：
update user set authentication_string =password("overseas") where user='root';
flush privileges;
mysql> exit

mysql -uroot -p
Enter password:

 

配置mysql支持远程连接
mysql> use mysql
mysql> update user set Host='%' where user='root';
mysql> flush privileges;

配置主主
master1:172.17.2.122
master2:172.17.2.123

master配置
/etc/my.cnf
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
auto-increment-increment = 2
auto-increment-offset = 1

slave配置

log-bin = mysql-bin
binlog_format = mixed
server-id = 2
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
auto-increment-increment = 2
auto-increment-offset = 2

注：master1和master2只有server-id不同和 auto-increment-offset不同。
mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：auto_increment_offset和auto_increment_increment。
auto-increment-increment表示自增长字段每次递增的量，其默认值是1。它的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2。
auto-increment-offset是用来设定数据库中自动增长的起点(即初始值)，因为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突，
注：可以在my.cnf文件中添加“binlog_do_db=数据库名”配置项（可以添加多个）来指定要同步的数据库


在master1主机上创建授权账户，允许在master1(172.17.2.123)主机上连接
mysql> grant replication slave on *.* to 'syn'@'172.17.2.123' identified by 'syn123';
flush privileges;
查看binlog状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在master2主机上创建授权账户，允许在master1(172.17.2.122)主机上连接
mysql>change master to master_host='172.17.2.122',master_user='syn',master_password='syn123',master_log_file='mysql-bin.000002 ',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.2.122
                  Master_User: syn
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
            
****************************************************
注:取消命令为
mysql> stop slave;
mysql> change master to master_host='';

****************************************************

查看master2的当前binlog状态信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      601 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在master2主机上创建授权账户，允许在master1(172.17.2.122)主机上连接

grant replication slave on *.* to 'syn'@'172.17.2.122' identified by 'syn123';

在master1上将master2设为自已的主服务器并开启slave功能。
mysql>change master to master_host='172.17.2.123',master_user='syn',master_password='syn123',master_log_file='mysql-bin.000002 ',master_log_pos=601;

mysql>flush privileges;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.2.122
                  Master_User: syn
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            

--------------------------------------------------------------------------

备注：Slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功。
Slave_IO_Running=NO
需要先
stop slave;
reset slave;
重新启动start slave;
-------------------------------------------------------------------------

验证主主同步
在master1上创建要同步的数据库名test_db，并在测试db中创建一张测试表如tab
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
mysql> create table tab(id int, name varchar(30));
mysql> use test_db;
Database changed
在master2中查看是否同步，并且在测试db测试tab中插入数据
mysql> insert into tab(id,name) values (1,'test');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看master1主机是否同步了master2上的数据变化
mysql> select * from tab;
+------+------+
| id   | name |
+------+------+
|    1 | test |
+------+------+
1 row in set (0.00 sec)



现在任何一台MySQL上更新数据都会同步到另一台MySQL，MySQL同步完成。

keepalived 安装配置

安装依赖包
yum install -y gcc openssl-devel

解压keepalived软件包
[root@wanda-portal-test1 opt]# tar xvf keepalived-1.3.6

编译安装
[root@wanda-portal-test1 opt]# cd keepalived-1.3.6
[root@wanda-portal-test1 keepalived-1.3.6]# ./configure
[root@wanda-portal-test1 keepalived-1.3.6]# make & make install
[root@wanda-portal-test1 keepalived-1.3.6]# cp keepalived/etc/init.d/keepalived /etc/init.d/
[root@wanda-portal-test1 keepalived-1.3.6]# cp keepalived/etc/sysconfig/keepalived  /etc/sysconfig/
[root@wanda-portal-test1 keepalived-1.3.6]# cp /usr/local/sbin/keepalived /usr/sbin/
[root@wanda-portal-test1 keepalived-1.3.6]# mkdir /etc/keepalived/
[root@wanda-portal-test1 keepalived-1.3.6]# cp -a /usr/local/etc/keepalived/keepalived.conf  /etc/keepalived/

配置keepalived
MASTER 配置
global_defs {
   notification_email {
     yfshen@sinoi.com.cn
   }
   notification_email_from yfshen@sinoi.com.cn
   smtp_server mail.sinoi.com.cn
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER         
    interface eno16777984
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        172.17.2.120/16
        
    }
}

[root@wanda-portal-test1 keepalived]# scp keepalived.conf root@172.17.2.123:/etc/keepalived/

BACKUP 配置
global_defs {
   notification_email {
     yfshen@sinoi.com.cn
   }
   notification_email_from yfshen@sinoi.com.cn
   smtp_server mail.sinoi.com.cn
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP         
    interface eno16777984
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        172.17.2.120/16
        
    }
}

[root@wanda-portal-test1 keepalived]# systemctl enable  keepalived
[root@wanda-portal-test1 keepalived]# systemctl start  keepalived