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]
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]
