uname -a # 內核/發行版
cat /etc/issue # 發行版詳情
/lib/libc.so.6 # glibc 版本/usr/local/mysql-5.6.xx，建軟連結 /usr/local/mysql。/data/mysql3306/{data,tmp,run,log}、/log/mysql3306/{iblog,binlog}。my.cnf (見文末完整示例)，關鍵：port、socket、datadir、innodb_log_group_home_dir、log-bin、relay-log 等分目錄。mysql_install_db --defaults-file=... --basedir=/usr/local/mysql --datadir=...mysqld_safe --defaults-file=... &mysql -h127.0.0.1 -uroot -P3306 -p# init3306.sh
rm -rf /data/mysql3306/data/* /log/mysql3306/iblog/* /log/mysql3306/binlog/*
chmod -R 777 /data/mysql3306/data /log/mysql3306/iblog /log/mysql3306/binlog
mysql_install_db --defaults-file=/data/mysql3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3306/data
# start3306.sh
mysqld_safe --defaults-file=/data/mysql3306/my.cnf &
# my3306.sh
mysql -h127.0.0.1 -uroot -P3306 -p-- 創建用戶 (推薦 5.7+ 語法)
CREATE USER 'app'@'%' IDENTIFIED BY 'strong_pass';
-- 授權
GRANT SELECT,INSERT,UPDATE,DELETE ON app_db.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION; -- 超級用戶
GRANT SUPER ON *.* TO 'repl'@'%'; -- 複制/管理權限
FLUSH PRIVILEGES;id INT PRIMARY KEY AUTO_INCREMENT，業務建議自定義主鍵 (雪花 ID/UUID) 避免全局順序爭用。ALTER TABLE t ADD col INT; CREATE INDEX idx_col ON t(col); 會拷貝表，大表慎用 (考慮 pt-online-schema-change)。EXPLAIN SELECT ... 看 type、key、rows、Extra。| 方式 | 適用場景 | 命令/工具 |
|---|---|---|
| mysqldump | 小/中表、邏輯備份、跨版本 | mysqldump -h... -u... -p db tbl > /tmp/tbl.sql |
| SELECT INTO OUTFILE | 大表導出文本 | SELECT * FROM t INTO OUTFILE '/tmp/t.txt'; |
| 物理備份 (停機) | 大實例、追求速度 | 停庫 → 拷貝 datadir → 啟庫 |
| 主從搭建遷移 (不停機) | 生產環境零停機 | 建 Slave → 同步追平 → 切主 → 應用切換 |
| 異構遷移 (Oracle→MySQL) | 跨平台 | 全量導出 + 增量同步 (觸發器/物化視圖) + 鎖表切換 |
RESET MASTER; -- 清空所有 (慎用)
SHOW BINLOG EVENTS; -- 查看事件
FLUSH LOGS; -- 切換新 Binlog (歸檔)
mysqlbinlog -vvv binlog.000001 > /tmp/binlog.sql -- 解析SHOW VARIABLES; -- 所有參數
SHOW VARIABLES LIKE '%bin%';
SHOW STATUS; -- 統計信息
SHOW GLOBAL STATUS LIKE '%insert%';[client]
port=3306
socket=/data/mysql3306/run/mysql.sock
[mysql]
port=3306
prompt=\\u@\\d \\r:\\m:\\s>
[mysqld]
default-storage-engine=INNODB
character-set-server=latin1
explicit_defaults_for_timestamp=true
# 目錄分離
innodb_log_group_home_dir=/log/mysql3306/iblog
innodb_data_home_dir=/log/mysql3306/iblog
basedir=/usr/local/mysql
datadir=/data/mysql3306/data
tmpdir=/data/mysql3306/tmp
slave_load_tmpdir=/data/mysql3306/tmp
log-error=/data/mysql3306/log/alert.log
slow_query_log_file=/data/mysql3306/log/slow.log
relay_log_info_file=/log/mysql3306/binlog/relay-log.info
master_info_file=/log/mysql3306/binlog/master.info
socket=/data/mysql3306/run/mysql.sock
log-bin=/log/mysql3306/binlog/binlog
relay-log=/log/mysql3306/binlog/relaylog
innodb_force_recovery=0應用端連接池關鍵參數：max_connect、min_connect、max_idle、timeout，用完必須 close() 歸還，防洩漏。
