Not the answer you need?
Register and ask your own question!

Empty binlog_pos in xtrabackup_info?

Ron_JohnsonRon_Johnson ContributorCurrent User Role Beginner
Hi,
# xtrabackup --version
xtrabackup version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)

# mysqld --version
mysqld  Ver 5.7.14-8-57 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel8, Revision a3f9d06, WSREP version 26.17, wsrep_26.17)

I've got an encrypted master-master cluster that I'm trying to add a slave to, and am trying to follow https://www.percona.com/blog/2015/02/20/how-to-setup-a-pxc-cluster-with-gtids-and-have-async-slaves-replicating-from-it/.

I added these rows (since log-bin was already defined to be /var/lib/mysql/binlog) to the my.cnf on all cluster nodes and then restarted.
log_slave_updates
enforce_gtid_consistency=1
gtid_mode=on

However, after I took and prepared a backup, the binlog_pos line of xtrabackup_info is empty. Anyone know why?

(Below is the output from the backup and prepare, plus the my.cnf.)

Thanks
CURDATE=$(date +%Y-%m-%d_%H_%M_%S)
DEST=/datadrive/backups/$CURDATE
KEYRING=/usr/local/mysql/mysql-keyring/keyring
SRVID=6
mkdir $DEST
chown mysql:mysql $DEST
xtrabackup --backup \
           --target-dir=$DEST \
           --user=root --password=Three.Blind.Mice \
           --keyring-file-data=$KEYRING \
           --server_id=$SRVID
161219 21:37:31  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'root'  (using password: YES).
161219 21:37:31  version_check Connected to MySQL server
161219 21:37:31  version_check Executing a version check against the server...
161219 21:37:31  version_check Done.
161219 21:37:31 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
Using server version 5.7.14-8-57-log
xtrabackup version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /datadrive/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 2146435072
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
161219 21:37:31 >> log scanned up to (152601003)
xtrabackup: Generating a list of tablespaces
...
161219 21:37:38 [00] Writing xtrabackup_info
161219 21:37:38 [00]        ...done
xtrabackup: Transaction log of lsn (152600994) to (152601019) was copied.
161219 21:37:38 completed OK!


xtrabackup --prepare \
           --target-dir=$DEST \
           --keyring-file-data=$KEYRING
xtrabackup version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
xtrabackup: cd to /datadrive/backups/2016-12-19_21_37_30
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(152600994)
...
InnoDB: xtrabackup: Last MySQL binlog file position 433, file name binlog.000016

xtrabackup: Recovered WSREP position: 22eab30e-7445-11e6-b4fd-da06db99a246:63445
...
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 152605913
161219 21:38:13 completed OK!

# cat /datadrive/backups/2016-12-19_21_37_30/xtrabackup_info
uuid = 4587f117-c65d-11e6-9092-005056a744ab
name =
tool_name = xtrabackup
tool_command = --backup --target-dir=/datadrive/backups/2016-12-19_21_37_30 --user=root --password=... --keyring-file-data=/usr/local/mysql/mysql-keyring/keyring --server_id=6
tool_version = 2.4.5
ibbackup_version = 2.4.5
server_version = 5.7.14-8-57-log
start_time = 2016-12-19 21:37:31
end_time = 2016-12-19 21:37:38
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 152601010
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

$ cat /etc/my.cnf
[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
datadir=/datadrive/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

server_id=6
bind_address=0.0.0.0

early-plugin-load=keyring_file.so
keyring_file_data=/usr/local/mysql/mysql-keyring/keyring

log_slave_updates
enforce_gtid_consistency=1
gtid_mode=on

connect_timeout=10
skip-name-resolve

innodb_buffer_pool_size=5G
innodb_log_file_size=2047M
innodb_log_buffer_size=128M
innodb_file_per_table=1

wsrep_cluster_address=gcomm://10.0.83.42,10.0.83.43,10.0.83.44
wsrep_node_name=san00006
wsrep_node_address=10.0.83.42
wsrep_sst_auth="sstuser:s3cret"
wsrep_sync_wait=1
wsrep_cluster_name="xxxxxx"
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_slave_threads=8
wsrep_sst_method=rsync

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
sync_binlog=0
log-bin=/var/lib/mysql/binlog
max_connections=512
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    Please try to run the backup with --binlog-info=ON
    xtrabackup --backup --binlog-info=ON --target-dir=$DEST --user=root --password=Three.Blind.Mice --keyring-file-data=$KEYRING --server_id=$SRVID
    
  • Ron_JohnsonRon_Johnson Contributor Current User Role Beginner
    jrivera wrote: »
    Please try to run the backup with --binlog-info=ON
    xtrabackup --backup --binlog-info=ON --target-dir=$DEST --user=root --password=Three.Blind.Mice --keyring-file-data=$KEYRING --server_id=$SRVID
    

    (I probably shouldn't have put the password in this post, should I?? lol)

    That worked!

    Next question: why wasn't that mentioned in the blog post?

    Sincerely,
    Ron
  • jriverajrivera Percona Support Engineer Percona Staff Role
    This feature was available starting PXB 2.3.2 (based on the manual https://www.percona.com/doc/percona-xtrabackup/2.3/advanced/lockless_bin-log.html), and the blog post was written months before that so it was not included there. Also if I'm not mistaken the PXB version used on that blog post was version 2.2.x.
  • Ron_JohnsonRon_Johnson Contributor Current User Role Beginner
    jrivera wrote: »
    This feature was available starting PXB 2.3.2 (based on the manual https://www.percona.com/doc/percona-xtrabackup/2.3/advanced/lockless_bin-log.html), and the blog post was written months before that so it was not included there. Also if I'm not mistaken the PXB version used on that blog post was version 2.2.x.

    Thanks. I was just coming back to edit my post to clarify that I was curious at the missing reference, not righteously outraged. :D
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Ron, for what it's worth I believe this topic is really good for a blog post. If not, at least it needs to be clearer in the docs. :D
  • jriverajrivera Percona Support Engineer Percona Staff Role
    For others who happen to see this forum post, I've submitted a bug report - https://bugs.launchpad.net/percona-xtrabackup/+bug/1651505
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.