FsseInfo: MySQL Dump

Revision [1220]

Last edited on 2011-11-22 10:31:25 by FsseInfo

No Differences

Revision [1161]

Edited on 2011-09-16 18:41:20 by FsseInfo

Additions:

$ mysqldump \
-u myuser -p -h mydbhost mydatabase > mydbbackup.sql
$ mysqldump \
--user=myuser --password > mybackup.sql
==remotehost==
If you db if not on localhost you'll need to specify a remote host

Deletions:

$ mysqldump -u myuser -p \
mydatabase > mydbbackup.sql
> mybackup.sql
==Specify a host db if not localhost==

Revision [1110]

Edited on 2011-04-18 14:49:48 by FsseInfo

Additions:

--add-drop-table \
mydatabase > mydbbackup.sql
--add-drop-table \
--all-databases \
> mybackup.sql
--add-drop-table \
mydatabase mytable > mybackup.sql
--add-drop-table \
mydatabase mytable -w "mycolumn in ('IC','GG','GJ','IM')" > mybackup.sql
--add-drop-table \
mydatabase $(echo "use mydatabase ; show tables like 'mytblprefix%'" | mysql --skip-column-names) > mybackup.sql

Deletions:

--add-drop-table mydatabase > mydbbackup.sql
--add-drop-table --all-databases > mybackup.sql
--add-drop-table mydatabase mytable > mybackup.sql
--add-drop-table mydatabase mytable -w "mycolumn in ('IC','GG','GJ','IM')" > mybackup.sql
mydbname $(echo "use mydbname ; show tables like 'mytblprefix%'" | mysql --skip-column-names) > mybackup.sql

Revision [1109]

Edited on 2011-04-18 14:16:33 by FsseInfo

Additions:

>>http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
>>===MySQL Dump===

Deletions:

~~===MySQL Dump===~~

Revision [1108]

Edited on 2011-04-18 14:16:05 by FsseInfo

Additions:

$ mysqldump -u myuser -p \
--compatible=ansi --complete-insert \
--single-transaction \
$ mysqldump --user=myuser --password \
--compatible=ansi --complete-insert \
--single-transaction \
$ mysqldump --user=myuser --password \
--complete-insert --compatible=ansi \
--single-transaction \
mysqldump --user=myuser --password \
--complete-insert --compatible=ansi \
$ mysqldump --user=myuser --password \
--complete-insert --compatible=ansi \
--single-transaction \
mydbname $(echo "use mydbname ; show tables like 'mytblprefix%'" | mysql --skip-column-names) > mybackup.sql
--complete-insert --compatible=ansi \
mydbname > mydb.sql

Deletions:

$ mysqldump -u myuser -p --complete-insert --compatible=ansi \
$ mysqldump --user=myuser --password --complete-insert --compatible=ansi \
$ mysqldump --user=myuser --password --complete-insert --compatible=ansi \
mysqldump --user=myuser --password --complete-insert --compatible=ansi \
$ mysqldump --user=myuser --password --complete-insert --compatible=ansi \
mydbname
$(echo "use mydbname ; show tables like 'mytblprefix%'" | mysql --skip-column-names) > mybackup.sql
$ mysqldump -h myhost -u myuser -p mydbname --complete-insert --compatible=ansi \
> mydb.sql

Revision [1107]

Edited on 2011-04-14 13:52:36 by FsseInfo

Additions:

$ mysqldump -u myuser -p --complete-insert --compatible=ansi \
--skip-lock-tables --skip-extended-insert --skip-quote-names \
--add-drop-table mydatabase > mydbbackup.sql
$ mysqldump --user=myuser --password --complete-insert --compatible=ansi \
--skip-lock-tables --skip-extended-insert --skip-quote-names \
--add-drop-table --all-databases > mybackup.sql
==Specify a host db if not localhost==
host
-h mydbip
host
--host mydbhost
$ mysqldump --user=myuser --password --complete-insert --compatible=ansi \
--skip-lock-tables --skip-extended-insert --skip-quote-names \
--add-drop-table mydatabase mytable > mybackup.sql
mysqldump --user=myuser --password --complete-insert --compatible=ansi \
--skip-lock-tables --skip-extended-insert --skip-quote-names \
--add-drop-table mydatabase mytable -w "mycolumn in ('IC','GG','GJ','IM')" > mybackup.sql
$ mysqldump --user=myuser --password --complete-insert --compatible=ansi \
--skip-lock-tables --skip-extended-insert --skip-quote-names \
$ mysqldump -h myhost -u myuser -p mydbname --complete-insert --compatible=ansi \
--skip-add-locks --skip-quote-names --skip-extended-insert \
> mydb.sql
--no-data --skip-lock-tables --compact \
--tab=mydir mydb
$ mysqldump -h myhost -u myuser -p
--skip-lock-tables --compact \
--xml mydb mytable

Deletions:

$ mysqldump --host mydbhost --user=myuser --password --all-databases > mybackup.sql
$ mysqldump -h mydbip -u myuser -p --add-drop-table mydatabase > mydbbackup.sql
$ mysqldump --user=myuser --password \
--complete-insert --skip-lock-tables --skip-extended-insert \
mydatabase mytable > mybackup.sql
mysqldump --user=myuser --password \
--complete-insert --compatible=ansi --skip-extended-insert --skip-quote-names \
mydatabase mytable -w "mycolumn in ('IC','GG','GJ','IM')" > mybackup.sql
$ mysqldump --user=myuser --password \
--skip-extended-insert --skip-quote-names --complete-insert --compatible=ansi \
$ mysqldump -h myhost -u myuser -p mydbname \
--skip-add-locks --skip-quote-names --compatible=ansi --skip-extended-insert > mydb.sql
$ mysqldump -h myhost -u myuser -p --no-data --skip-lock-tables --compact --tab=mydir mydb
$ mysqldump -h myhost -u myuser -p --skip-lock-tables --compact --xml mydb mytable

Revision [749]

Edited on 2008-10-02 11:05:58 by FsseInfo

Additions:

$(echo "use mydbname ; show tables like 'mytblprefix%'" | mysql --skip-column-names) > mybackup.sql

Deletions:

~~$(echo "use mydb ; show tables like 'mytblprefix%'" | mysql --skip-column-names) > mybackup.sql~~

Revision [746]

Edited on 2008-10-02 10:58:00 by FsseInfo

Additions:

MySqlDump lets you backup or dump your database(s)
$ mysqldump --host mydbhost --user=myuser --password --all-databases > mybackup.sql
$ mysqldump -h mydbip -u myuser -p --add-drop-table mydatabase > mydbbackup.sql
--complete-insert --skip-lock-tables --skip-extended-insert \
mydatabase mytable > mybackup.sql
mysqldump --user=myuser --password \
--complete-insert --compatible=ansi --skip-extended-insert --skip-quote-names \
mydatabase mytable -w "mycolumn in ('IC','GG','GJ','IM')" > mybackup.sql
==Backup multiple tables (with wildcards !)==
--skip-extended-insert --skip-quote-names --complete-insert --compatible=ansi \
mydbname
$(echo "use mydb ; show tables like 'mytblprefix%'" | mysql --skip-column-names) > mybackup.sql
$ mysqldump -h myhost -u myuser -p \
--skip-add-locks --skip-quote-names --no-data \
mydbname > mydb.sql
Also see
MySqlRestore

Deletions:

mysqldump lets you backup or dump your database
$ mysqldump --host mydbip --user=myuser --password --all-databases > mydbbackup.sql
$ mysqldump -h mydbip -u myuser -p --add-drop-table mydb > mydbbackup.sql
--complete-insert --skip-lock-tables --skip-extended-insert mydb mytable > mytablebackup.sql
mysqldump \
-c --skip-extended-insert --skip-quote-names \
mydb mytable -w "mycolumn in ('IC','GG','GJ','IM')" > mytable.sql
$ mysqldump -h myhost -u myuser -p mydbname \
--skip-add-locks --skip-quote-names --no-data > mydb.sql
==Restore==
$ mysql -u username -p mydb < mydbbackup.sql
or
$ mysql --user=username --password mydb < mydbbackup.sql

Revision [658]

Edited on 2008-07-15 17:37:08 by FsseInfo

Additions:

==Backup a single table (with where clause)==
mysqldump \
-c --skip-extended-insert --skip-quote-names \
mydb mytable -w "mycolumn in ('IC','GG','GJ','IM')" > mytable.sql

Revision [528]

Edited on 2008-05-08 09:19:22 by FsseInfo

Additions:

mysqldump lets you backup or dump your database
Password:
Password:
$ mysqldump --user=myuser --password \
--complete-insert --skip-lock-tables --skip-extended-insert mydb mytable > mytablebackup.sql
Password:
==Backup database into a single file==
structure only
$ mysqldump -h myhost -u myuser -p mydbname \
--skip-add-locks --skip-quote-names --no-data > mydb.sql
Password:
structure with data
$ mysqldump -h myhost -u myuser -p mydbname \
--skip-add-locks --skip-quote-names --compatible=ansi --skip-extended-insert > mydb.sql
Password:
Password:
Password:
Password:
Password:

Deletions:

$ mysqldump --complete-insert --skip-lock-tables --skip-extended-insert --user=myuser --password mydb mytable > mytablebackup.sql
==Backup structure into a single file==
$ mysqldump -h myhost -u myuser -p mydbname --no-data --skip-quote-names > mydbnamestruct.sql
$ mysqldump -h myhost -u myuser -p mydbname --skip-add-locks --compatible=ansi --skip-extended-insert fsseinf_wikkawiki > fsse.sql

Revision [500]

Edited on 2008-05-04 17:27:45 by FsseInfo

Additions:

$ mysqldump -h myhost -u myuser -p mydbname --no-data --skip-quote-names > mydbnamestruct.sql
$ mysqldump -h myhost -u myuser -p mydbname --skip-add-locks --compatible=ansi --skip-extended-insert fsseinf_wikkawiki > fsse.sql

Deletions:

~~$ mysqldump -h myhost -u myuser -p mydbname --no-data --skip-quote-names mydb > mydbnamestruct.sql~~

Revision [405]

Edited on 2007-08-14 12:26:09 by FsseInfo [added skip-extended-insert]

Additions:

$ mysqldump --host mydbip --user=myuser --password --all-databases > mydbbackup.sql
$ mysqldump -h mydbip -u myuser -p --add-drop-table mydb > mydbbackup.sql
==Backup a single table==
$ mysqldump --complete-insert --skip-lock-tables --skip-extended-insert --user=myuser --password mydb mytable > mytablebackup.sql

Deletions:

$ mysqldump -u myuser -p --add-drop-table mydb > mydbbackup.sql
$ mysqldump --user=myuser --password --all-databases > mydbbackup.sql

Revision [378]

Edited on 2007-05-03 10:23:22 by FsseInfo [added skip-extended-insert]

Additions:

==Backup table into XML==
Backup table to XML
$ mysqldump -h myhost -u myuser -p --skip-lock-tables --compact --xml mydb mytable

Revision [377]

Edited on 2007-05-03 10:21:55 by FsseInfo [added skip-extended-insert]

Additions:

$ mysqldump -h myhost -u myuser -p --no-data --skip-lock-tables --compact --tab=mydir mydb

Deletions:

~~$ mysqldump -h myhost -u myuser -p --no-data --skip-lock-tables --compact -T mydir mydb~~

Revision [376]

Edited on 2007-05-03 09:32:54 by FsseInfo [added skip-extended-insert]

Additions:

$ mysqldump -u myuser -p --add-drop-table mydb > mydbbackup.sql
$ mysqldump --user=myuser --password --all-databases > mydbbackup.sql
==Backup structure into a single file==
$ mysqldump -h myhost -u myuser -p mydbname --no-data --skip-quote-names mydb > mydbnamestruct.sql
==Backup structure into multiple files==
Backup structure of all tables to separate .sql files ready for adding to subversion
$ mysqldump -h myhost -u myuser -p --no-data --skip-lock-tables --compact -T mydir mydb
$ mysql -u username -p mydb < mydbbackup.sql
$ mysql --user=username --password mydb < mydbbackup.sql

Deletions:

$ mysqldump -u mysqluser -p --add-drop-table mydbname > mydbbackup.sql
$ mysqldump --user=mysqluser --password --all-databases > mydbbackup.sql
==Backup structure only==
$ mysqldump -u mysqluser -p mydbname --no-data --skip-quote-names mydbname > mydbnamestruct.sql
$ mysql -u username -p mydbname < mydbbackup.sql
$ mysql --user=username --password mydbname < mydbbackup.sql

Revision [260]

The oldest known version of this page was created on 2006-11-24 12:08:03 by FsseInfo [added skip-extended-insert]