关于mysql主从: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;

测验胜利！！！

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

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

另外一些状况就是单库数据提供了一个比拟大的零碎运行，发现数据库的瓶颈了，须要开始做读写拆散来解决了，然而有不想要进行数据库来备份，那就须要深刻思考和应用如何做主从数据备份和复原了。

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

• 备份形式划分

  • 逻辑备份：逻辑备份又称为导出，比方应用 navicate 工具的导出性能
    长处：灵活性高，版本要求不算特地高，小数据量 OK，能够拿到各种中央用
    毛病：慢，大数据量不适宜。
  • 物理备份：物理备份能够称为暴力备份，就是间接把整个 mysql 的 data 文件进行 copy 备份。
    长处：快，简略粗犷
    毛病：迁徙到新的中央，须要重新配置一些新的信息，还有版本要和新的中央保持一致

• 运行形式划分

  • 冷备份：数据库进行服务运行或者进行写操作，而后在这个根底上进行备份。
    长处：数据安全，不会呈现这边写，这边又备份，最初导致数据不统一
    毛病：须要进行服务，不适宜心愿不进行服务的我的项目
  • 热备份：在服务不停机的状况下进行在线备份。
    长处：服务照常运行，不须要进行服务器
    毛病：技术要求高，备份中须要用到 binlog

备份的抉择

• 一般来说但凡有用的数据库文件尽量不删除，都进行备份。
• 依据备份周期(依据服务器的负载、评估、上一次的备份复原的操作工夫来决定)，备份保留工夫等理论状况来指定备份打算。

接下来就次要解说几种备份与复原形式。

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

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

主从备份与复原步骤

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

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

   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@'%'; 

3. 主库进行写操作
   这里咱们采纳主库中对数据库全局加锁的形式。

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

4. 从库备份
   应用 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

5. 主库开释锁

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

6. 从库进行主从的 slave 配置。
   请看上方常识主从配置内容。
7. 从库进行与主库的 slave 的 ”change master to…” 设
   请看上方常识主从配置内容。

8. 复原数据
   在从库中执行

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

9. 开启同步

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

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

mydumper 是通过 多线程 的形式对于数据进行备份，效率会比 mysqldump 快很多(据说比 mysqldump 要快 10 倍)。这种备份形式也要在执行备份前进行启动写锁操作。

步骤

1. 下载安装
   从库中执行

   tar zxvf mydumper-0.9.1.tar.gz
   cd mydumper-0.9.1
   cmake .
   make
   make install
   # 验证装置是否胜利

2. 导出数据
   从库中执行

   [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 文件的。

3. 导入 (复原) 数据

   [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(热备份)

原理和物理备份一样，只不过主库是不须要进行写啥的。

步骤

1. 下载安装
   这里特地须要留神，xtralBackup 不同的版本对应不同的 mysql 版本, 所以要到官网查看对应的版本。
   能够查看官网，我这里用的是 mysql5.7，所以用 2.4 版本的。地址为：https://www.percona.com/doc/p…，请抉择对应的 linux 操作系统进行装置。
   

    yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm -y # 本地装置

2. 主库中执行备份操作
   针对数据库 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 看到备份过去的文件了。

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

   [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

4. 从库中筹备复原工作

   • 进行从库服务systemctl stop mysql
   • 清空 data 或合并。这里抉择：mv /www/server/data/ /www/server/data_bak；将原来的 mysql 的 data 进行迁徙备份。
5. 配置从库的 my.cnf – 与主库配置尽量统一

6. 复原

   [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 对应的配置文件，设置须要复原的源文件文件夹(留神：主库物理备份的时候，备份时会产生带工夫的子文件夹，这里要指定到对应的带日期的子文件夹中。)

7. 启动数据库

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

   个别启动报错

1. 验证
   本人查看吧！！！！！睡觉了！

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 中都只记录了作用在本身实例上的数据更新操作。