前言
记录MySQL常用命令操作
基础操作
mysql -u用户名 -p用户密码
ALTER TABLE order_info_tbl ADD COLUMN create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'; 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;
表分区
ALTER TABLE tache_stat_tbl_20190120 PARTITION BY HASH(province) PARTITIONS 31
表、索引、执行计划
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;
尽量避免廉价的建立索引,可以先根据数据区分度来判断,是否有必要建立索引。
select count(distinct 将要建立索引的字段) / count(*)
Using index表示使用了覆盖索引(Covering Index)
Using whereUsing where的作用提示了用where来过滤结果集。
Using temporary说明MySQL需要使用临时表来存储结果集,常见于排序和分组查询
Using filesortMySQL中无法利用索引完成的排序操作称为“文件排序”
常用维护操作
select concat('kill ', id, ';') from information_schema.processlist where command != 'Sleep' and time > 2*60 order by time desc
GRANT ALL PRIVILEGES ON *.* TO 'YourUserName'@'%' IDENTIFIED BY "YourPassword";
数据导入导出
mysqldump -uroot -proot --all-databases >/all.sql
mysqldump -uroot -proot --no-data --databases db1 > /table_with_no_data.sql
mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2
默认情况下,连接协议为socket,如遇到下述错误,可以尝试更换协议。
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
方案一:重启数据库会重新创建mysql.sock。方案二:若暂时无法重启数据库,可以通过TCP协议连接数据库。--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
mysqldump -uroot -p --host=localhost --all-databases --routines
mysql -uroot -e 'select * from cb_mon.t_book limit 10' > mytest.txt
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();