# 檢查網(wǎng)絡(luò)連通性
nc -zv host 3306
mtr host
# 檢查防火墻
iptables -L -n | grep 3306
# 驗證連接數(shù)限制
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

-- 創(chuàng)建傳統(tǒng)認(rèn)證用戶
CREATE USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
-- 臨時降低密碼強度
SET GLOBAL validate_password_policy=LOW;

-- 查找高消耗SQL
SELECT * FROM sys.processlist WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;
-- 使用Performance Schema
SELECT * FROM performance_schema.threads WHERE PROCESSLIST_TIME > 60\G
-- 分析慢查詢
SHOW ENGINE INNODB STATUS;

-- 添加復(fù)合索引
ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);
-- 重寫查詢語句
SELECT /*+ INDEX(idx_customer_status) */ * FROM orders 
WHERE customer_id=123 AND status IN ('shipped','completed');

# 安裝校驗工具
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.0/binary/tarball/percona-toolkit-3.5.0_x86_64.tar.gz
# 數(shù)據(jù)一致性校驗
pt-table-checksum -h master -u user -p pass --databases mydb

# my.cnf 優(yōu)化
[mysqld]
slave_parallel_workers = 8
slave_pending_jobs_size_max = 2G
innodb_flush_log_at_trx_commit = 0
sync_binlog = 1000

# 停止MySQL服務(wù)
systemctl stop mysqld
# 使用mysqlbinlog恢復(fù)
mysqlbinlog --start-position=107 /var/log/mysql-bin.000001 | mysql -uroot -p
# 使用延時從庫恢復(fù)
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY = 3600;
START SLAVE;

-- 從備份恢復(fù)單分區(qū)
ALTER TABLE logs IMPORT PARTITION p202301 
    FROM '/backup/202301_partition.ibd';

# 檢查SSH互信
masterha_check_ssh --conf=/etc/mha/app1.cnf
# 檢查復(fù)制健康
masterha_check_repl --conf=/etc/mha/app1.cnf
# 查看管理日志
tail -f /var/log/masterha/app1/manager.log

-- 強制重啟集群
dba.rebootClusterFromCompleteOutage('cluster1');
-- 人工重新組集群
SELECT * FROM performance_schema.replication_group_members;

# 強制恢復(fù)模式啟動
innodb_force_recovery = 6
# 導(dǎo)出數(shù)據(jù)
mysqldump -uroot -p --all-databases > full_backup.sql
# 重建數(shù)據(jù)庫
mysql_install_db --user=mysql
systemctl start mysqld
mysql -uroot -p < full_backup.sql

# my.cnf內(nèi)存優(yōu)化
[mysqld]
innodb_buffer_pool_size=64G
key_buffer_size=0
query_cache_size=0
table_open_cache=20000

-- 查找異常賬號
SELECT * FROM mysql.user WHERE authentication_string='' \G
-- 檢查數(shù)據(jù)庫文件權(quán)限
ls -l /var/lib/mysql
-- 審計可疑操作
mysqlbinlog /var/log/mysql-bin.000007 | grep -i 'ALTER\|CREATE\|DROP'

# Xtrabackup部分備份
xtrabackup --backup --databases="db1 db2" --target-dir=/backup/partial
# mysqldump分片備份
mysqldump -uroot -p db1 | split -b 2G - db1_part_

-- 開啟兼容SQL模式
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
-- 移除廢棄功能
ALTER TABLE mytable ROW_FORMAT=DYNAMIC;

# 安全模式啟動，高版本中不可用
mysqld_safe --skip-grant-tables --skip-networking &
# 重置配置
SET GLOBAL max_connections=100;
FLUSH PRIVILEGES;