MySQL timezone setting per query – shared hosting and VPS

Question:

We would like to set our local timezone for web-based transactions. Our web site uses the MySql ‘now()’ function to retrieve the current time.

Is there a way to adjust the time for all our database operations?

Thanks!

Answer:

Yes. The mysql documentation describes how to set it per query at :

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html (check this link fi the instrucitons here seem to be out of date)

As of this writing, you can do this by performing:


SET time_zone = timezone;

at the start of each session. Keep in mind that if you are doing this in a script, you’ll need to do this each time you connect (basically once per script in most cases).

If you are using one of our VPS’s you can make a global time zone setting and not perform this on each connection.

Can you make mysql index 3 letter words? (adjust the ft_min_word_len value)

Question:

I want my mysql full-text indexes to index 3 letter words. The default is 4 letters. Can you fix this?

Answer:

Sure, no problem.

Many of our servers already have this set. If yours does not just send in a support ticket and we’ll update it for you. After the update, you’ll need to rebuild the existing indexes to index the 3 letter words. Any new indexes will use 3 letters by default. The documentation at mysql.com has instructions for updating your indexes.

How do I backup my database?

Question:

How do I download a backup copy of my database.

Answer:

The best method is to login to ssh and type:

mysqldump name_of_your_database -u your_username -p > database_dump.sql

it will ask you for your password, enter it and then wait 🙂

When it completes you can also type:

gzip database_dump.sql

which will compress the file (usually by a lot since it is highly compressable data in most cases).

The new file will be :

database_dump.sql.gz

You can then ftp this to your local computer, etc.

If you ever need to open it, most windows and mac decompressors (winzip, etc.) can decompress the file. All unix/linux servers will be able to decompress it.

You can also use phpMyAdmin in the control panel. However we have found that for larger backups it sometimes does not work as smoothly.

I hope this helps!

I can’t create new databases

Question:

I can’t seem to create a new database.

I tried both from the mysql command line and from phpMyAdmin – it won’t work!

Answer:

All new databases must be created from the control panel.

Due to security issues, your database user cannot create new databases. However if you log in to the control panel and go to the database section you can create new databases there.

I have forgot my database password. How can I change it?

Question:

I’ve forgot my database password. What are my options?

Answer:

If you have forgot your database password and do not have any scripts that depend on it, you can change your password via the database section of your control panel. Just click on the link that says “Change Database Password”. Do not log-in to phpmyadmin to change your password.

However things become much more complicated if you have scripts (wordpress, joomla, drupal, etc.) that rely on the password. If you change your password it will break those scripts. Unfortunately, even server admins not have access to your existing database password (the passwords are encrypted and not easily decrypted).

The best option if your scripts depend on your password is to search the scripts for the password. If you know the location of the password this is easy. For example, wordpress stores your password in wp-config.php.

If you do not know where the password is stored, use something like the following to find it:

find . -name "*.php" -exec grep "localhost" {} ; -print
find . -name "*.inc" -exec grep "localhost" {} ; -print
find . -name "*.php" -exec grep "3306" {} ; -print
find . -name "*.inc" -exec grep "3306" {} ; -print

These search through common php file extensions and search for the strings “localhost” and “3306” which are usually close to the location of your password. You can also search for the term “password” or “pass” but will likely get a lot of false positives.

If you are not comfortable doing the above, our support staff can find your password for a one-time fee of $25. Contact support with your request.