Mysql databses Backup & restore

Database backup command.
You can use mysqldump command to take backup of your databases.
#mysqldump -h mysqlpc -u raj -p **** testdata > testdata.sql
[-h] = host machine ..specify if u want.
[-u] = this is your database username
[-p] = this is the password for your database
[testdata] = the name of your database
[testdata.sql] = the file to which the backup should be written.

Multiple database backup.

If you want to specify the databases to backup, you can use the –databases parameter followed by the list of databases you would like to backup. Each database name has to be separated by at least one space when you type in the command. So if you have to backup 3 databases, let say Customers, Orders and Comments, you can issue the following command to back them up.
#mysqldump -u root -p **** –databases Customers Orders Comments > multibackup.sql

This is okay if you have a small set of databases you want to backup. Now how about backing up all the databases in the server? That’s an easy one, just use the –all-databases parameter to backup all the databases in the server in one step.
#mysqldump –all-databases> alldatabases.sql

A Shell Script for Automating Backups?
You can automate the backup process by making a small shell script which will create a daily backup file. How do you get cron to back up your database without overwriting the older backup? You can use a tiny shell script to add the date to your backup file. An example of a shell script you could use is shown below.
#!/bin/sh
date=`date -I`
mysqldump –all-databases gzip > /var/backup/backup-$date.sql.gz

Restore your databases.
Make sure database name available on your machine.. To check use following command.
#mysql -u root -p (mysql password)
After login use following command to check databases name.
mysql>show databases;
If database name available that fine if not the create database ..use following command to create database
mysql > create database testdata;
After that quit from mysql….now use followig command to restore your database.
#mysql -u root -p **** testdata < /opt/backup/testdata.sql
Your database restore sucessfully…now u can check.

mysql> create database raj;
Query OK, 1 row affected (0.00 sec)

mysql> grant usage on raman.* to vendor@localhost identified by ‘addbnm#83’;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on raman.* to vendor@localhost;
Query OK, 0 rows affected (0.00 sec)

[root@raj ~]# mysql -u vendor -p raman
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.77 Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show tables;
Empty set (0.00 sec)

Advertisements
  1. #1 by Maik on September 29, 2014 - 2:15 AM

    This design is wicked! You most certainly know how
    to keep a reader amused. Between your wit and your videos,
    I was almost moved to start my own blog (well, almost…HaHa!) Excellent
    job. I really enjoyed what you had to say, and more than that, how
    you presented it. Too cool!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: