今天打开电脑MySQL数据库突然莫名奇妙地启动不起来了，在System Preferences中点击Start MySQL Server没有任何效果，然后在命令行登录mysql -uroot -p出现如下报错
|
1 |
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) |
在网上查找解决方案
|
1 |
sudo rm *.err && sudo rm *.pid |
仍然无法启动，按另一种方式启动
|
1 2 3 4 |
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 |
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添加
|
1 |
innodb_force_recovery = 1 |
2.启动MySQL服务
|
1 |
sudo /usr/local/mysql/support-files/mysql.server start |
3.关闭MySQL服务
|
1 |
sudo /usr/local/mysql/support-files/mysql.server stop |
4.打开my.conf删除
|
1 |
innodb_force_recovery = 1 |
5.启动MySQL服务
|
1 |
sudo /usr/local/mysql/support-files/mysql.server start |
注：
如Mac.local.err中出现如下错误
|
1 |
[ERROR] InnoDB: Unable to lock ./ibdata1, error: 35 |
执行
|
1 2 |
sudo mv ibdata1 ibdata1.bak sudo cp -a ibdata1.bak ibdata1 |
sudo killall mysqld
缺失 ibdata1文件如何恢复数据库
下载MySQL工具：https://downloads.mysql.com/archives/utilities/
|
1 |
innodb_file_per_table = 1 |
在丢失了 ibdata1时则需要通过.ibd文件恢复数据:
|
1 2 3 4 |
# 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：
|
1 2 3 |
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
