萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 常用命令大全:庫表操作、用戶授權、備份恢復、數據導入導出速查手冊

一、基礎連接與庫表操作

-- 連接
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 密碼驗證流程

  1. mysqladmin -u root password 'newpass'
  2. 無密碼登入報錯 → 成功
  3. 新密碼登入成功 → 確認生效

八、啟動/停止/自啟動


/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)

  1. 停止服務
  2. mv /var/lib/mysql /home/data/
  3. 修改 /etc/my.cnf:socket=/home/data/mysql/mysql.sock
  4. 修改 /etc/init.d/mysql:datadir=/home/data/mysql
  5. 重啟驗證

十、授權遠程 root 登入(謹慎使用)


GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'StrongPass!' WITH GRANT OPTION;
FLUSH PRIVILEGES;
copyright © 萬盛學電腦網 all rights reserved