从物理文件恢复MySQL数据库
是否可以从物理数据库文件恢复 MySQL 数据库。 我有一个包含以下文件类型的目录:
client.frm
客户端.MYD
client.MYI
但用于大约 20 个以上的表。
我通常使用 mysqldump 或类似的工具来获取 1 个 SQL 文件中的所有内容,那么处理这些类型的文件的方法是什么?
Is it possible to restore a MySQL database from the physical database files. I have a directory that has the following file types:
client.frm
client.MYD
client.MYI
but for about 20 more tables.
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(11)
就我而言,只需删除 /var/lib/mysql 中的 tc.log 就足以再次启动 mariadb/mysql。
In my case, simply removing the tc.log in /var/lib/mysql was enough to start mariadb/mysql again.
图标保持橙色,错误日志为空,直到我意外发现我必须将
my.cnf
文件中的名称从旧目录名称wamp64
替换/更新为wamp
在新电脑上。The icon remained orange with empty error log, until I accidentally discovered I had to replace/update names in
my.cnf
file from the old directory namewamp64
towamp
in the new PC.从 Biolinh 的回答中
完成详细的过程后,我收到以下错误:
mysqlcheck: Got error: 2013: Lost connection to server during query whenexecuting 'REPAIR TABLE ... '
然后我还复制了 /var/lib/mysql /mysql 目录从备份并运行命令:
mysql_secure_installation
之后一切正常。
From the answer of Biolinh
After doing the detailed procedures, I got the following error:
mysqlcheck: Got error: 2013: Lost connection to server during query when executing 'REPAIR TABLE ... '
Then I also copied the /var/lib/mysql/mysql directory from the backup and ran the command:
mysql_secure_installation
After that all works fine.
MySQL MyISAM 表是三个文件的组合:
您应该能够通过将它们复制到数据库文件夹中来恢复(在 Linux 中,默认位置是
/var/lib/mysql/
)。您应该在服务器未运行时执行此操作。
A MySQL MyISAM table is the combination of three files:
You should be able to restore by copying them in your database folder (In linux, the default location is
/var/lib/mysql/
)You should do it while the server is not running.
从@Vicent的回答中,我已经恢复MySQL数据库如下:
步骤1.关闭Mysql服务器
步骤2.将数据库复制到数据库文件夹中(在Linux中,默认位置是/var/lib/mysql)。 保持数据库同名,mysql模式下数据库同名。
步骤 3:更改文件夹所属并更改模式:
步骤 4:将 ibdata1 复制到数据库文件夹中
步骤 5:将 ib_logfile0 和 ib_logfile1 文件复制到数据库文件夹中。
请记住更改自己的文件并更改这些文件的根目录:
或
步骤 6(可选):我的站点已配置将文件存储在特定位置,然后我将这些文件准确地复制到相应的位置。
第7步:启动Mysql服务器。 一切都回来并享受它。
这就对了。
查看更多信息: https:// /biolinh.wordpress.com/2017/04/01/restoring-mysql-database-from-physical-files-debianubuntu/
From the answer of @Vicent, I already restore MySQL database as below:
Step 1. Shutdown Mysql server
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.
Step 3: Change own and change mode the folder:
Step 4: Copy ibdata1 in your database folder
Step 5: copy ib_logfile0 and ib_logfile1 files in your database folder.
Remember change own and change root of those files:
or
Step 6 (Optional): My site has configuration to store files in a specific location, then I copy those to corresponding location, exactly.
Step 7: Start your Mysql server. Everything come back and enjoy it.
That is it.
See more info at: https://biolinh.wordpress.com/2017/04/01/restoring-mysql-database-from-physical-files-debianubuntu/
我有同样的问题,但根据上面的说明无法成功恢复数据库。
我只能从 Ubuntu 操作系统恢复 mysql 数据库文件夹。 我的问题是如何使用那些不可读的 mysql 数据文件夹恢复我的数据库。 于是我又切换回win7操作系统作为开发环境。
*笔记
我有一个在 win7 中运行的现有数据库服务器,我只需要几个数据库文件即可从恢复的文件中检索。 为了成功地从 Ubuntu 操作系统恢复数据库文件,我需要重新安装 mysql 数据库服务器(与我的 win7 操作系统中的 Ubuntu 操作系统版本相同)以恢复旧数据库服务器中的所有内容。
制作另一个新的 mysql 数据库服务器相同版本
恢复文件。
停止 mysql 服务器
复制恢复的文件夹并粘贴到
(C:\ProgramData\MySQL\MySQL Server 5.5\data) mysql 数据库是
已存储。
复制位于linux mysql安装文件夹中的ibdata1文件并
将其粘贴到(C:\ProgramData\MySQL\MySQL Server 5.5\data)。 只需覆盖现有的或在替换之前进行备份。
启动mysql服务器并检查是否恢复成功
数据库文件。
在我当前使用的 mysql 服务器中使用恢复的数据库
只需导出恢复的数据库并将其导入我现有的 mysql
服务器。
希望这些会有所帮助,因为没有其他对我有用。
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.
*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.
Make another new mysql database server same version from the
recovered files.
Stop the mysql server
copy the recovered folder and paste in the
(C:\ProgramData\MySQL\MySQL Server 5.5\data) mysql database is
stored.
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.
start the mysql server and check if you have successfully recovered
the database files.
To use the recovered database in my currently used mysql server
simply export the recovered database and import it my existing mysql
server.
Hope these will help, because nothing else worked for me.
如果要恢复文件夹,请不要忘记将文件 chown 到 mysql:mysql
chown -R mysql:mysql /var/lib/mysql-data
否则在尝试删除数据库或添加新列等
并重新启动 MySQL时会出现错误
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..
and restart MySQL
使用MySql 5.1(Win7)。 为了重新创建数据库(InnoDbs),我替换了以下目录(my.ini 参数)的所有内容:
之后我启动了 MySql 服务,一切正常。
With MySql 5.1 (Win7). To recreate DBs (InnoDbs) I've replaced all contents of following dirs (my.ini params):
After that I started MySql Service and all works fine.
我遇到了这个问题,试图从幸运的是没有删除的 Docker 卷中恢复意外删除的 Docker 容器(oraclelinux 的 MySQL),该卷在物理文件中包含数据库数据。
因此,我想做的就是将物理文件中的数据转换为
.sql
可导入文件,以使用数据库和数据重新创建容器。我尝试了 biolin 的解决方案,但遇到了一些
[InnoDB] Multiple files found for the same tablespace ID
错误,重启后。 我意识到对某些文件夹/文件进行开放性损伤手术非常棘手。对我有用的解决方案是暂时将
my.cnf
中的datadir=
更改为可用文件夹并重新启动 MySQL 服务器。 它完美地完成了任务!I ran into this trying to revive an accidentally deleted Docker Container (oraclelinux's MySQL) from a luckily-not-removed docker volume that had the DB data in physical files.
So, all I wanted to do was to turn the data from physical files into a
.sql
importable file to recreate the container with the DB and the data.I tried biolin's solution, but ran into some
[InnoDB] Multiple files found for the same tablespace ID
errors, after restart. I realized that doing open hurt surgery on certain folders/files there is quite trickey.The solution that worked for me was temporarily changing the
datadir=
inmy.cnf
to the available folder and restarting the MySQL server. It did the job perfectly!是的! 只需将它们添加到您的数据库文件夹(取决于操作系统)并运行诸如“MySQL Fix Permissions”之类的命令。 这重新存储了数据库。 还要查看文件上是否设置了正确的权限。
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 数据库的数据库存储文件夹中,启动数据库并等待它“修复”文件,然后使用 mysqldump 提取它们。
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.