Skip to main content

Load large data into MySQL

Overview​

Load large data into a MySQL database using the LOAD DATA INFILE mechanism.

For smaller datasets, import data using a MySQL client connection. Use this guide when loading several GB of data.

SSH access required

To follow this guide, SSH access to the server and basic docker knowledge is required.

Copy data​

The LOAD DATA INFILE command imports a file from the MySQL server's file system. On Cloudron, the data file must be located under /run/mysql-files/ in the mysql container.

  1. Copy the data file (e.g., data.csv) to the server.

  2. Copy the data file into the mysql container:

root@my:~# docker cp data.csv mysql:/run/mysql-files/data.csv

Load data​

Execute LOAD DATA INFILE from inside the mysql container:

root@my:~# docker exec -ti mysql /bin/bash
root@mysql:/# mysql -uroot -p${CLOUDRON_MYSQL_ROOT_PASSWORD}
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.31-0ubuntu0.22.04.1 (Ubuntu)

mysql>

At the MySQL prompt, load the data into the database. Find your app's database name in the CLOUDRON_MYSQL_DATABASE environment variable in the app's Web Terminal:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| 405ae49cca94474c   |

...

mysql> use 405ae49cca94474c;
mysql> LOAD DATA INFILE '/run/mysql-files/data.csv' INTO TABLE employees FIELDS TERMINATED BY ',';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0