How to backup MySQL database from a remote server in Java

Question

MySQL

Good day, there is a MySQL database on a remote server, such as almost all web hosts provide. Accordingly, from it there is only a host, the name of the database, login and password. The task is to make a backup of this database using Java. Using JDBC, connecting to it is not a problem, but here's how to make a backup (backup.sql) and save it to the local computer?

Answer 1

Since MySQL has a standard mysqldump tool for this purpose, in your case with Java there are two options.
1) Connect to the database and do SHOW TABLES
2) For each table, do SHOW CREATE TABLE table_name to get a SQL query to create the same table.
3) For each table, do a SELECT to get data, from which you then form INSERT queries.
4) Save it all to a file - and here it is your backup.
1) Run the mysqldump utility , if installed on the server where you are running the Java program, using Runtime.getRuntime().exec- www.mkyong.com/java/how-to-execute-shell-command-f...
If you had a similar task in PHP, then I would recommend using this wonderful utility: https://github.com/clouddueling/mysqldump-php

Answer 2

1) Connect to the database and do SHOW TABLES
2) For each table, do SHOW CREATE TABLE table_name to get a SQL query to create the same table.
3) For each table, do a SELECT to get the data from which you then form INSERT queries.
4) Save it all to a file - and here it is your backup.

And what about connections?
with foreign keys?
if there are views?
if temporary tables?
stored procedures?