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_
Please help to troubleshoot and fix this.
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_
Please help to troubleshoot and fix this.
ASKER
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.i
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_
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.
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.i
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_
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.