• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

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

互联网 diligentman 1周前 (10-16) 13次浏览

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

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

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

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

mysql主从理解配置和主从备份与恢复实操
查看准作为主从中的主库的信息。

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

mysql主从理解配置和主从备份与恢复实操
对应的二进制日志文件和索引文件的关系。
mysql主从理解配置和主从备份与恢复实操
注意:每次mysql重启都会重新开启一个mysql-bin.*日志文件。长期不重启mysql-bin文件会过大。还是请dba来处理文件过大问题吧/(ㄒoㄒ)/~~。

如何查看文件内容

方法1:mysqlbinlog命令查看

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

为了方便,我们将mysql其他相关命令加入到系统命令行中。不需要或已经懂的直接跳过。
mysql主从理解配置和主从备份与恢复实操

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

查看日志
[root@MiWiFi-R3P-srv data]# mysqlbinlog ./mysql-bin.000006
mysql主从理解配置和主从备份与恢复实操

方法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 eventsG; 

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 8224G;

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

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

实操截图

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

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

主从配置

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

1.开启主库的binlog

在主库的mysql中:
mysql主从理解配置和主从备份与恢复实操

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

主库中创建复制账号和分配账号权限
mysql主从理解配置和主从备份与恢复实操
在从库中连接测试
mysql主从理解配置和主从备份与恢复实操

4.配置从库配置文件

在从库中配置
mysql主从理解配置和主从备份与恢复实操
在实际过程中,很可能不会进行整个mysql从节点的设置。一般情况下,会设置某个库或者某个表进行主从配置。在这个情况下,选择下面的配置参数到从库配置文件中就可以了
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;

mysql主从理解配置和主从备份与恢复实操
验证从库是否启动正常(在从库中执行):

show slave statusG

# 主要看字段信息
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错误的错误信息

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

关于slave_io_running和slave_sql_running图片解释(来自于某视频)
mysql主从理解配置和主从备份与恢复实操

相关报错:

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.检查结果

在主库和从库分别执行查看对应的库和表数据。
mysql主从理解配置和主从备份与恢复实操
mysql主从理解配置和主从备份与恢复实操
在主库中执行sql操作语句

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

在从库中进行检验

select * from user;

mysql主从理解配置和主从备份与恢复实操
检验成功!!!

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

在实际项目中,数据的主从同步配置大多情况下都是项目上线或运行一段时间后开始考虑主从同步配置(大厂可能会直接上吧ԾㅂԾ)。数据库开始可能二进制日志(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;

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

  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

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

  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 

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

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

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

  3. 导入(恢复)数据

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

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

与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操作系统进行安装
    mysql主从理解配置和主从备份与恢复实操

     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对应的配置文件,设置需要恢复的源文件文件夹(注意:主库物理备份的时候,备份时会产生带时间的子文件夹,这里要指定到对应的带日期的子文件夹中。)

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

  7. 启动数据库

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

    一般启动报错

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

  1. 验证
    自己查看吧!!!!!睡觉了!

logs-slave-updates的参数说明

logs-slave-updates 参数主要在多主多从的集群架构中开启,否则会导致各从实例无法完整同步集群的全量数据的问题。

多主多从集群架构:
masterA → slaveA
↑ ↓
masterB → slaveB

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


喜欢 (0)