Category Archives: MySQL

MySQL automatically set created and lastupdated timestamps on columns

ALTER TABLE `tablename` ADD COLUMN `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html https://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html Requires MySQL 5.6.5 or better

Transfering MySQL database between hosts

The single command approach mysqldump -h oldhost -u oldusername -poldpassword olddbname | mysql -h newhost -u newusername -pnewpassword newdbname make sure you can … mysql -h newhost -u newusername -pnewpassword newdbname … before attempting the transfer. Common issues are :- Firewall preventing access to MySQL server across the network User configured to only be able… Read More »

my MySQL default configuration

my.cnf file. Try and use this before you start MySQL Server for the first time! [mysqld] log-output=TABLE expire_logs_days=1 general_log=ON general_log_file=/var/db/mysql/mysqld-queries.log slow_query_log=ON slow_query_log_file=/var/db/mysql/mysqld-slow-queries.log log_queries_not_using_indexes=ON log-error=/var/db/mysql/mysqld-error.log default-storage-engine=innodb innodb_file_per_table Run the following SQL script to update the logs to use MyISAM (instead of CSV!). SET @old_log_state = @@global.general_log; SET GLOBAL general_log = ‘OFF’; TRUNCATE TABLE general_log; ALTER TABLE… Read More »