Differences

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

--- mysql_mysqldump_database [2015/12/14 12:19] – luke7858
+++ mysql_mysqldump_database [2024/05/23 07:26] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-=== Basic mysql dump ===
 === 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>
@@ Line 10: / Line 20: @@
 <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 ===
@@ Line 17: / Line 29: @@
 <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 ===