前言
記錄mysql常用命令操作
基礎(chǔ)操作
- 命令行登錄mysql
mysql -u用戶名 -p用戶密碼
- 為表增加創(chuàng)建時間和更新時間
ALTER TABLE order_info_tbl ADD COLUMN create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間';
ALTER TABLE order_info_tbl ADD COLUMN update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間'
修改密碼
- 普通
update user set password=password("root1234") where user="root";
- 帶插件
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY ''root;
表分區(qū)
- 數(shù)據(jù)按照31個省份分區(qū)
ALTER TABLE tache_stat_tbl_20190120 PARTITION BY HASH(province) PARTITIONS 31
表茧彤、索引、執(zhí)行計劃
- 表空間的情況查看(指定數(shù)據(jù)庫)
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables
# where TABLE_SCHEMA = 'yourdb'
group by TABLE_NAME
order by data_length desc;
- 索引的建立
盡量避免廉價的建立索引星爪，可以先根據(jù)數(shù)據(jù)區(qū)分度來判斷浆西，是否有必要建立索引。
select count(distinct 將要建立索引的字段) / count(*)
- 執(zhí)行計劃的extra的幾種類型解讀
Using index
表示使用了覆蓋索引（Covering Index）
Using where
Using where的作用提示了用where來過濾結(jié)果集顽腾。
Using temporary
說明MySQL需要使用臨時表來存儲結(jié)果集近零，常見于排序和分組查詢
Using filesort
MySQL中無法利用索引完成的排序操作稱為“文件排序”
常用維護操作
- 查詢執(zhí)行時間超過2分鐘的線程，然后拼接成 kill 語句
select concat('kill ', id, ';') from information_schema.processlist where command != 'Sleep' and time > 2*60 order by time desc
- 為用戶授予所有權(quán)限
GRANT ALL PRIVILEGES ON *.* TO 'YourUserName'@'%' IDENTIFIED BY "YourPassword";
數(shù)據(jù)導入導出
- 導出包括系統(tǒng)庫在內(nèi)的所有數(shù)據(jù)庫數(shù)據(jù)
mysqldump -uroot -proot --all-databases >/all.sql
- 只導出表結(jié)構(gòu)抄肖，不導出數(shù)據(jù)
mysqldump -uroot -proot --no-data --databases db1 > /table_with_no_data.sql
- 跨服務器導出導入數(shù)據(jù),目標數(shù)據(jù)庫必須存在秒赤，否則會報錯
mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2
- 導出數(shù)據(jù)時報mysql.sock錯的幾種解決方案
默認情況下，連接協(xié)議為socket憎瘸，如遇到下述錯誤入篮，可以嘗試更換協(xié)議。
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock'
方案一：重啟數(shù)據(jù)庫會重新創(chuàng)建mysql.sock幌甘。
方案二：若暫時無法重啟數(shù)據(jù)庫潮售，可以通過TCP協(xié)議連接數(shù)據(jù)庫痊项。
--protocol=name The protocol to use for connection (tcp, socket, pipe,memory).
樣例語句：
mysqldump -h127.0.0.1 -uroot -proot --protocol=TCP --database db1
--tables conf_area_tbl conf_app_tbl > 1.sql
- 導出存儲過程和自定義函數(shù)
mysqldump -uroot -p --host=localhost --all-databases --routines
- 終端執(zhí)行sql，將結(jié)果輸出到文件
mysql -uroot -e 'select * from cb_mon.t_book limit 10' > mytest.txt
- 使用存儲過程批量生成數(shù)據(jù)
DROP PROCEDURE if exists test_insert ;
DELIMITER ;;
CREATE PROCEDURE test_insert ()
BEGIN
DECLARE i INT DEFAULT 1;# can not be 0
WHILE i<1000
DO
insert into SS_BOOK values (i, CONCAT("00000",i) , CONCAT('book',i), 1, CONCAT('book_description',i));
SET i=i+1;
END WHILE ;
commit;
END;;
CALL test_insert();
