BreadCrumbs: Mysql
Mysql
From Luke Jackson
(Difference between revisions)
Revision as of 20:57, 8 February 2014 (edit) Ljackson (Talk | contribs) ← Previous diff |
Revision as of 15:08, 13 March 2014 (edit) Ljackson (Talk | contribs) (→Backup All Databases) Next diff → |
||
Line 1: | Line 1: | ||
+ | == Show Table(s) From Terminal Command Line == | ||
+ | |||
+ | All Tables in Database | ||
+ | |||
+ | <pre> | ||
+ | SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "io85" ORDER BY (data_length + index_length) DESC; | ||
+ | </pre> | ||
+ | |||
+ | Single Table | ||
+ | |||
+ | <pre> | ||
+ | SELECT table_name AS "Table", | ||
+ | round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" | ||
+ | FROM information_schema.TABLES | ||
+ | WHERE table_schema = "$DB_NAME" | ||
+ | AND table_name = "$TABLE_NAME"; | ||
+ | </pre> | ||
+ | |||
+ | |||
== Backup All Databases == | == Backup All Databases == | ||
Revision as of 15:08, 13 March 2014
Contents |
Show Table(s) From Terminal Command Line
All Tables in Database
SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "io85" ORDER BY (data_length + index_length) DESC;
Single Table
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME" AND table_name = "$TABLE_NAME";
Backup All Databases
mysqldump -n --user=XXXX --password=XXXX --all-databases | gzip -c > server.sql.gz
Dump Data and Structure
mysqldump -u root -p --skip-add-drop-table --replace --skip-extended-insert --lock-tables io85_com | sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' > io85_com_full.sql
Dump Data Only
mysqldump -u root -p --no-create-info --skip-add-drop-table --replace --skip-extended-insert --lock-tables io85_com > data_dump.sql
Dump Structure Only
mysqldump -u root -p --no-data --skip-add-drop-table --replace --skip-extended-insert --lock-tables io85_com | sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' > schema_dump.sql
Select DB for Import
mysql -u root -p --one-database db_to_restore < fulldump.sql
Extract Database from Dump File
sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql
Basic my.cnf
[mysqld] # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 skip_external_locking #skip-networking skip-name-resolve default-storage-engine=innodb
Ucwords for Mysql
DROP FUNCTION IF EXISTS proper; SET GLOBAL log_bin_trust_function_creators=TRUE; DELIMITER | CREATE FUNCTION proper( str VARCHAR(128) ) RETURNS VARCHAR(128) BEGIN DECLARE c CHAR(1); DECLARE s VARCHAR(128); DECLARE i INT DEFAULT 1; DECLARE bool INT DEFAULT 1; DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/'; SET s = LCASE( str ); WHILE i < LENGTH( str ) DO BEGIN SET c = SUBSTRING( s, i, 1 ); IF LOCATE( c, punct ) > 0 THEN SET bool = 1; ELSEIF bool=1 THEN BEGIN IF c >= 'a' AND c <= 'z' THEN BEGIN SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1)); SET bool = 0; END; ELSEIF c >= '0' AND c <= '9' THEN SET bool = 0; END IF; END; END IF; SET i = i+1; END; END WHILE; RETURN s; END; | DELIMITER ;