MySQL集群搭建,新加坡vps云主机租用评测,virmachvps搭建教程,久久vps加速器,云主机提供的主要功能

🗣 Wd提问: MySQL集群搭建

🤖 Ai回答:
我来为您详细介绍MySQL集群的搭建方法，主要介绍几种常见的MySQL集群方案。

1、MySQL主从复制（Master-Slave）

搭建步骤

主服务器配置
ini
/etc/my.cnf 或 my.ini
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = your_database_name

从服务器配置
ini
/etc/my.cnf 或 my.ini
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1
replicate-do-db = your_database_name

数据同步
bash
主服务器创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

查看主服务器状态
SHOW MASTER STATUS;

从服务器配置主从关系
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

启动从服务器
START SLAVE;
SHOW SLAVE STATUS\G

2、MySQL主主复制（Master-Master）

配置文件设置
ini
Server 1 配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
auto-increment-increment = 2
auto-increment-offset = 1

Server 2 配置
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
auto-increment-increment = 2
auto-increment-offset = 2

双向复制配置
sql
Server 1 上执行
CHANGE MASTER TO
MASTER_HOST='server2_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

START SLAVE;

Server 2 上执行
CHANGE MASTER TO
MASTER_HOST='server1_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

START SLAVE;

3、MySQL Group Replication（MGR）

环境准备
ini
所有节点通用配置
[mysqld]
基础配置
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE

Group Replication 配置
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = node1:33061
loose-group_replication_group_seeds = node1:33061,node2:33061,node3:33061
loose-group_replication_bootstrap_group = OFF

启动Group Replication
sql
在第一个节点上启动
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

启动组复制
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

在其他节点上加入
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

4、MySQL InnoDB Cluster

安装MySQL Shell
bash
Ubuntu/Debian
sudo apt-get install mysql-shell

CentOS/RHEL
sudo yum install mysql-shell

创建InnoDB Cluster
javascript
// 连接到MySQL Shell
mysqlsh uri=root@localhost:3306

// 创建集群
var cluster = dba.createCluster('myCluster');

// 添加实例
cluster.addInstance('root@host1:3306');
cluster.addInstance('root@host2:3306');
cluster.addInstance('root@host3:3306');

// 检查集群状态
cluster.status();

5、使用Docker搭建MySQL集群

Docker Compose配置
yaml
version: '3.8'

services:
mysql-master:
image: mysql:8.0
container_name: mysql-master
environment:
MYSQL_ROOT_PASSWORD: rootpassword
MYSQL_DATABASE: testdb
ports:
3307:3306
volumes:
./master/conf.d:/etc/mysql/conf.d
mysql-master-data:/var/lib/mysql
networks:
mysql-cluster

mysql-slave1:
image: mysql:8.0
container_name: mysql-slave1
environment:
MYSQL_ROOT_PASSWORD: rootpassword
ports:
3308:3306
volumes:
./slave1/conf.d:/etc/mysql/conf.d
mysql-slave1-data:/var/lib/mysql
depends_on:
mysql-master
networks:
mysql-cluster

mysql-slave2:
image: mysql:8.0
container_name: mysql-slave2
environment:
MYSQL_ROOT_PASSWORD: rootpassword
ports:
3309:3306
volumes:
./slave2/conf.d:/etc/mysql/conf.d
mysql-slave2-data:/var/lib/mysql
depends_on:
mysql-master
networks:
mysql-cluster

volumes:
mysql-master-data:
mysql-slave1-data:
mysql-slave2-data:

networks:
mysql-cluster:

Master配置文件 (master/conf.d/master.cnf)
ini
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

Slave配置文件 (slave1/conf.d/slave.cnf)
ini
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

6、监控和管理脚本

健康检查脚本
bash
!/bin/bash
check_mysql_cluster.sh

MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=monitor
MYSQL_PASS=monitor_password

check_slave_status() {
mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS -e
SHOW SLAVE STATUS\G
| grep -E (Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master)
}

check_master_status() {
mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS -e
SHOW MASTER STATUS\G

}

执行检查
echo === Master Status ===
check_master_status
echo === Slave Status ===
check_slave_status

自动故障转移脚本
python
!/usr/bin/env python3
import subprocess
import time
import smtplib

def check_mysql_connection(host, port):
try:
result = subprocess.run([
'mysqladmin',
'-h', host,
'-P', str(port),
'ping'
], capture_output=True, text=True, timeout=5)
return result.returncode == 0
except:
return False

def promote_slave_to_master(slave_host):
实现从库提升为主库的逻辑
pass

def send_alert(message):
发送告警邮件或短信
pass

if __name__ == __main__:
master_host = 192.168.1.100
slave_hosts = [192.168.1.101, 192.168.1.102]

if not check_mysql_connection(master_host, 3306):
print(Master is down!)
send_alert(Master database is down!)

尝试故障转移到从库
for slave in slave_hosts:
if check_mysql_connection(slave, 3306):
promote_slave_to_master(slave)
send_alert(fFailover to {slave} completed)
break

7、性能优化建议

参数调优
ini
内存相关
innodb_buffer_pool_size = 70% of RAM
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M

连接相关
max_connections = 1000
max_connect_errors = 100000

复制相关
slave_parallel_workers = 4
slave_preserve_commit_order = ON

监控指标
主从延迟时间
QPS和TPS
连

Ai作答

📣 商家广告：