Created
January 6, 2017 10:53
-
-
Save tuwid/25649b159a0bc6ede133031689e38a3f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ----- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment