• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mysql database restore

 
Azz Romaysa
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
executing mysqldump with Runtime.getRuntime().exec(mysqldump command) works for me from servlet, in that case i read the dump with inputstream object.

Now i want to restore teh backuped .sql file into databse: i tried with Runtime.getRuntime().exec(mysqlimport command) and Runtime.getRuntime().exec(mysql > .sqlFile) both does not work, is there a another way to restore my datbase from a .sql file?
I do that because i want to give webapplication users the ability make database backup en the ability to restore.
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13073
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
does not work

That is not a very helpful problem report. What exactly happens?

Be sure that you capture the error stream as well as the output stream from the Process you start.

Bill
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm going to move this to the JDBC forum where it is more likely to be seen by someone familiar with MySql.
 
Azz Romaysa
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
my try block looks like:
the next sql 2 commands tried:
mysql -hhost -uuse -ppassword database < sqlFlie
and
musqlimport -hhost -uuser -ppassword database sqlFile
I also find it strange because no error or exception is found or thrown. so it executes it, but nothing happens (no restore is happened)...

my sql file looks like:
-- Host: 127.0.0.1 Database: mysouq3_db
-- ------------------------------------------------------
-- Server version4.1.8-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;

--
-- Table structure for table `admin`
--

DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`username` varchar(100) NOT NULL default '',
`password` varchar(100) NOT NULL default '',
`role` varchar(100) NOT NULL default '',
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `admin`
--


/*!40000 ALTER TABLE `admin` DISABLE KEYS */;
LOCK TABLES `admin` WRITE;
INSERT INTO `admin` VALUES ('admin','admin','admin');
UNLOCK TABLES;
/*!40000 ALTER TABLE `admin` ENABLE KEYS */;


My Java code looks like:

try
{
Runtime rt = Runtime.getRuntime();

String host = "127.0.0.1";
String user = Config.DB_USERNAME;
String pw = Config.DB_PASSWORD;
String db = Config.DB_NAME;
String restoreDatabase = this.getRestoreDatabase(baseDir+fileName);

String command = "mysqlimport -h"+host+" -u"+user+" -p"+pw+" "+db+ " "+baseDir+fileName;


log.info("restore db quesy is: "+command);
rt.exec(command);

log.info("end try");
}
 
Azz Romaysa
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Javaranch specialists, i am waiting for your help?? see above....how can I restore .sql file (contains tables) to mysql database using java code into a webapplication?
 
Chetan Parekh
Ranch Hand
Posts: 3640
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to do two things
(1)Check whether the MYSQL_HOME\bin directly in the path or not
(2)Put sql file in bin directly of the application server

Just do this and let us know the result.
 
Chetan Parekh
Ranch Hand
Posts: 3640
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Azz Romaysa:
how can I restore .sql file (contains tables) to mysql database using java code into a webapplication?


You have to have file on the server, you can�t directly select sql file on client�s pc and restore it.

If you want to restore database from sql file residing on client�s pc, you need to upload it to the server first.
 
Azz Romaysa
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am now testing my webapplication locally, not on the server.
my .sql files resides into ....\resin-3.0.14\webapps\manager\DBBackups directory, I think it does not matter if you put backup files into bin dir or self created dir!!! That is also not good idea, because after testing i have to upload my webapplication to the server and as you know we have not access to the bin diirectory of the server!!

i have adjusted my code to :
String command = "mysql -h"+host+" -u"+user+" -p"+pw+" "+db;
String vars[] = null;
vars = new String[] {"<", baseDir+fileName, ""};
log.info("restore db quesy is: "+command);
rt.exec(command,vars);

but still nothing happens no restore!!!

I also tried to read the content op the file and save it into a string and tried exectuting it , but also no good result...
do you have some sample code doing restore ?
 
Azz Romaysa
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you mean by:
capture output and errorstream of the process?

give me a an idea about how to restore my database...
 
Azz Romaysa
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello javaranch...i am not receiving any response for my questions!!!
 
Jamie Williams
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi I have the exact same problem - it "just doesn't work" and I can't find the way to restore my sql file. I can do it in php easily, I can do the mysqldump command in java easily, but the restore won't work and I'm about to put my fist through a wall or a bullet in my head. PLEASE can someone help!

The output I get (from the process.getInputStream() etc. methods) is the same output I would get as if I type a malformed command on the command line. I am using RedHat linux. Sorry if this is messy, but I've looked and it's the same as if I made a syntax error on the command line:



The code I am using to do my restore is:




I can run the exact command that the program generates on the command line and IT WORKS but from inside the program it just doesn't. I really have no idea why not. Please can someone help!
[ May 09, 2006: Message edited by: Jamie Williams ]
 
Jamie Williams
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please? Can anyone help? I still can't figure out what's causing this error!
 
Martin Simons
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The problem may be the redirecting of input on the command line, the "<"
symbol. Runtime.exec does not have a true shell so the redirect will
probably not work in that manner. try prepending sh -c to the command:
i.e. sh -c mysql ...... < sqlfile
 
Jamie Williams
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply Martin, I tried what you suggested and I get a different problem now. It gives me back exit code 1 now ("Operation not permitted"). It might be the problem of using the redirect - I have run into errors with that before - but I have also tried to get it to work by executing

and that doesn't work either, it gives me the same error as using the redirect (without the "sh -c") i.e. invalid sytax.

Any other ideas? Thanks again!
 
Jamie Williams
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried the "sh -c" with the -e option


So once again I'm out of ideas.
[ May 09, 2006: Message edited by: Jamie Williams ]
 
Martin Simons
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you "just for fun" tried running the array argument version of
runtime exec rather than the string version?
 
Jamie Williams
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fixed it! I couldn't find a proper solution for this anywhere so here it is:



Note that you will also need to handle the input/output of the process by getting the streams eg. process.getInputStream() etc.

I think the most important thing here to notice is in the last element in the command array ("source " + scriptpath). There are no escaped double-quotes around the command i.e "\"source " + scriptpath + "\"" like I thought was the obvious way to do it. This must be being handled by Java. Please tell me if this helped anyone!
 
Jamie Williams
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry Martin, I was writing my last post when you did yours Yes I tried before but with the escaped double-quotes.
 
Ramesh kangamuthu
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ,

I need to know the right syntax or usage for restoring a backup file through java. I searched many forums and sites.

Nobody gave the properly working answer.

I need the java code for restoring a .sql file. Backup and restore worked properly if I run them in command prompt. but not in java code. Please give the exact code for it.




Thanks,

Ramesh Kangamuthu


 
Jan Cumps
Bartender
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you run a MySQL database?
Then have a look at the "Fixed it!" post a bit above your post. Does that work for you?

Regards, Jan
 
Ramesh kangamuthu
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jan Cumps,

I tried the post just above mine.But I didn't get the restore working. I feel that there is a need for a mechanism in java that enables the execution of windows command (especially in command prompt window ). Here is my code as follows,

Restore a backup database:-


String[] backupCommand = {"C:/mysql/bin/mysql","-u=webnet","-p=webnet",dbName,"-e","source "+backupFileName};

Process runtimeProcess=Runtime.getRuntime().exec(backupCommand);

here 'dbName' is a Java String variable for the database I used and

'backupFileName' is the file path of backup file.


I used the syntax accurately and no error and no restore. I provided the stack trace in the catch block. No restore.

Thanks,

Ramesh Kangamuthu
 
Jan Cumps
Bartender
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you have created a wrong command line
What happens if you replace "-u=webnet","-p=webnet" with "--user=webnet", "--password=webnet"

You have to use -uwebnet, or --user=webnet. -u=webnet is not valid.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic