MySql

sql-the-things.sql

--  Set Root Password
    mysqladmin -u root password <YOURNEWPASSWORD>
    
--  Set / Change MySQL Users Passwords from the Linux Shell
    mysqladmin -u <username >-h <host> -p password <newpassword>

--  How To Connect to MySQL
    mysql -ur <username> -p
    mysql -h <hostname> -u <username> -p

--  Create Databse
    create databse my_database;

--  Backup Database using mysqldump
    mysqldump -u <username> -p <db-name>  >  /tmp/database-backup.sql

    --  with compression
        mysqldump -u <root> -p <db-name> | gzip -v > /tmp/database-backup.sql.gz

--  Dump a remote DB from server and send to local machine with gzip
    mysqldump -u <username> -p <db-name> | gzip -c | ssh <user@local-machine> `cat > /tmp/database-backup.sql.gz`

--  Dump all DBs on a server
    mysqldump -u <username> -p <password> --opt > /tmp/databases.sql

--  Dump and skip broken tables
    mysqldump -u <username> -p <db-name >--ignore-table=your-database.broken-table > your-database.sql
    --  skip multiple tables    
        mysqldump -u <username> -p <db-name> --ignore-table=your-database.broken-table --ignore-table=your-database.broken-table2 > your-database.sql

--  Dump a specific table from DB
    mysqldump -c -u <username> -p <password> <db-name> <table-name>  > /tmp/db-name.table-name.sql
    
-- Import DB 
    mysqldump -u <username> -p -h <host> <db-name>  < database-backup.sql
    --  from .sql.gz
        zcat database-backup.sql.gz | mysql -u <usernmae> -p <db-name>
    --  from sql command line
        > source ./db-backup.sql

--  Select DB
    use <db-name>;
--  Show Tables
    show tables;
--  Create User
    grant usage on *.* to <user@localhost> identified by 'username';
    --  grant permission 
        grant all privileges on my_db.* to <user@localhost>;
    --  grant permission to all the things
        grant all privileges on *.* to <user@localhost>;

--  Show DB size
    cd /var/lib/mysql && ls -lh
    --  from sql command line
        > SELECT table_schema "<db-name>", SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

--  List DBs
    show databases;

--  Delete DB
    drop database <db-name>

--  Drop Table
    drop table <table-name>

--  Reset MySQL root password
    --  Stop mysql
        /etc/init.d/mysqld stop
    --  Start mysql in safe mode
        mysqld_safe --skip-grant-tables &
    --  login as root
        mysql -u root
    --  set roo password
        use mysql;
        UPDATE user SET password=PASSWORD("<new-root-pwd") WHERE user='<root>';
        flush privileges;
        quit;
    --  restart mysql service 
        /etc/init.d/mysql restart

--  Create Table
    CREATE TABLE table-name (
        firstname VARCHAR(20), 
        datestamp DATE,
        timestamp time,
        pgpemail VARCHAR(255)
    );

    --  INNODB Table
        CREATE TABLE your_table_name_innodb (
            id INT,
            data VARCHAR(100)
        ) TYPE=innodb;
        
    --  Convert MyIsam to INNODB
        ALTER TABE ENGINE=INNODB;

--  Repair Broken Table in MySql
    repair table <broken_table_name>;

--  Show DB fields/format
    describe <table-name>

--  Select
    SELECT * FROM <table-name>

--  Show columns in table
    SHOW COLUMNS FROM <table-name>

--  Add a new column
    ALTER TABLE <table-name> ADD COLUMN <new-column> varchar (20)

--  Delete column
    ALTER TABLE <table-name> DROP column <column-name>

--  Delete row
    DELETE FROM <table-name> WHERE <field-name> = '<batman>'

--  Show row count
    SELECT COUNT(*) FROM <table-name>

--  Join tables
    SELECT <column_names> FROM <table-1>, <table-2> WHERE (table-1.column = table-2.column);

--  Sum column 
    SELECT SUM(*) FROM <table-name>
    
--  Show unique records
    SELECT DISTINCT <column-name> FROM <table-name>

--  Search using regex
    SELECT * FROM <table-name> WHERE <value> RLIKE "^z"

Good Work!