It is simple but confusing nonetheless. Official documentation does not help much, as both definitions looks almost the same. But there is a difference, which is important if your code handles time zones. With TIMESTAMP MySQL converts the value to UTC timezone to store it in database. Consequently, to SELECT the data MySQL runs a…
All posts tagged mysql
MySQL Health Check release
We are pleased to announce our MySQL Health Check service. It’s free and takes about a minute to complete, you just need to submit MySQL server counters. How it works? You submit your MySQL server counters from SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES and get the report. We analyze a lot of DB metrics…
Google Cloud SQL MySQL: how to convert JSON to slowlog
Nowadays data volumes are growing very quickly. Today Hundreds of gigabytes database is not even being considered as a huge one. Naturally cloud services like Amazon Relational Database Service (RDS) or Google Cloud SQL services can be successfully used for such cases. This is very handy, you don’t even need to spend your time tuning…
MySQL: how to fix ‘ERROR 2006 (HY000) at line ##: MySQL server has gone away’
If you get mentioned error while restoring big database, make sure you have enough size of max_allowed_packet. [mysqld] max_allowed_packet=64M After adding it to your /etc/my.cnf restart MySQL server to apply changes.
MySQL: ERROR 1005 (HY000) at line 14: Can’t create table ‘example.tbl’ (errno: 150)
If you get an error ERROR 1005 (HY000) at line 14: Can’t create table example.tbl (errno: 150) it could be for at least two reasons. MySQL doesn’t allow to create foreign keys for a set of tables one of which doesn’t exist. To solve this restriction you can set SET foreign_key_checks to 0: mysql> SET…
phpMyAdmin: how to use it with several MySQL servers
If you have several MySQL servers it makes sense to use one phpMyAmin to manage them. To do it just add as many server as you need to config.inc.php. In Fedora and Centos it is located in /etc/phpMyAdmin/. Here’s an example: <?php /* Servers configuration */ $i = 0; /* Server: localhost [1] */ $i++;…
MySQL: chained replication M1>S1>S2
In most case simple master-slave replication provides enough level of high-avalability (about 99.9%), read ops balancing and allows non-blocking backups (if you use MyISAM tables since InnoDB tables can be backed up without blocking). But sometimes you may want to setup chained replication with two slaves: first slave replicates from the master, and the second…
Dumping MySQL database from slave for replication set up
Usually to set up MySQL replication it’s convenient to dump database from current master with –master-data parameter. With this option mysqldump includes commented SQL operator which needs to be issued on slave to set up replication: CHANGE MASTER TO MASTER_LOG_FILE=’mysqld-bin.000008′, MASTER_LOG_POS=687808977; But sometime it’s not very good idea to dump database from master server. For…
MySQL: checking replication with mk-table-checksum
Here is a simple way to check if your database on master and slave server have the same data. We will use mk-table-checksum from maatkit tools for this purpose. Note! Never do it on the production server! Or use it on your own risk! As stated: mk-table-checksum executes queries that cause the MySQL server to…
Three ways to make MySQL database dump
Everybody knows – backups are very important. Today a lot of web projects use MySQL to keep data. So you need to know how to set up reliable but simple backup of all your databases. And even if you are only developing you might probably need some tools to make quick dumps and to restore…