Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ----- 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 replicausr@8.29.134.10 IDENTIFIED BY 'my_supper_secret_password';
- GRANT ALL PRIVILEGES ON *.* TO replicausr@8.29.134.10 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement