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][HTML][/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=root:pass
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 ‘user:pass’ --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:
[url]Percona XtraBackup - MySQL Database Backup Software
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