CLOUDWAYS DATABASE MANAGER
To manage your application’s database and perform operations like importing and exporting the database (SQL file) into your application, Cloudways provides the Database Manager which can be launched via the Access Details page of your application. The goal of this article is to explore and understand the different options you will find using the Cloudways Database Manager.
However, before we proceed further, it is important to understand some of the basic MySQL statements, as this will help in understanding the different options provided in the Database Manager. Here’s a list of basic MySQL statements that you should know:
CREATE:
The CREATE DATABASE statement creates a new database in MySQL.
The CREATE TABLE statement allows you to create a new table in a database.
USE:
The USE statement helps you to select/use a database. You can also change to another database with this statement. Once you set the current database it will be the same until the end of the session unless you change it.
INSERT:
The INSERT statement allows you to insert one or more rows into a table.
DROP:
The DROP DATABASE statement drops all tables in the database and deletes the database permanently.
The DROP TABLE statement removes a table and its data permanently from the database.
UPDATE:
The UPDATE statement updates data in a table. It allows you to change the values in one or more columns of a single row or multiple rows.
TRUNCATE:
The TRUNCATE TABLE statement allows you to delete all data in a table.
DROP TABLE vs TRUNCATE TABLE — The difference
The DROP TABLE statement removes the table definition along with its content. As a result, table space is freed from memory and the view of table no longer exists.
On the other hand, the TRUNCATE statement deletes all the rows from the table. Running the TRUNCATE statement does not free the table space from memory and the view of the table still exists.
Database Export Options
We will be using the database uwhyvgjtdv as a sample to test out different available options.
1: Database
Available options for Database:
a) USE:
Selecting the USE option adds the line below to select the database name in the exported SQL file.
b) CREATE:
Selecting this option adds SQL code to create database uwhyvgjtdv
If this option is selected while exporting, you will need to manually drop the database uwhyvgjtdv before importing this SQL file otherwise you will run into the following error:
To avoid this error, you can use the DROP+CREATE instead.
c) DROP+CREATE:
Selecting this option adds the DROP DATABASE statement which drops all tables in the database and deletes the database before creating the database again.
If you want your application data to be removed completely and replaced with data from the MySQL file, you should use this option.
2: Tables
Here are the Tables options you will find while exporting the database.
a) CREATE:
Selecting the CREATE option adds SQL code to create the tables in the database.
If CREATE option is selected, when importing this SQL file, you will get the following error in the case that the orders table already exists in the database:
To avoid this, you can use the DROP+CREATE table option instead.
b) DROP+CREATE:
Adds the DROP TABLE statement to remove the table and its data from the database before creating the table.
3: Data
These are the available options to export Data:
For the purpose of understanding the different options to export “Data”, let’s consider two databases — app A (Source) database and app B (Destination) database to which we will be importing the SQL file created using the different export options.
Get Elisha Jamil’s stories in your inbox
Join Medium for free to get updates from this writer.
Here’s what app B (desination) database looks like:
This database consists of an “orders” table with 5 orders (rows). We will now see the changes in this database as a result of importing MySQL file created using different options in the Database Manager.
a) TRUNCATE+INSERT
This option will first delete all existing rows in table and then add the rows in the destination database table.
In other words, it will delete existing table data in the destination database and replace it with the table data from the MySQL file imported.
Here’s what the source database looks like:
This database has been exported using the TRUNCATE+INSERT data option.
Importing this SQL file at the destination removes data in the orders table and replaces it with data from the MySQL file we created.
Before import:
The order data is updated after successfully importing the MySQL file
Notice the new order 10106 has been added and order 10100 no longer exists after the import. If you want existing table data to be removed and replaced with data from the SQL file, you should use this option.
b) INSERT
If the INSERT option is selected in the DATA section, it will add all table rows to the destination table.
Note: If there is data that already exists at the destination, you will run into the following error while importing:
This error is because the destination database already consists of a table row with the primary key 10101.
This error can be avoided by using the INSERT+UPDATE option.
c) INSERT+UPDATE
Selecting this option will add table rows in the database and update any existing rows at the destination.
Here’s the difference in the MySQL file created using the INSERT and INSERT+UPDATE options.
Selecting the INSERT+UPDATE option adds the code highlighted in the screenshot below, to update any rows already existing at the destination.
Let’s try exporting a database using the INSERT+UPDATE option. Here’s what the source database looks like:
Importing the SQL file using the INSERT+UPDATE option at the destination adds table rows in the orders table and updates any existing data.
Before Import:
Here’s the updated database after importing the SQL file.
Note that order 10106 has been inserted/added and order 10102 has been updated after the import. Another thing to note is that this option doesn’t delete any previous data. For example, order 10100 is preserved after import even though the file we imported (app A) doesn’t contain this row.
Hence, when using the INSERT+UPDATE data option, you shouldn’t use the DROP+CREATE option for Tables or Database, as it would drop the existing tables/database before importing data from the SQL file and you would lose all previous data.
Export Selected Tables:
You can export the entire database by checking the Tables and Data boxes as highlighted below:
You can also export selected tables by only checking the tables you want to export.
Leaving the data box unchecked for a table will only include the CREATE TABLE statement and the table description in the exported MySQL file. Hence, you must have the data box checked to export the table data as well.
SQL commands:
The Database Manager also provides an interface to execute MySQL statements and make changes/updates to your database.
Conclusion:
The Database Manager is an easy management tool provided by Cloudways to manage your application’s database. It is highly advised to take a precautionary application-level backup before importing a MySQL file, which you can restore in case of any issues. The Cloudways support article for Database Manager states,
Cloudways Database Manager is not suitable for managing large databases (i.e. larger than 200MB). To manage massive databases, please use other Cloudways supported methods such as MySQL remote connections or SSH tunnel for MySQL clients.
If you run into any issues accessing/launching the Database Manager, check the MySQL service status under the Manage services section or reach out to Cloudways Support via LiveChat or ticket for 24/7 assistance.
