mysql database restore

[Azz Romaysa]

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]

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]

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]

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]

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]

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]

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]

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]

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]

hello javaranch...i am not receiving any response for my questions!!!

[Jamie Williams]

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:

/usr/bin/mysql Ver 12.22 Distrib 4.0.22, for pc-linux (i686)Copyright
 (C) 2002 MySQL ABThis software comes with ABSOLUTELY NO WARRANTY. This is 
free software,and you are welcome to modify and redistribute it under the 
GPL licenseUsage: /usr/bin/mysql [OPTIONS] [database] -?, --help Display 
this help and exit. --auto-rehash Enable automatic rehashing. One doesn't 
need to use 'rehash' to get table and field completion, but startup and 
reconnecting may take a longer time. Disable with --disable-auto-rehash. -A,
 --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get 
table and field completion. This gives a quicker start of mysql and disables
 rehashing on reconnect. WARNING: options deprecated; use 
--disable-auto-rehash instead. -B, --batch Print results with a tab as 
separator, each row on new line. Doesn't use history file. 
--character-sets-dir=name Directory where character sets are. 
--default-character-set=name Set the default character set. -C, --compress 
Use compression in server/client protocol. -D, --database=name Database to 
use. -e, --execute=name Execute command and quit. (Output like with 
--batch). -E, --vertical Print the output of a query (rows) vertically. -f, 
--force Continue even if we get an sql error. -g, --no-named-commands Named 
commands are disabled. Use \* form only, or use named commands only in the 
beginning of a line ending with a semicolon (  :wink:   Since version 10.9 the 
client now starts with this option ENABLED by default! Disable with '-G'. 
Long format commands still work from the first line. WARNING: option 
deprecated; use --disable-named-commands instead. -G, --named-commands 
Enable named commands. Named commands mean this program's internal commands;
 see mysql> help . When enabled, the named commands can be used from any 
line of the query, otherwise only from the first line, before an enter. 
Disable with --disable-named-commands. This option is disabled by default. 
-i, --ignore-spaces Ignore space after function names. --local-infile 
Enable/disable LOAD DATA LOCAL INFILE. -b, --no-beep Turn off beep on error.
 -h, --host=name Connect to host. -H, --html Produce HTML output. -X, --xml 
Produce XML output --line-numbers Write line numbers for errors. -L, 
--skip-line-numbers Don't write line number for errors. WARNING: -L is 
deprecated, use long version of this option instead. --no-pager Disable 
pager and print to stdout. See interactive help (\h) also. WARNING: option 
deprecated; use --disable-pager instead. --no-tee Disable outfile. See 
interactive help (\h) also. WARNING: option deprecated; use --disable-tee 
instead -n, --unbuffered Flush buffer after each query. --column-names Write
 column names in results. -N, --skip-column-names Don't write column names 
in results. WARNING: -N is deprecated, use long version of this options 
instead. -O, --set-variable=name Change the value of a variable. Please note
 that this option is deprecated; you can set variables directly with 
--variable-name=value. -o, --one-database Only update the default database. 
This is useful for skipping updates to other database in the update log. 
--pager[=name] Pager to use to display results. If you don't supply an 
option the default pager is taken from your ENV variable PAGER. Valid pagers
 are less, more, cat [> filename], etc. See interactive help (\h) also. 
This option does not work in batch mode. -p, --password[=name] Password to 
use when connecting to server. If password is not given it's asked from the 
tty. -P, --port=# Port number to use for connection. --prompt=name Set the 
mysql prompt to this value. -q, --quick Don't cache result, print it row by 
row. This may slow down the server if the output is suspended. Doesn't use 
history file. -r, --raw Write fields without conversion. Used with --batch 
-s, --silent Be more silent. -S, --socket=name Socket file to use for 
connection. -t, --table Output in table format. -T, --debug-info Print some 
debug info at exit. --tee=name Append everything into outfile. See 
interactive help (\h) also. Does not work in batch mode. -u, --user=name 
User for login if not current user. -U, --safe-updates Only allow UPDATE and
 DELETE that uses keys. -U, --i-am-a-dummy Synonym for option 
--safe-updates, -U. -v, --verbose Write more. (-v -v -v gives the table 
output format) -V, --version Output version information and exit. -w, --wait
 Wait and retry if connection is down. --connect_timeout=# 
--max_allowed_packet=# --net_buffer_length=# --select_limit=# 
--max_join_size=# Default options are read from the following files in the 
given order:/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf The following groups
 are read: mysql clientThe following options may be given as the first 
argument:--print-defaults Print the program argument list and 
exit--no-defaults Don't read default options from any options 
file--defaults-file=# Only read default options from the given file 
#--defaults-extra-file=# Read this file after the global files are 
readVariables (--variable-name=value)and boolean options {FALSE|TRUE} Value 
(after reading options)--------------------------------- 
-----------------------------auto-rehash FALSEcharacter-sets-dir (No default
 value)default-character-set (No default value)compress FALSEdatabase (No 
default value)vertical FALSEforce FALSEnamed-commands FALSElocal-infile 
FALSEno-beep FALSEhost (No default value)html FALSExml FALSEline-numbers 
TRUEunbuffered FALSEcolumn-names TRUEport 3306prompt mysql> quick 
FALSEraw FALSEsocket /var/lib/mysql/mysql.socktable FALSEdebug-info 
FALSEuser rootsafe-updates FALSEi-am-a-dummy FALSEconnect_timeout 
0max_allowed_packet 16777216net_buffer_length 16384select_limit 
1000max_join_size 1000000

The code I am using to do my restore is:

String cmd = "mysql -u user -ppassword PV1 < " + script.getPath();
Process restore = Runtime.getRuntime().exec(cmd);

InputStream inputstream = restore.getInputStream();
BufferedReader reader = new BufferedReader(new InputStreamReader(inputstream));
String line, result ="";

//THIS GETS THE OUTPUT ABOVE AND RETURNS TO AN ERROR PAGE IN MY JSF APPLICATION
while ((line = reader.readLine()) != null)   
{
    result += line;
}
 
if(restore.waitFor() != 0)
{
    return result + " " + cmd;
}  

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]

Please? Can anyone help? I still can't figure out what's causing this error!

[Martin Simons]

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]

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

mysql -u user -ppass DB1 -e "source file.sql"

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]

I tried the "sh -c" with the -e option

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
sh -c mysql -u root -ppass -D DB1 -e "source file.sql"

So once again I'm out of ideas.
[ May 09, 2006: Message edited by: Jamie Williams ]

[Martin Simons]

Have you "just for fun" tried running the array argument version of
runtime exec rather than the string version?

[Jamie Williams]

Fixed it! I couldn't find a proper solution for this anywhere so here it is:

String[] command = new String[] {"mysql", "--user=user", "--password=pass", "DBName", "-e", "source " + scriptPath};
Process restore = Runtime.getRuntime().exec(command);

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]

Sorry Martin, I was writing my last post when you did yours Yes I tried before but with the escaped double-quotes.

[Ramesh kangamuthu]

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]

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]

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]

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.