[mythtv-users] mysqldump shows no schema

[Michael T. Dean]

On 07/03/2012 09:36 AM, DFishburn wrote:
> Just finished installing a brand new Mythbuntu 12.04 install.
>
> Before I restore my backup from my previous system I was trying to get 
> a proper backup / dump of the defacto standard MythTV install.
>
> mythtv at myth:~/myth_backup$ mysql -u mythtv -p** -D mythconverg
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 205
> Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)
>
> Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights 
> reserved.
>
> Oracle is a registered trademark of Oracle Corporation and/or its
> affiliates. Other names may be trademarks of their respective
> owners.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input 
> statement.
>
> mysql>show tables;
>
> +--------------------------------+
> | Tables_in_mythconverg          |
> +--------------------------------+
> | archiveitems                   |
> | callsignnetworkmap             |
> | capturecard                    |
> | cardinput                      |
> | channel                        |
> ...
>
> Now, I run mysqldump:
>
> mythtv at myth:~/myth_backup$ /usr/bin/mysqldump -u mythtv -p*** 
> --delete_master_logs --databases mythconverg > 
> mythconverg_025_original.sql
> OR
> mythtv at myth:~/myth_backup$ /usr/bin/mysqldump -u mythtv -p*** 
> --delete_master_logs --opt mythconverg > mythconverg_025_original.sql
>
> mythtv at myth:~/myth_backup$  less mythconverg_025_original.sql
>
> -- MySQL dump 10.13  Distrib 5.5.24, for debian-linux-gnu (x86_64)
> -- 
> -- Host: localhost    Database: mythconverg
> -- ------------------------------------------------------
> -- Server version       5.5.24-0ubuntu0.12.04.1
>
> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
> /*!40101 SET NAMES utf8 */;
> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
> /*!40103 SET TIME_ZONE='+00:00' */;
> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, 
> FOREIGN_KEY_CHECKS=0 */;
> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, 
> SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
> /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
>
> /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
> /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
> /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
> /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
> /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
> /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
> /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
>
> -- Dump completed on 2012-07-03  9:26:18
> mythconverg_025_original.sql (END)
>
>
> When I used the same command on my previous system, I would at least 
> get the schema.  There is nothing here but comments, so that is not 
> much of a dump and no chance at restoring.
>
> Previous dump:
> -- MySQL dump 10.11
> -- 
> -- Host: localhost    Database: mythconverg
> -- ------------------------------------------------------
> -- Server version       5.0.45-Debian_1ubuntu3.1-log
>
> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
> /*!40101 SET NAMES utf8 */;
> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
> /*!40103 SET TIME_ZONE='+00:00' */;
> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, 
> FOREIGN_KEY_CHECKS=0 */;
> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, 
> SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
>
> -- 
> -- Position to start replication or point-in-time recovery from
> -- 
>
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002568', 
> MASTER_LOG_POS=98;
>
> -- 
> -- Table structure for table `archiveitems`
> -- 
>
> DROP TABLE IF EXISTS `archiveitems`;
> CREATE TABLE `archiveitems` (
>   `intid` int(10) unsigned NOT NULL auto_increment,
>   `type` set('Recording','Video','File') default NULL,
>   `title` varchar(128) default NULL,
>   `subtitle` varchar(128) default NULL,
>   `description` text,
>   `startdate` varchar(30) default NULL,
>   `starttime` varchar(30) default NULL,
>   `size` bigint(20) unsigned NOT NULL,
>   `filename` text NOT NULL,
>   `hascutlist` tinyint(1) NOT NULL default '0',
>   `cutlist` text,
>   PRIMARY KEY  (`intid`),
>   KEY `title` (`title`)
> ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
>
> ...
>
>
> Am I making an error with a newer version of mysql installed?

I highly recommend using the mythconverg_backup.pl script to do 
backups.  If you use the wrong mysqldump arguments, you'll get a backup 
that--if used to restore a DB--will corrupt your schema.

http://www.mythtv.org/wiki/Database_Backup_and_Restore

Generally the first and 2nd sections (through Quick Start) is all you 
need to read.

Mike