Blog Category • Databases

My PHP MySQL SSH tunneling experience

First, little background on why I’m writing this blog post… I have a client that has a “semi-dedicated” hosting account. In most respects, this account is just a fancy shared hosting account, but they say there are less accounts on the server. The client’s website is on that hosting account. The client also has a server at another location. I have root access to that server, and the client wants to be able to have that server do direct MySQL queries on the website’s database.

Read More …

Posted February 20th, 2017 in , , ,

Switching from MySQL to MariaDB on Ubuntu 14.04

I’ve known about MariaDB for a while, but because I wanted my development machines to match my production environments, I just stayed with MySQL. Today I found out that the production environments switched to MariaDB, so I searched around the internet to find out how to do the switch on my development machines. All are running Ubuntu 14.04, so I thought that as long as I could do it on one, it would be easy to do the rest. Well, that would be the case if there were good instructions somewhere, but I had to piece together a solution that worked for me, as I was getting error messages after following the advice of others.

Read More …

Posted December 22nd, 2015 in , , ,

Easy Backup & Restore a MySQL Database

If you want to make a database backup, it’s better to not use phpMyAdmin, because larger databases will cause that method of backup to fail. Instead, just use mysqldump, which should already be installed if mysql is installed:

#backup 
mysqldump -u username -ppassword database | gzip > ./dumpfilename.sql.gz

#restore
gunzip < ./dumpfilename.sql.gz | mysql -u username -ppassword database

Read More ...

Posted February 16th, 2015 in ,

MySQL: Convert Column From One Timezone to Another

It’s a pretty common thing to find an app that doesn’t specify to MySQL the timezone that should be used when storing dates and times. Once you’ve fixed that, you may still have a bunch of dates and times that need to be converted.

Read More …

Posted February 14th, 2015 in

Default Fetch Mode for Doctrine DBAL

After using Doctrine DBAL for a while, I’ve realized that I always fetch an object. There may be some good reasons to fetch an array, but I never do. The documentation for Doctrine DBAL is nice, in fact I think it is wonderful how basic usage is explained, but not everything is covered. After digging around in the code, I found out how to set a default fetch mode so I don’t have to do it in all my queries. You can do it right as you make your connection to the database.

Read More …

Posted February 7th, 2014 in , ,

Virtual Hosts in XAMPP on Windows 7 64-bit

Out of the box XAMPP only displays a single website, which is located in the htdocs directory. If needed, multiple websites can be served by implementing virtual hosts, which only requires editing 2 files. If you’ve been working with another type of server and have not been able to figure this out, don’t worry because it’s really easy.

In the <xampp>/apache/conf/extra directory, you will find a file named http-vhosts.conf. Lets say we want to add a domain named example and that it will be located at C:\xampp183\htdocs\example, here’s what we add to the conf file:

Read More …

Posted February 7th, 2014 in , ,

Quick MySQL Datatypes Comparison

Without looking, do you know the difference between MySQL’s text and mediumtext datatypes? How about the difference between int, mediumint, smallint, and tinyint? Unless you’re a Einstein level genius, you’re probably not going to be able to remember the differences between all datatypes, I know I can’t. So, I thought I’d post a comparison of MySQL datatypes. In the future I will probably come back and revise this blog post to show a more complete set of datatypes, but I
‘ll start off with the basics.

Read More …

Posted February 7th, 2014 in ,

« Older Posts