Skip to content

Latest commit

 

History

History
101 lines (89 loc) · 3.02 KB

README.md

File metadata and controls

101 lines (89 loc) · 3.02 KB

mysql-cluster

Set-up simple mysql master slave replication: master and 2 slaves

Start docker

./build.sh

Check replication is working for each slave

  1. Check that there are not tables in slaves
docker exec mysql_slave_1 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SHOW TABLES \G'"
docker exec mysql_slave_2 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SHOW TABLES \G'"
  1. Create a table in master
docker exec mysql_master sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; CREATE TABLE IF NOT EXISTS data(task_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL)'"
  1. Check again that there are the table data in slaves
 docker exec mysql_slave_1 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SHOW TABLES \G'"                   

*************************** 1. row ***************************
Tables_in_mydb: data
 docker exec mysql_slave_2 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SHOW TABLES \G'"                   

*************************** 1. row ***************************
Tables_in_mydb: data
  1. Insert some rows into data table in master
docker exec mysql_master sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; INSERT INTO data (title) VALUES (\"Example_1\"), (\"Example_2\")'"
  1. Check data table in slaves
docker exec mysql_slave_1 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SELECT * FROM data'"
task_id title
1       Example_1
2       Example_2
docker exec mysql_slave_2 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SELECT * FROM data'"
task_id title
1       Example_1
2       Example_2

Check replication is working after turning off a slave

docker-compose stop mysql_slave_1

Insert more data to master

docker exec mysql_master sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; INSERT INTO data (title) VALUES (\"Example_3\"), (\"Example_4\")'"

Turn on slave

docker-compose up -d mysql_slave_1

Check that data was replicate from the lost period of time

docker exec mysql_slave_1 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SELECT * FROM data'"
task_id title
1       Example_1
2       Example_2
3       Example_3
4       Example_4

Try to remove a column in database on slave node

Delete column title from the first slave

docker exec mysql_slave_1 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; ALTER TABLE data DROP COLUMN title'"
docker exec mysql_slave_1 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SELECT * FROM data'"

task_id
1
2
3
4

Insert more data to master and check slave. New data replicated to the remaining column

docker exec mysql_master sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; INSERT INTO data (title) VALUES (\"Example_5\")'"
docker exec mysql_slave_1 sh -c "export MYSQL_PWD=111; mysql -u root -e 'USE mydb; SELECT * FROM data'"                            

task_id
1
2
3
4
5