[mythtv-users] mysqldump shows no schema
Michael T. Dean
mtdean at thirdcontact.com
Tue Jul 3 15:22:47 UTC 2012
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
More information about the mythtv-users
mailing list