MySQL服务器学习笔记！（

这是一步步从头学习MySQL的笔记历程，本人在学习之前只接触过少量SQL Server。

第一天历程：压榨自己的学习能力极限，每天学习新内容之MySQL服务器！（一）
第二天历程：压榨自己的学习能力极限，每天学习新内容之MySQL服务器！（二）
第三天历程：压榨自己的学习能力极限，每天学习新内容之MySQL服务器！（三）

接下来每天都将更新~相信你通过笔记，一定能从零学会MySQL~

废话不多说，开始今天的学习压榨~！

---

 

MySQL的备份和恢复

    我们知道如果把表数据放在Raid1上，一块硬盘坏了我们可以继续使用，那么是否就意味着我们可以不用备份了呢？当然不是，假设我们不小心执行了”drop database mydb”。是否可以备份，只要执行一个删除语句，看是否能将其还原回来，这才是备份。而Raid只是在硬件级别上作出的冗余备份。

    对于我们Mysql来说，最重要的就是数据本身，所以我们必须能确保数据本身不会出现任何问题。而当出现问题的时候，我们如何能将损失降到最低，尽可能的将数据找回来，此时，我们就需要用到备份。

    但是注意，备份永远解决不了数据的恢复，还原才能。很多人仅仅只是备份了，备份之后的数据，将备份的东西随手一扔，或者备份完成之后不检查自己的备份到底是否能够使用。所以备份解决不了任何问题，还原才能。而作为一个好的DBA，完全掌握备份和恢复，才是工作的重中之重。

 

---

数据库的日志文件：

    日志一般有两类：

    1.事物日志：实现为数据库服务器提供以下几种功能：

    →将随机IO事件转换为顺序IO（可以大大提高数据库性能），所有的写操作不是被立即放在磁盘上去的，而是先放到了在磁盘上的一段连续空间：事物空间。之后再由RDBMS控制文件内容将其同步进硬盘内。这样让硬盘在寻道的时候速度就非常快，所以理论上顺序IO比随机IO快N倍。

    →事件重放（撤销）的基础。能够保证数据库的事物在掉电之后依然完整，再下次开启之后依然能将数据同步到数据库中。

             

    2.事件日志：过去的一段时间内某个关键时刻发生了什么事情

    其实就是历史事件，只记录某点某时某刻做了什么事情。而事物日志则是记录了做这些事情怎么做的，里面操作了什么数据，操作了多少等等精确的内容。事务日志一般只记录写操作。

 

一：MySQL的日志分类：

错误日志 the error log

二进制日志 the binary log

普通的查询日志 the general guery log :通用查询，谁查询了什么的等

慢查日志 the slow query log ：执行过程超出了某些特定时间长度的日志（比如记录超过1秒查询的那次任务）

中继日志 the relay log

默认情况下，我们的MySQL没有启动任何日志。而启动MySQL的那个脚本，帮我们开启了错误日志，错误日志的文件在data下，叫做*.err

 

二：错误日志：

    不仅仅记录了错误信息，还主要记录了MySQL进程在启动和停止的时候产生的信息，如果我们使用了主从复制的功能的话，在服务器上启动复制的进程的时候，进程信息也会记录在错误日志中。

    如何去启用error log呢？

    只需要在服务器的主配置文件里，定义：

    log-error = /var/log/mysqld.err

    (如果改到其他位置，请确保Mysql用户在那个位置上有写权限)

    如果没有定义这样日志的话，启动时的错误信息，都会输出到屏幕上来。所以我们的Mysql

    错误日志的命名格式：服务器名.err

    SHOW VARIABLES LIKE ‘%log%’，可以看到关于log的信息，

log_error：这个值就是我们错误日志文件的路径，我们也可以手动设置修改它的值。

       log_output:日志输出的保存格式，默认都是FILE，保存为一个文件，其实也可以保存到表中

 

三：二进制日志：

    有点类似于事物日志，它也能提供事物重放功能，但是它不能取代事物日志，它存在的主要目的，可以提供：

    1.增量备份机制

    2.基于时间点的恢复（可以手动控制，恢复到什么位置，什么时间恢复等）

    3.复制架构的基础（只要把二进制日志复制到另一台机子上，执行二进制日志中的内容，则）

    二进制日志的开启，会给服务器增加很大的压力，但是考虑到二进制日志的重要性，所以还是要开启的。

    二进制日志的定义：

    默认在数据文件目录data下叫做：xxx-bin.XXXXX

        而xxx-bin.index则是一个索引文件，记录到底现在有多少二进制文件，以及正在写哪个。

        如果要自己定义的话，二进制文件路径：

        log-bin=/data/logs/binary/changelog

        二进制文件索引文件修改：

        log-bin-index=/data/logs/relay/binarylog.index

 

四：慢查日志/查询日志：

    实现定义那些执行时间超过了特定时长的查询事件。默认是10秒。默认是没有开启的。

       long_query_time =定义默认时间

       slow_query_log=是否开启慢查日志 ON OFF

       slow_query_log_file=定义慢查日志的路径及文件名

       general_log=是否开启查询日志ON/OFF

       general_log_file=查询日志的保存路径以及文件名

       log_output=将日志保存在哪

 

五：如何做日志滚动：

    为了让日志文件保持特定大小以免超出了我们服务器响应的能力。我们要做日志滚动

    flush logs命令：手动其滚动

    一般只对binary logs有用，它会将当前的二进制日志关闭，然后新生成一个新的二进制日志。对于其他的日志文件，我们完全可以手动去滚动，不需要这个命令。

 

六：二进制日志的删除方式：

   对于慢查日志和查询日志的文件，滚动过后不想要了可以直接删除，但是对于二进制文件来说，我们不能手动实用rm命令对其进行删除。而需要使用

       PURGE BINARY LOGS命令：删除一个二进制日志之前的内容

mysql> PURGE BINARY LOGS TO ‘mysql-bin.00007’;

包括00007之前的所有文件都自动给你清除。

mysql> PURGE BINARY LOGS BEFORE ‘2011-3-5 23:00:00’;

    删除这个时间点之前的所有二进制日志文件

       二进制日志文件默认是1G:

              max_binlog_size=可以定义默认大小。

 

 

---

日志的备份和恢复

    对于我们Mysql的属于一个在线的数据库来讲，我们在备份的时候，可能数据也在一直被写入，而如果我们强行备份的话，可能会导致备份中存入的数据的损坏。以及数据的不一致性，所以我们要采用不同的类型进行备份：

 

一：备份的类型：（从对象来分）

    逻辑备份（逻辑导出）：备份的是数据库的逻辑结构，备份的一般都是各种逻辑语句。（比如CREATE DATABESES，INSERT之类的）也叫logical export逻辑导出。逻辑备份的力度比较小，可以做到仅仅备份一个表甚至一个表中的某些行。

    物理备份（裸备份）：复制对应数据库的数据文件。对于不同的数据库引擎，物理备份的方法也是不一样的，物理备份也叫裸备份。

    对于MyISAM这样的没有表空间的引擎来说，物理备份的速度要远快于逻辑备份。

    对于InnoDB这样的引擎来说，它直接创建了固定大小的文件，然后往里填充，物理备份就没有优势了。

 

    逻辑备份的优点：

    1.在备份速度上两种备份要取决于不同的存储引擎

    2.物理备份的还原速度非常快。但是物理备份的最小力度只能做到表。

    3.逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理。

    4.逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高

    5.逻辑备份也对保持数据的安全性有保证。

 

    逻辑备份的缺点：

    1.逻辑备份要对RDBMS产生额外的压力，而裸备份无压力

    2.逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩

    3.逻辑备份可能会丢失浮点数的精度信息

 

二：备份的类型：（从数据集来分）

完全备份：备份所有数据

增量备份：只备份从上一次完全备份或增量备份以来所增加的数据（通过二进制日志来实现增量备份，依靠二进制文件）

差异备份：每次备份都备份从上一次完全备份以来所有新增的数据

       对于Mysql来讲，我们一般都是结合用，比如完全+增量1+增量2+增量3，完全+差异，不会单独使用一种备份的。

 

       DBA的小把戏：

在每一张表背后，在别人所有加入的字段后都加了一个timestamp。当有人改变数据的时候，则将timestamp修改为改变数据的时间，这样就可以在数据改变之后，备份一段时间的数据。

 

三：备份的类型：（根据服务器状态）

    冷备：干净的关闭数据库，直接备份，此时可以做各种备份。

    温备：服务不关闭，但是只允许查询操作，然后后台进行备份。

    热备：不影响读写，服务在线的备份。

 

    一般来讲非事务性的引擎都不支持热备份，比如MyISAM。但是热备通常是各个组织都非常需要的，所以非事物引擎都采用其他的概念，达到几乎是热备的概念，（比如LVM）。

    对于事务性引擎，热备就非常简单的实现。像InnoDB来说，如果想使用干净的逻辑的热备的话，只要使用官方所提供的一个工具，就能完全达到。

 

四：备份都备份什么？

    我们备份，一般备份以下几个部分：

    1.数据文件

    2.日志文件（比如事务日志，二进制日志）

    3.存储过程，存储函数，触发器（这类MySQL数据库）

    4.配置文件（十分重要，各个配置文件都要备份）

    5.用于实现数据库备份的脚本，数据库自身清理的Croutab等……

    由于备份的机制不同，所以我们建议千万不要在一个表中混合使用数据引擎。

 

 

五：常用的备份工具

    mysqldump：逻辑备份工具，可以用于所有的存储引擎，但是在任何一个存储引擎上只支持温备，如果使用INNODB的话可以支持热备，备份速度中速，还原速度非常非常慢，但是在实现还原的时候，具有很大的操作余地。具有很好的弹性。

    SELECT INTO OUTFILE：MySQL的一个命令，是个逻辑备份，备份力度非常细，可以指定某个表的某些行，只支持到温备，备份速度慢，恢复速度慢，可操作性比较灵活。

    mk-parallel-backup：一个perl脚本,是一个人开发的，各种mk脚本，它的主要作用是模拟了mysqldump的功能，能够实现在多颗CPU上实现并行备份的能力，比mysqldump要快一些，备份速度中等，恢复速度中等。

    ibbackup:对于INNODB可以实现温备，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元……-。-

    BACKUP command：这个基本上已经被废弃了。

    Filesystem(copy files)：复制命令，只能实现冷备，物理备份。使用归档工具，cp命令，对其进行备份的，备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。

    Snapshot：基于快照(LVM，ZFS)的物理备份，速度非常快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用，所以备份速度比较快，恢复速度比较快，没有什么弹性空间，而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。

    mysqlhostcopy：基本上属于冷备的范畴，而且只能用于MyISAM引擎，物理备份，速度比较快。

备份方式1：mysqldump的使用：

    逻辑备份工具：使用SELECT语句将数据都转换出来，并将其保存成ASCII文件。

    备份使用格式：mysqldump db_name tbl_name

    备份的结果要使用输出重定向的方式，保存成一个文件比如：

    mysqldump db table > /root/tb1.sql

 还原的时候使用：

    mysql < /root/tb1.sql

    mysql> SOURCE /root/tb1.sql

它的常用选项有：

    --databases db1,db2,db3：一次备份多个数据库

    --all-databases：一次备份整个数据库上的所有数据库

    --events：备份create events创建事件

    --extended-insert：备份扩展的insert语句，恢复的时候使用一个insert语句进行插入，要比单个使用Insert速度快，默认是启用的。关闭使用 --skip-extended-insert。

    --flush-logs：主要用于实现刷新日志到磁盘中去，为了保证一致性的（大多数情况下也是需要的。）

    --lock-all-tables：在备份之前对所有表进行加锁（这个是必须的！）

    --lock-tables tb1,tb2：锁定某些个表（一般很少用）

    --routines：同时备份存储过程存储函数

    --triggers：备份触发器

    --single-transaction：对于InnoDB的引擎提供一个一致性的视图，然后备份。这样的备份完全是一个热备，可以不锁表的

        --master-data=n：复制的，为了能够启用实现新的复制服务器。一般常用的是2.

        --no-data：偶尔有用，只备份数据库和表的定义

        --where “WHERE clause”只备份数据库中某些表或者某些行的数据

 

    1.备份表hellodb中的表t1

        mysqldump -uroot -p hellodb t1 > /root/t1.sql

        t1.sql中使用 “--” 或者使用 “/*”都是注释信息

    2.恢复表hellodb中的表t1

        mysql -uroot -p hellodb < /root/t1.sql

    3.份份数据库hellodb

        mysqldump --databases hellpdb > /root/hellodb.sql

    4.恢复数据库hellodb

    mysql -uroot -p < /root/hellodb.sql

    真正备份的时候，要先锁定

    mysql> FLUSH TABLES;

    mysql> FLUSH TABLES WITH READ LOCK                 ##给加锁

    mysqldump --databases hellpdb > /root/hellodb.sql  ##快速备份

    mysql> UNLOCK TABLES;                              ##解锁

5.也可以自动让其加锁，并自动备份触发器：

    mysqldump --lock-all-tables --triggers --databases hellodb > /root/hellodb.sql

    备份出来之后完全可以将其压缩一下，比如用Gzip压缩

 

备份方式2：SELECT INTO OUTFILE:命令

用于备份某些表中的某些数据,这里备份已经要保存在/tmp下，它没有关于表格式的定义，如果用它来恢复的话，必须要确定表和表格式都已经存在.

    备份格式：SELECT * INTO OUTFILE ‘/tmp/t1.txt’FROM t1;

   还原格式：LOAD DATA INFILE ‘/tmp/t1.txt’INTO TABLE t1;     

1.从t2表中备份所有h开头的行

       mysql> SELECT * INTO OUTFILE ‘/tmp/t2.sql’FROM t2 WHERE CNAME LIKE ‘h%’

   2.还原：将表中的数据还原回来

mysql> LOAD DATA INFILE ‘/tmp/t2.sql’ INTO TABLE t3;

 

备份方式3：基于LVM来进行物理备份

    备份这类的前提:

    1.数据文件必须在独立的LV上。

    2.对数据库中的表施加读锁。

    3.立即对数据在的LV做快照；

    4.释放数据库的读锁；

    5.挂载快照卷，备份数据文件（如果是Innodb的引擎，一定要备份事物日志）

    6.删除快照卷

 

       对于Innodb数据引擎的备份还需要确保：

              Innodb的数据和事务日志在同一个LVM上

sync_binlog=1

log_bin=ON

innodb_support_xa=ON

 

使用物理备份做一次全库备份：

    mysql> FLUSH TABLES WITH READ LOCK;               ##给全表加读锁

    mysql> \q                                         ##快速退出

    lvcreate -L 50M -n mysnap -s /dev/myvg/mydata     ##快速创建快照

    mysql                                             ##快速进入Mysql

    mysql> UNLOCK TABLES;                             ##快速解锁

    mysql> \q                                         ##退出

    mount /dev/myvg/mysnap /mnt                       ##挂载快照卷

    cd /mnt  

    tar -jcf /root/mysql.tar.bz2 hellodb/ ibdata1 iblogfile* mysql-bin.*  ##使用tar压缩归档备份

    umount /mnt                                       ##卸载快照卷

    lvremove --force /dev/myvg/mysnap                 ##删除快照卷

 

    假设内容已经都坏了，重新安装好mysql之后，将刚才的所有内容恢复回来。

先别启动mysqld

    cd /mydata/data                            ##进到目录内

    tar xf /root/mysql.tar.bz2 -C ./           ##解压归档文件

    service mysqld start                       ## 重启Mysqld

      

对于我们快照完之后，又新增的内容的恢复，我们就需要使用二进制日志文件来恢复了

    查看二进制日志文件：mysqlbinlog

    常用的选项：

--start-position：从哪个事件位置开始

       --end-position：从哪个事件结束

       --start-datetime：指定从哪个时间点开始

            --start-datatime=”2011-09-05 11:39:40”

       --stop-datetime：从哪个时间点结束

            --start-datatime=”2011-09-05 12:00:00”

 

    二进制日志文件内容：

    at :事件开始的位置（整个mysql二进制日志中的位置）

    年月日 时分秒 服务器id 下一个事件开始的位置  线程号  语句发生时间 执行结果 执行内容 时间戳

 

    SHOW BINLOG EVENTS,查看事件点

    查看此时正在被使用的事物日志文件：以及下一次事件开始的时候起始的位置

    SHOW MASTER STATUS;

 

当我们需要单独备份一个正在使用的事件日志的时候，

SHOW MASTER STATUS;      ##查看当前事件日志

FLUSH LOGS ;             ##手动滚动日志以让其写入新的日志

此时再进行将上一个日志文件进行备份

 

 

一次完整的物理备份+基于二进制的差异备份，以及完全恢复的过程：

    mysql> FLUSH TABLES WITH READ LOCK;             ##给全表加读锁

    mysql> SHOW MASTER STATUS;                      ##记录事件日志的日志文件和时间起始位置

    mysql> \q                                       ##快速退出

    lvcreate -L 50M -n mysnap -s -p r /dev/myvg/mydata   ##快速创建快照

    mysql                                           ##快速进入Mysql

    mysql> UNLOCK TABLES;                           ##快速解锁

    mysql> \q                                       ##退出

    mount /dev/myvg/mysnap /mnt                     ##挂载快照卷

    cd /mnt  

    tar -jcf /root/mysql.tar.bz2 hellodb/ ibdata1 iblogfile*     ##使用tar压缩归档备份

    umount /mnt                                     ##卸载快照卷

    lvremove --force /dev/myvg/mysnap               ##删除快照卷

    mysqlbinlog --start-position 301 mysql -bin.000006 > /root/mysql2.incr.sql     ##备份二进制文件

 

    此时如果数据库坏了，需要全盘恢复。

    chown -R mysql .                    ##重装mysql，先修改属主

    scripts/mysql_install_db --datadir=/mydata/data/ --user=mysql  ##重装mysql，指定我们的目录

    chown -R root .                     ##重新恢复属主

    cd /mydata/data/                    ##进入工作目录

    tar xf /root/mysql2.tar.bz2 -C .    ##将备份解压至工作目录

    mysql < /root/mysql2.incr.sql   ##恢复新增的二进制日志到数据库中