before start:
- in the source master-master all the databases must be in the innodb format since is the only supported storage engine for Galera cluster. If not, a conversion is needed.
- in the destination, three nodes with percona cluster 5.5 need to be installed and should be remain in stopped state. Ubuntu 14.04 is used in this scenario
here below an example of my.cnf config, all the node must have the same config
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
symbolic-links = 0
log_slow_verbosity = full
slave_compressed_protocol = 1
default_storage_engine = InnoDB
log_slave_updates = 1
log_bin = bin-log
binlog_format = ROW
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_on=ON
wsrep_node_address=db01
wsrep_provider_options="gcache.size=10240M; gmcast.segment=0"
wsrep_cluster_name="my_wsrep_cluster"
wsrep_cluster_address=gcomm://db01:4567,db02:4567,db03:4567
wsrep_node_name=db01
wsrep_node_incoming_address=
wsrep_slave_threads=4
wsrep_certify_nonPK=1
wsrep_data_home_dir=
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_notify_cmd=
wsrep_sst_method=xtrabackup-v2
wsrep_sst_receive_address=
wsrep_sst_auth=bkp_usr:bkp_psw
innodb_flush_log_at_trx_commit = 2
innodb_autoinc_lock_mode=2
server_id=1
sync_binlog=0
innodb_read_io_threads=4
innodb_write_io_threads=4
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
!includedir /etc/mysql/conf.d/
take care that the following parameters must be unique
server-id=1
wsrep_node_name=db01
wsrep_node_address=db01
now we are ready to start backup the master (innobackupex come from percona-xtrabackup package) take note of the innodb_log_file parameters and master log positions
sudo innobackupex --user=cmon --password=psw /tmp/
sudo innobackupex --apply-log /tmp
modify the destionation mysql config with the proposed innodb config here below is just an example
innodb_data_home_dir = ./
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = ./
innodb_log_files_in_group = 1
innodb_log_file_size = 34855993344
restore to the data to one of the percona 5.5 nodes
sudo mkdir /var/lib/mysqlold
sudo rsync -av /var/lib/mysql/ /var/lib/mysqlold/
sudo rm -rf /var/lib/mysql/*
sudo rsync -avcn -e ssh master1:/tmp/2016-11-08_22-39-19/ /var/lib/mysql/
sudo chown mysql:mysql -R /var/lib/mysql
start first galera node
sudo /usr/sbin/mysqld --wsrep-new-cluster
now we have a galera cluster with just one node take the bin log coordinates before start the first connection between percona 5.1 and percona 5.5
cat xtrabackup_binlog_info
the output should be something like that
mysql-bin.000003 2504807
upgrade the schema
mysql_upgrade
if error SQL: Error ‘Cannot add or update a child row: a foreign key constraint Error_code: … 1452 is encountered and is not possible to correct it manually, consider to use slave-skip-error parameter as last chance becouse an inconsistency problem may occur
slave-skip-errors=1452
start first slave sync from the percona 5.1 node to the percona 5.5 galera node, remember to populate accordingly the value of master_log_file and master_log_pos
slave stop;
change master to master_host='dbsource01', master_user='cmon', master_password='psw', master_log_file = 'mysql-bin.000003', master_log_pos = 2504807;
slave start;
monitor the sync and wait until the seconds between master are zero
watch -n1 "mysql -e 'show slave status \G' | grep -i second"
connect to the others galera nodes and start mysql service
sudo service mysql start
if there are error verify wsrep_sst_auth user that have GRANT RELOAD, LOCK TABLES, REPLICATION permission and also check any dirty /var/lib/mysql/.sst data
restart the first node without –wsrep-new-cluster
sudo service mysql stop
sudo service mysql start
Troubleshooting
If error Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’ is encountered modify max_allowed_packet
show variables like 'max_allowed_packet';
set global max_allowed_packet=33554432;