Posts Tagged mysql
MySQL configuration file
Posted by Erik Bauffman in default on June 27, 2011
Place the configuration file ‘my.cnf’ in your home directory.
I no longer need to type ‘mysql -uroot -proot …’ to connect to my local databases. You can also set some other options such as the default character set which defaulted to latin1 in my case. I don’t like latin1, but I also don’t like typing this over and over when connection to a database.
Let mysqldump help you!
Posted by Erik Bauffman in default on June 17, 2011
I used to dump mysql data using PHPMyAdmin. People should get kicked in the nuts for doing that.
Dumping a (remote) database:
mysqldump -uroot -proot –host=mysql.openminds.be my_database > Desktop/dump.sql
Restoring a database:
mysql -uroot -proot my_database < Desktop/dump.sql
Disclaimer: I know that it’s stupid to set the password in the command, but this is just for the example. /cc @adeepersilence
MySQL command line import
Posted by Erik Bauffman in default on May 24, 2011
I always seem to forget the right command.
mysql -uroot -proot databaseName < /location/to/my/file.sql
MySQL connections in Zend Studio 8.0
Posted by Erik Bauffman in default on November 23, 2010
Recently I was having problems to get my unit tests to connect to my local MySQL server when ran within Zend Studio 8. After some reading on the internet it seems, that this issue is caused by the MySQL server running on the default port (3306). I’ve changed this to 8889 as MAMP (on mac) suggests and now it works like a charm.
Strange mysql sorting behaviour with enums
Posted by Erik Bauffman in default on September 23, 2010
If you have an ENUM(‘Y’,'N’) field and you want to sort ASC on this field, mysql will show the Y values before the N values. It seems this is based on the order you defined them when creating your table… See the screenshot.
I’d like to add the explanation from mysql.com which can be found here.
ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, ‘a’ sorts before ‘b’ for ENUM(‘a’, ‘b’), but ‘b’ sorts before ‘a’ for ENUM(‘b’, ‘a’). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values. To prevent unexpected results, specify the ENUM list in alphabetic order.
Setting your timezone in mysql
Posted by Erik Bauffman in default on February 8, 2010
If you want to set your timezone in mysql for your database connection, do this.
SET time_zone = ‘+2:00′;
Keep in mind that the number should be correct and can also have a minus sign, based on the timezone you want to set ofcourse. Do watch out for the UTC timezone, because mysql requires the +.
SET time_zone = ‘+0:00′;