How To Synchronize Your PHP and MySQL Timezones

PHP and MySQL are web processes that operate with their own separate default configurations for timezones. It is unlikely that you will have timezone issues during development or when you use a single server. PHP and MySQL can use the server’s time configurations in these situations.

Many developers set PHP as the default configuration if:

One’s application can be set to the user’s timezone.
If different timezone requirements exist in two or more running applications
One cannot set the default timezones because the application was installed on shared server.

Developers also change from dates to Unix timestamp integers for simpler usability but there is a risk of running into variety of issues such as additional processing of translating dates for your application, the difficulty to see the dates in a table, the difficulty of acquiring date-based SQL queries as well as the inability to use MySQL date/time functions like NOW().

However it is possible to synchronize the timetables of the PHP and MySQL. A single configuration variable is required to define your PHP timezone.

1.<?php
2. define(‘TIMEZONE’, ‘America/New_York’);

The code below will establish the default timezone for PHP:

date_default_timezone_set(TIMEZONE);

For MySQL , use this code:

SET time_zone=’offset’;

Offset is the difference of time in relation to the GMT zone. For instance, if you want to set the timezone a hour ahead of GMT you would replace offset with ‘+1:00′. For the code to work, you need to have a +/- sign with the hours even at 0:00.

To synchronize PHP and MySQL together, you must first start with the following PHP code:

1. $now = new DateTime(); 
2. $mins = $now->getOffset() / 60;

From there you can convert the number of seconds in the offset to minutes with the following calculation:

1. $sgn = ($mins < 0 ? -1 : 1); 
2. $mins = abs($mins); 
3. $hrs = floor($mins / 60);
4. $mins -= $hrs * 60;

From here, format the string with this function:

$offset = sprintf(‘%+d:%02d’, $hrs*$sgn, $mins);

After this step, you can now use the SET time_zone command. Now your PHP and MySQL codes should be synchronized.The following code is an example:

$db = new PDO(‘mysql:host=localhost;dbname=test’, ‘dbuser’, ‘dbpassword’); 
$db->exec(“SET time_zone=’$offset’;”);

—

Quoted from: http://blog.awardspace.com/how-to-synchronize-your-php-and-mysql-timezones

How-To create a MySQL database and set privileges to a user

MySQL is a widely spread SQL database management system mainly used on LAMP (Linux/Apache/MySQL/PHP) projects.

In order to be able to use a database, one needs to create: a new database, give access permission to the database server to a database user and finally grant all right to that specific database to this user.

This tutorial will explain how to create a new database and give a user the appropriate grant permissions.

For the purpose of this tutorial, I will explain how to create a database and user for the music player Amarok. In order to index its music collection, Amarok quand use a mysql backend.
The requirement for this set up is to have access to a database. We are going to create a database called amarok which will be accessible from localhost to user amarok idetified by the password amarok….

Obviously, we need to to have a mysql server installed as well as amarok:

$ sudo apt-get install mysql-server amarok

On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:

mysql> create database amarokdb;
Query OK, 1 row affected (0.00 sec)

We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:

mysql> grant usage on *.* to amarokuser@localhost identified by ‘amarokpasswd’;
Query OK, 0 rows affected (0.00 sec)

And finally we grant all privileges on the amarok database to this user:

mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
Query OK, 0 rows affected (0.00 sec)

And that’s it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u amarokuser -p’amarokpasswd’ amarokdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

—-

Quoted from: debuntu.org

MySQL Change User Password

How do I change user password under MySQL server? I’d like to change a password for user called tom using UNIX / Linux command line option.

First, login to MySQL server, type following command at shell prompt to login as root
$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt, do not include string “mysql>”):
mysql> use mysql;

3) Change password for user tom:
mysql> update user set password=PASSWORD("NEW-PASSWORD-HERE") where User='tom';

—

quoted from: www.cyberciti.biz