One, MySQL user management
MySQL > Grant all on
.To ' user1 ' @ ' 127.0.0.1 ' identified by ' 123456 '; Authorize all permissions to User1 users;
.The first one
refers to a library name, such as MySQL.The specified source IP is either 127.0.0.1,@ or write% directly, indicating a wildcard, all IPs can be accessed, and the password is 123456, creating a User1 user
MySQL > Quit
#mysql-uuser1-p123456//This does not go in, because the default is the sock connection, the above is authorized 127.0.0.1
#mysql-uuser1-p123456-h127.0.0.1//This time you need to specify-H to 127.0.0.1 you can connect
MySQL > Quit//exit can use quit or exit or Ctrl+d
How do I authorize localhost login?
#mysql-uroot-p123456//Login root
MySQL > Grant all on
.To ' user1 ' @ ' localhost ' identified by ' 123456 '; Authorized localhost
MySQL > Quit//Sometimes hit the wrong sign directly by the semicolon;
#mysql-uuser1-p123456//re-verify to log in.
MySQL > Grant Select,update,insert on DB1.
To ' user2 ' @ ' 192.168.133.1 ' identified by ' passwd ';//to User2 user authorization to the DB1 library has the query, update, write permissions
MySQL > Grant all on DB1.To ' user3 ' @ '% ' of ' identified by ' passwd '; User3 users have all rights to the DB1 library for all IP
MySQL > show grants for [email protected] ' 127.0.0.1 '; Used to see what the authorization of the specified user is
MySQL > show grants for [email protected] ' 192.168.133.1 '//view user2 Authorization for 192.168.133.1 this IP
If you need to log in 192.168.133.2 above, and 192.158.133.1 the above permissions, usernames, passwords are the same, you need to execute the two lines in the box below, modify the next IP is 192.168.133.2
II. Common SQL statements
MySQL > select count (
) from Mysql.user; Find the number of rows for this table, Mysql.user is a point in the middle of the library and table. As a delimiter
MySQL > selectFrom Mysql.db; //
means to view all content
The two commonly used database engines are MyISAM and InnoDB two, all the tables in the MySQL library are used by the MyISAM engine, which is characterized by the ability to automatically count how many rows
Above two commands select count () and select
execute as little as possible because it is time consuming and resource intensive
MySQL > select db from Mysql.db; Querying the DB field
MySQL > select db,user from Mysql.db; Querying DB and User two fields, separated by commas
MySQL > selectFrom mysql.db where host like ' 192.168.% '; Fuzzy query
MySQL > select * from mysql.db where host like ' 192.168.% ' \g; The display is structured some
MySQL > INSERT into db1.t1 values (1, ' abc '); Insert a piece of data
MySQL > INSERT into db1.t1 values (1, ' 234 '); Try to add single quotation marks to the operation of the string
MySQL > Update db1.t1 set name= ' AAA ' where id=1; Change the name of Id=1 to AAA
MySQL > Update db1.t1 set id=2 where name= ' AAA '; Change the ID of NAME=AAA to 2
MySQL > Delte from db1.t1 where id=2; To delete id=2 data
MySQL > Truncate db1.t1; Emptying a table, the difference between truncate and delte, and drop is that truncate only empties the contents of the table, the table structure remains, and the drop table contents and table structure are emptied.
mysql > drop database db1; Delete DB1 Library
Third, MySQL database backup recovery
#mysqldump-uroot-p123456 mysql >/tmp/mysql.sql//backup MySQL library to/tmp directory
#mysql-uroot-p123456 MySQL </tmp/mysql.sql//recover MySQL Library
#mysqldump-uroot-p123456 mysql user >/tmp/user.sql//Back up the user table inside the MySQL library with a space between the library name and the surface
#mysql-uroot-p123456 MySQL </tmp/user.sql//recovery table does not need to write table name, backup with mysqldump, restore with MySQL
#mysqldump-uroot-p123456-a >/tmp/mysql_all.sql//Back Up all libraries
#less/tmp/mysql_all.sql//After backup you can use less to view the contents of the backup
#mysqldump-uroot-p123456-d mysql >/tmp/mysql.sql//Use the-D option to back up the table structure only
MySQL user management Common SQL statements MySQL database backup recovery
