mysql_multi.sh (perl) issue.
aplsms opened this issue · 1 comments
aplsms commented
Hello Sebastian,
I have couple of issues with your scripts:
- 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;
}- 1st node on cluster should be running with
--wsrep-new-clusterfor initiating replication. Could you please add this tag? - (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 commented
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