Truncating MariaDB/MySQL tables in the Nexcess Cloud
Truncating MariaDB/MySQL tables allows you to easily remove all data from the chosen MySQL database tables without having to manually delete and recreate them. You can truncate MySQL tables from the command line interface or use a database management tool like phpMyAdmin.
MariaDB/MySQL database table truncation process
Truncate table is a statement used to empty a MariaDB/MySQL table without removing all rows one by one. For example, rather than removing all MariaDB/MySQL database table data or deleting and recreating a table manually, you can empty a MariaDB/MySQL table by truncating it.
For MariaDB/MySQL, the truncate table statement represents a combination of two operations — a DROP TABLE operation followed by a CREATE TABLE operation. Essentially, suppose you have decided to truncate a MySQL database table. In that case, two operations will be performed to make the process of data removal faster and more efficient, especially working with large MariaDB/MySQL tables. TRUNCATE TABLE has been proven to be much faster than DELETE for this reason.
Three main things to know before truncating MariaDB/MySQL tables
As truncate operations delete MySQL database tables entirely — thus removing all row data — you must be extremely careful. There are three main things you need to know before truncating any MySQL tables:
- Truncation operations cannot be rolled back as they cause an implicit commit. Therefore, once you have launched the truncation process, you cannot recover any data unless you restore the database table from a backup.
- The truncate table statement will fail for an InnoDB table referenced by other MySQL database tables using foreign key constraints. As a result, MariaDB/MySQL will throw an error message letting you know you cannot truncate a table referenced in a foreign key constraint.
- Truncation operations also cannot be performed if a current session holds an active table lock. Therefore, ensure that your website is not actively using the MariaDB/MySQL table that you will truncate in real time.
Truncating MariaDB/MySQL tables in the Nexcess Cloud using phpMyAdmin
You can truncate MariaDB/MySQL tables from the Nexcess Cloud by using phpMyAdmin. phpMyAdmin is a powerful database management tool that allows you to perform many operations on your website database from a graphical user interface.
Step by step instructions
- You can open the Databases management interface from the main menu in the Site Dashboard of the chosen website on WordPress and WooCommerce plans. For Magento and Drupal, the Databases page is available from the main menu of the selected cloud plan. You will find all MariaDB databases and database users you have created in the list.
- Click the Launch phpMyAdmin button, and the database management tool will open in a separate window. Ensure you have no browser extensions installed that can potentially block it to avoid any issues.
- Choose the MariaDB/MySQL database table to truncate by clicking the corresponding table name in the list on the left side of the page. Then, click on Operations from the main phpMyAdmin menu at the top of the window.
- Then, on the bottom of the page, choose Empty the Table (TRUNCATE). Next, confirm that you would like to perform the operation by clicking the OK button.
You should see the message saying that MySQL returned an empty result. This message means that the table has been successfully truncated, and you will see that it no longer has any data if you open it from phpMyAdmin again.
Conclusion
Truncating MariaDB/MySQL tables allows you to delete all row data fast and easily without performing any operations manually. phpMyAdmin is a great database management tool that will help you add and remove data from MariaDB/MySQL tables without using the command line interface. You can manage your MariaDB/MySQL databases and users from the Databases interface of the Nexcess Client Portal and access phpMyAdmin to work with your website database from a graphic user interface.