How to Backup MySQL Database?

To take a backup of MySQL database or databases, the database must exist in the database server and you must have access to it. The format of the command would be.

# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

The parameters of the said command as follows.
[username] : A valid MySQL username. 
[password] : A valid MySQL password for the user. 
[database_name] : A valid Database name you want to take backup. 
[dump_file.sql] : The name of backup dump file you want to generate.


How to Backup a Single MySQL Database?
To take a backup of single database, use the command as follows. The command will dump database [kcdata] structure with data on to a single dump file called kcdata.sql

# mysqldump -u root -psrinu kcdata > kcdata.sql

How to Backup Multiple MySQL Databases?
If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [kcdata, kcinfo] structure and data in to a single file called kcdata_kcinfo.sql

# mysqldump -u root -psrinu --databases kcdata kcinfo  > kcdata_kcinfo.sql

How to Backup All MySQL Databases?
If you want to take backup of all databases, then use the following command with option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql

# mysqldump -u root -psrinu --all-databases > all-databases.sql

How to Backup MySQL Database Structure Only?
If you only want the backup of database structure without data, then use the option –no-data in the command. The below command exports database [kcdata] Structure into a file kcdata_structure.sql

# mysqldump -u root -psrinu --no-data kcdata > kcdata_structure.sql

How to Backup MySQL Database Data Only?
To backup database Data only without structure, then use the option –no-create-info with the command. This command takes the database [kcdata] Data into a file kcdata_data.sql

# mysqldump -u root -psrinu --no-create-db --no-create-info kcdata > kcdata_data.sql