linux学习lesson55

1 mysql用户管理

grant all on . to ‘user1’ identified by ‘passwd’; //对所用的库，拥有所用的权限，指定user1访问：

mysql> grant all on *.* to 'user1' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

grant SELECT,UPDATE,INSERT on db1.* to ‘user2’@‘192.168.133.1’ identified by ‘passwd’; //限制的权限只有SELECT,UPDATE,INSERT，针对db1数据库的所有表，指定user2用户访问，ip为192.168.139.1：

mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.139.1' identified by '1234456';
Query OK, 0 rows affected (0.00 sec)

grant语句是不会记录到命令历史的，为了安全

grant all on db1.* to ‘user3’@’%’ identified by ‘passwd’; //针对db1数据库的所有表，拥有所用的权限，指定user3用户访问

mysql> grant all on db1.* to 'user3'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

show grants; //查看当前用户授权情况

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)

show grants for user2@192.168.139.1; //复制一个相同的用户，只有ip不一样的时候

mysql> show grants for user2@192.168.139.1;
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.139.1                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.139.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.139.1'                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

针对一个已存在的授权用户，而且是不同的ip需要访问，可以使用show grants for user2@192.168.139.1;查看权限，进行权限复制
复制一下两行，修改一下ip就可以了

mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.139.2' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'；
mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.139.2'

如何查看授权的所有用户
SELECT DISTINCT CONCAT(‘User: ‘’’,user,’’’@’’’,host,’’’;’) AS query FROM mysql.user;

select distinct concat(‘User: ‘’’,user,’’’@’’’,host,’’’;’) as query from mysql.user; //全是单引号

2 常用sql语句

select count(*) from mysql.user; //统计mysql的user表的行数

mysql> select count(1) from mysql.user;
+----------+
| count(1) |
+----------+
|        9 |
+----------+
1 row in set (0.01 sec)

select * from mysql.db; //查看mysql数据库db表所有字段内容

mysql> select * from mysql.db\G;
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 3. row ***************************
                 Host: 192.168.139.1
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 4. row ***************************
                 Host: %
                   Db: db1
                 User: user3
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
    4 rows in set (0.00 sec)

ERROR:
No query specified

select db from mysql.db; //查看mysql数据库db表的db字段内容

mysql> select db from mysql.db;
+---------+
| db      |
+---------+
| db1     |
| test    |
| test\_% |
| db1     |
+---------+
4 rows in set (0.00 sec)

select db,user from mysql.db;

mysql> select db,user from mysql.db; //查看mysql数据库db表的db和user字段内容
+---------+-------+
| db      | user  |
+---------+-------+
| db1     | user3 |
| test    |       |
| test\_% |       |
| db1     | user2 |
+---------+-------+
4 rows in set (0.00 sec)

select * from mysql.db where host like ‘192.168.%’; //查看mysql数据库db表所用字段内容，ip前段是192.168.

mysql> select * from mysql.db where host like '192.168.%'\G;
*************************** 1. row ***************************
                 Host: 192.168.139.1
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.00 sec)

ERROR:
No query specified

insert into db1.t1 values (1, ‘abc’); //插入字段的内容

mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

update db1.t1 set name=‘aaa’ where id=1; //更新字段的内容

mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

truncate table db1.t1; //清空数据库db1的t1表内容

mysql> truncate table db1.t1;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from db1.t1;
Empty set (0.00 sec)

drop table db1.t1; //删除db1数据的t1表

mysql> drop table db1.t1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

drop database db1; //删除数据库db1

mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

myisam可以统计行，查询很快；innodb不会统计行数，每次查询每次统计，会很耗时

3 mysql数据库备份恢复

备份库 mysqldump -uroot -p123456 mysql > /tmp/mysql.sql

[root@linux01 ~]# mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
[root@linux01 ~]# ls /tmp/
alan-fcgi.sock  mysql.sock  mysql.sql  php-fcgi.sock

恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql

[root@linux01 ~]# mysql -uroot -p123456 mysql < /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.

备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql

[root@linux01 ~]# mysqldump -uroot -p123456 mysql user > /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
[root@linux01 ~]# ls /tmp/
alan-fcgi.sock  mysql.sock  mysql.sql  php-fcgi.sock  user.sql

恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql //只需要写对应的数据库不用写表

[root@linux01 ~]# mysql -uroot -p123456 mysql < /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.

备份所有库 mysqldump -uroot -p -A >/tmp/123.sql

[root@linux01 ~]# mysqldump -uroot -p123456 -A > /tmp/123.sql
Warning: Using a password on the command line interface can be insecure.

只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

[root@linux01 ~]# mysqldump -uroot -p123456 -d mysql > /tmp/mysql-1.sql
Warning: Using a password on the command line interface can be insecure.

[root@linux01 ~]# ls /tmp/
123.sql  alan-fcgi.sock  mysql-1.sql  mysql.sock  mysql.sql  php-fcgi.sock  user.sql

扩展

SQL语句教程 http://www.runoob.com/sql/sql-tutorial.html
什么是事务？事务的特性有哪些？ http://blog.csdn.net/yenange/article/details/7556094
根据binlog恢复指定时间段的数据 https://blog.csdn.net/lilongsy/article/details/74726002
相关扩展 https://blog.csdn.net/linuxheik/article/details/71480882
mysql字符集调整 http://xjsunjie.blog.51cto.com/999372/1355013

使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份 http://zhangguangzhi.top/2017/08/23/innobackex工具备份mysql数据/#三、开始恢复mysql
相关视频
链接：http://pan.baidu.com/s/1miFpS9M 密码：86dx
链接：http://pan.baidu.com/s/1o7GXBBW 密码：ue2f