JDBC - Copy Database



Often, in real-world scenarios, there is a need to update a database. The motive may be to backup the database, or to make changes to some tables, but there is fear that other tables may get corrupted. Whatever be the case creating a backup is highly commonplace.

Copying a database in MySQL involves three steps. We have used database TUTORIALSPOINT to copy to a database TUTORIALSPOINT_COPY.

Step 1: Create Database

From mysql prompt, type following command to create a new database named TUTORIALSPOINT_COPY to act as backup database.

CREATE DATABASE TUTORIALSPOINT_COPY;

Now from mysql prompt, type following command to show all databases.

SHOW DATABASES;

It will show TUTORIALSPOINT_COPY database created as shown below:

Show Databases

Step 2: Create Dump of Original Database

From command prompt, type following command to create dump of database to be copied.

C:\> mysqldump -u root -p TUTORIALSPOINT > C:\Users\tutorialspoint.sql
C:\> Enter password: ******

The directory can be any directory. The file extension of the file has to be .sql ( in this case tutorialspoint.sql ). You can see the .sql file in your specified directory.

mysqldump tool copies database tables and everything related to a .sql file, tutorialspoint.sql. The above command uses "root" as the user, and password is entered in the next line. Note that root has privileges to mysqldump. The above command makes a sql file which has all the information.

Step 3: Use Dump of Original Database for Backup Database

From command prompt, type following command to use dump of original database to be used in backup database to copy complete database.

C:\> mysql -u root -p TUTORIALSPOINT_COPY < C:\Users\tutorialspoint.sql
C:\> Enter password: ******

That is all to it. Now you want to test if the copy has data. First, from mysql prompt, run the following command:

mysql> use TUTORIALSPOINT_COPY;
mysql> Database changed
mysql> select name from jdbc_blob_clob

See screenshot below:

Backup Database
Advertisements