should views be ignored for a mysqldump backup solution?
Rahul
wanted to ensure that daily backup dumps are taken. On my older mysql setup
I found a wrapper backup script (reproduced below). I read up on the
mysqldump command but one issue confuses me.
Question: Why is the "ignore views" part of the script there? That was put
by a previous sys admin. Is it irrelevant (or worse still, dangerous)?
Should I be getting rid of it? [I did read stuff about restrictions on the
way views are handled by mysql but this isn't fully clear to me]
What is the reccomended procedure to handle this?
#################################################
ignore_views=''
VIEWLIST=`echo "SELECT TABLE_SCHEMA, TABLE_NAME from
information_schema.VIEWS" | \
mysql -u backup information_schema | \
sed '/^TABLE_SCHEMA/d' |
gawk {'print $1"." $2'}`
for VIEW in $VIEWLIST; do ignore_views="$ignore_views --ignore-table
$VIEW"; done
mysqldump -u backup --all-databases --opt $ignore_views | gzip >
/var/log/mysql/mysql_`date "+%Y%m%d"`.gz
##############################################
--
Rahul
Gordon Burditt
>by a previous sys admin. Is it irrelevant (or worse still, dangerous)?
As I recall, an early version of mysqldump (5.0.* for some low version of
*, I think) used to dump views as tables, and if you restored it, you'd
get the table in place of the view. Now, it seems that restoring it
will create the table (no data), drop it, then create the view.
There may have been some problem in having the view restored *after*
the tables it's a view of.
Here's how 5.1.35 dumps a view:
-- MySQL dump 10.13 Distrib 5.1.35, for portbld-freebsd7.1 (i386)
--
-- Host: mysql.example.com Database: acct
-- ------------------------------------------------------
-- Server version 5.1.35-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 */;
--
-- Temporary table structure for view `wallet`
--
DROP TABLE IF EXISTS `wallet`;
/*!50001 DROP VIEW IF EXISTS `wallet`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `wallet` (
`id` int(11),
`denom` smallint(6),
`series` varchar(5),
`serial` varchar(20),
`m` enum('N','I','Y'),
`entered` date,
`lastupdate` date,
`e` int(11)
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;
--
-- Final view structure for view `wallet`
--
/*!50001 DROP TABLE `wallet`*/;
/*!50001 DROP VIEW IF EXISTS `wallet`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`gordon`@`mysql.example.com` SQL SECURITY INVOKER */
/*!50001 VIEW `wallet` AS select `b`.`id` AS `id`,`b`.`denom` AS `denom`,`b`.`series` AS `series`,`b`.`serial` AS `serial`,`b`.`marked` AS `m`,`b`.`entered` AS `entered`,`k`.`lastupdate` AS `lastupdate`,`k`.`entries` AS `e` from (`bills` `b` left join `george_keys` `k` on((`b`.`id` = `k`.`id`))) where isnull(`b`.`spent`) order by `b`.`id` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
/*!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 2009-07-03 21:54:25
Rahul
news:XNOdnRDJJ_A9HdPX...@posted.internetamerica:
> As I recall, an early version of mysqldump (5.0.* for some low version of
> *, I think) used to dump views as tables, and if you restored it, you'd
> get the table in place of the view. Now, it seems that restoring it
> will create the table (no data), drop it, then create the view.
>
> There may have been some problem in having the view restored *after*
> the tables it's a view of.
>
> Here's how 5.1.35 dumps a view:
>
Thanks Gordon! So, from your comments it seems safe to get rid of the part
which excludes views, right and just use the simple snippet:
mysqldump -u backup --all-databases | gzip >
/var/log/mysql/mysql_`date "+%Y%m%d"`.gz
I am using 5.0.45. (I wish I had 5.1.* but RHEL insists on dishing out
ancient versions of packages)
Can any users of 5.0.45 (or thereabouts) comment?
--
Rahul
Jerry Stuckle
Views have no data of their own; they only show data in tables (or other
views).
If you've backed up the view definition, you have everything. Backing
up the view data is just duplication of effort.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Gordon Burditt
>> *, I think) used to dump views as tables, and if you restored it, you'd
>> get the table in place of the view. Now, it seems that restoring it
>> will create the table (no data), drop it, then create the view.
>>
>> There may have been some problem in having the view restored *after*
>> the tables it's a view of.
>>
>> Here's how 5.1.35 dumps a view:
>>
>
>Thanks Gordon! So, from your comments it seems safe to get rid of the part
>which excludes views, right and just use the simple snippet:
>
>mysqldump -u backup --all-databases | gzip >
>/var/log/mysql/mysql_`date "+%Y%m%d"`.gz
>
>
>I am using 5.0.45. (I wish I had 5.1.* but RHEL insists on dishing out
>ancient versions of packages)
That seems like a fairly recent version of 5.0 compared with when the
view problems happened. Try dumping a single view with mysqldump:
mysqldump dbname viewname | more
(as usual, you may need args for logging in).
If it looks like the 5.1.35 output, with creating a table, dropping
it, then creating the view, I think you're fine. If in doubt,
dump a whole database including some views and some tables they
use, then restore it to an empty database (being sure not to restore
over the original). If you get errors, there's a problem.
Rahul
> That seems like a fairly recent version of 5.0 compared with when the
> view problems happened. Try dumping a single view with mysqldump:
Just tried that (output below)
> If it looks like the 5.1.35 output, with creating a table, dropping
> it, then creating the view, I think you're fine.
It does look similar to my eyes.
> If in doubt,
> dump a whole database including some views and some tables they
> use, then restore it to an empty database (being sure not to restore
> over the original).
I can try that. I've never really done a restore so was a bit afraid of
messing up my in-production tables!
##############################
mysqldump -p -u root ScreenerData C_Screener | more
Enter password:
-- MySQL dump 10.11
--
-- Host: localhost Database: ScreenerData
-- ------------------------------------------------------
-- Server version 5.0.45
/*!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 */;
--
-- Temporary table structure for view `C_Screener`
--
DROP TABLE IF EXISTS `C_Screener`;
/*!50001 DROP VIEW IF EXISTS `C_Screener`*/;
/*!50001 CREATE TABLE `C_Screener` (
`ImpurityMetal` varchar(2),
`HostMetal` varchar(2),
`x` double,
`y` double,
`Prediction` varchar(9),
`NSA_Name` varchar(6)
) */;
--
-- Final view structure for view `C_Screener`
--
/*!50001 DROP TABLE IF EXISTS `C_Screener`*/;
/*!50001 DROP VIEW IF EXISTS `C_Screener`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `C_Screener` AS (select sql_no_cache `SegEnergy`.
`ImpurityMetal` AS `ImpurityMetal`,`SegEnergy`.`HostMetal` AS `HostMe
tal`,`SegEnergy`.`SegEnergy` AS `x`,(`b`.`BE` - `a`.`BE`) AS `y`,
`ScreenerData`.`prediction`(`SegEnergy`.`SegEnergy`,(`b`.`BE` - `a`
.`BE`)) AS `Prediction`,`ScreenerData`.`create_nice_name`(`SegEnergy`.
`ImpurityMetal`,`SegEnergy`.`HostMetal`,`ScreenerData`.`predic
tion`(`SegEnergy`.`SegEnergy`,(`b`.`BE` - `a`.`BE`))) AS `NSA_Name` from
((`SegEnergy` join `MetalBEs` `a`) join `MetalBEs` `b`) whe
re ((`a`.`Adsorbate` = _latin1'C') and (`b`.`Adsorbate` = _latin1'C') and
(`SegEnergy`.`ImpurityMetal` = `a`.`MetalName`) and (`SegE
nergy`.`HostMetal` = `b`.`MetalName`) and (`SegEnergy`.`ImpurityMetal` <>
`SegEnergy`.`HostMetal`)) order by `ScreenerData`.`predict
ion`(`SegEnergy`.`SegEnergy`,(`b`.`BE` - `a`.`BE`)) desc,`ScreenerData`.
`create_nice_name`(`SegEnergy`.`ImpurityMetal`,`SegEnergy`.`
HostMetal`,`ScreenerData`.`prediction`(`SegEnergy`.`SegEnergy`,(`b`.`BE`
- `a`.`BE`)))) */;
/*!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 2009-07-04 2:17:13
############################################################
--
Rahul
Rahul
@news.eternal-september.org:
> Views have no data of their own; they only show data in tables (or other
> views).
Sure, I realize that.
> If you've backed up the view definition, you have everything. Backing
> up the view data is just duplication of effort.
My point is how does one backup the view definitions. Is that taken care by
default mysqldumps or not?
--
Rahul
Rahul
> That seems like a fairly recent version of 5.0 compared with when the
> view problems happened. Try dumping a single view with mysqldump:
>
Thanks for all your help Gordon.
One more question: Does the choice for InnoDB or MySIAM matter when backups
are made? Anything I need to be aware of?
--
Rahul
Gordon Burditt
>are made? Anything I need to be aware of?
Don't decide the table type based on how fast it can be backed up.
Otherwise, the BLACKHOLE table type starts to look pretty good. :-)
The backups will include the ENGINE=InnoDB or ENGINE=MyISAM
as appropriate on the CREATE TABLE, so the restored table will
end up having the same type as before.
There are speed differences for different table types but much more
important are differences in USING the table. Need transactions?
Use InnoDB. Need something simple, fast, without transactions, and
without enforced referential integrity? Consider MyISAM. Need
full-text indexing? Use MyISAM.
Jerry Stuckle
http://dev.mysql.com/doc/refman/5.4/en/mysqldump.html. Or
http://www.lmgtfy.com/?q=mysqldump+backup+view - check the second entry.
Or even try it and look at the dump file.