Differences

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

--- mysql_mysqldump_database [2015/12/14 12:23] – luke7858
+++ mysql_mysqldump_database [2024/05/23 07:26] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 === 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**:
 \\
@@ Line 11: / 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 18: / Line 30: @@
 \\
+Using PV to import - Progress report
+<sxh bash>
+pv database_file.sql | mysql database_name
+</sxh>
 ------------------------------------------------------------------------
 \\