[mythtv-users] mythconverg database server migration

Stephen Worthington stephen_agent at jsw.gen.nz
Wed Mar 29 15:43:40 UTC 2017

On Wed, 29 Mar 2017 11:09:09 -0400, you wrote:

>I'm attempting to use the mythconverg_backup.pl / mythconverg_restore.pl
>scripts to move my database from one very old slow server to another
>old, but not as slow server. After a backup, my restore has been running
>for about 6 days now, with the following output from show processlist:
>| Id | User   | Host                    | db          | Command | Time | State  | Info
>| 17 | mythtv |            myhost:37979 | mythconverg | Query   | 270851 | update | INSERT INTO `recordedseek` VALUES (1017,'2016-01-09 06:56:00',420,8972010,9),(1017,'2016-01-09 06:56 |
>Now, checking my mythweb statistics, I see:
>Total Running Time: 13 years 16 days 22 hrs 26 mins
>so I know there is an awful lot of data to restore, but is there
>anything I can do. I did a clean install of mysql 5.6 on an updated
>debian jessie...no tuning or config changes other than allowing LAN
>This was an empty database. I ran:
>mythconverg_restore.pl --hostname dbhost --create_database --verbose
>A few questions...
>1. will this ever end?
>2. can I kill this and restart, potentially exclude the big tables?
>3. is there any tuning to be done?
>Again, this was a fresh install of mysql 5.6. From a quick search, there
>were some suggestions to turn off
>autocommit/unique_checks/foreign_key_checks and something about
>tuning key_buffer_size.
>I assume if I can kill the restore, I can drop and recreate the database
>without any problems, but is there a faster way? What if I don't load
>the recordedseek table at all? I'd certainly prefer to not have to
>interfere in a database load, but I need this to finish in some
>definition of reasonable amount of time.
>Any thoughts? The restore is still running if that matters.
>Thanks in advance for any input

What is the size of your mythconverg*.sql.gz files?  I have a huge
database (over 20,000 recordings), so mine are around 1.2 Gibytes now,
but a restore takes less than one hour.  I am running on a fast NVME
SSD though.  And the box has 8 Gibytes of RAM, which helps.

The recordedseek table can be regenerated on your new system from the
recording files, if necessary, but it will take a long time.  So it
would be possible to do a restore that omitted that table.  You would
need to have an editor capable of handling multi-gigabyte text files
to do that though, as you would need to cut the recordedseek table out
of the .sql file.

One other option is to ensure that you have exactly the same MySQL
version installed on the new system as is on the old one, and then
copy across the database as binary files.  First, make sure that a
mythconverg database has been created on the new system.  Then just
copy the entire /var/lib/mysql/mythconverg directory.  After that, you
can update the MySQL version to the current one again.  It is also
possible that MySQL will be able to automatically upgrade an older
format database, but I have never tried that.

Did you ensure that the database was fully checked and repaired before
you backed it up?  If it has any problems, that can cause trouble when
it is restored.

If you have a more capable PC, it might also be possible to do an
install to a virtual PC on that box, restore the database there, and
then just copy it across in binary format to the new system.

I case it helps, here are the contents of my tweaks files, showing
what I am using that is different from the standard MySQL configs:

root at mypvr:/etc/mysql/conf.d# cat mythtv-tweaks.cnf
# The following values were partly taken from:
# http://www.gossamer-threads.com/lists/mythtv/users/90942#90942
# key_buffer = 48M
# max_allowed_packet = 8M
table_open_cache = 128
sort_buffer_size = 48M
net_buffer_length = 8M
# thread_cache_size = 4
query_cache_type = 1
query_cache_size = 32M
# don't do binary logs for mythconverg
binlog_ignore_db = mythconverg
root at mypvr:/etc/mysql/conf.d# cat mythtv-tweaks-jsw.cnf
# Customized config for MythTV baseline
# From http://www.mythtv.org/wiki/Tune_MySQL

# Warning: Some of the settings here override settings in other config
# Mysql uses the last setting it reads - so this file must be after
the other
# *.cnf files in /etc/mysql/conf.d in the sort order used to read the

# Warning: Mysql config files will be ignored if world writeable -
make sure
# if using Slickedit from Windows to run this after writing this file:
#   chmod a=r,u=rw /etc/mysql/conf.d/mythtv-tweaks-jsw.cnf

# Move tmpdir to a different drive from the system, where there is
more space
# and its use will not thash against use of the database files.
#tmpdir         = /mnt/rec3/tmp/mysql

key_buffer_size         = 64M
tmp_table_size          = 32M
table_open_cache        = 256
query_cache_limit       = 3M
query_cache_size        = 32M

# Set Base Innodb Specific settings here
innodb_flush_method             = O_DIRECT
innodb_file_per_table           = 1
innodb_file_format              = barracuda
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 20
innodb_flush_log_at_trx_commit  = 2
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size         = 128M
innodb_thread_concurrency       = 8

# Logging Options
long_query_time         = 5
slow_query_log          = ON
slow_query_log_file     = /var/log/mysql/mysql-slow.log
general_log_file        = /var/log/mysql/mysql.log
general_log             = OFF
log_error               = /var/log/mysql/error.log
binlog_do_db            = include_database_name

The config in the mythtv-tweaks-jsw.cnf file is loaded last, so it
overrides anything in the other config files.

I am actually using MariaDB instead of MySQL, but that should not make
much difference.

More information about the mythtv-users mailing list