搭建mysql主从同步
1.安装镜像
docker pull mysql:latest
下面配置文件难得写的话可以看这个里面的mysql_conf九问/conf_file - 码云 - 开源中国 (gitee.com)
2. 创建主数据库容器
2.1 在 opt目录下创建mysql_conf目录，在此目录下创建mysql_master目录，在此目录下 配置mysqld.cnf 文件，修改远程访问
#port = 3307
# datadir = /var/lib/mysql
user = root
# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size = 16M
# max_allowed_packet = 64M
# thread_stack = 256K
# thread_cache_size = -1
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam-recover-options = BACKUP
# max_connections = 151
# table_open_cache = 4000
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file = /var/log/mysql/query.log
# general_log = 1
#
# Error log - should be very few entries.
#
#log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
# server-id = 1
# log_bin = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds = 2592000
max_binlog_size = 100M
# binlog_do_db = include_database_name
# binlog_ignore_db = include_database_name
2.2 在 opt目录下创建mysql_conf目录，在此目录下创建mysql_master目录，在此目录下 配置my.cnf 文件,修改数据库端口，以及数据库名称
# Copyright (c) 2017, 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 Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
port = 3307
server_id=1
log-bin=mysql-bin
read-only=0
binlog-do-db=drf25
# Custom config should go here
#!includedir /etc/mysql/conf.d/
2.3 创建虚拟网络
docker network create mysql_network
2.4 创建主数据库容器
docker run -dti --network=mysql_network -p 3307:3307 --name mysql_master -v /opt/mysql-master:/var/lib/mysql -v /opt/mysql_conf/mysql_master/my.cnf:/etc/mysql/my.cnf -v /opt/mysql_conf/mysql_master/mysqld.cnf:/etc/mysql/conf.d/mysqld.cnf -e MYSQL_ROOT_PASSWORD=admin.123 mysql:latest
2.5 创建连接用户
2.5.1 进入容器内部
docker exec -it mysql_master bash
2.5.2 创建用户
mysql -uroot -padmin.123
创建用户。
CREATE USER 'user'@'%' IDENTIFIED BY 'user.123';
给用户绑定权限
GRANT REPLICATION SLAVE ON *.* TO 'user'@'%';
刷新权限
FLUSH PRIVILEGES;
修改用户密码的加密方式
alter user 'root'@'localhost' identified with mysql_native_password by 'admin.123';
刷新权限
FLUSH PRIVILEGES;
2.6 查看主数据库的状态
show master status\G
获取显示的File Position的值
3.配置从数据库
3.1 在 mysql_conf目录下，创建mysql_salve_01目录，在此目录下 配置mysqld.cnf 文件，修改远程访问
#port = 3307
# datadir = /var/lib/mysql
user = root
# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size = 16M
# max_allowed_packet = 64M
# thread_stack = 256K
# thread_cache_size = -1
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam-recover-options = BACKUP
# max_connections = 151
# table_open_cache = 4000
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file = /var/log/mysql/query.log
# general_log = 1
#
# Error log - should be very few entries.
#
#log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
# server-id = 1
# log_bin = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds = 2592000
max_binlog_size = 100M
# binlog_do_db = include_database_name
# binlog_ignore_db = include_database_name
3.2 在mysql_salve_01目录下， 配置my.cnf 文件,修改数据库端口，以及数据库名称
# Copyright (c) 2017, 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 Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
port = 3308
server_id=1
log-bin=mysql-bin
read-only=0
binlog-do-db=drf25
# Custom config should go here
#!includedir /etc/mysql/conf.d/
3.3 创建从数据库容器
docker run -dti --network=mysql_network -p 3308:3308 --name mysql_salve01 -v /opt/mysql-salve01:/var/lib/mysql -v /opt/mysql_conf/mysql_salve_01/my.cnf:/etc/mysql/my.cnf -v /opt/mysql_conf/mysql_salve_01/mysqld.cnf:/etc/mysql/conf.d/mysqld.cnf -e MYSQL_ROOT_PASSWORD=admin.123 mysql:latest
3.4 连接主数据库
3.4.1 进入从数据容器
docker exec -it mysql_salve01 bash
3.4.2 连接主数据库
以下内容注意替换，MASTER_LOG_FILE和 MASTER_LOG_POS
CHANGE MASTER TO
MASTER_HOST='mysql_master',
MASTER_USER='user',
MASTER_PASSWORD='user.123',
MASTER_LOG_FILE='mysql-bin.000028',
MASTER_LOG_POS=156,
MASTER_PORT=3307,
GET_MASTER_PUBLIC_KEY=1;
master_host 主数据库的宿主机IP地址
master_port 主数据库运行的端口
master_user 主数据库中创建的用于数据同步的用户名
master_password 主数据库中创建的用于数据同步的密码
master_log_file 指定从数据库要复制数据的日志文件，在主数据库中通过show master status查看主数据库的状态，获取File参数
master_log_pos 指定从数据库要从哪个位置开始复制数据，在主数据库中通过show master status查看主数据库的状态，获取Position参数
master_connect_retry 连接主数据库失败重试的时间间隔，单位为秒
3.4.3 查看状态
START SLAVE;
SHOW SLAVE STATUS\G;
如果Slave_IO_Running，Slave_SQL_Running 都为Yes 说明主从同步搭建成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3226
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
