A Few Useful MySQL Commands / Queries for Amateur DBAs – Part 1

3. Backup and Restore MySQL Database from Command Prompt

To Backup: /usr/local/mysql/bin/mysqldump -uroot -prootpwd db_mydb > db_mydb_bkup.sql

To Restore: /usr/local/mysql/bin/mysql -uroot -prootpwd db_mydb_new < db_mydb_bkup.sql;

Where Database that is being backed up is “db_mydb”, Database to which the back is restored is “db_mydb_new”

4. Automatic Initialization and Updating for TIMESTAMP

As per MySQL Documentation:

The TIMESTAMP data type offers automatic initialization and updating to the current date and time (that is, the current timestamp). You can choose whether to use these properties and which column should have them:

• One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

•     If the column is auto-initialized, it is set to the current timestamp for inserted rows that specify no value for the column.

•     If the column is auto-updated, it is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. The column remains unchanged if all other columns are set to their current values. To prevent the column from updating when other columns change, explicitly set it to its current value. To update the column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

By default when we create a TIMESTAMP type field and auto initialize it with CURRENT_TIMESTAMP, every new row inserted has the CURRENT_TIMESTAMP as default value. The Query I’m going to share is to set a TIMESTAMP type to auto initialize and auto update.

As I could not figure out how to do this using a GUI Tool like MySQL Query Browser, I make it happen executing the following 2 step method:

Step 1: Create and save the TIMESTAMP type field (e.g., field name “my_time”) with default value set to CURRENT_TIMESTAMP using the Table Editor of MySQL Query Browser

Step 2: Execute the following Query to set the auto initialize property :

ALTER TABLE tbl_my_table CHANGE `my_time` `my_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;