aspose file tools*
The moose likes JDBC and the fly likes mysql database restore Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "mysql database restore" Watch "mysql database restore" New topic
Author

mysql database restore

Azz Romaysa
Ranch Hand

Joined: Dec 08, 2004
Posts: 66
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

Joined: Mar 22, 2000
Posts: 12788
    
    5
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

Joined: Dec 11, 2004
Posts: 13410

I'm going to move this to the JDBC forum where it is more likely to be seen by someone familiar with MySql.


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Azz Romaysa
Ranch Hand

Joined: Dec 08, 2004
Posts: 66
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

Joined: Dec 08, 2004
Posts: 66
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

Joined: Sep 16, 2004
Posts: 3636
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.


My blood is tested +ve for Java.
Chetan Parekh
Ranch Hand

Joined: Sep 16, 2004
Posts: 3636
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

Joined: Dec 08, 2004
Posts: 66
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

Joined: Dec 08, 2004
Posts: 66
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

Joined: Dec 08, 2004
Posts: 66
hello javaranch...i am not receiving any response for my questions!!!
Jamie Williams
Ranch Hand

Joined: Mar 31, 2006
Posts: 70
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

Joined: Mar 31, 2006
Posts: 70
Please? Can anyone help? I still can't figure out what's causing this error!
Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
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

Joined: Mar 31, 2006
Posts: 70
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

Joined: Mar 31, 2006
Posts: 70
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

Joined: Mar 02, 2006
Posts: 196
Have you "just for fun" tried running the array argument version of
runtime exec rather than the string version?
Jamie Williams
Ranch Hand

Joined: Mar 31, 2006
Posts: 70
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

Joined: Mar 31, 2006
Posts: 70
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

Joined: Mar 13, 2007
Posts: 79
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

Joined: Dec 20, 2006
Posts: 2500
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
Ramesh kangamuthu
Ranch Hand

Joined: Mar 13, 2007
Posts: 79
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

Joined: Dec 20, 2006
Posts: 2500
    
    8

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: mysql database restore