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

参考链接

1. Docker MySQL master-slave replication
2. Mysql Master/Slave Replication With Docker