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.
Conceptual
figure
Lan - Local area network
Wan - Wide area network
Remote area - Which is accessible in a remote area
Backing up mysql database
Description
MySQL database can be classified into 2 ways.
Replicate the MySQL Database files
Convey tables to text files
Replicate the MySQL database files
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
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.
Username => Database user name
Password => User database password
Database name => Name of the database
Backupfile.mysql => File name for user database backup.
Examples
By viewing the below example, the concept of single database, multiple database and all database backup can be understood easily.
[sql]
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[/sql]
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
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.