Differences

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

--- mysql_mysqldump_database [2015/12/14 12:12] – created 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**:
+\\
+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
@@ Line 63: / Line 102: @@
 \\
 -----
-=== 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 ===
-Once you have created the db you can import with:
-<sxh bash>mysql -u root -p mytestdb2 < mytestdb.sql</sxh>