MySQL 此例简单介绍基于docker的mysql安装与双主互备

本文介绍如何使用Docker部署MySQL，并通过Python脚本实现MySQL的主从复制及双主互备配置。主要内容包括MySQL镜像的获取、配置文件编写、容器启动、Python脚本实现的主从复制及双主互备设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一安装：
1 获取mysql镜像

docker pull mysql

2 。写配置文件my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1
port=3306
innodb_buffer_pool_size=4G

3 启动容器

mysql -p 3306:3306 -e MYSQL_USER=root -e MYSQL_ROOT_PASSWORD=password -v /showcase/mysql/my.cnf:/etc/my.cnf --restart=always -d mysql:latest

二双主互备

1 双主互备设置脚本mysql_master.py

# coding=utf-8
__author__ = 'Bridge'

import MySQLdb
import sys


# 数据库连接
def connection(settings):
    connect = MySQLdb.Connection(settings["host"], settings["user"], settings["passwd"], 'information_schema',
                                settings["port"])
    return connect.cursor()


# 主备设置
def set_slave(master, slave):
    master_conn = connection(master)
    master_conn.execute("GRANT REPLICATION SLAVE ON *.* to 'slave_user'@'%' identified by 'copy'")
    master_conn.execute("show master status")
    m_obj = master_conn.fetchone()

    slave_conn = connection(slave)
    slave_conn.execute("show slave status")
    s_obj = slave_conn.fetchone()

    print m_obj
    print s_obj
    if None == s_obj or m_obj[1] != s_obj[6] or m_obj[0] != s_obj[5] or "No" == s_obj[10] or "No" == s_obj[11]:
        slave_conn.execute("stop slave")
        sql = "CHANGE MASTER TO MASTER_LOG_FILE='" + str(m_obj[0]) + "',MASTER_LOG_POS=" + str(m_obj[1])
        sql += ",MASTER_HOST='" + master["host"] + "',MASTER_USER='slave_user',MASTER_PASSWORD='copy'"
        slave_conn.execute(sql)
        slave_conn.execute("start slave")
    master_conn.close
    slave_conn.close

## python mysql_master.py root:password@*.*.*.*:3306 root:password@*.*.*.*:3306
master_arg = sys.argv[1]
slave_arg = sys.argv[2]
# 执行
if "" != master_arg and "" != slave_arg:
    # user:password@ip:port
    master_settings = {'host': master_arg[master_arg.rfind("@") + 1:master_arg.rfind(":")],
                    'port': int(master_arg[master_arg.rfind(":") + 1:]),
                    'user': master_arg[0:master_arg.find(":")],
                    'passwd': master_arg[master_arg.find(":") + 1:master_arg.rfind("@")]}
    slave_settings = {'host': slave_arg[slave_arg.rfind("@") + 1:slave_arg.rfind(":")],
                    'port': int(slave_arg[slave_arg.rfind(":") + 1:]),
                    'user': slave_arg[0:slave_arg.find(":")],
                    'passwd': slave_arg[slave_arg.find(":") + 1:slave_arg.rfind("@")]}

    # # 主主互备
    set_slave(master_settings, slave_settings)
    set_slave(slave_settings, master_settings)

2 仅在一个主上执行两次python脚本

python mysql_master.py root:password@*.*.*.*:3306 root:password@*.*.*.*:3306

三主从
1 主从设置脚本mysql_master.py

# coding=utf-8
__author__ = 'Bridge'

import MySQLdb
import sys


# 数据库连接
def connection(settings):
    connect = MySQLdb.Connection(settings["host"], settings["user"], settings["passwd"], 'information_schema',
                                settings["port"])
    return connect.cursor()


# 主备设置
def set_slave(master, slave):
    master_conn = connection(master)
    master_conn.execute("GRANT REPLICATION SLAVE ON *.* to 'slave_user'@'%' identified by 'copy'")
    master_conn.execute("show master status")
    m_obj = master_conn.fetchone()

    slave_conn = connection(slave)
    slave_conn.execute("show slave status")
    s_obj = slave_conn.fetchone()

    print m_obj
    print s_obj
    if None == s_obj or m_obj[1] != s_obj[6] or m_obj[0] != s_obj[5] or "No" == s_obj[10] or "No" == s_obj[11]:
        slave_conn.execute("stop slave")
        sql = "CHANGE MASTER TO MASTER_LOG_FILE='" + str(m_obj[0]) + "',MASTER_LOG_POS=" + str(m_obj[1])
        sql += ",MASTER_HOST='" + master["host"] + "',MASTER_USER='slave_user',MASTER_PASSWORD='copy'"
        slave_conn.execute(sql)
        slave_conn.execute("start slave")
    master_conn.close
    slave_conn.close

master_arg = sys.argv[1]
slave_arg = sys.argv[2]
# 执行
if "" != master_arg and "" != slave_arg:
    # user:password@ip:port
    master_settings = {'host': master_arg[master_arg.rfind("@") + 1:master_arg.rfind(":")],
                    'port': int(master_arg[master_arg.rfind(":") + 1:]),
                    'user': master_arg[0:master_arg.find(":")],
                    'passwd': master_arg[master_arg.find(":") + 1:master_arg.rfind("@")]}
    slave_settings = {'host': slave_arg[slave_arg.rfind("@") + 1:slave_arg.rfind(":")],
                    'port': int(slave_arg[slave_arg.rfind(":") + 1:]),
                    'user': slave_arg[0:slave_arg.find(":")],
                    'passwd': slave_arg[slave_arg.find(":") + 1:slave_arg.rfind("@")]}

    # 设置主从
    set_slave(master_settings, slave_settings)

2 在主上执行两次python脚本

python mysql_master.py root:password@*.*.*.*:3306 root:password@*.*.*.*:3306