mysql -u root -p
mysqldump --add-drop-table -h localhost -u dbuser -p magento_database > magento_database.sql
##then the fix
mysqlcheck --all-databases --check-upgrade --auto-repair
mysql < fix_priv_tables
SHOw variables like '%connect%'
SHOW INDEX FROM mydb.mytable;
mysqldump -u${DB_USERNAME} \
-p${DB_USERPASS} \
-h${DB_HOST} \
--complete-insert \
--skip-extended-insert \
--databases ${DB_NAME} > ${STORE_DIR}/${FILE_NAME}
DB Size in MB:
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;
Drop tables in the DB:
#!/bin/bashMUSER="$1"MPASS="$2"MDB="$3" # Detect pathsMYSQL=$(which mysql)AWK=$(which awk)GREP=$(which grep) if [ $# -ne 3 ]then echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}" echo "Drops all tables from a MySQL" exit 1fi TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' ) for t in $TABLESdo echo "Deleting $t table from $MDB database..." $MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"done
With Host:
#!/bin/bash MUSER="$1" MPASS="$2" MDB="$3" HOST="$4" # Detect paths MYSQL=$(which mysql) AWK=$(which awk) GREP=$(which grep) if [ $# -ne 4 ] then echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} {MySQL-Database-Host}" echo "Drops all tables from a MySQL" exit 1 fi TABLES=$($MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' ) for t in $TABLES do echo "Deleting $t table from $MDB database..." $MYSQL -h $HOST -u $MUSER -p$MPASS $MDB -e "drop table $t" done
From SQL:
mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD -BNe "show tables" YOUR_DBSCHEMA_NAME | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD YOUR_DBSCHEMA_NAME
SET FOREIGN_KEY_CHECKS=0; SET @tables = NULL; SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables WHERE table_schema = 'database-name-here'; -- specify DB name here. SET @tables = CONCAT('DROP TABLE ', @tables); PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS=1;
select CONCAT('DROP TABLE ', (SELECT GROUP_CONCAT(table_schema, '.', table_name)
FROM information_schema.tables
WHERE table_schema = 'DBNAME'));
table examples:
CREATE TABLE ESR_IST (Issue CHAR(30) NOT NULL PRIMARY KEY, Description VARCHAR(512), Start VARCHAR(512), StartDuration VARCHAR(512), Stop VARCHAR(512), StopDuration VARCHAR(512), Duration VARCHAR(512), StatusCondition VARCHAR(30));
insert into ESR_IST (Issue, Description, Start, StartDuration, Stop, StopDuration, Duration, StatusCondition) values ('ESR-739', 'Net Receipts Report', '12/Jul/16 3:54 PM', '24 seconds', '15/Jul/16 9:21 PM', '3 days and 5 hours and 26 minutes and 6 seconds', '3 days 5 hours 27 minutes', 'CLOSED'), ('ESR-994', 'Statement Details - Default Sort', '22/Mar/16 8:19 PM', '4 seconds', '23/Mar/16 8:56 AM', '2 minutes and 33 seconds', '12 hours 37 minutes', 'CLOSED');