部署

创建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]
lower_case_table_names=1
expire_logs_days = 7
max_connections = 10240
wait_timeout = 600
interactive_timeout = 600
basedir=/data/mysql-8.0.37
datadir=/data/mysql-8.0.37/data
server-id=120
sync-binlog=1
log_bin=master-bin-120
log_bin_index=master-bin.index
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
relay_log_index=master_relay_bin.index
relay_log=master_relay_bin
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled=1
loose_rpl_semi_sync_slave_enabled=1
loose_rpl_semi_sync_master_timeout=5000

设置权限

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.