搭建mysql一主一备

scx_link

已于 2023-03-10 16:35:14 修改

阅读量1k

点赞数 1

分类专栏： Mysql 文章标签： mysql docker 数据库

于 2023-03-10 13:42:33 首次发布

本文链接：https://blog.csdn.net/adminstate/article/details/129441093

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、master主机上部署=====
1.上传镜像文件mysql5.7.tar到/home目录下。
2.若无docker,先安装docker工具。
安装docker
进入到/home/sec-install/bin目录下执行sh start.sh
等待安装…
查询是否安装成功命令 docker version

3.使用load进行从tar文件导出镜像 docker load < /home/mysql5.7.tar
4.然后查看是否出现镜像 docker images

REPOSITORY TAG IMAGE ID CREATED SIZE
mysql 5.7 2a0961b7de03 2 weeks ago 462MB
8b94b71dcc1e 3 months ago 448MB
mysql 5.6 dd3b2a5dcb48 5 months ago 303MB

5.根据镜像文件创建容器
宿主机上如无 /data/mysqlmaster/conf、/data/mysqlmaster/logs、/data/mysqlmaster/data 目录手动创建
mkdir –p /data/mysqlmaster/conf;
mkdir –p /data/mysqlmaster/logs;
mkdir –p /data/mysqlmaster/data;

5.1执行创建并运行容器的命令：
docker run -d -p 3308:3306 --name mysql-master -v /data/mysqlmaster/conf:/etc/mysql/conf.d -v /data/mysqlmaster/logs:/logs -v /data/mysqlmaster/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=xxxadmin mysql:5.7 --lower_case_table_names=1;
5.2 查看目前启动的容器命令 docker ps
[root@xndfh home]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
62e8c5bd95fb mysql:5.7 “docker-entrypoint.s…” 50 seconds ago Up 49 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp mysql-master
添加配置文件：
/data/mysqlmaster/conf目录下创建my.cnf文件

在my.cnf里面输入如下信息

[mysqld]

同一局域网内注意要唯一

server-id=100

开启二进制日志功能

log-bin=mysql-bin

进入mysql-master容器里面重启mysql服务

docker exec -it mysql-master /bin/bash 进入mysql-master容器

service mysql restart重启mysql服务器

重启mysql服务之后，mysql-master容器被关掉了，所以我们需要docker start mysql-master在启动下容器

[root@iZssfdf /]# docker exec -it mysql-master /bin/bash
root@62e8c5bd95fb:/# service mysql restart
[root@iZ2zessddfjrxexaZ /]# docker start mysql-master

再次进入mysql-master容器授予slave用户 REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据[slave用户是用来做数据同步的]

1. CREATE USER ‘slave’@‘%’ IDENTIFIED BY ‘xxxadmin’;

2. GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘slave’@‘%’;

3. show master status; 查看主库状态，请记住File和Position对应的值

[root@iZ2ze31n2wkah07fjrxexaZ /]# docker exec -it mysql-master /bin/bash
root@62e8c5bd95fb:/# mysql -u root -pJitadmin
mysql> CREATE USER ‘slave’@‘%’ IDENTIFIED BY ‘Jitadmin’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘slave’@‘%’;
Query OK, 0 rows affected (0.01 sec)
mysql>flush privileges;

二、slave主机上部署========================

6.将主机:47.94.184.41的home下的文件复制到当前主机的home目录下
scp -r root@192.168.0.18:/home/* /home
安装docker
进入到/home/sec-install/bin目录下执行sh start.sh
等待安装…
查询是否安装成功命令 docker version

1.上传镜像文件mysql5.7.tar到/home目录下。
2.若无docker,先安装docker工具。
3.使用load进行从tar文件导出镜像 docker load < /home/mysql5.7.tar
4.然后查看是否出现镜像 docker images

REPOSITORY TAG IMAGE ID CREATED SIZE
mysql 5.7 2a0961b7de03 2 weeks ago 462MB
8b94b71dcc1e 3 months ago 448MB
mysql 5.6 dd3b2a5dcb48 5 months ago 303MB

5.根据镜像文件创建容器
宿主机上如无 /data/mysql/conf、/data/mysql/logs、/data/mysql/data 目录手动创建
mkdir -p /data/mysqlslave/conf
mkdir –p /data/mysqlslave/logs
mkdir –p /data/mysqlslave/data
5.1执行创建并运行容器的命令：
docker run -d -p 3309:3306 --name mysql-slave -v /data/mysqlslave/conf:/etc/mysql/conf.d -v /data/mysqlslave/logs:/logs -v /data/mysqlslave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=xxxadmin mysql:5.7 --lower_case_table_names=1;
5.2 查看目前启动的容器命令 docker ps
[root@iZ2ze31n2wkah07fjrxexbZ data]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9d4b5f082553 mysql:5.7 “docker-entrypoint.s…” 14 seconds ago Up 13 seconds 33060/tcp, 0.0.0.0:3308->3306/tcp mysql-slave

mysql-slave配置文件修改

[root@iZ2ze31n2wkah /]# cd /data/mysqlslave/conf
[root@iZ2ze31n2wkah conf]# vi my.cnf

在my.cnf里面输入如下信息

[mysqld]

设置server_id,注意要唯一

server-id=101

开启二进制日志功能，以备Slave作为其它Slave的Master时使用

log-bin=mysql-slave-bin

relay_log配置中继日志

relay_log=edu-mysql-relay-bin

进入mysql-slave容器里面重启mysql服务

docker exec -it mysql-slave /bin/bash 进入mysql-master容器

service mysql restart重启mysql服务器

这里又一点要注意，重启mysql服务之后，mysql-slave容器被关掉了，所以我们需要docker start mysql-slave在启动下容器

[root@in2wkah /]# docker exec -it mysql-slave /bin/bash
root@9d4b5f082553:/# service mysql restart
[root@iZ2ze31n2wkafj /]# docker start mysql-slave

再次进入mysql-slave容器，连上mysql-slave数据库

[root@iZ2ze31n2wka /]# docker exec -it mysql-slave /bin/bash
[root@iZ2ze31n2 conf]# docker exec -it mysql-slave /bin/bash
root@9d4b5f082553:/# mysql -u root -pJitadmin

mysql> change master to master_host=‘192.168.0.1’, master_port=3308, master_user=‘slave’,master_password=‘Jitadmin’,master_log_file=‘mysql-bin.000001’, master_log_pos=769;
mysql> stop slave; //停止slave
mysql> start slave; //启动slave
#查看备份机器的mysql状态：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event #表示成功了
Master_Host: 192.168.0.18
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 769
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 769
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 8d7960fd-ee2f-11ec-a257-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version: