查看: 1679|回复: 0

[Mysql数据库] MySQL用户管理、常用SQL语句、MySQL数据库备份恢复

发表于 2018-1-23 08:00:01
mysql用户管理 1.创建一个普通用户并授权
  1. [root@gary-tao ~]# mysql -uroot -p'szyino-123'
  2. Warning: Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id is 24
  5. Server version: 5.6.35 MySQL Community Server (GPL)
  6. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  7. Oracle is a registered trademark of Oracle Corporation and/or its
  8. affiliates. Other names may be trademarks of their respective
  9. owners.
  10. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  11. mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by 'szyino-123'; //创建一个普通用户并授权
  12. Query OK, 0 rows affected (0.00 sec)
复制代码
用法解释说明: grant:授权; all:表示所有的权限(如读、写、查询、删除等操作); .:前者表示所有的数据库,后者表示所有的表; identified by:后面跟密码,用单引号括起来; 'user1'@'127.0.0.1':指定IP才允许这个用户登录,这个IP可以使用%代替,表示允许所有主机使用这个用户登录; 2.测试登录
  1. [root@gary-tao ~]# mysql -uuser1 -pszyino-123 //由于指定IP,报错不能登录
  2. Warning: Using a password on the command line interface can be insecure.
  3. ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
  4. [root@gary-tao ~]# mysql -uuser1 -pszyino-123 -h127.0.0.1 //加-h指定IP登录,正常
  5. Warning: Using a password on the command line interface can be insecure.
  6. Welcome to the MySQL monitor. Commands end with ; or \g.
  7. Your MySQL connection id is 26
  8. Server version: 5.6.35 MySQL Community Server (GPL)
  9. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  10. Oracle is a registered trademark of Oracle Corporation and/or its
  11. affiliates. Other names may be trademarks of their respective
  12. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  14. mysql>
  15. mysql> grant all on *.* to 'user1'@'localhost' identified by 'szyino-123'; //授权localhost,所以该用户默认使用(监听)本地mysql.socket文件,不需要指定IP即可登录
  16. Query OK, 0 rows affected (0.00 sec)
  17. mysql> ^DBye
  18. [root@gary-tao ~]# mysql -uuser1 -pszyino-123 //正常登录
  19. Warning: Using a password on the command line interface can be insecure.
  20. Welcome to the MySQL monitor. Commands end with ; or \g.
  21. Your MySQL connection id is 28
  22. Server version: 5.6.35 MySQL Community Server (GPL)
  23. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  24. Oracle is a registered trademark of Oracle Corporation and/or its
  25. affiliates. Other names may be trademarks of their respective
  26. owners.
  27. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  28. mysql>
复制代码
3.查看所有授权
  1. mysql> show grants;
  2. +----------------------------------------------------------------------------------------------------------------------------------------+
  3. | Grants for root@localhost |
  4. +----------------------------------------------------------------------------------------------------------------------------------------+
  5. | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*B1E761CAD4A61F6FD6B02848B5973BC05DE1C315' WITH GRANT OPTION |
  6. | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
  7. +----------------------------------------------------------------------------------------------------------------------------------------+
  8. 2 rows in set (0.00 sec)
复制代码
4.指定用户查看授权
  1. mysql> show grants for user1@'127.0.0.1';
  2. +-----------------------------------------------------------------------------------------------------------------------+
  3. | Grants for user1@127.0.0.1 |
  4. +-----------------------------------------------------------------------------------------------------------------------+
  5. | GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*B1E761CAD4A61F6FD6B02848B5973BC05DE1C315' |
  6. +-----------------------------------------------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)
复制代码
注意:假设你想给同个用户授权增加一台电脑IP授权访问,你就可以直接拷贝查询用户授权文件,复制先执行一条命令再执行第二条,执行的时候把IP更改掉,这样就可以使用同个用户密码在另外一台电脑上登录。 常用sql语句 1.最常见的查询语句

第一种形式:

  1. mysql> use db1;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  4. Database changed
  5. mysql> select count(*) from mysql.user;
  6. +----------+
  7. | count(*) |
  8. +----------+
  9. | 8 |
  10. +----------+
  11. 1 row in set (0.00 sec)
  12. //注释:mysql.user表示mysql的user表,count(*)表示表中共有多少行。
复制代码

第二种形式:

  1. mysql> select * from mysql.db;
  2. //它表示查询mysql库的db表中的所有数据
  3. mysql> select db from mysql.db;
  4. +---------+
  5. | db |
  6. +---------+
  7. | test |
  8. | test\_% |
  9. +---------+
  10. 2 rows in set (0.00 sec)
  11. //查询db表里的db单个字段
  12. mysql> select db,user from mysql.db;
  13. +---------+------+
  14. | db | user |
  15. +---------+------+
  16. | test | |
  17. | test\_% | |
  18. +---------+------+
  19. 2 rows in set (0.00 sec)
  20. //查看db表里的db,user多个字段
  21. mysql> select * from mysql.db where host like '192.168.%'\G;
  22. //查询db表里关于192.168.段的ip信息
复制代码
2.插入一行
  1. mysql> desc db1.t1;
  2. +-------+----------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +-------+----------+------+-----+---------+-------+
  5. | id | int(4) | YES | | NULL | |
  6. | name | char(40) | YES | | NULL | |
  7. +-------+----------+------+-----+---------+-------+
  8. 2 rows in set (0.00 sec)
  9. mysql> select * from db1.t1;
  10. Empty set (0.00 sec)
  11. mysql> insert into db1.t1 values (1, 'abc'); //插入一行数据
  12. Query OK, 1 row affected (0.01 sec)
  13. mysql> select * from db1.t1;
  14. +------+------+
  15. | id | name |
  16. +------+------+
  17. | 1 | abc |
  18. +------+------+
  19. 1 row in set (0.00 sec)
  20. mysql> insert into db1.t1 values (1, '234');
  21. Query OK, 1 row affected (0.00 sec)
  22. mysql> select * from db1.t1;
  23. +------+------+
  24. | id | name |
  25. +------+------+
  26. | 1 | abc |
  27. | 1 | 234 |
  28. +------+------+
  29. 2 rows in set (0.00 sec)
复制代码
3.更改表的一行。
  1. mysql> update db1.t1 set name='aaa' where id=1;
  2. Query OK, 2 rows affected (0.01 sec)
  3. Rows matched: 2 Changed: 2 Warnings: 0
  4. mysql> select * from db1.t1;
  5. +------+------+
  6. | id | name |
  7. +------+------+
  8. | 1 | aaa |
  9. | 1 | aaa |
  10. +------+------+
  11. 2 rows in set (0.00 sec)
复制代码
4.清空某个表的数据
  1. mysql> truncate table db1.t1; //清空表
  2. Query OK, 0 rows affected (0.03 sec)
  3. mysql> select * from db1.t1;
  4. Empty set (0.00 sec)
  5. mysql> desc db1.t1;
  6. +-------+----------+------+-----+---------+-------+
  7. | Field | Type | Null | Key | Default | Extra |
  8. +-------+----------+------+-----+---------+-------+
  9. | id | int(4) | YES | | NULL | |
  10. | name | char(40) | YES | | NULL | |
  11. +-------+----------+------+-----+---------+-------+
  12. 2 rows in set (0.00 sec)
复制代码
5.删除表
  1. mysql> drop table db1.t1;
  2. Query OK, 0 rows affected (0.01 sec)
  3. mysql> select * from db1.t1;
  4. ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
复制代码
6.删除数据库
  1. mysql> drop database db1;
  2. Query OK, 0 rows affected (0.00 sec)
复制代码
mysql数据库备份恢复 1.备份恢复库
  1. [root@gary-tao ~]# mysqldump -uroot -pszyino-123 mysql > /tmp/mysql.sql //备份库
  2. Warning: Using a password on the command line interface can be insecure.
  3. [root@gary-tao ~]# mysql -uroot -pszyino-123 -e "create database mysql2" //创建一个新的库
  4. Warning: Using a password on the command line interface can be insecure.
  5. [root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/mysql.sql //恢复一个库
  6. Warning: Using a password on the command line interface can be insecure.
  7. [root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2
  8. Warning: Using a password on the command line interface can be insecure.
  9. Reading table information for completion of table and column names
  10. You can turn off this feature to get a quicker startup with -A
  11. Welcome to the MySQL monitor. Commands end with ; or \g.
  12. Your MySQL connection id is 38
  13. Server version: 5.6.35 MySQL Community Server (GPL)
  14. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  15. Oracle is a registered trademark of Oracle Corporation and/or its
  16. affiliates. Other names may be trademarks of their respective
  17. owners.
  18. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  19. mysql> select database();
  20. +------------+
  21. | database() |
  22. +------------+
  23. | mysql2 |
  24. +------------+
  25. 1 row in set (0.00 sec)
复制代码
2.备份恢复表
  1. [root@gary-tao ~]# mysqldump -uroot -pszyino-123 mysql user > /tmp/user.sql //备份表
  2. Warning: Using a password on the command line interface can be insecure.
  3. [root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/user.sql //恢复表
  4. Warning: Using a password on the command line interface can be insecure.
复制代码
3.备份所有库
  1. [root@gary-tao ~]# mysqldump -uroot -pszyino-123 -A > /tmp/mysql_all.sql
  2. Warning: Using a password on the command line interface can be insecure.
  3. [root@gary-tao ~]# less /tmp/mysql_all.sql
复制代码
4.只备份表结构
  1. [root@gary-tao ~]# mysqldump -uroot -pszyino-123 -d mysql > /tmp/mysql.sql
  2. Warning: Using a password on the command line interface can be insecure.
复制代码


回复

使用道具 举报