=== Basic Dump ===
__**NOTE:**__ Before taking a dump, check the table engines!!!
\\
If table engines are MyISAM, a write AND read lock will be placed on the tables, which means there will be "interruption" of service involved.
\\
Change 'databasename' to the name of the database you are looking to dump
<sxh bash>
select engine,count(*),sum(index_length+data_length)/1024/1024 from information_schema.tables where table_schema = 'databasename' group by engine;
</sxh>
\\
\\
You can then perform a dump with:
<sxh bash>mysqldump -u root -p database > filename.sql</sxh>
\\
**Recommended**: 
\\
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>
\\
Note: It may be benefitial to run the mysql dump in a screen session depending on how large the database is.
\\
To view database size please visit: https://lukeslinux.co.uk/mysql_database_sizes
------------------------------------------------------------------------
=== Importing ===
Once you have created the db you can import with:

<sxh bash>mysql -u root -p mytestdb2 < mytestdb.sql</sxh>

\\
Using PV to import - Progress report
<sxh bash>
pv database_file.sql | mysql database_name
</sxh>
------------------------------------------------------------------------
\\
\\
=== Advanced mysql dump ===
=== 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>
\\
-----