2018-3-23 13 weeks 5 lessons MySQL common operation (second)

13.4 MySQL User management

• Create User:grant all on * * to ' user1 ' identified by ' passwd ';

or specify the source ip:grant all on * * to ' user1 ' @ ' IP ' identified by ' passwd ';

Mysql> Grant All on * * to ' user1 ' identified by ' 123456 '; Query OK, 0 rows Affected (0.00 sec) Mysql> Grant all on *. * to ' user2 ' @ ' 127.0.0.1 ' identified by ' 123456 '; Query OK, 0 rows Affected (0.00 sec)

• Permissions on all tables in MySQL database

Grant all on mysql.* to ' user1 ' identified by ' passwd ';

• Authorized for all IPs

Grant all on * * to ' user2 ' @ ' percent ' identified by ' 123456 ';

• For Socket Authorization:

Mysql> Grant All on * * to ' user1 ' @ ' localhost ' identified by ' 123456 '; Query OK, 0 rows Affected (0.00 sec)

• View login User authorization: Show grants

• View specified user authorization: Show grants for username @ip;

• For partial permission authorization:

Grant Select,update,insert on db1.* to ' User3 ' @ ' 192.168.133.1 ' identified by ' passwd ';

• If you want to authorize the same user on a non-IP:

Copy the commands in the grants to change the IP

13.5 Common SQL statements

• See how many rows are in a table in a database

Select COUNT (*) from Mysql.user;

• See what a table says:

SELECT * from Mysql.db\g;

(SELECT COUNT (*) and select * This kind of operation to avoid, the data is too large tables, more time-consuming)

• Display fields:

Select db from Mysql.db;

What are fields: these are the fields

• Display multiple fields:

Select Db,user from Mysql.db;

• Fuzzy query:

SELECT * from mysql.db where host like ' 192.168.% ';

SELECT * from mysql.db where host like ' 192.168.% ' \g;

• Insert data into the table:

INSERT into DB1.T1 values (1, ' abc ');

• Change the data information in the table:

Update db1.t1 set name= ' AAA ' where id=1;

Update db1.t1 set id=2 where name= ' xyz ';

• Delete according to conditions:

Delete from db1.t1 where id=3;

• Clear all data from the table (without deleting it):

TRUNCATE TABLE db1.t1;

(The difference between truncate and drop, delete, truncate only clear the table data, do not destroy the structure of the tables)

• Delete the table completely:

drop table db1.t1;

mysql> drop table db1.t1; Query OK, 0 rows affected (0.01 sec)

• Completely delete the database:

Drop database db1;

mysql> drop Database db1; Query OK, 1 row affected (0.01 sec)

13.6 MySQL Database backup recovery

• Backup library:

Mysqldump-u User name-p password Database > backup data file path

[Email protected] ~]# mysqldump-uroot-p123456 mysql >/tmp/mysql.sql

(Too much data is not listed)

• Recovery library:

Mysql-u User name-p password Database < backup data file path

[Email protected] ~]# mysql-uroot-p123456 MYSQL2 </tmp/mysql.sq

• Backup table:

Mysqldump-u User name-p password database table name > Backup Data path

[[email protected] ~]# mysqldump-uroot-p123456 mysql user >/tmp/user.sql

• Recovery table:

Mysqldump-u User name-p password Database < backup data path

[Email protected] ~]# mysqldump-uroot-p123456 MySQL </tmp/user.sql

(Restore the table data, just write the library name)

• Back up all libraries:

Mysqldump-u Username-p password-A > Backup Data path

[Email protected] ~]# mysqldump-uroot-p123456-a >/tmp/mysql_all.sql

• Back up the table structure only:

Mysqldump-u User name-p password-D database > Backup Data path

[Email protected] ~]# mysqldump-uroot-p123456-d mysql2 >/tmp/mysql2.sql

2018-3-23 13 weeks 5 lessons MySQL common operation (second)