Skip to main content

MySQL

Login to MySQL​

  • mysql -u root -p -h localhost
  • mysql -uroot -hlocalhost -p{mypassword}

Show Users​

SELECT user,host FROM mysql.user;

Show Databases​

SHOW DATABASES;

Show Tables​

SHOW TABLES;

Count Rows In Database​

Standard, but slow method:

SELECT COUNT(SerialNumber) FROM TrademarkBasic;

Faster (maybe best) method:

SHOW INDEX FROM TrademarkBasic;

Alternative method:

SHOW TABLE STATUS LIKE 'TrademarkBasic';

Delete a Table​

TRUNCATE `TrademarkBasic_test`.`TrademarkBasic`;

Create a Database/Table (Example)​

CREATE DATABASE Dogs;
USE Dogs;
CREATE TABLE DogNames (Id INT, Name text);
INSERT INTO DogNames VALUES (1, "Koda");
SELECT * FROM DogNames;

Load CSV File (Example)​

LOAD DATA LOCAL INFILE '/tmp/TrademarkBasic.export.csv' INTO TABLE TrademarkBasic
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(SerialNumber, RegistrationNumber, Mark, IsActive, PrimaryCode, USCodes, IntlCodes, AttorneyName, @CreatedDate, @LastModifiedDate)
SET 
    CreatedDate = CURRENT_TIMESTAMP, 
    LastModifiedDate = CURRENT_TIMESTAMP;

Find Similar Name​

Select SerialNumber,Mark from TrademarkBasic WHERE (AttorneyName LIKE '%Hayoon%');

Clean Up "binlog" Files To Restore Disk Space​

  • mysql> PURGE BINARY LOGS BEFORE '2011-09-18 22:46:46';

Clean Up Disk Space Used By a Table​

  • mysql> OPTIMIZE TABLE TrademarkData_test.TrademarkArchive;

Check All Processes/Queries Running​

  • mysql> SHOW FULL PROCESSLIST;

Check Size of Tables​

  • In MySQL Workbench, right-click table > Table Inspector
    • Table size (estimate)

Check Size of All Tables​

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Copy Table From One Database To Another​

  • Use mysqldump to stream into the mysql
  • When you're finished, make sure to verify
    • Table structure is correct
    • Data is correct
    • Indices are correct
  • Template: 
    mysqldump -u{user} -p{password} {source-database} {source-table} | mysql -u{user} -p{password} -D{target-table}
  • Example: 
    mysqldump -uroot -p{password} TrademarkData_prod TrademarkBasic | mysql -uroot -p{password} -DTrademarkData_test

Compress/Uncompress A String​

SELECT CONVERT(UNCOMPRESS(COMPRESS('any string')) USING UTF8);

Create Table With Compressed Rows​

CREATE TABLE IF NOT EXISTS TrademarkXmlRawData (
	SerialNumber integer PRIMARY KEY,
	Xml BLOB,
	CreatedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	LastModifiedDate TIMESTAMP
)
ROW_FORMAT=COMPRESSED;

Update Table To Have Compressed Rows​

ALTER TABLE TrademarkArchive ROW_FORMAT=COMPRESSED;