How to Backup and Restore MYSQL Database using mysqldump command

                                               

Mysql:
                  MySQL is most commonly used for Web applications and for  embedded  applications  and  has become a popular alternative to proprietary database systems because of its speed  and reliability. MySQL can run on LINUX, Windows and Mac OS.

          By default remote access to the MySQL database server is disabled for security reasons.
However, some time you need to provide remote access to database server from home or a web server.

                  Migrate Database from one server to another server remotely

1.Single Database backup :
mysqldump is a command to take backup MYSQL Database in *.sql  format

# cd  /opt/ (we are take backup database in /opt/ directory)

# mysqldump -u root -p[mysql-root-password] [database-name] > backupfilename.sql

# mysqldump -u root -pmahesh12 bsrtech_data > bsrtech_data_bkp.sql

where mysql-root password is "mahesh12"
      database name is "bsrtech_data"
      backup file name is "bsrtech_data_bkp.sql"

2.Take backup multiple databases :

Using mysqldump command, we can also take backup multiple databases

# mysqldump -u root -p[mysql-root-password] --databases [database1-name] [database1-name] > backupfilename.sql

Ex:

# mysqldump -u root -pmahesh12 --databases mahesh_data bsrtech_data  bsrjobs_data > bsrdata_bkp.sql

where mahesh_data, bsrtech_data and bsrjobs_data are three databases

Verify the three databases in backup file "bsrdata_bkp.sql"

# grep -i "database:" /opt/bsrdata_bkp.sql

   Database: mahesh_data

   Database: bsrtech_data

   Database: bsrjobs_data

3. To take backup all databases :
 Using mysqldump command to take entire database in ur server

# mysqldump -u root -pmahesh12 --all-databases > /opt/server_database_bkp.sql

where "mahesh12" is a password of mysql root user, and take total database in /opt/ directory.

4. To take backup a specific table :

 using "mysqldump" command to take backup specific table in a selected database.

# mysqldump -u root -ppassword database_name table_name > /opt/backup_filename.sql

# mysqldump -u root -pmahesh12 bsrtech_data contact_numbers > /opt/contacts_table.sql

where "bsrtech_data" is a database, "contact_numbers" is a table in this database and "/opt/contacts_table.sql" is a backup file

5. How to restore mysql database :
connect mysql and using " < " symbol to restore backup database

In this example restore "bsrtech_data" database, When you are restore the "bsrdata_bkp.sql" backup database,
make sure to create the "bsrtech_data" databse before you can restore the database.

# mysql -u root -pmahesh12

mysql> create database bsrtech_data;
Query OK, 1 row affected (0.02 sec)

# mysql -u root -ppassword database < backup_filename.sql

# mysql -u root -pmahesh12 bsrtech_data < /opt/bsrdata_bkp.sql

where "mahesh12" is a mysql-root password, "bsrtech_data" is a database, and "/opt/bsrdata_bkp.sql" is backup database.