How to create backup copy of MySQL

There is a software (mysqldump) that allows creating of MySQL backup copies quickly and simple. mysqldump also allows fine adjustment of creating backup copies of database or individual tables. mysqldump would be the main tool for creating backup MySQL.

Let’s view how it works on a simple example. We have a hosting, database DBNAME, host HOST with MySQL server. Login info for server is:  LOGIN, PORT, PASS. We should make dump of DBNAME. Execute this in unix shell:

> mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME > dump.txt

When this command is executed in dump.txt file, we have a copy of MySQL-database DBNAME. mysqldump displays the results on your screen. You should redirect output to some file, for example, here ” > dump.txt “.

So, what do mysqldump do? It creates a script of restiring your data. For example, if you had test table in your database, which had test2 integer field and the only record “1111”, then mysqldump will create a script like this:

# MySQL dump 8.14
#
# Host: HOST             Database: DBNAME
#--------------------------------------------------------
# Server version        3.23.39-log

#
# Table structure for table 'test'
#

CREATE TABLE test (
  test2 int(11) default NULL
) TYPE=MyISAM;

#
# Dumping data for table 'test2'
#

INSERT INTO test2 VALUES ('1111');

Thus  mysqldump will describe all your tables and create INSERT-commands for restoring the data in tables. So, we redirect output of mysqldump in a text file. We will use this file for restoring later. Let’s view the process of restoring database from a backup.

We will use a standard mysql program for restoring. For example, we have backup in dump.txt file. We should restore it to a working base. Perform the following:

> mysql -uLOGIN -PPORT -hHOST -pPASS DBNAME < dump.txt

I.e. we force mysql-client to connect to a server and run the script. After this we will have tables and data from he backup in our database.