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 [url]https://www.percona.com/blog/2015/02/20/how-to-setup-a-pxc-cluster-with-gtids-and-have-async-slaves-replicating-from-it/[/url].
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
