si294r/mariadb_replication.md

## mariadb_replication.md

      
    Raw
  

              mariadb_replication.md
            
          
    MariaDB Replication
MASTER
docker create --name mariadb-master -e MYSQL_ROOT_PASSWORD=password -p 3316:3306  mariadb:10.2
docker start mariadb-master
docker exec -it mariadb-master /bin/bash
apt-get update
apt-get install nano
nano /etc/mysql/my.cnf
server-id               = 1
report_host             = master1
exit
docker restart mariadb-master
docker exec -it mariadb-master /bin/bash
ls -la /var/log/mysql/ (check replication log)
mysql -u root -p
CREATE USER 'slave'@'%' IDENTIFIED BY 'password’;
GRANT REPLICATION SLAVE ON . TO 'slave’;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      645 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
UNLOCK TABLES;
exit
exit
SLAVE 1
docker create --name mariadb-slave1 -e MYSQL_ROOT_PASSWORD=password -p 3326:3306  mariadb:10.2
docker start mariadb-slave1
docker exec -it mariadb-slave1 /bin/bash
apt-get update && apt-get install nano
nano /etc/mysql/my.cnf
server-id               = 2
report_host             = slave1
exit
docker restart mariadb-slave1
docker exec -it mariadb-slave1 /bin/bash
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='10.0.4.13', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3316, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=645, MASTER_CONNECT_RETRY=10;
START SLAVE;
exit
SLAVE 2
docker create --name mariadb-slave2 -e MYSQL_ROOT_PASSWORD=password -p 3336:3306  mariadb:10.2
docker start mariadb-slave2
docker exec -it mariadb-slave2 /bin/bash
apt-get update && apt-get install nano
nano /etc/mysql/my.cnf
server-id               = 3
report_host             = slave2
docker restart mariadb-slave2
docker exec -it mariadb-slave2 /bin/bash
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='10.0.4.13', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3316, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=645, MASTER_CONNECT_RETRY=10;
START SLAVE;
exit
Setup Maxscale
docker exec -it mariadb-master /bin/bash
mysql -u root -p
create user 'maxscale'@'%' identified by 'password’;
grant SELECT on mysql.user to 'maxscale'@'%’;
GRANT SELECT ON mysql.db TO 'maxscale'@'%’;
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%’;
GRANT SHOW DATABASES ON . TO 'maxscale'@'%’;
grant REPLICATION CLIENT on . to 'maxscale'@'%’;
docker run --name maxscale -p 4006:4006 -p 4008:4008 -p 4442:4442 -p 6603:6603 -d asosso/maxscale
docker exec -it maxscale /bin/bash
yum install nano
nano /etc/maxscale.cnf
exit
docker restart maxscale
SLAVE 3
docker create --name mariadb-slave3 -e MYSQL_ROOT_PASSWORD=password -p 3346:3306  mariadb:10.2
docker start mariadb-slave3
docker exec -it mariadb-slave3 /bin/bash
apt-get update && apt-get install nano
nano /etc/mysql/my.cnf
server-id               = 4
report_host             = slave3
docker restart mariadb-slave3
docker exec -it mariadb-slave3 /bin/bash
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='10.0.4.13', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3316, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=645, MASTER_CONNECT_RETRY=10;
START SLAVE;
exit