MySQL数据库备份实例详解(3)

由于master-data的值设置为1，change master命令并没有被注释掉，如果我们使用这个命令进行恢复，change master命令就会被执行，在一些情况下可能会造成一些错误，所以建议使用时最好还是设置为2

可以通过下面的命令查看，备份文件中包含哪些数据库

[root@localhost db_backup]# grep "Current Database" mc.sql -- Current Database: `mc_orderdb` -- Current Database: `mc_productdb` -- Current Database: `mc_userdb` -- Current Database: `mysql` [root@localhost db_backup]# 使用-tab参数指定备份文件的位置

首先在/tmp 目录下建立一个mc_orderdb目录用来存放指定的备份文件，之所以使用在此目录下建立目录，是因为使用--tab参数时，用户必须对目标目录有可写权限，而tmp目录对任何用户都有可写权限

[root@localhost db_backup]# mkdir -p /tmp/mc_orderdb

现在我们可以使用--tab参数指定备份路径

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb Enter password: -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'

可以发现，报错了，其实我们在这之前还缺少一步，由于用户需要有写文件的权限，所以我们还需要对备份用户赋予file权限

mysql> grant file on *.* to 'backup'@'localhost'; Query OK, 0 rows affected (0.00 sec)

我们再次执行上面的备份命令

[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdb Enter password: -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=347; mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

可以很清楚地从提示看到是因为mysql服务启用了–secure-file-priv，所以才无法执行。
那么–secure-file-priv又是什么呢，应该如何解决才能是它可以备份呢？
--secure-file-priv=name ：
Limit LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE() to files within specified directory

可以看到secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。

当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出

当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下

当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

查看数据库当前该参数的值

mysql> show global variables like '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ 3 rows in set (0.00 sec)

清楚地看到secure_file_priv 的值是NULL，说明此时限制导入导出的
所以应该改变该参数
可是查看了mysql.cnf中居然没有对这个参数进行设定，就说明这个参数默认便是null
所以再mysql.cnf中的[mysqld]加入secure_file_priv =

[mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/home/mysql/data # mysql数据存放的目录 socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid log_bin=mysql-bin server-id=1 secure_file_priv =

再重启mysql服务

[root@localhost tmp]# systemctl restart mysqld

然后再查一下此时参数的值

mysql> show global variables like '%secure%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | | +--------------------------+-------+ 3 rows in set (0.01 sec)