MySQL - SPLessons

MySQL Backup and Restore

Chapter 18

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL Backup and Restore

MySQL Backup and Restore

shape Description

Database are used to store huge amount of data and it becomes very crucial to Backup the data. In the event of some software or hardware failures, backup data can be used to recover the data. The current chapter MySQL Backup and Restore fundamentals provides the necessary security for protecting data stored in the MySQL Databases server.

shape Conceptual
figure

  • Lan – Local area network
  • Wan – Wide area network
  • Remote area – Which is accessible in a remote area

Backing up mysql database

shape Description

MySQL database can be classified into 2 ways.

Replicate the MySQL database files

shape Description

MySQL uses similar table arrangement on distant platforms, so copying MySQL table and index files from one platform to another without any complications. Backing up MySQL databases server, working test recovery procedures backups, and keeping copies of backups is secure, off-site location protects database from disastrous data loss.

Conveying tables to text files

shape Description

MySQL DUMP is an accessible command that can be used to backup the MySQL Database instantly to the text files. To use the MySQLDump command it is mandatory to login to the System running the MySQL Database. 

shape Syntax

mysqldump -u[username] -p[password] [database name]>[backupfile.mysql]

Username => Database user name

Password => User database password

Database name => Name of the database

Backupfile.mysql => File name for user database backup.

shape Examples

By viewing the below example, the concept of single database, multiple database and all database backup can be understood easily.

D:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -h 127.0.0.1 -u root -p employee;
d:\backup\singleDBBackup.sql
Enter password:**********
d:\Program Files\MySQL\MySQLServer 5.5\bin>mysqldump -h 127.0.0.1 -u root -p --database employee sakila>
d:\backup\multipleDBBackup.sql
Warning: Using unique option prefix database instead of databases is deprecated
and will be removed in future release.Please use the full name instead.
Enter password:**********
d:\Program Files\MySQL\MySQLServer 5.5\bin>mysqldump -h 127.0.0.1 -u root -p --all-database>
d:\backup\allDBBackup.sql
Warning: Using unique option prefix all-database instead of all-databases is deprecated
and will be removed in future release.Please use the full name instead.
Enter password:**********
Warning:Skipping the data of table mysql event.specify the --events option explicit

In the above example, first employee table had been chosen for backup and all the data contained in employee database has been backup into another location in the server. In the same way multiple and all databases can be backup.

Summary

shape Key Points

  • MySQL Backup and Restore is used to recover the data from database server, if any technical problems arises.
  • Replication is nothing but coping data from the server.
  • Converting tables into files.