Last active
September 24, 2020 15:08
-
-
Save sysadminbp/ad8ea2b2d1f31b80d83ac9242392f782 to your computer and use it in GitHub Desktop.
This file contains hidden or 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
| First of all you need two identical mysql versions of mysql, example: 5.7.2 and 5.7.32. 5.7.X with not-5.7.x WILL WORK ONE SIDE ONLY! | |
| ================= my.cnf configuration ================== | |
| -----master (1.1.1.1) (where database is located now) | |
| [mysqld] | |
| # Unique identifier | |
| server_id = 1 | |
| # [master] incrementation of auto-increment ID in the tables - 1 3 5 .... | |
| auto-increment-increment = 2 | |
| auto-increment-offset = 1 | |
| # Prevent infinite loops caused by circular replication | |
| replicate-same-server-id=0 | |
| log_bin = /var/log/mysql/mysql-bin.log | |
| log_bin_index = /var/log/mysql/mysql-bin.log.index | |
| relay_log = /var/log/mysql/mysql-relay-bin.log | |
| relay_log_index = /var/log/mysql/mysql-relay-bin.index | |
| relay-log-info-file = /var/log/mysql/mysql-relay-log.info | |
| #Master information file | |
| master-info-file = /var/log/mysql/mysql-master.info | |
| log-error = /var/log/mysql/mysql.err | |
| expire_logs_days = 10 | |
| max_binlog_size = 1000M | |
| bind-address = 0.0.0.0 | |
| binlog-do-db = mydatabase | |
| replicate-do-db = mydatabase | |
| binlog_checksum = NONE | |
| ----------slave (2.2.2.2) | |
| [mysqld] | |
| # Unique identifier | |
| server_id = 2 | |
| # [master] incrementation of auto-increment ID in the tables - 2 4 6 .... | |
| auto-increment-increment = 2 | |
| auto-increment-offset = 2 | |
| # Prevent infinite loops caused by circular replication | |
| replicate-same-server-id=0 | |
| #[master] binlog | |
| log_bin = /var/log/mysql/mysql-bin.log | |
| log_bin_index = /var/log/mysql/mysql-bin.log.index | |
| #[slave] logs from master | |
| relay_log = /var/log/mysql/mysql-relay-bin.log | |
| relay_log_index = /var/log/mysql/mysql-relay-bin.index | |
| relay-log-info-file = /var/log/mysql/mysql-relay-log.info | |
| #Master information file | |
| master-info-file = /var/log/mysql/mysql-master.info | |
| log-error = /var/log/mysql/mysql.err | |
| expire_logs_days = 10 | |
| max_binlog_size = 1000M | |
| bind-address = 0.0.0.0 | |
| binlog-do-db = mydatabase | |
| replicate-do-db = mydatabase | |
| binlog_checksum = NONE | |
| ------------- | |
| "systemctl restart mysql" on both servers | |
| -------------- | |
| ============= Grant replication privileges ============== | |
| Master (ip 1.1.1.1) | |
| mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'2.2.2.2' IDENTIFIED BY 'password'; | |
| Master (ip 2.2.2.2) | |
| mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'1.1.1.1' IDENTIFIED BY 'password'; | |
| Check connection: | |
| [email protected]# mysql -u rept_user -p -h 2.2.2.2 | |
| [email protected]# mysql -u rept_user -p -h 1.1.1.1 | |
| ==== Dumping database on master (where mydatabase is located) and connecting master (1.1.1.1) to slave (2.2.2.2) ==== | |
| mysql> use mydatabase; | |
| mysql> FLUSH TABLES WITH READ LOCK; | |
| mysql> SHOW MASTER STATUS; | |
| +------------------+----------+--------------+------------------+-------------------+ | |
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | | |
| +------------------+----------+--------------+------------------+-------------------+ | |
| | mysql-bin.000002 | 435960 | mydatabase | | | | |
| +------------------+----------+--------------+------------------+-------------------+ | |
| 1 row in set (0.00 sec) | |
| Now dump database in other ssh session | |
| [email protected]# mysqldump mydatabase > mydatabase-dump.sql | |
| mysql> UNLOCK TABLES; (in the first terminal) | |
| Copy database to 2.2.2.2: | |
| [email protected]# scp mydatabase-dump.sql 2.2.2.2: | |
| on slave (2.2.2.2) | |
| mysql> create database mydatabase; | |
| [email protected]# mysql -u root -p mydatabase < mydatabase-dump.sql | |
| mysql> STOP SLAVE; | |
| mysql> CHANGE MASTER TO master_host='1.1.1.1', master_port=3306, master_user='repl_user', master_password='password', master_log_file='mysql-bin.000002', master_log_pos=435960; | |
| mysql> START SLAVE; | |
| =================== Connecting slave (1.1.1.1) to master (2.2.2.2) ======================= | |
| On 2.2.2.2 | |
| mysql> use mydatabase; | |
| mysql> FLUSH TABLES WITH READ LOCK; | |
| mysql> SHOW MASTER STATUS; | |
| +------------------+----------+--------------+------------------+-------------------+ | |
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | | |
| +------------------+----------+--------------+------------------+-------------------+ | |
| | mysql-bin.000004 | 2341 | mydatabase | | | | |
| +------------------+----------+--------------+------------------+-------------------+ | |
| On 1.1.1.1 fill with data from 2.2.2.2: | |
| STOP SLAVE; | |
| CHANGE MASTER TO master_host='2.2.2.2', master_port=3306, master_user='repl_user', master_password='password', master_log_file='mysql-bin.000004', master_log_pos=2341; | |
| START SLAVE; | |
| On 2.2.2.2: | |
| mysql> UNLOCK TABLES; | |
| ========= Useful commands ==== | |
| show master status; --- show current position of master | |
| show slave status; ---- show status of slave | |
| stop slave; --- do not continue replication from master | |
| ====== TIPS ===== | |
| - Use strong password but not too large up to 20 is ok. | |
| - Block access to port 3306 to other hosts. | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment