[mysql新增空格字段]MySQL数据库维护管理与备份实战

HWS326

作者

MySQL是最盛行的统计资料库，主要就特征：

单纯：MySQL采用很单纯，任何人稍稍有IT大背景的技工都能初学地参考文件格式加装运转和采用MySQL,这基本上没甚么准入门槛。

开放源码：开放源码意味著盛行和完全免费。

全力支持多种不同储存发动机：MySQL有多种不同储存发动机，五种储存发动机有各别的优劣，能免试优先选择采用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

全力支持高需用构架：MySQL另一方面提供更多的replication（主从复制）机能能同时实现MySQL统计数据的动态存储。

Mysql最常见的有三个储存发动机：MyISAM和 InnoDB

MySQL4和5采用预设的MyISAM储存发动机。从MYSQL5.5已经开始，MySQL已将预设储存发动机从MyISAM更改成InnoDB。

三种储存发动机的大体差别整体表现在：

1）InnoDB全力支持外交事务，MyISAM不全力支持，这一点儿是十分之关键。外交事务是一类高阶的作法，如在许多列增删去中手忙脚乱还能格式化还原成，而MyISAM就不能了。

2）MyISAM查阅统计数据相较较快，适宜大批的select ，能全文检索，InnoDB适宜频密修正和牵涉到可靠性较低的应用领域。

3）InnoDB全力支持定义方法，全力支持行级锁，MyISAM不全力支持。

4） MyISAM检索和统计数据是合二为一的，所以其检索是填充的，内存在内存的是检索，并非统计数据。而InnoDB内存在内存的是统计数据，相较而言，伺服器内存越大，InnoDB充分发挥的竞争优势越大。?

5） InnoDB可全力支持大并发请求，适宜大批insert、update操作。

关于MyISAM与InnoDB优先选择采用：

1）如果应用领域程序一定要采用外交事务，毫无疑问要优先选择INNODB发动机。

2）如果应用领域程序对查阅性能要求较低，就要采用MYISAM了。MYISAM拥有全文检索的机能，这能极大地优化查阅的效率。

Percona Server由领先的MySQL咨询公司Percona发布。

Percona Server是一款独立的统计资料库产品，其能完全与MySQL兼容，能在不更改代码的情况了下将储存发动机更换成XtraDB。是最接近官方MySQL Enterprise发行版的版本。

Percona提供更多了高性能XtraDB发动机，还提供更多PXC高需用解决方案，并且附带了percona-toolkit等DBA管理工作工具箱。

MariaDB由MySQL的创始人开发，MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

MariaDB提供更多了MySQL提供更多的标准储存发动机，即MyISAM和InnoDB，10.0.9版起采用XtraDB（名称代号为Aria）来代替MySQL的InnoDB。

综合多年采用经验和性能对比，线上业务系统首选Percona分支，其次是MYSQL版本，最后是MariaDB。

1、连接Mysql ?（客户端工具NaviCat、phpMyAdmin、MySQL-Front）

格式：

1）连接到本机上的MYSQL。

首先进入Mysql加装程序的bin目录下，再键入命令：

回车后提示你输密码.注意用户名前能有字符也能没字符，但是密码前必须没字符，否则让你重新输入密码。

2）连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123，则键入以下命令：

3）退出MYSQL命令：

格式：

1）首先在Mysql加装目录下面的bin目录，然后键入以下命令：

注：因为已经开始时root没密码，所以-p旧密码一项就能省略了。

2）再将root的密码改成abc345：

注意：和上面不同，下面的因为是MYSQL命令行中的命令，所以后面都带一个分号作为命令结束符。

格式：

1）增加一个用户test1密码为abc，让他能在任何人主机上登录，并对所有统计资料库有查阅、插入、修正、删除的权限。首先用root用户连入MYSQL，然后键入以下命令：

但这种权限增加的用户是十分危险的，如某个人知道test1的密码，那么他就能在internet上的任何人一台电脑上登录这台mysql统计资料库，并可对统计数据进行任意操作，解决办法是设置登录权限。

2）增加一个用户test2密码为abc,让它只能在localhost上登录，并能对统计资料库mydb进行查阅、插入、修正、删除的操作（localhost指本地主机，即MYSQL统计资料库所在的那台主机），这样用户即采用知道test2的密码，他也无法从internet上直接访问统计资料库。

如果你不想test2有密码，能再执行下面这个命令将密码取消掉。

如果想给一个用户test2授予访问mydb统计资料库的所有权限，并且仅允许test2在192.168.11.121这个客户端ip登录访问，可执行如下命令：

1）创建统计资料库

注意：创建统计资料库之前要先连接Mysql伺服器。

命令：

创建统计资料库，并分配用户方法：

例1：建立一个名为abc的统计资料库

2）显示统计资料库

命令：

3）删除统计资料库

命令：

例如：删除名为 iivey的统计资料库

4）连接统计资料库

命令：

例如：如果iivey统计资料库存在，尝试存取它：

use语句能通告MySQL把iivey统计资料库作为预设（当前）统计资料库采用，用于后续语句。该统计资料库保持为预设统计资料库，直到语段的结尾，或者直到发布一个不同的USE语句。

1）创建统计数据表

命令：

2）删除统计数据表

命令：

例如：删除表名为 MyClass 的表

3）表插入统计数据

命令：

例如：在表MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为90.45, 编号为2 的名为Joan 的成绩为88.99，编号为3的名为Wang的成绩为99.5。

4）查阅表中的统计数据

1. 查阅所有行

命令：

例如：查看表 MyClass 中所有统计数据

2. 查阅前几行统计数据

例如：查看表 MyClass 中前2行统计数据

5）删除表中统计数据

命令：

例如：删除表 MyClass中编号为1的记录

6）修正表中统计数据

语法：

7）增加表头

命令：

例如：在表MyClass中添加了一个表头passtest，类型为int(4)，预设值为0.

8）修正表名

命令：

例如：在表MyClass名字更改成YouClass

1）错误日志，当mysql启动有问题的时候，我们需要借助这个日志进行排查。

2）慢日志，当业务出现慢或者超时的时候，我们需要观查mysql的慢日志是否过多。

mysql的错误日志：

删除掉统计数据，无法启动mysql，观察错误日志：

观察mysql错误日志：

重启观察日志。

mysql慢日志说明：

测试统计数据的添加：

mysql慢日志说明：

1）导出整个统计资料库

导出文件预设是存在：

2）导出一个表

3）导出一个统计资料库结构

含义：-d 没统计数据 --add-drop-table 在每个create语句之前增加一个drop table。

4）带语言参数导出

案例：

1.?cmd导出sql文件

2. 导入sql文件

首先启动mysql统计资料库。

cmd 下输入：

然进入mysql的加装的bin目录下：

登录myslq。

然后输入：

后面为统计资料库的名称。

最后把要导入的sql文件根据本地导入：

3. 导入csv文件

首先要选中要导入的表。

通过Navicat客户端进入统计资料库，点击pr_pip_run表，右击然后优先选择导入向导：

优先选择.csv文件：?

优先选择要导入的文件，编码utf-8，之后点击下一步：

直接按照预设的跳过，下一步：?

时间分隔符，预设下一步：?

优先选择要导入的表pr_pip_run，点击下一步：?

优先选择栏位，手动对应的往下依次排就行了，然后预设下一步：

要把表头和表头相应的内容一一对应(参考下面的pr_pipe_run统计资料库中的内容)，只需要按照已经排好的顺序依次往下优先选择即可,这样导入的时候才知道往哪个表头导入csv哪个对应的统计数据。

下面是测试表的：

选额追加，预设下一步：?

点击已经开始：?

出现successful后关闭即可。?

之后选中表点击刷新：?

统计数据已经全部导入了。?

Xtrabackup是由percona提供更多的mysql统计资料库存储工具，Xtrabackup是一个对InnoDB做统计数据存储的工具，全力支持在线热存储，不会锁表。

Xtrabackup中主要就包含三个工具：

xtrabackup：是用于热存储innodb、?xtradb表中统计数据的工具，不能存储其他类型的表，也不能存储统计数据表结构；

innobackupex：是将xtrabackup进行封装的perl脚本，能存储和恢复MyISAM表和统计数据表结构。

Percona XtraBackup 2.4.11之后innobackupex与xtrabackup软连，故后期版本命令中移除了innbackupex。

增量存储其实只针对的是InnoDB，对于MyISAM来说，还是完整存储。

Xtrabackup与mysqldump差别：

Xtrabackup属于物理存储，mysqldump属于逻辑存储。

物理存储缺点：跨平台性差(跨文件系统可能不能恢复），存储时间长，冗余存储，浪费储存空间。

mysqldump存储缺点：效率较低，存储和还原成速度慢，锁表，且存储过程中，统计数据插入和更新操作被阻塞。

Xtrabackup占用的CPU与内存较少，消耗的IO相较较大，存储后的文件较大。

通过mysql自带的工具mysqldump进行逻辑存储和恢复，虽然能节省磁盘空间，但是速度很慢。

1. 几个percona官方yum源

Do、wnload Percona Release

Percona Repo

2. 下载yum源

Download Percona Release

3. 加装yum源

4. 测试加装库

通过yum方式加装percona-xtrabackup：

1. xtrabackup工具介绍

xtrabackup有三个主要就的工具：xtrabackup、innobackupex。

xtrabackup只能存储InnoDB和XtraDB三种统计数据表，所以只有ibd文件，frm文件它不管，恢复时就需要DBA提供更多frm ，同时，xtrabackup做存储的时候不能存储表结构、触发器等等。

innobackupex是perl脚本对xtrabackup的封装和机能扩展。 innobackupex能存储和恢复MyISAM、 InnoDB和XtraDB表和frm文件，所以我们采用innobackupex来存储MySQL统计资料库。

Xtrabackup和innobackupex是根据my.cnf配置文件来获取存储的文件，同时，需要连接到统计资料库和datadir操作权限。

需要注意的是：innobackupex在存储MyISAM表之前要对全库进行加READ LOCK，会阻塞写操作，若存储是在从库上进行的话会影响主从同步，造成延迟。对InnoDB表存储不会阻塞读写。

2. xtrabackup存储恢复过程

在InnoDB内部会保护一个redo日志文件，我们也能叫做外交事务日志文件。外交事务日志会储存每一个InnoDB表统计数据的记录修正。当InnoDB启动时，InnoDB会检查统计数据文件和外交事务日志，并执行三个步骤：它应用领域（前滚）已经提交的外交事务日志到统计数据文件，并将修正过但没提交的统计数据进行格式化操作。

xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的统计数据文件。复制过程需要许多时间，所以这期间如果统计数据文件有改动，那么将会使统计资料库处于一个不同的时间点。这时，xtrabackup会运转一个后台进程，用于监视外交事务日志，并从外交事务日志复制最新的修正。

xtrabackup必须持续的做这个操作，是因为外交事务日志是会轮转重复的写入，并且外交事务日志能被重用。所以xtrabackup自启动已经开始，就不停的将外交事务日志中每个统计数据文件的修正都记录下来。

Innobackupex会启动xtrabackup，xtrabackup准备复制统计数据文件前，首选执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来，同时把MyISAM表统计数据刷到硬盘上，然后已经开始复制MyISAM统计数据文件，文件复制完成，最后释放锁。

以上就是xtrabackup的存储过程。

接下来是准备（prepare）过程。在这个过程中，xtrabackup采用之前复制的外交事务日志，对各个统计数据文件执行灾难恢复（就像MySQL刚启动时要做的一样）。当这个过程结束后，统计资料库就能做恢复还原成了。

存储MyISAM和InnoDB表最终会处于一致，在准备（prepare）过程结束后，InnoDB表统计数据已经前滚到整个存储结束的点，而并非格式化到xtrabackup刚已经开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以MyISAM表统计数据与InnoDB表统计数据是同步的。类似Oracle的，InnoDB的prepare过程能称为recover（恢复），MyISAM的统计数据复制过程能称为restore（还原成）。

1. innobackupex常见选项

2. 通过innobackupex 进行全存储的过程

1）创建存储用户

2）进行全库存储

采用innobakupex存储时，其会调用xtrabackup存储所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、和MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会存储触发器和统计资料库配置信息相关的文件。这些文件会被保存至一个以时间戳命名的目录中。

3）一个全存储例子

?/data/backup/full/是将存储存放的目录。

innobackupex或者xtrabackup通过--user和--password连接到统计资料库服务，--defaults-file指定mysql配置文件目录。

在存储的同时，innobackupex还会在存储目录中创建xtrabackup_checkpoints文件。

内容如下： ? ? ?

1.?Innobackupex完全存储Mysql原理

1. 首先会启动一个xtrabackup_log后台检测的进程，动态检测mysql redo的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_logfile中；

2. 复制innodb的统计数据文件和系统表空间文件idbdata1到对应的以预设时间戳为存储目录的地方；

3. 复制结束后，执行flush table with read lock操作；

4. 复制.frm，MYI，MYD文件；

5. 并且在这一时刻获得binary log 的位置；

6. 将表进行解锁unlock tables；

7. 停止xtrabackup_log进程；

2. 通过Innobackupex完全恢复统计资料库原理

全库恢复过程：这一阶段会启动xtrabackup内嵌的innodb实例，将xtrabackup日志xtrabackup_Log进行回放，将提交的外交事务信息变更应用领域到innodb统计数据或表空间，同时格式化未提交的外交事务。

3. Innobackupex完全恢复统计资料库案例

1）prepare统计资料库

在存储完成后，统计数据尚且不能直接用于恢复操作，因为存储的统计数据中可能会包含尚未提交的外交事务或已经提交但尚未同步至统计数据文件中的外交事务，所以存储过程中可能还有统计数据的更改动作，此时xtrabackup_logfile就可派上用场了。

xtrabackup会解析该文件，对外交事务已经提交但统计数据还没写入的部分，进行redo重做；将已经写到统计数据文件，但未提交的外交事务通过undo进行格式化，最终使得统计数据文件处于一致性状态。

Prepare过程是通过采用innobakupex命令的 --apply-log 选项同时实现的：

成功后，这个完全存储就能被用来还原成统计资料库了。

prepare的过程，其实是读取存储文件夹中的配置文件，然后innobackupex重做已提交外交事务，格式化未提交外交事务，之后统计数据就被写到了存储的统计数据文件(innodb文件)中，并重建日志文件。

2）恢复统计资料库

采用innobackupex --copy-back来还原成存储（recovery）

innobackupex会根据my.cnf的配置，将所有存储统计数据复制到my.cnf里面指定的datadir路径下。

如果恢复成功，最后会有如下提示：

注：datadir必须是为空的，innobackupex --copy-back不会覆盖已存在的文件，还要注意，还原成时需要先关闭mysql服务，如果服务是启动的，那么就不能还原成到datadir。

3）修正权限启动统计资料库

预设情况下是通过root用户恢复的统计数据，所以mysql统计数据文件夹目录是root权限，需要修正文件的所有者和权限：

最后启动统计资料库即可。

所谓的增量，一定是在已有全量的基础之上的，不然是没任何人意义的。innobackupex增量存储过程中的"增量"处理，其实主要就是相较innodb而言，对myisam和其他储存发动机而言，它仍然是全拷贝(全存储)。

"增量"存储的过程主要就是通过拷贝innodb中有变更的"页"（这些变更的统计数据页指的是"页"的LSN大于xtrabackup_checkpoints中给定的LSN）。增量存储是基于全备的，第一次增备的统计数据必须要基于上一次的全备，之后的每次增备都是基于上一次的增备，最终达到一致性的增备。增量存储的过程如下，和全备的过程类似，差别仅在第2步。