MySQL Data Backup System Solution

With the popularization of computer networks, computer viruses have the opportunity to spread quickly, and the damage to data files is becoming increasingly serious. Users often need to back up important personal data in different regions. Based on the consideration of multiple users and data privacy, a personal data backup system solution is proposed. It utilizes the large capacity, high reliability, and high security features of network storage devices, It provides users with a certain amount of space to store personal data, and uses software permission management to achieve the confidentiality of personal data.
Currently, FTP server software in Linux mainly includes Wu-FTP and ProFTPD. Wu-FTP is a software pre-installed on Red Hat Linux, but it has many security vulnerabilities. ProFTPD is developed for the weaknesses of Wu-FTP. In addition to improving security, it also has many features, such as easy setup and running in Stand-alone mode. ProFTPD has become one of the most popular FTP server software After Wu-FTP.
ProFTPD uses the Linux User Account to manage users before version 1.2, that is, the password file. This method has many shortcomings. Compared with database operations, it is complex to add user accounts, modify passwords, and delete accounts, and files cannot support concurrent read/write operations; in Linux, the user account has the Telnet permission, which poses a security risk. The password file can only be modified by the root account. It is technically difficult to modify this file using a browser.
The Database Management User Function is added to ProFTPD version 1.2.4. Currently, only MySQL databases are supported. This account is only applicable to FTP servers, and there is no security risk. The development platform used in this article is a combination of Linux + ProFTPD + MySQL.
Overall Design
The system background uses an FTP server to provide users with the file management function. You can use various software that supports the FTP function to operate data files. The solution enables automatic application and password modification for user accounts. network administrators can use a browser to manage functions such as Account Modification space limit, password modification, and account deletion.
The core of the system is how to dynamically configure ProFTPD using development languages to manage accounts of a large number of users.
Solution details
For more information about ProFTPD installation and configuration files, see the system documentation. This document focuses on database-related information.
1. Install ProFTPD
1) install the MySQL database, create a MySQL installation directory symbol, and connect to/usr/local/mysql.
2) Before compilation, add MySQL and space limit modules to the compilation options. The Code is as follows:
#. /Configure -- with-deployed des =/usr/local/mysql/include -- with-libraries =/usr/local/mysql/lib/mysql -- with-modules = mod_ SQL: mod_ SQL _mysql: mod_quota
2. Add users
Create the FTPusers database and users table in MySQL to save FTP account information. The Code is as follows:

Create table users (userid varchar (50) not null, // user's FTP account name password varchar (50), // user's FTP account password uid int (5) DEFAULT '0' not null, // uid gid int (5) DEFAULT '100' not null in the user's home directory, // gid homedir varchar (65533) in the user's home directory ), // user main directory count int (11) DEFAULT '0', // Number of logins ftime timestamp (14), // last logon time shell varchar (255) DEFAULT '/bin/bash', primary key (userid) // PRIMARY keyword );

Add the MySQL configuration in proftpd. conf. The Code is as follows:

SQLConnectInfo ftpusers @ MySQL server name username password // database connection secret // The specified table name SQLUsernameFielduserid // The following information must correspond to sqlpasswordfieldpasswordwordsqlhomedirfieldhomedir secret in the users table

When creating a user, you must add a record to the table to add the account. When a user logs in with this account for the first time, the system automatically creates the user's home directory and sets the users and groups of the directory based on the uid and gid values. Generate a limit File Based on the default space limit value in the configuration file. The following is the configuration for setting the default space limit in ProFTPD. conf:

DefaultQuota204800000 // 200MB size QuotaTypehard

3. delete a user
To delete a user, you must delete the MySQL users table and the user's home directory. Note that, because the browser is used, the execution user of the development system should have the write permission on the user's home directory. The user and group in the user's main directory are determined by the uid and gid in the users table.
4. Modify the space limit
After the user's home directory is created, the system automatically generates a. quota space limit file. The file can only be modified by the root user. The file format is "total capacity used". The account management system only needs to overwrite the file with a new limited number of files. For "used capacity", the FTP server will automatically calculate each login time. For details about how to grant root permissions to the software system, see "sudoers ".
5. Logging
From the security perspective, logs are an important part. Traditional logs are recorded in files, and ProFTPD provides the function to record logs to the database. This not only facilitates log query, but also greatly improves the log query speed.
First, create the FTP_log data table in the MySQL database. The Code is as follows:

Create table FTP_log (id int (12) unsigned DEFAULT '0' not null auto_increment, // primary keyword filename varchar (255), // file name filesize int (10) unsigned, // file size email varchar (20) not null, // the user's account ip address varchar (15) not null, // the user's ip address action varchar (255 ), // user-operated content ftime datetime, // operation time primary key (id ));

Modify the ProFTPD. conf file and disable "log recording to file". The settings are as follows:

TransferLognone

To add the log configuration, you must upload, download, and delete operation records to the log. You can set the recorded content. For example:

SQLLog RETR,STOR,DELE insert FTP_log "null,'%f', %b, '%u','%h','%r',now()"

The following operations are recorded:
◆ RETR: Download of files;
◆ STOR: file upload;
◆ DELE: delete an object.
The log records the following content:
◆ % F. The name of the uploaded or downloaded file contains an absolute path;
◆ % B, file size, in bytes;
◆ % U, the FTP account used by the user;
◆ % H, user's IP address;
◆ Now (): provides a system time function for MySQL.
Conclusion
After learning about the key technologies of the system in detail, the development language can be selected based on the actual situation, and can be combined with the email server to achieve unified account and password for user operations. The detailed configuration of MySQL and ProFTPD is not described in detail due to limited space.