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

拓展连接

一 安装：
1 获取mysql镜像

docker pull mysql

2 。写配置文件my.cnf

[mysqld]log-bin=mysql-binserver-id=1port=3306innodb_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 MySQLdbimport 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@*.*.*.*:3306master_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 MySQLdbimport 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.closemaster_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