Mac系统MySQL数据库无法启动

Alan

8 年前

今天打开电脑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	ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

在网上查找解决方案

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

1	sudo rm .err && sudo rm .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).

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).

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

 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

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

有两个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.server start

3.关闭MySQL服务

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

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

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.server start

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

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

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

执行

sudo mv ibdata1 ibdata1.bak 
sudo cp -a ibdata1.bak ibdata1

1 2	sudo mv ibdata1 ibdata1.bak sudo cp -a ibdata1.bak ibdata1

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;

# SET FOREIGN_KEY_CHECKS = 0;

alter table xxx discard tablespace;

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

alter table xxx import tablespace;

在从 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;

CREATE TABLE `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