How to truncate MySQL tables from the CLI

What powers the internet? Well, truthfully, there are many things, but databases are essential in enabling the world wide web to function as we know it.

When you navigate web address like "www.google.com" data packets fly back and forth between your computer’s and Google’s servers (since that’s the website you’re trying to access) in the background.

The data in those packets, which makes your internet browsing possible, is all stored in databases. Web developers use the MySQL truncate function to more efficiently recreate and organize this data.

Each database contains a vast amount of internet data. The MySQL database server platform keeps this data by distributing it within a table with organized columns and rows. Column headers name the data type, and rows contain the data itself.

About truncating MySQL tables

The MySQL truncate table function allows web developers to easily replicate a current table. This process is much faster than editing a table row by row, which can be tedious and time-consuming for larger tables.

There are several ways to truncate your site’s MySQL data. The method below requires SSH access and covers the process for our non-cloud hosting setup at Nexcess. If you need to review similar information for a cloud-based product at Nexcess, refer to the following article:

Truncating MariaDB/MySQL tables in the Nexcess Cloud

Nexcess clients on physical non-cloud servers must first enable SSH access and adjust their firewall settings in SiteWorx. This is because Nexcess Cloud accounts have SSH enabled by default, but non-cloud hosting accounts do not. Furthermore, non-cloud clients can manage their SSH details using SiteWorx.

Use the following procedure to perform the truncate table operation using the MySQL Command Line Interface (MySQL CLI).

Step 1: Open a command prompt or terminal window

The first step in the truncate process is opening up a command line prompt in Windows. You can search for "command prompt" on your Windows taskbar. Once your command line is open, you should see a black window with white text. It should say something like "C:/…>" with the cursor blinking.

You can also use MySQL Workbench to connect to a database to truncate MySQL tables. As well, there are other MySQL tools available, with the phpMyAdmin database utility being the most popular of them. Tuning your MySQL database for performance is always recommended.

Step 2: Connect to your MySQL server using the appropriate command, providing the necessary credentials

Before we connect to MySQL, we need to launch the application. Launch MySQL on your computer by using the following command:

mysql.exe -u[username] -p

After using this prompt, a MySQL root user account will launch from the Windows command prompt. You’ll be asked to enter a password.

Enter the MySQL password for this account but be sure to tag it with the "-u" tag. Next, press the "Enter" key, and you’ll connect to the MySQL server and see something like this:

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.11-log MySQL Community Server (GPL)

The command prompt should change and contain "mysql>" as the beginning prompt indicator. Next, use the command prompt to change your location to the MySQL folder. This can be done using the "cd" command. The full string will look like this:

cd c:\Program Files\MySQL

The next step is to list the contents of the current folder. Execute this by typing the "dir" function and pressing the "Enter" key. The contents of the that folder will be listed in front of you. One of the folders listed should be named after your current MySQL version.

For example, if MySQL 5.5 is installed on your computer, you’ll see a folder named "MySQL 5.5" present.

Step 3: Once connected to the MySQL server, select the database that contains the table you want to truncate via the USE statement

The USE statement in MySQL allows you to select a particular database as the default. Locate the database name you want to use and ensure you type that name on a single line. It should look like this:

USE db_name

Once a database is selected, it won’t change until you end the session or execute another USE command.

Step 4: Now, you can truncate a table using the TRUNCATE TABLE statement with the table name

Using the TRUNCATE TABLE statement in MySQL enables you to completely empty a table. It’s similar to executing the DROP TABLE and CREATE TABLE functions but TRUNCATE allows you to skip the Data Manipulation Language (DML) deletion method to aid in tuning MySQL databases for performance.

To use TRUNCATE TABLE, enter the following text string:

TRUNCATE TABLE table_name;

Step 5: Optionally, you can include the CASCADE keyword after the table name if you want to truncate the table and any child tables that reference it through foreign key constraints

You may want to use the CASCADE function if the table you’re referencing is a parent table with child tables nested underneath it. When used correctly, the CASCADE keyword will mirror any updates you make in the parent table in the corresponding child table.

To execute the CASCADE function, use this text string:

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

You can use the CASCADE function ON UPDATE or ON DELETE. To do so, you must input CASCADE where you see "reference_option" available in the statement syntax.

Step 6: Once you've entered the TRUNCATE TABLE statement, press the "Enter" key to execute the command

Double-check to ensure all your parameters are correct. If all the previous text strings are appropriately structured for your particular database, you can execute your TRUNCATE TABLE command by pressing the "Enter" key.

Step 7: Confirm the truncation action for a large amount of data is OK if prompted

Occasionally, when you truncate a table in MySQL, you may be asked to confirm the truncation. Truncate MySQL protocol uses this confirmation as a failsafe since you’re about to edit a large amount of data simultaneously. If prompted, take the necessary steps to confirm, and your TRUNCATE operation will continue.

Step 8: After truncating the table, you can exit the MySQL CLI by typing "exit" and pressing the "Enter" key

At this point, your selected table should be truncated. If you want to use MySQL to truncate another table, just run another truncate command.

If you’re done, type "exit" and press the "Enter" key to leave MySQL CLI prompt. Feel free to refer to the MySQL website for the most current and comprehensive MySQL documentation, which complements our Nexcess hosting documentation.

Not a Nexcess customer yet?

Get faster speeds using expert tips from us — such as those related to truncating MySQL tables covered in this article — on optimizing databases, website management, and hosting setup.

Stronger security, scalability, and 24/7/365 award-winning hosting support are just a few reasons why people choose Nexcess.

Explore our various web hosting plans and features or contact us to get started today. We're ready to partner with you on a superior web presence for your organization!

For 24-hour assistance any day of the year, reach out to our support team by email, via a chat, or through your Nexcess Client Portal.

Note about the original publication date

This article was originally published in September 2019. It has since been updated for accuracy and comprehensiveness.