RE: Re: [OT] PostgreSQL / MySQL Data Dictionary
mysqldump --no-data --all-databases SNIP Eamon Daly Yeap Eamon, as mentioned MyRun is not the only utility on earth with the functionality. The difference between mysqldump and MyRun is that while MyRun includes all the mysqldump functionality, MyRun can take ANY source script. Let's make an example: mysqldump is great for backuping up complete database(s) with or without data. This is ofcourse nice, except when you have 50M records in a table, because then you get a resulting script which is huge. So essentially they both do something like this to generate the insert record sql for data backup purposes: select * from accounts; -- as an example but because you can customize the source sql script for MyRun, you can go like: select * from accounts where AccountDateYEAR(CURDATE()); -- i.e. limit the inserts you going to get to that which is really important. Also because it takes a source script, you can essentially limit the tables in a specific database to those with the important stuff in which you want to backup: --- use this-db; select * from accounts; -- Yes, important select * from orders; -- Yes, important -- select * from sessions; -- No skip this table completely select * from logs limit 0; -- Data not important, only capture schema .. -- Maybe do a little maintenance while we are busy? update accountpasswords set AccPassword=encrypt(AccPassword) where AccOpenDateCURDATE(); select * from accountpasswords; .. use that-db; select * from ...etc etc --- The logs table is a good example of such tables, it contains temporary kind of data and potentially a huge amount,ex. millions of recs. This will unnecessarily bloat the destination script file, so we limit it. Honestly, your backups is only limited by your imagination. Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] PostgreSQL / MySQL Data Dictionary
Great, MyCon produces SQL statements ready to recreate just your schema and/or all data as well, now did I miss something, or does MyCon actually write the SQL one needs to create and populate a set of system tables for the schema? PB . Nope Peter, you didn't miss a beat ;) Just to be very clear, MyCon is the front-end GUI which auto setup scripts for MyRun to use for backups or it can also optionally schedule MyRun to auto execute these source scripts. The end-user hardly ever sees MyRun (except when the OS task scheduler fires it up). All the end-user ever does, is to click on a database (or a specific table) and click Backup, the rest just happens. MyRun is the commandline utility which actually does the hard yards. And to just state it again so there is no confusion; MyRun's target script is fully capable of recreating the full schema as well as populate it with data (insert statements) from whatever was selected in the source script. NOTES: * MyRun can also do this from a remote MySQL server. * It can also execute the target script against another mysql server/database instead of to a script file * MySQL V3.23 and higher * The target script can optionally contain USE db;, DROP table if exists tb1;, CREATE table if not exists tb1 ... and your data using INSERT into tb1 (..columns..) values ((...record1...),(...record2...),..); in 100xrecord batches (so a 1,000 records will be contained in only 10 insert statements). These batch inserts are used because from the MySQL Manual: This is much faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to non-empty table, you may tune up the bulk_insert_buffer_size variable to make it even faster. EXAMPLE: Every morning we have a scheduled MyRun which backups up our company's MySQL webdata from our remote webhost/ISP's MySQL server to our local network using a ADSL connection. It contains tens of tables with thousands of records each and from start to finish takes approx. 10 seconds (our webhost and we are on different continents). And to set all this up initially took a massive.. one click! If our ISP drops our database by mistake, it will take us all of two seconds to recreate a complete snapshot of the database using the latest target script. Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] PostgreSQL / MySQL Data Dictionary
SciBit MySQL Team [EMAIL PROTECTED] wrote: select * from accounts; -- as an example but because you can customize the source sql script for MyRun, you can go like: select * from accounts where AccountDateYEAR(CURDATE()); -- i.e. limit the inserts you going to get to that which is really important. mysqldump also supports it: -w, --where=nameDump only selected records; QUOTES mandatory! :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [OT] PostgreSQL / MySQL Data Dictionary
-w, --where=nameDump only selected records; QUOTES mandatory! :) The more options the merrier for MySQL and the end-users :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] PostgreSQL / MySQL Data Dictionary
On Wed, 11 Aug 2004 17:20:45 +0200 Jochem van Dieten [EMAIL PROTECTED] wrote: SHOW TABLES does not make sense. How are you going to join the output of SHOW TABLES against the output of SHOW COLUMNS and SHOW INDEXES? SELECT * FROM INFORMATION_SCHEMA.TABLES makes sense. And as for easy remembering: I prefer to remember just one standard, instead of the idiosyncracies of each product. Yes, a queryable (sp?) set of dictionary tables/views would be nice for doing this. The MySQL set of SHOW commands is pretty painful for any serious development. Does PostgreSQL have a set of information schema tables to query against like Oracle does (e.g. SELECT table_name FROM user_tables)? I noticed this from a quick google search: http://gborg.postgresql.org/project/orapgsqlviews/projdisplay.php Is something similar planned for MySQL in the future? Doesn't appear to be from: http://dev.mysql.com/doc/mysql/en/Roadmap.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] PostgreSQL / MySQL Data Dictionary
On Wed, 11 Aug 2004 11:04:01 -0500, Josh Trutwin wrote: On Wed, 11 Aug 2004 17:20:45 +0200 Jochem van Dieten wrote: SHOW TABLES does not make sense. How are you going to join the output of SHOW TABLES against the output of SHOW COLUMNS and SHOW INDEXES? SELECT * FROM INFORMATION_SCHEMA.TABLES makes sense. And as for easy remembering: I prefer to remember just one standard, instead of the idiosyncracies of each product. Yes, a queryable (sp?) set of dictionary tables/views would be nice for doing this. The MySQL set of SHOW commands is pretty painful for any serious development. Does PostgreSQL have a set of information schema tables to query against like Oracle does (e.g. SELECT table_name FROM user_tables)? It does have a system that is pretty comparable to what is in the the SQL standard. There is a pg_catalog schema that contains the base tables (information about database objects in PostgreSQL is stored in normal tables). Although not recommended (to say the least), these base tables can even be updated using SQL. As a queryable front end there is a read-only information_schema schema that contains many views describing the objects in the database. It is somewhat similar to what Oracle has, but it is exactly the same as what the SQL standard has. Since MS SQL Server implements the same part of the SQL standard metadata queries are quite portable between PostgreSQL and MS SQL Server. I noticed this from a quick google search: http://gborg.postgresql.org/project/orapgsqlviews/projdisplay.php Is something similar planned for MySQL in the future? Doesn't appear to be from: http://dev.mysql.com/doc/mysql/en/Roadmap.html MySQL strives to ful SQL standard compliance (ISO/IEC 9075), so an INFORMATION_SCHEMA must be planned for someday. However, that day does not appear to be in the near future. I wonder how far one would be able to mimic one once MySQL 5.1 is out by creating a database named INFORMATION_SCHEMA, which contains views which call all the SHOW commands. That would probably need some serious work to make the SHOW commands joinable, but the naming convention might work. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] PostgreSQL / MySQL Data Dictionary
SHOW TABLES does not make sense. How are you going to join the output of SHOW TABLES against the output of SHOW COLUMNS and SHOW INDEXES? In MySQL, by parsing the output of SHOW CREATE TABLE. It would be a boon if someone were to write a utility, in an OS-independent language, which does that parsing for all tables in a MySQL database and returns SQL output that's suitable for creating a set of system tables. Obviously bits of this task have already been accomplished by the folks who wrote the MySQL module in PHP, for example, and for all we know, much of the code for doing it may already exist in phpMyAdmin. PB
RE: Re: [OT] PostgreSQL / MySQL Data Dictionary
In MySQL, by parsing the output of SHOW CREATE TABLE. It would be a boon if someone were to write a utility, in an OS-independent language, which does that parsing for all tables in a MySQL database and returns SQL output that's suitable for creating a set of system tables. Obviously bits of this task have already been accomplished by the folks who wrote the MySQL module in PHP, for example, and for all we know, much of the code for doing it may already exist in phpMyAdmin. PB Well it is not OS-independent, nor free, but the part that does the job you wish for is actually free and can easily become OS independent. MyCon (http://www.scibit.com/products/mycon) comes with a commandline utility called MyRun which it uses in the background to backup databases or single tables or groups of tables or general scripts' data or to schedule these tasks with. It has full helpfile on the command-line parameters and options, some of the output script options can optionally include drop if exists statements, create table and the actual data, all neatly parsed into SQL statements ready to recreate just your schema and/or all data as well. It can also dump to another server live instead of to an output script file. It has been optimized to dump an average size table's data at roughly 1000recs/sec on a 1xCPU3GHz with MySQL running localhost using only enough client-side memory for a single record at any given time. In short is takes any source script (which you can setup yourself or use MyCon to auto create it for you),ex: --- select * from accounts; SNIP...etc select * from visits left join countries using (CountryID); select * from servicerequests where ServDateYEAR(CURDATE()); SNIP...etc --- And then produces something like this: -- MyRun -- Source Server: SNIPPED -- Source Script: Tables.Scheduled.Run.All.sql -- Target Script: Tables.Scheduled.Backup.All.sql -- Target:Tables.Scheduled.Backup.All.sql -- Date: 2004-08-11 09:00:02 500 -- SCRIPT SQL: select * from accounts; -- DROP: accounts drop table if exists accounts; -- CREATE: accounts create table if not exists `accounts` ( `AccountID` int(10) NOT NULL auto_increment, `AccountEmail` varchar(50) default NULL, SNIP..etc PRIMARY KEY (`AccountID`), KEY `AccountStatusID` (`AccountStatusID`), KEY `AccountTypeID` (`AccountTypeID`) ) TYPE=MyISAM; -- DATA: accounts BATCH #1 insert into accounts (AccountID,AccountEmail,SNIP..etc) values SNIP it then proceeds to add batches of 100 recs per INSERT statement -- DATA: accounts BATCH #2 SNIP..etc As said, the DROP, CREATE and DATA are all optional. MyRun is ofcourse not the only utility to do this, mysqldump has been around forever and just about every other MySQL GUI includes this type of functionality. That said, if there is really a demand for something like this, I am sure we can put MyRun into both a linux and win32 CGI which can then be prompted from a website. MyRun's source is OS independent, so this won't be an issue. The current version is win32 and although the scripting side of it is really small, it got a bit bloated because it also includes code to generate live PDF,XLS,RTF,etc reports from MySQL data, which means the report modules made it heavy. So a cgi would more than likely fall into the 200Kb footprint range, instead of 1Mb. Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] PostgreSQL / MySQL Data Dictionary
I couldn't follow the threading for this one, but you can quickly and easily dump the table structures for all or part of your MySQL databases with mysqldump: mysqldump --no-data --all-databases This dumps all the database and table creates thusly: -- -- Current Database: test -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ test; USE test; -- -- Table structure for table `DIALUP` -- CREATE TABLE DIALUP ( Full_Name varchar(40) NOT NULL default '', Framed_IP_Address varchar(16) NOT NULL default '' ) TYPE=MyISAM; -- -- Table structure for table `DSL` -- CREATE TABLE DSL ( Full_Name varchar(40) NOT NULL default '', Framed_IP_Address varchar(16) NOT NULL default '' ) TYPE=MyISAM; et cetera. Eamon Daly - Original Message - From: SciBit MySQL Team [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 11, 2004 4:34 PM Subject: RE: Re: [OT] PostgreSQL / MySQL Data Dictionary In MySQL, by parsing the output of SHOW CREATE TABLE. It would be a boon if someone were to write a utility, in an OS-independent language, which does that parsing for all tables in a MySQL database and returns SQL output that's suitable for creating a set of system tables. Obviously bits of this task have already been accomplished by the folks who wrote the MySQL module in PHP, for example, and for all we know, much of the code for doing it may already exist in phpMyAdmin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
