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

Process description:

[] install innobackupex [] backup data [] create and authorize backup users

Looking at many examples of innobackupex backup on the Internet, it is found that many of them directly use "root" users to back up the data; however, this backup method is very insecure, and the "root" password of the mysql database is directly exposed in front of the screen; to put it bluntly, what's the difference between this and "streaking"? If the password of the database falls into the hands of others, then the consequences can be. (think for yourself.) This article is born to solve this problem!

First, install innobackup

Install dependency packages

# yum-y install rsync perl-DBD-MySQL numactl libaio-devel perl-Digest perl-Digest-MD5# wget https://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/l/libev-4.15-3.el7.x86_64.rpm# rpm-ivh libev-4.15-3.el7.x86_64.rpm# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/7/x86_64/percona -xtrabackup-24-2.4.11-1.el7.x86_64.rpm# rpm-ivh percona-xtrabackup-24-2.4.11-1.el7.x8664.rpm, Backup data # innoxtrabackupex-defaults-file=/etc/my.cnf-socket=/data/mysql/mysql.sock-user=root-password=123456 / mnt/xtra

At the end, there is a hint that the backup is successful.

Check the backed-up directory. The data has been backed up.

Warm reminder:

If the data is backed up in this way in the enterprise, then the password of root users will be directly exposed. What's the difference between this and "streaking"? it's too insecure.

Therefore, it is used to authorize the login of the user, control the authority, and back up with the authorized user.

Third, create and authorize the backup user Mysql > grant reload,lock tables,replication client,create tablespace,process,super on *. * to 'xtrabackup'@'localhost' identified by' 123456 backup Mysql > flush privileges

Do not use (grant all on. To 'root-text1'@'%' indentified by' 123456;) was created to control the use of permissions and avoid abuse of permissions.

Note: try to log in to mysql with "xtrabackup" user. You can log in normally. You only have permission to view the library / table / user and cannot delete or modify it.

Backup database with "xtrabackup" user

# innoxtrabackupex-- defaults-file=/etc/my.cnf-- socket=/data/mysql/mysql.sock-- user=xtrabackup-- password=123123 / mnt/xtra

Backup succeeded

Log in to mysql to view the user's permissions

Mysql > select user,host,Reload_priv from user

It is precisely because the user "xtrabackup" is authorized to reload "Reload" that the database can be backed up.

If you try to backup with the "back" user on the database, you will find an error. As shown in the following figure

Warm reminder: if the user does not have "Reload" permission, the backup cannot be performed.

Summary (two steps):

Authorize backup users

Mysql > grant reload,lock tables,replication client,create tablespace,process,super on *. * to 'xtrabackup'@'localhost' identified by' 123456'

Backup

# innoxtrabackupex-- defaults-file=/etc/my.cnf-- socket=/data/mysql/mysql.sock-- user=xtrabackup-- password=123123 / mnt/xtra attach mysql user permissions details permissions level privilege description CREATE database, table or index database, table or index permissions DROP database tables delete database or table permissions GRANT OPTION databases, tables or saved programs grant permission options REFERENCES database or table ALTER table change table Such as adding fields, Indexes and other DELETE tables delete data permissions INDEX table index permissions INSERT table insert permissions SELECT query permissions UPDATE update permissions CREATE VIEW view create view ALTER ROUTINE stored procedure change stored procedure permissions CREATE ROUTINE stored procedure create stored procedure permissions EXECUTE stored procedure permissions execute stored procedure permissions file access permissions on the FILE server host CREATE TEMPORARY TABLES server management creation Temporary table permissions LOCK TABLES server management lock table permissions CREATE USER server management create user rights PROCESS server management view process permissions RELOAD server management execute flush-hosts Permissions of flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload, etc. REPLICATION CLIENT server management replication permissions REPLICATION SLAVE server management replication permissions SHOW DATABASES server management view database permissions SHUTDOWN server management turn off database permissions SUPER server management executes kill thread permissions

How the permissions of MYSQL are distributed, that is, what permissions can be set for tables, what permissions can be set for columns, and so on, can be explained from a table in the official document.

Permission table for permission distribution configuration permissions' Select', 'Insert',' Update', 'Delete',' Create', 'Drop',' Grant', 'References',' Index', 'Alter' list permissions' Select', 'Insert',' Update', 'References' process permissions' Execute', 'Alter Routine',' Grant'

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.