Mysqldump UTC timezone workaround

Hello,

Today We needed to use mysqldump to dump lots of tables from one database to another, but seeing

select COUNT(*) from table where date_time < ‘2007-08-30 23:59:59’;

New copied table says 18 extra rows, so ‘something is wrong’.

Our mysqldump was something like:

hostbackup$ mysqldump dumpoptions -w date_time < ”2007-08-30 23:59:59″ | mysql options

Reading the mysqldump manual, it tells that it uses UTC by default, so the only way to disable it is using “–skip-tz-utc” option.

Using mysqldump ‘–skip-tz-utc’ works as expected, since we do not use UTC., we use CET instead.

The complet bash script, if someone want use it :)

#!/bin/sh
# default path
PATH=/bin:/usr/bin
# tables
tables="table1 table2 table3 etc"
# mysqldump & mysql options
db="database"
rhost="remotedatabasehost"
lhost="localhost"
user="ouruser"
pass="ourpass"
dumpopts="--skip-tz-utc --insert-ignore --quote-names --no-create-info --host=${rhost} --user=${user} -p${pass}"
lsqlopts="-h${lhost} -u${user} -p${pass}"
rsqlopts="-h${rhost} -u${user} -p${pass}"
# current time
datesql="`date '+%Y-%m-%d'` 00:00:00"

for table in $tables; do
  echo "dumping $table ..."
  if mysqldump $dumpopts -w "fecha_hora < '$datesql'" $db $table | mysql $lsqlopts $db; then
    echo "table $table was dumped :)"
  else
    echo "$table not dumped!"
  fi
done

See you!

Advertisement