Dumping Data with mysqldump

mysqldump is a command to dump all the data into a SQL file from a Database, which is also called as logical backup, here the server will interact with database information and convert the data into logical form, usually the process is very slow when compared with Physical Backup.

In this post, we will look into how to take a backup of tables, stored procedures and views in a MySQL Database.

How to take backup of Tables:

mysqldump -u username -p password --single-transaction databasename > tables_dump.sql 

How to take backup of Stored Procedures:

mysqldump -u username -p password routines --no-create-info --no-data --no-create-db --single_transaction databasename > routines_dump.sql

How to take backup of Views:

mysqldump creates the views as tables in the exported sql file, the problem with this is while importing this backup to the database all the views will be created as Tables instead of Views. mysqldump doesn't support taking backup of views. But we have a trick here to follow.

Connect to your MySQL Client and run the below command. This will generates the set of sql's, copy those sql's into a file and then import back.

SELECT CONCAT('DROP TABLE IF EXISTS ',TABLE_NAME,';\n CREATE OR REPLACE VIEW ',TABLE_NAME, ' AS ', REPLACE(REPLACE(VIEW_DEFINITION,'\`schema_name\`.',''),'\`',''), ';') AS CREATE_VIEW_SQL from information_schema.VIEWS where TABLE_SCHEMA='schema_name';

Note: Here replace the schema_name with your database name.