96. Create a regular user and authorize, common SQL statements, MySQL database backup and recovery

Source: Internet
Author: User
Tags db2 reserved

First, create a regular user and authorize 1, create a user and authorize

[Email protected] ~]# mysql-uroot-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server version:5.6.36 MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Grant all on - User01 identified by ' 123456 '
; The previous line of command forgot to enter '; ', which can be entered after continuing execution
Query OK, 0 rows affected (0.01 sec)
Create user users and grant them all permissions . (The first represents all databases, the second represents all tables)
The User01 here refers specifically to the User01 on localhost.
Identified by: Sets the password, enclosed in single quotation marks.

2. Authorize a user for other machines on the network

Mysql> Grant all in . to ' user02 ' @ ' 127.0.0.1 ' identified by ' 123456 '; Specify IP, which is only available through this IP login, there is a @ between the user and the host. You can use the wildcard character%, which represents all IPs (not normally used)
Query OK, 0 rows Affected (0.00 sec)

Mysql> quit
Bye
[[email protected] ~]# mysql-uuser02-p123456//Not connected to the specified IP, login error
Warning:using a password on the command line interface can is insecure.
ERROR 1045 (28000): Access denied for user ' user02 ' @ ' localhost ' (using Password:yes)
[[email protected] ~]# mysql-uuser02-p123456-h127.0.0.1//Specify IP, Error-
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 9
Server version:5.6.36 MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

3. View User Authorization
Mysql> Show grants//view current user authorizations, +--------------------------------------------------------------------- -------------------------------------------------------------------+|                                                                                                              Grants for [email protected] |+--------------------------------------------------------------------------------------------------- -------------------------------------+|  GRANT all privileges on * * to ' root ' @ ' localhost ' identified by PASSWORD ' *a89494294b2411291d21451d05bab332a65aab5d ' with GRANT OPTION | |                                                                           Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION |+-----------------------------------------------------------------------------------------------------------      -----------------------------+2 rows in Set (0.00 sec) mysql> Show grants for ' User02 ' @ ' 127.0.0.1 '; View specified user authorization +------------------------------------------------------------------------------------------------------------------------+|                                                                                            Grants for [email protected] |+------------------------------------------------------------------------------------------------------------- -----------+| GRANT all privileges on * * to ' user02 ' @ ' 127.0.0.1 ' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ' |+ --------------------------------------------------------------------------------------------------------------- ---------+1 Row in Set (0.00 sec) 4, authorized partial permissions (such as read, write, query, insert, etc.) mysql> grant SELECT, INSERT, UPDATE *. * user03 identified by ' 123 456 '; Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' *. * USER03 identified by ' 123456 ' at line 1mysql> Grant SELECT, INSERT, UPDATE on *. * to user03 identified by ' 123456 '; Query OK, 0 roWS Affected (0.00 sec) mysql> Show grants for USER03; +-------------------------------------------------------- ----------------------------------------------------------------+|                                                                                                    Grants for [email protected]% |+------------------------------------------------------------------------------------------------------------ ------------+| GRANT SELECT, INSERT, UPDATE on *. * to ' user03 ' @ '% ' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ' |+- --------------------------------------------------------------------------------------------------------------- --------+1 Row in Set (0.00 sec)
II. Common SQL statements

select count(*) from mysql.user; //查询mysql库中user表的行数select * from mysql.db; //查询mysql库中db表的所有内容select db from mysql.db; //查询mysql库中db表的db字段select db,user from mysql.db; 查询mysql库中db表的db和user字段select * from mysql.db where host like ‘192.168.%‘; //查询mysql库中db表中匹配192.128.开头的内容insert into db1.t1 values (1, ‘abc‘); //在db1数据库的t1表中插入对应 内容update db1.t1 set name=‘aaa‘ where id=1; //更改db1数据库的t1表中name列内容,当id是1的时候truncate table db1.t1; //清除表内数据drop table db1.t1; //删除表drop database db1; //删除数据库

Third, MySQL database backup and recovery
mysql> CREATE DATABASE db1;     Create library query OK, 1 row affected (0.01 sec) mysql> use DB1;                   Switch library database changed mysql> CREATE TABLE T1 (' id ' int (4), ' name ' char (40)); Create tables and fields query OK, 0 rows affected (0.02 sec) mysql> Show tables;+---------------+| TABLES_IN_DB1 |+---------------+| T1 |+---------------+1 row in Set (0.00 sec) mysql> select * from T1; Empty Set (0.00 sec) mysql> quitbye[[email protected] ~]# mysqldump-uroot-psdwaqw123456 MySQL >/tmp/ Mysql.sql//Backup warning:using a password on the command line interface can be insecure. [[email protected] ~]# ls/tmp/mysql.sql/tmp/mysql.sql[[email protected] ~]# mysql-uroot-psdwaqw123456 DB2 </tmp/mysql.sql//recovery warning:using a password on the command line interface can be insecure. [[email protected] ~]# mysql-uroot-psdwaqw123456warning:using a password on the command line interface can be inse Cure.  Welcome to the MySQL Monitor. Commands End With; Or \g.your MySQL connection ID is 29Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and/ 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> use DB2//Switch to DB2, see if the recovery is successful reading table Informati On for completion of table and column namesyou can turn off this feature to get a quicker startup With-adatabase changedm Ysql> Show tables;+---------------------------+| TABLES_IN_DB2 |+---------------------------+| 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 | | T1 | | Tables_priv | | Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | | User |+---------------------------+29 rows in Set (0.01 sec) mysql> quitbye[[email protected]  ~]# mysqldump-uroot-psdwaqw123456 mysql user >/tmp/mysqluser.sql//backup MySQL Library user table warning:using a password on the Command line interface can insecure.  [[email protected] ~]# mysql-uroot-psdwaqw123456 DB1 </tmp/mysqluser.sql//revert to db1warning:using a password The command line interface can insecure. [[email protected] ~]# mysql-uroot-psdwaqw123456wArning:using a password on the command line interface can is insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 32Server 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> use DB1//See if the recovery was successful reading 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_DB1 |+---------------+| T1 | | User |+---------------+2 rows in Set (0.00 sec) mysql>

96. Create a regular user and authorize, common SQL statements, MySQL database backup and recovery

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.