MySQL Simple Full Backup Project

Backup Basics in MySQL – Part 4

MySQL Course

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

By: Chrysanthus Date Published: 16 Jun 2015

Introduction

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

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 (in my system, simple default installation of both servers is OK, without further configuration).

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. If the MySQL server is already on, shut it down with the following tool (or similar) at the C:\ DOS prompt:

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqladmin" -u root -p shutdown

Type the password and press Enter if asked.

Start the server with binary logging enabled and the directory for the binary log already created. In my Windows operating system I typed the following (after creating the dir1 directory in drive C:\):

"C:\Program Files\MySQL\MySQL Server 5.5\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 command (DOS) prompt window, login as root and start Mysqlcom.pl (client). If you are using the Windows OS, then you will start Mysqlcom.pl and login like this:

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

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

    CREATE USER paul 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 Mysqlcom.pl client window (at the prompt):

    grant CREATE on *.* to paul;

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 Mysqlcom window as Paul and then create the database. Start a new command prompt window and login as Paul similarly to the following Windows OS commands:

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

You should now have 3 MySQL command prompt windows opened.

As Paul, create the database, shop as follows:

    create database shop;

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

    grant DROP on shop.* to paul;

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;

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;

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

Now, go to Paul’s Mysqlcom 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 REFERENCES Sales (SaleID) ON UPDATE CASCADE ON DELETE CASCADE,
    ProductID INTEGER NOT NULL,
    Quantity INTEGER,
    SoldPrice DECIMAL(19,2)
) ENGINE = INNODB;
CREATE INDEX saleIDProductID ON SaleDetails (SaleID, ProductID);

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;

Go back to the Mysqlcom 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 Mysqlcom 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\Application Data\MySQL\MySQL Server 5.5\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;

Type the following statement:

    show tables;

Go now to the file where you saved the data definition statements and note the creation statements for this database and these tables.

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\Application Data\MySQL\MySQL Server 5.5\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 Mysqlcom client window:

unlock tables;

And there you are. You have done a simple (manual) full backup of the database, shop (and mysql). Close and quit the two client programs and shutdown the server (and the computer).

Time to take a break. We stop here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

MySQL Simple Full Backup Project

Backup Basics in MySQL – Part 4

MySQL Course

Introduction

Related Links

Comments