Simple Recovery Project in MySQL
MySQL Backup Basics – Part 5
Division 6
Forward: 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: 8 Aug 2012
Introduction
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 log 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 you backed up. If you also backed up (copied) stored procedure and triggers creation statements, then you have to use the backup to recreate the stored procedures and triggers. 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.
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 (or the one after, if you want precision), 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 mysql client program command for incremental recovery from the log files is:
mysqlbinlog binlog.000007 binlog.000008 binlog.000009 | mysql -u root -ppswrd
where this is an example statement with 3 binary log files.
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.1\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 mysql client in a new command prompt window as root with a command similar to:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -h localhost -u root -ppswrd
Start another instance of the mysql client in a third command prompt window as Paul with a command similar to:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -h localhost -u paul -paulo123
Each time you start the MySQL server (as above) a new binary log file is open. So at the moment, you should have 3 binary log files. In the previous series, we did a full backup (and flushing the logs). Let us add data whose insertion statements will go to the newly open binary log file. Execute (copy and paste) the following statements in Paul’s mysql 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;
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 save locations. We have to restore the mysql and shop databases. For simplicity, I indicated in the previous part of the series that we shall 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 mysql 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 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 mysql 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 mysql 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. You should see the database creation statement and the two creation statements for the two tables. You should also see other texts, in the file.
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 mysql 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 mysql client window; add a semicolon at the end of each pasted statement and press Enter for each statement, to create each table.
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
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 new (fourth) mysql client window, replacing, pswrd, with your root password – if you are not using the Windows OS, then you may need but forward slashes for the path.
mysqlbinlog c:\dir1\binshop.000003 | mysql -u root –ppswrd
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 mysql 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
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course