使用mysqldump实现mysql备份

注意:备份文件和二进制日志文件不能与mysql放在同一磁盘下

节点1

1、节点1上修改mysql配置文件,开起二进制日志保存

这里我将二进制日志放在/data/mysql/目录下,/data/是我创建的另外一个lvm磁盘,本来想直接放在/data/下,发现无法启动mysql,所以建议还是放在/data/mysql中

[root@node1 ~]# mkdir -pv /data/mysql/
[root@node1 ~]# chown mysql:mysql /data/*
[root@node1 mysql]# cd /var/lib/mysql
[root@node1 mysql]# cp -a mysql-bin.000001 mysql-bin.000002 mysql-bin.index /data/mysql/
[root@node1 ~]# vim /etc/my.cnf.d/server.cnf
  [server]
  log_bin=/data/mysql/mysql-bin
[root@node1 ~]# service mariadb restart

2、查看二进制日志的一些信息

[root@node1 ~]# mysql
  MariaDB [(none)]> show master logs;
  +------------------+-----------+
  | Log_name     | File_size |
  +------------------+-----------+
  | mysql-bin.000001 |    264 |
  | mysql-bin.000002 |    245 |
  +------------------+-----------+

3、查看表的存储引擎类型并备份

MariaDB [hellodb]> show table status\G;

如果engine是myisam则备份方案如下,需要对锁表后操作

[root@node1 ~]# mysqldump -uroot --lock-tables --master-data=2 --flush-logs --databases hellodb > /root/hellodb_myis.sql

如果engine是innodb则备份方案如下

[root@node1 ~]# mysqldump -uroot --single-transaction --master-data=2 --flush-logs --databases hellodb > /root/hellodb_inno.sql
--single-transaction:热备
--master-data=2:记录为注释的CHANGE MASTER TO语句
--flush-logs:日志滚动

批量修改表的存储引擎【将得到的结果一次执行即可修改,不建议直接在mysql中修改】

MariaDB [hellodb]> SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE table_schema='hellodb' AND ENGINE='myisam';

4、修改表内数据

MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> insert into students (Name,Age,Gender,ClassID,TeacherID) values ('caocao',99,'M',6,8);
MariaDB [hellodb]> delete from students where stuid=3;

5、复制备份文件到另一节点

[root@node1 ~]# scp hellodb_inno.sql 192.168.1.114:/root/

节点2

6、在另一个节点进行mysql恢复

修改节点2的配置文件

[root@node2 ~]# mkdir -pv /data/mysql
[root@node2 ~]# vim /etc/my.cnf
  [mysqld]
  log_bin=/data/mysql/mysql-bin
[root@node2 ~]# chown mysql:mysql /data/*
[root@node2 ~]# chown mysql:mysql /data
[root@node2 ~]# service mariadb start

还原备份文件

[root@node2 ~]# mysql < /root/hellodb_inno.sql
[root@node2 ~]# less hellodb_inno.sql
  -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245;

根据表中的显示,在备份那一刻,二进制日志mysql-bin.000002,操作到了245

7、在节点2上恢复二进制日志

在节点1上将245之后的二进制日志文件转换为sql文件

[root@node1 ~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000002 > binlog.sql

复制给节点2

[root@node1 ~]# scp binlog.sql 192.168.1.114:/root/

利用刚才生产的sql文件来恢复备份之后操作的内容

[root@node2 ~]# mysql < /root/binlog.sql

8、查看恢复情况

[root@node2 ~]# mysql
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name     | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|   1 | Shi Zhongyu  | 22 | M   |    2 |     3 |
|   2 | Shi Potian  | 22 | M   |    1 |     7 |
|   4 | Ding Dian   | 32 | M   |    4 |     4 |
|   5 | Yu Yutong   | 26 | M   |    3 |     1 |
|   6 | Shi Qing   | 46 | M   |    5 |   NULL |
|   7 | Xi Ren    | 19 | F   |    3 |   NULL |
|   8 | Lin Daiyu   | 17 | F   |    7 |   NULL |
|   9 | Ren Yingying | 20 | F   |    6 |   NULL |
|  10 | Yue Lingshan | 19 | F   |    3 |   NULL |
|  11 | Yuan Chengzhi | 23 | M   |    6 |   NULL |
|  12 | Wen Qingqing | 19 | F   |    1 |   NULL |
|  13 | Tian Boguang | 33 | M   |    2 |   NULL |
|  14 | Lu Wushuang  | 17 | F   |    3 |   NULL |
|  15 | Duan Yu    | 19 | M   |    4 |   NULL |
|  16 | Xu Zhu    | 21 | M   |    1 |   NULL |
|  17 | Lin Chong   | 25 | M   |    4 |   NULL |
|  18 | Hua Rong   | 23 | M   |    7 |   NULL |
|  19 | Xue Baochai  | 18 | F   |    6 |   NULL |
|  20 | Diao Chan   | 19 | F   |    7 |   NULL |
|  21 | Huang Yueying | 22 | F   |    6 |   NULL |
|  22 | Xiao Qiao   | 20 | F   |    1 |   NULL |
|  23 | Ma Chao    | 23 | M   |    4 |   NULL |
|  24 | Xu Xian    | 27 | M   |  NULL |   NULL |
|  25 | Sun Dasheng  | 100 | M   |  NULL |   NULL |
|  26 | caocao    | 99 | M   |    6 |     8 |
+-------+---------------+-----+--------+---------+-----------+
(0)

相关推荐

  • mysqldump备份还原和mysqldump导入导出语句大全详解

    MYSQLdump参数详解 mysqldump备份: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 a -w "sql条件" –lock-all-tables > 路径 mysqldump还原: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 < 路径 mysqldump按条件导出: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 a –where "条件语句&

  • MySQL的备份工具mysqldump的基础使用命令总结

    MySQL数据库的备份工具有很多,不过这里介绍的是一个小巧好用的mysqldump工具,位于数据库目录下bin/mysqldump.exe.这几天用phpMyAdmin备份数据库的时候出现乱码,反复在本地跟远程的Dreamhost空间上测试了数据库,但是还是导出数据库会出现乱码,应该是phpMyAdmin出现的问题,数据库本身没有问题.扯远咯,呵呵.我Google到的关于mysqldump工具的相关用法. 如果主机主机支持 Shell 的话,可以 SSH 登陆主机,执行如下的命令实现备份与恢复,

  • mysql数据库备份及恢复命令 mysqldump,source的用法

    还原一个数据库:mysql -h localhost -u root -p123456 www<c:\www.sql 备份一个数据库:mysqldump -h localhost -u root -p123456 www > d:\www2008-2-26.sql //以下是在程序中进行测试 //$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile&qu

  • 用mysqldump备份和恢复指定表的方法

    复制代码 代码如下: mysqldump -u user -p db tab1 tab2 > db.sql 恢复  复制代码 代码如下: mysql -u user -p db < db.sql 参考: 1.拷备文件          :   (保证数据库没有写操作(可以给表上锁定))直接拷贝文件不能移植到其它机器上,除非你正在拷贝的表使用MyISAM存储格式 2.mysqldump    :   mysqldump生成能够移植到其它机器的文本文件 例: 备份整个数据库     --> 

  • mysqldump数据库备份参数详解

    mysqldump备份: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 a -w "sql条件" –lock-all-tables > 路径 mysqldump还原: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 < 路径 mysqldump按条件导出: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 a –where "条件语句" –no-建表&g

  • mysqldump备份数据库时排除某些库的实例

    说明: 使用mysqldump –all-databases会导出所有库.但如果做主从,从主库dump出数据时,我们是不需要也不想要information_schema 和 mysql 库的.数据库少的情况下还可以通过/usr/local/mysql/bin/mysqldump -uroot -p --databases db1 db2 > db1db2.sql 这样再导出,但如果数据多,这样指定就很麻烦了. mysql是支持 ignore-table 的,但是没有ignore-database

  • 如何备份MySQL数据库

    在MySQL中进行数据备份的方法有两种: 1. mysqlhotcopy 这个命令会在拷贝文件之前会把表锁住,并把数据同步到数据文件中,以避免拷贝到不完整的数据文件,是最安全快捷的备份方法. 命令的使用方法是: mysqlhotcopy -u root -p<rootpass> db1 db2 - dbn <output_dir> 如果需要备份全部数据库,可以加上–regexp=".*"参数. Mysqlhotcopy命令可自动完成数据锁定工作,备份时不用关闭服

  • 使用mysqldump实现mysql备份

    注意:备份文件和二进制日志文件不能与mysql放在同一磁盘下 节点1 1.节点1上修改mysql配置文件,开起二进制日志保存 这里我将二进制日志放在/data/mysql/目录下,/data/是我创建的另外一个lvm磁盘,本来想直接放在/data/下,发现无法启动mysql,所以建议还是放在/data/mysql中 [root@node1 ~]# mkdir -pv /data/mysql/ [root@node1 ~]# chown mysql:mysql /data/* [root@node

  • mysql备份脚本 mysqldump使用方法详解

    本文实例为大家分享了mysql备份脚本,供大家参考,具体内容如下 #!/bin/bash #全备方式,一般在从机上执行,适用于小中型mysql数据库 #删除15天以前备份 #作者:fafu_li #时间:2015.08.10 source /etc/profile #加载系统环境变量 source ~/.bash_profile #加载用户环境变量 set -o nounset #引用未初始化变量时退出 #set -o errexit #执行shell命令遇到错误时退出 user="root&q

  • 小型Drupal数据库备份以及大型站点MySQL备份策略分享

    中小站点简单备份策略 基于drupal的中小行网站,我们可以使用backup_migrate模块,该模块提供了定期备份的功能,备份的时间.保留多少个备份等等设置,设置好之后,定期执行cron即可备份成功. 一般的Drupal小站,我们只需使用svn即可,在服务器端,我们把备份好的数据提交到svn,就可以达到备份的目的.由于Drupal的备份模块可以设置备份保留的文件份数,因此不会造成太多的备份文件,从而导致svn很大. 下面是一个简单的备份脚本,放置到站点根目录,然后加到crontab每天执行即

  • Windows下简单的Mysql备份BAT脚本分享

    前言 本文介绍的是一个简单的在 Windows 下备份 Mysql 的 BAT 脚本,脚本使用 mysqldump 命令来备份一个指定的 Mysql 数据库到一个文件,文件格式为 %dbname%-yyyyMMddHHmmss.sql,只保留最近60天的备份.如果想定时执行,在 Windows 中添加任务计划即可,具体的可以参考这篇文章. 示例代码如下 @echo off set hour=%time:~0,2% if "%time:~0,1%"==" " set

  • MySQL备份原理详解

    本文为大家介绍了MySQL备份原理,欢迎大家阅读. 备份是数据安全的最后一道防线,对于任何数据丢失的场景,备份虽然不一定能恢复百分之百的数据(取决于备份周期),但至少能将损失降到最低.衡量备份恢复有两个重要的指标:恢复点目标(RPO)和恢复时间目标(RTO),前者重点关注能恢复到什么程度,而后者则重点关注恢复需要多长时间.这篇文章主要讨论MySQL的备份方案,重点介绍几种备份方式的原理,包括文件系统快照(LVM),逻辑备份工具Mysqldump,Mydumper,以及物理备份工具Xtraback

  • mysql备份脚本并保留7天

    脚本需求: 每天备份mysql数据库,保留7天的脚本. 存放在/opt/dbbak目录中. 脚本名称为database_xxxx-xx-xx.sql 脚本内容: #!/bin/bash export NOW="$(date +"%Y-%m-%d")" export DATA_DIR=/opt/dbbak /usr/local/ywgh/mysql/bin/mysqldump --opt -uroot –p 'mypassword' ywghblog > $DA

  • Mysql备份多个数据库代码实例

    这篇文章主要介绍了Mysql备份多个数据库代码实例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 备份数据脚本 #!/bin/bash # date是linux的一个命令 date [参数] [+格式] time=` date +%Y_%m_%d_%H_%M_%S ` # 备份输出路径 backupdir=/home/backup/ # 备份文件路径 filedir=/home/my_app/files/ # 用sql语句取出所有以'test'

  • MySQL备份脚本的写法

    前言: 数据库备份的重要性不言而喻,特别是在生产环境,任何数据的丢失都可能产生严重的后果.所以,无论什么环境,我们都应该有相应的备份策略来定时备份数据库.在 MySQL 中,比较常用的逻辑备份工具是 mysqldump,本篇文章将介绍 MySQL 定时备份的方法. 1.制定合适的备份策略 对于不同的数据库环境,我们应该考虑不同的备份策略.制定备份策略时,应考虑以下几点因素: 物理备份还是逻辑备份.这个可以由数据库大小决定,比如说小于100G用逻辑备份,大于100G用物理备份. 备份文件保留时间.

  • MySQL备份恢复设计思路

    背景 首先交代一下背景,由于某些因素的限制,我们公司目前的备份策略采用的是隔天全备的方案,增量备份则使用的是binlog server的方式,那么如何快速恢复就成为了我们需要思考的问题 恢复需求 根据我以往的一些经验来说,通常需要从备份恢复数据的场景有如下几种: 1.被误删库了 2.被误删表了,类型为TRUNCATE或者DROP 3.被误删列了,类型为ALTER ... DROP COLUMN 4.被误删数据了,类型为DELETE或者UPDATE或者REPLACE 5.表空间损坏或出现坏块了 根

  • mysql备份策略的实现(全量备份+增量备份)

    目录 设计场景 技术点 服务器信息 准备工作 编写全量备份脚本(Mysql-FullyBak.sh) 编写增量备份脚本 设置定时任务crontab 恢复操作 最近项目需要对数据库数据进行备份,通过查阅各种资料,设计了一套数据库备份策略,通过调试运行一周后,目前已经处于平稳运行状态.现在将思路分享出来,同时感谢gredn大佬. 设计场景 1)增量备份在周一到周六凌晨3点,复制mysql-bin.00000*到指定目录: 2)全量备份则使用mysqldump将整个数据库导出,每周日凌晨3点执行,并会

随机推荐