mysql主从理解配置和主从备份与恢复实操

二进制日志(bin-log)的说明——主从的前提

bin-log的相关字段和文件说明

查看日志日否开启等信息情况

log_bin：是否开启
log_bin_basename：二进制日志文件名字(实际情况下会在后面加上.索引来命名文件)
log_bin_index：记录日志文件的索引文件

查看准作为主从中的主库的信息。

File：当前使用的二进制日志的文件和索引
Possition：当前日志运行到那个点了

对应的二进制日志文件和索引文件的关系。

注意：每次mysql重启都会重新开启一个mysql-bin.*日志文件。长期不重启mysql-bin文件会过大。还是请dba来处理文件过大问题吧/(ㄒoㄒ)/~~。

如何查看文件内容

方法1：mysqlbinlog命令查看

mysqlbinlog 命令，以用户可视的方式展示出二进制日志中的内容。同时，也可以将其中的内容读取出来，供其他MySQL实用程序使用。

为了方便，我们将mysql其他相关命令加入到系统命令行中。不需要或已经懂的直接跳过。

查看日志
[root@MiWiFi-R3P-srv data]# mysqlbinlog ./mysql-bin.000006

方法2：show binlog events命令查看

show binlog events 命令查看某个binlog日志内容。

命令：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

选项解析：

  IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)
  FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
  LIMIT [offset,] 偏移量(不指定就是0)
  row_count 查询总条数(不指定就是所有行)

实例：

A.查询第一个(最早)的binlog日志：
  mysql> show binlog events\G; 

B.指定查询 mysql-bin.000021 这个文件：
  mysql> show binlog events in 'mysql-bin.000021'\G;

C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
  mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
  mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
  mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

实操截图

[root@MiWiFi-R3P-srv data]# mysqlbinlog mysql-bin.000001 --start-position 3078 --stop-position 4413 | mysql -u root -p

主从配置

master(1)-slave(1)实现过程

1.开启主库的binlog

在主库的mysql中:

2.主库中配置复制账号和账号的权限

主库中创建复制账号和分配账号权限

在从库中连接测试

4.配置从库配置文件

在从库中配置

在实际过程中，很可能不会进行整个mysql从节点的设置。一般情况下，会设置某个库或者某个表进行主从配置。在这个情况下，选择下面的配置参数到从库配置文件中就可以了

5.启动复制

在从库中执行：

# 参数说明
master_host：# master ip
master_port: # master 端口号
master_user: # 连接主库的用户名
master_password: # 连接主库的密码
master_log_file: # 启动是开始读取的二进制日志
master_log_pos: # 打算从主库开始复制的日志节点，对应日志文件中的position

# 执行语句
change master to
master_host='192.168.153.129',
master_port=3306,
master_user='slave_user',
master_password='slave_pwd',
master_log_file='mysql-bin.000001',
master_log_pos=0;

# 启动从节点
start slave;

# 停止从节点
stop slave;

# 清除slave信息(配置有误的时候用)
reset slave;

验证从库是否启动正常(在从库中执行）：

show slave status\G

# 主要看字段信息
slave_io_running：表示异步连接主库进行binlog同步的IO是否正常
slave_sql_running：表示中继日志文件同步到磁盘是否正常

Last_IO_Errno: 对应slave_io_running错误的错误码
Last_IO_Error: 对应slave_io_running错误的错误信息
Last_SQL_Errno: 对应slave_sql_running错误的错误码
Last_SQL_Error: 对应slave_sql_running错误的错误信息

关于slave_io_running和slave_sql_running图片解释(来自于某视频)

相关报错:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
data目录中有auto.cnf文件这个和原来的克隆过来冲突，将该文件删除重启就好了

6.检查结果

在主库和从库分别执行查看对应的库和表数据。

在主库中执行sql操作语句

insert into user(name) value('李四');

在从库中进行检验

select * from user;

检验成功！！！

MySQL主要复制启动配置

MySOL安装配置的时候，已经介绍了几个启动时的常用参数，其中包括MASTER HOST、MASTER PORT、MASTER_USER、MASTER PASSWORD、MASTER_LOG_FILE 和MASTER LOG POS。这几个参数需要在从服务器上配置，下面介绍几个常用的启动选项，如log-slave-updates、master-connect-retry、read-only 和slave-skip-errors等。

（1）log-slave-updates log-slave updates 参数主要用来配置从服务器的更新是否写入二进制日志，该选项默认是不打开的，如果这个从服务器同时也作为其他服务器的主服务器，搭建一个链式的复制，那么就需要开启这个选项，这样他的从服务器才能获取它的二进制日志进行同步操作。更加详细的请看本文最下面的原理解释。

（2）master-connect-retry master-connect-retry参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒。

（3）read-only read-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作。如果主数据库创建了一个普通用户，在默认情况下，该用户是可以更新从数据库中的数据的，如果使用read-only选项启动从数据库以后，该用户对从数据库的更新会提示错误。使用read-only选项启动语法如下。

（4）slave-skip-errors 在复制的过程中，从服务器可能会执行BINLOG中的错误的SQL语句，此时如果不忽略错误，从服务器将会停止复制进程，等待用户处理错误。这种错误如果不能及时发现，将会对应用或者备份产生影响。slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，设置该参数后，MySQL会自动跳过所配置的一系列错误，直接执行后面的SQL语句，该参数可以定义多个错误号，如果设置成all，则表示跳过所有的错误，具体语法如下：

vi /etc/my. cnf
slave-skip-errors=1007,1051,1062

如果从数据库主要是作为主数据库的备份，那么就不应该使用这个启动参数，设置不当，很可能造成主从数据库的数据不同步。如果从数据库仅仅是为了分担主数据库的查询压力，并且对数据的完整性要求不是很严格，那么这个选项可以减轻数据库管理源维护从数据库的工作量。

主从数据库备份和恢复(高级篇）

在实际项目中，数据的主从同步配置大多情况下都是项目上线或运行一段时间后开始考虑主从同步配置（大厂可能会直接上吧ԾㅂԾ）。数据库开始可能二进制日志(bin-log)都没开启。这个情况下，开启主从同步，就需要从库与主库的数据保持一致，才能开启主从同步。

另外一些情况就是单库数据提供了一个比较大的系统运行，发现数据库的瓶颈了，需要开始做读写分离来解决了，但是有不想要停止数据库来备份，那就需要深入考虑和使用如何做主从数据备份和恢复了。

有几种办法来初始化备库或者从其他服务器克隆数据大备库。包括从主库复制数据、从另一台备库克隆数据等等。主要的思路就是：

在某个时间点的主库的数据快照。
二进制日志文件（其中必须拥有快照时间点到现在的二进制日志）。
数据快照的时间点和该时间点对应的二进制日志文件中的偏移量，我们把这两个值成为日志文件坐标。通过这两个值可以确定二进制日志的位置。可以通过SHOW MASTER STATUS命令来获取这些值。

数据库的备份可按照两种方式进行划分：备份方式划分和运行方式划分。

备份方式划分

逻辑备份：逻辑备份又称为导出，比如使用navicate工具的导出功能

*优点：灵活性高，版本要求不算特别高，小数据量OK，可以拿到各种地方用*
*缺点：慢，大数据量不适合。*

物理备份：物理备份可以称为暴力备份，就是直接把整个mysql的data文件进行copy备份。

*优点：快，简单粗暴*
*缺点：迁移到新的地方，需要重新配置一些新的信息，还有版本要和新的地方保持一致*

运行方式划分

冷备份：数据库停止服务运行或者停止写操作，然后在这个基础上进行备份。

*优点：数据安全，不会出现这边写，这边又备份，最后导致数据不一致*
*缺点：需要停止服务，不适合希望不停止服务的项目*

热备份：在服务不停机的情况下进行在线备份。

*优点：服务照常运行，不需要停止服务器*
*缺点：技术要求高，备份中需要用到binlog*

在实际的工作中，我们一般对于数据库优化或者进行架构的时候主数据库往往是会有数据的，而从一开始就进行主从的基本很少。所以这并非我们实际的情况。

备份的抉择
一般来说凡是有用的数据库文件尽量不删除，都进行备份。
根据备份周期(根据服务器的负载、评估、上一次的备份恢复的操作时间来决定)，备份保留时间等实际情况来指定备份计划。

接下来就主要讲解几种备份与恢复方式。

mysqldump数据备份与恢复(逻辑备份)

mysql自带逻辑备份命令工具：mysqldump。它位于mysql项目文件的bin目录下。

主从备份与恢复步骤

在主库中开启binlog功能
同主从复制,见上方

在主库中配置复制账号和账号的权限

create user 'dump_user'@'%' identified by 'dump_pwd';
-- 如果是用mysqldump 来做备份、那么备份用户的相关权限如下
grant select on *.* to dump_user@'%'; 
grant show view on *.* to dump_user@'%'; 
grant trigger on *.* to dump_user@'%';
-- 如果要产生一份一致的备份 mysqldump 要有lock tables 权限。这里要用到mysqldump就必须加这个权限
grant lock tables on *.* to dump_user@'%'; 
grant process on *.* to dump_user@'%';

主库停止写操作
这里我们采用主库中对数据库全局加锁的方式。

-- 主库中加锁
-- 理论上备份账户有锁权限，我觉得这里其实就不用该命令了。不过本人没试过(*^_^*)。
mysql >  flush tables with read lock;

从库备份
使用mysqldump进行从库备份

# 可以用这个命令多试试，就能找出账户需要什么权限和其他错误了。
[root@MiWiFi-R3-srv data]# mysqldump -h 192.168.31.162 -u dump_user -p laravel-shop
[root@MiWiFi-R3-srv data]# mysqldump -h 192.168.31.162 -u dump_user -p laravel-shop > /home/laravel-shop.sql

主库释放锁

-- 在主库中的mysql执行解锁
-- 步骤3 不需要的话，这里也就不需要了
mysql >  unlick tables; // 主库中解锁

从库进行主从的slave配置。
请看上方知识主从配置内容。
从库进行与主库的slave的"change master to..."设
请看上方知识主从配置内容。

恢复数据
在从库中执行

[root@MiWiFi-R3-srv data]# mysql -f -u root -p laravel-shop < /home/laravel-shop.sql

开启同步

-- 在从库中执行
mysql > start slave;

mydumper数据备份与恢复(逻辑备份)(第三方)

mydumper是一个针对MySQL和drizzle的高性能多线程的备份和恢复工具。此工具的开发人员分别来自MySQL、facebook、skysql公司、目前已经有一些大型产品业务测试并使用了该工具。我们在恢复数据库时也可使用myloader工具。 Mydumper的主要特性包括：

· 采用轻量级C语言写的代码。 · 相比于mysqldump，其速度快了近10倍。 · 具有事务性和非事务性表一致的快照（适用于0.2.2+）。 · 可快速进行文件压缩（File compression on-the-fly）。 · 支持导出binlog。 · 可多线程恢复（适用于0.2.1+）。 · 可以用守护进程的工作方式，定时扫描和输出连续的二进制日志。

步骤

下载安装
从库中执行

yum install glib2-devel zlib-devel pcre-devel cmake
git clone https://github.com/maxbube/mydumper.git
cd mydumper
cmake .
make
make install
# 验证安装是否成功
mydumper -V

# 参数介绍
-host，-h：连接的MySQL服务器。 
-user，-u：用户备份的连接用户。
-password，-p：用户的密码。 
-port，-P：连接端口。 
-socket，-S：连接socket文件。
-database，-B：需要备份的数据库。 
-table-list，-T：需要备份的表，用逗号（，）分隔。 
-outputdir，-o：输出的目录。 
-build-empty-files，-e：默认无数据则只有表结构文件。 
-regex，-x：支持正则表达式，如mydumper-regex'（2l（mysqltest）'。 
-ignore-engines，-i：忽略的存储引擎。 
-no-schemas，-m：不导出表结构。 
-long-query-guard：长查询，默认60s。 
-kill-long-queries，-k：可以设置kill长查询。 
-verbose，-v：0=silent，1=errors，2=warmings，3=info，默认是2。 
-binlogs，-b：导出binlog。
-daemon，-D：启用守护进程模式。 
-snapshot-interval，-I:dump快照间隔时间，默认60s。 
-logfile，-L:mysaqldumper的目志输出，一般在Daemon模式下使用。

导出数据
从库中执行

[root@MiWiFi-R3-srv home]# mydumper -h 192.168.31.162 -u root -p adminadmin123 -B laravel-shop -o /home/laravel-shop-mydumper
# 这里可以直接-p 填写密码。
# -B 表示对应要导出的主库中的哪个数据库，如果不填写，表示主库中的所有数据库。
# -o 表示导出到从服务器中的哪个目录，注意是目录不是文件；导出后可以进入该目录中查看，是一些.sql文件的。

导入(恢复)数据

[root@MiWiFi-R3-srv home]# myloader -u root -p adminadmin123 -B laravel-shop -d /home/laravel-shop-mydumper/

与mysqldump效率对比的执行窍门
time 执行命令，可以查看命令执行完成所需要的时间来对比mydumper和mysqldump的执行效率
如：time mydumper -h 192.168.31.162 -u root -p adminadmin123 -B laravel-shop -o /home/laravel-shop-mydumper可得出任务执行花费的时间。

xtralBackup(热备份)

热备份的方式也是直接复制数据物理文件，和冷备份一样，但热备份可以不停机直接复制，一般用于7×24小时不间断的重要核心业务。MySQL社区版的热备份工具ImnoDB Hot Backup是付费的，只能试用30天，只有购买企业版才可以得到永久使用权。Percona公司发布了一个xtrabackup热备份工具，和官方付费版的功能一样，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDBHot Backup的一个很好的替代品。下面具体介绍一下这个软件的使用方法。

xtrabackup是Percona公司的开源项目，用以实现类似ImnoDB官方的热备份工具ImmoDB Hot Backup的功能，它能非常快速地备份与恢复MySQL数据库。xtrabackup中包含两个工具：

· xtrabackup是用于热备份InnoDB及XtraDB表中数据的工具，不能备份其他类型的表，也不能备份数据表结构。 · innobackupex是将xtrabackup进行封装的perl脚本，它提供了备份MyISAM表的能力。由于innobackupex的功能更为全面完善，所以一般选择innobackupex来进行备份。

对于热备份实现解释

innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；
xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）
xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）
innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。
当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；
xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；
innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；
最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。

步骤

下载安装
这里特别需要注意，xtralBackup不同的版本对应不同的mysql版本,所以要到官网查看对应的版本。
可以查看官网，我这里用的是mysql5.7，所以用2.4版本的。地址为：https://www.percona.com/doc/p...，请选择对应的linux操作系统进行安装。

# 下载
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
# 安装
yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm -y # 本地安装

Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
   --host     指定主机
   --user     指定用户名
   --password    指定密码
   --port     指定端口
   --databases     指定数据库
   --incremental    创建增量备份
   --incremental-basedir   指定包含完全备份的目录
   --incremental-dir      指定包含增量备份的目录
   --apply-log        对备份进行预处理操作
 一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
   --redo-only      不回滚未提交事务
   --copy-back     恢复备份目录

主库中执行备份操作
针对数据库data下的文件

[root@MiWiFi-R3-srv home]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=adminadmin123 --backup /home/laravel-shop-innobackupex
# 主库中执行，设置对应的mysql的配置文件为/etc/my.cnf。
# 设置数据库用户名为root,设置密码为adminadmin123。
# 设置备份到的目录为/home/laravel-shop-innobackupex。
# 就可以在/home/laravel-shop-innobackupex看到备份过来的文件了。

把备份的主库中文件传递给从库

[root@MiWiFi-R3-srv home]# scp -r /home/laravel-shop-innobackupex/ root@192.168.31.207:/home/laravel-shop-innobackupex
 # 主库中执行；把主库下的整个备份文件夹/home/laravel-shop-innobackupex/ 远程传递给从库对应服务器192.168.31.207，以root方式登录传递；传递到的目标目录为/home/laravel-shop-innobackupex

从库中准备恢复工作
- 停止从库服务systemctl stop mysql
- 清空data或合并。这里选择：mv /www/server/data/ /www/server/data_bak；将原来的mysql的data进行迁移备份。
配置从库的my.cnf - 与主库配置尽量一致

恢复

[root@MiWiFi-R3-srv laravel-shop-innobackupex]# innobackupex --defaults-file=/etc/my.cnf --copy-back /home/laravel-shop-innobackupex/2020-10-16_07-41-44
# 从库执行。执行恢复。设置mysql对应的配置文件，设置需要恢复的源文件文件夹(注意：主库物理备份的时候，备份时会产生带时间的子文件夹，这里要指定到对应的带日期的子文件夹中。)

启动数据库

chown -R mysql:mysql /www/server/data  # 从库中执行。将原来备份回来的文件重新分配权限。如果不分配执行的时候会报类似pid的错误
/etc/init.d/mysqld start  # 从库中执行。启动mysql这里可能会报错误。请使用lsof -i:3306找找看进程，有的话杀死就好了。

一般启动报错

验证
自己查看吧！！！！！睡觉了！

logs-slave-updates的参数说明
logs-slave-updates 参数主要在多主多从的集群架构中开启，否则会导致各从实例无法完整同步集群的全量数据的问题。
多主多从集群架构：
masterA → slaveA
↑ ↓
masterB → slaveB
logs-slave-updates：Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log.
即，正常情况下，一个slave节点是不会将其从master节点同步的数据更新操作记录至自己的二进制日志bin-log中的。
在多主的场景下，各master节点其实又相互作为另一方的slave节点进行着数据的一致性同步操作。例如 masterA 会以slave的角色同步 masterB 上的数据，masterB 也会以slave的角色同步 masterA 上的数据，如果没有开启 logs-slave-updates参数配置，则masterA masterB 虽然也能保证数据的一致性和完整性，但二者的 bin-log 中都只记录了作用在自身实例上的数据更新操作。

mysql主从理解配置和主从备份与恢复实操

二进制日志(bin-log)的说明——主从的前提

bin-log的相关字段和文件说明

如何查看文件内容

方法1：mysqlbinlog命令查看

方法2：show binlog events命令查看

主从配置

master(1)-slave(1)实现过程

1.开启主库的binlog

2.主库中配置复制账号和账号的权限

4.配置从库配置文件

5.启动复制

6.检查结果

MySQL主要复制启动配置

主从数据库备份和恢复(高级篇）

mysqldump数据备份与恢复(逻辑备份)

主从备份与恢复步骤

mydumper数据备份与恢复(逻辑备份)(第三方)

步骤

xtralBackup(热备份)

对于热备份实现解释

步骤

haokeed

引用和评论

Nginx实用操作

Mysql：关于查询的那些事儿

Mybatis源码-缓存机制

Mybatis-基础使用

Mysql梳理1

MySQL-事务隔离级别与MVCC

mysql梳理2