MySQL user management, common SQL statements, database backup recovery

MySQL User management

MySQL is divided into regular users and root users. These two types of users have different permissions.
New Normal User
There are 3 ways to build a user in the MySQL database:
Use the Create USER statement to make new users;
Insert the user directly in the Mysql.user table;
Use the GRANT statement to create a new user;

Grant Command
grant all on *.* to ‘user1‘@‘192.168.71.131‘ identified by ‘123456‘;

All means all permissions.
. Represents all libraries, the preceding represents the library name, and the following represents all the tables
' User1 ' @ ' 192.168.71.131 ' specifies the source IP of 192.168.71.131, which allows all IP words to use%, which indicates wildcard characters. The user created is UESR1

You can also use Locahost
grant SELECT,UPDATE,INSERT on db1.* to ‘user2‘@‘192.168.71.132‘ identified by ‘passwd‘;

Create a user2 normal user, and the source IP is 192.168.71.131, the library permission for the specified DB1 is Select,update,insert

View the user's authorization, here is the command to query the UESR1 user's authorization
show grants for [email protected]\G

Common SQL statements

To view the number of rows in a table in a library
select count(*) from mysql.user;

See all the content
select * from mysql.db\G;

Query fields

select db from mysql.db;select db,user from mysql.db;

Fuzzy query
select * from mysql.db where host like ‘192.168.%‘;

Insert a data
insert into db1.t1 values (1, ‘abc‘);

Update data
update db1.t1 set name=‘aaa‘ where id=1;

Empty a table

truncate table db1.t1;drop table db1.t1;

Empty Library
drop database db1;

MySQL Database backup recovery

Backup Library
mysqldump -uroot -p‘testpasswd1.3‘ mysql > /tmp/mysql.sql;

Recovery Library
mysql -uroot -p‘testpasswd1.3‘ mysql < /tmp/mysql.sql；

Backup table
mysqldump -uroot -p‘testpasswd1.3‘ mysql user > /tmp/user.sql；

Recovery table
mysql -uroot -p‘testpasswd1.3‘ mysql < /tmp/user.sql

Back Up all libraries
mysqldump -uroot -p -A >/tmp/123.sql

Back up table structure only
mysqldump -uroot -p‘testpasswd1.3‘ -d mysql > /tmp/mysql.sql

MySQL user management, common SQL statements, database backup recovery