Simple Full Backup Project in MySQL

MySQL Backup Basics – Part 4

Division 6

Forward: In this part of the series you will carry out a simple project to full backup a small MySQL database.

By: Chrysanthus Date Published: 8 Aug 2012

Introduction

This is part 4 of my series, MySQL Backup Basics. I assume you have read the previous part before reaching here. This is a continuation. In this part of the series you will carry out a simple project to full backup a small MySQL database.

Project Description
The project has full backup. Incremental backup will be going along side. In the next part of the series, we shall recovery the database with the assumption that the server computer has crashed.

You need a personal web server and a MySQL 5.x.x server. The principles (statements and commands) that the project demonstrates will work with your operating system. I (writer) am using the Windows operating system.

You, as root, will create a user whose account name and password are paul@localhost and paulo123 respectively. You will give Paul the privilege to create databases and the privilege to create and use tables in the database. For formality, you will also give him the privilege to drop his own database but not all databases, so that he cannot drop your own database. You on behalf of Paul, will login to the server and create a database called shop. You will create two tables in the database and populate the tables. You will then do full backup of the database. Of course, you as root will start the MySQL server with binary logging enabled.

The personal web server and the MySQL server should be setup to work together.

The Project
Start the personal web server if it has not already started. In my computer, my Apache server (personal web server) starts automatically. The Apache web server can be used as personal web server or as Internet server.

For simplicity in this project, the binary log files will be saved in a directory in the host computer. However, in real life you should save it in a different computer (network) or a device (drive) that is separated from the host computer. Start the server with binary logging enabled and the directory for the binary log already created. In my Windows operating system computer I typed the following:

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

After executing this statement, the files binshop.000001 and binshop.index were created in my c:\dir1 directory.

Open a new mysql client window, login as root and start mysql client. If you are using the Windows OS, then you will login and start mysql like this:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -h localhost -u root –ppsword

When the mysql> prompt is displayed, type the following to create the user, Paul:

    create user 'paul'@'localhost' identified by 'paulo123';

Now, still as root, give Paul the privilege to create any database, but not to drop any. You will give him the privilege to drop his own database later. Execute (type and press Enter) the following statement in the opened root mysql client window (at the prompt):

    grant CREATE on *.* to 'paul'@'localhost';

At this point the user can create his own database. The fact that “*.*” has been used in the above statement, means that the CREATE privilege in the statement is referring to databases and not tables.

You will now login in a new mysql window as Paul and then create the database. Start a new command prompt window and login as Paul similarly to the following Windows command:

    "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -h localhost -u paul -ppaulo123

As Paul, create the database, shop as follows:

    create database shop;

Go now to the root mysql client window and give Paul the privilege to drop (delete) his shop database as follows:

    grant DROP on shop.* to 'paul'@'localhost';

Still at the root window, grant Paul the tables privileges at the database (shop) level as follows:

    grant ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, SHOW VIEW, TRIGGER, UPDATE on shop.* to 'paul'@'localhost';

In the statement, since “shop.*” has been used instead of “*.*”, it means the CREATE and DROP privileges of the statement are for the tables and not for any database. The DELETE, INSERT and UPDATE privileges are for the table data (rows).  With the above privileges, Paul can now create tables and populate them with data in his shop database.

Still in the root window, give Paul the LOCK TABLES privilege as follows:

    grant SELECT, LOCK TABLES on shop.* to 'paul'@'localhost';

Paul needs the LOCK TABLES privilege in order to carry out his transactions.

Now, go to Paul’s mysql client window and type (you may copy and paste) the following statements. These statements create two tables called, Sales and SaleDeatails in the shop database and populate them with data (do not forget to press the Enter key after the last statement):

use shop;

#create tables
create table Sales
(
    SaleID INTEGER NOT NULL AUTO_INCREMENT,
    DateAndTime TIMESTAMP,
    EmployeeID INTEGER,
    CustomerID INTEGER,

    PRIMARY KEY (SaleID ASC)
) ENGINE = INNODB;
create table SaleDetails
(
    SaleID INTEGER NOT NULL,
    ProductID INTEGER NOT NULL,
    Quantity INTEGER,
    SoldPrice DECIMAL(19,2),

    PRIMARY KEY (SaleID, ProductID),
    FOREIGN KEY (SaleID) REFERENCES Sales (SaleID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB;

lock tables Sales WRITE, SaleDetails WRITE;
insert into Sales (EmployeeID, CustomerID) values (25,45);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 20, 5, 15.99);
insert into Sales (EmployeeID, CustomerID) values (25,44);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 22, 10, 25.99);
insert into Sales (EmployeeID, CustomerID) values (25,48);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 40, 3, 30.99);
unlock tables;

You have learned that when the server is shutdown, the current binary log file closes, for the next one to start when the server starts again. Test that as follows: Close mysql client for Paul, by typing quit and pressing Enter at Paul’s window. Paul’s instance of mysql has closed; you can go ahead to close his window. Quit your root mysql instance in the same way and shutdown the server from there. You can go ahead to close your root window. You can also close the server window.

You have shut down the server. If you like, you can shutdown the computer and then start everything again.

Now start the computer if it was off. Start the personal web server. Start the MySQL server with binary logging enabled – use same command above. You should see a new binary log file created in its directory (c:\dir1). In my computer the name of the new file is, binshop.000002 .

Start the mysql client window as Paul – see command above. Add more data with the following code:

use shop;
lock tables Sales WRITE, SaleDetails WRITE;
insert into Sales (EmployeeID, CustomerID) values (20,50);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 2, 15, 100);
insert into Sales (EmployeeID, CustomerID) values (20,51);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 3, 16, 150);
insert into Sales (EmployeeID, CustomerID) values (20,52);
set @SaleID = NULL;
select max(SaleID) from Sales into @SaleID;
insert into SaleDetails (SaleID, ProductID, Quantity, SoldPrice) values (@SaleID, 17, 3, 175);
unlock tables;

Start the mysql client window as root. You need to backup the mysql database and the shop database. You need to backup the data of the tables of the mysql database and not its creation statements. For simplicity in the project, we shall backup only the data of the user table. However, we shall backup the creation statements and the data of the tables (two) of the shop database.

To begin the full backup, you have to flush all logs and lock all database tables first. Execute the following statements in the root mysql client window (global level):

    flush logs;
    flush tables with read lock;

You will do all the backup as the root. You will begin by backing up the data of the user table in the mysql database. Type the following to select the mysql database:

    use mysql;

Backup the data of the user table as follows:

    select * from user into outfile 'userdata.txt';

The file should not already exist. Where has the server stored this file? Each MySQL installation has a directory referred to as the data directory. Maybe in your computer the file has been saved there. In my Windows OS computer the file is saved in the "C:\Documents and Settings\All Users.WINDOWS\Application Data\MySQL\MySQL Server 5.1\data\mysql" directory. This is the mysql database directory, created by the MySQL server, in my computer. Search your computer to see where it is saved - it is also possible for it to be saved in a hidden directory. In practice, you should copy the file from where it has been saved in the server host to a device that is separated from the server host (computer). In practice, you will also have to backup all the data of the tables of the server mysql database; here we backup only the user data for simplicity.

Execute the following statement, which display the databases in the server:

    show databases;

In the display you can see the databases in the server. You have to decide on the ones (or ones) to backup. You have already backup the mysql database, so you cannot choose that again. Choose shop, as intended (above); so execute the following statement:

    use shop;

Now, type the following commands and statements (copy and paste) to backup (save) the creation statements for the shop database (do not forget to press the Enter key after the last line):

    tee c:\creationStmts.txt
    show create database shop;
    show create table Sales;
    show create table SaleDetails;
    notee

In this code segment, the text file, creationStmts.txt is created in drive c and the create statements are copied into the file.

Now, backup up the Sales and SaleDetails tables of the shop database, by typing the following two statements (pressing the Enter key after the last one.):

    select * from Sales into outfile 'salesdata.txt';
    select * from SaleDetails into outfile 'SaleDetailsdata.txt';

In my computer, these files have been saved (backup) in the "C:\Documents and Settings\All Users.WINDOWS\Application Data\MySQL\MySQL Server 5.1\data\shop" directory. This is the shop database directory, created by the MySQL server, in my computer. The situation in you computer should be similar. In practice, the files have to be copied from there into a safe location separated from the server host.

So, in my computer, the “select ... into outfile 'file_name'” statement backups (saves) data in their corresponding database directories: Your case should be similar.

Now, you will unlock all the tables so that users can continue to use the database (e.g. to insert data) in their usual way. To do that, execute the following statement in the root mysql client window:

unlock tables;

And there you are. We have done a simple (manual) full backup of the database, shop (and mysql). Quit the two client programs and shutdown the server (and the computer). We stop here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

Simple Full Backup Project in MySQL

MySQL Backup Basics – Part 4

Division 6

Introduction

Related Links

Comments