Mysql数据库
登录
标签搜索
侧边栏壁纸
博主昵称
良辰美景好时光

  • 累计撰写 68 篇文章
  • 累计收到 0 条评论
Mysql

Mysql数据库

lilymaxyz
lilymaxyz
2025-02-12 / 0 评论 / 3 阅读 / 正在检测是否收录...

@TOC

1.范例: 针对一个电商项目创建项目的管理员用户

mysql> create database eshop;
Query OK, 1 row affected (0.00 sec)

mysql> create user eshop@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on eshop.* to eshop@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

ALTER USER 'eshop'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';

2.mysqldump 备份工具

2.1.mysqldump 常见通用选项：:star::star::star::star::star::star:

-u, --user=name               User for login if not current user
-p, --password[=name]         Password to use when connecting to server
-A, --all-databases           #备份所有数据库，含create database
-B, --databases db_name…      #指定备份的数据库，包括create database语句
-E, --events：                #备份相关的所有event scheduler
-R, --routines：            #备份所有存储过程和自定义函数
--triggers：                #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集


--master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-data
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）

-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，
                 #配合-A 或 -B 选项时，会导致刷新多次数据库。
                 #建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，
                 #此时只刷新一次二进制日志

--compact                 #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data             #只备份表结构,不备份数据,即只备份create table
-t, --no-create-info     #只备份数据,不备份表结构,即不备份create table
-n,--no-create-db         #不备份create database，可被-A或-B覆盖
--flush-privileges         #备份mysql或相关时需要使用
-f, --force             #忽略SQL错误，继续执行
--hex-blob                 #使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick             #不缓存查询，直接输出，加快备份速度

2.2.生产环境实战备份策略

2.2.1.InnoDB建议备份策略:star::star::star::star::star::star:

--master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-data
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --master-data=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql


#新版8.0.26以上
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --source-data=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

2.2.2.MyISAM建议备份策略:star::star::star::star::star::star:

mysqldump -uroot -p123456 -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

2.2.3.实战案例：特定数据库的备份脚本

vim mysql_backup.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=123456

[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip >${DIR}/${DB}_${TIME}.sql.gz

2.3.安装数据库脚本【在线离线下载版】

#!/bin/bash

#MySQL5.7 Download URL: 
#https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
#https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
#https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz


#MySQL8.0 Download URL: 
#https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
#https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.39-linux-glibc2.12-x86_64.tar.xz

. /etc/init.d/functions 
SRC_DIR=`pwd`
#MYSQL='mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz'
#URL='https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz'
MYSQL='mysql-8.0.39-linux-glibc2.12-x86_64.tar.xz'
URL='https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.39-linux-glibc2.12-x86_64.tar.xz'
COLOR='echo -e \E[01;31m'
END='\E[0m'
#MYSQL_ROOT_PASSWORD=magedu
MYSQL_ROOT_PASSWORD=123456


check (){

if [ $UID -ne 0 ]; then
  action "当前用户不是root,安装失败" false
  exit 1
fi

cd  $SRC_DIR
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
        $COLOR"正在在线下载，请稍后......"$END
        rpm -q wget || yum -y install wget
        wget ${URL}
elif [ -e /usr/local/mysql ];then
        action "数据库已存在，安装失败" false
        exit
else
    return
fi

if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
        $COLOR"未下载成功,请将相关软件放在${SRC_DIR}目录下"$END
        exit
fi
} 

install_mysql(){
    $COLOR"开始安装./.."$END
    yum  -y -q install libaio numactl-libs ncurses-compat-libs &>/dev/null
    cd $SRC_DIR
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R  root.root /usr/local/mysql/
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; action "创建mysql用户"; }
        
    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
    ln -s /usr/local/mysql/bin/* /usr/bin/
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
[client]
user=root
password=123456
socket=/data/mysql/mysql.sock
default-character-set=utf8mb4
EOF
    [ -d /data ] || mkdir /data 
    mysqld --initialize --user=mysql --datadir=/data/mysql 
    cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
    chkconfig --add mysqld
    chkconfig mysqld on
    service mysqld start
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
    sleep 3
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
    mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
    action "数据库安装完成" 
}


check
install_mysql

3.mysql备份和恢复

3.1.二进制日志记录三种格式

二进制日志记录三种格式

  • 基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
  • 基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
  • 混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

3.3.切换日志文件

mysql> FLUSH LOGS;

3.4.生成二进制日志binlog

[root@Rocky8 ~]#mysqladmin flush-binary-log
[root@Rocky8 ~]#mysqladmin flush-logs
[root@Rocky8 ~]#mysql
MariaDB [hellodb]> flush logs;
  • 生成二进制日志文件的情况
restart mysqld
1G 满了
mysql -e 'flush logs'
mysqladmin flush-logs
mysqldump -F

3.5.启用二进制日志（备份）

#MySQL 8.0 默认使用ROW方式
#基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

3.6.实战案例：恢复误删除的表【案例】:warning:

案例说明：每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的scores表

3.6.1.安装Mysql，启用二进制日志，并导入测试文件

# 安装mysql
yum -y install mysql-server

mysql
# 检查是否启用二进制日志（备份）
mysql> select @@sql_log_bin;
mysql> select @@log_bin;
mysql> show variables like 'binlog_format';
# 修改root密码
mysql> select user,host from mysql.user;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

# 导入测试文件
mysql <hellodb_innodb.sql

3.6.2.检查二进制日志

ll /var/lib/mysql

在这里插入图片描述

3.6.3.备份所有数据库

mkdir /backup
mysqldump -uroot -p123456 -A --source-data=2 >/backup/all.sql

3.6.4.删表---【模拟数据库破坏】:warning:

mysql> use hellodb;
mysql> drop tables scores;

在这里插入图片描述

3.6.5.增加数据---【模拟数据库破坏】:warning:

mysql> insert into teachers (name,age,gender)values('a',18,'M'),('b',20,'F');

在这里插入图片描述

3.6.6.模拟数据库恢复【备份文件+二进制日志】

head /backup/all.sql -n 30

在这里插入图片描述

3.6.7.检查二进制日志

ll /var/lib/mysql

在这里插入图片描述

3.6.8.导出二进制日志文件

mysqlbinlog --start-position=21693 /var/lib/mysql/binlog.000001 >/backup/logbin.sql

grep -ni drop /backup/logbin.sql
#36行
#awk 'NR==36' /backup/logbin.sql
#删除这行
sed -i.bak '/^DROP TABLE `scores`/d' /backup/logbin.sql
sed -Ei.bak '/^DROP TABLE `scores`/s/^/#/' /backup/logbin.sql

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

3.6.9.拷贝到测试机进行还原【测试机还原】:warning:

  • 测试机需要相同的mysql版本环境
rsync -avz /backup/logbin.sql /backup/all.sql root@10.0.0.38:
yum -y install mysql-server
mysql -uroot

# 临时关闭二进制日志
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source all.sql;
mysql> source logbin.sql;

# 临时开启二进制日志
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

3.6.10.检查是否还原【success】

在这里插入图片描述
在这里插入图片描述

3.6.11.导入生产环境进行恢复

  • 单表备份

在这里插入图片描述
在这里插入图片描述

  • 数据恢复
    在这里插入图片描述
    在这里插入图片描述

3.7.保留备份--模拟删除数据库

yum -y install mysql-server
# 备份数据
mysqldump -uroot -p123456 -A -F --single-transaction --source-data=2 >/backup/all.sql

systemctl stop mysqld
rm -rf /data/mysql/*
systemctl start mysqld

#数据库恢复
mysql -uroot </backup/all.sql
#刷新权限
mysql> flush privileges;

4.xtrabackup 备份工具

XtraBackup：https://www.percona.com/downloads
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

5.Mysql主从复制

5.1.主从复制架构及原理:star::star::star::star::star::star:

在这里插入图片描述
在这里插入图片描述

主从复制相关线程：

  • 主节点：

    • dump Thread : 为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

  • 从节点：

    • I/O Thread ：向Master请求二进制日志事件，并保存于中继日志中
    • SQL Thread : 从中继日志中读取日志事件，在本地完成重放

主从复制的基本原理：

  • 主从复制基于二进制日志（Binary Log, binlog）和中继日志（Relay Log）来实现。

  • 主服务器（Master）：

    • 记录所有对数据库进行更改的 SQL 语句（如 INSERT、UPDATE、DELETE 等）到二进制日志（binlog）中。
    • 将 binlog 发送给从服务器。

  • 从服务器（Slave）：

    • 接收主服务器发送的 binlog，并将其写入中继日志（relay log）。
    • 从中继日志中读取 SQL 语句，并在从服务器上执行这些语句，从而保持与主服务器数据的一致性。

5.2.实现主从复制配置【实验】【Mysql.8.0.36】:star::star:

在这里插入图片描述

yum -y install mysql-server
reset slave all;#删除同步信息

5.2.1.主节点配置

5.2.1.1.启用二进制日志

5.2.1.2.为当前节点设置一个全局惟一的ID号

vim /etc/my.cnf

[mysqld]
server-id=8
log-bin=/data/mysql/logbin/mysql-bin
# 存储二进制日志文件夹
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data/mysql/

# 开机启动
systemctl enable --now mysqld

5.2.1.3.查看从二进制日志的文件和位置开始进行复制

mysql> show master status;

在这里插入图片描述

5.2.1.4.创建有复制权限的用户账号

# MySQL8.0 分成两步实现
# 创建账号
mysql> create user repluser@'10.0.0.%' identified by '123456';
# 授权
mysql> grant replication slave on *.* to repluser@'10.0.0.%';

在这里插入图片描述

5.2.2.从节点配置

5.2.2.1.启用二进制日志

5.2.2.2.为当前节点设置一个全局惟一的ID号

vim /etc/my.cnf

[mysqld]
server-id=18            #为当前节点设置一个全局惟的ID号
read_only                #设置数据库只读，针对supper user无效
log-bin=/data/mysql/logbin/mysql-bin
# 存储二进制日志文件夹
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data/mysql/

# 开机启动
systemctl enable --now mysqld

5.2.2.3.查看主从同步状态，设置从库读取主库的服务器配置

mysql> help change master to
mysql> show slave status;

CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=157,
  MASTER_CONNECT_RETRY=2;
主从复制命令参数说明:
master_host : 主数据库的IP地址;
master_port : 主数据库的运行端口;
master_user : 在主数据库创建的用于同步数据的用户账号;
master_password : 在主数据库创建的用于同步数据的用户密码;
master_log_file : 指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数;
master_log_pos : 指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数;
master_connect_retry : 连接失败重试的时间间隔，单位为秒。

在这里插入图片描述
在这里插入图片描述

5.2.2.4.开启线程，查看状态

mysql> show processlist;
mysql> start slave;
mysql> show slave status\G;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

5.3.主从复制【模拟企业环境--备份】

5.3.1.主节点【先整体备份数据库】

vim /etc/my.cnf

[mysqld]
server-id=8
log-bin=/data/mysql/logbin/mysql-bin
# 存储二进制日志文件夹
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data/mysql/

# 开机启动
systemctl enable --now mysqld
mysql <hellodb_innodb.sql

# MySQL8.0 分成两步实现
# 创建账号
mysql> create user repluser@'10.0.0.%' identified by '123456';
# 授权
mysql> grant replication slave on *.* to repluser@'10.0.0.%';

mysql> show master status;
# 数据库备份
mysqldump -uroot -A -F --single-transaction --source-data=1 >/data/full_back.sql

#拷贝到从节点上
scp /data/full_back.sql 10.0.0.18:/data/

5.3.2.从节点

vim /etc/my.cnf

[mysqld]
server-id=18
read_only
log-bin=/data/mysql/logbin/mysql-bin
# 存储二进制日志文件夹
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data/mysql/

# 开机启动
systemctl enable --now mysqld

5.3.3.从节点还原数据库

  • 临时将二进制日志关闭，恢复数据后再开启
  • 可以将change master to放在修改文件里
  • 也可以直接进入mysql，先还原数据库，再change maseter
    在这里插入图片描述
CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=157,
  MASTER_CONNECT_RETRY=2;

image-20241126105804807
在这里插入图片描述

# 临时关闭二进制日志
mysql> set sql_log_bin=0;
mysql> source /data/full_back.sql;

# 检查工作
mysql> show slave status\G;
# 开启线程
mysql> start slave;
mysql> show processlist;
mysql> show slave status\G;

# 开启二进制日志
mysql> set sql_log_bin=1;

在这里插入图片描述

5.4.下载mysql-server及依赖包到本地

mkdir mysql-server-packages-8.0.36
cd mysql-server-packages-8.0.36
yum install yum-utils -y
yumdownloader --resolve mysql-server

# 安装
yum -y install *

5.5.三台主机实现级联复制

  • 需要在中间的从服务器启用以下配置 ，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制
    在这里插入图片描述
  • 同前面
#在10.0.0.8充当master
#在10.0.0.18充当级联slave
#在10.0.0.28充当slave
  • 18机器
vim /etc/my.cnf

[mysqld]
server-id=18
read_only
log-bin=/data/mysql/logbin/mysql-bin
#级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加,其它版本默认不开启
log_slave_updates

mysqldump -uroot -A -F --single-transaction --source-data=1 >/data/all.sql

scp /data/all.sql 10.0.0.28:
  • 28机器
CHANGE MASTER TO
  MASTER_HOST='10.0.0.18',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=157,
  MASTER_CONNECT_RETRY=2;
mysql> set sql_log_bin=0;

mysql> source all.sql;
mysql> show slave status\G;
mysql> start slave;
mysql> show slave status\G;

mysql> set sql_log_bin=1;

5.6.主主复制【会发生冲突】:warning::warning::warning:

  • 在一主一从基础上
  • 机器修改都一样
vim /etc/my.cnf

[mysqld]
server-id=18
#read_only
log-bin=/data/mysql/logbin/mysql-bin
#log_slave_updates
  • 8机器，需要在18机器查看下日志位置
CHANGE MASTER TO
  MASTER_HOST='10.0.0.18',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=465;
  • 18机器，查位置

在这里插入图片描述

  • 8机器

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

  • 测试添加记录是否同步
mysql> insert into teachers (name,age,gender)values('Libai',20,'M');

mysql> insert into teachers (name,age,gender)values('王五',50,'F');

mysql> select * from teachers;

5.7.实现半同步复制【主从复制基础上】【Rocky8】

  • :one:主服务器安装插件8
#主服务器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插件
mysql> UNINSTALL PLUGIN rpl_semi_sync_master ;  #删除插件
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql> SHOW PLUGINS; #查看插件
mysql> SHOW GLOBAL STATUS LIKE '%semi%'; #查看有几个从节点，客户端

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

  • :two:从服务器安装插件18
#从服务器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #永久安装插件
mysql> UNINSTALL PLUGIN rpl_semi_sync_slave ;  #删除插件
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql> SHOW PLUGINS; #查看插件

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

  • :three:主服务器8
vim /etc/my.cnf

[mysqld]
server-id=8
log-bin=/data/mysql/logbin/mysql-bin
rpl_semi_sync_master_enabled  #修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动
rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端

systemctl restart mysqld
mysql> select @@rpl_semi_sync_master_enabled;

在这里插入图片描述

  • :four:从服务器18
vim /etc/my.cnf

[mysqld]
server-id=18
log-bin=/data/mysql/logbin/mysql-bin
rpl_semi_sync_slave_enabled  #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

systemctl restart mysqld
mysql> select @@rpl_semi_sync_slave_enabled;

在这里插入图片描述

  • :five:从服务器28
vim /etc/my.cnf

[mysqld]
server-id=28
log-bin=/data/mysql/logbin/mysql-bin
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data/mysql/
systemctl start mysqld
  • 安装插件，更改配置，重启服务
#从服务器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #永久安装插件
mysql> UNINSTALL PLUGIN rpl_semi_sync_slave ;  #删除插件
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql> SHOW PLUGINS; #查看插件
vim /etc/my.cnf

[mysqld]
server-id=28
log-bin=/data/mysql/logbin/mysql-bin
rpl_semi_sync_slave_enabled

systemctl restart mysqld
mysql> select @@rpl_semi_sync_slave_enabled;

在这里插入图片描述

CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000007',
  MASTER_LOG_POS=157,
  MASTER_CONNECT_RETRY=2;

6.复制的问题和解决方案:star::star::star::star::star:

6.1.数据损坏或丢失

  • Master：MHA + semisync replication
  • Slave： 重新复制

6.2.不惟一的 server id

  • 解决方法: 重新复制

6.3.复制延迟

  • 升级到MySQL5.7以上版本(5.7之前的版本，没有开GTID之前，主库可以并发事务，但是dump传输时是串行)利用GTID(MySQL5.6需要手动开启,MySQL5.7以上默认开启)支持并发传输binlog及并行多个SQL线程
  • 减少大事务,将大事务拆分成小事务
  • 减少锁
  • sync_binlog=1 加快binlog更新时间,从而加快日志复制
  • 需要额外的监控工具的辅助
  • 多线程复制：对多个数据库复制
  • 一从多主：Mariadb10 版后支持

6.4.MySQL 主从数据不一致

6.4.1.造成主从不一致的原因

  • 主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
  • 主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
  • 从节点未设置只读，误操作写入数据
  • 主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
  • 主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
  • 主从sql_mode 不一致
  • MySQL自身bug导致

6.4.2.主从不一致修复方法

:one:将从库重新实现
虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。

:two:使用percona-toolkit工具辅助
PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用
关于使用方法，可以参考下面链接：https://www.cnblogs.com/feiren/p/7777218.html

:three:手动重建不一致的表
在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致
这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的

:four:范例：A,B,C这三张表主从数据不一致

1、从库停止Slave复制
mysql> stop slave;

2、在主库上dump这三张表，并记录下同步的binlog和POS点
mysqldump -uroot -pmagedu -q --single-transaction --master-data=2 testdb A B C >/backup/A_B_C.sql

3、查看A_B_C.sql文件，找出记录的binlog和POS点
head A_B_C.sql
例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666;

#以下指令是为了保障其他表的数据不丢失，一直同步直到那个点结束，A,B,C表的数据在之前的备份已
经生成了一份快照，只需要导入进入，然后开启同步即可
4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置
mysql> start slave until MASTERLOGFILE='mysql-bin.888888',MASTERLOGPOS=666666;

5、在Slave机器上导入A_B_C.sql
mysql -uroot -pmagedu testdb
mysql> set sql_log_bin=0;
mysql> source /backup/A_B_C.sql
mysql> set sql_log_bin=1;

6、导入完毕后，从库开启同步即可。
mysql> start slave;

6.4.3.如何避免主从不一致

  • 主库binlog采用ROW格式
  • 主从实例数据库版本保持一致
  • 主库做好账号权限把控，不可以执行set sql_log_bin=0
  • 从库开启只读，不允许人为写入
  • 定期进行主从一致性检验

7.实现MHA实战案例

  • 项目
  • 我在工作中设置了mysql主从，设置了半同步，设置了GTD，并且我又添加了MHA
    在这里插入图片描述
  • 环境：四台主机
ip版本角色
10.0.0.7Centos7MHA管理端
10.0.0.8Rocky8MySQL8.0 Master
10.0.0.18Rocky8MySQL8.0 Slave1
10.0.0.28Rocky8MySQL8.0 Slave2

7.1.在管理节点上安装两个包mha4mysql-manager和mha4mysql-node【manager】

  • 说明:
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm 只支持CentOS7上安装,
不支持在CentOS8安装,支持MySQL5.7和MySQL8.0 ,但和CentOS8版本上的Mariadb-10.3.17不兼容

mha4mysql-manager-0.56-0.el6.noarch.rpm 不支持CentOS 8，只支持CentOS7及以下版本
  • 两个安装包：
mha4mysql-manager
mha4mysql-node
#下载
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58
https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58
  • 案例：
yum -y install epel-release
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ql mha4mysql-manager

7.2.在所有MySQL服务器上安装mha4mysql-node包

  • 此包支持CentOS 8，7，6
yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

7.3.在所有节点实现基于 ssh-key 的免密登录

#生成密钥对，并在当前主机完成C/S校验
[root@mha-manager ~]# ssh-keygen
[root@mha-manager ~]# ssh-copy-id 127.0.0.1
[root@mha-manager ~]# ll .ssh

#分发
[root@mha-manager ~]# rsync -av .ssh 10.0.0.8:/root/
[root@mha-manager ~]# rsync -av .ssh 10.0.0.18:/root/
[root@mha-manager ~]# rsync -av .ssh 10.0.0.28:/root/

#测试ssh连接
......

7.4.在 mha-manager 节点创建相关配置文件

mkdir /etc/mastermha/
vim /etc/mastermha/app1.cnf

[server default]
user=mhauser #用于远程连接MySQL所有节点的用户,需要有管理员的权限
password=123456
manager_workdir=/data/mastermha/app1/ #目录会自动生成,无需手动创建
manager_log=/data/mastermha/app1/manager.log #当前集群的日志
remote_workdir=/data/mastermha/app1/ #mysql 节点mha 工作目录，会自动创建
ssh_user=root #用于实现远程ssh基于KEY的连接,访问二进制日志
repl_user=repluser #主从复制的用户信息
repl_password=123456
ping_interval=1 #健康性检查的时间间隔，manager节点对于master节点的心跳检测时间间隔
master_ip_failover_script=/usr/local/bin/master_ip_failover #切换VIP的perl脚本,不支持跨网络,也可用Keepalived实现
report_script=/usr/local/bin/sendmail.sh #当执行报警脚本
check_repl_delay=0 #默认值为1,表示如果slave中从库落后主库relay log超过100M，主库不会选择这个从库为新的master，
                   #因为这个从库进行恢复需要很长的时间.通过设置参数check_repl_delay=0，mha触发主从切换时会忽略复制的延时，
                   #对于设置candidate_master=1的从库非常有用，这样确保这个从库一定能成为最新的master
master_binlog_dir=/data/mysql/logbin/ #指定二进制日志存放的目录,mha4mysql-manager-0.58必须指定,之前版本不需要指定

[server1]
hostname=10.0.0.8
port=3306
candidate_master=1
[server2]
hostname=10.0.0.18
port=3306
[server3]
hostname=10.0.0.28
port=3306
candidate_master=1 #设置为优先候选master，即使不是集群中事件最新的slave,也会优先当master
[server default]
user=mhauser
password=123456
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=123456
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0 
master_binlog_dir=/data/mysql/logbin/

[server1]
hostname=10.0.0.8
port=3306
candidate_master=1
master_binlog_dir="/data/mysql/"

[server2]
hostname=10.0.0.18
port=3306
master_binlog_dir="/data/mysql/"

[server3]
hostname=10.0.0.28
port=3306
candidate_master=1
master_binlog_dir="/data/mysql/"
  • 说明: 主库宕机谁来接管新的master
提升 slave 节点为 master 节点的策略
1. 所有从slave 节点日志都是一致的，默认会以配置文件的顺序去选择一个新主
2. 从节点日志不一致，自动选择数据量最接近于主库的从库充当新主，将其提升为master 节点。
3. 如果对于某节点设定了权重（candidate_master=1），权重节点会优先选择。但是此节点日志量落后主库超过100M日志的话，也不会被选择。
4. 可以配合check_repl_delay=0，关闭日志量的检查，强制选择候选节点

7.5.发送邮件脚本:star::star::star::star::star:

# 告警消息脚本
[root@mha-manager ~]# cat /usr/local/bin/sendmail.sh
#!/bin/bash
echo "MHA is failover!" | mail -s "MHA Warning" 649352141@qq.com
[root@mha-manager ~]# chmod +x /usr/local/bin/sendmail.sh

# 安装邮件服务
[root@mha-manager ~]# yum install mailx postfix

# 邮件服务配置
[root@mha-manager ~]# vim /etc/mail.rc
# 加在最下面
#发件箱
set from="2636775731@qq.com"
# 配置的第三方smtp服务器的地址及端口
set smtp=smtp://smtp.qq.com:587
#发件人
set smtp-auth-user="2636775731@qq.com"
#授权码
set smtp-auth-password=ttnvcrlfgywididb
# 认证方式
set smtp-auth=login
#开启ssl
set ssl-verify=ignore
set smtp-use-starttls=yes
#证书目录，下方为centos系统证书默认位置，也自行生成证书并指定
set nss-config-dir=/etc/pki/nssdb


--说明
from：对方收到邮件时显示的发件人
smtp：指定第三方发邮件的smtp服务器地址,云服务器必须使用465端口默认25端口被禁用
set smtp-auth-user：第三方发邮件的用户名
set smtp-auth-password：  邮箱的授权码注意不是密码

#echo "测试邮件" | mail -s "发送成功" 2636775731@qq.com &>/dev/null

# 重新启动postfix
[root@mha-manager ~]# systemctl restart postfix.service

# 测试告警邮件
[root@mha-manager ~]# sendmail.sh
[root@mha-manager ~]# vim /usr/local/bin/master_ip_failover
#!/usr/bin/env perl

#  Copyright (C) 2011 DeNA Co.,Ltd.
#
#  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; either version 2 of the License, or
#  (at your option) any later version.
#
#  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 Street, Fifth Floor, Boston, MA  02110-1301  USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;
use warnings FATAL => 'all';

use Getopt::Long;
use MHA::DBHelper;

my (
  $command,        $ssh_user,         $orig_master_host,
  $orig_master_ip, $orig_master_port, $new_master_host,
  $new_master_ip,  $new_master_port,  $new_master_user,
  $new_master_password
);

# 修改内容
my $vip = '10.0.0.100/24'; #virtually IP，此IP会在不同的MySQL节点漂移
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; #在网卡上添加IP，确保每台 MySQL 节点网卡名一样
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

GetOptions(
  'command=s'             => \$command,
  'ssh_user=s'            => \$ssh_user,
  'orig_master_host=s'    => \$orig_master_host,
  'orig_master_ip=s'      => \$orig_master_ip,
  'orig_master_port=i'    => \$orig_master_port,
  'new_master_host=s'     => \$new_master_host,
  'new_master_ip=s'       => \$new_master_ip,
  'new_master_port=i'     => \$new_master_port,
  'new_master_user=s'     => \$new_master_user,
  'new_master_password=s' => \$new_master_password,
);

exit &main();

sub main {
  if ( $command eq "stop" || $command eq "stopssh" ) {

    # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
    # If you manage master ip address at global catalog database,
    # invalidate orig_master_ip here.
    my $exit_code = 1;
    eval {

      # updating global catalog, etc
      $exit_code = 0;
    };
    if ($@) {
      warn "Got Error: $@\n";
      exit $exit_code;
    }
    exit $exit_code;
  }
    elsif ( $command eq "start" ) {

        # all arguments are passed.
        # If you manage master ip address at global catalog database,
        # activate new_master_ip here.
        # You can also grant write access (create user, set read_only=0, etc) here.
        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &start_vip();
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn $@;
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "status" ) {
        print "Checking the Status of the script.. OK \n";
        `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}


sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master 
sub stop_vip() {
   `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}


sub usage {
  print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
# 加上执行权限
chmod +x /usr/local/bin/master_ip_failover

7.6.实现Master

# 在 master 节点配置 VIP，此IP会在不同 MySQL 节点上漂移
ifconfig eth0:1 10.0.0.100/24
yum -y install mysql-server

vim /etc/my.cnf
[mysqld]
server-id=8
log-bin=/data/mysql/mysql-bin

mkdir -p /data/mysql/
chown -R mysql.mysql /data/mysql/
systemctl restart mysqld
[root@master ~]#mysql
mysql> show master logs;
# 如果是MySQL8.0执行下面操操作
#创建主从同步账号并授权
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
#创建 mha-manager 使用的账号并授权
mysql> create user mhauser@'10.0.0.%' identified by '123456';
mysql> grant all on *.* to mhauser@'10.0.0.%';

# 如果是MySQL8.0以前版本执行下面操操作
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
mysql> grant all on *.* to mhauser@'10.0.0.%' identified by '123456';

7.7.实现slave1

yum -y install mysql-server

vim /etc/my.cnf
[mysqld]
server-id=18
read-only
log-bin=/data/mysql/mysql-bin


mkdir -p /data/mysql/
chown -R mysql.mysql /data/mysql/
systemctl restart mysqld
[root@slave1 ~]#mysql
mysql>
CHANGE MASTER TO 
MASTER_HOST='10.0.0.8', 
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=157;

mysql> start slave;
mysql> show slave status\G;
# 重置slave
mysql> stop slave;
mysql> reset slave all;

7.8.实现slave2

yum -y install mysql-server

vim /etc/my.cnf
[mysqld]
server-id=28    #不同节点此值各不相同
read-only
log-bin=/data/mysql/mysql-bin


mkdir -p /data/mysql/
chown -R mysql.mysql /data/mysql/
systemctl restart mysqld
[root@slave1 ~]#mysql
mysql>
CHANGE MASTER TO 
MASTER_HOST='10.0.0.8', 
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=157;

mysql> start slave;
mysql> show slave status\G;
# 重置slave
mysql> stop slave;
mysql> reset slave all;

7.9.检查MHA的环境

#检查环境
#配置和 SSH 连接检查
vim /etc/mastermha/app1.cnf

#主从复制检查，会在mysql节点自动创建 remote_workdir=/data/mastermha/app1/
masterha_check_ssh --conf=/etc/mastermha/app1.cnf
masterha_check_repl --conf=/etc/mastermha/app1.cnf

#查看当前mysql 集群状态
masterha_check_status --conf=/etc/mastermha/app1.cnf

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

7.10.启动MHA

#开启MHA,默认是前台运行,生产环境一般为后台执行，并且与终端分离
nohup masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failover &> /dev/null

#测试环境：
#masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failover

#如果想停止后台执行的MHA,可以执行下面命令
masterha_stop --conf=/etc/mastermha/app1.cnf
Stopped app1 successfully.

#查看状态
masterha_check_status --conf=/etc/mastermha/app1.cnf

#查看生成的文件
tree /data/mastermha/app1/
#查看日志
cat /data/mastermha/app1/manager.log

在这里插入图片描述
在这里插入图片描述

#开启 master 节点通用日志
mysql> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
tail -f /var/lib/mysql/Rocky8.log

在这里插入图片描述

7.11.排错日志

tail /data/mastermha/app1/manager.log

在这里插入图片描述

7.12.模拟故障

#模拟故障
[root@master ~]#systemctl stop mysqld

#当 master down机后,mha管理程序自动退出
[root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf

在这里插入图片描述

在这里插入图片描述

[root@mha-manager ~]#cat /data/mastermha/app1/manager.log

[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf

#验证VIP漂移至新的Master上
[root@slave1 ~]#ip a

#自动修改manager节点上的配置文件,将master剔除
[root@mha-manager ~]#cat /etc/mastermha/app1.cnf
[server2]
candidate_master=1
hostname=10.0.0.18
master_binlog_dir="/data/mysql/"
port=3306

[server3]
hostname=10.0.0.28
master_binlog_dir="/data/mysql/"
port=3306

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

  • 注意: 如果出错,需要删除下面文件再执行MHA
[root@mha-manager ~]#rm -f /data/mastermha/app1/app1.failover.error

在这里插入图片描述

7.13.修复主从

修复故障的主库,保证数据同步
修复主从,手工新故障库加入新的主,设为为从库
修复manager的配置文件
清理相关目录
检查ssh互信和replication的复制是否成功
检查VIP,如果有问题,重新配置VIP
重新运行MHA,查询MHA状态,确保运行正常

7.14.如果再次运行MHA,需要先删除下面文件

  • MHA只能漂移一次，如果多次使用必须删除以下文件，要不MHA不可重用
[root@mha-manager ~]#rm -rf /data/mastermha/app1/ # mha_master自己的工作路径
[root@mha-manager ~]#rm -rf /data/mastermha/app1/manager.log #m ha_master自己的日志文件
[root@master ~]#rm -rf /data/mastermha/app1/ # 每个远程主机即三个节点的的工作目录

endl

0
暂无标签
版权属于： lilymaxyz
本文链接： https://www.hwpdyl.com/index.php/archives/56/
作品采用： 《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权

评论

博主关闭了所有页面的评论