一创建普通用户并授权
1 创建用户并授权
- [root@zlinux ~]# mysql -uroot -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 1
- Server version: 5.6.36 MySQL Community Server (GPL)
- Copyright (c) 2000, 2017, 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> grant all on *.* to user01 identified by 123456
- -> ; // 上一行命令忘记输入;, 这里可以输入后继续执行
- Query OK, 0 rows affected (0.01 sec)
创建 user 用户并授予其所有权限 *.*(第一个 * 表示所有数据库, 第二个 * 表示所有表)
这里的 user01 特指 localhost 上的 user01
identified by : 设定密码, 用单引号括起来
2 给网络上其他机器某个用户授权
- mysql> grant all on *.* to user02@127.0.0.1 identified by 123456; // 指定 IP, 即只可通过此 IP 登录, 用户和主机之间有个 @可以使用通配符 %, 代表所有 IP(一般不使用)
- Query OK, 0 rows affected (0.00 sec)
- mysql> quit
- Bye
- [root@zlinux ~]# mysql -uuser02 -p123456 // 未连接到指定 IP, 登录报错
- Warning: Using a password on the command line interface can be insecure.
- ERROR 1045 (28000): Access denied for user user02@localhost (using password: YES)
- [root@zlinux ~]# mysql -uuser02 -p123456 -h127.0.0.1 // 指定 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 9
- Server version: 5.6.36 MySQL Community Server (GPL)
- Copyright (c) 2000, 2017, 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>
3 查看用户授权
- mysql> show grants // 查看当前用户授权
- -> ;
- +----------------------------------------------------------------------------------------------------------------------------------------+
- | Grants for root@localhost |
- +----------------------------------------------------------------------------------------------------------------------------------------+
- | GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY PASSWORD *A89494294B2411291D21451D05BAB332A65AAB5D WITH GRANT OPTION |
- | GRANT PROXY ON @ TO root@localhost WITH GRANT OPTION |
- +----------------------------------------------------------------------------------------------------------------------------------------+
- 2 rows in set (0.00 sec)
- mysql> show grants for user02@127.0.0.1; // 查看指定用户授权
- +------------------------------------------------------------------------------------------------------------------------+
- | Grants for user02@127.0.0.1 |
- +------------------------------------------------------------------------------------------------------------------------+
- | GRANT ALL PRIVILEGES ON *.* TO user02@127.0.0.1 IDENTIFIED BY PASSWORD *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
- +------------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
4 授权部分权限 (如读写查询插入等)
- mysql> grant SELECT, INSERT, UPDATE *.* user03 identified by 123456;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near *.* user03 identified by 123456 at line 1
- mysql> grant SELECT, INSERT, UPDATE on *.* to user03 identified by 123456;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show grants for user03
- -> ;
- +------------------------------------------------------------------------------------------------------------------------+
- | Grants for user03@% |
- +------------------------------------------------------------------------------------------------------------------------+
- | GRANT SELECT, INSERT, UPDATE ON *.* TO user03@% IDENTIFIED BY PASSWORD *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
- +------------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
二常用 SQL 语句
- select count( * ) from mysql.user; // 查询 mysql 库中 user 表的行数
- select * from mysql.db; // 查询 mysql 库中 db 表的所有内容
- select db from mysql.db; // 查询 mysql 库中 db 表的 db 字段
- select db,
user from mysql.db; 查询 mysql 库中 db 表的 db 和 user 字段 select * from mysql.db where host like192.168. % ; // 查询 mysql 库中 db 表中匹配 192.128. 开头的内容
- insert into db1.t1 values(1, abc); // 在 db1 数据库的 t1 表中插入对应 内容
- update db1.t1 set name = aaawhere id = 1; // 更改 db1 数据库的 t1 表中 name 列内容, 当 id 是 1 的时候
- truncate table db1.t1; // 清除表内数据
- drop table db1.t1; // 删除表
- drop database db1; // 删除数据库
三 MySQL 数据库备份与恢复
- mysql> create database db1; // 创建库
- Query OK, 1 row affected (0.01 sec)
- mysql> use db1; // 切换库
- Database changed
- mysql> create table t1 (`id` int(4),`name` char(40)); // 创建表及字段
- Query OK, 0 rows affected (0.02 sec)
- mysql> show tables;
- +---------------+
- | Tables_in_db1 |
- +---------------+
- | t1 |
- +---------------+
- 1 row in set (0.00 sec)
- mysql> select * from t1;
- Empty set (0.00 sec)
- mysql> quit
- Bye
- [root@zlinux ~]# mysqldump -uroot -pzlinux123456 mysql>/tmp/mysql.sql // 备份
- Warning: Using a password on the command line interface can be insecure.
- [root@zlinux ~]# ls /tmp/mysql.sql
- /tmp/mysql.sql
- [root@zlinux ~]# mysql -uroot -pzlinux123456 db2 </tmp/mysql.sql // 恢复
- Warning: Using a password on the command line interface can be insecure.
- [root@zlinux ~]# mysql -uroot -pzlinux123456
- 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 29
- Server version: 5.6.36 MySQL Community Server (GPL)
- Copyright (c) 2000, 2017, 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> use db2 // 切换到 db2, 查看是否恢复成功
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> show tables;
- +---------------------------+
- | Tables_in_db2 |
- +---------------------------+
- | columns_priv |
- | db |
- | event |
- | func |
- | general_log |
- | help_category |
- | help_keyword |
- | help_relation |
- | help_topic |
- | innodb_index_stats |
- | innodb_table_stats |
- | ndb_binlog_index |
- | plugin |
- | proc |
- | procs_priv |
- | proxies_priv |
- | servers |
- | slave_master_info |
- | slave_relay_log_info |
- | slave_worker_info |
- | slow_log |
- | t1 |
- | tables_priv |
- | time_zone |
- | time_zone_leap_second |
- | time_zone_name |
- | time_zone_transition |
- | time_zone_transition_type |
- | user |
- +---------------------------+
- 29 rows in set (0.01 sec)
- mysql> quit
- Bye
- [root@zlinux ~]# mysqldump -uroot -pzlinux123456 mysql user> /tmp/mysqluser.sql // 备份 mysql 库 user 表
- Warning: Using a password on the command line interface can be insecure.
- [root@zlinux ~]# mysql -uroot -pzlinux123456 db1 </tmp/mysqluser.sql // 恢复到 db1
- Warning: Using a password on the command line interface can be insecure.
- [root@zlinux ~]# mysql -uroot -pzlinux123456
- 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 32
- Server version: 5.6.36 MySQL Community Server (GPL)
- Copyright (c) 2000, 2017, 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> use db1 // 查看是否成功恢复
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> show tables;
- +---------------+
- | Tables_in_db1 |
- +---------------+
- | t1 |
- | user |
- +---------------+
- 2 rows in set (0.00 sec)
- mysql>
来源: http://www.bubuko.com/infodetail-2536672.html