Shulou(Shulou.com)06/01 Report--

1. Background:

Due to the deterioration of network security environment, it is more and more undesirable to directly backup with the highest authority and root user in the past. It is too troublesome to backup manually every time, and it is afraid that the high-authority account will be leaked based on script backup.

2. Solution:

Permission minimization grant

3. Operation:

Permission that may be required when backing up with a physical backup tool: Physical backup tools: innobackupex, MySQL Enterprise Backup, etc. Permission: lock tables Role: Lock tables when backing up, resulting in consistent backups Authority: process Functions: show processlist,show engine innodb status, view threads, view engine status Permission: reload Function: flush table/host/logs/tables/status/threads/refresh/reload, all flush operations. Used for locking tables, cutting logs, updating permissions Permission: replication client Function: show master/slave status; view transaction log execution status and location show binary logs; View a list of currently saved transaction logs and file sizes Authority: super Role: super has a lot of permissions, but there is no CURD (add, delete, change and search permissions). Here, let's talk about backup related. Start and stop replication threads, switch the main library location, change replication filter conditions, clean up binary logs, Grant DEFINER permissions to account views and stored procedures, create linked servers (similar to MSSQL Subscribers), Close threads, VIP connection channels that are not limited by the maximum number of connection threads, commands that block refresh threads, are not affected by offline mode, --Authorization statement: grant lock tables,reload,process,replication client,super on *.* to bak@'192.168.% ';flush privileges; Privileges that may be required when backing up using the logical backup tool: Logical backup tools: mysqldump,mysqlpump,mydumper, etc. Permission: SELECT Role: Query data in tables Access: Show View Role: View statements that create views Authority: TRIGGER Role: Backup Trigger Authority: EVENT Role: Backup events (timed tasks) Permission: lock tables Role: Lock tables when backing up, resulting in consistent backups Permission: reload Functions: show processlist,show engine innodb status, view threads, view engine status Permission: replication client Function: show master/slave status; view transaction log execution status and location show binary logs; View a list of currently saved transaction logs and file sizes Authority: super Function: Close thread, VIP connection channel not limited by maximum number of connection threads, block command to refresh thread, not affected by offline mode--authorization statement: grant lock tables,reload,process,replication client,super,select,event,trigger,show view on *.* to bak@'192.168.% ';flush privileges;

Remarks:

The super privilege prevents backups from rolling over because the backup task cannot connect to the database due to thread overload. The basic principle of logical backup is to read all the data, you must select and show permissions, and view the permissions defined in the table. When the login-path is saved by port+host, the.login.cnf file will be generated in the user directory and copied to other hosts in the network. You can still log in, which is convenient and leaves the root of disaster.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.