2-18mysql数据备份-白红宇

2-18mysql数据备份

阅读量：6818 次

发布时间：2019-06-26

本文共 11628 字，大约阅读时间需要 38 分钟。

1、mysql修复工具

mysqlcheck 应用场景：数据库服务器因机房掉电，异常关机，等待服务器重新起来，启动mysql服务后，发现部分表有所损坏。报错：can't open file:

"xxxx.MYI"(errno: 145)

mysqlcheck 的使用

mysqlcheck客户端工具可检查和修复myisan表，可以优化和分析表。实际上它集成了mysql工具中check,repair,analyze,optimize的功能。

/usr/local/mysql/bin/mysqlcheck#源码编译安装位置。

rpm -qf `which mysqlcheck` 查看安装

参数选项：

-c,check 检查表

-r,repair 修复表

-a,analyze 分析表

-o,opitimize 优化表

-u 使用mysql中那个用户操作

mysqlcheck 语法

#mysqlcheck[options] db_name [tables]

# mysqlcheck[options] ---database DB1 [DB2 DB3...]

#mysqlcheck[options] --all--database

eg:

[root@xuegod72 ~]# mysqlcheck -u root -p -c book books -检查表

Enter password:
book.books OK

[root@xuegod72 ~]# mysqlcheck -u root -p -r book --修复指定的数据库

Enter password:
book.books OK
book.category OK

[root@xuegod72 ~]# mysqlcheck -u root -p -r book books ---修复表

Enter password:
book.books OK

拓展：修改文件容易丢失数据

[root@xuegod63 ~]# fsck -y -f /dev/sda1 （执行不成功）

注意： fsck 扫描文件系统时一定要在单用户模式、修复模式或把设备umount后进行

警告：如果扫描运行中的系统，会造成系统文件损坏。

# runlevel ---查看运行级别

fileserver:~# runlevel

N 2

#init 1 --单用户模式（1 S），在转换成单用户模式时可能会需要输入root密码。

fileserver:~# init 1

GTID方式会讲吗？

SQLyog很不错，老师会讲这个工具吗？

fsck from util-linux-ng 2.17.2

e2fsck 1.41.12 (17-May-2010)

/dev/sda1 is mounted.

WARNING!!! The filesystem is mounted. Ifyou continue you ***WILL***

cause ***SEVERE*** filesystem damage.

Do you really want to continue (y/n)? yes

/dev/sda1: recovering journal

Pass 1: Checking inodes, blocks, and sizes

Pass 2: Checking directory structure

Pass 3: Checking directory connectivity

Pass 4: Checking reference counts

Pass 5: Checking group summary information

/dev/sda1: 38/51200 files (0.0%non-contiguous), 34603/204800 blocks

[root@xuegod72 ~]# mysqlcheck -u root -A -r -p ---修复所有数据库

Enter password:
book.books                                         OK
book.category                                      OK
book2.books                                        OK
book2.category                                     OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
每天自动优化数据库可以

crontab -e

0 3 * * * mysqlchek -u root -p 111111 -r -o -A>/dev/null 2>&1

mysql备份与恢复：

按照备份时对数据库的影响范围,备份的方法

Hot backup(热备) Cold Backup（冷备） Warm Backup（温备）

Hot backup：指在数据库运行中直接备份，对正在运行的数据库没有任何影响。（OnlineBackup）官方手册为在线备份

Cold Backup：指在数据库停止的情况下进行备份(OfflineBackup) 官方手册称为离线备份

Warm Backup：备份同样在数据库运行时进行，但是会对当前数据库的操作有所影响，例如加一个全局读锁以保证备份数据的一致性

按照备份后文件内容：

逻辑备份-->

指备份后的文件内容是可读的，通常为文本文件，内容一般是SQL语句，或者是表内的实际数据，如mysqldump和SELECT * INTO OUTFILE的方法，一般适用于数据库的升级和迁移，恢复时间较长

裸文件备份-->

拷贝数据库的物理文件，数据库既可以处于运行状态（mysqlhotcopy、ibbackup、xtrabackup这类工具），也可以处于停止状态，恢复时间较短。

按照备份数据库的内容来分，又可以分为：

完全备份：对数据库完整的备份

增量备份：在上一次完全备份基础上，对更新的数据进行备份（xtrbackup）

日志备份：二进制日志备份，主从复制

逻辑备份工具：mysqldump

使用的时候MySQL当要导入或者导出数据量大的库的时候,用PHPMYADMIN甚至MySQL Administrator这些工具都会力不从心,这时只能使用MySQL所提供的命令行工具mysqldump进行备份恢复。数据量大的时候不推荐使用，可支持MyISAM,InnoDB

MySQL数据的导出和导入工具:mysqldump。

导出数据：

mysqldump [options] database [tables]>导出的文件名称.sql

例：1.导出整个book数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql # 注意是-p空格后是数据库名，不是密码。

[root@xuegod63 ~]#mysqldump -u root -p book > /opt/book.sql

或：

[root@xuegod63 mysql]# mysqldump -u root -p123456 book >/opt/book.sql

查看内容：

[root@xuegod63 mysql]# vim /opt/book.sql

例：2.导入数据库

法一：

[root@xuegod63 ~]# mysql -u root -p book < /opt/book.sql

Enter password: 输入密码即可。

如果导入时，没有对应的数据库，需要你手动创建一下：

mysql>create database book;

方法二：使用source 命令导入数据

如mysql -u root -p

mysql>use 数据库

然后使用source命令，后面参数为脚本文件（如这里用到的.sql）

mysql> drop table books; #删除数据，再导入数据

mysql> source /opt/book.sql ;

。。。

QueryOK, 44 rows affected (0.00 sec) #查询行，44行的影响（0秒）

Records: 44 Duplicates: 0 Warnings: 0 #记录：44份：0警告：0

。。。

Duplicates ˈdju:plɪkeɪt 重复的

mysql> create database book;

mysql -usystem-p123456 book<booktable.sql 先导入表结构

mysql -usystem-p123456 book<bookdata.sql 再导入数据

c:导入表

mysql> droptable books;

mysql>source /root/books.sql; ##导入表时，不需要重新，创建表。

mysql>select * from books;

D：导入表结构和数据

mysql> create database book;

mysql -usystem-p123456 book<booktable.sql 先导入表结构

mysql -usystem-p123456 book<bookdata.sql 再导入数据

例：导出单张表：

[root@xuegod63 mysql]# mysqldump -u root -p123456 book books> books.sql

#导入book库中的books表。

导入：

mysql> drop table books;

mysql> source /root/books.sql; ##导入表时，不需要重新，创建表。

mysql> select * from books;

例：导出所有数据库：

[root@xuegod63 ~]# mysqldump -u root-p123456 -A > all1.sql

[root@xuegod63 ~]# mysqldump -u root-p123456 --all-databases > all2.sql

或：

参数：-A, --all-databasesDump all the databases.

[root@xuegod63 /]# vim all.sql #查看sql语句，导入数据时，会自动创建对应的数据库

file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image015.jpg

导入：

[root@xuegod63 /]# mysql -u root-p123456 < all.sql

D:导出库的表结构

mysqldump-usystem -p123456 -d book>booktable.sql #只导出book库的表结构

E：只导出数据

mysqldump-usystem -p123456 -t book>bookdata.sql #只导出book库中的数据

F:导出数据库，并自动生成库的创建语句

mysqldump-uroot -p123456 -B book2 >book2.sql

mysql-uroot -p123456 < book2.sql 导入不用指定数据名

msyqlhotcopy工具----à 裸文件备份

原理：

如果备份时候不能停止mysql服务器，可以采用mysqlhotcopy工具。此工具比mysqldump命令快。

mysql工具是一个perl脚本，主要在linux下使用

mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能用于备份MyISAM存储引擎和运行在数据库目录所在的机器上.

与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.使用mysqlhotcopy命令前需要要安装相应的软件依赖包.

Mysqlhotcopy本质是使用锁表语句后再使用cp或scp拷贝数据库

[root@xuegod63 ~]# mysqladmin -u root password'123456' #创建 root密码

1. 安装mysqlhotcopy所依赖的软件包(perl-DBD)

安装方法一：

[root@tong2 ~]# yum install perl-DBD* -y

安装方法二：

[root@xuegod63 ~]# ls /mnt/Packages/*DBD*

/mnt/Packages/perl-DBD-MySQL-4.013-3.el6.x86_64.rpm

/mnt/Packages/perl-DBD-Pg-2.15.1-3.el6.x86_64.rpm

/mnt/Packages/perl-DBD-SQLite-1.27-3.el6.x86_64.rpm

2.添加关于 mysqlhotcopy相关配置

[root@tong2 ~]# vim /etc/my.cnf --在配置文件中添加如下参数

[mysqlhotcopy]

interactive-timeout

host=localhost

user=root

password=123456

port=3306

[root@tong2 ~]# /etc/init.d/mysqld restart --重启服务

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

2.查看mysqlhotcopy的帮助信息

[root@tong2 ~]#mysqlhotcopy --help

--allowold don't abort if target dir alreadyexists (rename it _old) --不覆盖以前备份的文件

--addtodest don't rename target dir if it exists,just add files to it --属于增量备份

--noindices don't includefull index files in copy --不备份索引文件

--debug enabledebug --启用调试输出

--regexp=# copy all databases with namesmatching regexp --使用正规表达式

--checkpoint=# insert checkpoint entry into specifieddb.table --插入检查点条目

--flushlog flush logs once all tables arelocked --所有表锁定后刷新日志

--resetmaster reset the binlog once all tables arelocked --一旦锁表重置binlog文件

--resetslave reset the master.info once all tablesare locked --一旦锁表重置master.info文件

3.备份一个数据库到一个目录中

[root@xuegod63 ~]# mysqlhotcopy -u root -p 123456 book /opt/ #-p和密码之前要有空格

Locked 2 tables in 0 seconds.

Flushed tables (`book`.`books`, `book`.`category`)in 0 seconds.

Copying 7 files...

Copying indices for 0 files...

Unlocked tables.

mysqlhotcopy copied 2 tables (7 files) in 0 seconds(0 seconds overall).

[root@xuegod63 ~]# ls /opt/book/

books.frm books.MYI category.MYD db.opt

books.MYD category.frm category.MYI

对比下大小

[root@xuegod63 ~]# du -h /opt/book/ /var/lib/mysql/book/

48K /opt/book/

48K /var/lib/mysql/book/

4.备份多个数据库:book和mysql库到一个目录中

[root@xuegod63 ~]# mkdir /opt/book-mysql

[root@xuegod63 ~]# mysqlhotcopy -u root -p 123456 book mysql /opt/book-mysql/

Locked 23 tables in 0 seconds. #锁住23个表

Flushed tables（刷新表）(`book`.`books`, `book`.`category`,`mysql`.`columns_priv`, `mysql`.`db`, `mysql`.`event`, `mysql`.`func`,`mysql`.`help_category`, `mysql`.`help_keyword`, `mysql`.`help_relation`,`mysql`.`help_topic`, `mysql`.`host`, `mysql`.`ndb_binlog_index`,`mysql`.`plugin`, `mysql`.`proc`, `mysql`.`procs_priv`, `mysql`.`servers`,`mysql`.`tables_priv`, `mysql`.`time_zone`, `mysql`.`time_zone_leap_second`,`mysql`.`time_zone_name`, `mysql`.`time_zone_transition`,`mysql`.`time_zone_transition_type`, `mysql`.`user`) in 0 seconds.

Copying 7 files... #复制7个文件

Copying indices for 0 files...

Copying 69 files...

Copying indices for 0 files...

Unlocked tables.

mysqlhotcopy copied 23 tables (76 files) in 1 second(1 seconds overall). #整体花费1秒钟

5.备份数据库中某一个表

语法：mysqlhotcopy -u 用户 -p 密码数据库名./要备份的表名/ 要备份的路径

[root@xuegod63 ~]# mkdir /opt/books

[root@xuegod63 ~]# mysqlhotcopy -u root -p 123456book./books/ /opt/book/

Locked 1 tables in 0 seconds.

Flushed tables (`book`.`books`) in 0 seconds.

Copying 3 files...

Copying indices for 0 files...

Unlocked tables.

mysqlhotcopy copied 1 tables (3 files) in 0 seconds(0 seconds overall).

#实际上是把对应的表文件复制到/opt目录下

[root@tong2 ~]# ll /var/lib/mysql/mysql/user.*

-rw-r--r--. 1 mysql mysql 10684 Jan 4 16:49 /var/lib/mysql/mysql/user.frm

-rw-r--r--. 1 mysql mysql 784 Jan 4 16:49 /var/lib/mysql/mysql/user.MYD

-rw-r--r--. 1 mysql mysql 2048 Jan 4 16:49 /var/lib/mysql/mysql/user.MYI

[root@tong2 ~]#

6.恢复数据

首先我们破解下数据“：

[root@xuegod63 ~]# rm -rf /var/lib/mysql/book/

[root@tong2~]# mysql -u root -p

mysql> show databases; #查看没有book库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

开始恢复：

[root@xuegod63 ~]# cp -ar /opt/book/ /var/lib/mysql/ --将备份的数据移到mysql数据根目录

注：不要加-a参数可以吗？ 不行，不加-a复制完后，文件的拥有者是root，mysql用户没有权限对此文件操作。

查看：mysqlhotcopy备份完的数据，拥有者和属于组都是mysql

[root@xuegod63 ~]# ll -d /opt/book-mysql/book/

drwxr-x--- 2 mysql mysql 4096Mar 30 17:23 /opt/book-mysql/book/

测试：

[root@tong2 ~]# mysql -u root -p

Enter password:

mysql> show tables;

mysql> use book;

mysql> select * from category;

mysql> insert into category values(11,"aaaaa"); #插入数据测试一下

mysql> select * from category;

+---------+---------------+

| bTypeId | bTypeName |

+---------+---------------+

| 1 |windows应用 |

| 2 | 网站 |

| 3 | 3D动画 |

| 4 |linux学习 |

| 5 |Delphi学习 |

| 6 | 黑客 |

| 7 | 网络技术 |

| 8 | 安全 |

| 9 | 平面 |

| 10 |AutoCAD技术 |

| 11 | aaaaa | #可以看到刚添加的记录，说明恢复成功

+---------+---------------+

11 rows in set (0.00 sec)

总结：

mysqldump和mysqlhotcopy的比较：

1、mysqldump是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，数据库大时，占用系统资源较多，支持常用的MyISAM，innodb

2、mysqlhotcopy只是简单的缓存写入和文件复制的过程，占用资源和备份速度比mysqldump快很多很多。特别适合大的数据库，但需要注意的是：mysqlhotcopy只支持MyISAM 引擎

3、mysqlhotcopy只能运行在数据库目录所在的机器上，mysqldump可以用在远程客户端。

4、相同的地方都是在线执行LOCK TABLES 以及UNLOCK TABLES
5、mysqlhotcopy恢复只需要COPY备份文件到源目录覆盖即可，mysqldump需要倒入SQL文件到原来库中。

实战：写个自动备份MySQL数据库shell脚本

[root@xuegod63 ~]# cat mysql-autoback.sh

#!/bin/bash

export LANG=en_US.UTF-8

savedir=/database_back/

cd "$savedir"

time="$(date+"%Y-%m-%d")"

mysqldump -u root -p123456 book >book-"$time".sql

然后添加一个计划任务就可以。

数据库迁移

数据库迁移是指将数据库从一个系统移动到另外一个系统上

数据库迁移场景：

1. 升级了计算机，部署开发的管理系统

2. 升级mysql数据库

3. 换用其他数据库

数据库分类：

1. 在相同版本的mysql数据库之间的迁移

2. 迁移到其他版本的mysql数据库当中

3. 迁移到其他类型的数据库中。

相同版本的mysql数据库之间的迁移

相同版本的数据库迁移的原因有很多，通常的原因是换了新的机器或者装了新的操作系统。还有一种常见的原因就是将开放的管理系统部署到工作的机器上，因为迁移后mysql数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移。但是只有数据库表都是mysqlISAM类型才能使用这种方式。

不同版本的数据库迁移通常是mysql升级的原因。例如原来的4.0版本要升级到5.0版本。这样就需要进行不同版本的数据库之间进行迁移。

高版本的mysql数据库通常都会兼容低版本，因此从低版本的mysql数据库迁移到高版本，对于mysiam类型的表可以直接复制，也可以使用mysqlhotcopy工具。但是对于innodb类型的表不可以使用这两种方法。最常用的是使用mysqldump命令来备份，然后通过mysql命令将备份文件还原到目标的mysql当中去。迁移过程中一定要小心避免数据丢失。

MySQL管理之使用XtraBackup进行热备

http://www.linuxidc.com/Linux/2014-04/99671.htm

数据库选择备份数据库的方法？

根据数据库表的存储引擎的类型不同，备份表的方法也不一样，对于myisam类型表，可以直接复制mysql数据库文件或者使用msyqlhotcopy 命令进行快速备份。复制mysql数据文件夹时将mysql服务停止。否则可能出现异常。而mysqlhotcoyp命令则不需要停止mysql服务器。mysqldump命令是最安全的备份方法，它既适合myisam 类型的表，也适合用于innodb类型的表。