从物理文件恢复 MySQL 数据库

Restoring MySQL database from physical files

提问by Abdullah Jibaly

Is it possible to restore a MySQL database from the physical database files. I have a directory that has the following file types:

是否可以从物理数据库文件中恢复 MySQL 数据库。我有一个包含以下文件类型的目录：

client.frm
client.MYD
client.MYI

客户
.frm
客户.MYD客户.MYI

but for about 20 more tables.

但是对于大约 20 多张桌子。

I usually use mysqldump or a similar tool to get everything in 1 SQL file so what is the way to deal with these types of files?

我通常使用 mysqldump 或类似的工具来获取 1 个 SQL 文件中的所有内容，那么处理这些类型的文件的方法是什么？

采纳答案by Vincent

A MySQL MyISAM table is the combination of three files:

MySQL MyISAM 表是三个文件的组合：

• The FRM file is the table definition.
• The MYD file is where the actual data is stored.
• The MYI file is where the indexes created on the table are stored.

• FRM 文件是表定义。
• MYD 文件是存储实际数据的地方。
• MYI 文件是存储在表上创建的索引的位置。

You should be able to restore by copying them in your database folder (In linux, the default location is /var/lib/mysql/)

您应该能够通过将它们复制到您的数据库文件夹中来恢复（在 linux 中，默认位置是/var/lib/mysql/）

You should do it while the server is not running.

您应该在服务器未运行时执行此操作。

回答by biolinh

From the answer of @Vicent, I already restore MySQL database as below:

从@Vicent 的回答中，我已经恢复了 MySQL 数据库，如下所示：

Step 1. Shutdown Mysql server

步骤 1. 关闭 Mysql 服务器

Step 2. Copy database in your database folder (in linux, the default location is /var/lib/mysql). Keep same name of the database, and same name of database in mysql mode.

步骤 2. 将数据库复制到您的数据库文件夹中（在 linux 中，默认位置为 /var/lib/mysql）。保持数据库同名，mysql模式下数据库同名。

sudo cp -rf   /mnt/ubuntu_426/var/lib/mysql/database1 /var/lib/mysql/

Step 3: Change own and change mode the folder:

第 3 步：更改自己的文件夹并更改模式：

sudo chown -R mysql:mysql /var/lib/mysql/database1
sudo chmod -R 660 /var/lib/mysql/database1
sudo chown  mysql:mysql /var/lib/mysql/database1 
sudo chmod 700 /var/lib/mysql/database1

Step 4: Copy ibdata1 in your database folder

第 4 步：将 ibdata1 复制到您的数据库文件夹中

sudo cp /mnt/ubuntu_426/var/lib/mysql/ibdata1 /var/lib/mysql/

sudo chown mysql:mysql /var/lib/mysql/ibdata1

Step 5: copy ib_logfile0 and ib_logfile1 files in your database folder.

第 5 步：将 ib_logfile0 和 ib_logfile1 文件复制到您的数据库文件夹中。

sudo cp /mnt/ubuntu_426/var/lib/mysql/ib_logfile0 /var/lib/mysql/

sudo cp /mnt/ubuntu_426/var/lib/mysql/ib_logfile1 /var/lib/mysql/

Remember change own and change root of those files:

记住更改自己并更改这些文件的根目录：

sudo chown -R mysql:mysql /var/lib/mysql/ib_logfile0

sudo chown -R mysql:mysql /var/lib/mysql/ib_logfile1

or

或者

sudo chown -R mysql:mysql /var/lib/mysql

Step 6 (Optional): My site has configuration to store files in a specific location, then I copy those to corresponding location, exactly.

第 6 步（可选）：我的站点配置为将文件存储在特定位置，然后我将这些文件准确复制到相应位置。

Step 7: Start your Mysql server. Everything come back and enjoy it.

第 7 步：启动您的 Mysql 服务器。一切都回来并享受它。

That is it.

这就对了。

See more info at: https://biolinh.wordpress.com/2017/04/01/restoring-mysql-database-from-physical-files-debianubuntu/

查看更多信息：https: //biolinh.wordpress.com/2017/04/01/restoring-mysql-database-from-physical-files-debianubuntu/

回答by Joel Davey

If you are restoring the folder don't forget to chown the files to mysql:mysql

chown -R mysql:mysql /var/lib/mysql-data

otherwise you will get errors when trying to drop a database or add new column etc..

如果您正在恢复文件夹，请不要忘记将文件 chown 到 mysql:mysql

chown -R mysql:mysql /var/lib/mysql-data

否则在尝试删除数据库或添加新列等时会出现错误。

and restart MySQL

并重启 MySQL

service mysql restart

回答by Ivan Igniter

I have the same problem but was not able to successfully recover the database, based on the instructions above.

我有同样的问题，但无法根据上述说明成功恢复数据库。

I was only able to recover mysql database folders from my Ubuntu OS. My problem is how to recover my database with those unreadable mysql data folders. So I switched back to win7 OS for development environment.

我只能从我的 Ubuntu 操作系统中恢复 mysql 数据库文件夹。我的问题是如何使用那些不可读的 mysql 数据文件夹恢复我的数据库。所以我切换回win7 OS作为开发环境。

*NOTE I have an existing database server running in win7 and I only need few database files to retrieve from the recovered files. To successfully recover the database files from Ubuntu OS I need to freshly install mysql database server (same version from Ubuntu OS in my win7 OS) to recover everything in that old database server.

*注意我有一个在 win7 中运行的现有数据库服务器，我只需要从恢复的文件中检索几个数据库文件。要从 Ubuntu 操作系统成功恢复数据库文件，我需要全新安装 mysql 数据库服务器（与我的 win7 操作系统中的 Ubuntu 操作系统版本相同）以恢复旧数据库服务器中的所有内容。

1. Make another new mysql database server same version from the recovered files.

2. Stop the mysql server

3. copy the recovered folder and paste in the (C:\ProgramData\MySQL\MySQL Server 5.5\data) mysql database is stored.

4. copy the ibdata1 file located in linux mysql installed folder and paste it in (C:\ProgramData\MySQL\MySQL Server 5.5\data). Just overwrite the existing or make backup before replacing.

5. start the mysql server and check if you have successfully recovered the database files.

6. To use the recovered database in my currently used mysql server simply export the recovered database and import it my existing mysql server.

1. 从恢复的文件中制作另一个新的 mysql 数据库服务器相同的版本。

2. 停止mysql服务器

3. 复制恢复的文件夹并粘贴到(C:\ProgramData\MySQL\MySQL Server 5.5\data) mysql 数据库中存储。

4. 复制位于 linux mysql 安装文件夹中的 ibdata1 文件并将其粘贴到 (C:\ProgramData\MySQL\MySQL Server 5.5\data)。只需在替换之前覆盖现有的或进行备份。

5. 启动mysql服务器并检查是否成功恢复了数据库文件。

6. 要在我当前使用的 mysql 服务器中使用恢复的数据库，只需导出恢复的数据库并将其导入我现有的 mysql 服务器。

Hope these will help, because nothing else worked for me.

希望这些会有所帮助，因为没有其他东西对我有用。

回答by sergey.olifirenko

With MySql 5.1 (Win7). To recreate DBs (InnoDbs) I've replaced all contents of following dirs (my.ini params):

使用 MySql 5.1 (Win7)。为了重新创建数据库（InnoDbs），我替换了以下目录（my.ini params）的所有内容：

datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/"
innodb_data_home_dir="C:/MySQL Datafiles/"

After that I started MySql Service and all works fine.

之后我启动了 MySql 服务，一切正常。

回答by Filip Ekberg

Yes it is! Just add them to your database-folder ( depending on the OS ) and run a command such as "MySQL Fix Permissions". This re-stored the database. See too it that the correct permissions are set on the files aswell.

是的！只需将它们添加到您的数据库文件夹（取决于操作系统）并运行诸如“MySQL Fix Permissions”之类的命令。这重新存储了数据库。也请注意对文件设置了正确的权限。

回答by Spikolynn

I once copied these files to the database storage folder for a mysql database which was working, started the db and waited for it to "repair" the files, then extracted them with mysqldump.

我曾经将这些文件复制到正在运行的 mysql 数据库的数据库存储文件夹中，启动数据库并等待它“修复”文件，然后用 mysqldump 提取它们。

回答by SirJohnFranklin

In my case, simply removing the tc.log in /var/lib/mysql was enough to start mariadb/mysql again.

就我而言，只需删除 /var/lib/mysql 中的 tc.log 就足以再次启动 mariadb/mysql。