资讯详情

深入了解 · 专业解读

docker mysql主从安装-元一软件

发布时间：2026/7/15 20:23:40
docker mysql主从安装-元一软件 1. 首先拉去镜像docker pull mysql:5.72. 运行镜像主要将日志 存储文件 配置文件 映射到主机上docker run-p13307:3306--name mysql-master \-v/mydata/mysql-master/log:/var/log/mysql \-v/mydata/mysql-master/data:/var/lib/mysql \-v/mydata/mysql-master/conf:/etc/mysql \-eMYSQL_ROOT_PASSWORD123456\-d mysql:5.7[rootbasenode~]# docker run-p13307:3306--name mysql-master \-v/mydata/mysql-master/log:/var/log/mysql \-v/mydata/mysql-master/data:/var/lib/mysql \-v/mydata/mysql-master/conf:/etc/mysql \-eMYSQL_ROOT_PASSWORD123456\-d mysql:5.776176e5ae29317ea207080be62aeffadaada4c82c03753d2c500916449d6476e[rootbasenode~]#3. 主节点创建配置文件并且添加内容如下1.目录/mydata/mysql-master/cnf[rootbasenodeconf]# vi my.cnf[mysqld]## 设置server_id同一局域网中需要唯一 server_id101## 指定不需要同步的数据库名称 binlog-ignore-dbmysql ## 开启二进制日志功能 log-binmall-mysql-bin ## 设置二进制日志使用内存大小事务 binlog_cache_size1M ## 设置使用的二进制日志格式mixed,statement,row binlog_formatmixed ## 二进制日志过期清理时间。默认值为0表示不自动清理。 expire_logs_days7## 跳过主从复制中遇到的所有错误或指定类型的错误避免slave端复制中断。 ## 如1062错误是指一些主键重复1032错误是因为主从数据库数据不一致 slave_skip_errors10624. 添加完配置后重启mysql[rootbasenodeconf]# docker restart mysql-master mysql-master[rootbasenodeconf]#5. 进入到mysql 容器内部# 进入到容器 docker exec-it mysql-master/bin/bash # 登录mysql mysql-uroot-p1234566. 创建同步用户6.1 创建用户命令mysqlCREATEUSERslave%IDENTIFIEDBY123456;QueryOK,0rows affected(0.01sec)mysqlGRANTREPLICATIONSLAVE,REPLICATIONCLIENTON*.*TOslave%;QueryOK,0rows affected(0.00sec)mysql7. 创建从服务器[rootbasenode~]# docker run-p13308:3306--name mysql-slave \-v/mydata/mysql-slave/log:/var/log/mysql \-v/mydata/mysql-slave/data:/var/lib/mysql \-v/mydata/mysql-slave/conf:/etc/mysql \-eMYSQL_ROOT_PASSWORD123456\-d mysql:5.7230880d2ab8c53351656ceb3998e28595dcd5af28879de3755cf6c6993b0879d[rootbasenode~]#8. 从节点创建配置文件并且添加内容如下[rootbasenode~]# cd/mydata/mysql-slave/cnf[rootbasenodeconf]# vi my.cnf[mysqld]## 设置server_id同一局域网中需要唯一 server_id102## 指定不需要同步的数据库名称 binlog-ignore-dbmysql ## 开启二进制日志功能以备Slave作为其它数据库实例的Master时使用 log-binmall-mysql-slave1-bin ## 设置二进制日志使用内存大小事务 binlog_cache_size1M ## 设置使用的二进制日志格式mixed,statement,row binlog_formatmixed ## 二进制日志过期清理时间。默认值为0表示不自动清理。 expire_logs_days7## 跳过主从复制中遇到的所有错误或指定类型的错误避免slave端复制中断。 ## 如1062错误是指一些主键重复1032错误是因为主从数据库数据不一致 slave_skip_errors1062## relay_log配置中继日志 relay_logmall-mysql-relay-bin ## log_slave_updates表示slave将复制事件写进自己的二进制日志 log_slave_updates1## slave设置为只读具有super权限的用户除外 read_only19. 重启从节点[rootbasenodeconf]# docker restart mysql-slave mysql-slave[rootbasenodeconf]#10.在主数据库中查看主从同步状态mysqlshow master status;------------------------------------------------------------------------------------|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|------------------------------------------------------------------------------------|mall-mysql-bin.000001|154||mysql||------------------------------------------------------------------------------------1row in set(0.00sec)mysql11. 进入从数据库[rootbasenodeconf]# docker exec-it mysql-slave/bin/bash root230880d2ab8c:/# mysql-uroot-pEnterpassword:WelcometotheMySQLmonitor.Commandsendwith;or \g.YourMySQLconnection id is2Serverversion:5.7.36-logMySQLCommunityServer(GPL)Copyright(c)2000,2021,Oracleand/or itsaffiliates.Oracleis a registered trademark ofOracleCorporationand/or itsaffiliates.Othernames may be trademarks of their respectiveowners.Typehelp;or\hforhelp.Type\ctoclearthe current input statement.mysqlshow databases;--------------------|Database|--------------------|information_schema||mysql||performance_schema||sys|--------------------4rows in set(0.00sec)mysql12. 在从节点上执行同步命令change master to master_host宿主机ip, master_userslave, master_password123456, master_port3307, master_log_filemall-mysql-bin.000001, master_log_pos617, master_connect_retry30;mysqlchange mastertomaster_host192.168.1.180,master_userslave,master_password123456,master_port13307,master_log_filemall-mysql-bin.000001,master_log_pos154,master_connect_retry30;QueryOK,0rows affected,2warnings(0.00sec)mysql13. 在从节点上开启主从同步mysqlstart slave;14 查看 从节点的状态mysqlshow slave status \G;***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.1.180Master_User:slaveMaster_Port:13307Connect_Retry:30Master_Log_File:mall-mysql-bin.000001Read_Master_Log_Pos:154Relay_Log_File:mall-mysql-relay-bin.000002Relay_Log_Pos:325Relay_Master_Log_File:mall-mysql-bin.000001Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:154Relay_Log_Space:537Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:101Master_UUID:9eedd539-7601-11ec-93ac-0242ac110005Master_Info_File:/var/lib/mysql/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehas read all relay log;waitingformore updatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position:0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1row in set(0.00sec)ERROR:Noquery specified mysql主要出现 两个都位yes 就表示成功Slave_IO_Running: YesSlave_SQL_Running: Yes15 测试成功docker-mysql 主从.png

相关新闻