Linux Learning Summary (46) MySQL basic operation next

1 MySQL User management

For internet companies, we know that data is a non-renewable resource, and when important data is lost, it can be costly. The security of the database is very important, so for the background administrator's permissions to strictly control, to be targeted to create users, in order to meet the management needs of the basis, to minimize its operational rights. As a result, we will selectively authorize individual libraries or tables for different users to minimize the risk of data loss and prevent them from happening.
1) Maximum Authorization
grant all on *.* to ‘user1‘@‘127.0.0.1‘ idtentified by ‘passwd‘;
All means all operations, additions and deletions can be
Two *, the former represents all databases, the latter represents all the tables
User1 refers to the user name
127.0.0.1 is the native IP, which can be the IP of other hosts on the network, meaning the source IP of the client
Identified by followed by password. You need to add single quotes.
2) Authorized for certain operations only
grant SELECT,UPDATE,INSERT on db1.* to ‘user2‘@‘192.168.226.129‘ identified by ‘passwd‘;
3) for all source IP authorizations
grant all on db1.* to ‘user3‘@‘%‘ identified by ‘passwd‘;
4) View an authorization
show grants for [email protected];
Example:
Grant Select,update,insert on db1.* to ' user2 ' @ ' 192.168.226.129 ' identified by ' lvlinux ';
If you want to add an access IP to the above authorized users to do, of course, according to the above command, directly modify the IP, if you forget the previous authorization, we can directly find out, copy the previous command results, change the IP re-execution can be.
Show grants for [email protected];

GRANT USAGE on *. user2 ' @ ' 192.168.226.130 ' identified by PASSWORD ' *E8F4006805F5210EB4D651BD6F9CB6100ACD1BFF ';
GRANT SELECT, INSERT, UPDATE on ' db1 '. * to ' user2 ' @ ' 192.168.226.130 ';

Show grants for [email protected];

2 commonly used SQL statements, additions and deletions to change the search.

 select count(*) from mysql.user;  //统计mysql库中user表的行数 select * from mysql.db\G;  //  查看表db表里的所有内容，慎用。 select db from mysql.db; // 查看db表里的db字段 select db,user from mysql.db; // 同时查看两个字段 select * from mysql.db where host like ‘192.168.%‘\G; //模糊匹配查询 insert into db1.t1 values (1, ‘abc‘);  // 在表里插入一条数据 update db1.t1 set name=‘aaa‘ where id=1; //更新一条数据 delete from db1.t1 where id=2;   //删除某一条数据 truncate table db1.t1; // 清空一个表，保留表结构。 drop table db1.t1;  //删除整个表 drop database db1; // 删除库

3 Backup and recovery of databases

 备份库  mysqldump -uroot -p123456 mysql > /tmp/mysql.sql 恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql 备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql 恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql 备份所有库 mysqldump -uroot -p -A >/tmp/123.sql 只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

Note: Backup with mysqldump, restore with MySQL, backup table to write the library name and table name, restore the table does not write the table name. Back up all libraries, is-A, the backup table structure is-D

Linux Learning Summary (46) MySQL basic operation next