MySQL Commands

Important MySQL Commands.

• How to check if multiple instance are running on the Server

sudo netstat -tap | grep mysql

• Kill mySql processors

pkill mysqld

• Start , Stop or Restart MySQL

/etc/init.d/mysql start|stop|restart|reload|force-reload|status

• Backup MySQL Database

mysqldump -u root -p DB > database_dump.txt

• Restore MySQL Database

mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

• Back up MySQL Database with Compress

mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

• Restore compressed backup files

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

• Restore a database that already exists

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

How to create a mysql procedure

DROP PROCEDURE IF EXISTS mydatabase.datespopulate;

DELIMITER |
CREATE PROCEDURE mydatabase.datespopulate(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
     INSERT INTO mydatabase.datetable (d) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;

DELIMITER ;
CALL mydatabase.datespopulate('2013-02-01','2015-06-30');

delete from mydatabase.datetable;