MySQL常用操作

1.mysql用户登录： mysql>mysql -u user -p 回车后输入密码

2.新建mysql用户： mysql>insert into mysql.user(Host,User,Password) values("localhost","xiaoming",password("1234"));

3.用户授权 mysql>grant all privileges/select/update/delete on dataname.tablename to username@localhost identified by 'password';

                  mysql>flush privileges;

4.取消用户所有权限 ：mysql>revoke all privileges on *.* from user@host;

5.删除用户 :mysql>DELETE FROM user WHERE User="username" and Host="localhost";
               mysql>flush privileges;

6.修改用户密码: mysql> update mysql.user set password=password('pwd') where User="xiaoming" and Host="localhost";

                    mysql>flush privileges;

7.查看当前用户数据库操作权限: mysql>show grants；
   查看其它用户数据库操作状态权限: mysql>show grants for user@host;

8.导出数据库 mysqldump -u root -ppassword 库名>c:\data.sql；-p和密码中间无空格

9.导入数据库mysql>source 文件路径;

10.查看mysql环境配置:mysql>show variables;

11.开启二进日志,在my.ini添加：log-bin = E:/wamp/bin/mysql/mysql5.5.8/log/binlog.log;具体路径根据mysql安装路径选择。

12.开启二进制索引，在my.ini添加：log-bin-index = E:/wamp/bin/mysql/mysql5.5.8/log/binlog.index;