您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

Linux 下 MySQL 從入門到實戰：連線、帳號、DDL/DML、匯入匯出完整操作手冊

編輯：MySQL 資料庫相關知識

一、連線 MySQL 伺服器

# 本地連線 (Socket)
mysql -u root -p

# 遠程連線 (TCP)
mysql -h 192.168.1.100 -u root -p'password' -P 3306

# 退出
exit / quit / \q

提示符 mysql> 表示進入互動模式。

二、密碼管理與帳號權限體系

修改密碼

# 5.7.6+ / 8.0 官方推薦
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStr0ngP@ss!';
FLUSH PRIVILEGES;

# 相容舊版
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewStr0ngP@ss!');
-- 或
UPDATE mysql.user SET authentication_string=PASSWORD('NewStr0ngP@ss!') WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

建立應用帳號（最小權限原則）

-- 僅允許特定 IP、特定庫、特定權限
CREATE USER 'app_user'@'10.0.%' IDENTIFIED BY 'AppP@ssw0rd!';
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'app_user'@'10.0.%';
FLUSH PRIVILEGES;

-- 僅本地存取（配合應用同機部署）
CREATE USER 'local_user'@'localhost' IDENTIFIED BY 'LocalP@ss!';
GRANT ALL PRIVILEGES ON `mydb`.* TO 'local_user'@'localhost';
FLUSH PRIVILEGES;

移除匿名帳號與測試庫（安全基線）

DELETE FROM mysql.user WHERE User='';
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

三、資料庫與資料表 DDL 操作

-- 建庫 (指定字元集排序規則)
CREATE DATABASE `school` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 切換庫
USE `school`;

-- 建表 (範例：教師表)
CREATE TABLE `teacher` (
  `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` CHAR(10) NOT NULL COMMENT '姓名',
  `address` VARCHAR(50) DEFAULT '深圳' COMMENT '地址',
  `year` DATE COMMENT '入職年份',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教師資訊表';

-- 修改結構
ALTER TABLE `teacher` ADD COLUMN `phone` VARCHAR(11) COMMENT '手機' AFTER `name`;
ALTER TABLE `teacher` CHANGE `address` `addr` VARCHAR(100) DEFAULT '深圳';
ALTER TABLE `teacher` DROP COLUMN `year`;

-- 刪表/刪庫
DROP TABLE `teacher`;
DROP DATABASE `school`;

四、資料操作 DML 基礎

-- 插入
INSERT INTO `teacher` (`name`, `address`) VALUES ('張三', '北京');
INSERT INTO `teacher` (`name`, `address`) VALUES ('李四', '上海'), ('王五', '廣州');

-- 查詢
SELECT * FROM `teacher`;
SELECT `id`, `name` FROM `teacher` WHERE `address` = '北京';
SELECT `address`, COUNT(*) AS cnt FROM `teacher` GROUP BY `address`;

-- 更新
UPDATE `teacher` SET `address` = '杭州' WHERE `name` = '張三';

-- 刪除 (慎用無 WHERE)
DELETE FROM `teacher` WHERE `id` = 1;
TRUNCATE TABLE `teacher`;  -- 快速清空、重置自增、不觸發 Trigger

五、資料匯出與匯入（備份遷移核心技能）

mysqldump 邏輯備份

# 單庫
mysqldump -u root -p --single-transaction --routines --triggers --events `mydb` > mydb_$(date +%F).sql

# 全庫 (含系統帳號)
mysqldump -u root -p --all-databases --single-transaction --master-data=2 > full_$(date +%F).sql

# 壓縮備份
mysqldump -u root -p `mydb` | gzip > mydb_$(date +%F).sql.gz

# 僅結構
mysqldump -u root -p -d `mydb` > schema_only.sql

關鍵參數：--single-transaction (InnoDB 一致性備份不鎖表)、--master-data=2 (記錄 Binlog 位點、利於從庫搭建)。

還原

# 還原單庫
mysql -u root -p `mydb` < mydb_2024-01-15.sql

# 還原全庫 (需較高權限)
mysql -u root -p < full_2024-01-15.sql

# 還原壓縮檔
gunzip -c mydb_2024-01-15.sql.gz | mysql -u root -p `mydb`

物理備份 (Percona XtraBackup)——大資料量首選

# 安裝
yum install -y percona-xtrabackup-80

# 完整備份 (不鎖表、支援增量)
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=root --password='xxx'

# 準備 (回放 Redo Log 達一致性)
xtrabackup --prepare --target-dir=/backup/full_2024-01-15

# 還原 (需停庫、datadir 為空)
xtrabackup --copy-back --target-dir=/backup/full_2024-01-15
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

六、常用檢視與診斷指令

SHOW DATABASES;
SHOW TABLES;
DESC `teacher`;           -- 結構
SHOW CREATE TABLE `teacher`;  -- 建表語句
SHOW INDEX FROM `teacher`;    -- 索引詳情
SHOW PROCESSLIST;         -- 當前連線
SHOW ENGINE INNODB STATUS;    -- InnoDB 內部狀態
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

七、字元集最佳實踐：全鏈路 utf8mb4

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4