AutoMySQLBackup 3.0在MySQL 5.7中的问题修复

最近一个电子看板小项目上线，由于数据库非常小，而且数据也不太重要。因此未选择XtraBackup备份，打算用AutoMySQLBackup来备份，结果部署后测试发现，有一些小问题是之前解决过的。有一些是MySQL 5.7版本才有的。下面记录一下解决过程。关于AutoMySQLBackup的基础知识，参考我这篇博客“MySQL备份还原——AutoMySQLBackup介绍”。这里不做详细介绍。这里的MySQL版本： 5.7.30

测试过程，备份日志出现下面告警信息：

###### WARNING ######

Errors reported during AutoMySQLBackup execution.. Backup failed

Error log below..

mysql: [Warning] Using a password on the command line interface can be insecure.

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

mysqlshow: [Warning] Using a password on the command line interface can be insecure.

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

1：解决“WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.”报错：

因为从MySQL 5.7.11开始，开始使用--ssl-mode参数，而抛弃/丢弃了参数--ssl，所以之前的MySQL版本不会遇到这个告警信息。关于这方面的知识，具体参考下面官方文档资料：

MySQL client programs now support an --ssl-mode option that enables you to specify the security state of the connection to the server. Permitted option values are PREFERRED (establish an encrypted connection if the server supports the capability, falling back to an unencrypted connection otherwise), DISABLED (establish an unencrypted connection), REQUIRED (establish an encrypted connection, or fail), VERFIFY_CA (like REQUIRED, but additionally verify the server certificate), VERIFY_IDENTITY (like VERIFY_CA, but additionally verify that the server certificate matches the host name to which the connection is attempted). For backward compatibility, the default is PREFERRED if --ssl-mode is not specified.

These clients support --ssl-mode: mysql, mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlshow, mysqlpump, mysqlslap, mysqltest, mysql_upgrade.

The --ssl-mode option comprises the capabilities of the client-side --ssl and --ssl-verify-server-cert options. Consequently, both of those options are now deprecated and will be removed in a future MySQL version. Use --ssl-mode=REQUIRED instead of --ssl=1 or --enable-ssl. Use --ssl-mode=DISABLED instead of --ssl=0, --skip-ssl, or --disable-ssl. Use --ssl-mode=VERIFY_IDENTITY instead of --ssl-verify-server-cert options. (The server-side --ssl option is not deprecated.)

For the C API, the new MYSQL_OPT_SSL_MODE option for mysql_options() corresponds to the --ssl-mode option. The MYSQL_OPT_SSL_ENFORCE and MYSQL_OPT_SSL_VERIFY_SERVER_CERT options for mysql_options() correspond to the client-side --ssl and --ssl-verify-server-cert options. They are now deprecated and will be removed in a future MySQL version. Use MYSQL_OPT_SSL_MODE with an option value of SSL_MODE_REQUIRED or SSL_MODE_VERIFY_IDENTITY instead.

For more information, see Command Options for Encrypted Connections, and mysql_options().

In consequence of this change, the minor C API version number was incremented

所以这里有两个解决方案，都非常简单：

1：在配置文件里面设置CONFIG_mysql_dump_usessl="no"后即可解决。简单快捷，不用修改代码

# Use ssl encryption with mysqldump

CONFIG_mysql_dump_usessl="no"

2：修改脚本automysqlbackup，具体操作，将脚本中的--ssl选项替换为--ssl-mode。治标治本。当然最正确的方法是根据MySQL版本选择参数。

2：解决上面问题后，报错的提示变为下面这个样子，之前这篇博客“MySQL备份还原——AutoMySQLBackup介绍”中介绍的方法不灵了。因为MySQL版本变化了。输出信息变化了。之前的方法自然失灵了。世界总是变化的，很难有一成不变的事物！

###### WARNING ######

Errors reported during AutoMySQLBackup execution.. Backup failed

Error log below..

mysql: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqlshow: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

修改脚本automysqlbackup（这个脚本视Linux版本不同，位置有所不同，一般位于/usr/local/bin/automysqlbackup或/usr/bin/automysqlbackup下面），在removeIO后面加上这段代码解决这个问题：

# Remove annoying warning message since MySQL 5.7

if [[ -s "$log_errfile" ]]; then

      sedtmpfile="/tmp/$(basename $0).$$.tmp"

      grep -v "mysqldump: [Warning] Using a password on the command line interface can be insecure." "$log_errfile" | 

      grep -v "mysql: [Warning] Using a password on the command line interface can be insecure."  | 

      grep -v "mysqlshow: [Warning] Using a password on the command line interface can be insecure."  > $sedtmpfile

      mv $sedtmpfile $log_errfile

fi

如果你能驾驭工具，自然得心应手。随着平台环境、版本的变化，出现的各种问题都能被你解决。如果你只是被动的使用工具，那么遇到一点小问题，就会束手无策。工作这么多年，感觉DBA的硬技能中的一非常重要的能力：解决问题的能力。这个能力需要不断通过实战锻炼、培养、升级！

参考资料：

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-11.html