The consistent backup of the database is extremely important so that we can maintain the integrity of the data in case of a system failure, hardware or even to correct any user failures, such as the accidental removal of a database. For this, it is important to adopt a consistent backup policy (daily), as well as to know the possible techniques to do it. In MySQL it is possible to make a binary backup of the database, that is, a copy of the structure of files and directories that make up your databases and tables will be kept. In addition, you can opt for automatic mysql backup, where data will be stored in text format or in the form of SQL commands. We will describe here how to use these two forms of backup to perform a consistent copy of data.
When performing the backup procedure, an image of your data is created at the time of execution of the backup routine. When there are problems with your database that need a backup, you can use your last backup, returning only the data to the situation in which the database was at the time of this backup. What happens to data changed or inserted between backup and failure? In MySQL you can enable a binary change log (log-bin option in the configuration file), which stores all the commands that modify the database structure, and these can be used to recover data not contained in the backup. Logs are created with the extension indicating the log sequence number, which is incremented each time a new log is created. To “translate” the binary log into SQL commands, and its output can be used directly as input to MySQL, as in the example:
shell>mysqlbinlog mysql-bin.012 | mysql
Or,
shell> mysqlbinlog mysql-bin.012 > dump.log
shell> mysql < dump.log
These commands must be executed after restoring the backup. To facilitate the handling of the log in the restoration, that is, how to identify which commands were executed after the backup, it is important to maintain the synchronism between the binary log and the backup. As the logs have a sequential number, use the FLUSH LOGS command to create a new log file at backup time. Thus, it will be guaranteed that all changes after the backup will be stored in the logs created from that moment on. In data recovery, it is enough to execute all the logs from the moment of the backup, since the alterations of the previous logs will already be contained in the backup itself.
Once the use of log files for data restoration has been presented, we analyze the first form of backup that is based on copying database files. This copy can be done manually with the operating system (OS) copy commands or using backup tools of the OS itself. It is worth mentioning that to guarantee a consistent copy of these files, it is necessary to guarantee that there will be no writings in the database during the execution of the backup routine. This condition can be guaranteed by stopping the database manager (DBMS) or by blocking the tables, allowing only data readings (lock) during the backup. It is possible to make an online backup in MySQL, that is, without restrictions on using the database for writing during the backup process, but these methods will be covered in the next article.
To back up the physical structure of MyISAM tables, just copy all MYI, MYD and frm files. In the case of InnoDB tables, you must copy the frm files, all InnoDB log files and the files that make up your tablespace, which are defined in the innodb_data_file_path variable placed in the MySQL configuration file. In addition, the configuration file must be copied, since InnoDB consists of the sizes of the files defined in the configuration with the existing files, at DBMS initialization time, if these sizes do not match, the DBMS will not start its execution. To restore this type of backup, just copy the files back to their original locations and apply the binary logs to recover data changed between the backup and the problem with the database, as previously presented.
For MyISAM tables there are some MySQL tools for performing this type of backup. The first of these is the BACKUP TABLE command, which copies the MYD and frm files to the specified location. To restore the backup made with BACKUP TABLE, execute the RESTORE TABLE command, which will copy the files back and recreate the table indexes, since they are not copied by BACKUP TABLE. It is important to point out that before executing RESTORE TABLE you will have to remove the table, since MySQL does not overwrite files for security reasons. Another possibility is to use the mysqlhotcopy script, which locks the tables for writing during file copying, copies the files to the specified location and releases the tables lock.
The main problem with file copy backup is the fact that if there are corrupted files, your backup will inherit this inconsistent structure, and possibly cause problems when restoring the database using this backup. To work around this situation, it is better to make a copy of only the data and not the files. This type of backup can be performed using the SELECT .. INTO OUTFILE command or the mysqldump client. To export data from a table to a text file, proceed as follows:
mysql>SELECT * INTO OUTFILE ‘backup-t1.txt’
-> FIELDS TERMINATED BY ”
-> LINES TERMINATED BY ‘\n’
->FROM t1;
In this case, the columns will be separated by “tab” and the end of the line will be the character “\n”, and the import of this data can be done as follows:
mysql>LOAD DATA INFILE ‘backup-t1.txt’ INTO TABLE t1;
The second way to export data is through mysqldump, which is a tool for converting the database structure into SQL commands. There are many options for running this client, these options can be seen by running shell>mysqldump –help
The default way mysqldump runs generates CREATE TABLE statements for each table and INSERT statements for each of the table’s rows of data. The following example illustrates exporting a table via mysqldump:
shell>mysqldump banco1 tabela1 > backup-tabela1.sql
shell>cat backup-tabela1.sql
CREATE TABLE tabela1 ( id INT NOT NULL PRIMARY KEY,
nome CHAR(30) NOT NULL DEFAULT ” ) type=MyISAM;
INSERT INTO tabela1 VALUES (1, ‘Teste 1’);
INSERT INTO tabela1 VALUES (2, ‘Teste 2’);
INSERT INTO tabela1 VALUES (3, ‘Teste 3’);
INSERT INTO tabela1 VALUES (4, ‘Teste 4’);
INSERT INTO tabela1 VALUES (5, ‘Teste 5’);
…
In the example above, a dump of just one table was generated, but we could obtain a backup of all tables, informing only the name of the database, or generate the structure for all databases using the –all-databases option. To restore the backup made with mysqldump just execute the generated SQL file:
mysql>source backup-table1.sql;
or,
shell>mysql bank1 < backup-table1.sql;
What is PostgreSQL?
Broadly speaking, PostgreSQL is a manager that works with relational databases and is object-oriented. It is an open source program, that is, it is not under the control of any particular company, but rather it has a community of developers who work to improve the program in a selfless way. Besides, PostgreSQL backup tools have become users’ favourite thanks to the fact that they offer a series of advantages that are worth highlighting.
Main features of PostgreSQL
The main features of PostgreSQL are summarized in the following:
- It consists of a relational database manager, supports different types of data and is object-oriented.
- It is open source, that is, they have a community of developers who altruistically implement improvements or resolve errors.
- It uses a SQL language based on the ISO/IEC standard. Therefore, it allows you to import queries and script code from other databases.
- It complies with the ACID model, that is, the stored data has the characteristics of Atomicity, Consistency, Isolation and Durability.
- It offers options that other databases do not have, such as the possibility of creating legacy tables, schemas or triggers.
- It not only allows you to create procedures in SQL language, but also allows you to use other programming languages such as Python or Pearl.
- Allows you to define data that the program does not support by default.
- You can add new functions or extensions developed by third parties, or even by yourself.
- It is also characterized by offering great vertical scalability.
The advantage of file copying backup methods over data copying is that the former perform in a shorter time, since there is no need to read the database and convert the data into text or SQL commands. On the other hand, data copying is safer, since there are no corrupted files, and in addition the generated backup is portable since the generated output is compatible with several other DBMSs.
