MySQL mysql_tzinfo_to_sql program

Question

I am using MySQL 5.0.51b on microsoft windows xp. I am trying to load data from zoneinfo files(generated by library downloaded from here) to database tables as described here.

Now i am not able to find where would i get this "mysql_tzinfo_to_sql" program for windows. I tried executing it on mysql command line client but no success. On linux you can directly execute this command on the shell.

Any help is appreciated.

Answer 1

You don't need to run mysql_tzinfo_to_sql on Windows.

For Windows just do this:

1. Download the files. Links here
2. Move them to your MySQL directory. Example: C:\ProgramData\MySQL\MySQL Server 5.5\data\mysql
3. Restart your server.
4. Now, if you want, you can change your timezone like this: SET time_zone = 'America/Costa_Rica';
5. Check it with SELECT NOW();

---

More information here: MySQL

And take a look at this: Answer

Answer 2

The command "mysql_tzinfo_to_sql" doesn't work on Windows. You have to download the timezone packages wich contains SQL statements and populate the timezone tables using the "source" command, like this:

mysql> use mysql ;
mysql> source /path/to/file/timezone_posix.sql ;

Check the following links for reference:

Blog: https://discourse.looker.com/t/cannot-connect-time-zone-tables-dont-appear-to-be-loaded-in-mysql/208/6

Scripts sql: http://downloads.mysql.com/general/timezone_2016a_posix_sql.zip , http://downloads.mysql.com/general/timezone_2016a_leaps_sql.zip

Answer 3

None of the 'populate file' methods worked for me with mysql 8.

A lot of answer contains this link: http://dev.mysql.com/downloads/timezones.html There's downloadable zip files that contain sql files. Putting it to any directory didn't help. One thing helped me: I issued a "use mysql;" and executed the content of the downloadable sql file as a script.

Answer 4

based on Francisco Corrales Morales answer.

For MySQL 5.7+ on Windows 10 machine, my procedure is

1. download latest POSIX Standard time zone script under 5.7+ section from https://dev.mysql.com/downloads/timezones.html
2. Extract the file then there would be a single SQL file named timezone_posix.sql
3. run the sql script, in my case, use command line below

bin\mysql.exe --host=localhost --port=3306 --user=USERNAME -p mysql < c:\...\Downloads\timezone_2020d_posix_sql\timezone_posix.sql

Note make sure you run the time zone script under mysql database/schema.

Answer 5

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uadmin -ppassword mysql

Answer 6

For Windows, MySQL supplies an already loaded database for you to download and stick in your data directory: http://dev.mysql.com/downloads/timezones.html

Copied from the user comments on the MySQl docs:

Posted by Jyotsna Channagiri on November 20 2008 6:28pm

Hi,

I thought this information will helps somebody who are looking for changing mysql timezone.

The steps are:

1. Download the timezone table structure and data from http://dev.mysql.com/downloads/timezones.html

2. Copy and paste the data in your Mysql/data/mysql folder

3. Restart your mysql server.

4. mysql> SET GLOBAL time_zone = 'America/Toronto';

5. mysql> SET SESSION time_zone = 'America/Toronto';

6. Check SELECT @ @global.time_zone , @ @session.time_zone ; It should give you the time zone you set just before.

Comment:

Yes, but the tables provided by MySQL are outdated (generated by 2006p version of D olson's timezone library). I need the latest timezones data, hence i downloaded the latest library and generated the binaries. Now i need a way to load these tables in mysql.But i don't know how to do it on windows.

Ah, I see. Then you're going to need to do one of two things. 1) get the tool that does this and compile it (or whatever) on Windows. If you're lucky, it's a perl script.

2) fill the database on linux, then copy it to Windows. [This guy][http://it-idiot.einsamsoldat.net/2008/01/moving-mysql-database-from-windows-to-linux-redhat/comment-page-1/2] says it can be done, at least for MyIsam.

Answer 7

I am using XAMPP and PHP 7.4.27 on Windows 10 and had some difficulties getting other solutions to work.

Here are the steps I took to get it working was what worked for me.

1. Download the latest MySQL Community Downloads POSIX Standard or the Non POSIX with leap seconds(if you need the leap seconds included) time zone script under the section that states:

Each file contains SQL statements to fill the tables

2. Extract the file, which should be a single SQL file named timezone_posix.sql.
3. Open the extracted SQL file in the code editor of your choice and copy its content.
4. Open your DB administration tool of choice, select the "mysql" table and under the "SQL" tab paste the contents of the extracted file.

Note: #4 Instructs on the basis of PHPMyAdmin, other administration tools might have a different process.

5. Click "Go" and follow any prompts after that.
6. All needed time_zone tables should be populated with timezone data