How to Split MySQL Database Dumps into Tables

Learn how to split MySQL database dumps into tables for better security, flexibility, and data efficiency.

Cloudways Product avatar
Written by Cloudways Product
Updated over a week ago

Splitting your database into tables allows multiple benefits. These include security, enhanced flexibility, scalability, and data efficiency. The split database is easier to protect and maintain, but unfortunately, this powerful approach needs technical expertise.

How to Split MySQL Database Dumps into Tables

Splitting a MySQL dump into tables helps in many cases. Some of them are listed below:

  • When moving significant data to different places (especially during development or migrating your website), it makes sense to use tables resulting in smaller file sizes for quick transferring of data.

  • When different people are involved in developing web applications including distinct database tables, it is recommended to split your database.

  • When you want to set multiple levels of permissions for different people on the various parts of the data collection, it may be more feasible to split your database.

To achieve that task. First, you have to get an SSH connection to your server.

When you successfully connect SSH, you need to go to your application folder by following these commands:

1) cd applications (enter)

2) type LS in lowercase and hit enter to see all the application folder name

3) cd (type app folder name) enter

4) cd public_html

5) mkdir (foldername) to save the dump and tables in this folder

6) cd (foldername)

You can see the example image below:

To split the database in multiple tables in the form of database dump into different files, the command below will split all the databases in the form of a gzip file):

for T in `mysql –u(username) –p(password) -N -B -e 'show tables from DBNAME'`; do echo $T; mysqldump –u(username) –p(password)  DBNAME $T | gzip -c > DBNAME_$T.dump.gz ;

done;

(hit enter)

The above command takes the database dump and then splits it into tables, which means you do not have to take the database dump separately.

To check the split tables, type ls.

If you already had a dump of your database and want to split it into tables, click here.

That’s it! We hope this article was helpful. If you need any help, then feel free to search your query on Cloudways Support Center or contact us via chat (Need a Hand > Send us a Message). Alternatively, you can also create a support ticket.

Did this answer your question?