ikun

外观

Mysql

约 1520 字大约 5 分钟

2025-02-20

Centos7.9 docker-compose安装mysql:5.7.20主从

注

以下操作在master做

下载mysql.yaml

wget https://www.ikun.blog/downloads/mysql/mysql-master.yaml

安装docker并启动

curl -sSL curl -fsSL https://get.docker.com | sh
systemctl start docker

安装docker-compose

curl -SL https://github.com/docker/compose/releases/download/v2.34.0/docker-compose-linux-x86_64 -o /usr/local/bin/docker-compose
chmod +x /usr/local/bin/docker-compose

创建文件夹并给予权限

mkdir -p /opt/mysql/conf
mkdir -p /opt/mysql/dumpdb/
mkdir -p /downloads/mysql/
chmod 755 -R /opt/mysql/conf
chmod 777 -R /opt/mysql/dumpdb/
chmod 777 -R /downloads/mysql/

警告

配置文件挂载目录权限一定要是755，777会提示权限过大，644不报错但是配置文件不会生效

下载mysql配置文件

wget https://www.ikun.blog/downloads/mysql/my.cnf
wget https://www.ikun.blog/downloads/mysql/docker.cnf
wget https://www.ikun.blog/downloads/mysql/mysql.cnf

mv配置文件到/opt/mysql/conf/下

mv my.cnf /opt/mysql/conf/
mv mysql.cnf /opt/mysql/conf/
mv docker.cnf /opt/mysql/conf/
chmod 755 -R /opt/mysql/conf/

使用yaml文件创建mysql容器

docker-compose -f mysql-master.yaml up -d

查看mysql容器是否up

docker ps

CONTAINER ID   IMAGE                              COMMAND                  CREATED         STATUS         PORTS                                       NAMES
fe2c05a18662   daocloud.io/library/mysql:5.7.20   "docker-entrypoint.s…"   8 minutes ago   Up 9 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp   docker-compose_mysql_1

注

以下操作在slave节点做

下载mysql.yaml

wget https://ikun.blog/downloads/mysql/mysql-slave.yaml

创建文件夹并给予权限

mkdir -p /opt/mysql/conf
mkdir -p /opt/mysql/dumpdb/
mkdir -p /downloads/mysql/
chmod 755 -R /opt/mysql/conf
chmod 777 -R /opt/mysql/dumpdb/
chmod 777 -R /downloads/mysql/

下载mysql配置文件

wget https://www.ikun.blog/downloads/mysql/my.cnf
wget https://www.ikun.blog/downloads/mysql/mysql.cnf
wget https://www.ikun.blog/downloads/mysql/docker.cnf

修改配置文件

vim my.cnf 
=====================================================================================================================================================
[mysqld]
default-time-zone = '+08:00'
server-id=200	
log-bin=/var/lib/mysql/mysql-bin

mv配置文件到/opt/mysql/conf/下

mv my.cnf /opt/mysql/conf/
mv mysql.cnf /opt/mysql/conf/
mv docker.cnf /opt/mysql/conf/
chmod 755 -R /opt/mysql/conf/

使用yaml文件创建mysql容器

docker-compose -f mysql-slave.yaml up -d

查看mysql容器是否up

docker ps

CONTAINER ID   IMAGE                              COMMAND                  CREATED              STATUS         PORTS                                       NAMES
ad2c88f24530   daocloud.io/library/mysql:5.7.20   "docker-entrypoint.s…"   About a minute ago   Up 2 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp   docker-compose_mysql_1

注

配置主从 以下操作在master节点操作

进入docker容器

docker exec -it fe2c05a18662 /bin/bash

进入mysql

mysql -u root -pcaixukun

查看master状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注

以下操作在slave执行

设置slave

CHANGE MASTER TO master_host = '192.168.x.xx',master_port = 3306,master_user = 'root',master_password = 'caixukun',master_log_file = 'mysql-bin.000004',master_log_pos = 154;

启动slave

start slave;

查看主从状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.x.xx
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 154
              Relay_Log_Space: 523
              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: 150
                  Master_UUID: e3f32dfa-4857-11ed-9fd7-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: 
1 row in set (0.00 sec)

使用Mysql-binlog恢复Table

提示

Mysql BinLog是二进制日志文件，用于记录Mysql数据库的更新或者潜在更新。Mysql主从复制就是依靠Mysql-BinLog来实现。

有两种通过Binlog恢复方法：全量恢复和增量恢复

全量

适用于没有备份，但是有全部Binlog的情况下

使用Binlog1-N全部恢复一遍

mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p123123

注意

要注意恢复顺序

增量

增量适用于有备份的情况下，只需要把丢失的数据恢复

首先要定位从那个Binlog开始恢复，这里是通过备份的Binlog编号来定位的，比如说我们只备份到150，而现在已经跑到了160，那我们就要从150恢复到160。

把指定Binlog二进制文件转成sql文件

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --downloadsbase=mysql mysql-bin.xxxxx > mysql-bin.sql

注

-d 指定数据库 --start-position=指定position位置作为开始 --stop-position=指定position作为结束

position在sql文件里表示为

# at 1137
#220831 22:04:47 server id 11  end_log_pos 1223 CRC32 0xe6be46ca        Query   thread_id=1771248       exec_time=0     error_code=0
SET TIMESTAMP=1661954687/*!*/;
BEGIN
/*!*/;

注

at 1137：为事件的起点 end_log_pos 1223：为事件的终点

拿到sql文件后进入到指定数据库执行suorce操作

use mysql;
source /root/mysql.sql;

相关信息

要写绝对路径

Mysql基础命令

列出所有数据库

show downloadsbases;

进入到某个数据库

use dio;

列出当前库的表

show tables;

查看某一个表的所有数据

SELECT * FROM TableName;

查看某一个表的表结构

describe TableName;

创建新库

create downloadsbase DatabaseName;

查看Mysql Server_id

show variables like 'server_id';

误删除全部Mysql-Binlog后处理方案

首先恢复主库的Binlog输出

  1. 进入数据库执行操作
mysql -u root -p
  1. 直接reset，也不用删多少多少之前的binlog了，因为已经全删了
reset master;
  1. 这时应该已经正常输出Binlog了，也可以选择手动刷一次
flush logs;
  1. 查看当前master状态
show master status;

从库恢复

首先，重置从库数据（删了重新搭建），锁主库表，dump主库表，dump完成后查看master状态，记住master状态后解锁表，从库使用sql文件导入数据，导入完成后从新配置主从即可

注意

mysqldump操作是会锁表的，无法避免

重置从库，具体操作我就不写了

进入数据库进行操作

mysql -u root -p

锁主库表

flush tables with read lock;

查看当前Master状态

show master status;

Dump所有数据

mysqldump -u root -p --all-downloadsbases --single-transaction > all.sql

相关信息

--all-downloadsbases：所有库;--single-transaction：此选项会将隔离级别设置为：REPEATABLE READ。而且随后再执行一条START TRANSACTION语句，让整个数据在dump过程当中保证数据的一致性，这个选项对InnoDB的数据表颇有用，且不会锁表（其实还是会锁，只是改为Dump到哪锁到那，而不是全锁）。

查看Master状态,看看跟锁表完成后的状态是否一致

show master status;

表解锁

unlock tables;

从库使用sql文件进行数据导入

mysql -u root -p < all.sql

导入完成后进行配置Master信息

change master to master_host='x.x.x.x',master_user='root',master_password='xxxx',master_log_file='mysql-bin.xxx',master_log_pos=xxxx;

开启Slave

start slave;

验证主从状态

show slave status\G

自行验证数据是否一致

清理General Log

停用General Log

SET GLOBAL general_log = 'OFF';

清空General Log表

TRUNCATE mysql.general_log;

启用General Log

SET GLOBAL general_log = 'ON';