Essay on MySQL Backup and Recovery

Identify the open source PostgreSQL and MySQL databases back-up and recovery strategies

Database backup can be either physical (raw) or logical backups. Database back methods vary, but the most common database backup method is through physical hot backup using the MySQL enterprise backup. The latest edition of MySQL enterprise backup can be used to physically backup the whole instances of databases on just selected databases both incremental and compressed copies. Physical backup is preferred because the physical databases can be easily restored as compared using the MySQL database command. For example using the MySQL enterprise matchup method the database tables from storage engine are copied using the buffer pool when the database is running (Oracle Corporation, 2017).

Alternatively, backups can be made using MySQL dump or mysqlhotcopy which are just scripts for backing up all types of tables. However, it is important to note the specificity of the mysqlhotcopy that only works with specific storage engine. Online backup is also possible with--single-transaction. Finally, the backup can be made by copying the table files when using storage engines. The tables are represented by their files and copied. The tables are stored in the form of files then the files are copied in the form of (*.frm, *.MYD, and *.MYI files). The files can also be converted to delimited text files backups when needed using the command SELECT * INTO OUTFILE 'file_name' FROM tbl_name to allow the creation of the files into the MySQL server host as opposed to the client host. Never the less, in the recent past, incremental backup have been made by enabling a log bin (binary log), files system snap shop or replication slaves using the --slave-load-tmpdir command.

PostgreSQL and MySQL Database recovery strategies

MySQL restoration can be done using the MySQL enterprise backup to restore the InnoDB, tables or files. The ndb_restore can restore the NDB tables. While those files that are coped at the files system level can easily be copied into their original location using simple files systems commands (Oracle Corporation, 2017).

Recovery can also be a point in time recovery using event time or positions by bringing the server up to date incrementally depending on the time when the full backup was made. For enterprise backup recovery, the MySQL client is used to process the systems binary log output that was produced by the mysqlbinlog. It is, however, important to note that the MySQL cannot parse the binary logs with \0 (null) characters. However, it can be invoked using the --binary-mode option. One can install and configure PostgreSQL Database Servers, create PostgreSQL Database and Tables, Configure SSH Connectivity.

Major differences in database back-ups and recovery between open source databases and other DBMS systems like Oracle, SQL Server or DB2

Whereas closed sources allow schema based table managed, the open sources do not allow schema based table management (Oracle Corporation, 2017). Additionally, closed sourced databases such as DB2 only portions tables through shrading without in memory capacity while the open source systems meet all the current SQL standards. Finally, backup and recovery in closed sources system can be done easily using enterprise backup, and recovery program while the open sources are much flexible and can be done using a wide range of methodologies.

References

Oracle Corporation. (2017). MySQL :: MySQL 5.5 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log. Dev.mysql.com. Retrieved 26 November 2017, from https://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

Oracle Corporation. (2017). MySQL :: MySQL Backup and Recovery :: 1.2 Database Backup Methods. Dev.mysql.com. Retrieved 26 November 2017, from https://dev.mysql.com/doc/mysql-backup-excerpt/5.5/en/backup-methods.html