HowTo: FTP service using MySQL database

Despite some uncertainty that may surround MySQL, personally I still prefer working with this DB to using some other. I have nothing against Postgre, I have simply used MySQL my whole life, and until now I have had no reason to rethink its use.

This time I will teach you how to install an FTP server, but not only that, I will teach you in a not so complex way, how to make users, passwords and other user data be stored in a MySQL database, and not in accounts local.

Why do this like this?

Simple, because when making a backup, reinstalling a server or any other significant change, moving the service would be as simple as copying a configuration file, and exporting the MySQL database to FTP.

To achieve this we will use Pure-FTPd, well ... let's get started 🙂

Installing the FTP service with Pure-FTPd

1. The first thing to do is install the package: pure-ftpd-mysql

In distros like Debian or derivatives: aptitude install pure-ftpd-mysql

2. Once installed, we started the service but we must stop it, to stop it on systems such as Debian or derivatives it is enough with:

/etc/init.d/pure-ftpd-mysql stop

However, I leave you a line that will stop the service regardless of the distro you use:

ps ax | grep pure | grep -v grep | awk '{print $1}' | xargs kill

Preparing conditions on the MySQL server

I already explained not long ago how to create a database, a user and give that user permissions in the database: Users and permissions in MySQL

Let's move on to what will we do here? ...

1. We will create a database yes, but first we access MySQL:

mysql -u root -p

2. Once inside MySQL we go on to create the database myftpdb:

CREATE DATABASE myftpdb;

3. Now we will create the user myftpuser and we will give permissions to use user on the database that we just created, this user will have as password myftppassword:

CREATE USER 'myftpuser'@'localhost' IDENTIFIED BY 'myftppassword';
GRANT ALL PRIVILEGES ON myftpdb.* TO 'myftpuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES ;

4. Ready, we have created the database, the user and set the permissions. Now we must import the default (or clean) database for this to be complete. To do this, let's first exit MySQL:

exit;

Now let's download the default database that I offer you:

Or on the server use the following line:

wget http://ftp.desdelinux.net/myftpdb.sql

Ready, we already have it on our server, now it only remains to import your data:

mysql -u root -p myftpdb < myftpdb.sql

And ready!

5. And this is all to have the conditions of our MySQL ready.

Joining FTP with MySQL

Well, we already have the FTP service installed, the MySQL service installed and with our database set ... now we just need, join FTP service with MySQL.

1. First we must download the configuration file that we will use for the aforementioned. In the server terminal let's put the following line:

cd /etc/pure-ftpd/ && wget http://ftp.desdelinux.net/pure-ftpd-mysql.conf

2. Now we start the FTP service telling it to use to authenticate MySQL users, and we will also indicate which configuration file to use to connect to MySQL:

pure-ftpd-mysql -l mysql:/etc/pure-ftpd/pure-ftpd-mysql.conf

And voila 😀

This is enough to install our own FTP server authenticating with a MySQL database.

If you want that whenever the server starts automatically it starts the FTP service, you must put in the file /etc/rc.local the line that we use to execute the FTP, that is, we put in /etc/rc.local this:

pure-ftpd-mysql -l mysql:/etc/pure-ftpd/pure-ftpd-mysql.conf

By the way, you can access FTP using any browser, as well as FTP clients like Filezilla ... and not only that, using file browsers like Nautilus, Dolphin or PCManFM you can also download and upload files 😀

Test user who is in the database

User: testuser

Password: testpassword

How to manage FTP users?

Well, considering that it is a MySQL database, as I said above ... using PHPMyAdmin or Adminer will suffice. Just use your favorite application to manage the database, which contains a single table: users ... and in it are the users, here is a screenshot:

If you want to create a new user, you can duplicate or clone the existing line and change the data that would be different between both users, here I show you a screenshot:

Well ... nothing more to add 🙂

I hope this is useful to you and you know, any questions or suggestions let me know.

regards

PD: In this tutorial we use passwords saved in the database in plain text, if you want more security I recommend you try md5 😉