It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Installation → Server lost, can't recover
I really got in a big disaster 
((
I lost my Debian server accidentally, that was my own fault ( For about 3 days (day & night) I am working with DB raw files.
I only copied all mysql *.myd *.myi *.frm files within " /var/lib/mysql" directory. I don't taken backup is MyISAM or InnoDB.
I got tired of Googling, please help me.
below is the uploaded files in my web server (
www.eai.co.ir/Backup/fa_db.zip
I am very sorry to hear this. I use a combination of mysqlautobackup and Owncloud to backup my data.
I hope someone can help you with this.
Appreciate you kind condolence
I must solve this, don't have any excuse for my friend
The following 42 tables in your mysql data folder zip are corrupted:
audit_trail
bank_trans
budget_trans
comments
crm_categories
crm_contacts
crm_persons
cust_allocations
dashboard_reminders
dashboard_widgets
debtor_trans
debtor_trans_details
dimensions
fiscal_year
gl_trans
grn_batch
grn_items
item_tax_type_exemptions
item_tax_types
loc_stock
purch_order_details
purch_orders
recurrent_invoices
refs
sales_order_details
sales_orders
stock_master
stock_moves
supp_allocations
supp_invoice_items
supp_trans
sys_types
tax_group_items
tax_groups
tax_types
trans_tax_details
voided
wo_issue_items
wo_issues
wo_manufacture
wo_requirements
workordersDo you still want the data from the other tables recovered? The missing tables schema can be populated from your original sql but their missing data needs to be reconstructed manually.
The 2 dashboard_* tables can be deleted and reconstructed by reinstalling the dashboard theme and extension.
Practically all your InnoDB tables have been lost!
@asgare: Attached is your full sql set that you can populate into your database. All missing tables have been populated with just the schema.
Lessons learned: To recover InnoDB tables, you will need the InnoDB cache(ibdata1, ib_logfile0, ib_logfile1) by taking the zip of the entire MySQL data folder after stopping the MySQL server.
The FA 2.3 InnoDB lost tables schema is attached for utf8_unicode_ci collation.
Hi dear apmuthu
Thanks a ton
please if you don't mind answering some question
Some DB files had three items of (*.myd *.myi *.frm) but some just had (*.frm) files. Since I knew copy action was done successfully, DB files should not be corrupted. Is it possible some DB Tables became MyISAM and some of them InnoDB? if yes how could I know that?
How did you restore that? can I do via WAMP? or certainly should do it via Linux based mysql? and how should my configuration would be? by stopping MySQL engine and copy/paste solve my needs?
Please give me a comprehensive solution to bring out this DB, I lost my trust beside my friend
Thanks for the time you put for analyzing DB files.
In FA 2.3 some tables are MyISAM and others are InnoDB.
In FA 2.4 all tables are InnoDB.
Since you were using a debian server - which version od debian and which version of MySQL were you using?
I just created a FA db with utf8 and persian collation in XAMPP v1.7.3 and then turned off the MySQL server.
I then replaced all files in the MySQL data folder's FA db folder with the ones extracted from your zip file and then start the MySQL server.
You can then use phpMyAdmin to do some diagnostic checks and repair if needed and identify the corrupt tables and replace them with ones from the standard sql/en_US-new.sql file after altering the collations.
All InnoDB table data files have only the .frm extension and none other. In some instances there will be some .ibd files as well.
Here are some references:
Ref 1 : http://www.fromdual.com/recover-lost-frm-files-for-innodb-tables
Ref 2:http://dba.stackexchange.com/questions/16875/mysql-how-to-restore-table-stored-in-a-frm-and-a-ibd-file
InnoDB tables cannot be copied the same way that MyISAM tables can.
Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.
Ref 3: http://www.hexblot.com/blog/recovering-innodb-tables-ibd-and-frm-files
Ref 4: http://www.thegeekstuff.com/2014/04/recover-innodb-mysql/
Ref 5: http://egil.biz/how-to-recover-mysql-data-from-innodb/
Ref 6: http://www.bluegecko.net/mysql/recovering-a-schema-from-innodb-frm-files/
Do you still have the filesystem of the Debian server?
Attached is an InnoDB recovery manual PDFed and attached here.
I don't know how really should I appreciate your kind helps
I read all of references last night. Also I found those (ibdata1, ib_logfile0, ib_logfile1) files from lost server, attached via below link.
Download Link from my web server
According your kind recommendation & references:
1. I made a virtual machine with Debian 7 and all needed staff, such as phpmyadmin, mysql and ...
2. Create an empty DB with that name
3. Stop MySQL engine
4. Copy those mentioned files in place
5. Gave desire mysql group permission to files and DB files
6. Start MySQL
While starting mysql, it reported can't repair some DB in my made DB.
The only thing that I can't control mysql version compatibility with my lost server.
Sorry for taking your valuable time too much, can you put some effort for the attached files!
BTW, many time I tried these commands to repair tables but didn't worked!
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
mysqlcheck -u root -p --check --all-databases
mysqlcheck -u root -p --optimize --all-databases
Also your last message which attached with the title of "Recovering an InnoDB table from only an .ibd file" was more out of my knowledge :-) my brain smashed
I believe there should be a way, because I saved those needed files but I can't put together them
If ($brain_smashed)
echo "Seek professional assistance."; $brain_smashed = $apmuthu_knowledge; Thank you apmuthu :-)
I successfully recover .... yuhahahahahah
With the help of your last pdf attachment and some Googling upon full text guide, finally issue solved!
http://www.thegeekstuff.com/2014/04/recover-innodb-mysql
----------------------------------------------------------------------------------------
How to Recover InnoDB MySQL Table Data from ibdata and .frm Files
This tutorial explains how to restore MySQL tables when all or some of the tables are lost, or when MySQL fails to load table data.
One of the reason for this to happen is when the table data is corrupted.
In this particular scenario, when you connect to the MySQL database server, you cannot see one more tables, as they are missing.
Under this scenario, the MySQL log file contained the following messages:
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
The method explained below will work only for InnoDB database.
Note: Before you do anything, take a backup of all the MySQL files and database in the current condition, and keep it somewhere safe.
To restore the table data you have make sure that data directory and its contents are intact. In my case it was fine.
drwx------ 2 mysql mysql 4096 Oct 11 2012 performance_schema
drwx------ 2 mysql mysql 4096 Dec 10 2012 ndbinfo
drwx--x--x 2 mysql mysql 4096 Dec 10 2012 mysql
-rw-rw---- 1 mysql mysql 56 Dec 19 2012 auto.cnf
drwx------ 2 mysql mysql 4096 Jul 30 2013 bugs
-rw-r----- 1 mysql mysql 50331648 Mar 18 10:35 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 22 2013 ib_logfile1
-rw-r----- 1 mysql mysql 35651584 Mar 18 10:35 ibdata1
..
Ibdata1 – This file is the InnoDB system table space, which contains multiple InnoDB tables and associated indexes.
*.frm – Holds metadata information for all MySQL tables. These files are located inside the folder of the corresponding MySQL database. (for example, inside “bugs” directory)
ib_logfile* – All data changes are written into these log files. This is similar to the archive logs concepts that we find in other RDBMS databases.
Copy the Files
To restore the data from the above files, first stop the MySQL server.
# service mysqld stop
Copy the ibdata files, and the database schema folder to some other directory. We will use this to restore our Mysql database. In this case, we’ll copy it to the /tmp directory. The name of the database scheme in this example is bugs.
cp –r ibdata* ib_logfile* /tmp
cp –r schema_name/ /tmp/schema_name/
Start the MySQL server:
# service mysqld start
On a related note, for a typical MySQL database backup and restore, you should use the mysqldump command.
Restore the Data
Next, restore the table data as explained below.
In the my.cnf configuration file, set the value of the following parameter to the current size of the ib_logfile0 file. In the following example, I’ve set it to 48M, as that is the size I see for the ib_logfile0 file when I did “ls -lh ib_logfile0″
innodb_log_file_size=48M
Please note that both the ib_logfile0 and ib_logfile1 file size will be the same.
Copy the previous ibdata files to respective position, inside mysql data directory.
cp –r /tmp/ibdata* /var/lib/mysql/
Create an empty folder inside data directory with the same name as the database schema name that you are trying to restore, and copy the previous .frm files inside this folder as shown below:
cp –r /tmp/ib_logfile* /var/lib/mysql/
cp –r /tmp/schema_name/*.frm /var/lib/mysql/schema_name/
Finally, restart the MySQL server.
service mysqld restart
Now you have MySQL server running with the restored tables. Don’t forget to grant appropriate privileges for the clients to connect to the MySQL database.
FrontAccounting forum → Installation → Server lost, can't recover
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.