mysql_multi.sh (perl) issue.

[aplsms]

Hello Sebastian,

I have couple of issues with your scripts:

1. If option value has a spaces it wrongly processed by the script:
   for example: init-connect = 'SET NAMES utf8'
   on the result we will have mysqld .... --init-connect=SET NAMES utf8 --character-set-server=utf8...
   fix:

sub defaults_for_group
{
  my ($group) = @_;

  return () unless $my_print_defaults_exists;

  my $com= join ' ', 'my_print_defaults', @defaults_options, $group;
  my @defaults = `$com`;
  chomp @defaults;
  for (my $j = 0; defined($defaults[$j]); $j++)
  {
    if ($defaults[$j] =~ /\ /)
    {
      warn "Option $options[$j] with spaces\n";
      my ($opt_name,$opt_val) = split( "=", $defaults[$j]);
      $defaults[$j] = $opt_name."=\'".$opt_val."\'";
    }
  }
  return @defaults;
}

1. 1st node on cluster should be running with --wsrep-new-cluster for initiating replication. Could you please add this tag?
2. (my big issue) I have a problem to start replication (with method rsync or xtrabackup-v2)
   my configs

[mysqld_multi]
mysqld     = /usr/sbin/mysqld
mysqladmin = /usr/bin/mysqladmin
user       = root
password   = mysql
log        = /var/log/mysqld_multi.log

[mysqld1]
#check IP
bind-address             = 10.211.55.13
socket                   = /var/run/mysql-001/mysqld.sock
port                     = 3307
pid-file                 = /var/run/mysql-001/mysqld.pid
datadir                  = /var/lib/mysql-001/
user                     = mysql
tmpdir                   = /var/lib/mysql-001/tmp
log-bin                  = /var/lib/mysql-001/mysql-bin
log-bin-index            = /var/lib/mysql-001/binlog.index
log-error                = /var/log/mysql-001/mysql.err
general_log_file         = /var/log/mysql-001/mysql.log
general_log              = 1
expire-logs-days         = 2

wsrep_provider           = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name       = "openstack"
wsrep_cluster_address  = "gcomm://110.211.55.12:5674,10.211.55.13:5674?pc.wait_prim=no"
wsrep_provider_options = "gmcast.listen_addr=tcp://10.211.55.13:5674"

wsrep_slave_threads      = 12
wsrep_sst_method         = rsync
wsrep_sst_auth           = wsrep_sst:dhkcfrLJ
wsrep_node_address       = 110.211.55.13


# Must have and Performance values
binlog_format           = ROW
default-storage-engine  = innodb
max_connections         = 16384
collation-server        = utf8_general_ci
init-connect            = 'SET NAMES utf8'
character-set-server    = utf8
myisam_sort_buffer_size = 64M
wait_timeout            = 1800
open_files_limit        = 102400
table_open_cache        = 10000
key_buffer_size         = 64M
max_allowed_packet      = 256M
query_cache_size        = 0
query_cache_type        = 0
sync_binlog             = 0
binlog-ignore-db        = zabbix

skip-external-locking
skip-name-resolve

innodb_file_format             = Barracuda
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 3201M
innodb_log_file_size           = 640M
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_io_capacity             = 500
innodb_flush_log_at_trx_commit = 2
innodb_flush_method            = O_DIRECT
innodb_doublewrite             = 0
innodb_autoinc_lock_mode       = 2
innodb_locks_unsafe_for_binlog = 1

[mysqld2]
#check IP
bind-address             = 10.211.55.13
socket                   = /var/run/mysql-002/mysqld.sock
port                     = 3308
pid-file                 = /var/run/mysql-002/mysqld.pid
datadir                  = /var/lib/mysql-002/
user                     = mysql
tmpdir                   = /var/lib/mysql-002/tmp
log-bin                  = /var/lib/mysql-002/mysql-bin
log-bin-index            = /var/lib/mysql-002/binlog.index
log-error                = /var/log/mysql-002/mysql.err
general_log_file         = /var/log/mysql-002/mysql.log
general_log              = 1
expire-logs-days         = 2

wsrep_provider           = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name       = "openstack"
wsrep_cluster_address  = "gcomm://110.211.55.12:5675,10.211.55.13:5675?pc.wait_prim=no"
wsrep_provider_options = "gmcast.listen_addr=tcp://10.211.55.13:5675"

wsrep_slave_threads      = 12
wsrep_sst_method         = xtrabackup-v2
wsrep_sst_auth           = wsrep_sst:dhkcfrLJ
wsrep_node_address       = 110.211.55.13


# Must have and Performance values
binlog_format           = ROW
default-storage-engine  = innodb
max_connections         = 16384
collation-server        = utf8_general_ci
init-connect            = 'SET NAMES utf8'
character-set-server    = utf8
myisam_sort_buffer_size = 64M
wait_timeout            = 1800
open_files_limit        = 102400
table_open_cache        = 10000
key_buffer_size         = 64M
max_allowed_packet      = 256M
query_cache_size        = 0
query_cache_type        = 0
sync_binlog             = 0
binlog-ignore-db        = zabbix

skip-external-locking
skip-name-resolve

innodb_file_format             = Barracuda
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 3201M
innodb_log_file_size           = 640M
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_io_capacity             = 500
innodb_flush_log_at_trx_commit = 2
innodb_flush_method            = O_DIRECT
innodb_doublewrite             = 0
innodb_autoinc_lock_mode       = 2
innodb_locks_unsafe_for_binlog = 1

[mysqld_safe]
syslog
log_error=/var/log/mysql_error.log

[xtrabackup]
parallel=6

[sst]
streamfmt=xbstream
transferfmt=socat
sockopt=,nodelay,sndbuf=1048576,rcvbuf=1048576

root@ubuntu-12:~# dpkg -l|grep my
ii  iproute                             1:3.12.0-2ubuntu1                all          transitional dummy package for iproute2
ii  libdbd-mysql-perl                   4.025-1                          amd64        Perl5 database interface to the MySQL database
ii  libmysqlclient18                    5.5.48+maria-1~trusty            amd64        Virtual package to satisfy external depends
ii  mariadb-common                      5.5.48+maria-1~trusty            all          MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
ii  module-init-tools                   15-0ubuntu6                      all          transitional dummy package (module-init-tools to kmod)
ii  mysql-common                        5.5.48+maria-1~trusty            all          MariaDB database common files (e.g. /etc/mysql/my.cnf)

root@ubuntu-12:~# dpkg -l|grep perco
ii  percona-xtrabackup                  2.3.2-1.trusty                   amd64        Open source backup tool for InnoDB and XtraDB

I have 2 VM with IP 110.211.55.12 and 110.211.55.13, on the second node ip in config is 110.211.55.12.

I can add logs if you need.

Thank you in advance.

[aplsms]

after fixing typo in ip address i'm able to run rsync replication. but not xtrabackup-v2

error:

160318 20:18:05 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 0)
160318 20:18:05 [Note] WSREP: State transfer required:
    Group state: 08333f45-ed81-11e5-9994-d21173fbcd29:0
    Local state: 00000000-0000-0000-0000-000000000000:-1
160318 20:18:05 [Note] WSREP: New cluster view: global state: 08333f45-ed81-11e5-9994-d21173fbcd29:0, view# 2: Primary, number of nodes: 2, my index: 1, protocol version 3
160318 20:18:05 [Warning] WSREP: Gap in state sequence. Need state transfer.
160318 20:18:05 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.211.55.12' --datadir '/var/lib/mysql-002/' --defaults-file '/etc/mysql/my.cnf' --parent '12856''
WSREP_SST: [INFO] Logging all stderr of SST/Innobackupex to syslog (20160318 20:18:05.282)
160318 20:18:05 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.211.55.12' --datadir '/var/lib/mysql-002/' --defaults-file '/etc/mysql/my.cnf' --parent '12856'
    Read: '(null)'
160318 20:18:05 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.211.55.12' --datadir '/var/lib/mysql-002/' --defaults-file '/etc/mysql/my.cnf' --parent '12856': 2 (No such file or directory)
160318 20:18:05 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
160318 20:18:05 [ERROR] Aborting