0%

【mysql使用系列】docker1.10安装mysql5.6主从

发表于 2017-03-13 14:42:36 分类于业务运行的基础设施平台， mysql技术 Valine：

本文可用于测试环境，正式环境，主从还是需要部署在不同的节点上。

安装过程

建数据存储目录

mkdir mysql_master/
mkdir mysql_slave/
chown -R mysql:mysql mysql_master/
chown -R mysql:mysql mysql_slave/

准备my.cnf配置文件

mysql主配置文件

# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 1 

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1

#log-error  = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

mysql从配置文件

# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 2 

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1

#log-error  = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

启动mysql主从

docker run -d -e MYSQL_ROOT_PASSWORD=admin  --add-host www.slave.com:10.2.1.31 --name mysql_master -v /docker/mysql_cluster/master/conf/my.cnf:/etc/mysql/my.cnf -v /docker/mysql_cluster/master/data:/var/lib/mysql -p 3308:3306 mysql:5.6

docker run -d -e MYSQL_ROOT_PASSWORD=admin  --add-host www.master.com:10.2.1.31 --name mysql_slave -v /docker/mysql_cluster/slave/conf/my.cnf:/etc/mysql/my.cnf -v /docker/mysql_cluster/slave/data:/var/lib/mysql -p 3309:3306 mysql:5.6

iptables防火墙放通权限

iptables -I INPUT 1 -p tcp -m state --state NEW --dport 3308 -j ACCEPT

在mysql主服务上创建用户及授权

CREATE USER 'rep01'@'%' IDENTIFIED BY '123456'; 
GRANT REPLICATION SLAVE ON *.* TO 'rep01'@'%' IDENTIFIED BY '123456';
SHOW MASTER STATUS;
#show variables like '%server_uuid%';

在mysql从服务上创建用户及授权

CREATE USER 'rep01'@'%' IDENTIFIED BY '123456';
change master to master_heartbeat_period = 10; 
CHANGE MASTER TO MASTER_HOST='www.master.com', MASTER_PORT=3308, MASTER_USER='rep01', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=472;
START SLAVE;
show slave status;

验证效果

在主服务上建立数据库/表，插入数据

create database test_base;
use test_base;
CREATE TABLE `test_base_bank` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `bank_code` varchar(10) DEFAULT NULL COMMENT '银行编码',
  `bank_name` varchar(30) DEFAULT NULL COMMENT '银行名称',
  `enable` varchar(10) DEFAULT NULL COMMENT '是否可用',
  `single_payment_limit` varchar(20) DEFAULT NULL COMMENT '单笔限额【元】',
  `day_payment_limit` varchar(20) DEFAULT NULL COMMENT '日累计限额【元】',
  `month_payment_limit` varchar(20) DEFAULT NULL COMMENT '月累计限额【元】',
  `bank_logo` varchar(155) DEFAULT NULL COMMENT '银行logo',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `test_base`.`test_base_bank` (`id`, `create_time`, `modify_time`, `bank_code`, `bank_name`, `enable`, `single_payment_limit`, `day_payment_limit`, `month_payment_limit`, `bank_logo`) VALUES ('2', '2015-10-26 17:42:53', '2015-11-10 22:39:37', 'ABC', '中国农业银行', 'true', '1', '1', '1', 'http://***/images/bank/ABC_large.png');

查看从服务器上的数据