MySQL用户管理

1.创建用户：grant命令

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

测试

[root@weix-01 mysql]# mysql -uuser1 -p123456a -h127.0.0.1Warning: 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 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on *.* to 'user1'@'localhost'  identified by '123456a';Query OK, 0 rows affected (0.00 sec)mysql> quitBye[root@weix-01 mysql]# mysql -uuser1 -p123456aWarning: 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 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.查看授权：

mysql> show grants;+-----------------------------------------------------------------------------------------------------------------------+| Grants for user1@localhost                                                                                            |+-----------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' |+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>

mysql> show grants for user1@127.0.0.1    -> ;+-----------------------------------------------------------------------------------------------------------------------+| Grants for user1@127.0.0.1                                                                                            |+-----------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' |+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

常用mysql语句

1.查看一个表有多少行：

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

2.查看字段：

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: YCreate_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: YCreate_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: Y2 rows in set (0.00 sec)ERROR:No query specified

3.查看固定字段：

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

mysql> select db,user from mysql.db;+---------+------+| db      | user |+---------+------+| test    |      || test\_% |      |+---------+------+2 rows in set (0.01 sec)

4.模糊查询：ip来源

mysql> select * from mysql.db where host like '127.0.%'\G;Empty set (0.00 sec)ERROR:No query specifiedmysql> select * from mysql.db where host like '%'\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: YCreate_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: YCreate_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: Y2 rows in set (0.00 sec)ERROR:No query specified

5.插入数据：

mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(4)   | YES  |     | NULL    |       || name  | char(40) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> select * from db1.t1;Empty set (0.01 sec)mysql> insert into db1.t1 values (1,'abc');Query OK, 1 row affected (0.01 sec)mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | abc  |+------+------+1 row in set (0.00 sec)

6.update：可用来修改

mysql> update db1.t1 set name='aaa' where id=1;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | aaa  |+------+------+1 row in set (0.00 sec)

7.清空一个表：结构不动

mysql> truncate db1.t1;Query OK, 0 rows affected (0.02 sec)mysql> select * from db1.t1;Empty set (0.00 sec)

8.把结构也删除：

mysql> drop table t1;Query OK, 0 rows affected (0.01 sec)mysql> select * from db1.t1;ERROR 1146 (42S02): Table 'db1.t1' doesn't existmysql> drop database db1;Query OK, 0 rows affected (0.01 sec)

mysql数据库备份恢复

1.备份库：

[root@weix-01 mysql]# mysqldump -uroot -pwei914 mysql > /tmp/mysqlbak.sqlWarning: Using a password on the command line interface can be insecure.

2.恢复数据库：可以恢复到指定库

[root@weix-01 mysql]# mysql -uroot -pwei914 -e "create database mysql2"Warning: Using a password on the command line interface can be insecure.[root@weix-01 mysql]# mysql -uroot -pwei914 mysql2 < /tmp/mysqlbak.sqlWarning: Using a password on the command line interface can be insecure.[root@weix-01 mysql]# mysql -uroot -pwei914 mysql2Warning: Using a password on the command line interface can be insecure.Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 15Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select database();+------------+| database() |+------------+| mysql2     |+------------+1 row in set (0.01 sec)mysql> show tables;+---------------------------+| Tables_in_mysql2          |+---------------------------+| 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                  || tables_priv               || time_zone                 || time_zone_leap_second     || time_zone_name            || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+28 rows in set (0.00 sec)mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+| 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                  || tables_priv               || time_zone                 || time_zone_leap_second     || time_zone_name            || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+28 rows in set (0.00 sec)

3.备份表：

[root@weix-01 mysql]# mysqldump -uroot -pwei914 mysql user > /tmp/user.sqlWarning: Using a password on the command line interface can be insecure.

4.恢复表：恢复表时不用加表名字

[root@weix-01 mysql]# mysql -uroot -pwei914 mysql2 < /tmp/user.sqlWarning: Using a password on the command line interface can be insecure.

5.备份所有库：-A

[root@weix-01 mysql]# mysqldump -uroot -pwei914 -A > /tmp/mysql_all.sqlWarning: Using a password on the command line interface can be insecure.

6.只备份表结构：-d

[root@weix-01 mysql]# mysqldump -uroot -pwei914 -d mysql2 > /tmp/mysql2.sqlWarning: Using a password on the command line interface can be insecure.