Avatar
# for historical purposes:
[acool@localhost ~]$ date
Thu Jan 24 18:44:23 PST 2019
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ cat /etc/redhat-release
Fedora release 28 (Twenty Eight)
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker --version
Docker version 1.13.1, build 1556cce-unsupported
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker network create mysql-cluster
72af69d434e8cefa031555d2ba6039c2260aa73fe27df380b9454e3790c89337
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker network ls
NETWORK ID NAME DRIVER SCOPE
5dfcb11eac94 bridge bridge local
c146b3afd86e host host local
72af69d434e8 mysql-cluster bridge local
1299a7349094 none null local
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ tree mysql-fun
mysql-fun
├── master
│ ├── config
│ │ └── mysqld.cnf
│ └── data
└── slave
├── config
│ └── mysqld.cnf
└── data
6 directories, 2 files
[acool@localhost ~]$
[acool@localhost ~]$ cat mysql-fun/master/config/mysqld.cnf
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin-master
[acool@localhost ~]$
[acool@localhost ~]$ cat mysql-fun/slave/config/mysqld.cnf
[mysqld]
server_id = 2
relay_log = /var/log/mysql/mysql-relay-slave-bin
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ getenforce
Permissive
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/mysql 5.7 5cd9bd4c8a5e 3 days ago 372 MB
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker run -v /home/acool/mysql-fun/slave/config:/etc/mysql/conf.d -v /home/acool/mysql-fun/slave/data/:/var/lib/mysql/:rw --detach --network mysql-cluster --name=slave-mysql --env="MYSQL_ROOT_PASSWORD=temp123" mysql:5.7
599f302193cda964ada60d42e90324705c8d5b578c805c57f395958b97ed6702
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker run -v /home/acool/mysql-fun/master/config:/etc/mysql/conf.d -v /home/acool/mysql-fun/master/data/:/var/lib/mysql/:rw --detach --network mysql-cluster --name=master-mysql --env="MYSQL_ROOT_PASSWORD=temp123" mysql:5.7
32d0c2ecc7e6d17ae99ebabd6c75f0566846d60451bfb953b747b767dda92129
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
32d0c2ecc7e6 mysql:5.7 "docker-entrypoint..." 15 seconds ago Up 14 seconds 3306/tcp, 33060/tcp master-mysql
599f302193cd mysql:5.7 "docker-entrypoint..." 46 seconds ago Up 44 seconds 3306/tcp, 33060/tcp slave-mysql
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker inspect master-mysql |grep \"IPAddress\"
"IPAddress": "",
"IPAddress": "172.18.0.3",
[acool@localhost ~]$
[acool@localhost ~]$ sudo docker inspect slave-mysql |grep \"IPAddress\"
"IPAddress": "",
"IPAddress": "172.18.0.2",
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ mysql -h 172.18.0.3 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> CREATE USER 'replication_user'@'%' IDENTIFIED BY 'ru_temp123';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW MASTER STATUS;
+-------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000003 | 763 | | | |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$
[acool@localhost ~]$ mysql -h 172.18.0.2 -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST = '172.18.0.3', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'ru_temp123', MASTER_LOG_FILE = 'mysql-bin-master.000003', MASTER_LOG_POS = 763;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.3
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000003
Read_Master_Log_Pos: 763
Relay_Log_File: mysql-relay-slave-bin.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: mysql-bin-master.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 763
Relay_Log_Space: 540
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 60c1469e-2049-11e9-89ca-0242ac120003
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[acool@localhost ~]$
[acool@localhost ~]$ # create new database in master
[acool@localhost ~]$ mysql -h 172.18.0.3 -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> CREATE DATABASE users;
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| users |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[acool@localhost ~]$ # confirm database creation was replicated to slave
[acool@localhost ~]$
[acool@localhost ~]$ mysql -h 172.18.0.2 -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| users |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[acool@localhost ~]$
[acool@localhost ~]$ # Exercise finished! SQL statements are being replicated from master to slave :)
[acool@localhost ~]$
Comments
Comments are not available for this entry.