CODEWITHSUNDEEP
javamysqlconnector-j
xboxmods
xboxmods

Reputation: 1

How to clone MySQL database in Java

I've been able to successfully connect to the MySQL database with connector/j, but I'm not sure where to go from here. I want to clone the database for use with a new sub domain and I'm just wondering if anyone could just give me a hint so I know which direction to go.

Any help would be much appreciated, thanks.

Upvotes: 0

Views: 2458

Answers (2)

Konrad Fryszkowski
Konrad Fryszkowski

Reputation: 1

I had the same problem.

So one solution is with Runtime.getRuntime().exec()

String command = "mysqldump -u <your login> --password=<your password> <database name to clone> | mysql -u root --password=<your password> <new database name>";

Process p = Runtime.getRuntime().exec(new String[]{"bash","-c",command});

Disadvantages: You need to store your password somewhere safely. It's not system independent.

Other solution (I think better one) is to run SQL file which will create your database schema. In mysql you can easily use mysqldump to generate schema for you. 

Resource resource = new ClassPathResource(defaultDatabaseSchemaFilePath);
ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(resource);
DataSource dataSource = multiTenantDataSource.getDataSource(dbName);
databasePopulator.execute(dataSource);

Disadvantages: In your SQL file in the beginning you need to add SET FOREIGN_KEY_CHECKS=0; and in the end SET FOREIGN_KEY_CHECKS=1; as databasePopulator executes commands one by one and probably there will be foreign key check problem.

Hope it will help someone in the future.

Upvotes: 0

chaos
chaos

Reputation: 124355

By far the easiest way to clone a MySQL database is by using the standard admin tools: mysqldump dbname > dbname.sql to get a SQL dump, mysqladmin create clonedb to make the new database, mysql clonedb < dbname.sql to read in the SQL to the clone database.

If you want to do it through Java code, you're kinda making life a lot harder than it needs to be.

Upvotes: 5

Related Questions