今天打开电脑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