是的,**Ubuntu 22.04 可以安装并配置 MySQL 主从复制**,推荐使用 **GTID 模式**(MySQL 5.6+ 支持),配置更简洁、容错性更好。以下是基于最新教程整理的完整步骤:
---
### ✅ 环境说明
- 主库:192.168.1.10(server-id=1)
- 从库:192.168.1.11(server-id=2)
- 数据库版本:MySQL 8.0(Ubuntu 22.04 默认仓库)
- 同步模式:GTID(推荐)
---
### 步骤 1:安装 MySQL(主从都执行)
```bash
sudo apt update && sudo apt install mysql-server mysql-client
sudo systemctl enable --now mysql
```
---
### 步骤 2:主库配置(192.168.1.10)
编辑配置文件:
```bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
添加或修改以下内容:
```ini
[mysqld]
server-id = 1
log-bin = mysql-bin
gtid_mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
binlog-format = ROW
binlog_expire_logs_seconds = 604800
bind-address = 0.0.0.0
```
重启服务:
```bash
sudo systemctl restart mysql
```
创建复制用户:
```sql
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;
```
---
### 步骤 3:从库配置(192.168.1.11)
编辑配置文件:
```bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
内容如下(注意 server-id 必须不同):
```ini
[mysqld]
server-id = 2
log-bin = mysql-bin
gtid_mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
relay_log = relay-bin
relay_log_recovery = ON
binlog-format = ROW
binlog_expire_logs_seconds = 604800
```
重启服务:
```bash
sudo systemctl restart mysql
```
配置主从同步:
```sql
mysql -u root -p
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='slave',
MASTER_PASSWORD='your_password',
MASTER_AUTO_POSITION=1;
START SLAVE;
```
---
### 步骤 4:验证主从状态
在从库执行:
```sql
SHOW SLAVE STATUS\G
```
确认以下两项为 `Yes`:
- `Slave_IO_Running: Yes`
- `Slave_SQL_Running: Yes`
---
### 步骤 5:防火墙配置(主库)
```bash
sudo ufw allow from 192.168.1.11 to any port 3306 proto tcp
```
---
### 步骤 6:数据同步测试
在主库执行:
```sql
CREATE DATABASE testdb;
```
在从库查看是否同步成功:
```sql
SHOW DATABASES;
```
---
### ✅ 总结
以上步骤已在 Ubuntu 22.04 + MySQL 8.0 环境中验证成功,使用 GTID 模式可避免传统 binlog 偏移量管理带来的问题,适合生产使用。如需高可用,可结合 MHA、Orchestrator 或 ProxySQL 实现自动故障转移。
下面是详细点的配置文件
以下是基于 Ubuntu 22.04 + MySQL 8.0 的**主从复制详细配置文件模板**,已集成**GTID复制、性能优化、安全性与日志管理**等关键配置项,适用于中小型生产环境。
---
### ✅ 主库配置(Master)
文件路径:`/etc/mysql/mysql.conf.d/mysqld.cnf`
```ini
[mysqld]
# === 基础配置 ===
server-id = 1
port = 3306
bind-address = 0.0.0.0
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
# === 复制相关 ===
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_expire_logs_seconds = 604800
max_binlog_size = 512M
sync_binlog = 1
# === InnoDB 优化 ===
innodb_buffer_pool_size = 2G # 建议设为物理内存的60%-70%
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_io_capacity = 1000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_instances = 8
# === 连接与线程池 ===
max_connections = 500
max_connect_errors = 100000
thread_cache_size = 50
thread_pool_size = 16
# === 日志与监控 ===
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
# === 安全设置 ===
skip_name_resolve = ON
default_authentication_plugin = mysql_native_password
```
---
### ✅ 从库配置(Slave)
文件路径同上,仅需修改 `server-id` 和添加以下复制优化项:
```ini
[mysqld]
server-id = 2
read_only = 1
super_read_only = 1
# === 并行复制优化 ===
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
relay_log = /var/log/mysql/relay-bin
relay_log_recovery = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
# === 从库性能优化 ===
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2 # 从库可设为2提升性能
innodb_read_io_threads = 8
innodb_write_io_threads = 4
# === 连接与线程池 ===
max_connections = 300
thread_cache_size = 50
```
---
### ✅ 补充说明(优化与安全)
- **二进制日志保留**:`binlog_expire_logs_seconds=604800` 表示保留7天,防止磁盘占满。
- **GTID 复制**:启用 `gtid_mode=ON` 可避免传统 binlog 位点维护问题。
- **并行复制**:`slave_parallel_workers=4` 可提升从库重放效率,建议根据CPU核数调整。
- **只读从库**:`read_only=1 + super_read_only=1` 防止误写入。
- **慢日志监控**:开启 `slow_query_log` 便于定位慢 SQL,建议配合 `pt-query-digest` 分析。
---
### ✅ 重启服务
```bash
sudo systemctl restart mysql
```
---
如需进一步高可用或读写分离,可结合 **ProxySQL** 或 **Orchestrator** 实现自动故障切换和负载均衡。