MySQL Simple Recovery Project

Backup Basics in MySQL – Part 5

MySQL Course

Foreword: In this part of the series you will carry out a simple project to recover a small MySQL database after a crash.

By: Chrysanthus Date Published: 16 Jun 2015

Introduction

This is part 5 of my series, Backup Basics in MySQL. In this part of the series you will carry out a simple project to recover a small MySQL database after a crash. I assume you have read the previous parts of the series before reaching here; this is a continuation. In the project below, you will recovery the database as the root user. The crash is assumed (not real).

Recovery
After there is a crash in a MySQL server host, there has to be recovery of the database from the backup and the binary log. Both the mysql database and the created database have to be recovered. Full recovery is made from the last full backup. Incremental recovery is made from the binary logs increase after the last full backup.

In the project below, the full recovery will be made from the full backup of the precious part of the series; incremental recovery will be made from the binary log having the data added in this part of the series.

Simple Full Backup Recovery
After a crash in a server host, the thing to do is to repair the computer that had the crash and then reinstall MySQ server. Nothing stops you from abandoning the computer that had the crash and install MySQL server in a new computer.

The reinstalled server will have a new mysql database created automatically. A mysql database after operating for some time, has tables with sensitive information like user names, passwords and privileges. So you have to replace all the data (if any) in the mysql tables with the data backed up from the previous mysql database. In the project below, we backed up only the data in the user table. So only that data will be recovered for the mysql database.

When the MySQL server is reinstalled, it creates only a new mysql database and not the company or personal databases. For these databases, you have to recreate them using the creation statements (data definition statements) you backed up (copied). If you also backed up (copied) stored procedure and triggers creation statements, then you have to use that backup to recreate the stored procedures and triggers. Note: there is “shore create procedure” and “show create trigger” statements as well. In the project below, apart from the user table in the mysql database, we backed up only the creation statements for the shop database and its two tables. To recreate the items, you just have to execute (again) the creation statements, using the privileges in the recovered mysql database.

The data of the tables were backed up using the “select ... into outfile 'file_name'” statement. Then the files (file_name) were supposed to have been copied to a safe device (drive) separated from the server host. To restore, you have to copy the files from the safe location to the directories in the new host where they were saved by the “select ... into outfile 'file_name'” statements, in the previous server (host). Then you use the complement statement of “select ... into outfile 'file_name'”, which is, “load data infile 'file_name' into table tbl_name” to copy the data from the files in the directories in the server host to the re-created tables in the server host. The syntax of the complement statement is:

load data infile 'file_name' into table tbl_name

Similarly to the way you used “select ... into outfile 'file_name'” for each table, you will have to use “load data infile 'file_name' into table tbl_name” for each table file.

Incremental Recovery
The binary log records the activities of all databases as they happen. So when you flush logs during full backup note the number of the last binary log file. During recovery, you need but the binary log files that were created after this last one for the incremental backup. This is because all data that was logged before full backup is backup during full backup and so you do not need the binary log before full backup.

The mysqlcom client program command for incremental recovery from the log files is:

    increment result-file;

where result-file may be preceded by a path and it is the file that was saved with,

    mysqlbinlog --result-file=myFile.txt binlog.00000A binlog.00000B …

However, not all versions may support the “increment result-file;” command. In that case, in an independent command prompt (DOS Prompt) window use,

    mysqlbinlog binlog.00000A binlog.00000B …

to display the contents of the binlog files in text format. You can then copy the statements and input them to the mysqlcom client tool as freshly typed statements.

The Simple Project
Start the personal web server. Start the MySQL server in a command prompt window with a commend similar to:

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld" --console --log-bin=c:\dir1\binshop

This statement enables the binary log. However, you do not need to enable binary log during recovery. After recovery, you should re-start the server, enabling binary log for normal operations.

Start the mysqlcom client in a new command prompt window as root with command sequence similar to:

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Password: sql

Start another instance of the mysqlcom client in a third command prompt window as Paul with a command sequence similar to:

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "paul", "localhost", 3306;
Password: paulo123

In the previous series, we did a full backup (after flushing the logs). Let us add data whose insertion statements will go to the newly open binary log file (of course the data will go to the database tables). Execute (copy and paste) the following statements in Paul’s mysqlcom client window (do not forget to press the Enter key after the last statement):

use shop;
lock tables Sales WRITE, SaleDetails WRITE;
insert into Sales (EmployeeID, CustomerID) values (31,41);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 12, 15, 153);
insert into Sales (EmployeeID, CustomerID) values (31,42);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 11, 16, 18);
insert into Sales (EmployeeID, CustomerID) values (31,43);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 8, 3, 156);
unlock tables;

The insert statements for all these new data should now be in the latest (new) binary log file.

Now assume that the server has crashed and has been re-installed. So, in the server we have the newly created mysql database with tables that will have sensitive data (like passwords). At the moment (as we pretend) the tables are more or less empty. The shop database that Paul had is not in the re-installed server; it is in files in safe locations. We have to restore the mysql and shop databases. For simplicity, I indicated in the previous part of the series that we would restore only the user table for the mysql database; and that is what we shall do here. We shall restore all of the small shop database. For the mysql database tables, only the table data are fully backed up. Any incremental data for the tables can be got from the binary log.

Let us begin by restoring the user table. In the root mysqlcom client window, select the mysql database by typing the following:

    use mysql;

Since we are pretending that the user table is new, in order to use it as new, delete all its data with the following statement:

    delete from user;

The user table is now empty as if it were new. In this project, the user table data backup is still in the server host. It is in the file called, userdata.txt. So we shall use the “load data infile 'file_name' into table tbl_name” statement to restore the data into the table. So execute (type and press Enter) the following in the root mysql client window:

    load data infile 'userdata.txt' into table user;

Note: with the “select ... into outfile 'file_name'” and “load data infile 'file_name' into table tbl_name” statements, you do not normally type the path to the file.

Since we are pretending that the re-installed server does not have the shop database, in order to restore the database as if it was in real life, drop the shop database in the root mysqlcom window, with the following statement. (The statement may not work because in this project the corresponding shop directory has the backup table files).

    drop database shop;

If the above statement does not work, do not worry; go ahead and drop the tables in the shop database as follows in the root mysqlcom window (read the comment below):

    use shop;
    drop table Sales;
    drop table SaleDetails;

Comment: If it says the tables do not exist, for this project, do not worry.

Time to restore the shop database:

Open with a text editor (double-click) the text file, creationStmts.txt, in drive c, that has the creation statements (assuming you saved the data definition statements in creationStmts.txt). You can have any name for the creationStmts.txt file. You should see the database creation statement and the two creation statements for the two tables.

Select the database creation statement with your mouse and paste it in the root mysql client window; add a semicolon at the end of the pasted statement and press Enter, to create the database – If it says database already exist, for this project, do not worry. If the semicolon is already there, do not add anything.

In order to create the tables of the shop database, you have to select the newly created shop database. To do this, execute the following statement in the root mysqlcom client window:

    use shop;

The shop tables have to be created now. Select the Sales and SaleDetails creation statements with your mouse from the creationStmts.txt file, one after the other and paste in the root mysqlcom client window; add a semicolon at the end of each pasted statement and press Enter for each statement, to create each table. If the semicolon is already there, do not add anything.

At this point you have re-created the shop database and its tables. The next thing to do is to put back the data into the tables. To do this, execute the following statements (do not forget to press the Enter key after the last statement):

    load data infile 'salesdata.txt' into table Sales;
    load data infile 'SaleDetailsdata.txt' into table SaleDetails;

This completes Full recovery

Project Incremental Recovery
For this project, just after the full backup, we had the binshop.000001 and binshop.000002 binary files. The full recovery above has restored all what is in these two files. After the full recovery, any new data activity was recorded in the binshop.000003 file. So we need only the binshop.000003 file for the incremental recovery in this project. Execute the following command in a fourth Mysqlcom client window, to convert the binary file to a text format:

    mysqlbinlog --result-file=c:\binText.txt c:\dir1\binlog.000003

The file, binText.txt that is a name of your choice, now has the binlog file content in text format.

Execute the following command in the root (second) Mysqlcom client window to run the incremental statements, which have the increased data:

    increment c:\binText.txt;

If this method of incremental backup does not work, then in an independent command prompt (DOS Prompt) window use,

    mysqlbinlog binlog.000003

to display the contents of the binlog file in text format. Then copy the statements and input them to the Mysqlcom client tool as freshly typed statements.

At this point, recovery (for the project) is complete; the full recovery and incremental recovery are complete.

Now, Paul created and populated his shop database before the crash, while the root recovered (recreated) the database and its tables and data. This is possible because the root has all the privileges in the server. The root can access all the databases in the server, but Paul and others can only access their own databases.

Quit the two mysqlcom client programs ( and close windows). Shut down the MySQL server. Close any opened command prompt window.

Conclusion
This series has explained to you (reader) a simple way to backup and recover MySQL databases, in any operating system that supports MySQL (5.x.x) server. You must have heard that there are third party programs that do MySQL backup and recovery. These programs do what you have learned in this series, more automatically and more conveniently. They also have extra features. Now that you know the basics, maybe tomorrow, you can write your own third party program. Whatever is the case, you are now in the position to backup your own MySQL server. And that is the end of the series. I hope you appreciated it.

Chrys

Broad Network

Related Articles

MySQL Simple Recovery Project

Backup Basics in MySQL – Part 5

MySQL Course

Introduction

Related Links

Comments