How to take Mysql Dump

We can just check how to back up and restore mysql database and tables

if we want the backup of entire mysql db

# mysqldump -u root -p -A > /bkp/mysql_full.sql

-A ------ take all(full)db from /var/lib/mysql

-p ------ password

-u ------ user

# vim mysql_full.sql shows the complete file db entries in it.

If we want the backup of a single db

# mysqldump -u root  -p dbname > /bkp/bkp_dbname.sql

After taking this when we restore the db to another system first create the db with same name there and then restore the backup file to the new db

Restoring mysql db

# mysql -u root -p dbname < /bkp/bkp_dbname.sql

To take full db bkp with date,

# mysqldump -uroot -p -A > /bkp/fulldb-$(date %d.%m.%y).sql

Restoring a particular db from full db backup to another system or same system, If we want to create the db in another system first copy the full backup in that system

mysql> create database dbname;   ----- which we want to restore from other system (also from full db backup) and come out from the db, and put the restoration command

# mysql -u root -p dbname < full-02.Jun.10.sql

it will restore the db to the new system. Then login to the db and check the tables in the db

mysql> use dbname;

mysql> show tables;

Mysql dump full db with triggers and routines

# mysqldump -u root -p -A --triggers=TRUE --routines=TRUE --add-drop-table --databases | bzip2 > ALL_DB_`date %Y%m%d`.sql.bz2

 --add-drop-table may remove the existing table when you dump the file to another machine which has the same db.

above command dump all the databases. If we give "--all-databases --databases " parameters we don't want to make any db name while restoring the dump or else we have to create the same db before restoring the dump.

Dump single db with  triggers and routines. It will take stored procedures and functions as well

#  mysqldump -u root -p --triggers=TRUE --routines=TRUE --add-drop-table --databases dbname > dbname.sql

 If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

Taking a mysql table backup and restoring

# mysqldump -uroot -p dbname tablename > tablename.sql

For restoring a data base table

# mysql -uroot -p bdname < tablename.sql

Backup table with triggers and routines

# mysqldump -uroot -p -R --triggers dbname tablename > tablename.sql 

To take only trigger and routines from the db

# mysqldump -R -t -n -d  --databases dbname > dbname.sql

To take db dump without any date

# myqldump -u root -p -R -t -n -d --skip-opt dbname > dbname.sql 

To backup db,either cp files or db from /var/lib/mysql to another bd or other system and give mysql permission and if we run 'show' we can see all tables in the new db or in the system. Or use mysqldump

--add-drop-database  --- Add a DROP DATABASE statement before each CREATE DATABASE statement

--add-drop-table     --- Add a DROP TABLE statement before each CREATE TABLE statement

--add-locks          --- Surround each table dump with LOCK TABLES and UNLOCK TABLES statements

--all-databases      --- Dump all tables in all databases

--all-tablespaces    --- Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB Cluster table

--bind-address=ip_address ---- Use the specified network interface to connect to the MySQL Server

--comments           --- Add comments to the dump file

--databases          --- Dump several databases

--debug-info         --- Print debugging information, memory and CPU statistics when the program exits

--delete-master-logs --- On a master replication server, delete the binary logs after performing the dump operation

--dump-date          ---  Include dump date as "Dump completed on" comment if --comments is given

--flush-logs         --- Flush the MySQL server log files before starting the dump

--flush-privileges   --- Emit a FLUSH PRIVILEGES statement after dumping the mysql database

--host               --- Host to connect to (IP address or hostname)

--lock-all-tables    --- Lock all tables across all databases

--no-data            --- Do not dump table contents

--routines           --- Dump stored routines (procedures and functions) from the dumped databases

--skip-add-drop-table --- Do not add a DROP TABLE statement before each CREATE TABLE statement

--skip-triggers      --- Do not dump triggers

--triggers           --- Dump triggers for each dumped table