标签 MySQL 下的文章

MySQL导入数据报错:#2006 - MySQL server has gone away

今天导入stats统计表的时候出现

MySQL Server has gone away

原因是SQLyog备份的表数据一句INSERT INTO就包含了所有的表数据,造成数据量太大,MySQL拒绝接收。

修改MySQL的配置文件my.ini里面的[wampmysqld]字段。将默认的

max_allowed_packet = 1M

修改为

max_allowed_packet = 10M

后保存。然后重启MySQL服务器,重新导入SQL转储文件。

MySQL 5.6从*.ibd文件恢复数据

MySQL我最常用的两种数据引擎是MyISAM和InnoDB。最近在本地WAMP环境下安装了WordPress博客。某一天发现无法登录WordPress后台了,直接跳转到WordPress的安装页面。看来是PHP无法连接MySQL数据库造成的。我继续用phpMyAdmin、SQLyog等客户端工具连接数据库查询WordPress表数据都返回一句法文错误信息,翻译过来的意思是“该数据表不存在”。打开 D:\wamp\bin\mysql\mysql5.6.17\my.ini配置文件,找到lc-messages=fr_FR修改为lc-messages=en_US 保存信息变为看得懂的英文。但是奇怪的是show tables 显示出来有的表,但是运行select全部报错table doesn't exist.

又打开Windows事件查看器(eventvwr.msc),筛选事件源为MySQL,发现了大量这种的警告信息:

InnoDB: Cannot open table wordpress/wp_posts from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

经过两天两夜的大量Gooogle,发现此问题充斥着网络,貌似是一个普遍问题,而且是一个对MySQL新手来说容易犯的错误。MyISAM数据表.frm可以从一个服务器移动到另外一个服务器,而InnoDB数据引擎则不能直接移动,否则会出现tablespace不一致的问题。这种情况下日志会这种报错:

InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.

了解了这么多,中文网络里面的一般做法就是修改my.ini,加上

[mysqld]
innodb_force_recovery = 1

innodb_force_recovery的值可以是1、2、3、4、5、6,具体的含义参见:https://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html。但是我尝试了这样的方法还是找不到数据表。看来从MySQL服务器配置上恢复数据的方法走不通。我想尝试直接通过InnoDB的数据文件ibdata1、ib_logfile0、ib_logfile1,还是wordpress目录下的wp_posts.frm和wp_posts.ibd文件直接恢复。用Winhex分辨查看了以上文件,发现wp_posts.ibd里面有明文的数据内容(记得将Winhex的Charset设置为UTF-8)。然后Google到了从ibd文件恢复数据的方法。此方法略微“复杂”,以下我详细的写一下:

首选需要Linux系统,我这里安装的是最新的Debian 8.0 Jessie,直接通过apt-get加软件库来安装

加入apt签名:

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

将以下内容添加到/etc/apt/sources.list,VERSION是版本代码,比如wheezy ,jessie等。

deb http://repo.percona.com/apt VERSION main deb-src http://repo.percona.com/apt VERSION main

更新一下: $ apt-get update

安装Percona Server、Client

$ apt-get install percona-server-server-5.6 percona-server-client-5.6

注意:Percona Server不能和MySQL Server同时共存,安装Percona前请先移除MySQL Serer。

安装完成后登录MySQL

mysql -uroot -ppassword

创建一个数据库

mysql> create database tunps_com; mysql> use tunps_com;

将我丢失数据的WordPress数据库表结构SQL文件导到刚才建的tunps_com数据库wp_posts表。

这个时候数据库目录下会有一个wp_posts.ibd文件。

分离数据表的数据和结构:ALTER TABLE wp_posts DISCARD TABLESPACE;

此时我们会看到该数据表的ibd文件消失了。

把要恢复的ibd文件复制到新的数据表文件夹中,然后使用:ALTER TABLE wp_posts IMPORT TABLESPACE;来建立关系。 此时有可能会报错,报错内容为:

[SQL]ALTER TABLE wp_posts IMPORT TABLESPACE;
[Err] 1030 - Got error -1 from storage engine

此时需要查看mysql错误日志,发现:

InnoDB: how you can resolve the problem.
170728 11:10:23
InnoDB: Error: tablespace id and flags in file ‘.\tunps_com\wp_posts.ibd’ are 4 and 0, but in the InnoDB
InnoDB: data dictionary they are 3 and 0.

错误表示两个表的tablespace id不一致。旧数据表的tablespace id为4,但目前的数据表的tablespace id为3。那么接下来要做的是使tablespace id一致。

比较tablespace id,使得tablespace id一致

如果新的表的tablespace id 小于待恢复表的tablespace id,则可以通过创建表来增加tablespace id,最后在待恢复表的tablespace id处再创建一次members表,再分离表结构和表数据,把待恢复的ibd文件复制到新的表文件夹中,再把结构和数据建立关系,如果此时没报错,则关系已建立完成。
批量生成数据表可使用php脚本来完成:

<?php
    $dsn = "mysql:host=localhost;port=3306;charset=utf8;dbname=blueshop";
    $user = 'root';
    $password = 'root';
    $pdo = new PDO($dsn,$user,$password);

    $sql = "";
    for($i=1;$i<12;$i++){
        $sql .= "create table i{$i}(id int)engine innodb;";
    }
    $pdo->exec($sql);
?>

如果新的表的tablespace id 大于待恢复表的tablespace id,则有两种方案,第一,重建一个mysql服务,则tablespace id就会从1开始,则必定小于待恢复表的tablespace id。第二,找到待恢复表的tablespace id所对应的那个表,然后把这个表改成与待恢复表的结构和表名。

备份数据

关系建立完成,但还是不能查询数据,此时需要更改mysql配置文件, 添加或修改:innodb_force_recovery=5,不行则1-6都尝试一遍。待数据可查询时,立即备份即可。

参考:
https://www.percona.com/blog/2013/11/05/how-to-recover-an-orphaned-ibd-file-with-mysql-5-6/

升级WampServer中Apache、PHP、MySQL版本

初到jjsj公司工作又近2个月。了解到公司从16年4月开始做EC的B2B商城。由于公司领导不懂技术。找到外包公司做了一个Java版本的系统短期快速上线后发现问题多多也找不到售后。功能修改的需求无法快速满足。又下线后重新采用PHP+ECShop重新开发了一套。前期Java版本在阿里云ECS CentOS上跑。后来ECShop上马又转到Windows Server 2008 R2 服务器环境用的是奇葩的IIS+PHP组合。确实是够折腾的。

三个月我把服务器上面的IIS卸载掉,转到WAMP环境(采用WampServer),我用的是WampServer2.0i.exe安装包,2009年发布的版本,包含:Apache2.2.11,PHP5.3.0,MySQL5.1.36。为什么要用这么老的版本?难道不怕security bug吗。非也非也。最新的ECShop源码2.7.3最高只能支持到PHP5.3。ECShop里面又很多引用新建对象代码:&new Object,这玩意儿在新版本PHP5.5及其以上的已经版本已经deprecated。

WampServer2.0i.exe里面的二进制都是采用VC6编译。而最新的一般都是VC9。这点需要注意。

虽然说AMP套件是跨平台开源软件,但实际上Windows平台一直都是被歧视的。所以找到对应版本的Win32 VC9 编译版确实是门学问。

这里:http://mirrors.cnnic.cn/apache//httpd/binaries/win32/ 有可以下载编译好的Win32版本。

Apache 2.2.11升级到2.2.31

到apachehaus下载 httpd-2.2.31-x86-r6.zip解压到D:\tunps.com\bin\apache\Apache2.2.31,将老版本(2.2.11)手动卸载。在cmd下运行:D:\tunps.com\bin\apache\Apache2.2.11\bin\httpd.exe -k uninstall,然后安装新版本:D:\tunps.com\bin\apache\Apache2.2.31\bin\httpd.exe -k install -n apache22,将老版本的conf目录覆盖到新版本的conf目录。修改PHP LoadModule的路径和ServerRoot:

ServerRoot "D:/tunps.com/bin/apache/apache2.2.31"

LoadModule php5_module "D:/tunps.com/bin/php/php5.3.29/php5apache2_2.dll"

然后重启Apahce让配置生效:D:\tunps.com\bin\apache\Apache2.2.31\bin\httpd.exe -k restart
注意:因为2.2.31用的是VC9编译,为了让httpd跑起来,必须确保服务器环境已经安装VC++ Redist 2008 SP1。

PHP 5.3.0升级到5.3.29

PHP也是同样的道理,官方php.net不提供win32而精致版,所以在apachelounge搜索到人家的编译版:

https://phpdev2.toolsforresearch.com/php-5.3.29-nts-Win32-VC9-x86.zip 
https://phpdev2.toolsforresearch.com/php-5.3.29-Win32-VC9-x86.zip 
https://phpdev2.toolsforresearch.com/php-5.3.29-nts-Win32-VC9-x64.zip 
https://phpdev2.toolsforresearch.com/php-5.3.29-Win32-VC9-x64.zip 

nts表示非线程安全,我们不需要这个。下载好之后解压到D:\tunps.com\bin\php\php5.3.29,将老版本PHP5.3.0的php.ini覆盖到5.3.29,并且覆盖一份到apache2.2.31\bin目录下。这样不用修改任何配置,可以直接使用。但是php.ini有一个地方还是要修改:

extension_dir = "D:/tunps.com/bin/php/php5.3.29/ext/"

MySQL 5.1.36升级到最新的5.7.16

因为PHP一般都是采用tcp socket和MySQL连接,所以兼容性要求并不高,哪怕PHP的MySQL扩展(php_mysqli.dll)还是5.0.8 2010年的版本,但还是可用。

到MySQL官网下载最新的MySQL win32压缩包(mysql-5.7.16-win32.zip),现在的MySQL安装包做得很大,有400MB左右,里面不仅仅包含二进制还是各种文档、各种语言的客户端连接library还有Visual Studio扩展等等。安装程序也不是十年那样的简陋,可以安装的过程中实现各种高级配置(端口、用户名、安全性设置 etc.)。

解压MySQL压缩包到D:\tunps.com\bin\mysql\mysql-5.7.16-win32,管理员cmd下安装MySQL:

mysqld --install mysql57

初始化数据:

mysqld --initialize

或者:

mysqld --initialize-insecure

没有加insecure的自动生成一个随机的root密码, root密码在:D:\tunps.com\bin\mysql\mysql-5.7.16-win32\data\<host name>.err 日志文件里面。加了insecure的,默认root没有密码。mysql -uroot -p登录后用

set password = password('tunps.com');

设置为新密码。

然后重新导入SQL文件即可。

如果不初始化mysql直接启动mysql服务会启动不起来,事件查看器eventvwr.msc报错:

failed to set datadir to D:\tunps.com\bin\mysql\mysql-5.7.16-win32\data\

phpMyAdmin 3.2.0.1升级到4.6.5.1

更炫的ajax操作,效率大为提升,下载后解压缩到D:\tunps.com\apps\phpMyAdmin-4.6.5.1-all-languages,修改D:\tunps.com\alias\phpmyadmin.conf里面Alias路径。

注意:以上所有的软件都是x86的。

phpMyAdmin 无法加载 mysqli 扩展,请检查您的 PHP 配置

昨天打算给公司的WAMP套件更新一下版本,更新完了Apahce、PHP、MySQL版本之后,结果浏览器打开phpMyAdmin报错:

phpMyAdmin 无法加载 mysqli 扩展,请检查您的 PHP 配置

首先想到的是打开php.ini配置查看php_mysql.dllphp_mysqli.dll扩展是否已经加载起来。

打开D:\wamp\bin\apache\Apache2.2.31\bin\php.ini,发现

extension=php_mysql.dll
extension=php_mysqli.dll

前面没有; 然后随便写一个phpinfo()http://tunps.com/info.php,查看mysqlmysqli扩展也是加载起来了的。

phpinfo

在困扰了我一个小时之后,偶然将用IE浏览器可以正常打开phpMyAdmin,并且可以正常登录。然后返回到Chrome开发浏览器还是一样的红色标题报错。那么到此真相大白。是Chrome缓存的问题,清空缓存了一些正常。这里不得不吐槽一下phpMyAdmin的报错设计。又错误直接给你跳转到http://tunps.com/error.php,浏览器缓存后就一直跳转到这里页面,就算问题解决了也是这样。坑啊!!!