华为云 mysql 主备_mysql做主从备份

最新推荐文章于 2022-01-24 17:13:14 发布

JJ Ying

最新推荐文章于 2022-01-24 17:13:14 发布

阅读量181

点赞数

文章标签：华为云 mysql 主备

本文链接：https://blog.csdn.net/weixin_32871457/article/details/113647765

版权

本文演示如何在mysql上建立主从备份。为了方便我们使用docker作为基础环境。

文件准备

创建文件夹

mkdir mysqlcd mysql

创建如下文件

docker-compose.yaml

version: '3.1'services: db0: image: mysql:5.7 ports: - "3300:3306"

# command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: 123456 volumes: - ./my_master.cnf:/etc/mysql/my.cnf db1: image: mysql:5.7 ports: - "3301:3306"

# command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: 123456 volumes: - ./my_slave.cnf:/etc/mysql/my.cnf

my_master.cnf

# Copyright (c) 2016, 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!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mysql.conf.d/

[mysqld]# [必须]启用二进制日志log-bin=mysql-bin

# [必须]服务器唯一IDserver-id=1

my_slave.cnf

!includedir /etc/mysql/mysql.conf.d/

[mysqld]# [非必须]Slave可以不启用二进制日志，配置二进制日志可以便于Master和Slave交换角色log-bin=mysql-bin

# [必须]服务器唯一IDserver-id=2

配置主服务器

查询进入主库

fangleMac:~ fangle$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

aabec4d6d0c2 mysql:5.7 "docker-entrypoint.s…" 43 minutes ago Up 43 minutes 33060/tcp, 0.0.0.0:3300->3306/tcp mysql_db0_1

b59d7fe2d10a mysql:5.7 "docker-entrypoint.s…" 43 minutes ago Up 43 minutes 33060/tcp, 0.0.0.0:3301->3306/tcp mysql_db1_1

fangleMac:~ fangle$ docker exec -it aabec4d6d0c2 bash

root@aabec4d6d0c2:/# root@aabec4d6d0c2:/# mysql -uroot -p123456mysql> GRANT REPLICATION SLAVE ON *.* to 'nick'@'%' identified by '123456';

mysql> FLUSH PRIVILEGES;

查看master 状态,记录File和Position值

mysql> show master status;

登陆slave节点

fangleMac:~ fangle$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

aabec4d6d0c2 mysql:5.7 "docker-entrypoint.s…" About an hour ago Up About an hour 33060/tcp, 0.0.0.0:3300->3306/tcp mysql_db0_1

b59d7fe2d10a mysql:5.7 "docker-entrypoint.s…" About an hour ago Up About an hour 33060/tcp, 0.0.0.0:3301->3306/tcp mysql_db1_1fangleMac:~ fangle$ docker exec -it b59d7fe2d10a bash

root@b59d7fe2d10a:/# mysql -uroot -p123456mysql> change master to master_host='db0',master_user='nick',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=589;

mysql> start slave;

使用root账户登录MySQL核对Slave状态,注意Slave_IO_State，Slave_IO_Running，Slave_SQL_Running的状态

mysql> show slave status\G;

*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event

Master_Host: db0

Master_User: nick

Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 1039 Relay_Log_File: b59d7fe2d10a-relay-bin.000002

Relay_Log_Pos: 770 Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

测试