Linux centosvmware MySQL user management, common SQL statements, MySQL database backup recovery

One, MySQL user management

Grant all on * * to ' user1 ' @ ' 127.0.0.1 ' identified by ' mimA123 '; Create User1 user

Sign in with User1

/usr/local/mysql/bin/mysql-uuser1-pmima123-h127.0.0.1

To localhost, without-H can also log in

Mysql> Grant All on * * to ' user1 ' @ ' localhost ' identified by ' mimA123 ';

[Email protected] ~]#/usr/local/mysql/bin/mysql-uuser1-pmima123

Grant Select,update,insert on db1.* to ' user1 ' @ ' 192.168.1.101 ' identified by ' mimA123 '; Authorized

Grant all on db1.* to ' user2 ' @ '% ' of ' identified by ' mimA123 '; Give any IP license, any IP in% instead of

Show grants;

Show grants for [email protected]; View specified user authorizations

II. Common SQL statements

Login to MySQL switch to DB1 library

Select COUNT (*) from Mysql.user; Inquire

SELECT * from Mysql.db;

The ENGINR used inside the MySQL library is MyISAM, which automatically helps to count how many rows

The library engine you created is InnoDB

Select db from Mysql.db;

Select Db,user from Mysql.db;

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

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

Update db1.t1 set name= ' AAA ' where id=1; Change a row of a table

Delete data

Delete from db1.t1 where id=1;

TRUNCATE TABLE db1.t1; Clear the data table, the table is still

drop table db1.t1; Delete the table, all the table data is deleted

Drop database db1; Deleting a database

Third, MySQL database backup recovery

Create a new database Mysql2

/USR/LOCAL/MYSQL/BIN/MYSQL-UROOT-E "CREATE Database Mysql2"

Backup library mysqldump-uroot-pmima123 mysql >/tmp/mysql.sql

Recovery library mysql-uroot-pmima123 MySQL </tmp/mysql.sql

Backup table mysqldump-uroot-pmima123 mysql user >/tmp/user.sql

Recovery table mysql-uroot-pmima123 MySQL </tmp/user.sql

Back up all Libraries mysqldump-uroot-p-A >/tmp/123.sql

Back up table structure only mysqldump-uroot-pmima123-d mysql >/tmp/mysql.sql

Linux centosvmware MySQL user management, common SQL statements, MySQL database backup recovery