mysql database restoring in JAVA

Question

I can find some commands through which I can restore a table or some tables from backup sql to database.

But If I want to create the full databse from back up sql what will be the procedure?

Please note that the database do not exist in server. I need to create it from the back up files.

mysqldump or a gui tools export routine like Mysql Workbench one. generates a script. create new schema/db. run script — Drew
– Drew, Commented Aug 14, 2015 at 11:07
but there is no existing db in server... so when dbname and password in a string like this will be there String[] executeCmd = new String[]{"mysql", "--user=" + dbUserName, "--password=" + dbPassword, dbName,"-e", " source "+source}; how to put it??? because there is no database in server. The dump is from another location and I can not create it before. @Drew — Bee
– Bee, Commented Aug 14, 2015 at 11:14
so you cannot create a new db in any way. There is NO way for you to create a db? I know I can't help you then if that is the case — Drew
– Drew, Commented Aug 14, 2015 at 11:28

Drew · Accepted Answer · 2015-08-14 11:42:30Z

1

I will give you an example from Mysql Workbench, a free download from Oracle/Mysql.

You go into Management / Data Export from the upper left window pane. Select the database. Decide what you want to export, such as Data and Structure, Stored Procedures, Views, etc.

You can export to individual .sql files for each table, or glom them all together compressed. So now you have one or more .sql files.

Alter the top of the .sql that says

`CREATE DATABASE  IF NOT EXISTS `myDbName` ... ;
USE `myDbName`;

Those are lines 1 and 2

Change those to what your new DB name is going to be.

Save .sql file. If more than one .sql file, do the same to all of them.

run those scripts against a server with adequate privileges. For instance, hypothetically, let's say you have a user such as dbAdminUser that came to life with a call like this:

CREATE USER 'dbAdminUser'@'localhost' IDENTIFIED BY 'myPassword';
CREATE USER 'dbAdminUser'@'127.0.0.1' IDENTIFIED BY 'myPassword';
CREATE USER 'dbAdminUser'@'%' IDENTIFIED BY 'myPassword';

GRANT ALL ON *.* TO 'dbAdminUser'@'localhost';
GRANT ALL ON *.* TO 'dbAdminUser'@'127.0.0.1';
GRANT ALL ON *.* TO 'dbAdminUser'@'%';

I see success in the making with the above approach, using dbAdminUser.

answered Aug 14, 2015 at 11:42

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Drew Over a year ago

in your comment, DbName would not be necessary, as the script is creating then doing a use

Drew Over a year ago

connect as the user dbAdminUser if using my Answer, following along with it

Drew Over a year ago

mysql -udbAdminUser -pmyPassword < dump.sql after modifying dump.sql as described in my Answer

Bee Over a year ago

I am going to try it . I was trying something... Unfortunatly I pasted that here. That's why I deleted. But I am not sure that I will be able to modify The SQL . The things will be done by a mid authority. they will just receive the dump. I was just writing a code to anonymise the database. The anonimise databse will be sent to me. With your comment I think I need to either modify the sql or have to create a database before. There is no way way to create the database directly.

Bee Over a year ago

can you think of any other way? @Drew

|

Collectives™ on Stack Overflow

mysql database restoring in JAVA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related