一些常用的MySQL命令,个人工作中使用频率高的,既长又易忘。


创建数据库:

1
2
3
mysql> create database if not exists <db_name> --- 数据库名称
default charset utf8 --- 字符编码
collate utf8_general_ci; --- 字符编码集

修改root密码:

1
2
mysql> set password for 'root'@'localhost'=password('newpass');
mysql> update user set password=password('newpass') where user='root'

用户权限:

添加并授权新用户:

1
2
3
mysql> grant all on testDB.* to test@localhost identified by '1234';
mysql> grant select,insert,update,delete,execute
on *.* to qmsys@"localhost" Identified by "qmsys";

刷新权限:

1
mysql> flush privileges;

查看权限:

1
mysql> show grants for '<user_name>'@'%’;

删除权限:

1
mysql> REVOKE ALL PRIVILEGES ON *.* FROM '<user_name>'@'%';


Event调度器:

查看Event:

1
2
mysql> show events;
mysql> show create event <name>;

开启Event:

1
mysql> SET GLOBAL event_scheduler = ON;

或在my.cnf的[mysqld]下添加event_scheduler = ON

创建Event:

1
2
3
4
5
6
7
8
9
10
delimiter|
CREATE DEFINER='root';@'%' EVENT 'update_reform'
ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(current_date(), '00:30:00')
ON COMPLETION PRESERVE ENABLE DO
BEGIN
/*
*
*/
END
|delimiter ;


常见易忘函数:

时间加减:

1
mysql> date_add(tc.check_time, interval 3 month);

day、hour、minute、second、microsecond、week、quarter、year


导出数据库:

指定数据库全量导出:

1
mysql> mysqldump -u root --password=<password> --events -R <db_name> > /root/<db_name>.$(date '+%Y%m%d').sql --lock-tables=false