In this article, we will go through understanding the different methods that are used to set up an FTP connection with a MySQL server. We will also guide you through a step-by-step process for each of those methods. These methods involve different tools that can be used to access the MySQL server and help in setting up FTP for the MySQL server.
Table of Contents
- What is File Transfer Protocol (FTP)?
- What is MySQL Database?
- Connecting MySQL to FTP via phpMyAdmin
- Connecting MySQL to FTP using Command-Line
- Connecting MySQL to FTP using MySQL Workbench
- Connecting MySQL to FTP using Skyvia
- Conclusion
What is FTP?
FTP (File Transfer Protocol) is a network protocol that is used for transferring data between different systems. This protocol allows for transferring huge amounts of data between the servers and is one of the most reliable methods to transfer data. Generally, users access FTP via a command-line interface to connect with the remote host (i.e. the system that we want to send or receive data) and transfer data to the local host (i.e. the system that we are initiating the FTP connection).
What is MySQL Database?
MySQL Database is a relational database management system, also known as RDBMS, that can store the data in a tabular format. It is one of the most widely used technologies in the big data ecosystem. The database is based upon structured query language (SQL) like most traditional relational databases. The data consists of tables and several relationships like one-to-one, one-to-many, and many-to-many relationships can exist between the data elements in the tables.
Connecting MySQL to FTP via phpMyAdmin
phpMyAdmin is an open-source management tool for databases like MySQL and MariaDB. It provides a user interface for the users to manage the databases. It also supports multiple operations like managing the tables in the databases, creating users and managing permissions to these users, executing SQL queries on the tables, etc.
phpMyAdmin, however, does not support connecting directly to an FTP server. We can import and export data from and to CSV files and upload/download these files to/from FTP manually or with some other tool.
Exporting data from MySQL using phpMyAdmin
phpMyAdmin allows transferring the MySQL database through FTP using its user interface. To export the data from MySQL, the following steps can be implemented:
1. On the homepage, click on the database that you want to export to your local machine.
2. Click on the Export button present in the top menu to initiate the exporting process.
3. Select the export option as required. phpMyAdmin allows two methods to export the data –
- Exporting the entire database as it is.
- Export only the selected tables with specific format options.
4. After selecting the required tables to be exported, click on the Go button at the bottom right corner to proceed with the export process. The data will successfully be exported to your local machine.
Importing the data to MySQL using phpMyAdmin
We can also import the CSV files from our local machine to the MySQL server using phpMyAdmin. The following steps guide you to import the data using FTP to the MySQL database:
1. After landing on the homepage of the phpMyAdmin dashboard, click on the database where you need to import the data. After that, click on the Import button present in the menu bar at the top.
2. Once the import page shows up, you shall see a Browse button for importing the CSV file through FTP. You may select the format of the file that will be uploaded. phpMyAdmin offers multiple file formats to import the data into the MySQL database.
3. If the first row of the file contains the names of the columns, then select the checkbox listed in the format-specific options. You can also change the delimiter options for the file. Click Go after the changes have been made.
4. The data import has been successfully executed. You will be able to see that the table has been created in the database which contains the data to be imported.
Limitations
- phpMyAdmin does not allow connecting to a remote FTP server to import or export the data. The data can be imported from or exported to the local machine only. Local machine refers to the system that the user is working on.
- phpMyAdmin does not support databases other than MySQL or MariaDB. So the data can only be imported/exported from these databases.
- It may run into errors when we are dealing with a huge database amounting to Gigabytes in size. It is preferred to use the tool when dealing with small databases.
Connecting MySQL to FTP using the Command-Line prompt
MySQL database provides a command-line interface that can be used for executing queries, managing the databases, users, permissions, etc. Many SQL developers also prefer using such command-line interfaces instead of GUIs. To be able to access the command-line interface, you need to be able to access the server where the database is hosted.
Besides, you can download and upload files from/to FTP servers via the command line.
Exporting data from MySQL using command-line interface
To export the data from the MySQL database, we will be using a utility called mysqldump that is provided by MySQL. It allows us to export multiple databases as well as tables into a single file at once. This utility is generally used for creating backups of the data whenever required.
The syntax of the utility is as follows:
mysqldump -u <username> -p [database_name] [table_name] > output_file_locationWe have used the following options in the above syntax:
- -u option is to specify the username of the MySQL server that will perform the operation.
- -p flag is used to indicate that we should be prompted to enter the password for the corresponding username.
- database_name is the database that we will refer to for exporting the data.
- table_name is the table to be exported in the specified location.
Exporting the database is a single-step procedure because of this utility.
mysqldump -u root -p app_db positive_data > positive.sql
root@804b9cef4532:/# mysqldump -u -root app_db positive_data > positive.sqlThe above command exports the data of table ‘positive_data’ from the ‘app_db’ database. If we want to export the entire database then, we do not require mentioning the table name.
Importing the data to MySQL using command-line interface
Before importing the data, we will need to upload the data into the server where MySQL is hosted via FTP. We shall also need to create a new database that will load the data into MySQL.
Use the following commands to create a new database in MySQL.
create database positive_messages;
mysql > create database positive_messages;
Query OK, 1 row affected (0.00 sec)After creating a new table, use the following commands to import the data to the MySQL database.
mysql -u root -p positive_messages < positive_new.sql
root@804b9cef4532:/# mysqldump -u -root -p positive_messages < positive_new.sql
Enter password:
root@804b9cef4532:/#The data has been successfully imported into the MySQL database. To verify the same, we can log in to the MySQL shell using the credentials and check the count of the records.
Limitations
- The command line prompt is not a viable option for all the users. Some users would want a GUI for a seamless experience.
- The process is more time-consuming and non-interactive than the other methods mentioned.
Connecting MySQL to FTP using MySQL Workbench
MySQL Workbench is a GUI for executing queries, managing users and their permissions, administering multiple databases and MySQL servers, etc. For exporting and importing the data, it provides a wizard-based process that simplifies the user experience. The below section shows how to export and import the data to MySQL using Workbench.
MySQL Workbench, however, does not support connecting directly to an FTP server. We can import and export data from and to CSV files and upload/download these files to/from FTP manually or with some other tool.
Exporting the data from MySQL using MySQL workbench
To export the data from MySQL over FTP, MySQL workbench provides a wizard for exporting the contents of the table. Follow the below steps to do the same:
1. Right-click on the table name that needs to be exported. It will show us the option to open the export wizard as highlighted in the below image. Click on the export wizard to initiate the process.
2. Select the columns that need to be exported from the table and click on Next.
3. Click on the browse button to specify where the table data needs to be exported. It will provide other options like exporting the data as a CSV file, or a json file. If you want to export the data on your local machine, then check the box as shown in the below image.
4. Click on Next to start the export process.
5. The export process will be successfully completed in some time. The next wizard shows the confirmation for the export process.
Importing the data to MySQL using MySQL workbench
To import the data from your local machine to the MySQL server,
1 Right-click on the database where you need to import the table. Select the Table Data Import Wizard option to start the process of importing data into MySQL via FTP.
2. Click on the browse option to select the file that needs to be imported.
Click on Next after the path of the file has been specified.
3. In the next step, we can either use an existing table to import the data or create a new one. For this example, we shall import the data into a new table. Enter the name of the table that needs to be created as specified in the below image.
4. The wizard automatically detects the type of file that needs to be exported. Specify the data type of each of the columns listed from the drop-down.
5. Click on Next to start the import process. After the importing has been completed, the wizard displays the confirmation for the same.
Limitations
- Limited Performance with Remote Connections. MySQL Workbench does allow for connections to remote databases, which is a feature many users find useful. However, it’s essential to note that the performance of these remote connections can be significantly impacted by network latency and bandwidth limitations. If you’re working with a database hosted in a different geographical location, you may experience delays or even timeouts during data transfer or query execution.
- Optimized Mainly for MySQL. While MySQL Workbench is a powerful tool for managing MySQL databases, its support for other database systems is limited. The tool does offer some level of compatibility through ODBC (Open Database Connectivity), but it’s not as seamless or feature-rich as when you’re working with MySQL or MariaDB databases. If you’re looking for a multi-database management solution, you might need to consider other options.
- Handling Large Databases. MySQL Workbench is capable of managing large databases, but doing so may require additional system resources and careful configuration. The tool can run into performance issues or errors when dealing with databases that are several gigabytes in size or larger. This limitation is particularly relevant for users who need to perform complex queries or data manipulations on large datasets. In such cases, you may need to optimize your system and MySQL Workbench settings to ensure smooth operation.
Connecting MySQL to FTP Using Skyvia
Skyvia is a powerful cloud data platform for a wide range of data-related tasks. It consists of several different products, including Data Integration product, which offers no-coding data integration between different cloud apps and databases. This product can help users integrate FTP and MySQL by automating exporting and importing csv file through FTP.
Unlike some of the previously listed tools, Skyvia can actually connect to FTP server, get CSV files from and put them to the server automatically. It can also be easily automated with a schedule settings and get CSV files from FTP by file masks. Besides, it’s not limited to MySQL — it supports a number of databases.
Exporting data from MySQL to FTP using Skyvia
The first step of MySQL and FTP integration using Skyvia is creating connections to MySQL and FTP server. To create a connection on Skyvia, click New and then click Connection. After this select the connector to use, MySQL or FTP. Skyvia also supports SFTP connections or FTPS connections.
For FTP connection, you need to enter the host to connect to. You may also need to specify user name, password, and port. By specifying the corresponding encryption mode, you can create FTPS connections.
For MySQL connection, you also need to enter the host, user name, password, port. In the Advanced settings you can select protocol for secure SSL and SSH connections. You can also create Agent connections, which work via the Agent application, to work with local databases on your computer or in your local network.
After creating connections, we need to configure export. We need to perform the following steps for it:
1 Click New, and under Integration click Export.
2 Optionally specify the meaningful name for the export package. Click Untitled and enter a more suitable name.
3. Select Source connection — the MySQL connection we have created.
4. Click CSV to storage service and then select the target FTP connection.
5. Optionally select the folder on the FTP server to export data to, or leave it empty to export data to the root folder.
6. Optionally specify the CSV options if you need non-default ones.
7. Add export task. Near Tasks click Add New.
8. Select an object to export data from in the object list. Then click Save. In the same way, you can add multiple export tasks, exporting data from multiple MySQL tables to FTP.
9. Finally, click Create. The export package is ready. Now you can click Run to start export.
Additionally, you can automate export by clicking Schedule and specifying schedule settings.
Importing Data to MySQL Using Skyvia
For importing data, we also need to create connections to MySQL and FTP. Though we don’t need to re-create them if we have already created them for data export. So let’s proceed to configuring import.
1 Click New, and under Integration click Import.
2. Optionally specify the meaningful name for the import package. Click Untitled and enter a more suitable name.
3. Click CSV from storage service and select the source connection — the FTP connection.
4. Select the Target connection — the MySQL connection.
5. Near Tasks click Add New to add a new import tasks.
6. In the CSV Path box, select the CSV file on FTP to export. Click Next Step.
7. On the next step, select the target table to import data to, and click Next Step again.
8. On the next step, we map target and source columns. Since source CSV and target table have columns with the same name, they are mapped automatically. Click Save.
9. Finally, click Create. The import package is ready. Now you can click Run to start import.
Limitations
- Skyvia has the free pricing plan for smaller data volumes, but if you need to export/import a lot of data, you will need to select one of the paid pricing plans.
- Skyvia Import does not automatically create table. Basically, most of the Skyvia tools don’t work with metadata. Skyvia exports and imports only data.
Conclusion
In this article, we glanced through the introduction of what FTP is, and what is MySQL server. We also underwent various methods to set up a connection of FTP to MySQL server. The article provided a step-by-step guide to completing the data import or data export process to the MySQL server through FTP for each of the methods mentioned in the article. Some of the methods can be used by beginners that are easy to understand because of the graphical user interface provided while the other method like the command-line method does not provide the same. Skyvia provides you with a simple user interface that is apt for beginners. It also supports automating the data import/export process with detailed logging for the executions.
