Re: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 21:51, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 14:12, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() First - sorry for confusion, according to http://dev.mysql.com/doc/mysql/en/Replication_Options.html replicate-do-db is not expected to do anything if you have replicate-wild-do-table. So you are right - it does not work as you expected. But replicate-wild-do-table should work, no matter whether you use insert into db.table values() or use db; insert into table values() Could you provide a repeatable test case to show that replicate-wild-do-table does not work ? If yes - please submit it at http://bugs.mysql.com/ Regards, Sergei I can't reproduce it anymore, I must have made something wrong that messed up the replication sooner. Sorry and thanks for your help, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Hi! On May 11, Nico Sabbi wrote: Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! On May 11, Nico Sabbi wrote: Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) Regards, Sergei I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Regards, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 14:12, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() are executed. That's why yesterday I posted my patch to mysqldump Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Nico Sabbi [EMAIL PROTECTED] wrote: Alle Wednesday 12 May 2004 14:12, hai scritto: sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() are executed. That's why yesterday I posted my patch to mysqldump Works fine for me. Do you use any other replication-* options? Which version of MySQL server do you use? -- 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: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 16:14, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Alle Wednesday 12 May 2004 14:12, hai scritto: sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() are executed. That's why yesterday I posted my patch to mysqldump Works fine for me. Do you use any other replication-* options? Which version of MySQL server do you use? mysql-4.0.18-max on both sides. this is the configuration of the slave: [mysqld] log-bin server-id=2001 master-host=master master-port=3306 master-user=replica master-password= replicate-ignore-db=mysql master-connect-retry=60 slave-skip-errors=all replicate-do-db=db replicate-wild-do-table=db.% replicate-wild-do-table=dbsetting.db% replicate-wild-do-table=image_repository.db% replicate-wild-do-table=workflow.table_categories Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 14:12, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() First - sorry for confusion, according to http://dev.mysql.com/doc/mysql/en/Replication_Options.html replicate-do-db is not expected to do anything if you have replicate-wild-do-table. So you are right - it does not work as you expected. But replicate-wild-do-table should work, no matter whether you use insert into db.table values() or use db; insert into table values() Could you provide a repeatable test case to show that replicate-wild-do-table does not work ? If yes - please submit it at http://bugs.mysql.com/ Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to dump data in db.table syntax?
Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? Another question: is there a way to dump all dbs that DON'T match a pattern without resorting to pipes / grep -v / xargs? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. Another question: is there a way to dump all dbs that DON'T match a pattern without resorting to pipes / grep -v / xargs? No, mysqldump doesn't have such option. -- 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: How to dump data in db.table syntax?
Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? Another question: is there a way to dump all dbs that DON'T match a pattern without resorting to pipes / grep -v / xargs? No, mysqldump doesn't have such option. it's a pity :( Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]