数据库基础之Mysql（1）常用命令

1 创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

比如

create user 'test_user'@'%' identified by 'test';

ps：如果只允许本机登录则host=localhost，如果允许从任意远程主机登陆则host=%

修改密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

2 授权

GRANT privileges ON dbname.tablename TO 'username'@'host'

比如

GRANT ALL ON test_db.* TO 'test_user'@'%';

撤销权限

REVOKE privilege ON dbname.tablename FROM 'username'@'host';

3 查看某个用户的授权

show grants for test_user;

4 数据导出

$ mysqldump -u dbuser -p dbname [tablename1] [tablename2] > dump.sql

ps：tablename可选

如果想导出所有database，dataname处传 --all-databases

更多参数：

--no-create-info　不导出建表语句

--skip-add-locks　跳过锁表语句

--where="..."　　只导出满足条件的数据

5 数据导入

mysql>source /path/dump.sql

或者

$ mysql -uusername -p dbname < dump.sql

6 定位问题

mysql>show processlist;

mysql>show variables;

 

mysql>show index from $db.$table;

 

mysql>select @@profiling;

mysql>set profiling=1;

mysql>select * from $table;

mysql>show profiles;

mysql>show profile all for query $query_id;

mysql>set profiling=0;

7 修改字符集

mysql> alter database $database_name character set utf8;
mysql> alter table $table_name default character set utf8;
mysql> alter table $table_name change $column_name $column_name varchar(50) character utf8;