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