MySQL User management
1. Create User: Grant command
mysql> grant all on *.* to ‘user1‘@‘127.0.0.1‘ identified by ‘123456a‘;Query OK, 0 rows affected (0.00 sec)
Test
[[email protected] 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[[email protected] 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. View Authorization:
mysql> Show grants;+------------------------------------------------------------------------------ -----------------------------------------+| Grants for [email protected] |+------------------------------------------------------------------------------------------------------------- ----------+| GRANT all privileges on * * to ' user1 ' @ ' localhost ' identified by PASSWORD ' *b012e8731ff1df44f3d8b26837708985278c3ced ' |+- --------------------------------------------------------------------------------------------------------------- -------+1 Row in Set (0.00 sec) Mysql>
mysql> show grants for [email protected] -> ;+-----------------------------------------------------------------------------------------------------------------------+| Grants for [email protected] |+-----------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘user1‘@‘127.0.0.1‘ IDENTIFIED BY PASSWORD ‘*B012E8731FF1DF44F3D8B26837708985278C3CED‘ |+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
Common MySQL Statements
1. See how many rows a table has:
mysql> select count(*) from mysql.user;+----------+| count(*) |+----------+| 8 |+----------+1 row in set (0.01 sec)
2. View the fields:
Mysql> SELECT * from mysql.db\g;*************************** 1. Row *************************** Host:% db:test user:select_p Riv:y insert_priv:y update_priv:y delete_priv:y create_priv:y drop_pri V: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:selec T_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. View fixed fields:
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. Fuzzy query: IP source
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_p Riv:y insert_priv:y update_priv:y delete_priv:y create_priv:y drop_pri V: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:selec T_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_PR Iv:y alter_routine_priv:n execute_priv:n event_priv:y trigger_priv:y2 rows in Set (0.00 s EC) Error:no query specified
5. Insert Data:
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: Can be used to modify
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. Clear a table: structure does not move
mysql> truncate db1.t1;Query OK, 0 rows affected (0.02 sec)mysql> select * from db1.t1;Empty set (0.00 sec)
8. Also remove the structure:
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 Database backup recovery
1. Backup Library:
[[email protected] mysql]# mysqldump -uroot -pwei914 mysql > /tmp/mysqlbak.sqlWarning: Using a password on the command line interface can be insecure.
2. Restore the database: can revert to the specified library
[[email protected] mysql]# mysql-uroot-pwei914-e "CREATE Database Mysql2" warning:using a password on the command Line interface can insecure. [[email protected] mysql]# mysql-uroot-pwei914 mysql2 </tmp/mysqlbak.sqlwarning:using a password on the Comman D line interface can insecure. [[email protected] 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 W Ith-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) #, Oracle an d/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names 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 Informat Ion for completion of table and column namesyou can turn off this feature to get a quicker startup With-adatabase changed Mysql> 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. Backup table:
[[email protected] mysql]# mysqldump -uroot -pwei914 mysql user > /tmp/user.sqlWarning: Using a password on the command line interface can be insecure.
4. Recovery table: Do not add a table name when restoring a table
[[email protected] mysql]# mysql -uroot -pwei914 mysql2 < /tmp/user.sqlWarning: Using a password on the command line interface can be insecure.
5. Back up all libraries:-A
[[email protected] mysql]# mysqldump -uroot -pwei914 -A > /tmp/mysql_all.sqlWarning: Using a password on the command line interface can be insecure.
6. Back up the table structure only:-D
[[email protected] mysql]# mysqldump -uroot -pwei914 -d mysql2 > /tmp/mysql2.sqlWarning: Using a password on the command line interface can be insecure.
MySQL common operations (bottom)