Emergency

Galera Cluster xtrabackup SST not working

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Galera Cluster xtrabackup SST not working

    dear users,
    i have a Problem with the sync in galera.


    have installed xtrabackup and mysql + galera.

    i have (for test) 2 nodes. node1 10.0.0.241, node2 10.0.0.242.

    so my config in node1:
    # All files in this package is subject to the GPL v2 license
    # More information is in the COPYING file in the top directory of this package.
    # Copyright (C) 2011 severalnines.com

    [mysqld]
    user=mysql
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    basedir = /usr
    datadir = /var/lib/mysql

    port=3306
    log_error=error.log
    #log_output=FILE
    #relay_log=relay-bin
    ### INNODB OPTIONS
    innodb_buffer_pool_size=2262M
    innodb_additional_mem_pool_size=20M
    innodb_flush_log_at_trx_commit=2
    innodb_file_per_table=1
    HTML Code:
    [HTML]
    [/HTML]
    ## 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=512M
    innodb_log_buffer_size=64M
    innodb_buffer_pool_instances=4
    innodb_log_files_in_group=2
    innodb_thread_concurrency=64
    #innodb_file_format=barracuda
    innodb_flush_method = O_DIRECT
    innodb_locks_unsafe_for_binlog=1
    innodb_autoinc_lock_mode=2
    ## avoid statistics update when doing e.g show tables
    innodb_stats_on_metadata=0

    engine_condition_pushdown=1

    default_storage_engine=innodb

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

    # REPLICATION SPECIFIC
    #server_id must be unique across all mysql servers participating in replication.
    #server_id=SERVERID
    #auto_increment_increment=2
    #auto_increment_offset=SERVERID
    binlog_format=ROW
    #log_bin=binlog
    #log_slave_updates=1
    #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=500
    thread_cache_size=512
    query_cache_type = 0
    query_cache_size = 0
    table_open_cache=1024
    lower_case_table_names=0
    ##
    ## WSREP options
    ##

    # Full path to wsrep provider library or 'none'
    wsrep_provider=/usr/lib/galera/libgalera_smm.so

    wsrep_node_address=10.0.0.241
    # Provider specific configuration options
    wsrep_provider_options="gcache.size=8192M"

    # Logical cluster name. Should be the same for all nodes.
    wsrep_cluster_name="my_wsrep_cluster"

    # Group communication system handle
    wsrep_cluster_address=gcomm://

    # Human-readable node name (non-unique). Hostname by default.
    wsrep_node_name=10.0.0.241

    # Address for incoming client connections. Autodetect by default.
    #wsrep_node_incoming_address=

    # How many threads will process writesets from other nodes
    wsrep_slave_threads=8

    # 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=1

    # 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=xtrabackup

    # 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=

    # 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=rootass

    # Desired SST donor name.
    #wsrep_sst_donor=

    # Protocol version to use
    # wsrep_protocol_version=


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

    [mysqldump]
    max_allowed_packet = 512M

    [mysqld_safe]
    socket = /var/run/mysqld/mysqld.sock
    nice = 0


    config on node 2 (same as config 1, only the following parameters was changed):
    ...
    wsrep_node_address=10.0.0.242
    wsrep_cluster_address=gcomm://10.0.0.241
    wsrep_node_name=10.0.0.242
    ...

    on node 1 have done a (after mysql Server starts):
    mysql -u root --execute="SET wsrep_on = OFF; GRANT ALL ON *.* TO user@'%' identified by 'pass';"

    so the node 1 i can start without Problems. but if i start the node 2 i get the following error(s) on node 1:

    /var/lib/mysqL/error.log
    141128 11:34:22 [ERROR] WSREP: Failed to read from: wsrep_sst_xtrabackup --role 'donor' --address '10.0.0.242:4444/xtrabackup_sst' --auth 'userass' --socket '/var/run/mysqld/mysqld.sock' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --gtid '06ab79ea-76ea-11e4-bfd5-9f746c398e32:1'

    /var/lib/mysql//innobackup.backup.log:
    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

    Get the latest version of Percona XtraBackup, documentation, and help resources:
    http://www.percona.com/xb/p

    141128 11:34:22 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup;mysql_s ocket=/var/run/mysqld/mysqld.sock' as 'user' (using password: YES).
    innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup;mysql_s ocket=/var/run/mysqld/mysqld.sock','user',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at /usr//bin/innobackupex line 2945

    why? i only will connect via the replication user (user).

    can anybody help me?

    thanks

  • #2
    It seems like 'root' user has no privileges to connect to node1 (donor) for SST. I would suggest to create dedicated SST user on node1 and use wsrep_sst_auth in node2 my.cnf This all is well covered here http://www.percona.com/doc/percona-x...ots_howto.html

    Comment


    • #3
      It could also be that your firewall is blocking access on port 4444. The configuration guides I've seen tend to leave that one out of the firewall setting steps, for some reason.

      Comment

      Working...
      X