# 啟動(dòng)MySQL
systemctl start mysqld
# 防火墻開(kāi)放3306端口
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

# 1. 編輯配置文件
vi /etc/my.cnf
# [mysqld]下添加
skip-grant-tables=on
# 2. 重啟MySQL
systemctl restart mysqld
# 3. 無(wú)密碼登錄并重置密碼
mysql
mysql> UPDATE mysql.user SET authentication_string='' WHERE user='root' AND Host='localhost';
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '新密碼';
# 4. 刪除skip-grant-tables，重啟MySQL
systemctl restart mysqld
# 5. 創(chuàng)建遠(yuǎn)程用戶并授權(quán)
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '密碼';
mysql> GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

vi /etc/my.cnf
# [mysqld]下添加
skip-name-resolve=on
systemctl restart mysqld

# 命令行修復(fù)MyISAM表
myisamchk -r /var/lib/mysql/庫(kù)名/表名.MYI
# SQL修復(fù)（InnoDB/MyISAM通用）
mysql> REPAIR TABLE 表名;

# 臨時(shí)解除阻塞
mysqladmin -uroot -p flush-hosts
# 永久配置
vi /etc/my.cnf
max_connect_errors=1000
systemctl restart mysqld

# 臨時(shí)修改（重啟失效）
mysql> SET GLOBAL max_connections=10000;
# 永久修改
vi /etc/my.cnf
max_connections=10000
systemctl restart mysqld

chmod 644 /etc/my.cnf
chown mysql:mysql /etc/my.cnf

vi /etc/my.cnf
# [mysqld]下添加
innodb_force_recovery=4
# 啟動(dòng)后備份數(shù)據(jù)，再刪除參數(shù)重啟
systemctl start mysqld
mysqldump -uroot -p --all-databases > all_backup.sql

# 從庫(kù)修改配置
vi /etc/my.cnf
server-id=2  # 主從必須不同
systemctl restart mysqld
# 重啟同步
mysql> STOP SLAVE;
mysql> START SLAVE;

# 跳過(guò)1條錯(cuò)誤SQL
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> START SLAVE;
# 從庫(kù)設(shè)為只讀（防止數(shù)據(jù)篡改）
mysql> SET GLOBAL read_only=ON;

# 重新指定主庫(kù)binlog和pos點(diǎn)
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO 
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
mysql> START SLAVE;

[mysqld]
# 基礎(chǔ)配置
user=mysql
port=3306
datadir=/var/lib/mysql
socket=/data/mysql/mysql.sock
skip-name-resolve=on
default-storage-engine=InnoDB
character-set-server=utf8mb4
# 連接與線程
max_connections=1000
thread_cache_size=100
max_connect_errors=1000
# InnoDB核心配置
innodb_buffer_pool_size=40G
innodb_log_file_size=2G
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_thread_concurrency=0
innodb_autoinc_lock_mode=2
# 臨時(shí)表與排序
tmp_table_size=128M
max_heap_table_size=128M
sort_buffer_size=4M
join_buffer_size=8M
# 日志配置
slow_query_log=ON
long_query_time=1
log_error=/var/log/mysql/error.log
binlog_format=ROW
expire_logs_days=7

-- 創(chuàng)建庫(kù)
CREATE DATABASE test_db;
USE test_db;
-- 創(chuàng)建用戶表
CREATE TABLE user_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age TINYINT NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 插入10萬(wàn)測(cè)試數(shù)據(jù)
DELIMITER $$
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100000 DO
        INSERT INTO user_info(username, phone, age)
        VALUES (CONCAT('test_',i), CONCAT('1380000',FLOOR(RAND()*10000)), FLOOR(RAND()*50)+18);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
-- 執(zhí)行存儲(chǔ)過(guò)程
CALL insert_data();

-- 未加索引，全表掃描
EXPLAIN SELECT * FROM user_info WHERE username='test_1234';
-- 添加索引
ALTER TABLE user_info ADD INDEX idx_username(username);
-- 加索引后，索引查找
EXPLAIN SELECT * FROM user_info WHERE username='test_1234';