mysql高可用方案之Keepalived+主主复制
环境规划: 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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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>
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
MySQL：世界上最受歡迎的數據庫的簡介
Apr 12, 2025 am 12:18 AM
MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。
MySQL的位置：數據庫和編程
Apr 13, 2025 am 12:18 AM
MySQL在數據庫和編程中的地位非常重要，它是一個開源的關係型數據庫管理系統，廣泛應用於各種應用場景。 1）MySQL提供高效的數據存儲、組織和檢索功能，支持Web、移動和企業級系統。 2）它使用客戶端-服務器架構，支持多種存儲引擎和索引優化。 3）基本用法包括創建表和插入數據，高級用法涉及多表JOIN和復雜查詢。 4）常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5）性能優化方法包括合理使用索引、優化查詢和使用緩存，最佳實踐包括使用事務和PreparedStatemen
apache怎麼連接數據庫
Apr 13, 2025 pm 01:03 PM
Apache 連接數據庫需要以下步驟：安裝數據庫驅動程序。配置 web.xml 文件以創建連接池。創建 JDBC 數據源，指定連接設置。從 Java 代碼中使用 JDBC API 訪問數據庫，包括獲取連接、創建語句、綁定參數、執行查詢或更新以及處理結果。
為什麼要使用mysql？利益和優勢
Apr 12, 2025 am 12:17 AM
選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。
MySQL的角色：Web應用程序中的數據庫
Apr 17, 2025 am 12:23 AM
MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢，開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作，確保查詢速度可接受。
docker怎麼啟動mysql
Apr 15, 2025 pm 12:09 PM
在 Docker 中啟動 MySQL 的過程包含以下步驟：拉取 MySQL 鏡像創建並啟動容器，設置根用戶密碼並映射端口驗證連接創建數據庫和用戶授予對數據庫的所有權限
laravel入門實例
Apr 18, 2025 pm 12:45 PM
Laravel 是一款 PHP 框架，用於輕鬆構建 Web 應用程序。它提供一系列強大的功能，包括：安裝： 使用 Composer 全局安裝 Laravel CLI，並在項目目錄中創建應用程序。路由： 在 routes/web.php 中定義 URL 和處理函數之間的關係。視圖： 在 resources/views 中創建視圖以呈現應用程序的界面。數據庫集成： 提供與 MySQL 等數據庫的開箱即用集成，並使用遷移來創建和修改表。模型和控制器： 模型表示數據庫實體，控制器處理 HTTP 請求。
centos7如何安裝mysql
Apr 14, 2025 pm 08:30 PM
優雅安裝 MySQL 的關鍵在於添加 MySQL 官方倉庫。具體步驟如下：下載 MySQL 官方 GPG 密鑰，防止釣魚攻擊。添加 MySQL 倉庫文件：rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm更新 yum 倉庫緩存：yum update安裝 MySQL：yum install mysql-server啟動 MySQL 服務：systemctl start mysqld設置開機自啟動
