查看: 802|回复: 0

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

发表于 2018-1-23 08:00:01
13.4 mysql用户管理 创建用户并授权
  1. [root@taoyuan ~]# mysql -uroot -p'taoyuan' //登录
  2. mysql> grant all on *.* to 'user1'@'172.0.0.1' identified by '123456';
  3. #创建user用户并授予其所有权限“*.*”(通配符)
  4. #第一个*表示db_name;第二个*表示tb_name
  5. #同时指定其来源IP127.0.0.1(即,只可通过此IP登录)
  6. #此处可以使用通配符%,代表所有IP(一般不使用)
  7. #identified by :设定密码
  8. #quit 退出
  9. [root@taoyuan ~]# mysql -uuser1 -p123456 -h127.0.0.1
复制代码

使用socket登录

  1. #登录root用户
  2. mysql> grant all on *.* to 'user1'@'localhost' identified by '123456';
  3. Query OK, 0 rows affected (0.01 sec)
  4. #授权localhost
  5. #登录
  6. [root@taoyun ~]# mysql -uuser1 -p123456
复制代码

说明: 因为指定登录主机为localhost,所以该用户默认使用(监听)本地mysql.socket文件,不需要指定IP即可登录。

对具体权限进行授权
  1. [root@taoyun ~]# mysql -uroot
  2. mysql> show grants;
  3. #查看当前用户的权限
  4. mysql> show grants for user1@'127.0.0.1';
  5. #查看指定用户的权限
  6. mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.1.15';
  7. Query OK, 0 rows affected (0.01 sec)
  8. #在不知道密码的情况下,可以直接用show grants命令进行复制,修改ip地址 增加。
复制代码
13.5 常用sql语句

查看 select

select count(*) from mysql.user;
  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. | 10 |
  10. +----------+
  11. 1 row in set (0.03 sec)
  12. mysql> select * from mysql.db;
  13. mysql> select db from mysql.db;
  14. +---------+
  15. | db |
  16. +---------+
  17. | test |
  18. | test\_% |
  19. | db1 |
  20. +---------+
  21. 3 rows in set (0.00 sec)
  22. mysql> select db,user from mysql.db;
  23. +---------+-------+
  24. | db | user |
  25. +---------+-------+
  26. | test | |
  27. | test\_% | |
  28. | db1 | user2 |
  29. +---------+-------+
  30. 3 rows in set (0.01 sec)
  31. mysql> select * from mysql.db where host like '192.168.%';
  32. #模糊查询
复制代码

插入 insert

  1. mysql> insert into db1.t1 values (1,'abc');
  2. Query OK, 1 row affected (0.01 sec)
  3. mysql> select * from db1.t1;
  4. +------+------+
  5. | id | name |
  6. +------+------+
  7. | 1 | abc |
  8. +------+------+
  9. 1 row in set (0.00 sec)
  10. #成功插入一条数据
复制代码

更改 update

  1. mysql> select * from db1.t1;
  2. +------+-------+
  3. | id | name |
  4. +------+-------+
  5. | 1 | abc |
  6. | 1 | 12454 |
  7. | 1 | 12454 |
  8. +------+-------+
  9. 3 rows in set (0.00 sec)
  10. mysql> update db1.t1 set name='aaa' where id=1;
  11. Query OK, 3 rows affected (0.01 sec)
  12. Rows matched: 3 Changed: 3 Warnings: 0
  13. mysql> select * from db1.t1;
  14. +------+------+
  15. | id | name |
  16. +------+------+
  17. | 1 | aaa |
  18. | 1 | aaa |
  19. | 1 | aaa |
  20. +------+------+
  21. 3 rows in set (0.00 sec)
  22. #更改成功
  23. #删除
  24. mysql> delete from db1.t1 where id=1;
  25. Query OK, 3 rows affected (0.03 sec)
  26. mysql> select * from db1.t1;
  27. Empty set (0.00 sec)
  28. #已经删完了
复制代码

清空数据表

  1. mysql> insert into db1.t1 values(1,'234');
  2. Query OK, 1 row affected (0.02 sec)
  3. mysql> select * from db1.t1;
  4. +------+------+
  5. | id | name |
  6. +------+------+
  7. | 1 | 234 |
  8. +------+------+
  9. 1 row in set (0.01 sec)
  10. mysql> truncate db1.t1;
  11. Query OK, 0 rows affected (0.05 sec)
  12. #清空这个表的内容
  13. mysql> desc db1.t1;
  14. +-------+----------+------+-----+---------+-------+
  15. | Field | Type | Null | Key | Default | Extra |
  16. +-------+----------+------+-----+---------+-------+
  17. | id | int(4) | YES | | NULL | |
  18. | name | char(40) | YES | | NULL | |
  19. +-------+----------+------+-----+---------+-------+
  20. 2 rows in set (0.00 sec)
复制代码

drop

  1. mysql> drop table db1.t1;
  2. Query OK, 0 rows affected (0.02 sec)
  3. mysql> desc db1.t1;
  4. ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
  5. #已经把表结构删除了
  6. mysql> drop database db1;
  7. Query OK, 0 rows affected (0.02 sec)
  8. #删除库,危险操作,尽量少用,防止删除正要数据
复制代码
13.6 mysql数据库备份恢复 备份库
  1. [root@taoyun ~]# mysqldump -uroot -p mysql > /tmp/mysqlbak.sql
  2. Enter password:
复制代码
恢复库
  1. #创建新的库
  2. [root@taoyun ~]# mysql -uroot -e "create database mysql2"
  3. [root@taoyun ~]# mysqldump -uroot mysql2 < /tmp/mysqlbak.sql
  4. [root@taoyun ~]# mysql -uroot mysql2
  5. #直接登录到mysql2库
  6. mysql> select database();
  7. +------------+
  8. | database() |
  9. +------------+
  10. | mysql2 |
  11. +------------+
  12. 1 row in set (0.00 sec)
复制代码
备份表
  1. [root@taoyun ~]# mysqldump -uroot mysql user > /tmp/user.sql;
复制代码
恢复表
  1. [root@taoyun ~]# mysql -uroot mysql2 < /tmp/user.sql
复制代码
备份所有库
  1. [root@taoyun ~]# mysqldump -uroot -A > /tmp/123.sql
  2. [root@taoyun ~]# less /tmp/123.sql
  3. #检查 内容
复制代码
只备份表结构
  1. [root@taoyun ~]# mysqldump -uroot -d mysql > /tmp/mysql.sql
  2. #less 检查备份内容
复制代码


回复

使用道具 举报