xtrabackup无法连接socket和xtrabackup版本与mysql版本支持问题

安装了  xtrabackup 8.0后备份数据库报错

[root@lzl ~]# xtrabackup -uroot -poracle --backup -S=/tmp/mysql.sock -P3306 --target-dir=/data/backup 
xtrabackup: recognized server arguments: --datadir=/data/mysql 
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --socket==/tmp/mysql.sock --port=3306 --target-dir=/data/backup 
xtrabackup version 8.0.13 based on MySQL server 8.0.20 Linux (x86_64) (revision id: fdf0f4c)
200721 14:56:48  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket==/tmp/mysql.sock' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306;mysql_socket==/tmp/mysql.sock','root',...) failed: Can't connect to local MySQL server through socket '=/tmp/mysql.sock' (2) at - line 1535
200721 14:56:48 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: =/tmp/mysql.sock
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '=/tmp/mysql.sock' (2).

 

查看我的sock是否存在，和权限是否正常

[root@lzl ~]# ls -lrt /tmp/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Jul 20 10:14 /tmp/mysql.sock
[root@lzl ~]# 

没问题

 

查看my.cnf socket配置
[root@lzl ~]# cat /etc/my.cnf
[mysqld]
datadir = /data/mysql
socket = /tmp/mysql.sock
character_set_server = utf8
default-time-zone = '+08:00'
port = 3306
[mysqld_safe]
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysqld.pid

也没问题

 

测试mysql直接登录
[root@lzl ~]# mysql -uroot -poracle 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

没问题

 

最后发现：报错中的信息有两个=

mysql_socket==/tmp/mysql.sock',

把-S=中的=删掉，直接跟socket文件
[root@lzl ~]# xtrabackup -uroot -poracle --backup -S/tmp/mysql.sock -P3306 --target-dir=/data/backup --defaults-group=mysql 
xtrabackup: recognized server arguments: --datadir=/data/mysql --defaults_group=mysql 
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --socket=/tmp/mysql.sock --port=3306 --target-dir=/data/backup 
xtrabackup version 8.0.13 based on MySQL server 8.0.20 Linux (x86_64) (revision id: fdf0f4c)
200721 15:34:37  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
200721 15:34:37  version_check Connected to MySQL server
200721 15:34:37  version_check Executing a version check against the server...
200721 15:34:37  version_check Done.
200721 15:34:37 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
Error: Unsupported server version: '5.7.20'.
This version of Percona XtraBackup can only perform backups and restores against MySQL 8.0 and Percona Server 8.0
Please use Percona XtraBackup 2.4 for this database.

ok，报错不一样了，说明socket问题已解决

 

最新的报错显示8.0的xtrabackup不支持8.0以下的mysql，让我去下XtraBackup 2.4

重新下载2.4 的xtrabackup，删除8.0的xtrabackup
[root@lzl soft]# rpm -e percona-xtrabackup-80-8.0.13-1.el6.x86_64
[root@lzl soft]# xtrabackup
-bash: /usr/bin/xtrabackup: No such file or directory

删除完成

 

安装xtrabackup 2.4
[root@lzl soft]# rpm -ivh --nodeps percona-xtrabackup-24-2.4.20-1.el6.x86_64.rpm 
warning: percona-xtrabackup-24-2.4.20-1.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing...                ########################################### [100%]
   1:percona-xtrabackup-24  ########################################### [100%]

[root@lzl soft]# xtrabackup -version
xtrabackup: recognized server arguments: --datadir=/data/mysql 
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)

ok，低版本xtrabackup软件安装完成

 

再次尝试备份数据库

xtrabackup -uroot -poracle --backup -S/tmp/mysql.sock -P3306 --target-dir=/data/backup --defaults-group=mysql 
...
200721 16:28:26 [00]        ...done
xtrabackup: Transaction log of lsn (2667853) to (2667862) was copied.
200721 16:28:27 completed OK!

备份完成

查看备份文件

[root@lzl soft]# ls -lrt /data/backup/
total 12332
-rw-r----- 1 root root 12582912 Jul 21 16:28 ibdata1
drwxr-x--- 2 root root     4096 Jul 21 16:28 lzldb
drwxr-x--- 2 root root     4096 Jul 21 16:28 performance_schema
drwxr-x--- 2 root root    12288 Jul 21 16:28 sys
drwxr-x--- 2 root root     4096 Jul 21 16:28 mysql
-rw-r----- 1 root root     2560 Jul 21 16:28 xtrabackup_logfile
-rw-r----- 1 root root      135 Jul 21 16:28 xtrabackup_checkpoints
-rw-r----- 1 root root      324 Jul 21 16:28 ib_buffer_pool
-rw-r----- 1 root root      487 Jul 21 16:28 backup-my.cnf
-rw-r----- 1 root root      480 Jul 21 16:28 xtrabackup_info
备份成功

 

总结

1 xtrabackup备份时在指定socket时，-S不要跟=，直接-S跟目录和文件名

2 xtrabackup 8不支持mysql 5.7及以下的备份，这可能跟mysql 8.0数据字典保存方式的升级有关。总之mysql 8.0就使用xtrabackup 8，mysql 5.7及以下需要使用低版本xtrabackup，如xtrabackup 2.4