MySQL实践
目录
前言
在上一篇文章,我们整理总结MySQL的常用知识点和MySQL的复制相关概念,在这篇文章,我们尝试搭建一个MySQL的主从服务。
主从架构
我们从github上拉取MySQL主从复制的版本库,直接尝试进行构建。该版本库构建了主从服务,定义了对数据库mydb的基于行的复制。
cd dev
git clone git@github.com:vbabak/docker-mysql-master-slave.git
./build.sh
# 执行成功后会显示
Slave_IO_State: Waiting for master to send event
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 600
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
查看构建日志
docker-compose logs
查看当前进程
docker-compose ps
Name Command State Ports
------------------------------------------------------------------------------------------------
mysql_master docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp, 0.0.0.0:4406->4406/tcp
mysql_slave docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp, 0.0.0.0:5506->5506/tcp
查看master的状态
docker exec mysql_master sh -c 'mysql -u root -p111 -e "SHOW MASTER STATUS \G"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 600
Binlog_Do_DB: mydb
Binlog_Ignore_DB:
Executed_Gtid_Set:
查看slave的状态
docker exec mysql_slave sh -c 'mysql -u root -p111 -e "SHOW SLAVE STATUS \G"'
Slave_IO_State: Waiting for master to send event
Master_Host: 172.19.0.2
Master_User: mydb_slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 600
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在master上创建一个dev数据库,在slave上查看是否同步成功。
# 查看数据库
docker exec mysql_slave sh -c 'mysql -u root -p111 -e "SHOW DATABASES \G"'
docker exec mysql_master sh -c 'mysql -u root -p111 -e "SHOW DATABASES \G"'
# 查看记录
docker exec mysql_master sh -c "export MYSQL_PWD=111; mysql -u root mydb -e 'create table code(code int); insert into code values (100), (200)'"
# 查看master的状态
docker exec mysql_master sh -c "export MYSQL_PWD=111; mysql -u root mydb -e 'select * from code \G'"
docker exec mysql_master sh -c 'mysql -u root -p111 -e "SHOW DATABASES \G"'
docker exec mysql_master sh -c 'mysql -u root -p111 -e "SHOW MASTER STATUS \G"'
# 查看同步结果 slave状态
docker exec mysql_slave sh -c "export MYSQL_PWD=111; mysql -u root mydb -e 'select * from code \G'"
docker exec mysql_slave sh -c 'mysql -u root -p111 -e "SHOW DATABASES \G"'
docker exec mysql_slave sh -c 'mysql -u root -p111 -e "SHOW SLAVE STATUS \G"'
在这里,我们可以看到两条数据已经完整的同步过来。
*************************** 1. row ***************************
code: 100
*************************** 2. row ***************************
code: 200