databaseCheatSheet.md

#Database Cheat Sheet

For MySQL 5.5

• Change user password in MySQL

  UPDATE mysql.user SET Password=PASSWORD('<new_password>') WHERE User='<user_name>';
  FLUSH PRIVELAGES;
  

• Move database from one machine to the other

  • On the first server (include stored procedures and events):
    mysqldump firstdatabase -u user -p --routines --events > dump.sql
scp dump.sql user@secondserver:/home/user
  • On the second server:
    mysql seconddatabase -u user -p < dump.sql

• List all stored procedures
  SHOW PROCEDURE STATUS;

• Display code for a certain procedure
  SHOW CREATE PROCEDURE <your_procedure>;

• Destroy procedure
  DUMP PROCEDURE IF EXISTS <your_procedure>;

• Select unique elements
  SELECT DISTINCT <column_name> FROM <table_name>;

• Delete all data from a table

  • Slow way:
    DELETE FROM <table_name>;
  • Fast way (drops and recreates table):
    TRUNCATE TABLE <table_name>;