部署

创建mysql用户

useradd -r -M -s /sbin/nologin mysql

下载安装包

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.39-linux-glibc2.28-x86_64.tar

解压文件：

tar xf mysql-8.0.37-linux-glibc2.28-x86_64.tar -C /data/
mv /data/mysql-8.0.37-linux-glibc2.28-x86_64/ /data/mysql-8.0.37
mkdir -pv /data/mysql-8.0.37/{binlog,log,relaylog}

创建配置文件

[mysqld]
user=mysql
port=3306
basedir=/data/mysql-8.0.37
datadir=/data/mysql-8.0.37/data
symbolic-links=0
open_files_limit = 65535
server_id=63
# GTID 配置
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=on
# 多源复制必须的参数
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay_log_recovery=1
# 自动增量配置
auto-increment-increment=2
auto-increment-offset=1
# 复制日志同步频率
sync_relay_log=1
sync_relay_log_info=1
sync_master_info=1
# InnoDB 缓冲池大小，建议设置为系统内存的 50%-80%
innodb_buffer_pool_size=32G  # 根据实际内存调整
# InnoDB 日志文件大小，建议设置为 1G-2G
innodb_log_file_size=1G
# InnoDB 日志缓冲区大小
innodb_log_buffer_size=64M
# InnoDB 刷新日志方式
innodb_flush_method=O_DIRECT
# InnoDB 并发线程数
innodb_thread_concurrency=0  # 0 表示自动调整
# InnoDB IO 容量
innodb_io_capacity=2000  # 根据磁盘性能调整
innodb_io_capacity_max=4000
# InnoDB 刷新邻居页
innodb_flush_neighbors=0  # SSD 建议设置为 0
# InnoDB 自适应哈希索引
innodb_adaptive_hash_index=ON
# InnoDB 文件格式
innodb_file_per_table=ON
# InnoDB 临时表空间
innodb_temp_data_file_path=ibtmp1:200M:autoextend
# 最大连接数
max_connections=5000
# 最大用户连接数
max_user_connections=4000
# 连接错误限制
max_connect_errors=100000
# 连接超时时间
wait_timeout=600
interactive_timeout=600
# 线程缓存大小
thread_cache_size=256
# 表缓存大小
table_open_cache=4000
table_definition_cache=2000
# 二进制日志大小
max_binlog_size=1G
log-bin=/data/mysql-8.0.37/binlog/mysql-bin
relay_log=/data/mysql-8.0.37/relaylog/mysql-relay-bin
# 二进制日志格式
binlog_format=ROW
# 二进制日志缓存大小
binlog_cache_size=1M
# 二进制日志过期时间
expire_logs_days=30
sync_binlog=1
# 慢查询日志
slow_query_log=1
long_query_time=10
log_output=FILE
slow_query_log_file=/data/mysql-8.0.37/log/mysql-slow.log
general_log = OFF
# 错误日志
log_error=/data/mysql-8.0.37/log/mysql-error.log
# 字符集
character_set_server=utf8mb4
# 时区
default_time_zone='+8:00'
log_timestamps=SYSTEM
# 大小写不敏感
lower_case_table_names=1
# 最大允许的数据包大小
max_allowed_packet=1G
# 临时表大小
tmp_table_size=64M
max_heap_table_size=64M
# 排序和连接缓冲区大小
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=4M
join_buffer_size=4M
# 事务隔离级别
transaction-isolation=READ-COMMITTED
# 日志信任函数创建者
log_bin_trust_function_creators=1

设置权限

chown mysql.mysql -R /data/mysql-8.0.37
chmod 755 -R /data/mysql-8.0.37

初始化数据库，初始化密码在/data/mysql-8.0.37/log/mysql-error.log路径

/data/mysql-8.0.37/bin/mysqld --initialize --user=mysql \
--basedir=/data/mysql-8.0.37 \
--datadir=/data/mysql-8.0.37/data \
--lower-case-table-names=1

创建mysqld systemd托管配置文件

cat > /etc/systemd/system/mysqld8.service << EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql-8.0.37/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

启动服务

systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld

创建环境变量

cat > /etc/profile.d/mysql.sh << 'EOF'
#!/bin/bash
MYSQL_HOME=/data/mysql-8.0.37
PATH=$PATH:$MYSQL_HOME/bin:$HOME/bin
EOF
bash

修改mysql密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

恢复主从

用于从节点故障重新部署数据库恢复主从集群。

在主节点备份数据库数据

mysqldump --all-databases \
          --single-transaction \
          --master-data=2 \
          --routines \
          --triggers \
          --events \
          -u root -p > full_backup.sql

恢复数据库

mysql -u root -p < full_backup.sql

从备份文件获取binlog信息

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=12345;

创建主从

-- 停止可能存在的复制
STOP SLAVE;

-- 配置主节点信息（替换 IP、用户、密码及 binlog 信息）
CHANGE MASTER TO
  MASTER_HOST='主节点IP',
  MASTER_USER='repl',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=12345;

-- 启动复制
START SLAVE;

查看主从状态

show slave status\G;

数据恢复

[ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata1 are 0x4000!

解决方式

获取表结构数据

/data/mysql-8.0.37/bin/ibd2sdi access_info.ibd > /tmp/access_info.json

根据生成的json文件创建表，卸载新表的表空间

ALTER TABLE access_info DISCARD TABLESPACE;

将.ibd文件复制到新数据库目录

cp access_info.ibd /data/mysql-8.0.37/data/network_llm/
chown -R mysql:mysql /data/mysql-8.0.37/data/network_llm/

导入表空间

ALTER TABLE access_info IMPORT TABLESPACE;

报错：

ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.)

TestDisk 7.3-WIP, Data Recovery Utility, September 2024
Christophe GRENIER <grenier@cgsecurity.org>
https://www.cgsecurity.org


TestDisk is free data recovery software designed to help recover lost
partitions and/or make non-booting disks bootable again when these symptoms
are caused by faulty software, certain types of viruses or human error.
It can also be used to repair some filesystem errors.

Information gathered during TestDisk use can be recorded for later
review. If you choose to create the text file, testdisk.log , it
will contain TestDisk options, technical information and various
outputs; including any folder/file names TestDisk was used to find and
list onscreen.

Use arrow keys to select, then press Enter key:
>[ Create ] Create a new log file
 [ Append ] Append information to log file
 [ No Log ] Don't record anything





TestDisk 7.3-WIP, Data Recovery Utility, September 2024
Christophe GRENIER <grenier@cgsecurity.org>
https://www.cgsecurity.org


Disk /dev/mapper/datavg-datalv - 11 TB / 10 TiB

Please select the partition table type, press Enter when done.
 [Intel  ] Intel/PC partition
 [EFI GPT] EFI GPT partition map (Mac i386, some x86_64...)
 [Humax  ] Humax partition table
 [Mac    ] Apple partition map (legacy)
 [None   ] Non partitioned media
>[Sun    ] Sun Solaris partition
 [XBox   ] XBox partition
 [Return ] Return to disk selection



Hint: None partition table type has been detected.