How to Change MySQL Server Time Zone in Linux?

Introduction

As a MySQL sеrvеr administrator, onе of thе еssеntial tasks is to еnsurе that thе sеrvеr's timе zonе is corrеctly configurеd. Thе timе zonе sеtting affеcts various aspеcts of thе databasе, such as datе and timе calculations, timеstamp convеrsions, and quеry rеsults. In this guidе, wе will walk you through thе stеp by stеp procеss of changing thе MySQL sеrvеr timе zonе in Linux.

Steps to check the current time zone setting of MySQL Server in Linux

To check the current time zone setting of MySQL Server in Linux, you can follow these steps:

Step 1. Use the MySQL command line client to connect to the MySQL Server. You can do this by opening a terminal window and typing:

Replace `username` with your MySQL username. You will be prompted to enter your MySQL password.

Step 2. Once you are logged in to MySQL, execute the following SQL query to view the current time zone setting:

This query retrieves the global time zone variable value, which represents the current time zone setting for the MySQL Server.

Step 3. After executing the query, MySQL will return the current time zone setting of the server.

The time zone setting could be `SYSTEM`, a specific time zone name (e.g., `UTC`, `America/New_York`), or an offset from UTC (e.g., `+00:00`, `-05:00`). You can check your system time zone by running the date command.

By following these steps, you can easily check the current MySQL Server time zone setting on a Linux system.

Option 1: Use the SET GLOBAL time_zone Command

The first option to change the MySQL server time zone is by using the SET GLOBAL time_zone command. This command allows you to set the time zone for the entire server.

To change the time zone using this method, follow these steps:

Step 1. Connect to the MySQL server using the command-line client or a MySQL management tool.

Step 2. Run the following command to set the global time zone:

Replace 'time_zone' with the desired time zone value. For example, for Eastern Time Zone (ET), you can use:

After running the command, the time zone for the MySQL server will be changed immediately.

Using the SET GLOBAL time_zone command is a straightforward method to change the MySQL server time zone. However, keep in mind that this change affects the entire server and all connected clients.

Option 2: Edit the MySQL Configuration File

Another way to change the MySQL server time zone is by editing the MySQL configuration file. This method allows you to set a default time zone for the server, which will be applied to all new connections.

To change the time zone using this method, follow these steps:

Step 1. Open the MySQL configuration file using a text editor. The location of the file may vary depending on your Linux distribution, but it is commonly located at /etc/mysql/my.cnf or /etc/my.cnf.

Step 2. Locate the [mysqld] section in the file. If it doesn't exist, add it at the beginning of the file.

Step 3. Add the following line under the [mysqld] section to set the default time zone:

Replace 'time_zone' with the desired time zone value. For example, to set the time zone to 'Asia/Kolkata', use:

Step 4. Save the changes and exit the text editor. Restart the MySQL server for the changes to take effect.

By editing the MySQL configuration file, you can set a default time zone that will be used for all new connections to the server.

Option 3: Using the Set Session Command

The third option to change the MySQL server time zone is by using the SET SESSION command. This command allows you to set the time zone for the current session only, without affecting other sessions or the global time zone setting.

To change the time zone using this method, follow these steps:

Step 1. Connect to the MySQL server using the command-line client or a MySQL management tool.

Step 2. Run the following command to set the session time zone:

Replace 'time_zone' with the desired time zone value. For example, to set the time zone to 'Asia/Tokyo', use:

After running the command, the time zone for the current session will be changed.

Using the SET SESSION command provides flexibility in changing the time zone for specific sessions without affecting the global time zone setting or other sessions.

Option 4: Change MySQL Server Time Zone in WHM/cPanel

If you are using WHM/cPanel to manage your MySQL server, you can change the time zone through the WHM interface. This method is suitable for users who prefer a graphical user interface (GUI) rather than command-line tools.

To change the time zone using cPanel, follow these steps:

Step 1. Log in to your cPanel WHM (Web Host Manager) account.

Step 2. Navigate to the "Server Configuration" section. You can locate it in the left pane or use the search function if you're unable to find it.

Step 3. Within the "Server Configuration" section, locate and click on the "Server Time" option.

Step 4. In the "Server Time" settings, you'll find a dropdown menu where you can select your desired time zone.

Step 5. After selecting the appropriate time zone, click on the "Change Timezone" button.

Step 6. The "Current Time" value will be updated based on your selection. If you believe the displayed time is incorrect, you can choose to synchronize the time with a Time Server.

If prompted, reboot the server directly from WHM.

Changing the MySQL server time zone in WHM/cPanel is a user-friendly method that allows you to make the configuration change without using the command-line interface.

Conclusion

In this guidе, we havе еxplainеd thе stеp by stеp procеss of changing thе MySQL sеrvеr timе zonе in Linux. You havе lеarnеd four diffеrеnt mеthods to accomplish this task: using thе SET GLOBAL timе_zonе command, еditing thе MySQL configuration filе, using thе SET SESSION command, and changing thе timе zonе in WHM/cPanеl. Rеmеmbеr to vеrify thе nеw timе zonе configuration to еnsurе that it has bееn appliеd corrеctly. By mastеring thе timе zonе configuration in MySQL, you can еnsurе accuratе datе and timе calculations and improvе thе ovеrall functionality of your databasе.