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?
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.
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.
sudo cp -rf /mnt/ubuntu_426/var/lib/mysql/database1 /var/lib/mysql/
Step 3: Change own and change mode the folder:
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
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.
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.
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/
With MySql 5.1 (Win7). To recreate DBs (InnoDbs) I've replaced all contents of following dirs (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.
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.
In my case, simply removing the tc.log in /var/lib/mysql was enough to start mariadb/mysql again.
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
service mysql restart
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.
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.
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= in my.cnf to the available folder and restarting the MySQL server. It did the job perfectly!
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
service mysql restart
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.
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.
With MySql 5.1 (Win7). To recreate DBs (InnoDbs) I've replaced all contents of following dirs (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.
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.
sudo cp -rf /mnt/ubuntu_426/var/lib/mysql/database1 /var/lib/mysql/
Step 3: Change own and change mode the folder:
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
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.
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.
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/
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.
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= in my.cnf to the available folder and restarting the MySQL server. It did the job perfectly!
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.
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.
In my case, simply removing the tc.log in /var/lib/mysql was enough to start mariadb/mysql again.
Source: Stackoverflow.com