mysql高可用方案之Keepalived+主主复制-mysql教程-PHP中文網

首頁

資料庫

mysql教程

mysql高可用方案之Keepalived+主主复制

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:56 PM

mysql可用方案高可用

环境规划: node1: 192.168.1.250 node2: 192.168.1.251 vip: 192.168.1.201 数据库: mysql-5.6.23 mysqldba技术群 378190849 武汉-linux运维群 236415619 1.各节点的网络配置 node1节点: [root@node1 ~]# hostname node1 [root@node1 ~]# ip addr 1: lo: LOO

环境规划:

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 addr
1: lo: 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 forever
2: eth0: 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 addr
1: lo: 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 forever
2: eth0: 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 http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz

[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.cnf

basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23
port = 3306
server_id = 10 --将另一台主修改为20
socket = /tmp/mysql.sock

log-bin=mysql-bin
log-bin-index=mysql-bin-index

replicate-do-db=tong
replicate-ignore-db=mysql

auto_increment_offset=1 --将另一台主修改为2
auto_increment_increment=2

relay-log=relay-log
relay-log-index=relay-log-inde

log_slave_updates
sync-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 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server 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 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> 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 2
Server 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 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> 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 changed
mysql> 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 names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 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 http://www.keepalived.org/software/keepalived-1.2.15.tar.gz

[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 16
drwxr-xr-x. 2 root root 4096 Apr 30 11:46 bin
drwxr-xr-x. 5 root root 4096 Apr 30 11:46 etc
drwxr-xr-x. 2 root root 4096 Apr 30 11:46 sbin
drwxr-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/bash
pkill keepalived
/usr/bin/keepalived -D
[root@node1 keepalived]#

node2节点:

安装keepalived软件是一样

[root@node2 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
   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/bash
pkill 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 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server 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 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> 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: 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 forever
2: eth0: 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 27
Server 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 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> \u tong
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 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> exit
Bye
[root@node2 keepalived]# /etc/init.d/mysqld stop    --关闭node2节点的mysql服务
Shutting down MySQL.... SUCCESS!
[root@node2 keepalived]# ip addr show
1: lo: 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 forever
2: eth0: 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: 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 forever
2: eth0: 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 48
Server 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 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> \u tong
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from g;      --数据同步了
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

學習MySQL：新用戶的分步指南Apr 19, 2025 am 12:19 AM

MySQL值得學習，因為它是強大的開源數據庫管理系統，適用於數據存儲、管理和分析。 1）MySQL是關係型數據庫，使用SQL操作數據，適合結構化數據管理。 2）SQL語言是與MySQL交互的關鍵，支持CRUD操作。 3）MySQL的工作原理包括客戶端/服務器架構、存儲引擎和查詢優化器。 4）基本用法包括創建數據庫和表，高級用法涉及使用JOIN連接表。 5）常見錯誤包括語法錯誤和權限問題，調試技巧包括檢查語法和使用EXPLAIN命令。 6）性能優化涉及使用索引、優化SQL語句和定期維護數據庫。

MySQL：初學者的基本技能Apr 18, 2025 am 12:24 AM

MySQL適合初學者學習數據庫技能。 1.安裝MySQL服務器和客戶端工具。 2.理解基本SQL查詢，如SELECT。 3.掌握數據操作：創建表、插入、更新、刪除數據。 4.學習高級技巧：子查詢和窗口函數。 5.調試和優化：檢查語法、使用索引、避免SELECT*，並使用LIMIT。

MySQL：結構化數據和關係數據庫Apr 18, 2025 am 12:22 AM

MySQL通過表結構和SQL查詢高效管理結構化數據，並通過外鍵實現表間關係。 1.創建表時定義數據格式和類型。 2.使用外鍵建立表間關係。 3.通過索引和查詢優化提高性能。 4.定期備份和監控數據庫確保數據安全和性能優化。

MySQL：解釋的關鍵功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一個開源的關係型數據庫管理系統，廣泛應用於Web開發。它的關鍵特性包括：1.支持多種存儲引擎，如InnoDB和MyISAM，適用於不同場景；2.提供主從復制功能，利於負載均衡和數據備份；3.通過查詢優化和索引使用提高查詢效率。

SQL的目的：與MySQL數據庫進行交互Apr 18, 2025 am 12:12 AM

SQL用於與MySQL數據庫交互，實現數據的增、刪、改、查及數據庫設計。 1）SQL通過SELECT、INSERT、UPDATE、DELETE語句進行數據操作；2）使用CREATE、ALTER、DROP語句進行數據庫設計和管理；3）複雜查詢和數據分析通過SQL實現，提升業務決策效率。

初學者的MySQL：開始數據庫管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括創建數據庫、表格，及使用SQL進行數據的CRUD操作。 1.創建數據庫：CREATEDATABASEmy_first_db;2.創建表格：CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入數據：INSERTINTObooks(title,author,published_year)VA