MySQL common operations (bottom)

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&GT 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)