Database backup command.
You can use mysqldump command to take backup of your databases.
#mysqldump -h mysqlpc -u raman -p kumar 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 pass21 --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 folowing 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 raman;
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@raman ~]# 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)
You can use mysqldump command to take backup of your databases.
#mysqldump -h mysqlpc -u raman -p kumar 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 pass21 --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 folowing 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 raman;
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@raman ~]# 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)