clevertonh · June 22, 2022 14:01 · May 16, 2013 · May 16, 2013 · May 16, 2013 · May 16, 2013
diff --git a/mysql.sh b/mysql.sh
@@ -46,7 +46,7 @@ mysqldump -uuser -ppassword SourceDatabase > file.sql
 set -e
 set -u
 
-USER="root"
+USER="REDACTED"
 PWD="REDACTED"
 DB="REDACTED"
 

diff --git a/mysql.sh b/mysql.sh
@@ -21,6 +21,7 @@ FROM    information_schema.tables;
 
 ## tuning
 # tips: http://www.debianhelp.co.uk/mysqlperformance.htm
+# mysql tuning primer script: http://www.day32.com/MySQL/
 
 # check the status of the running server (used key buffers, ...), for tuning
 mysqlreport --user=$USER --password=$PASSWORD | less

diff --git a/mysql.sh b/mysql.sh
@@ -20,6 +20,8 @@ SELECT  table_schema                                        "DB Name",
 FROM    information_schema.tables;
 
 ## tuning
+# tips: http://www.debianhelp.co.uk/mysqlperformance.htm
+
 # check the status of the running server (used key buffers, ...), for tuning
 mysqlreport --user=$USER --password=$PASSWORD | less
 

diff --git a/mysql.sh b/mysql.sh
@@ -19,9 +19,13 @@ SELECT  table_schema                                        "DB Name",
         Round( (data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
 FROM    information_schema.tables;
 
-## check the status of the running server (used key buffers, ...), for tuning
+## tuning
+# check the status of the running server (used key buffers, ...), for tuning
 mysqlreport --user=$USER --password=$PASSWORD | less
 
+# check current values
+mysqladmin --user=$USER --password=$PWD variables | less
+
 ## mysql big deletes
 # source: http://mysql.rjweb.org/doc.php/deletebig
 # use paritions or delete in chunks...
@@ -77,8 +81,4 @@ mysqlcheck -o <db_name> -u<username> -p
 
 ## dumping an entire database!
 # source: http://dba.stackexchange.com/questions/20/how-can-i-optimize-a-mysqldump-of-a-large-database
-mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz
-
-## tuning
-# check current values
-mysqladmin --user=$USER --password=$PWD variables | less
+mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz
diff --git a/mysql.sh b/mysql.sh
@@ -36,6 +36,9 @@ mysqldump -uuser -ppassword SourceDatabase > file.sql
 # export per table (and optimize+analyze)
 #!/bin/sh
 
+set -e
+set -u
+
 USER="root"
 PWD="REDACTED"
 DB="REDACTED"

diff --git a/mysql.sh b/mysql.sh
@@ -19,6 +19,9 @@ SELECT  table_schema                                        "DB Name",
         Round( (data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
 FROM    information_schema.tables;
 
+## check the status of the running server (used key buffers, ...), for tuning
+mysqlreport --user=$USER --password=$PASSWORD | less
+
 ## mysql big deletes
 # source: http://mysql.rjweb.org/doc.php/deletebig
 # use paritions or delete in chunks...

diff --git a/mysql.sh b/mysql.sh
@@ -30,15 +30,13 @@ FROM    information_schema.tables;
 # export
 mysqldump -uuser -ppassword SourceDatabase > file.sql
 
-# export per table
+# export per table (and optimize+analyze)
 #!/bin/sh
 
 USER="root"
 PWD="REDACTED"
 DB="REDACTED"
 
-# export
-
 TABLES=$(mysql --user=$USER --password=$PWD -Bse 'show tables' $DB | grep -v 'log')
 for TABLE in $TABLES
 do
@@ -54,7 +52,6 @@ mysqlcheck --user=$USER --password=$PWD -o $DB
 echo "analyzing all tables"
 mysqlcheck --user=$USER --password=$PWD -a $DB
 
-
 # import
 mysql -u USER -p DATABASE < file.sql
 
@@ -74,4 +71,8 @@ mysqlcheck -o <db_name> -u<username> -p
 
 ## dumping an entire database!
 # source: http://dba.stackexchange.com/questions/20/how-can-i-optimize-a-mysqldump-of-a-large-database
-mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz
+mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz
+
+## tuning
+# check current values
+mysqladmin --user=$USER --password=$PWD variables | less
diff --git a/mysql.sh b/mysql.sh
@@ -31,12 +31,30 @@ FROM    information_schema.tables;
 mysqldump -uuser -ppassword SourceDatabase > file.sql
 
 # export per table
+#!/bin/sh
+
+USER="root"
+PWD="REDACTED"
+DB="REDACTED"
+
+# export
+
 TABLES=$(mysql --user=$USER --password=$PWD -Bse 'show tables' $DB | grep -v 'log')
 for TABLE in $TABLES
 do
-    mysqldump --user=$USER --password=$PWD $DB $TABLE > dump-$TABLE.sql
+    echo "dumping $TABLE"
+    mysqldump -u $USER -p$PWD $DB $TABLE > dump-$TABLE.sql
 done
 
+# optimize
+echo "optimizing all tables"
+mysqlcheck --user=$USER --password=$PWD -o $DB
+
+# analyze
+echo "analyzing all tables"
+mysqlcheck --user=$USER --password=$PWD -a $DB
+
+
 # import
 mysql -u USER -p DATABASE < file.sql
 

diff --git a/mysql.sh b/mysql.sh
@@ -34,7 +34,7 @@ mysqldump -uuser -ppassword SourceDatabase > file.sql
 TABLES=$(mysql --user=$USER --password=$PWD -Bse 'show tables' $DB | grep -v 'log')
 for TABLE in $TABLES
 do
-    mysqldump -u $USER -p$PWD $DB $TABLE > dump-$TABLE.sql
+    mysqldump --user=$USER --password=$PWD $DB $TABLE > dump-$TABLE.sql
 done
 
 # import

diff --git a/mysql.sh b/mysql.sh
@@ -31,7 +31,7 @@ FROM    information_schema.tables;
 mysqldump -uuser -ppassword SourceDatabase > file.sql
 
 # export per table
-TABLES=$(mysql --user=$USER --password=$PWD -Bse 'show tables' $DB | grep -v 'lol')
+TABLES=$(mysql --user=$USER --password=$PWD -Bse 'show tables' $DB | grep -v 'log')
 for TABLE in $TABLES
 do
     mysqldump -u $USER -p$PWD $DB $TABLE > dump-$TABLE.sql

diff --git a/mysql.sh b/mysql.sh
@@ -30,6 +30,13 @@ FROM    information_schema.tables;
 # export
 mysqldump -uuser -ppassword SourceDatabase > file.sql
 
+# export per table
+TABLES=$(mysql --user=$USER --password=$PWD -Bse 'show tables' $DB | grep -v 'lol')
+for TABLE in $TABLES
+do
+    mysqldump -u $USER -p$PWD $DB $TABLE > dump-$TABLE.sql
+done
+
 # import
 mysql -u USER -p DATABASE < file.sql
 

diff --git a/mysql.sh b/mysql.sh
@@ -47,4 +47,6 @@ mysqlcheck -o <db_name> -u<username> -p
 
 ## analyze tables
 
-# test
+## dumping an entire database!
+# source: http://dba.stackexchange.com/questions/20/how-can-i-optimize-a-mysqldump-of-a-large-database
+mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz
diff --git a/mysql.sh b/mysql.sh
@@ -6,6 +6,7 @@ set -u
 # I'm just getting acquainted with mysql so cut me some slack ;)
 
 ## check the size of the db
+# source: http://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database 
 # per schema
 SELECT  table_schema                                        "DB Name", 
         Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
@@ -30,7 +31,7 @@ FROM    information_schema.tables;
 mysqldump -uuser -ppassword SourceDatabase > file.sql
 
 # import
-mysql -u USER -p DATABASE < FILE.SQL
+mysql -u USER -p DATABASE < file.sql
 
 ## check if tables are myisam or innodb (all the ones I inherited seem to be myisam, drat)
 SHOW TABLE STATUS FROM `database`;

diff --git a/mysql.sh b/mysql.sh
@@ -5,6 +5,19 @@ set -u
 
 # I'm just getting acquainted with mysql so cut me some slack ;)
 
+## check the size of the db
+# per schema
+SELECT  table_schema                                        "DB Name", 
+        Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
+FROM    information_schema.tables 
+GROUP   BY table_schema; 
+
+# per table
+SELECT  table_schema                                        "DB Name", 
+        table_name,
+        Round( (data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
+FROM    information_schema.tables;
+
 ## mysql big deletes
 # source: http://mysql.rjweb.org/doc.php/deletebig
 # use paritions or delete in chunks...

diff --git a/mysql.sh b/mysql.sh
@@ -31,4 +31,6 @@ mysqlcheck -o <db_schema_name>
 mysqlcheck -o --all-databases
 mysqlcheck -o <db_name> -u<username> -p
 
-## analyze tables
+## analyze tables
+
+# test
diff --git a/mysql.sh b/mysql.sh
@@ -5,17 +5,9 @@ set -u
 
 # I'm just getting acquainted with mysql so cut me some slack ;)
 
-# check if tables are myisam or innodb (all the ones I inherited seem to be myisam, drat)
-SHOW TABLE STATUS FROM `database`;
-
-## optimize tables
-# source: http://stackoverflow.com/questions/5474662/mysql-optimize-all-tables
-# source2: http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/
-mysqlcheck -o <db_schema_name>
-
-# -or- (for all databases
-mysqlcheck -o --all-databases
-mysqlcheck -o <db_name> -u<username> -p
+## mysql big deletes
+# source: http://mysql.rjweb.org/doc.php/deletebig
+# use paritions or delete in chunks...
 
 ## dump data and import again
 # sourcE: http://stackoverflow.com/questions/5475200/mysql-restoring-a-database-via-mysqldump-does-it-overwrite-the-different-desti?rq=1
@@ -27,8 +19,16 @@ mysqldump -uuser -ppassword SourceDatabase > file.sql
 # import
 mysql -u USER -p DATABASE < FILE.SQL
 
-## mysql big deletes
-# source: http://mysql.rjweb.org/doc.php/deletebig
-# use paritions or delete in chunks...
+## check if tables are myisam or innodb (all the ones I inherited seem to be myisam, drat)
+SHOW TABLE STATUS FROM `database`;
+
+## optimize tables
+# source: http://stackoverflow.com/questions/5474662/mysql-optimize-all-tables
+# source2: http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/
+mysqlcheck -o <db_schema_name>
+
+# -or- (for all databases
+mysqlcheck -o --all-databases
+mysqlcheck -o <db_name> -u<username> -p
 
-# testing the sublime plugin
+## analyze tables
diff --git a/mysql.sh b/mysql.sh
@@ -29,4 +29,6 @@ mysql -u USER -p DATABASE < FILE.SQL
 
 ## mysql big deletes
 # source: http://mysql.rjweb.org/doc.php/deletebig
-# use paritions or delete in chunks...
+# use paritions or delete in chunks...
+
+# testing the sublime plugin
diff --git a/mysql.sh b/mysql.sh
@@ -5,6 +5,9 @@ set -u
 
 # I'm just getting acquainted with mysql so cut me some slack ;)
 
+# check if tables are myisam or innodb (all the ones I inherited seem to be myisam, drat)
+SHOW TABLE STATUS FROM `database`;
+
 ## optimize tables
 # source: http://stackoverflow.com/questions/5474662/mysql-optimize-all-tables
 # source2: http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/

diff --git a/mysql.sh b/mysql.sh
@@ -15,9 +15,15 @@ mysqlcheck -o --all-databases
 mysqlcheck -o <db_name> -u<username> -p
 
 ## dump data and import again
+# sourcE: http://stackoverflow.com/questions/5475200/mysql-restoring-a-database-via-mysqldump-does-it-overwrite-the-different-desti?rq=1
+# handy options: add-drop-table and add-drop-database
 
 # export
 mysqldump -uuser -ppassword SourceDatabase > file.sql
 
 # import
-mysql -u USER -p DATABASE < FILE.SQL
+mysql -u USER -p DATABASE < FILE.SQL
+
+## mysql big deletes
+# source: http://mysql.rjweb.org/doc.php/deletebig
+# use paritions or delete in chunks...
diff --git a/mysql.sh b/mysql.sh
@@ -0,0 +1,23 @@
+#!/bin/sh
+
+set -e
+set -u
+
+# I'm just getting acquainted with mysql so cut me some slack ;)
+
+## optimize tables
+# source: http://stackoverflow.com/questions/5474662/mysql-optimize-all-tables
+# source2: http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/
+mysqlcheck -o <db_schema_name>
+
+# -or- (for all databases
+mysqlcheck -o --all-databases
+mysqlcheck -o <db_name> -u<username> -p
+
+## dump data and import again
+
+# export
+mysqldump -uuser -ppassword SourceDatabase > file.sql
+
+# import
+mysql -u USER -p DATABASE < FILE.SQL