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.
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.
Runtime.getRuntime().exec(). This will let you run the mysql command directly, or you could even put the mysql commands into a shell or batch script and execute it like that.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.
mysqldumpto do this?