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
mysqldump-script-for-windows mysqldump.exe –defaults-file=mysqldump.cnf –all-databases -h 192.168.1.22 > backup-%date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%%time:~3,2%%time:~6,2%.sql Produces filename like backup-2015-08-27-160938.sql
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.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 »
CREATE DEFINER=`root`@`localhost` FUNCTION `FIND_DISTANCE`(`src_latitude` float,`src_longitude` float, `dest_latitude` float,`dest_longitude` float) RETURNS int(11) BEGIN /* This function takes four parameterss, two sets of latitude and latitude pairs and returns the distance between them in miles. Example use SELECT * , FIND_DISTANCE(home_latitude, home_longitude, 52.5, -1) AS distance FROM users WHERE deleted = 0; */ RETURN ( 3959 *… Read More »
[mysqld] … log-output=TABLE expire_logs_days=1 general_log=1 general_log_file=/var/log/mysqld-queries.log slow_query_log=1 slow_query_log_file=/var/log/mysqld-slow-queries.log … SET @old_log_state = @@global.general_log; SET GLOBAL general_log = ‘OFF’; ALTER TABLE mysql.general_log ENGINE = MyISAM; SET GLOBAL general_log = @old_log_state; SET GLOBAL general_log = ‘ON’; SET @old_log_state = @@global.slow_query_log; SET GLOBAL slow_query_log = ‘OFF’; ALTER TABLE mysql.slow_log ENGINE = MyISAM; SET GLOBAL slow_query_log = @old_log_state;… Read More »
DROP FUNCTION IF EXISTS `degree2decimal`; CREATE DEFINER = `root`@`localhost` FUNCTION `degree2decimal`(deg_coord float) RETURNS float BEGIN #converts NMEA/GPS Lat -Long to Decimal Degrees. Code comments are self explanatory. #The code is not graceful, but it works. declare degree INT; declare minutes2 FLOAT; declare dotdegree FLOAT; declare decimal2 FLOAT; set degree= (deg_coord/100); set minutes2 = deg_coord-(degree*100); set… Read More »