MySQL common operations (bottom)

Source: Internet
Author: User
Tags reserved create database

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.