This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

No GTID in xtrabackup_binlog_info

Hi,

I'm setting up a slave for a multi-master cluster, following this post: https://mariadb.com/kb/en/library/using-mariadb-replication-with-mariadb-galera-cluster-configuring-mariadb-r/#start-replication-on-the-new-slave The cluster is up and running, but I can't manage to obtain the GTID to start the slave. The backup from one of the masters is done correctly, then prepared, the restored on the slave, however I can't find the GTID as it is not present in the xtrabackup_binlog_info.

cat /var/mariadb/backup/xtrabackup_binlog_info
binlog.000006	59317700

When I try to find more information with the mysql variables on the master, I don't get much more:

MariaDB [(none)]> show master status \G
*************************** 1. row ***************************
            File: binlog.000004
        Position: 69145989
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)

MariaDB [(none)]> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_annotate_row_events              | ON                   |
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_commit_wait_count                | 0                    |
| binlog_commit_wait_usec                 | 100000               |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_file_cache_size                  | 16384                |
| binlog_format                           | ROW                  |
| binlog_optimize_thread_scheduling       | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_stmt_cache_size                  | 32768                |
| encrypt_binlog                          | OFF                  |
| gtid_binlog_pos                         | 0-4-57912            |
| gtid_binlog_state                       | 0-3-29474,0-4-57912  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| read_binlog_speed_limit                 | 0                    |
| sync_binlog                             | 0                    |
| wsrep_forced_binlog_format              | NONE                 |


MariaDB [(none)]> SELECT @@GLOBAL.gtid_current_pos\G
*************************** 1. row ***************************
@@GLOBAL.gtid_current_pos:

I think that I have all the options set correctly, but it doesn't seem to be running correctly. Here is a copy of one of my masters' my.cnf:

[MYSQLD]
bind-address = 172.16.0.12
user=mysql
basedir=/usr/
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
pid_file=mysqld.pid
port=3306

### LOG OPTIONS
log_error=error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 5
log_slow_verbosity  = query_plan

### INNODB OPTIONS
innodb_buffer_pool_size=11489M
#obsolete in mariadb 10.2
#innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=0
innodb_file_per_table=1
innodb_data_file_path = ibdata1:100M:autoextend
## You may want to tune the below depending on number of cores and disk sub
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_doublewrite=1
innodb_log_file_size=1024M
innodb_log_buffer_size=96M
innodb_buffer_pool_instances=8
innodb_log_files_in_group=2
innodb_thread_concurrency=0
innodb_file_format=barracuda
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
# Obsolete, set by default if supported and unset if unsupported
#engine_condition_pushdown=1
default_storage_engine=innodb

##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="cluster1"
wsrep_node_name='server1'
wsrep_node_address='172.16.0.12'
wsrep_provider_options="gcache.size=128M;gcache.page_size=1G;base_host=172.16.0.12;gmcast.listen_addr=tcp://172.16.0.12:4567"
wsrep_cluster_address="gcomm://172.16.0.12,172.16.0.11,172.16.0.9"

wsrep_node_incoming_address='172.16.0.12'
wsrep_slave_threads=16

# CHARACTER SET
#collation_server = utf8_unicode_ci
#init_connect='SET NAMES utf8'
#character_set_server = utf8

# REPLICATION SPECIFIC - GENERAL
#server_id must be unique across all mysql servers participating in replication.
server_id=3
# auto_increment_increment corresponds to the number of masters in the multimaster cluster
auto_increment_increment=3
# auto_increment_offset avoids issues of conflicts between two simultaneous writes in two masters.
auto_increment_offset=3
# REPLICATION SPECIFIC
binlog_format=ROW
log_bin=binlog
#relay_log=relay-bin
#expire_logs_days=7
# OTHER THINGS, BUFFERS ETC
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
#sort_buffer_size = 256K
#read_buffer_size = 256K
#read_rnd_buffer_size = 512K
#myisam_sort_buffer_size = 8M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=200
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=1024
lower_case_table_names=0

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Location of the directory with data files. Needed for non-mysqldump
# state snapshot transfers. Defaults to mysql_real_data_home.
#wsrep_data_home_dir=

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=2

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# replicate myisam
wsrep_replicate_myisam=1
# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
#wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
# ClusterControl currently DOES NOT support wsrep_sst_method=mysqldump
#wsrep_sst_method=rsync
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=mariabackup

# Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
wsrep_sst_receive_address=172.16.0.12

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
wsrep_sst_auth=<redacted>:<redacted>

# Desired SST donor name.
#wsrep_sst_donor=

# Protocol version to use
# wsrep_protocol_version=

# GTIDs for replication between masters and slave
wsrep_gtid_mode=ON
# wsrep_gtid_domain_id must be the same on all nodes
wsrep_gtid_domain_id=42
#
log_slave_updates=ON
# gtid_domain_id must be distinct on each node AND distinct from wsrep_gtid_domain_id
gtid_domain_id=11

[MYSQL]
socket=/var/run/mysqld/mysqld.sock
#default_character_set=utf8

[client]
socket=/var/run/mysqld/mysqld.sock
#default_character_set=utf8

[mysqldump]
max_allowed_packet = 512M
#default_character_set=utf8

[MYSQLD_SAFE]
pid_file=mysqld.pid
log_error=error.log
basedir=/usr/
datadir=/var/lib/mysql

I'm a bit lost, do someone see something evident that I missed? Thanks,

Simon

Answer Answered by Simon Gareste in this comment.

I think I finally may have found why: in https://mariadb.com/kb/en/library/setting-up-replication/ it says that it is recommended to use GTIDs, and that they were introduced in 10.0. GIven that my cluster was upgraded version by version, I never saw that and never did the update:

CHANGE MASTER TO MASTER_USE_GTID = slave_pos;

Also it seems that you can start the slave with

CHANGE MASTER TO
  MASTER_HOST='master.domain.com',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000096',
  MASTER_LOG_POS=568;

which is on the same page, but I coulnd't find anywhere else. So I guess this is solved.

Simon

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.