Linux系统运维之MYSQL数据库集群部署(主主互备)
一、介绍
既然是部署MYSQL高可用集群环境，就要介绍下MYSQL Replication，MYSQL Replication是MYSQL自带的一个主从复制功能，也就是一台MYSQL服务器向另外一台MYSQL服务器上复制日志，然后进行日志解析并应用到自身的一个过程。同时MYSQL Replication是单向、异步复制的。
基于单向复制的技术特点如下：
- 增加了MYSQL应用的健壮性，如果Matser端出现问题，那么可以随时切换到Slave端继续提高服务；
- 在同一个网络中，业务量不是非常大的情况下，Slave端同步数据很快，基本上可以保障与Master端的实时同步，且同步过程中不会造成Master端的正常工作；
- 可以将MYSQL读、写分离开，写操作只在Master端进行，而读操作可以在一个或多个Slave端完成，由于Master和Slave是保持数据同步的，这样在保证不影响业务连续性的同时，又降低了单台MYSQL服务器的负载。
MYSQL Replication支持多类型复制方式：
- 基于SQL模式：MYSQL默认采用基于SQL语句的复制方式，这样效率较高；工作原理：在Master端执行SQL语句，在Slave端再次执行同样的语句；
- 基于行模式：直接将Master端改变的内容复制到Slave端；
- 混合模式：先基于SQL模式进行SQL语句的复制，当无法精准复制时，再基于行模式进行内容上的复制；
二、拓扑图
三、配置环境
服务器地址及软件版本信息如下：
将主机名、IP对应关系写入到/etc/hosts文件中：
[root@UIPLUSt-MSDB02 ~]# echo '192.168.0.191 UIPLUSt-MSDB01' >> /etc/hosts [root@UIPLUSt-MSDB02 ~]# echo '192.168.0.192 UIPLUSt-MSDB02' >> /etc/hosts [root@UIPLUSt-MSDB01 ~]# echo '192.168.0.191 UIPLUSt-MSDB01' >> /etc/hosts [root@UIPLUSt-MSDB01 ~]# echo '192.168.0.192 UIPLUSt-MSDB02' >> /etc/hosts
安装Mysql数据库，这里用yum安装，建议生成环境选用源码安装：
[root@UIPLUSt-MSDB01 ~]# yum install httpd php mysql mysql-server php-mysql -y --------------------------------------- Transaction Summary ================================================================================================================================================================================================================== Install 15 Package(s) Upgrade 1 Package(s) Total download size: 17 M Downloading Packages: (1/16): apr-1.3.9-5.el6_2.x86_64.rpm | 123 kB 00:00 (2/16): apr-util-1.3.9-3.el6_0.1.x86_64.rpm | 87 kB 00:00 (3/16): apr-util-ldap-1.3.9-3.el6_0.1.x86_64.rpm | 15 kB 00:00 (4/16): httpd-2.2.15-59.el6.centos.x86_64.rpm | 834 kB 00:00 (5/16): httpd-tools-2.2.15-59.el6.centos.x86_64.rpm | 79 kB 00:00 (6/16): mailcap-2.1.31-2.el6.noarch.rpm | 27 kB 00:00 (7/16): mysql-5.1.73-8.el6_8.x86_64.rpm | 895 kB 00:02 (8/16): mysql-libs-5.1.73-8.el6_8.x86_64.rpm | 1.2 MB 00:03 (9/16): mysql-server-5.1.73-8.el6_8.x86_64.rpm | 8.6 MB 00:32 (10/16): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:00 (11/16): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:02 (12/16): php-5.3.3-49.el6.x86_64.rpm | 1.1 MB 00:02 (13/16): php-cli-5.3.3-49.el6.x86_64.rpm | 2.2 MB 00:05 (14/16): php-common-5.3.3-49.el6.x86_64.rpm | 530 kB 00:00 (15/16): php-mysql-5.3.3-49.el6.x86_64.rpm | 86 kB 00:00 (16/16): php-pdo-5.3.3-49.el6.x86_64.rpm | 80 kB 00:00 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Total 329 kB/s | 17 MB 00:51 Installed: httpd.x86_64 0:2.2.15-59.el6.centos mysql.x86_64 0:5.1.73-8.el6_8 mysql-server.x86_64 0:5.1.73-8.el6_8 php.x86_64 0:5.3.3-49.el6 php-mysql.x86_64 0:5.3.3-49.el6 Dependency Installed: apr.x86_64 0:1.3.9-5.el6_2 apr-util.x86_64 0:1.3.9-3.el6_0.1 apr-util-ldap.x86_64 0:1.3.9-3.el6_0.1 httpd-tools.x86_64 0:2.2.15-59.el6.centos mailcap.noarch 0:2.1.31-2.el6 perl-DBD-MySQL.x86_64 0:4.013-3.el6 perl-DBI.x86_64 0:1.609-4.el6 php-cli.x86_64 0:5.3.3-49.el6 php-common.x86_64 0:5.3.3-49.el6 php-pdo.x86_64 0:5.3.3-49.el6 Dependency Updated: mysql-libs.x86_64 0:5.1.73-8.el6_8 Complete!
[root@UIPLUSt-MSDB02 ~]# yum install httpd php mysql mysql-server php-mysql -y --------------------------------------- Transaction Summary ================================================================================================================================================================================================================== Install 15 Package(s) Upgrade 1 Package(s) Total download size: 17 M Downloading Packages: (1/16): apr-1.3.9-5.el6_2.x86_64.rpm | 123 kB 00:00 (2/16): apr-util-1.3.9-3.el6_0.1.x86_64.rpm | 87 kB 00:00 (3/16): apr-util-ldap-1.3.9-3.el6_0.1.x86_64.rpm | 15 kB 00:00 (4/16): httpd-2.2.15-59.el6.centos.x86_64.rpm | 834 kB 00:00 (5/16): httpd-tools-2.2.15-59.el6.centos.x86_64.rpm | 79 kB 00:00 (6/16): mailcap-2.1.31-2.el6.noarch.rpm | 27 kB 00:00 (7/16): mysql-5.1.73-8.el6_8.x86_64.rpm | 895 kB 00:02 (8/16): mysql-libs-5.1.73-8.el6_8.x86_64.rpm | 1.2 MB 00:03 (9/16): mysql-server-5.1.73-8.el6_8.x86_64.rpm | 8.6 MB 00:32 (10/16): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:00 (11/16): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:02 (12/16): php-5.3.3-49.el6.x86_64.rpm | 1.1 MB 00:02 (13/16): php-cli-5.3.3-49.el6.x86_64.rpm | 2.2 MB 00:05 (14/16): php-common-5.3.3-49.el6.x86_64.rpm | 530 kB 00:00 (15/16): php-mysql-5.3.3-49.el6.x86_64.rpm | 86 kB 00:00 (16/16): php-pdo-5.3.3-49.el6.x86_64.rpm | 80 kB 00:00 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Total 329 kB/s | 17 MB 00:51 Installed: httpd.x86_64 0:2.2.15-59.el6.centos mysql.x86_64 0:5.1.73-8.el6_8 mysql-server.x86_64 0:5.1.73-8.el6_8 php.x86_64 0:5.3.3-49.el6 php-mysql.x86_64 0:5.3.3-49.el6 Dependency Installed: apr.x86_64 0:1.3.9-5.el6_2 apr-util.x86_64 0:1.3.9-3.el6_0.1 apr-util-ldap.x86_64 0:1.3.9-3.el6_0.1 httpd-tools.x86_64 0:2.2.15-59.el6.centos mailcap.noarch 0:2.1.31-2.el6 perl-DBD-MySQL.x86_64 0:4.013-3.el6 perl-DBI.x86_64 0:1.609-4.el6 php-cli.x86_64 0:5.3.3-49.el6 php-common.x86_64 0:5.3.3-49.el6 php-pdo.x86_64 0:5.3.3-49.el6 Dependency Updated: mysql-libs.x86_64 0:5.1.73-8.el6_8 Complete!
启动数据库服务：
[root@UIPLUSt-MSDB01 ~]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Initializing MySQL database: Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h UIPLUSt-MSDB01 password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/bin/mysqlbug script! [ OK ] Starting mysqld: [ OK ]
根据提示，配置数据库密码以及访问权限：
[root@UIPLUSt-MSDB01 ~]# /usr/bin/mysqladmin -u root password 'mysqldb' [root@UIPLUSt-MSDB01 ~]# /usr/bin/mysqladmin -u root -h192.168.0.191 password 'mysqldb' ---------------------------------------------------------------------------
[root@UIPLUSt-MSDB02 ~]# /usr/bin/mysqladmin -u root password 'mysqldb' [root@UIPLUSt-MSDB02 ~]# /usr/bin/mysqladmin -u root -h192.168.0.192 password 'mysqldb'
验证账号密码，登录mysql：
[root@UIPLUSt-MSDB01 ~]# mysql -uroot -pmysqldb Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec) mysql>
四、配置主从同步
修改MySQL配置文件，在/etc/my.cnf配置文件中“[mysqld]”段落后，配置如下内容：
[root@UIPLUSt-MSDB01 etc]# vim my.cnf server-id=1 log-bin=mysql-bin relay-log=mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.% log-slave-updates [root@UIPLUSt-MSDB02 etc]# vim my.cnf server-id=2 log-bin=mysql-bin relay-log=mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.% log-slave-updates
- server-id : 节点标识，主、从节点不能相同，必须是全局唯一；
- log-bin：开启MySQLde binlog日志功能，‘mysql-bin’为日志文件格式；
- relay-log：定义relay-log日志文件格式；
- replicate-wild-ignore-table：复制过滤，过滤不需要复制的数据库；
- replicate_wild_do_table：复制过滤，与上面相反，过滤需要复制的数据库；
- log-slave-updates：将更新记录写入二进制文件，以便从进行复制；
- slave-skip-errors：不要乱使用这个跳过错误的参数，除非你知道自己在做什么，当使用这个参数时候，MYSQL会忽略那些错误，这样会导致主从服务器数据不一致；
- 注意：不要在主库上使用binlog-do-db或者binlog-ignore-db选项，也不要在从库上使用replicate-do-db或者replicate-ignore-db选项，这样会造成夸库更新失败；推荐在从库上使用replicate_wild_do_table和replicate-wild-ignore-table选项来解决复制过滤问题。
配置完以上配置，如果主数据库有数据，需要登录数据库进行手动同步操作：
[root@UIPLUSt-MSDB01 etc]# mysql -uroot -pmysqldb mysql> use mysql； mysql> flush tables with read lock; Query OK, 3 rows affected (0.01 sec) #注：这里不要退出终端，从新打开终端进行mysqldump备份数据 [root@UIPLUSt-MSDB01 ~]# mysqldump -uroot -pmysqldb --events --ignore-table=mysql.events mysql > mysql.sql [root@UIPLUSt-MSDB01 ~]# ls anaconda-ks.cfg install.log install.log.syslog mysql.sql
将数据导入到MSDB02数据库上，依次重启MySQL服务：
[root@UIPLUSt-MSDB02 ~]# mysql -uroot -pmysqldb < mysql.sql [root@UIPLUSt-MSDB01 etc]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@UIPLUSt-MSDB02 etc]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
依次登录MSDB01，MSDB02，进行创建复制用户并授权操作:
[root@UIPLUSt-MSDB01 etc]# mysql -uroot -pmysqldb mysql> grant replication slave on *.* to 'repl'@'192.168.0.192' identified by 'replpwd'; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 261 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) ================================================= [root@UIPLUSt-MSDB02 ~]# mysql -uroot -pmysqldb mysql> change master to master_host='192.168.0.191',master_user='repl',master_password='replpwd',master_log_file='mysql-bin.000002',master_log_pos=106; Query OK, 0 rows affected (0.08 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
查询Slave状态，确认主从配置：
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.191 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 261 Relay_Log_File: mysql-relay-bin.000004 Relay_Log_Pos: 406 Relay_Master_Log_File: mysql-bin.000002 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.%,test.%,information_schema.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 261 Relay_Log_Space: 561 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: 1 row in set (0.00 sec
以上，从MSDB01到MSDB02的主从复制配置完成；下面配置MSDB02到MSDB01的主从复制：
[root@UIPLUSt-MSDB02 ~]# mysql -uroot -pmysqldb mysql> grant replication slave on *.* to 'repl'@'192.168.0.191' identified by 'replpwd'; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 261 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) ==================================================== [root@UIPLUSt-MSDB01 etc]# mysql -uroot -pmysqldb mysql> change master to master_host='192.168.0.192',master_user='repl',master_password='replpwd',master_log_file='mysql-bin.000003',master_log_pos=106; Query OK, 0 rows affected (0.05 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: 192.168.0.192 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 261 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 406 Relay_Master_Log_File: mysql-bin.000003 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.%,test.%,information_schema.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 261 Relay_Log_Space: 561 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: 1 row in set (0.00 sec)
至此，MYSQL双主模式的主从复制配置完成。
五、配置Keepalived实现双主高可用
关于Keepalived:
Keepalived最初是为LVS设计，监控集群系统中各个服务节点的状态；根据TCP/IP参考模型的第三、第四和第五层交换机制检测节点的状态，如果服务节点出现异常，宕机等，Keepalived将检测到，并将有异常的节点从集群系统中摘除；而在异常节点恢复后， 从新添加到集群中，这些操作都是自 动化，无需人工干预。
Keepalived后来又加入了VRRP(虚拟路由冗余协议)功能，目的是解决静态路由出现的单节点故障；通过VRRP可以实现网络不间断稳定运行，因此Keepalived具备服务器状态检测和故障隔离能力，同时又具备HA cluster功能。
下载安装Keepalived：
[root@UIPLUSt-MSDB02 ~]# cd /opt/ && wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz [root@UIPLUSt-MSDB02 opt]# cd keepalived-1.3.5 [root@UIPLUSt-MSDB02 ~]# yum install gcc openssl-devel kernel-devel popt-devel ipvsadm -y [root@UIPLUSt-MSDB02 keepalived-1.3.5]# ./configure --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-504.el6.x86_64 ------------------------------------------------------------------------ Keepalived configuration ------------------------ Keepalived version : 1.3.5 Compiler : gcc Preprocessor flags : -I/usr/src/kernels/2.6.32-504.el6.x86_64/include Compiler flags : -Wall -Wunused -Wstrict-prototypes -Wextra -g -O2 Linker flags : Extra Lib : -lcrypto -lssl Use IPVS Framework : Yes IPVS use libnl : No IPVS syncd attributes : No IPVS 64 bit stats : No fwmark socket support : Yes Use VRRP Framework : Yes Use VRRP VMAC : Yes Use VRRP authentication : Yes With ip rules/routes : Yes ------------------------------------------------------------------------ [root@UIPLUSt-MSDB02 keepalived-1.3.5]# make && make install
[root@UIPLUSt-MSDB02 keepalived-1.3.5]# ln -s /usr/local/sbin/keepalived /sbin/ [root@UIPLUSt-MSDB02 keepalived-1.3.5]# cp keepalived/etc/init.d/keepalived /etc/init.d/
[root@UIPLUSt-MSDB02 keepalived-1.3.5]# chkconfig --add keepalived [root@UIPLUSt-MSDB02 keepalived-1.3.5]# chkconfig keepalived on
配置/etc/keepalived/keepalived.conf文件：
global_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script check_mysqld { script "/ect/keepalived/mysqlcheck/check_slave.sh 127.0.0.1" interval 2 weight 21 } vrrp_instance HA_1 { state BACKUP interface eth2 virtual_router_id 80 priority 100 advert_int 2 nopreempt authentication { auth_type PASS auth_pass qweasdzxc } track_script { check_mysqld } virtual_ipaddress { 192.168.0.193/24 dev eth2 } }
关于/ect/keepalived/mysqlcheck/check_slave.sh脚本：
#!/bin/bash export HOST_IP=$1 export HOST_PORT=3306 export IP=`ifconfig | grep Ethernet -A1 | grep -v Link | awk -F[' ':]+ '{print $4}'` MYUSER=root MYPASS="mysqldb" MYSQL_PATH=/usr/bin/ MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS" MailTitle="" time1=`date +"%Y%m%d%H%M%S"` time2=`date +"%Y-%m-%d %H:%M:%S"` SlaveStatusFile="/tmp/slave_status_${HOST_PORT}.${time1}.log" echo "------------------Begins at :"$time2 > $SlaveStatusFile echo "" >> $SlaveStatusFile #get slave status $MYSQL_CMD -e "show slave status\G" >> $SlaveStatusFile #get io_thread_status, sql_thread_status SlaveStatus=($($MYSQL_CMD -e "show slave status\G" | egrep "_Running"|awk '{print $NF}')) echo "" >> $SlaveStatusFile if [ "${SlaveStatus[0]}" = "No" ] || [ "${SlaveStatus[1]}" = "No" ] then echo "Slave $IP $HOST_PORT is down !" >> $SlaveStatusFile MailTitle="[ERROR] Slave replication is down on $IP $HOST_PORT !" fi if [ -n "$MailTitle" ] then /etc/init.d/keepalived stop fi
至此，分别启动MSDB01和MSDB02上额Keepalived服务，然后查看VIP是否在MSDB01上：
[root@UIPLUSt-MSDB01 keepalived]# /etc/init.d/keepalived start Starting keepalived: [ OK ] [root@UIPLUSt-MSDB02 keepalived]# /etc/init.d/keepalived start Starting keepalived: [ OK ] [root@UIPLUSt-MSDB01 keepalived]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 forever 2: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:c0:2b:82 brd ff:ff:ff:ff:ff:ff inet 192.168.0.191/24 brd 192.168.0.255 scope global eth2 inet 192.168.0.193/24 scope global secondary eth2 inet6 fe80::20c:29ff:fec0:2b82/64 scope link valid_lft forever preferred_lft forever
六、测试
本文来自博客园，作者：白日梦想家Zz，转载请注明原文链接：https://www.cnblogs.com/zzlain/p/6860007.html
