一、基礎連接與庫表操作
-- 連接
mysql -h host -u user -p
-- 庫操作
CREATE DATABASE dbname;
USE dbname;
DROP DATABASE dbname;
SHOW DATABASES;
-- 表操作
SHOW TABLES;
DESCRIBE tbl_name; -- 結構
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(20));
DROP TABLE t;
ALTER TABLE t1 RENAME t2; -- 重命名
二、查詢技巧
SELECT VERSION(), CURRENT_DATE; -- 版本與日期
SELECT DISTINCT col FROM t; -- 去重
SELECT * FROM t WHERE species='cat'; -- 條件查詢
三、用戶授權體系(GRANT/REVOKE)
創建超級管理員
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'StrongPass!' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'StrongPass!' WITH GRANT OPTION;
創建業務專用賬號(最小權限)
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPass123!';
撤銷權限並刪除用戶
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';
DELETE FROM mysql.user WHERE User='root' AND Host='%';
FLUSH PRIVILEGES;
四、密碼修改
-- mysqladmin(命令行)
mysqladmin -u root -p'oldpass' password 'newpass'
-- SQL 語句
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
-- 或
UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;
五、mysqldump 備份與恢復
完整備份(含結構+數據)
mysqldump -h host -u root -p dbname > backup.sql
僅導出結構(-d / --no-data)
mysqldump -u root -p -d dbname > schema.sql
僅導出數據(-t / --no-create-info)
mysqldump -u root -p -t dbname > data.sql
導出純文本數據(-T,生成 .sql 建表 + .txt 數據)
mysqldump -T ./ dbname tbl_name
恢復數據庫
mysqladmin -h host -u root -p create dbname
mysql -h host -u root -p dbname < backup.sql
六、從文件批量執行 SQL
mysql -h host -u root -p dbname < script.sql
七、修改 root 密碼驗證流程
mysqladmin -u root password 'newpass'
- 無密碼登入報錯 → 成功
- 新密碼登入成功 → 確認生效
八、啟動/停止/自啟動
/etc/init.d/mysql start|stop|restart
mysqladmin -u root -p shutdown
chkconfig --add mysql; chkconfig --level 345 mysql on
九、遷移數據目錄(如 /var/lib/mysql → /home/data/mysql)
- 停止服務
mv /var/lib/mysql /home/data/
- 修改
/etc/my.cnf:socket=/home/data/mysql/mysql.sock
- 修改
/etc/init.d/mysql:datadir=/home/data/mysql
- 重啟驗證
十、授權遠程 root 登入(謹慎使用)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'StrongPass!' WITH GRANT OPTION;
FLUSH PRIVILEGES;