Differences

This shows you the differences between two versions of the page.

--- mysql_mysqldump [2015/10/27 17:05] – luke7858
+++ mysql_mysqldump [2024/05/23 07:26] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-=== Dumping and Importing database ===
+~~NOCACHE~~
-To take a mysql dump you should not be logged into mysql, you will just need to be in the terminal. Use the following command to export the db to the directory you are currently in:
+====== mysqldump ======
-\\
+{{tabembed>[[mysql_mysqldump_intro|Intro, [[mysql_mysqldump_database|Database Import/Export, [[mysql_mysqldump_table|Table Import/Export}}
-=== Viewing databases outside of mysql ===
-You can view your mysql databases without logging into mysql with the following command:
-<sxh bash>mysql -e "show databases"</sxh>
-\\
-=== Database Engine ===
-Check the db engines before you dump. This will allow you to find the most efficient way of dumping the tables
-\\
-Replace "database name" with the name of your database
-<sxh bash>
-mysql -e 'SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = "database name"
-</sxh>
-E.g Output:
-<sxh bash>
-+------------------------+--------+
-| TABLE_NAME             | ENGINE |
-+------------------------+--------+
-| wp_commentmeta         | MyISAM |
-| wp_comments            | MyISAM |
-| wp_links               | MyISAM |
-| wp_options             | MyISAM |
-| wp_popularpostsdata    | InnoDB |
-| wp_popularpostssummary | InnoDB |
-| wp_postmeta            | MyISAM |
-| wp_posts               | MyISAM |
-| wp_term_relationships  | MyISAM |
-| wp_term_taxonomy       | MyISAM |
-| wp_terms               | MyISAM |
-| wp_usermeta            | MyISAM |
-| wp_users               | MyISAM |
-+------------------------+--------+
-</sxh>
-\\
-\\
-==== Creating A DUMP ====
-You will need to dump the db based on the storage engine:
-=== INNODB ===
-source: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction
-<sxh bash>
-mysqldump -u user -p --single-transaction mydatabase > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql
-</sxh>
-Using **//__PV__//** to see progress:
-<sxh bash>
-mysqldump -u user -p --single-transaction mydatabase | pv > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql
-</sxh>
-=== myISAM ===
-source: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_lock-tables
-<sxh bash>
-mysqldump -u user -p --lock-tables mydatabase > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql
-</sxh>
-Using PV to see progress:
-<sxh bash>
-mysqldump -u user -p --lock-tables mydatabase | pv > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql
-</sxh>
-=== HYBRID ===
---lock-all-tables will provide a consistent backup for hybrid databases
-\\
-<sxh bash>
-mysqldump -u user -p --lock-tables mydatabase > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql
-</sxh>
-Using PV to see progress:
-<sxh bash>
-mysqldump -u user -p --lock-tables mydatabase | pv > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql
-</sxh>
-\\
-=== Basic Dump ===
-You can then perform a dump with:
-<sxh bash>mysqldump -u root -p database > filename.sql</sxh>
-You can also use the following to add date to the mysql dump:
-<sxh bash>mysqldump database > mysql_dump_name_backup.$(date +"%Y-%m-%d").sql</sxh>
-== Dumping with progress report ==
-<sxh bash>mysqldump database | pv > mysql_dump_name_backup.$(date +"%Y-%m-%d").sql</sxh>
-\\
-\\
-=== Importing a database ===
-Once you have taken the dump you can then import the db using the following commands.
-\\
-If you haven’t already prepared a blank database then you will need this command first:
-<sxh bash>mysqladmin -u root -p create databasename</sxh>
-\\
-Once you have created the db you can import with:
-<sxh bash>mysql -u root -p mytestdb2 < mytestdb.sql</sxh>