Backing up and restoring MySQL databases and tables

MySQL is a robust relational database management system. It is used by many web applications around the globe. Most of the open source projects like Wordpress and phpBB uses MySQL. Some of the high-traffic sites like Google, Flicker, you tube, Facebook etc also make use of MySQL.

In this tutorial, I will describe how to backup MySQL databases and tables and later restore them.

Backing up databases

There are different ways to back MySQL databases.

Backing up with mysqldump

Backing a MySQL database is very easy with mysqldump utility. Just specify the name of database to be backed up and redirect the output to a file.

mysqldump db_name >backup_file_name.sql

For example, to make a backup of the august_sales database in the current directory, we would issue the following command

mysqldump august_sale >backup_august_sale.sql

This command will use the logged in user as username and no password (the default installation of MySQL has no password for user root but it is allowed access only from localhost).

To specify a different username use -u username and for password use -p, like so

mysqldump -u root -p august_sale >backup_august_sale.sql

It will prompt you for the root’s password and after typing it correctly, the database will be backed up as backup_august_sale.sql in the current working directory.

To backup more than one database at the same time into a single file, use –databases

mysqldump -u root -p --databases june_sales july_sales august_sale >backup_jun_jul_augt_sale.sql

It will backup the three databases mentioned to backup_jun_jul_augt_sale.sql.

To backup all databases, use –all-databases or -A

mysqldump -u root -p --all-databases >backup_all_Dbs.sql

To backup a specific table, specify the database name and then the table name, mysqldump will understand that you want to backup the table in that database. Example

mysqldump -u root -p  august_sale profit >backup_profit_tbl.sql

will dump table profit into backup_profit_tbl.sql file.

To backup only the data, use –no-create-info like so

mysqldump -u root -p --no-create-info august_sale >backup_august_sale_data.sql

Sometimes we want to just backup the structure or schema of a database. Use –no-data option for schema only as shown below

mysqldump -u root -p –no-data august_sale >backup_august_sale_schema.sql

Backing up with mysqlhotcopy

mysqlhotcopy is another utility that can be used to backup databases. The beauty of this perl script is that it first executes a READ LOCK for the specified databases and then FLUSH TABLES to ensure that the database files are actually in their current versions. It copies the database files directly from mysql data directory usually /var/lib/mysql/ to the backup location specified.

mysqlhotcopy db1 db2 db3 /var/backup/

Backing up with SELECT … INTO OUTFILE

Another useful way to back up, more appropriately exporting, data from tables is SELECT … INTO OUTFILE which allows us to export data from tables terminated by various delimiters. The exported data can then be imported into other applications including mysql.

SELECT * INTO OUTFILE '/home/user/mytable.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
 from mytable;

It will create table mytable.txt in /home/user/ directory in which each field is terminated by ‘,’ and each line by carriage return and new line feed. This kind of file is also called CSV and can be imported in Microsoft excel among other applications.

Restoring databases

Restoring databases is easier than often thought. There are two ways to restore a database or tables backed up with mysqldump.

From Linux shell, just redirect the back up file to mysql client, like below

mysql -u root -p 
or log in to mysql server using the mysql client
mysql -uroot -p
Enter password:
Then at mysql prompt type in source backup_file_name.sql
mysql>source backup_file_name.sql
Backups made using mysqlhotcopy is even easier to restor. Just copy the backed up files to mysql data directory and change the ownership to user mysql
cp -a /var/backup/ /var/lib/mysql/
chown -R mysql.mysql /var/lib/mysql/db*
We have two options for importing data exported with SELCT ... INTO OUTFILE. One by using mysqlimport utility and another LOAD DATA LOCAL INFILE.
Using mysqlimport is straightforward.  To import mytable.txt into database 'august_sale', use the following command
mysqlimport -uroot -p  --local august_sale '/home/user/mytable.txt' FIELDS TERMINATED BY ','LINES TERMINATED BY '\r\n'
Be careful though. The name of the table where the data is being imported to, must correspond to backup file name without its extension, mytable in this case.
Defaults options for FIELDS clause are FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
 and those for LINES clause are LINES TERMINATED BY '\n' STARTING BY ''. So if you have not specified the different options for FIELDS and LINES clauses when exporting then the defaults have been used. In that case, we can omitFIELDS TERMINATED BY ','LINES TERMINATED BY '\r\n'.
LOAD DATA LOCAL INFILE is similar except that it needs to be run from within mysql client.
mysql>LOAD DATA LOCAL INFILE '/home/user/mytable.txt' INTO TABLE employee FIELDS TERMINATED BY ','LINES TERMINATED BY '\r\n';
As for mysqlimport, if the default options were used when exporting then we do not need to specify them with LOAD DATA LOCAL INFILE.
For more in-depth information you can visit http://dev.mysql.com/doc/.
 


 



Share The Knowledge: