• Configuration
    • /etc/my.cnf
    • /etc/mysql/my.cnf
  • Standard Connection
    • mysql -h host -uusername -ppasswd
    • mysql -uusername -ppassword < filename - Importing
    • mysqldump -uusername -ppassword database [tables] > filename - Dumping
  • Remote Connection
    • mysqld - server process
    • --skip-networking - disables network connections
    • 3306 - default port
    • SSH Connection
      • involves a ssh host, which can be same as the mysql host
  • Basic
    • show databases
    • use database
    • show table
    • describe table
    • create database db1
    • drop database db1
    • history - history of commands
  • Grants & Privileges
    • show grants;
    • show privileges;
    • show processlist;
  • Query
    • select * from employees where first_name = "tom" ORDER BY field1 desc LIMIT 10
    • select * from employees where age > 25
    • select * from employees where first_name LIKE '%Robert%'
    • select * from employees where dob IS NULL
    • select * from employees where dob IS NOT NULL
    • select * from employees where first_name IN ("Tom", "Harry") - check from array
    • select * from employees where first_name NOT IN ("Tom", "Harry") - Inverse check from array
    • AND OR to join the conditions
    • `select * from employees join deparments on employees.department_id = department.id’
  • Dates
    • select * from memberships where expiration_date > '2015-04-24';
    • select * from memberships where expiration_date > Now();
    • select * from memberships where expiration_date BETWEEN Now() and '2015-05-24';
  • Design
    • Foreign Key - If a key in a table, refers to a primary key of another table, its called foreign key
    • Example: Address table(per)
  • Data Manipulation
    • UPDATE employees SET street_address="40 prince ct" where first_name="Harry" and last_name="Emerson"
    • delete vs truncate - truncate is faster
    • insert into tbl_name (col1, col2) VALUES(val1, val2);
  • Optimization
    • Check the slow_query.log
    • Use explain statement
  • Maintenance
    • mysqlcheck -h host -u username -ppassword tables tablename --auto-repair
  • User Management
    • Identify - username@hostname
    • create user 'newuser'@'host' identified by 'passwd';`
    • grant all privileges on * . * to 'newuser'@'host';
    • revoke all privileges on * . * to 'newuser' @ 'host';
    • flush privileges
    • Types of privileges - create drop delete insert select update grant option
  • Wild Cards
    • % can mean anything
    • LIKE keyword
    • _
  • Group By
    • Aggreate Functions - avg, count, max, min, sum
    • group by col1 col2 - clause
    • where name REGEXP 'new' - searches for the word new
    • . - any single character
    • new|old - or
  • Functions
    • Result of a regular function - is an entire column
    • Result of an aggregate function - is a single entity
    • UPPER()
    • SQRT()
  • Table & Server Administration
    • flush to clear temporary caches
    • Options -
    • DES_KEY_FILE - DES encryption file
    • HOSTS - hosts cache
    • LOGS - close all logfiles and reopens them
    • PRIVILEGES - reloads grant table
    • QUERY_CACHE -
    • STATUS - resets status variables
    • TABLE tablename -
    • TABLES
    • TABLES WITH READ LOCK
    • USER_RESOURCES
    • Multiple options can be given in comma separated list
  • Storage Engines
    • Innodb
    • MyISAM
  • Table Status
    • show table status like 'pattern';
  • DB & Table Size

    • select table_name as "Table", round(((data_length+index_length)/1024/1024/1000),2) as size_in_gb from information_schema.tables where table_schema = '$db_name' order by size_in_gb desc; - Size of tables in descending order

    • select table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" from information_schema.tables group by table_schema ; - Size of the database

  • Indexes
    • show indexes from table_name;
  • Delete Rows
    • Time required to delete individual rows is proportional to the number of indexes on the table
    • Increasing the size of key_buffer_size makes it faster
  • Resources
    • http://mysqlresources.com