本文共 14553 字，大约阅读时间需要 48 分钟。
环境规划:
node1: 192.168.1.250
node2: 192.168.1.251
vip: 192.168.1.201
数据库: mysql-5.6.23
mysql dba技术群 378190849
武汉-linux运维群 236415619
1.各节点的网络配置
node1节点:
[root@node1 ~]# hostname
node1[root@node1 ~]# ip addr1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 10:78:d2:c9:50:28 brd ff:ff:ff:ff:ff:ff inet 192.168.1.250/24 brd 192.168.1.255 scope global eth0 inet6 fe80::1278:d2ff:fec9:5028/64 scope link valid_lft forever preferred_lft forever[root@node1 ~]#node2节点:
[root@node2 ~]# hostname
node2[root@node2 ~]# ip addr1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000 link/ether d0:27:88:7d:83:9b brd ff:ff:ff:ff:ff:ff inet 192.168.1.251/24 brd 192.168.1.255 scope global eth0 inet6 fe80::d227:88ff:fe7d:839b/64 scope link valid_lft forever preferred_lft forever[root@node2 ~]#2.下载安装mysql数据库
node1节点和node2节点一样安装(下面红色部分不一样)
[root@node1 ~]# wget
[root@node1 ~]# tar xvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@node1 ~]# cd /usr/local/
[root@node1 local]# mv mysql-5.6.23-linux-glibc2.5-x86_64 mysql-5.6.23 [root@node1 local]# chown -R root:mysql mysql-5.6.23/[root@node1 local]# chown -R mysql:mysql mysql-5.6.23/data/[root@node1 local]# cd mysql-5.6.23/[root@node1 mysql-5.6.23]# ./scripts/mysql_install_db --user=mysql --group=mysql --database=/usr/local/mysql-5.6.23/data --basedir=/usr/local/mysql-5.6.23 [root@node1 mysql-5.6.23]# cp -a my.cnf /etc/[root@node1 mysql-5.6.23]# cp -a support-files/mysql.server /etc/init.d/mysqld[root@node1 mysql-5.6.23]# vim /etc/my.cnfbasedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23port = 3306server_id = 10 --将另一台主修改为20socket = /tmp/mysql.socklog-bin=mysql-binlog-bin-index=mysql-bin-indexreplicate-do-db=tongreplicate-ignore-db=mysqlauto_increment_offset=1 --将另一台主修改为2auto_increment_increment=2relay-log=relay-logrelay-log-index=relay-log-indelog_slave_updatessync-binlog=1[root@node1 mysql-5.6.23]# /etc/init.d/mysqld restart
ERROR! MySQL server PID file could not be found!Starting MySQL. SUCCESS! [root@node1 mysql-5.6.23]#3.配置主主复制
node1节点:
[root@node1 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqladmin -u root password 'system' --修改初始密码为system
Warning: Using a password on the command line interface can be insecure.[root@node1 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.23-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database tong;Query OK, 1 row affected (0.05 sec)mysql> grant replication slave,replication client on *.* to repl_user@'192.168.1.251' identified by 'system!#%246'; --创建复制用户Query OK, 0 rows affected (0.05 sec)mysql> flush privileges;Query OK, 0 rows affected (0.05 sec)mysql> show master status; --查看node1节点的二进制位置+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 690 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql>node2节点:
[root@node2 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysqladmin -u root password 'system'
Warning: Using a password on the command line interface can be insecure.[root@node2 mysql-5.6.23]# /usr/local/mysql-5.6.23/bin/mysql -u root -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.23-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database tong;Query OK, 1 row affected (0.03 sec)mysql> grant replication slave,replication client on *.* to repl_user@'192.168.1.250' identified by 'system!#%246';Query OK, 0 rows affected (0.06 sec)mysql> flush privileges;Query OK, 0 rows affected (0.03 sec)mysql> change master to master_host='192.168.1.250',master_port=3306,master_user='repl_user',master_password='system!#%246',master_log_file='mysql-bin.000001',master_log_pos=690; --同步node1的数据Query OK, 0 rows affected, 2 warnings (0.39 sec)mysql> show master status; --查看node2节点的二进制日志+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 690 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.03 sec)mysql>node1节点:
mysql> change master to master_host='192.168.1.251',master_port=3306,master_user='repl_user',master_password='system!#%246',master_log_file='mysql-bin.000001',master_log_pos=690; --同步node2节点的数据
Query OK, 0 rows affected, 2 warnings (0.49 sec)mysql> start slave;Query OK, 0 rows affected (0.08 sec)mysql>4.测试主主同步是否正常(在两个节点各写一行数据,在两个节点查看数据)
node1节点:
mysql> \u tong
Database changedmysql> create table t (a int);Query OK, 0 rows affected (0.33 sec)mysql> insert into t values(1);Query OK, 1 row affected (0.08 sec)mysql>node2节点:
mysql> \u tong
Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from t;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> insert into t values(2);Query OK, 1 row affected (0.12 sec)mysql> select * from t;+------+| a |+------+| 1 || 2 |+------+2 rows in set (0.00 sec)mysql>node1节点:
mysql> select * from t;
+------+| a |+------+| 1 || 2 |+------+2 rows in set (0.00 sec)mysql>5.下载安装keepalived软件(node1和node2是一样)
node1节点:
[root@node1 ~]# wget
[root@node1 ~]# tar xvf keepalived-1.2.15.tar.gz
[root@node1 ~]# cd keepalived-1.2.15[root@node1 keepalived-1.2.15]# ./configure --prefix=/usr/local/keepalived-1.2.15 --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64
[root@node1 keepalived-1.2.15]# make && make install
[root@node1 keepalived-1.2.15]# echo $?
0[root@node1 keepalived-1.2.15]# cd /usr/local/keepalived-1.2.15/[root@node1 keepalived-1.2.15]# ll
total 16drwxr-xr-x. 2 root root 4096 Apr 30 11:46 bindrwxr-xr-x. 5 root root 4096 Apr 30 11:46 etcdrwxr-xr-x. 2 root root 4096 Apr 30 11:46 sbindrwxr-xr-x. 3 root root 4096 Apr 30 11:46 share[root@node1 keepalived-1.2.15]# mkdir /etc/keepalived --创建文件夹不能少,否则出错
[root@node1 keepalived-1.2.15]# cp -a etc/rc.d/init.d/keepalived /etc/init.d/
[root@node1 keepalived-1.2.15]# cp -a etc/keepalived/keepalived.conf /etc/keepalived
[root@node1 keepalived-1.2.15]# cp -a etc/sysconfig/keepalived /etc/sysconfig/
[root@node1 keepalived-1.2.15]# cd /etc/keepalived/
[root@node1 keepalived]# vim keepalived.conf! Configuration File for keepalived
global_defs { notification_email { z597011036@qq.com --邮件报警 } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 127.0.0.1 --邮件服务器 smtp_connect_timeout 30 --连接超时30秒报警 router_id mysql-ha }vrrp_instance VI_1 { state MASTER --主节点是MASTER,备节点是BACKUP interface eth0 virtual_router_id 50 --id值在两台服务器必须一至 priority 100 --优先级,node1是100,node2是90 advert_int 1 nopreempt --不抢占资源 authentication { auth_type PASS --两个节点认证的权限 auth_pass 1111 } virtual_ipaddress { --VIP地址 192.168.1.201 }}virtual_server 192.168.1.201 3306 { --VIP地址和端口 delay_loop 6 lb_algo wrr --权纵 lb_kind DR --DR模式 nat_mask 255.255.255.0 persistence_timeout 50 protocol TCP --协议 real_server 192.168.1.250 3306 { --node1的IP地址和端口 weight 1 notify_down /usr/local/mysql-5.6.23/bin/mysql.sh --检测mysql宕机后执行的脚本 TCP_CHECK { connect_timeout 3 --连接超时 nb_get_retry 3 --重试3秒 connect_port 3306 --连接端口}
}}[root@node1 keepalived]# cat /usr/local/mysql-5.6.23/bin/mysql.sh --脚本内容
#!/bin/bashpkill keepalived/usr/bin/keepalived -D[root@node1 keepalived]#node2节点:
安装keepalived软件是一样
[root@node2 keepalived]# vim keepalived.conf
! Configuration File for keepalivedglobal_defs { notification_email { z597011036@qq.com } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id mysql-ha}vrrp_instance VI_1 { state BACKUP --与node1不一样 interface eth0 virtual_router_id 50 priority 99 --与node1不一样 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.201 }}virtual_server 192.168.1.201 3306 { delay_loop 6 lb_algo wrr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 50 protocol TCP real_server 192.168.1.251 3306 { --node2的IP地址和端口 weight 1 notify_down /usr/local/mysql-5.6.23/bin/mysql.sh TCP_CHECK { connect_timeout 3 nb_get_retry 3 connect_port 3306 } }}
[root@node1 keepalived]# cat /usr/local/mysql-5.6.23/bin/mysql.sh --脚本内容
#!/bin/bashpkill keepalived/usr/bin/keepalived -D[root@node1 keepalived]#6.启动服务和测试状态
node1节点和node1节点:
[root@node2 keepalived]# /etc/init.d/mysqld restart --两个节点启动服务
ERROR! MySQL server PID file could not be found!Starting MySQL. SUCCESS! [root@node2 keepalived]# /etc/init.d/keepalived restart --两个节点启动服务Stopping keepalived: [FAILED]Starting keepalived: [ OK ][root@node2 etc]# /usr/local/mysql-5.6.23/bin/mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.23-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> grant all privileges on *.* to remote@'%' identified by 'system'; --在两个节点创建相同的远程登陆用户Query OK, 0 rows affected (0.07 sec)mysql> flush privileges;Query OK, 0 rows affected (0.03 sec)mysql>node2节点:
[root@node2 keepalived]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000 link/ether d0:27:88:7d:83:9b brd ff:ff:ff:ff:ff:ff inet 192.168.1.251/24 brd 192.168.1.255 scope global eth0 inet 192.168.1.201/32 scope global eth0 --VIP地址在node2已经启动 inet6 fe80::d227:88ff:fe7d:839b/64 scope link valid_lft forever preferred_lft forever[root@node2 keepalived]#用mysql客户端登陆VIP地址:
[root@node3 keepalived]# /usr/local/mysql-5.6.23/bin/mysql -u remote -p -h 192.168.1.201
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 27Server version: 5.6.23-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \u tongReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table g (a int);Query OK, 0 rows affected (0.29 sec)mysql> insert into g values(1);Query OK, 1 row affected (0.09 sec)mysql> select * from g;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> exitBye[root@node2 keepalived]# /etc/init.d/mysqld stop --关闭node2节点的mysql服务Shutting down MySQL.... SUCCESS! [root@node2 keepalived]# ip addr show1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000 link/ether d0:27:88:7d:83:9b brd ff:ff:ff:ff:ff:ff inet 192.168.1.251/24 brd 192.168.1.255 scope global eth0 --VIP不见了 inet6 fe80::d227:88ff:fe7d:839b/64 scope link valid_lft forever preferred_lft forever[root@node2 keepalived]#node1节点:
[root@node1 keepalived]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 10:78:d2:c9:50:28 brd ff:ff:ff:ff:ff:ff inet 192.168.1.250/24 brd 192.168.1.255 scope global eth0 inet 192.168.1.201/32 scope global eth0 --VIP在node1启动了 inet6 fe80::1278:d2ff:fec9:5028/64 scope link valid_lft forever preferred_lft forever[root@node1 keepalived]#用mysql客户端登陆VIP地址:
[root@node3 bin]# ./mysql -u remote -p -h 192.168.1.201 -P 3306
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 48Server version: 5.6.23-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \u tongReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from g; --数据同步了+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql>转载地址：http://etfdx.baihongyu.com/