Docker搭建MySQL主从复制

原创
07/01 21:52
阅读数 107

1、运行两个MySQL容器:

# mysql-master
docker run -d --name mysql-master -v /home/soft/mysql/mster:/var/lib/mysql -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.28

# mysql-slave
docker run -d --name mysql-slave -v /home/soft/mysql/slave:/var/lib/mysql -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 --link mysql-master mysql:5.7.28

2、进入主容器:

docker exec -it mysql-master bash
安装vim:
apt-get update & apt-get install vim

编辑配置文件:

vi /etc/mysql/my.cnf

在最后添加

[mysqld] 
# 同一局域网内注意要唯一 
server-id=1 
# 开启二进制日志功能 
log-bin=mysql-master-bin
保存,退出容器,重启mysql-master:
docker restart mysql-master

3、进入从容器:

docker exec -it mysql-slave bash
安装vim:
apt-get update & apt-get install vim

编辑配置文件:

vi /etc/mysql/my.cnf

在最后添加

[mysqld] 
# 同一局域网内注意要唯一 
server-id=2
保存,退出容器,重启mysql-slave:
docker restart mysql-slave

4、进入主容器:

docker exec -it mysql-master mysql -u root -p

创建用户并赋予复制权限:

GRANT replication slave ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
执行命令查看二进制文件信息:
show master status;
+-------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-master-bin.000001 | 609 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set
记录File、Position两个字段值

5、进入从容器:

docker exec -it mysql-slave mysql -u root -p

执行命令停止slave:

stop slave;

执行命令切换主从复制配置:

change master to master_host='mysql-master',master_user='slave',master_password='123456',master_log_file='mysql_master_bin.000001',master_log_pos=609;

执行命令启动slave:

start slave;

查看配置状态:

+----------------------------------+-------------+-------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+-------------+-------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | mysql1 | slave | 3306 | 60 | mysql1-bin.000001 | 609 | 59e0b81b9fbb-relay-bin.000002 | 321 | mysql1-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 609 | 535 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | 1625200d-3672-11ea-ae5f-0242ac110002 | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | |
+----------------------------------+-------------+-------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set

6、验证:

连接主数据库,新建数据库表或插入数据;查看从数据库是否自动创建,验证主从复制是否成功;
 
 
 

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
1 评论
0 收藏
0
分享
返回顶部
顶部