MySQL用户管理
创建一个普通用户并授权
mysql> grant all on *.* to 'ahao'@'127.0.0.1' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec)
用法解释说明：
● grant：授权；
● all:表示所有的权限（如读、写、查询、删除等操作）；
● *.*：前者表示所有的数据库,后者表示所有的表；
● identified by：后面跟密码，用单引号括起来；
● 'user1'@'127.0.0.1':指定IP才允许这个用户登录，这个IP可以使用%代替，表示允许所有主机使用这个用户登录;
● 其中主机ip可以用%替代，他表示所有ip，则localhost为本机
测试登入
[root@ahao-01 ~]# mysql -uahao -ppasswd -h127.0.0.1 //由于指定IP，所以需要加-h指定IP登录，正常 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> //授权localhost，所以该用户默认使用（监听）本地mysql.socket文件，不需要指定IP即可登录 mysql> grant all on *.* to 'ahao'@'localhost' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec)
3.查看所有授权
mysql> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
4.指定用户查看授权
mysql> show grants for ahao@127.0.0.1; +----------------------------------------------------------------------------------------------------------------------+ | Grants for ahao@127.0.0.1 | +----------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'ahao'@'127.0.0.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' | +----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
注意：假设你想给同个用户授权增加一台电脑IP授权访问，你就可以直接拷贝查询用户授权文件，复制先执行一条命令再执行第二条，执行的时候把IP更改掉,这样就可以使用同个用户密码在另外一台电脑上登录。
grant all on db1.* to 'user3'@'%' identified by 'passwd';
常用sql语句
1、查询mysql库中的user表，count（*）表示表中共有几行
mysql> select count(*) from mysql.user; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.01 sec)
2、查询mysql数据哭的db表中所有的数据
mysql> select * from mysql.db; +------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv | +------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y | | % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y | +------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ 2 rows in set (0.00 sec)
3、查询单个字段的数据
mysql> select db from mysql.db; +---------+ | db | +---------+ | test | | test\_% | +---------+ 2 rows in set (0.00 sec)
4、查询多个字段的数据
mysql> select db,user from mysql.db; +---------+------+ | db | user | +---------+------+ | test | | | test\_% | | +---------+------+ 2 rows in set (0.00 sec)
5、查询语句中也可以使用万能匹配符%如下图
mysql> select * from mysql.db where host like '192.168.%';
二、插入数据
mysql> insert into db1.t1 values (1, 'abc'); //插入一行数据 Query OK, 1 row affected (0.01 sec) //其中abc为字符串，必须要加引号，除了插入数字之外可以不加 mysql> select * from db1.t1; //查询表 +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec)
2.1、更改表的一行。
mysql> update db1.t1 set name='aaa' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | aaa | +------+------+ 1 row in set (0.00 sec)
2.2、清空某个表的数据
注意：truncate只是清空表里面的数据，而drop是整个表都删除包括字段 mysql> truncate table db1.t1; //清空表 Query OK, 0 rows affected (0.05 sec) mysql> select * from db1.t1; Empty set (0.00 sec)
2.3、删除表
mysql> drop table db1.t1; Query OK, 0 rows affected (0.00 sec) mysql> select * from db1.t1; ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
2.4、删除数据库
mysql> drop database db1; Query OK, 2 rows affected (0.02 sec)
MySQL数据库备份恢复
3.1、备份还原数据库
[root@ahao-01 ~]# mysqldump -uroot -p密码 mysql > /tmp/mysql.sql //备份库
[root@ahao-01 ~]# mysql -uroot -p密码 mysql2 < /tmp/mysql.sql //恢复一个库
3.2、创建库
[root@ahao-01 ~]# mysql -uroot -p密码 -e "create database mysql2" //创建一个库
3.3、备份还原表
[root@ahao-01 ~]# mysqldump -uroot -p密码 mysql user > /tmp/user.sql //备份某张表 [root@ahao-01 ~]# mysql -uroot -p密码 mysql < /tmp/user.sql //恢复某张表
3.4、备份所有库
[root@ahao-01 ~]# mysqldump -uroot -p -A >/tmp/quanbu.sql //备份所有库
3.5、备份表结构不要数据
[root@ahao-01 ~]# mysqldump -uroot -p密码 -d mysql > /tmp/mysql.sql
