Re: (mysqldump) Serial output. . .?
Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in order for the changes in one table to be able to apply to the other? Isn't that why you are worried about capturing your schemas as ALTER TABLE statements? With SBR, each time a table is altered on the replication master, that ALTER TABLE statement is inserted in the binlog so that the change propagates to the slaves. The DML (data modification language) statements that follow the ALTER TABLE statement in the binlog won't fail because they will be applied to the correct schema on the slave. Again, my sincere apologies for missing the difference in the purpose of what you are trying to do. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/20/2006 01:36:18 PM: Sheeri, Thanks very much for the reply. However, that is not what I'm looking for. I don't want the tables that *have been* altered. I want CREATE TABLE statements output in the ALTER TABLE format for re-creation on another system (clean or otherwise). Regards, Michael -- Original Message -- From: sheeri kritzer [EMAIL PROTECTED] Date: Mon, 20 Feb 2006 12:22:55 -0500 mysqldump takes a table or database and dumps it -- current schema, current data. You won't get alter tables. What you want is something that will show all the alter statements. You can run something like this on unix: tail -f binlog* | grep ALTER alter.sql and then the alter.sql text file will always have the alter statements. The binary log captures the alter statements. Or, you could create an 'alteration' table with a text field and timestamp, and have a trigger copy the alter statement to the alteration table. But mysqldump is the wrong solution, because it only dumps now. hope this helps! -Sheeri On 2/16/06, mwilliams [EMAIL PROTECTED] wrote: All, I'm looking to output every piece of data from the database line by line. Is there any methody by which 'mysqldump' can output the following?: use MY_DATABASE; CREATE TABLE IF NOT EXISTS MY_TABLE; ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . The most important of the features above are the ability to CREATE a table only if it doesn't exist (I never want to drop because the same script will be used for syncing) and the ability to have 'mysqldump' be smart and output ALTER IGNORE statements. Any asistance would be greatly appreciated. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (mysqldump) Serial output. . .?
Shawn, Thanks for the reply, but I think the thread has become much more dramatic than nececessary. Basically, I'm not looking for what has been ALTERed. I simply need the table creation data output in ALTER IGNORE (or whatever is appropriate) format so as to ensure table structure is the same before performing any INSERTS. I don't wish to DROP tables because that would then require reimporting all data. I simply want to write a tool to perform updates between multiple databases that keep them in two-way sync with their respective DBs (and even then, only specific tables) on a main server, both structure-wise and data-wise. The current systems don't use bin logs (*eyes rolling in back of head*) and we don't need replication, but true two-way syncing. Are there any truly quality two-way replication master techniques that you can recommend? I've been working with SJA and I like it pretty well. I'd like to write my own, similar program, but I think it might just work for now. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 11:18:44 -0500 Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in order for the changes in one table to be able to apply to the other? Isn't that why you are worried about capturing your schemas as ALTER TABLE statements? With SBR, each time a table is altered on the replication master, that ALTER TABLE statement is inserted in the binlog so that the change propagates to the slaves. The DML (data modification language) statements that follow the ALTER TABLE statement in the binlog won't fail because they will be applied to the correct schema on the slave. Again, my sincere apologies for missing the difference in the purpose of what you are trying to do. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/20/2006 01:36:18 PM: Sheeri, Thanks very much for the reply. However, that is not what I'm looking for. I don't want the tables that *have been* altered. I want CREATE TABLE statements output in the ALTER TABLE format for re-creation on another system (clean or otherwise). Regards, Michael -- Original Message -- From: sheeri kritzer [EMAIL PROTECTED] Date: Mon, 20 Feb 2006 12:22:55 -0500 mysqldump takes a table or database and dumps it -- current schema, current data. You won't get alter tables. What you want is something that will show all the alter statements. You can run something like this on unix: tail -f binlog* | grep ALTER alter.sql and then the alter.sql text file will always have the alter statements. The binary log captures the alter statements. Or, you could create an 'alteration' table with a text field and timestamp, and have a trigger copy the alter statement to the alteration table. But mysqldump is the wrong solution, because it only dumps now. hope this helps! -Sheeri On 2/16/06, mwilliams [EMAIL PROTECTED] wrote: All, I'm looking to output every piece of data from the database line by line. Is there any methody by which 'mysqldump' can output the following?: use MY_DATABASE; CREATE TABLE IF NOT EXISTS MY_TABLE; ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . The most important of the features above are the ability to CREATE a table only if it doesn't exist (I never want to drop because the same script will be used for syncing) and the ability to have 'mysqldump' be smart and output ALTER IGNORE statements. Any asistance would be greatly appreciated. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: (mysqldump) Serial output. . .?
One problem with dual-master or multi-master replication is that you have to be able to set and check a lock across all masters before performing a schema change. How would you deal with this scenario using your ALTER TABLE database dumps without such a lock? Server A and B share a table X that has the following definition CREATE TABLE X ( id int auto_increment ,name varchar(20) not null ,status tinyint ) Simultaneously, separate changes are applied to table X on servers A and B with the following statements: SERVER A: ALTER TABLE X CHANGE status status tinyint unsigned; SERVER B: ALTER TABLE X CHANGE status status int; Without some way to serialize those changes you could possibly get stuck in an endless loop. 1) Server A's sync process detects B's change and applies it. B's sync process detects A's changes and applies them to itself. 2) Now both tables (A.X and B.X) are different again. Synchronization attempts to match schemas again. Repeat step 1) until someone wins. Question: What should be the definition of X on both servers at that point? Which change should have precedence? /end scenario NDB (clustering) is the only MySQL database that supports distributed locking and distributed transactions (making sure that at any one time all replicas of the data are kept in sync across the cluster). SBR replication takes care of the circular reference problem by tagging each DML statement with the originating server. If a server detects that it is attempting to process a statement that it already applied to itself, it quits and moves on to the next statement. Clustering can use both SBR and RBR replication (RBR = row-based replication or row-by-row) No, I do not know of any good system for two-way synching (other than NDB) built on top of MySQL. Again, you haven't explained why your synching plan is that much different than setting up circular replication. In circular replication server A is the master to B and B is the master of A. This is a useful design if you can ensure that you can somehow ensure that each server only issues private id values so that your records remain unique throughout your enterprise. Schema changes must occur with great care. I have worked with several different replicating database servers (MySQL, MS SQL server, Lotus Notes) and each have a different way of handling what they call replication conflicts. Those arise from scenarios very similar to what I described above (changes occur to the same record on separate servers between synchronization cycles). How do you plan to handle those? Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 12:12:42 PM: Shawn, Thanks for the reply, but I think the thread has become much more dramatic than nececessary. Basically, I'm not looking for what has been ALTERed. I simply need the table creation data output in ALTER IGNORE (or whatever is appropriate) format so as to ensure table structure is the same before performing any INSERTS. I don't wish to DROP tables because that would then require reimporting all data. I simply want to write a tool to perform updates between multiple databases that keep them in two-way sync with their respective DBs (and even then, only specific tables) on a main server, both structure-wise and data-wise. The current systems don't use bin logs (*eyes rolling in back of head*) and we don't need replication, but true two-way syncing. Are there any truly quality two-way replication master techniques that you can recommend? I've been working with SJA and I like it pretty well. I'd like to write my own, similar program, but I think it might just work for now. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 11:18:44 -0500 Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in order for the changes in one table to be able to apply to the other? Isn't that why you are worried about capturing your schemas as ALTER TABLE statements? With SBR, each time a table is altered on the replication master, that ALTER TABLE statement is inserted in the binlog so that the change propagates to the slaves. The DML (data modification language) statements that follow the ALTER TABLE statement in the binlog won't fail because they will be applied to the correct schema on the slave. Again, my sincere apologies for missing the difference in the purpose of what you are trying to do. Respectfully, Shawn
Re: (mysqldump) Serial output. . .?
Ah, well, in this particular DB, *every single transaction* it's it's own entry. . .basically the DB itself is a binary log. . .kinda. . .sorta. So the current value of a particular item isn't necessarily an issue since, once entered, it will always be the same. A change to that value will in itself be a transaction with its own UUID. As for your question regarding how it's any different, I'm not really sure, besides the need to have the server go down or be locked for a period of time. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 12:51:52 -0500 One problem with dual-master or multi-master replication is that you have to be able to set and check a lock across all masters before performing a schema change. How would you deal with this scenario using your ALTER TABLE database dumps without such a lock? Server A and B share a table X that has the following definition CREATE TABLE X ( id int auto_increment ,name varchar(20) not null ,status tinyint ) Simultaneously, separate changes are applied to table X on servers A and B with the following statements: SERVER A: ALTER TABLE X CHANGE status status tinyint unsigned; SERVER B: ALTER TABLE X CHANGE status status int; Without some way to serialize those changes you could possibly get stuck in an endless loop. 1) Server A's sync process detects B's change and applies it. B's sync process detects A's changes and applies them to itself. 2) Now both tables (A.X and B.X) are different again. Synchronization attempts to match schemas again. Repeat step 1) until someone wins. Question: What should be the definition of X on both servers at that point? Which change should have precedence? /end scenario NDB (clustering) is the only MySQL database that supports distributed locking and distributed transactions (making sure that at any one time all replicas of the data are kept in sync across the cluster). SBR replication takes care of the circular reference problem by tagging each DML statement with the originating server. If a server detects that it is attempting to process a statement that it already applied to itself, it quits and moves on to the next statement. Clustering can use both SBR and RBR replication (RBR = row-based replication or row-by-row) No, I do not know of any good system for two-way synching (other than NDB) built on top of MySQL. Again, you haven't explained why your synching plan is that much different than setting up circular replication. In circular replication server A is the master to B and B is the master of A. This is a useful design if you can ensure that you can somehow ensure that each server only issues private id values so that your records remain unique throughout your enterprise. Schema changes must occur with great care. I have worked with several different replicating database servers (MySQL, MS SQL server, Lotus Notes) and each have a different way of handling what they call replication conflicts. Those arise from scenarios very similar to what I described above (changes occur to the same record on separate servers between synchronization cycles). How do you plan to handle those? Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 12:12:42 PM: Shawn, Thanks for the reply, but I think the thread has become much more dramatic than nececessary. Basically, I'm not looking for what has been ALTERed. I simply need the table creation data output in ALTER IGNORE (or whatever is appropriate) format so as to ensure table structure is the same before performing any INSERTS. I don't wish to DROP tables because that would then require reimporting all data. I simply want to write a tool to perform updates between multiple databases that keep them in two-way sync with their respective DBs (and even then, only specific tables) on a main server, both structure-wise and data-wise. The current systems don't use bin logs (*eyes rolling in back of head*) and we don't need replication, but true two-way syncing. Are there any truly quality two-way replication master techniques that you can recommend? I've been working with SJA and I like it pretty well. I'd like to write my own, similar program, but I think it might just work for now. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 11:18:44 -0500 Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in
Re: (mysqldump) Serial output. . .?
Ok, I get that. I have several tables just like that (I use mine as shadow tables for change audits. Every change to the normal table ends up creating new record in the shadow table thus documenting each state of the normal table through time). However, shouldn't schema changes be very rare under such a design as yours? Such infrequent modifications deserve any special attention it would take to convert the output of a SHOW CREATE TABLE xxx into a sequence of ALTER TABLE statements within whichever programming language you are using to control the synchronization. I mean it's not hard to take the output of a SHOW CREATE TABLE xxx statement and transform it into a sequence of ALTER TABLE statements. Alternatively, you could use the output from a SHOW COLUMNS FROM xxx statement as the important parts are already parsed into separate columns (column name, data type, null or not null, etc.). If the application on A is changed to use a new table design (X2) and that schema change is sent to Server B, how does the application on Server B not break ? In particular, if you created X2 by deleting a column from X, that would cause major problems with the application running on B until B is notified of the change from X to X2, wouldn't it? I am not trying to discourage your design or your plan of attack. I am just trying to help by playing devil's advocate. If you plan works well, it may be something many of us in the community may be interested in trying for some of our data issues, if you can share. We understand if you can't. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 01:08:52 PM: Ah, well, in this particular DB, *every single transaction* it's it's own entry. . .basically the DB itself is a binary log. . . kinda. . .sorta. So the current value of a particular item isn't necessarily an issue since, once entered, it will always be the same. A change to that value will in itself be a transaction with its own UUID. As for your question regarding how it's any different, I'm not really sure, besides the need to have the server go down or be locked for a period of time. Regards, Michael snipped
Re: (mysqldump) Serial output. . .?
Yes, schema changes would/should be rare. You may be right, but I would think that since schema information is *alway* checked before any INSERTS then we should be good. There should never be an occasion to break per se. Obviously anything can happen, and appropriate recovery methods (e.g. via reguar dumps) would be in place, but I don't necessarily see that happening often, if at all. All excellent points though! Regards -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 13:29:22 -0500 Ok, I get that. I have several tables just like that (I use mine as shadow tables for change audits. Every change to the normal table ends up creating new record in the shadow table thus documenting each state of the normal table through time). However, shouldn't schema changes be very rare under such a design as yours? Such infrequent modifications deserve any special attention it would take to convert the output of a SHOW CREATE TABLE xxx into a sequence of ALTER TABLE statements within whichever programming language you are using to control the synchronization. I mean it's not hard to take the output of a SHOW CREATE TABLE xxx statement and transform it into a sequence of ALTER TABLE statements. Alternatively, you could use the output from a SHOW COLUMNS FROM xxx statement as the important parts are already parsed into separate columns (column name, data type, null or not null, etc.). If the application on A is changed to use a new table design (X2) and that schema change is sent to Server B, how does the application on Server B not break ? In particular, if you created X2 by deleting a column from X, that would cause major problems with the application running on B until B is notified of the change from X to X2, wouldn't it? I am not trying to discourage your design or your plan of attack. I am just trying to help by playing devil's advocate. If you plan works well, it may be something many of us in the community may be interested in trying for some of our data issues, if you can share. We understand if you can't. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 01:08:52 PM: Ah, well, in this particular DB, *every single transaction* it's it's own entry. . .basically the DB itself is a binary log. . . kinda. . .sorta. So the current value of a particular item isn't necessarily an issue since, once entered, it will always be the same. A change to that value will in itself be a transaction with its own UUID. As for your question regarding how it's any different, I'm not really sure, besides the need to have the server go down or be locked for a period of time. Regards, Michael snipped -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (mysqldump) Serial output. . .?
mysqldump takes a table or database and dumps it -- current schema, current data. You won't get alter tables. What you want is something that will show all the alter statements. You can run something like this on unix: tail -f binlog* | grep ALTER alter.sql and then the alter.sql text file will always have the alter statements. The binary log captures the alter statements. Or, you could create an 'alteration' table with a text field and timestamp, and have a trigger copy the alter statement to the alteration table. But mysqldump is the wrong solution, because it only dumps now. hope this helps! -Sheeri On 2/16/06, mwilliams [EMAIL PROTECTED] wrote: All, I'm looking to output every piece of data from the database line by line. Is there any methody by which 'mysqldump' can output the following?: use MY_DATABASE; CREATE TABLE IF NOT EXISTS MY_TABLE; ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . The most important of the features above are the ability to CREATE a table only if it doesn't exist (I never want to drop because the same script will be used for syncing) and the ability to have 'mysqldump' be smart and output ALTER IGNORE statements. Any asistance would be greatly appreciated. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (mysqldump) Serial output. . .?
Sheeri, Thanks very much for the reply. However, that is not what I'm looking for. I don't want the tables that *have been* altered. I want CREATE TABLE statements output in the ALTER TABLE format for re-creation on another system (clean or otherwise). Regards, Michael -- Original Message -- From: sheeri kritzer [EMAIL PROTECTED] Date: Mon, 20 Feb 2006 12:22:55 -0500 mysqldump takes a table or database and dumps it -- current schema, current data. You won't get alter tables. What you want is something that will show all the alter statements. You can run something like this on unix: tail -f binlog* | grep ALTER alter.sql and then the alter.sql text file will always have the alter statements. The binary log captures the alter statements. Or, you could create an 'alteration' table with a text field and timestamp, and have a trigger copy the alter statement to the alteration table. But mysqldump is the wrong solution, because it only dumps now. hope this helps! -Sheeri On 2/16/06, mwilliams [EMAIL PROTECTED] wrote: All, I'm looking to output every piece of data from the database line by line. Is there any methody by which 'mysqldump' can output the following?: use MY_DATABASE; CREATE TABLE IF NOT EXISTS MY_TABLE; ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . The most important of the features above are the ability to CREATE a table only if it doesn't exist (I never want to drop because the same script will be used for syncing) and the ability to have 'mysqldump' be smart and output ALTER IGNORE statements. Any asistance would be greatly appreciated. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Dirk, My fear is that the output I must massage won't be 100% consistent across different Linux distros. It would make me feel all warm and fuzzy if I knew I could count on a format that would simply give me what I needed when I needed (pie in the sky, right?). Anywho, I probbaly will just end up 'parsing' and formatting the output. Thanks, Michael -- Original Message -- From: Dirk Bremer [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 14:51:20 -0600 Rather than changing the function of the mysqldump program, why not massage its output to your specifications. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: mwilliams [mailto:[EMAIL PROTECTED] Sent: Thursday, February 16, 2006 14:44 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: (mysqldump) Serial output. . .? Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Mysqldump can do all the following except dump the ALTER commands, it will log the create statement of the table as the table exists. Mysql doesn't keep a record of what, when, how a table was altered only the final result. Look at the mysqldump options for the stuff you want to do by typing mysqldump --help :~ -Original Message- :~ From: mwilliams [mailto:[EMAIL PROTECTED] :~ Sent: Thursday, February 16, 2006 11:50 AM :~ To: mysql@lists.mysql.com :~ Subject: (mysqldump) Serial output. . .? :~ :~ All, :~ :~ I'm looking to output every piece of data from the database line by :~ line. Is there any :~ methody by which 'mysqldump' can output the following?: :~ :~ :~ use MY_DATABASE; :~ :~ CREATE TABLE IF NOT EXISTS MY_TABLE; :~ :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ :~ :~ :~ The most important of the features above are the ability to CREATE a :~ table only if it doesn't :~ exist (I never want to drop because the same script will be used for :~ syncing) and the ability to :~ have 'mysqldump' be smart and output ALTER IGNORE statements. Any :~ asistance would be :~ greatly appreciated. :~ :~ Regards, :~ Michael :~ :~ -- :~ MySQL General Mailing List :~ For list archives: http://lists.mysql.com/mysql :~ To unsubscribe:http://lists.mysql.com/mysql?unsub=dathan- :~ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Thanks Dathan, But I'm more concerned with ALTER than anything. As I said, I basically want to be able to output field, type, etc. by row so that *if* the receiving DB needs to add a field it can do so without having to nuke the table with all data in it just to add all fields again with a CREATE statement. The final purpose is for the sake of data syncronization, not replication. Regards, Michael -- Original Message -- From: Dathan V. Pattishall [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 12:06:40 -0800 Mysqldump can do all the following except dump the ALTER commands, it will log the create statement of the table as the table exists. Mysql doesn't keep a record of what, when, how a table was altered only the final result. Look at the mysqldump options for the stuff you want to do by typing mysqldump --help :~ -Original Message- :~ From: mwilliams [mailto:[EMAIL PROTECTED] :~ Sent: Thursday, February 16, 2006 11:50 AM :~ To: mysql@lists.mysql.com :~ Subject: (mysqldump) Serial output. . .? :~ :~ All, :~ :~ I'm looking to output every piece of data from the database line by :~ line. Is there any :~ methody by which 'mysqldump' can output the following?: :~ :~ :~ use MY_DATABASE; :~ :~ CREATE TABLE IF NOT EXISTS MY_TABLE; :~ :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ :~ :~ :~ The most important of the features above are the ability to CREATE a :~ table only if it doesn't :~ exist (I never want to drop because the same script will be used for :~ syncing) and the ability to :~ have 'mysqldump' be smart and output ALTER IGNORE statements. Any :~ asistance would be :~ greatly appreciated. :~ :~ Regards, :~ Michael :~ :~ -- :~ MySQL General Mailing List :~ For list archives: http://lists.mysql.com/mysql :~ To unsubscribe:http://lists.mysql.com/mysql?unsub=dathan- :~ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/16/2006 03:22:51 PM: Thanks Dathan, But I'm more concerned with ALTER than anything. As I said, I basically want to be able to output field, type, etc. by row so that *if* the receiving DB needs to add a field it can do so without having to nuke the table with all data in it just to add all fields again with a CREATE statement. The final purpose is for the sake of data syncronization, not replication. Regards, Michael -- Original Message -- From: Dathan V. Pattishall [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 12:06:40 -0800 Mysqldump can do all the following except dump the ALTER commands, it will log the create statement of the table as the table exists. Mysql doesn't keep a record of what, when, how a table was altered only the final result. Look at the mysqldump options for the stuff you want to do by typing mysqldump --help :~ -Original Message- :~ From: mwilliams [mailto:[EMAIL PROTECTED] :~ Sent: Thursday, February 16, 2006 11:50 AM :~ To: mysql@lists.mysql.com :~ Subject: (mysqldump) Serial output. . .? :~ :~ All, :~ :~ I'm looking to output every piece of data from the database line by :~ line. Is there any :~ methody by which 'mysqldump' can output the following?: :~ :~ :~ use MY_DATABASE; :~ :~ CREATE TABLE IF NOT EXISTS MY_TABLE; :~ :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] :~ :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ INSERT IGNORE . . . . :~ :~ :~ :~ The most important of the features above are the ability to CREATE a :~ table only if it doesn't :~ exist (I never want to drop because the same script will be used for :~ syncing) and the ability to :~ have 'mysqldump' be smart and output ALTER IGNORE statements. Any :~ asistance would be :~ greatly appreciated. :~ :~ Regards, :~ Michael :~ :~ -- :~ MySQL General Mailing List :~ For list archives: http://lists.mysql.com/mysql :~ To unsubscribe:http://lists.mysql.com/mysql?unsub=dathan- :~ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Hi Michael, Have you considered a Master-Master (or more if required) replication setup for achieving what you mentioned below? That would certainly provide the I'll show you mine if you show me yours scenario. I can recommend the High Performance MySQL book by Jeremy Zawodny as it has an example of the setup required. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: mwilliams [mailto:[EMAIL PROTECTED] Sent: Friday, 17 February 2006 7:14 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: (mysqldump) Serial output. . .? Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Rather than changing the function of the mysqldump program, why not massage its output to your specifications. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: mwilliams [mailto:[EMAIL PROTECTED] Sent: Thursday, February 16, 2006 14:44 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: (mysqldump) Serial output. . .? Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
