MySQL : Replication

From kraba notes
Jump to: navigation, search


Info

A list of classic/new mysql replication. Tested on ubuntu 16.04 with cancerd systemd.

Servers :

Name IP Type
mysql1 10.0.0.2 masterA
myql2 10.0.0.3 masterB/slaveA

Classic replica master-slave

  • On mysql1 masterA:
vim /etc/mysql/mysql.conf.d/mysqld.cnf

#bind-address = 127.0.0.1 
server-id = 1
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
relay_log_index = /var/log/mysql/mysql-relay-bin.index
binlog_do_db = mydb1
binlog_do_db = mydb2
binlog_do_db = mydb3

Restart service

service mysql restart (or systemctl restart mysql on cancerd systemd)

Login to mysql:

mysql -u root -p

mysql > FLUSH TABLES WITH READ LOCK;
mysql > create user 'replica'@'%' identified by 'replicapassword';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
mysql > FLUSH PRIVILEGES;
mysql > show master status;

+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                       
|
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| mysql-bin.000001 |   590133 |              |                  | afe95c42-d6f3-11e7-8a1a-fa163ecd2d53:1-2158,
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Perform a dump of DB:

mysqldump -u root -p --opt mydb1 > mydb1.sql
mysqldump -u root -p --opt mydb3 > mydb2.sql
mysqldump -u root -p --opt mydb3 > mydb3.sql

Unlock mysql:

mysql -u root -p

mysql > UNLOCK TABLES;

Save these values:

  1. File : mysql-bin.000001
  2. Position : 590133

And finally transfer the sql dump to the other DB machine.

  • On mysql2 slaveA:
vim /etc/mysql/mysql.conf.d/mysqld.cnf

#bind-address = 127.0.0.1 
server-id = 2
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
relay_log_index = /var/log/mysql/mysql-relay-bin.index
binlog_do_db = mydb1
binlog_do_db = mydb2
binlog_do_db = mydb3


Restart service

service mysql restart (or systemctl restart mysql on cancerd systemd)

Login to mysql and create the replica DBs:

mysql -u root -p

mysql > stop slave; 
mysql > create database mydb1;
mysql > create database mydb2;
mysql > create database mydb3;

And import the DB dump:

mysql -u root -p mydb1 < mydb1.sql
mysql -u root -p mydb2 < mydb2.sql 
mysql -u root -p mydb3 < mydb3.sql

Login to mysql:

mysql -u root -p

mysql > CHANGE MASTER TO MASTER_HOST = '10.0.0.2', MASTER_USER = 'replica', MASTER_PASSWORD = 'replicapassword', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 590133; 
mysql > start slave;

Now replication is running.

  • Check if it's working:

On masterA:

SHOW MASTER STATUS;
SHOW SLAVE HOSTS;

Error log :

[ERROR] Slave I/O for channel : error connecting to master 'replica@10.0.0.2:3306' - retry-time: 60  retries: 1, Error_code: 1045
Check password or MASTER string.

On slaveA:

SHOW SLAVE STATUS\G;

On both server:

SHOW PROCESSLIST \G;

Comments

blog comments powered by Disqus