mac mysql innodb_Mac系统MySQL数据库无法启动

今天打开电脑MySQL数据库突然莫名奇妙地启动不起来了，在System Preferences中点击Start MySQL Server没有任何效果，然后在命令行登录mysql -uroot -p出现如下报错

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

1

ERROR2002(HY000):Can't connect to local MySQL server through socket '/tmp/mysql.sock'(2)

在网上查找解决方案

sudo rm *.err && sudo rm *.pid

1

sudorm *.err&&sudorm *.pid

仍然无法启动，按另一种方式启动

sudo /usr/local/mysql/support-files/mysql.server start

Starting MySQL

.Logging to '/usr/local/mysql/data/AlansMac.local.err'.

ERROR! The server quit without updating PID file (/usr/local/mysql/data/AlansMac.local.pid).

1

2

3

4

sudo/usr/local/mysql/support-files/mysql.serverstart

StartingMySQL

.Loggingto'/usr/local/mysql/data/AlansMac.local.err'.

ERROR!TheserverquitwithoutupdatingPIDfile(/usr/local/mysql/data/AlansMac.local.pid).

查看该报错文件，发现如下信息：

2017-03-20 12:56:22 2269 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace xxx/auth_group uses space ID: 403 at filepath: ./xxx/auth_group.ibd. Cannot open tablespace magento/xxx _catalog_product_index_price_bundle_opt_idx which uses space ID: 403 at filepath: ./magento/xxx_catalog_product_index_price_bundle_opt_idx.ibd

1

2017-03-2012:56:222269[ERROR]InnoDB:Attemptedtoopenapreviouslyopenedtablespace.Previoustablespacexxx/auth_groupusesspaceID:403atfilepath:./xxx/auth_group.ibd.Cannotopentablespacemagento/xxx_catalog_product_index_price_bundle_opt_idxwhichusesspaceID:403atfilepath:./magento/xxx_catalog_product_index_price_bundle_opt_idx.ibd

有两个tablespace的报错：

最终得到解决方案如下：

1.打开my.conf添加

innodb_force_recovery = 1

1

innodb_force_recovery=1

2.启动MySQL服务

sudo /usr/local/mysql/support-files/mysql.server start

1

sudo/usr/local/mysql/support-files/mysql.serverstart

3.关闭MySQL服务

sudo /usr/local/mysql/support-files/mysql.server stop

1

sudo/usr/local/mysql/support-files/mysql.serverstop

4.打开my.conf删除

innodb_force_recovery = 1

1

innodb_force_recovery=1

5.启动MySQL服务

sudo /usr/local/mysql/support-files/mysql.server start

1

sudo/usr/local/mysql/support-files/mysql.serverstart

注：

如Mac.local.err中出现如下错误

[ERROR] InnoDB: Unable to lock ./ibdata1, error: 35

1

[ERROR]InnoDB:Unabletolock./ibdata1,error:35

执行

sudo mv ibdata1 ibdata1.bak

sudo cp -a ibdata1.bak ibdata1

1

2

sudomvibdata1ibdata1.bak

sudocp-aibdata1.bakibdata1

sudo killall mysqld

缺失 ibdata1文件如何恢复数据库

下载MySQL工具：https://downloads.mysql.com/archives/utilities/

innodb_file_per_table = 1

1

innodb_file_per_table=1

在丢失了 ibdata1时则需要通过.ibd文件恢复数据:

# SET FOREIGN_KEY_CHECKS = 0;

alter table xxx discard tablespace;

# 将备份的.ibd 文件拷贝到data下对应的数据库目录内

alter table xxx import tablespace;

1

2

3

4

# SET FOREIGN_KEY_CHECKS = 0;

altertablexxxdiscardtablespace;

# 将备份的.ibd 文件拷贝到data下对应的数据库目录内

altertablexxximporttablespace;

在从 MySQL 5.6迁移到5.7中时会报Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.

因5.7做了一个变化，将默认的 ROW_TYPE指定为DYNAMIC，此时需要重新创建数据表来重新指定ROW_TYPE：

CREATE TABLE `xxx` (

...

) ... ROW_FORMAT=COMPACT;

1

2

3

CREATETABLE`xxx`(

...

)...ROW_FORMAT=COMPACT;

常见问题

1、Library not loaded: libmysqlclient.18.dylib

sudo ln -s /usr/local/mysql/lib/libmysqlclient.xx.dylib /usr/lib/libmysqlclient.18.dylib