mysql restore error

Dear experts

I  could manage to take mysql database back from linux server with the following command
mysqldump -u root -pPASSWORD workspacedata > database.sql

after the complete backup the shell prompt returned without any error/message hence backup is complete successfully without any error.
Later restore the backed up database  by executing the below command
mysql -u root -pPASSWORD  workspacedata  <  database.sql
the restore did not complete and ended abruptly, please find the below message in the shell prompt
[root@crm opt]# mysql -u root -p workspace < workspace07112015.sql
Enter password:
ERROR 1005 (HY000) at line 13623: Can't create table 'workspace.inet_prelaunch_to_launch_info' (errno: 121)

Please help to troubleshoot and fix this.
D_wathiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

William NettmannPHP Web DeveloperCommented:
Could you post the exact error message?

As a wild guess, it is possible that some tables with foreign keys are being restored before the tables referenced in the keys.

If that is the case, you could backup and restore all the tables without foreign keys first, and then do the remainder of the tables.

Alternatively, remove all the foreign keys, do a backup and restore, and ten recreate all the foreign keys.
D_wathiAuthor Commented:
Hi William Nettmann, thank you very much for the reply, i was just waiting experts to reply, thanks once again,
I understood from the engineer on what all the steps done ,please bear with me for below explanation.
let me explain what all happened so you will be able support us. approx month ago mysql start and stop scripts found to be missed like we were not able to perform service mysqld start /stop similarly /etc/init.d/mysqld start/stop, this got broken hence the only option to start and stop mysql server was by the command /usr/libexec/mysqld -u root
Instance started working but every other day this used to stop we had to kill the mysql pid and start again.
Last night took mysql our crm database backup and performed
yum reinstalled mysql-server , installed successfully but were not able to start the mysql server below were the error
MySQL Daemon failed to start.
Starting mysqld: [FAILED]
How did we bring back the mysql service running:
 found MySQL Daemon was not coming up normally due to binlog index related errors. We were able to bring MySQL Daemon up by renaming /var/lib/mysql/mysql-bin.index to mysql-bin.index.20151108 and creating an empty mysql-bin.index.

Finally we understood , the sysadmin after the database backup he went to the path /var/lib/mysql here manually deleted the directory of the database named workspace.

and now when we try to restore following is the result
ERROR 1005 (HY000) at line 13623: Can't create table 'workspace.inet_prelaunch_to_launch_info' (errno: 121)

i am not in situation to take onceagain backup as the sysadmin has deleted the /var/lib/mysql/workspace.
Please suggest the workaround to resolve this as this is a production server.
William NettmannPHP Web DeveloperCommented:
You could try this - edit the database.sql file and remove all the CONSTRAINT keywords. This will cause MySQL to automatically name all your constraints with unique names.

Ensure that all the tables are defined with ENGINE = INNODB, or remove all ENGINE keywords and ensure MySQL is configured with INNODB as the default engine. You need INNODB for referential integrity (foreign keys).

Make a copy of database.sql first!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
D_wathiAuthor Commented:
Hi William Nettmann

Thank you very much also I did read the below

Open the dump file with a text editor and put this somewhere at the top:

SET FOREIGN_KEY_CHECKS=0;

Remember to do a SET FOREIGN_KEY_CHECKS=1; after.
------------------------
I have a doubt on the above that is if we SET FOREIGN_KEY_CHECKS=0; for the backedup dump by editing and save through vim editor and then execute the restore command by mysql -u root -pPASSWORD  workspacedata  <  database.sql # this takes some time and will restore the database , after this should I have to again edit the same backedup dump file and remove the SET FOREIGN_KEY_CHECKS=0; and add the SET FOREIGN_KEY_CHECKS=1;  and again execute the mysql restore command mysql -u root -pPASSWORD  workspacedata  <  database.sql

Please suggest is this how to be done.
Dan CraciunIT ConsultantCommented:
No, you don't have to do the import twice.

In your sql file, put "SET FOREIGN_KEY_CHECKS=0;" at the beginning and "SET FOREIGN_KEY_CHECKS=1;" at the end.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.