mariadb replication and migration

----- ALL TABLES MUST BE INNODB -- IF NOT REPLICA FAILS!!!

- file_per_table = 1 

- convert to innodb

for DB in $(mysql -u root -e "show databases;" | grep -v "Database")
do
  echo $DB
  mysql -u root $DB -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql -u root $DB
  echo "Sleeping"
  sleep 1
done 


- optimize tables 
mysqlcheck -o --all-databases

- make sure tmp is big enough 
- innodb_log is big enough 

- restart mysql service after convert and optimize



Make sure we have ID on master my.cnf:

log_bin = binlog
binlog_format=row
bind-address = 0.0.0.0
expire-logs-days=3
server-id = 501

On the slave my.cnf: 

server_id=1001

Create replica users on master: 
CREATE USER 'replicausr'@'%' IDENTIFIED BY 'my_supper_secret_password';
GRANT ALL PRIVILEGES ON *.* TO 'replicausr'@'%' IDENTIFIED BY 'my_supper_secret_password' WITH GRANT OPTION;

if you have just one IP you access it from :
CREATE USER [email protected] IDENTIFIED BY 'my_supper_secret_password';
GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY 'my_supper_secret_password' WITH GRANT OPTION;

We can either use xtrabackup or manual procedure: 
(for xtrabackup option keep in mind to have clocks synced )

Manual method: 

on master:

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;
take note of the Poisition field and the file

MariaDB [(none)]> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 | 72894029 | adclk_imobi_tmp,mgd |                  
|+---------------+----------+--------------+------------------+

We do the dump: 

cd /backups/
time mysqldump --all-databases --single-transaction --quick --lock-tables=false > alles.sql

We unlock the tables: 
UNLOCK TABLES;

open SCREEN and into screen 
scp alles.sql root@ip:/backup/

On the slave: 
time mysql -ureplicausr -pmy_supper_secret_password < alles.sql


STOP SLAVE;

CHANGE MASTER TO MASTER_HOST='IP_OF_MASTER', 
MASTER_USER='replicausr', 
MASTER_PASSWORD='my_supper_secret_password', 
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.00_FILE_WE_TOOK_NOTE_EARLIER__',
MASTER_CONNECT_RETRY=10,
MASTER_LOG_POS=__POISTION_WE_TOOK_NOTE_EARLIER__;


START SLAVE;
SHOW SLAVE STATUS \G;

# in case of a few rows might have gotten behind we can just skip the duplicates by doing:  
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=100; START SLAVE;

after everything is synced: 

we just do this on the slave:

STOP SLAVE;
RESET MASTER;
Optional: CHANGE MASTER TO MASTER_HOST='';

and change the application pointer to the slave