MySQL备份,恢复方案,mysqlbinlog,mysqldump,主从,主主复制

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介:

DBMS数据库管理系统的三层模型:物理层,逻辑层以及视图层。

  物理层:决定数据的存储形式。

  逻辑层:是一张有一张的表,一行行的数据记录。

  视图层:让用户看起来更方便,可有可无。

  存储引擎:使逻辑层中sql语句转换成能在磁盘上存储的物理形式,连接逻辑层与物理层。

  常用MySQL存储引擎:

    MyISAM:

      最经典的MySQL存储引擎,但如果数据库一旦崩溃,再重启时需要对表进行修复,但MyISAM

    存储引擎无法保证安全修复,且其不支持事务的进行。支持表级锁。

    Innodb:

      Innodb存储引擎,支持事务,数据库的存储,是以表空间的方式存储,支持MVCC的高并发,

    支持四种隔离级别,read-uncommitted读未提交,read-committed读提交,repeatable-read幻读

    serializable串行化。支持行级锁,间隙锁。

    Aria:

      Aria存储引擎数据库一旦崩溃,能够对数据库安全修复增强版MyISAM存储引擎。

    Memory:

      内存级存储引擎,支持自适应hash索引,查询能力十分强大。

    MRG_MyISAM:

      将多张表逻辑层连接在一起,使用户就像使用一张表一样。

    PERFORMANCE_SCHEMA:

      展示数据库运行时的状态参数和统计数据。

    CSV:

      基于文件的文件存储数据存储引擎。

    ARCHIVE:

      归档存储引擎,通常用于做数据仓库。

  

  MySQL的日志:

  ①查询日志:

     general_log {ON|OFF} :查询日志是否开启关闭。

     general_log_file hostname.log :该变量的生效,需要在log_output为FILE时才能生效。

     log_output  FILE:

      FILE:表示将日志记录于文件系统中的文件;

      TABLE:表示将日志记录于MySQL中指定的表;

      NONE:表示不将日志记录输出出去;

  ②慢查询日志:

     运行时间超过某指定时长的操作。

     定义查询超时时长的变量:

       long_query_time   10.000000

     慢查询日志是否开启:

      log_slow_queries    OFF    

     

     slow_query_log_file hostname.log :该变量的生效,需要在log_output为FILE时才能生效。

     log_output  FILE:

      FILE:表示将日志记录于文件系统中的文件;

      TABLE:表示将日志记录于MySQL中指定的表;

      NONE:表示不将日志记录输出出去;

  ③错误日志

      log_error:保存了错误日志的文件路径;

      log_warnings  {ON|OFF}:是否将mysqld运行过程中产生的"Warning"类的信息一并记录到错误日志中;

  ④二进制日志

     用于记录引起数据库改变的SQL语句,可以通过备份二进制日志中的指定内容来达到数据库备份,还原的目的

   这一部分操作,在后边有示例。

     需要在/etc/my.cny配置文件中设置log_bin路径。

     mysql>show master|binary logs:显示当前数据库中所有二进制日志列表。

     mysql>show master status:显示当前数据库中正在使用的数据库列表,可以对指定日志的position,datetime进行备份还原。

     mysql>show binlog events in '日志path':显示对应二进制日志文件的内容。

  数据库备份方式:

    数据库备份作为一种重要的数据保存手段,需要根据不同环境采取不同的备份操作,最大限度保证数据的安全,毕竟数据

  才是一个企业生存的根本

  ①LVM实现MYSQL物理备份,恢复

   首先,在主机中加入一块硬盘充当MYSQL的数据存放的逻辑卷,以及备份数据的存储源

   #fdisk /dev/sdb

   因实验需要,所以只配置了一个扩展分区,及一个逻辑分区,将逻辑分区类型更改为8e即可;

   创建逻辑卷:

     #pvcreate /dev/sdb5

     #vgcreate myvg /dev/sdb5

     #lvcreate -L 5G -n mylv myvg

   对逻辑卷进行格式化:

     #mke2fs -t ext4 /dev/myvg/mylv

   创建数据库存放的路径

     #mkdir -pv  /mysql/data

   修改数据库配置文件/etc/my.cnf

    QQ截图20180104182739.jpg

    并在当前启动mysql用户家目录下创建.my.cnf文件,否则无法正常启动mysql

    QQ截图20180104183405.jpg

    

   到这里mysql服务就会在我们指定的逻辑卷中运行,lvm的备份方式主要是温备份,但

   也可以说是几乎热备份,只要对数据库加锁,解锁的过程够快,一般几秒钟即可,就

   不会引起数据的错乱;

   对所有表进行加锁操作:

   MariaDB [hellodb]> flush tables with read lock;

   

   紧接着拍摄快照,针对于该逻辑卷:

   [root@zabbix-agent4 ~]# lvcreate -L 5G -s -n snap /dev/myvg/mylv


   再对数据库中的表进行解锁操作:

   MariaDB [hellodb]> unlock tables;

   

   对二进制日志进行备份操作,备份当前运行的位置

   mysql -e "show master status" > /path

   (这样在需要还原数据时可以得知在备份那个时间段后我们执行了哪些操作,从二进制日志文件可以看出,从而进行还原)


   速度够快的话就不会产生多大的数据损失,紧接着将快照卷挂载到指定目录下,对数据进行打包压缩,卸载快照卷即可将数据库备份;

   QQ截图20180104184202.jpg

   

  ②select语句进行逻辑备份

    创建一个同classes表中数据结构一样的表;

    QQ截图20180104184627.jpg

    使用select将数据保存在文件中,并导入到test表当中对数据库进行备份

    QQ截图20180104185156.jpg

     QQ截图20180104185248.jpg


  ③使用mysqldump对数据库进行备份

      mysqldump -uroot -p password 数据库名 --lock-tables --flush-logs --master-data=2 > /path.sql

      使用上述方式对指定数据库中的所有表进行加锁,--flush-logs对二进制日志文件仅刷新一次,而不是重复刷新

      --master-data将二机制日志文件名和其所用到的时间的位置标识,追加到备份文件中,1为不注释,2为注释;

     

      保存在/path.sql路径下后,只需要进入指定备份的数据库,并使用source /path.sql即可还原备份数据;

      

      注意:上述方式需要事先创建同名数据库,然后进入该数据库执行source操作;

      可使用另一种musqldump格式,可将数据库的数据格式也备份下来,这样就无需创建数据库进行还原

      如:

         mysqldump -uroot -p password --database 数据库名 --lock-tables --flush-logs --master-data=2 > /path.sql


      对二进制日志进行备份操作,备份当前运行的位置

        mysql -e "show master status" > /path

      (这样在需要还原数据时可以得知在备份那个时间段后我们执行了哪些操作,从二进制日志文件可以看出,从而进行还原)


  ④percona xtrabackup实现数据库备份

       xtrabackup是一款由percona提供的世界上唯一一款开源实现innodb数据库热备份的工具

     其备份过程快速可靠,不会打断正在执行的事务,还原速度快,能够实现自动检验;

     MYISAM完全备份

     MYISAM因为不不支持事务,所以只能实现温备份,即在只读不可写的状况下的数据库备份,无法进行增量备份

     只能进行完全备份

     热备份:可读可写;

     温备份:只读不可写;

     冷备份:不可读不可写;

     安装xtrabackup,在阿里云中的epel源中

     #yum install -y percona xtrabackup

    

     使用innobackupex进行备份

       --user:指定备份数据库所用的用户;

       --password:备份数据库用户所用的密码;

       --host:备份数据库所在的主机;

       --socket:指定备份数据库所用的socket路径;

       如:

       战盟图片1515153854.jpg

         因为之前有更改过数据库文件的默认路径,所以需要指定socket

       最后的路径为完全备份存储的路径,innobackex命令如果不特别指定格式的话,会以日期的形式将完全备份

       存储在该目录下

        #mkdir -pv /mysql/backup2

        #chown mysql:mysql /mysql/backup2

       战盟图片1515154224.jpg

        进行还原准备操作,因为数据库在进行还原时,需要考虑在备份数据库时是否有事务在进行却尚未提交,是否有事务

      已经提交,但尚未同步到数据库当中,我们需要针对这类型的数据进行一致化操作,"准备"的主要作用正是通过回滚

      未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

      战盟图片1515155774.jpg

       备份存储好之后,将数据全部删除,验证备份数据

       使用--copy-back选项即可,不管是完全备份,还是完全备份+增量备份的还原都是该选项进行

       战盟图片1515154361.jpg

       

      注意:备份还原操作,需要在原数据库文件全部删除,mysqld进程不在运行的条件下执行

      且备份后的数据库文件权限均为root,需要更改为mysql

      战盟图片1515154530.jpg

      启动服务,数据库完好无损。


      INNODB完全备份+增量备份

      Innodb存储引擎支持事务,其可支持温备份以及热备份;这也是innobackupex的一大特点;

      增量备份是基于上一次备份后产生的数据差别进行的备份;

      首先将数据库进行几次修改,并逐一进行完全备份,增量备份操作

      首次完全备份

      战盟图片1515156178.jpg

      增量备份:

      战盟图片1515156200.jpg

      战盟图片1515156229.jpg

      准备操作:

      战盟图片1515156440.jpg

      战盟图片1515156453.jpg

      战盟图片1515156464.jpg

      --redo-only:在最后一次增量备份时不使用

      战盟图片1515156007.jpg

      准备结束后就可执行还原操作,删除原数据库内容,关闭数据库服务

      战盟图片1515156735.jpg

      战盟图片1515156782.jpg

     采用增量备份的方式,分两次添加了ClassID9,10数据

      战盟图片1515156924.jpg

     

  MySQL主从复制:

    在MySQL中,支持单项,异步复制,而主从复制,则是由至少两台MySQL服务器实现,由一台MySQL

  作为主服务器,进行写入操作,而由另一台MySQL服务器作为从服务器进行读操作,主数据库中的数据

  会自动备份到从服务器,可以进行读写分离操作,以增强数据库的读写性能。而两台服务器之间使用不同

  的硬盘,当主服务器的硬盘损坏时,从服务器的数据就保留下来,进行数据恢复。

    主从服务器搭建的原理是,从服务器开启数据库线程sql_thread,io_thread并在/etc/my.cnf中构建中继

  日志,用于存放从主服务器复制过来的数据,io_thread线程是向主服务器之间搭建数据复制的桥梁,当主

  服务器中数据库的数据改变时,会写入二进制日志当中,由io_thread读取,并复制,需要由主服务器指定

  执行复制操作的数据库用户,并授权,在从服务器配置相关master,指定主服务器IP,复制数据库用户的

  账号,密码,由哪一个二进制日志文件开始复制,从该二进制文件当中的哪个位置开始复制等。当io_thread

  取回复制的数据库内容后,就存放在从服务器的中继日志当中,由sql_thread线程将中继日志中的数据写入

  到执行存储引擎,备份成功!

    主服务器配置:IP,172.16.25.101

    配置二进制文件,设置server_id

    QQ截图20180108130514.jpg

    

    对用户进行授权,设置用户复制权限

    QQ截图20180108130717.jpg

    

    主从复制需要将主服务器中的数据库完全备份到从服务器,否则会报错,无法进行主从复制

     主服务器:

     QQ截图20180108131154.jpg

     从服务器:

     QQ截图20180108131439.jpg

     

    从服务器配置:

      配置 relay_log中继日志,server_id在/etc/my.cnf中

   QQ截图20180108131628.jpg

   配置从服务器中的master指向

   QQ截图20180108131758.jpg

  master_host:主服务器IP.

  master_user:主服务器上进行复制的数据库用户。

  master_password:主服务器上进行复制的数据库用户密码。

  master_log_file:从主服务器上的哪个二进制日志文件开始复制。这里我选定的是最后一个二进制日志。

  master_log_pos:从主服务器上的指定二进制日志文件的哪个位置开始复制。


  这个时候可以使用show slave status\G查看

  QQ截图20180108132207.jpg

  两个线程均没有开启,现在开启线程则主从复制启用

  QQ截图20180108132323.jpg

  

  在主服务器数据库中插入一条数据,在查看从哪个服务器看是否进行了复制操作

  主服务器:

  QQ截图20180108132510.jpg

  从服务器:

  查看数据是否复制:

  QQ截图20180108132644.jpg

 

  MySQL主从复制之半同步:

    半同步复制,在对主从复制的基础上进行延伸,如主从复制时,主服务器在向从服务器传输数据时,从服务器

  突然宕机,则数据的传输会有两种情况,

     1.事务还未发送到从服务器上。

     2.事务已发到从服务器上,但客户端会接受到事务传送失败的消息,重新发送事务。

    所以针对于以上情况,MySQL数据库推出了全新的半同步机制,在从服务器宕机后,会有一段延迟时间让主服

  务器去联系从服务器,若没有联系上就写入主服务器自身,待从服务器联系上了再写入从服务器,这种异步加同

  步的操作就称之为半同步。

     半同步需要对主从服务器加载特殊的插件,插件保存在/usr/lib64/mysql/plugin中

   QQ截图20180109124102.jpg

   主服务器/etc/my.cnf

   QQ截图20180109130855.jpg

   从服务器/etc/my.cnf

   QQ截图20180109130909.jpg

   半同步复制则需要semisync_master.so,semisync_slave.so两种插件,分别加载到主从服务器

   主:

   QQ截图20180109130420.jpg

   从:

   QQ截图20180109130441.jpg

   将主从服务器的同步机制开启:

     QQ截图20180109130707.jpg

     QQ截图20180109130723.jpg

     将从服务器进行授权,指定master

     QQ截图20180109131041.jpg

     在从服务器上设置只读,read_only开启,并开启另一个MySQL会话并执行

     mysql>flush tables with read lock;

     只要该会话不关闭则读锁一直存在。

     

   如何查看半同步是否成功:

   rpl_semi_sync_master_clients为1

   QQ截图20180109131958.jpg

   看nakahcehur在主服务器处进行写操作查看是否同步到从服务器上

     QQ截图20180109131315.jpg

     从服务器:

     QQ截图20180109131328.jpg

    当关闭从服务器线程时

    主服务器再次写入数据,延迟十秒

    QQ截图20180109131458.jpg

    这是因为主服务器上的同步延迟设置为十秒,当十秒内无法联系上从服务器,就写入自身,如:

    QQ截图20180109131739.jpg

    timeout设置为10000毫秒,即为10s

    

    

  MySQL主主复制:

    主主复制,至少两台的数据库服务器 ,都作为主服务器,每一台服务器既是主服务器,也是对面主服务器

  的从服务器,实现原理同上,不同的是每一台服务器都需要有中继日志和二进制日志,因为每一台服务器都

  作为主服务器以及从服务器。主主复制相较于主从复制来说,多了一个mysql入口,相当于mysql的高可用

  但却需要考虑ID增长的问题。

    主主复制相对于主从复制,需要注意的是

    1.数据不一致,比如,一台服务器在进行更新操作,将35岁包括以上人的工资上调3000元,当数据库复制

    到另一台服务器,数据进行重写,写入到这台服务器时,如果这台服务器执行了给每一个员工增加1岁时

    这样刚巧有些人从34岁到了35岁的年龄的话,该数据就会不一致。目前只能使用一些数据恢复软件来进

    行排错.

    2.主键,当两台服务器同时都插入数据时,一些自动增长的如ID的属性可能会重合,这样会导致冲突,数

    据插入删除修改也将会失败。所以需要设置auto_incremental_incremental以及auto_incremental_offset

    用于设置每次自动增长的量,以及初次增长的基数。一般设置为奇数偶数相对应,这样就不会重合。

    

  主主服务器配置:

    主服务器1:IP 172.16.25.101

    /etc/my.cnf

    QQ截图20180108143205.jpg

    设置为奇数增长,针对于那些auto_increment的字段


    授权,指定master为172.16.25.102

    QQ截图20180108130717.jpg

    QQ截图20180108143652.jpg

    

    主服务器2:IP 172.16.25.102

    /etc/my.cnf

    QQ截图20180108143758.jpg

    

    授权,指定master为172.16.25.101

    QQ截图20180108143957.jpg

    QQ截图20180108144110.jpg

   

   分别为两台服务器开启线程

   mysql>start slave;

  

   分别从两台服务器进行读写操作:

   主服务器1插入一条数据到classes中

    QQ截图20180108144445.jpg

    因为设置的是奇数增长所以由11增长到13

  

    到主服务器2进行查看,并插入一条数据:

    QQ截图20180108144731.jpg

    由图可知复制成功,再在主服务器2中插入一条数据,并在主服务器1中可见。

    主主复制成功!

   

 

  

   

   

     本文转自 wujunqi1996 51CTO博客,原文链接:http://blog.51cto.com/12480612/2058997






相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
29天前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
71 3
|
29天前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
36 3
|
2月前
|
安全 关系型数据库 MySQL
MySQL用户备份
【10月更文挑战第2天】MySQL用户备份
61 3
|
2月前
|
存储 关系型数据库 MySQL
MySQL如何备份?
【10月更文挑战第2天】MySQL如何备份?
127 3
|
3天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
47 15
|
4天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
8天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
16天前
|
关系型数据库 MySQL 数据库
GBase 数据库如何像MYSQL一样存放多行数据
GBase 数据库如何像MYSQL一样存放多行数据
|
28天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1
|
1月前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
39 4