{"id":2274,"date":"2013-07-12T15:29:22","date_gmt":"2013-07-12T07:29:22","guid":{"rendered":"http:\/\/rmohan.com\/?p=2274"},"modified":"2013-07-12T15:30:39","modified_gmt":"2013-07-12T07:30:39","slug":"how-to-take-mysql-dump","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=2274","title":{"rendered":"How to take Mysql Dump"},"content":{"rendered":"
We can just check how to back up and restore mysql database and tables<\/div>\n

 <\/p>\n

if we want the backup of entire mysql db<\/div>\n
# mysqldump -u root -p -A > \/bkp\/mysql_full.sql<\/div>\n
-A —— take all(full)db from \/var\/lib\/mysql<\/div>\n
-p —— password<\/div>\n
-u —— user<\/div>\n

 <\/p>\n

# vim mysql_full.sql shows the complete file db entries in it.<\/div>\n
<\/div>\n
If we want the backup of a single db<\/div>\n
# mysqldump -u root\u00a0 -p dbname > \/bkp\/bkp_dbname.sql<\/div>\n
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<\/div>\n

 <\/p>\n

Restoring mysql db<\/div>\n
# mysql -u root -p dbname < \/bkp\/bkp_dbname.sql<\/div>\n

 <\/p>\n

To take full db bkp with date,<\/div>\n
# mysqldump -uroot -p -A > \/bkp\/fulldb-$(date %d.%m.%y).sql<\/div>\n

 <\/p>\n

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<\/div>\n

mysql> create database dbname;\u00a0\u00a0\u00a0—– which we want to restore from other system (also from full db backup) and come out from the db, and put the restoration command<\/p>\n

# mysql -u root -p dbname < full-02.Jun.10.sql<\/div>\n
it will restore the db to the new system. Then login to the db and check the tables in the db<\/div>\n
mysql> use dbname;<\/div>\n
mysql> show tables;<\/div>\n

 <\/p>\n

Mysql dump full db with triggers and routines<\/div>\n
# 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.<\/p>\n<\/div>\n
above command dump all the databases. If we give “--all-databases<\/code>\u00a0–databases\u00a0” 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.<\/div>\n

 <\/p>\n

Dump single db with\u00a0 triggers and routines. It will take stored procedures and functions as well<\/div>\n
#\u00a0 mysqldump -u root -p –triggers=TRUE –routines=TRUE –add-drop-table –databases dbname > dbname.sql<\/p>\n
\u00a0If you do not name any tables following\u00a0db_name<\/code><\/i>\u00a0or if you use the\u00a0--databases<\/code>or\u00a0--all-databases<\/code>\u00a0option, entire databases are dumped.<\/div>\n<\/div>\n

 <\/p>\n

Taking a mysql table backup and restoring<\/div>\n
# mysqldump -uroot -p dbname tablename > tablename.sql<\/div>\n

 <\/p>\n

For restoring a data base table<\/div>\n
# mysql -uroot -p bdname < tablename.sql<\/div>\n

 <\/p>\n

Backup table with triggers and routines<\/div>\n
# mysqldump -uroot -p -R –triggers dbname tablename > tablename.sql<\/div>\n

 <\/p>\n

To take only trigger and routines from the db<\/div>\n
# mysqldump -R -t -n -d\u00a0 –databases dbname > dbname.sql<\/div>\n

 <\/p>\n

To take db dump without any date<\/div>\n
# myqldump -u root -p -R -t -n -d –skip-opt dbname > dbname.sql<\/div>\n

 <\/p>\n

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<\/b>
\n
\n<\/b><\/p>\n
–add-drop-database\u00a0 — Add a DROP DATABASE statement before each CREATE DATABASE statement<\/div>\n
–add-drop-table\u00a0\u00a0\u00a0\u00a0 — Add a DROP TABLE statement before each CREATE TABLE statement<\/div>\n
–add-locks\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Surround each table dump with LOCK TABLES and UNLOCK TABLES statements<\/div>\n
–all-databases\u00a0\u00a0\u00a0\u00a0\u00a0 — Dump all tables in all databases<\/div>\n
–all-tablespaces\u00a0\u00a0\u00a0 — Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB Cluster table<\/div>\n
–bind-address=ip_address\u00a0—- Use the specified network interface to connect to the MySQL Server<\/div>\n
–comments\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Add comments to the dump file<\/div>\n
–databases\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Dump several databases<\/div>\n
–debug-info\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Print debugging information, memory and CPU statistics when the program exits<\/div>\n
–delete-master-logs\u00a0— On a master replication server, delete the binary logs after performing the dump operation<\/div>\n
–dump-date\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 —\u00a0 Include dump date as “Dump completed on” comment if –comments is given<\/div>\n
–flush-logs\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Flush the MySQL server log files before starting the dump<\/div>\n
–flush-privileges\u00a0\u00a0 — Emit a FLUSH PRIVILEGES statement after dumping the mysql database<\/div>\n
–host\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Host to connect to (IP address or hostname)<\/div>\n
–lock-all-tables\u00a0\u00a0\u00a0 — Lock all tables across all databases<\/div>\n
–no-data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Do not dump table contents<\/div>\n
–routines\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Dump stored routines (procedures and functions) from the dumped databases<\/div>\n
–skip-add-drop-table\u00a0— Do not add a DROP TABLE statement before each CREATE TABLE statement<\/div>\n
–skip-triggers\u00a0\u00a0\u00a0\u00a0\u00a0 — Do not dump triggers<\/div>\n
–triggers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Dump triggers for each dumped table<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

We can just check how to back up and restore mysql database and tables <\/p>\n

 <\/p>\n

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 <\/p>\n

 <\/p>\n

# vim mysql_full.sql shows the complete file db entries in […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2274"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2274"}],"version-history":[{"count":3,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2274\/revisions"}],"predecessor-version":[{"id":2276,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2274\/revisions\/2276"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2274"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2274"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2274"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}