Re: On fighting with master-slave replication lag
I would suggest a Nagios monitoring system, useful for many different checks and with plugins to check also mysql replication. Aloha! Claudio Nanni 2008/12/23 xufeng xuf...@yuanjie.net Hello everyone, In my production system, I set up MySQL 5.0.67 master/slave replication, and recently I met with master/slave replication lag problem. Is there a good monitoring tool or some other tools to detect and discover this latency on slave? Any suggestion is welcomed. Thank you in advance. Yours, Xu Feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Problem with MySQL prompt
Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/ mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example / etc/mysql.config/my50_SIA_SVILUPPO.cnf), the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it
Re: Problem with MySQL prompt
Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.itwrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf),the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it email%3astefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Re: Problem with MySQL prompt
Hi Krishna, the problem is that you use --default-file on the command line and in this mode it's working fine, I also tried, but I have the configuration of the variable default-file in the general my.cnf and I enter in MySQL in this way: mysql --socket=/tmp/mysql50_SIA_SVILUPPO.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1408 Server version: 5.0.51b-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql in my case the prompt doesn't change Il giorno 23/dic/08, alle ore 11:01, Krishna Chandra Prajapati ha scritto: Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/ mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/ mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf), the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it
Re: Problem with MySQL prompt
Hi Stefano, I believe that, different mysql configuration file for different mysql instance is a better idea. In your case, if any thing goes wrong with my.cnf Then, it is going to effect all mysql instance (either restart mysql server or any other way). It has a lots of disadvantages. You might be using mysql_multi to manage mysql instance (restart, start, stop) As, you told me. You have 1 general my.cnf and three different my.cnf for mysql instance. In one of my.cnf add and try [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' But again, if you are connecting with socket then it will not show the changed prompt. Try, to connect using defaults-file. I feel that connecting to mysql server using socket is not reading the configuration file. i mean to say the it's using default available to it. Thanks, Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 5:14 PM, Stefano Elmopi stefano.elm...@sociale.itwrote: Hi Krishna, the problem is that you use --default-file on the command line and in this mode it's working fine, I also tried, but I have the configuration of the variable default-file in the general my.cnf and I enter in MySQL in this way: mysql --socket=/tmp/mysql50_SIA_SVILUPPO.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1408 Server version: 5.0.51b-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql in my case the prompt doesn't change Il giorno 23/dic/08, alle ore 11:01, Krishna Chandra Prajapati ha scritto: Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf),the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel.
If I use iconv to convert mysql dump data file with some blob field then blob binary data will be corrupted ?
Hi, This my script to convert latin1 database to utf8 : $ mysqldump --user=root --password=password --host=mybox mydatabase -- default-character-set=latin1 mydatabase.latin1.sql$ mysqldump -- user=root --password=password --host=mybox mydatabase --default-character- set=latin1 mydatabase.latin1.sql $ sed -e 's/latin1/utf8/g' mydatabase.latin1.sql mydatabase.utf8.sql $ iconv -f latin1 -t utf8 mydatabase.utf8.sql mydatabase.utf8.sql $ echo SET NAMES utf8; tmp.sql $ cat mydatabase.utf8.sql tmp.sql $ mv tmp.sql mydatabase.utf8.sql I've one question : * if my database have some blob field with binary data (like image...), iconv convert also this data then this data will be corrupted ? Thanks for your information, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with MySQL prompt
Hi Krishna, I use the script mysql_multi to stop and the start several, or all, instances. At the end, to work with more ease, I will create scripts to connect to each instance and in the script force the variable prompt at the connection seems easy Thanks for all !!! Il giorno 23/dic/08, alle ore 13:51, Krishna Chandra Prajapati ha scritto: Hi Stefano, I believe that, different mysql configuration file for different mysql instance is a better idea. In your case, if any thing goes wrong with my.cnf Then, it is going to effect all mysql instance (either restart mysql server or any other way). It has a lots of disadvantages. You might be using mysql_multi to manage mysql instance (restart, start, stop) As, you told me. You have 1 general my.cnf and three different my.cnf for mysql instance. In one of my.cnf add and try [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' But again, if you are connecting with socket then it will not show the changed prompt. Try, to connect using defaults-file. I feel that connecting to mysql server using socket is not reading the configuration file. i mean to say the it's using default available to it. Thanks, Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 5:14 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, the problem is that you use --default-file on the command line and in this mode it's working fine, I also tried, but I have the configuration of the variable default-file in the general my.cnf and I enter in MySQL in this way: mysql --socket=/tmp/mysql50_SIA_SVILUPPO.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1408 Server version: 5.0.51b-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql in my case the prompt doesn't change Il giorno 23/dic/08, alle ore 11:01, Krishna Chandra Prajapati ha scritto: Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/ mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/ mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf), the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put
Re: If I use iconv to convert mysql dump data file with some blob field then blob binary data will be corrupted ?
I've break line misteak in my previous message, this is the fix : $ mysqldump --user=root --password=password --host=mybox mydatabase -- default-character-set=latin1 mydatabase.latin1.sql$ mysqldump -- user=root --password=password --host=mybox mydatabase --default-character- set=latin1 mydatabase.latin1.sql $ sed -e 's/latin1/utf8/g' mydatabase.latin1.sql mydatabase.utf8.sql $ iconv -f latin1 -t utf8 mydatabase.utf8.sql mydatabase.utf8.sql $ echo SET NAMES utf8; tmp.sql $ cat mydatabase.utf8.sql tmp.sql $ mv tmp.sql mydatabase.utf8.sql Regards, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: If I use iconv to convert mysql dump data file with some blob field then blob binary data will be corrupted ?
Le Tue, 23 Dec 2008 14:42:40 +, KLEIN Stéphane a écrit : Hi, This my script to convert latin1 database to utf8 : $ mysqldump --user=root --password=password --host=mybox mydatabase -- default-character-set=latin1 mydatabase.latin1.sql$ mysqldump -- user=root --password=password --host=mybox mydatabase --default-character- set=latin1 mydatabase.latin1.sql $ sed -e 's/latin1/utf8/g' mydatabase.latin1.sql mydatabase.utf8.sql $ iconv -f latin1 -t utf8 mydatabase.utf8.sql mydatabase.utf8.sql $ echo SET NAMES utf8; tmp.sql $ cat mydatabase.utf8.sql tmp.sql $ mv tmp.sql mydatabase.utf8.sql I've one question : * if my database have some blob field with binary data (like image...), iconv convert also this data then this data will be corrupted ? I've the answer ! My previous script corrupt binary data. To fix this issue I need to dump data with --hex-dump option : $ mysqldump --hex-dump --user=root --password=password --host=mybox mydatabase --default-character-set=latin1 mydatabase.latin1.sql$ mysqldump --user=root --password=password --host=mybox mydatabase Regards, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: If I use iconv to convert mysql dump data file with some blob field then blob binary data will be corrupted ?
Le Tue, 23 Dec 2008 15:33:34 +, KLEIN Stéphane a écrit : Le Tue, 23 Dec 2008 14:42:40 +, KLEIN Stéphane a écrit : Hi, This my script to convert latin1 database to utf8 : $ mysqldump --user=root --password=password --host=mybox mydatabase -- default-character-set=latin1 mydatabase.latin1.sql$ mysqldump -- user=root --password=password --host=mybox mydatabase --default-character- set=latin1 mydatabase.latin1.sql $ sed -e 's/latin1/utf8/g' mydatabase.latin1.sql mydatabase.utf8.sql $ iconv -f latin1 -t utf8 mydatabase.utf8.sql mydatabase.utf8.sql $ echo SET NAMES utf8; tmp.sql $ cat mydatabase.utf8.sql tmp.sql $ mv tmp.sql mydatabase.utf8.sql I've one question : * if my database have some blob field with binary data (like image...), iconv convert also this data then this data will be corrupted ? I've the answer ! My previous script corrupt binary data. To fix this issue I need to dump data with --hex-dump option : $ mysqldump --hex-dump --user=root --password=password --host=mybox mydatabase --default-character-set=latin1 mydatabase.latin1.sql$ mysqldump --user=root --password=password --host=mybox mydatabase Sorry, it's --hex-blob not --hex-dump $ mysqldump --hex-blob --user=root --password=password --host=mybox mydatabase --default-character-set=latin1 mydatabase.latin1.sql Regards, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: replication of databases
On Mon, Dec 22, 2008 at 6:40 PM, Baron Schwartz ba...@xaprb.com wrote: Right. Unless you have some other settings (binlog-do-db or binlog-ignore-db on the master?) that are interfering You saw this ^ right? on master: mysql show master status; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.000122 | 98 | mysql| | +--+--+--+--+ 1 row in set (0.00 sec) You need to read up on what binlog_do_db means. And you shouldn't use it. It is the problem. Indeed it was a gap in my understanding. That did the trick! Many thanks!! -Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: On fighting with master-slave replication lag
Hi I would suggest a Nagios monitoring system, useful for many different checks and with plugins to check also mysql replication. I'll second this. The standard check_mysql plugin included with Nagios allows you to monitor a MySQL slave and alert when the lag behind the master is larger than a given threshold (e.g. 600 seconds.) We had an issue last week where the slave's SQL thread died following a server failure - Nagios caught it and let us know immediately. www.nagios.org. Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Conditional Joins
Let's say I have the following tables: Plates table +++-+ | id | Name | Description | +++-+ | 1 | Paper | Blah| | 2 | Plastic| Blah| | 3 | China | Blah| | 4 | Glass | Blah| +++-+ Cups table +++-+ | id | Type | Description | +++-+ | 1 | Paper | Blah| | 2 | Mug| Blah| | 3 | Coffee | Blah| | 4 | Glass | Blah| +++-+ Flatware table +++-+ | id | Form | Description | +++-+ | 1 | Spork | Blah| | 2 | Plastic| Blah| | 3 | Antique| Blah| | 4 | Tin| Blah| +++-+ Inventory table ++++---+ | id | ItemType | ItemId | Owned | ++++---+ | 1 | PLATES | 2 | 17| | 2 | CUPS | 4 | 3 | | 3 | FLATWARE | 3 | 6 | | 4 | CUPS | 3 | 9 | | 5 | CUPS | 1 | 7 | | 6 | FLATWARE | 4 | 12| | 7 | PLATES | 1 | 1 | ++++---+ Is there a way to construct a query so that only the appropriate tables are included as a join? I'm trying to do a conditional (and more elegant) version of the following query: SELECT Inventory.id, CASE Inventory.ItemType WHEN 'PLATES' THEN Plates.Name WHEN 'CUPS' THEN Cups.Type WHEN 'FLATWARE' THEN Flatware.Form END as ItemName Inventory.ItemType, Inventory.ItemId, Inventory.Owned FROM Inventory LEFT OUTER JOIN Plates ON Inventory.ItemType = 'Plates' AND Plates.Id = Inventory.ItemId LEFT OUTER JOIN Cups ON Inventory.ItemType = 'Cups' AND Cups.Id = Inventory.ItemId LEFT OUTER JOIN Flatware ON Inventory.ItemType = 'Flatware' AND Flatware.Id = Inventory.ItemId WHERE Inventory.id IN (2, 4, 5) In the query above, the joins on both the Plates and Flatware table are superfluous because those rows are never selected. I'm not sure I can get out of specifying each possible case in column list part of the query but it seems to me like it should be possible to only join those tables that are relevant based on the conditions set in the WHERE clause. Is something like this even possible? thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct Query Problem
Hi Sonal, What information do you require ? Basically in the MasterTB we have productID and name, description. In the LookupTB we have the productID from the MasterTB, the productID from ProductTB and a char field for preferred supplier Yes/No. In the ProductTB we have a list of products from each supplier. This is link using the LookupTB. Let me know what other information you require. Thanks Neil On Tue, Dec 23, 2008 at 4:19 AM, Raghani, Sonal (IE10) sonal.ragh...@honeywell.com wrote: Hi, The problem statement needs to be elaborated. Please give the exact columns interms of wat is suppliers which table is it coming from etc. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Monday, December 22, 2008 5:16 PM To: Baron Schwartz; [MySQL] Subject: Re: Distinct Query Problem Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row -per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Lookup record with same id and match it's status
Hi, I have a table which stores log traffic. The table contains these fields: transaction_id, from, to, message, status, insertdate For example there is a message from A send to B, when the message sent to B it will insert new record. And when the message is read by B, it will also insert new record. So the records should be something like this: transaction_id, from, to, message, status, insertdate 20081224001, A, B, stest, SENT, 2008-12-24 01:01:01 20081224001, A, B, NULL, READ, 2008-12-24 01:01:03 My question is if I want to lookup 20081224001 and expect the result to be like this: transaction id, from, to, message, sent, received 20081224001, A, B, stest, 2008-12-24 01:01:01, 2008-12-24 01:01:03 How to build the query then? Please help and TIA. Willy Soap and education are not as sudden as a massacre, but they are more deadly in the long run. -- Mark Twain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: On fighting with master-slave replication lag
Hi, I use Mycat to monitor more than 300 servers using a single config file. It can be used only for replication monitoring though Regards, Chandru www.mafiree.com On Tue, Dec 23, 2008 at 10:51 PM, Andy Shellam andy-li...@networkmail.euwrote: Hi I would suggest a Nagios monitoring system, useful for many different checks and with plugins to check also mysql replication. I'll second this. The standard check_mysql plugin included with Nagios allows you to monitor a MySQL slave and alert when the lag behind the master is larger than a given threshold (e.g. 600 seconds.) We had an issue last week where the slave's SQL thread died following a server failure - Nagios caught it and let us know immediately. www.nagios.org. Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=chandru@gmail.com
Re: On fighting with master-slave replication lag
There's a couple ways to go about this. The simplest thing (and what we use in most of our simple monitoring cases) is a shell script that checks the output of show slave status\G on the slave periodically. If it reports that either thread isn't running, or there's something in Last error, or the time behind master is too long, it throws an error and alerts us in our monitoring software. We do something similar for non-replication checking... we parse the output of mysqladmin status. Slightly more complicated (and also probably more accurate- the time reported by show slave status is known to be unreliable in some cases) would be a script that inserts a row into a table, then check the slave over and over till it arrives. Or even better, insert 2 values... a timestamp that *you* provide (in a shell script, something like $(date) would work) and a timestamp generated by MySQL assuming the times are syncronized on the master, slave, and the box you're inserting from, when the insert hits the slave it'll generate it's own timestamp, which you can then subtract *your* timestamp from. There's also a tool in maatkit which does replication tracking, although I've not yet used it. Judging by the other tools in that package though, it's probably pretty decent :). Jake On Mon, Dec 22, 2008 at 8:26 PM, xufeng xuf...@yuanjie.net wrote: Hello everyone, In my production system, I set up MySQL 5.0.67 master/slave replication, and recently I met with master/slave replication lag problem. Is there a good monitoring tool or some other tools to detect and discover this latency on slave? Any suggestion is welcomed. Thank you in advance. Yours, Xu Feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jakem...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
