MySQL Plugin
Hi I am running collectd (http://www.collectd.org/wiki/index.php/Plugin:MySQL) version 4.9.0 on Ubuntu Linux 8.04 (Hardy) Server. Plugin mysql Database SMSPAY Host localhost User nagios Password Database smspay Socket /var/run/mysqld/mysqld1.sock # MasterStats true /Database # Database SMSIN Host localhost User nagios Password Database smsin Socket /var/run/mysql/mysqld2.sock # SlaveStats true # SlaveNotifications true /Database /Plugin # mysql -u nagios -h localhost -S /var/run/mysqld/mysqld1.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 50 Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; ++ | Database | ++ | information_schema | | SMSPAY | | mysql | ++ 3 rows in set (0.00 sec) mysql quit Bye # mysql -u nagios -h localhost -S /var/run/mysqld/mysqld2.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; ++ | Database | ++ | information_schema | | SMSIN | | mysql | ++ 3 rows in set (0.00 sec) mysql I get the below issue in the collectd.log [2009-12-21 03:12:22] mysql_real_connect failed: Access denied for user 'root'@'localhost' (using password: NO) [2009-12-21 03:12:22] read-function of plugin `mysql' failed. Will suspend it for 10 seconds. I am not able to figure out why i get r...@localhost and not nag...@localhost in the collectd.log file even after specifying it in the collectd.conf file. Please suggest/guide. Thanks, Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
help design the table
Hello, I have a table, which has a column named as domain, each domain has some IPs, each IP has two attributes: disabled,noticed. For example, the table: domainIP www.aol.com 64.12.245.203 64.12.244.203 64.12.190.33 64.12.190.1 But I don't know how to control the IP's attributes if I set this table-stru. Please help,thanks. Eva. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help design the table
Hi Eva, It seems to me that you might want to have two tables that will describe data in your database * - DOMAINS * - IPS DOMAINS table should contain two fields: * `domain_key` - auto-increment * `domain_name` - varchar(20) IPS table should contain three fields domain_key - int(10) - being a foreign key to DOMAINS table ip_number - varchar(15) ip_attribute - varchar(10) Hoping this gives you some ideas how to handle this data. Regards, Mikhail Berman Eva wrote: Hello, I have a table, which has a column named as domain, each domain has some IPs, each IP has two attributes: disabled,noticed. For example, the table: domainIP www.aol.com 64.12.245.203 64.12.244.203 64.12.190.33 64.12.190.1 But I don't know how to control the IP's attributes if I set this table-stru. Please help,thanks. Eva. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: help design the table
-Original Message- From: Mikhail Berman [mailto:mikhail...@gmail.com] Sent: Wednesday, December 23, 2009 9:16 AM To: Eva Cc: mysql@lists.mysql.com Subject: Re: help design the table Hi Eva, It seems to me that you might want to have two tables that will describe data in your database * - DOMAINS * - IPS DOMAINS table should contain two fields: * `domain_key` - auto-increment * `domain_name` - varchar(20) [JS] Quick reminder: domain names can be much longer than 20 characters. Otherwise, your suggestion is wise. Also, Eva, you should check out the MySQL functions INET_ATON(expr) and INET_NTOA(expr). Before you use them, though, check to make sure they handle IP6 addresses. If they do, then you should store your IP addresses as integers. IPS table should contain three fields domain_key - int(10) - being a foreign key to DOMAINS table ip_number - varchar(15) ip_attribute - varchar(10) Hoping this gives you some ideas how to handle this data. Regards, Mikhail Berman Eva wrote: Hello, I have a table, which has a column named as domain, each domain has some IPs, each IP has two attributes: disabled,noticed. For example, the table: domainIP www.aol.com 64.12.245.203 64.12.244.203 64.12.190.33 64.12.190.1 But I don't know how to control the IP's attributes if I set this table-stru. Please help,thanks. Eva. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table level locking when inserting auto-increment PK to InnoDB
Hey. Back to few years ago, InnoDB require table level locking when inserting auto-increment PK to the table, and Heikki said there will be a fix. Is this problem still exist now? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
HELP! RESET MASTER hosed replication
I got an alert that one of the drives was filling up (3% free). So I figured out that a large chunk was from /var/log/mysql r...@pse10:~# find / -type d -print0 | xargs -0 -n1 du -sk | sort -rn | head -n20 ~/dir-sizes.txt r...@pse10:~# cat ~/dir-sizes.txt 159121012 / 70442396 /var 70127764 /var/log 69991160 /var/log/mysql big offender 56307436 /data 31479936 /home 29386076 /data/mysql 26899784 /data/archive It looked like the /var/log/mysql was pretty full of these Mysql-bin.00 log files, ... -rw-rw 1 mysql adm 105019928 2009-12-23 05:07 mysql-bin.001196 -rw-rw 1 mysql adm 105004751 2009-12-23 05:08 mysql-bin.001197 -rw-rw 1 mysql adm 104978518 2009-12-23 05:10 mysql-bin.001198 -rw-rw 1 mysql adm 104949073 2009-12-23 05:11 mysql-bin.001199 -rw-rw 1 mysql adm 104925795 2009-12-23 05:13 mysql-bin.001200 -rw-rw 1 mysql adm 104974354 2009-12-23 05:14 mysql-bin.001201 -rw-rw 1 mysql adm 105089249 2009-12-23 05:16 mysql-bin.001202 -rw-rw 1 mysql adm 105165487 2009-12-23 05:17 mysql-bin.001203 -rw-rw 1 mysql adm 104926853 2009-12-23 05:19 mysql-bin.001204 -rw-rw 1 mysql adm 105139076 2009-12-23 05:20 mysql-bin.001205 -rw-rw 1 mysql adm 104891552 2009-12-23 05:22 mysql-bin.001206 -rw-rw 1 mysql adm 104959626 2009-12-23 05:25 mysql-bin.001207 -rw-rw 1 mysql adm 104883048 2009-12-23 05:27 mysql-bin.001208 -rw-rw 1 mysql adm 104993511 2009-12-23 05:28 mysql-bin.001209 -rw-rw 1 mysql adm 104945974 2009-12-23 05:30 mysql-bin.001210 -rw-rw 1 mysql adm 35468892 2009-12-23 05:30 mysql-bin.001211 -rw-rw 1 mysql adm 21728 2009-12-23 05:30 mysql-bin.index -rw-r- 1 mysql adm 12836 2009-12-23 00:12 mysql-slow.log ... so I took the liberty of resetting them... http://dev.mysql.com/doc/refman/5.0/en/reset.html vince...@pse10 /var/log/mysql $ dbroot (r...@localhost) [(none)] RESET MASTER; vince...@pse10 /var/log/mysql $ ll total 2792 -rw-rw 1 mysql adm 2801618 2009-12-23 05:35 mysql-bin.01 -rw-rw 1 mysql adm 32 2009-12-23 05:35 mysql-bin.index -rw-r- 1 mysql adm 14987 2009-12-23 05:35 mysql-slow.log -rw-r- 1 mysql adm1102 2009-12-22 00:13 mysql-slow.log.1.gz -rw-r- 1 mysql adm 891 2009-12-21 00:02 mysql-slow.log.2.gz -rw-r- 1 mysql adm1318 2009-12-20 00:02 mysql-slow.log.3.gz -rw-r- 1 mysql adm 687 2009-12-19 00:02 mysql-slow.log.4.gz -rw-r- 1 mysql adm5246 2009-12-17 20:38 mysql-slow.log.5.gz -rw-r- 1 mysql adm 156 2009-12-16 06:25 mysql-slow.log.6.gz -rw-r- 1 mysql adm1114 2009-12-15 16:26 mysql-slow.log.7.gz Which freed up a tremendous amount of space again... However, a co-worker informed me that now our slaves are broken and replication is hosed! What did I do wrong or forget to do? I see no mention of something I was supposed to do for replication scenarios... Was I supposed to RESET SLAVE too? As of right now, /var/log/mysql has grown to mysql-bin.28 since last night when I reset it. How do I recover from this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HELP! RESET MASTER hosed replication
That was a wrong operation to do. Anyway, issue a show master status, and set the replucation again on the slave with the new values. Next time look for 'purge binary logs'! Ciao Claudio On 23 dec 2009 21:21, Daevid Vincent dae...@daevid.com wrote: I got an alert that one of the drives was filling up (3% free). So I figured out that a large chunk was from /var/log/mysql r...@pse10:~# find / -type d -print0 | xargs -0 -n1 du -sk | sort -rn | head -n20 ~/dir-sizes.txt r...@pse10:~# cat ~/dir-sizes.txt 159121012 / 70442396 /var 70127764 /var/log 69991160 /var/log/mysql big offender 56307436 /data 31479936 /home 29386076 /data/mysql 26899784 /data/archive It looked like the /var/log/mysql was pretty full of these Mysql-bin.00 log files, ... -rw-rw 1 mysql adm 105019928 2009-12-23 05:07 mysql-bin.001196 -rw-rw 1 mysql adm 105004751 2009-12-23 05:08 mysql-bin.001197 -rw-rw 1 mysql adm 104978518 2009-12-23 05:10 mysql-bin.001198 -rw-rw 1 mysql adm 104949073 2009-12-23 05:11 mysql-bin.001199 -rw-rw 1 mysql adm 104925795 2009-12-23 05:13 mysql-bin.001200 -rw-rw 1 mysql adm 104974354 2009-12-23 05:14 mysql-bin.001201 -rw-rw 1 mysql adm 105089249 2009-12-23 05:16 mysql-bin.001202 -rw-rw 1 mysql adm 105165487 2009-12-23 05:17 mysql-bin.001203 -rw-rw 1 mysql adm 104926853 2009-12-23 05:19 mysql-bin.001204 -rw-rw 1 mysql adm 105139076 2009-12-23 05:20 mysql-bin.001205 -rw-rw 1 mysql adm 104891552 2009-12-23 05:22 mysql-bin.001206 -rw-rw 1 mysql adm 104959626 2009-12-23 05:25 mysql-bin.001207 -rw-rw 1 mysql adm 104883048 2009-12-23 05:27 mysql-bin.001208 -rw-rw 1 mysql adm 104993511 2009-12-23 05:28 mysql-bin.001209 -rw-rw 1 mysql adm 104945974 2009-12-23 05:30 mysql-bin.001210 -rw-rw 1 mysql adm 35468892 2009-12-23 05:30 mysql-bin.001211 -rw-rw 1 mysql adm 21728 2009-12-23 05:30 mysql-bin.index -rw-r- 1 mysql adm 12836 2009-12-23 00:12 mysql-slow.log ... so I took the liberty of resetting them... http://dev.mysql.com/doc/refman/5.0/en/reset.html vince...@pse10 /var/log/mysql $ dbroot (r...@localhost) [(none)] RESET MASTER; vince...@pse10 /var/log/mysql $ ll total 2792 -rw-rw 1 mysql adm 2801618 2009-12-23 05:35 mysql-bin.01 -rw-rw 1 mysql adm 32 2009-12-23 05:35 mysql-bin.index -rw-r- 1 mysql adm 14987 2009-12-23 05:35 mysql-slow.log -rw-r- 1 mysql adm1102 2009-12-22 00:13 mysql-slow.log.1.gz -rw-r- 1 mysql adm 891 2009-12-21 00:02 mysql-slow.log.2.gz -rw-r- 1 mysql adm1318 2009-12-20 00:02 mysql-slow.log.3.gz -rw-r- 1 mysql adm 687 2009-12-19 00:02 mysql-slow.log.4.gz -rw-r- 1 mysql adm5246 2009-12-17 20:38 mysql-slow.log.5.gz -rw-r- 1 mysql adm 156 2009-12-16 06:25 mysql-slow.log.6.gz -rw-r- 1 mysql adm1114 2009-12-15 16:26 mysql-slow.log.7.gz Which freed up a tremendous amount of space again... However, a co-worker informed me that now our slaves are broken and replication is hosed! What did I do wrong or forget to do? I see no mention of something I was supposed to do for replication scenarios... Was I supposed to RESET SLAVE too? As of right now, /var/log/mysql has grown to mysql-bin.28 since last night when I reset it. How do I recover from this? -- 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: HELP! RESET MASTER hosed replication
The issue is that replication relies on this logs !!!, so when you deleted them . Generally speaking you have to: stop the slave sync the master with the slave (there are several ways to do this and depending how busy is your master) grab the master status (position) change the slave to point to the master new position start the slave Hope this helps. Carlos On 12/23/2009 2:20 PM, Daevid Vincent wrote: I got an alert that one of the drives was filling up (3% free). So I figured out that a large chunk was from /var/log/mysql r...@pse10:~# find / -type d -print0 | xargs -0 -n1 du -sk | sort -rn | head -n20 ~/dir-sizes.txt r...@pse10:~# cat ~/dir-sizes.txt 159121012 / 70442396 /var 70127764 /var/log 69991160 /var/log/mysql big offender 56307436 /data 31479936 /home 29386076 /data/mysql 26899784 /data/archive It looked like the /var/log/mysql was pretty full of these Mysql-bin.00 log files, ... -rw-rw 1 mysql adm 105019928 2009-12-23 05:07 mysql-bin.001196 -rw-rw 1 mysql adm 105004751 2009-12-23 05:08 mysql-bin.001197 -rw-rw 1 mysql adm 104978518 2009-12-23 05:10 mysql-bin.001198 -rw-rw 1 mysql adm 104949073 2009-12-23 05:11 mysql-bin.001199 -rw-rw 1 mysql adm 104925795 2009-12-23 05:13 mysql-bin.001200 -rw-rw 1 mysql adm 104974354 2009-12-23 05:14 mysql-bin.001201 -rw-rw 1 mysql adm 105089249 2009-12-23 05:16 mysql-bin.001202 -rw-rw 1 mysql adm 105165487 2009-12-23 05:17 mysql-bin.001203 -rw-rw 1 mysql adm 104926853 2009-12-23 05:19 mysql-bin.001204 -rw-rw 1 mysql adm 105139076 2009-12-23 05:20 mysql-bin.001205 -rw-rw 1 mysql adm 104891552 2009-12-23 05:22 mysql-bin.001206 -rw-rw 1 mysql adm 104959626 2009-12-23 05:25 mysql-bin.001207 -rw-rw 1 mysql adm 104883048 2009-12-23 05:27 mysql-bin.001208 -rw-rw 1 mysql adm 104993511 2009-12-23 05:28 mysql-bin.001209 -rw-rw 1 mysql adm 104945974 2009-12-23 05:30 mysql-bin.001210 -rw-rw 1 mysql adm 35468892 2009-12-23 05:30 mysql-bin.001211 -rw-rw 1 mysql adm 21728 2009-12-23 05:30 mysql-bin.index -rw-r- 1 mysql adm 12836 2009-12-23 00:12 mysql-slow.log ... so I took the liberty of resetting them... http://dev.mysql.com/doc/refman/5.0/en/reset.html vince...@pse10 /var/log/mysql $ dbroot (r...@localhost) [(none)] RESET MASTER; vince...@pse10 /var/log/mysql $ ll total 2792 -rw-rw 1 mysql adm 2801618 2009-12-23 05:35 mysql-bin.01 -rw-rw 1 mysql adm 32 2009-12-23 05:35 mysql-bin.index -rw-r- 1 mysql adm 14987 2009-12-23 05:35 mysql-slow.log -rw-r- 1 mysql adm1102 2009-12-22 00:13 mysql-slow.log.1.gz -rw-r- 1 mysql adm 891 2009-12-21 00:02 mysql-slow.log.2.gz -rw-r- 1 mysql adm1318 2009-12-20 00:02 mysql-slow.log.3.gz -rw-r- 1 mysql adm 687 2009-12-19 00:02 mysql-slow.log.4.gz -rw-r- 1 mysql adm5246 2009-12-17 20:38 mysql-slow.log.5.gz -rw-r- 1 mysql adm 156 2009-12-16 06:25 mysql-slow.log.6.gz -rw-r- 1 mysql adm1114 2009-12-15 16:26 mysql-slow.log.7.gz Which freed up a tremendous amount of space again... However, a co-worker informed me that now our slaves are broken and replication is hosed! What did I do wrong or forget to do? I see no mention of something I was supposed to do for replication scenarios... Was I supposed to RESET SLAVE too? As of right now, /var/log/mysql has grown to mysql-bin.28 since last night when I reset it. How do I recover from this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HELP! RESET MASTER hosed replication
In this case it should not be needed to sync the slave, Resetting the master basically broke the 'pipe' but events are still on the new binary logs, it might have lost some event in some unfortunate case, but anyway it is probably for now to put slave back on track, check and eventually resync. Ciao Claudio On 23 dec 2009 21:35, Carlos Proal carlos.pr...@gmail.com wrote: The issue is that replication relies on this logs !!!, so when you deleted them . Generally speaking you have to: stop the slave sync the master with the slave (there are several ways to do this and depending how busy is your master) grab the master status (position) change the slave to point to the master new position start the slave Hope this helps. Carlos On 12/23/2009 2:20 PM, Daevid Vincent wrote: I got an alert that one of the drives was filling...
Re: HELP! RESET MASTER hosed replication
*Next time look for 'purge binary logs'!* Purge may also cause problem , before doing purge make sure slave is in sync with master or at least cross check slave currently which bin file getting sync. On Thu, Dec 24, 2009 at 2:05 AM, Carlos Proal carlos.pr...@gmail.comwrote: The issue is that replication relies on this logs !!!, so when you deleted them . Generally speaking you have to: stop the slave sync the master with the slave (there are several ways to do this and depending how busy is your master) grab the master status (position) change the slave to point to the master new position start the slave Hope this helps. Carlos On 12/23/2009 2:20 PM, Daevid Vincent wrote: I got an alert that one of the drives was filling up (3% free). So I figured out that a large chunk was from /var/log/mysql r...@pse10:~# find / -type d -print0 | xargs -0 -n1 du -sk | sort -rn | head -n20 ~/dir-sizes.txt r...@pse10:~# cat ~/dir-sizes.txt 159121012 / 70442396 /var 70127764 /var/log 69991160 /var/log/mysql big offender 56307436 /data 31479936 /home 29386076 /data/mysql 26899784 /data/archive It looked like the /var/log/mysql was pretty full of these Mysql-bin.00 log files, ... -rw-rw 1 mysql adm 105019928 2009-12-23 05:07 mysql-bin.001196 -rw-rw 1 mysql adm 105004751 2009-12-23 05:08 mysql-bin.001197 -rw-rw 1 mysql adm 104978518 2009-12-23 05:10 mysql-bin.001198 -rw-rw 1 mysql adm 104949073 2009-12-23 05:11 mysql-bin.001199 -rw-rw 1 mysql adm 104925795 2009-12-23 05:13 mysql-bin.001200 -rw-rw 1 mysql adm 104974354 2009-12-23 05:14 mysql-bin.001201 -rw-rw 1 mysql adm 105089249 2009-12-23 05:16 mysql-bin.001202 -rw-rw 1 mysql adm 105165487 2009-12-23 05:17 mysql-bin.001203 -rw-rw 1 mysql adm 104926853 2009-12-23 05:19 mysql-bin.001204 -rw-rw 1 mysql adm 105139076 2009-12-23 05:20 mysql-bin.001205 -rw-rw 1 mysql adm 104891552 2009-12-23 05:22 mysql-bin.001206 -rw-rw 1 mysql adm 104959626 2009-12-23 05:25 mysql-bin.001207 -rw-rw 1 mysql adm 104883048 2009-12-23 05:27 mysql-bin.001208 -rw-rw 1 mysql adm 104993511 2009-12-23 05:28 mysql-bin.001209 -rw-rw 1 mysql adm 104945974 2009-12-23 05:30 mysql-bin.001210 -rw-rw 1 mysql adm 35468892 2009-12-23 05:30 mysql-bin.001211 -rw-rw 1 mysql adm 21728 2009-12-23 05:30 mysql-bin.index -rw-r- 1 mysql adm 12836 2009-12-23 00:12 mysql-slow.log ... so I took the liberty of resetting them... http://dev.mysql.com/doc/refman/5.0/en/reset.html vince...@pse10 /var/log/mysql $ dbroot (r...@localhost) [(none)] RESET MASTER; vince...@pse10 /var/log/mysql $ ll total 2792 -rw-rw 1 mysql adm 2801618 2009-12-23 05:35 mysql-bin.01 -rw-rw 1 mysql adm 32 2009-12-23 05:35 mysql-bin.index -rw-r- 1 mysql adm 14987 2009-12-23 05:35 mysql-slow.log -rw-r- 1 mysql adm1102 2009-12-22 00:13 mysql-slow.log.1.gz -rw-r- 1 mysql adm 891 2009-12-21 00:02 mysql-slow.log.2.gz -rw-r- 1 mysql adm1318 2009-12-20 00:02 mysql-slow.log.3.gz -rw-r- 1 mysql adm 687 2009-12-19 00:02 mysql-slow.log.4.gz -rw-r- 1 mysql adm5246 2009-12-17 20:38 mysql-slow.log.5.gz -rw-r- 1 mysql adm 156 2009-12-16 06:25 mysql-slow.log.6.gz -rw-r- 1 mysql adm1114 2009-12-15 16:26 mysql-slow.log.7.gz Which freed up a tremendous amount of space again... However, a co-worker informed me that now our slaves are broken and replication is hosed! What did I do wrong or forget to do? I see no mention of something I was supposed to do for replication scenarios... Was I supposed to RESET SLAVE too? As of right now, /var/log/mysql has grown to mysql-bin.28 since last night when I reset it. How do I recover from this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
RE: HELP! RESET MASTER hosed replication
Thank you guys for the tips. I've also updated the documentation so others don't run into this: http://dev.mysql.com/doc/refman/5.0/en/reset.html -Original Message- From: prabhat kumar [mailto:aim.prab...@gmail.com] Sent: Wednesday, December 23, 2009 12:46 PM To: Carlos Proal Cc: mysql@lists.mysql.com Subject: Re: HELP! RESET MASTER hosed replication *Next time look for 'purge binary logs'!* Purge may also cause problem , before doing purge make sure slave is in sync with master or at least cross check slave currently which bin file getting sync. On Thu, Dec 24, 2009 at 2:05 AM, Carlos Proal carlos.pr...@gmail.comwrote: The issue is that replication relies on this logs !!!, so when you deleted them . Generally speaking you have to: stop the slave sync the master with the slave (there are several ways to do this and depending how busy is your master) grab the master status (position) change the slave to point to the master new position start the slave Hope this helps. Carlos On 12/23/2009 2:20 PM, Daevid Vincent wrote: I got an alert that one of the drives was filling up (3% free). So I figured out that a large chunk was from /var/log/mysql r...@pse10:~# find / -type d -print0 | xargs -0 -n1 du -sk | sort -rn | head -n20 ~/dir-sizes.txt r...@pse10:~# cat ~/dir-sizes.txt 159121012 / 70442396 /var 70127764 /var/log 69991160 /var/log/mysql big offender 56307436 /data 31479936 /home 29386076 /data/mysql 26899784 /data/archive It looked like the /var/log/mysql was pretty full of these Mysql-bin.00 log files, ... -rw-rw 1 mysql adm 105019928 2009-12-23 05:07 mysql-bin.001196 -rw-rw 1 mysql adm 105004751 2009-12-23 05:08 mysql-bin.001197 -rw-rw 1 mysql adm 104978518 2009-12-23 05:10 mysql-bin.001198 -rw-rw 1 mysql adm 104949073 2009-12-23 05:11 mysql-bin.001199 -rw-rw 1 mysql adm 104925795 2009-12-23 05:13 mysql-bin.001200 -rw-rw 1 mysql adm 104974354 2009-12-23 05:14 mysql-bin.001201 -rw-rw 1 mysql adm 105089249 2009-12-23 05:16 mysql-bin.001202 -rw-rw 1 mysql adm 105165487 2009-12-23 05:17 mysql-bin.001203 -rw-rw 1 mysql adm 104926853 2009-12-23 05:19 mysql-bin.001204 -rw-rw 1 mysql adm 105139076 2009-12-23 05:20 mysql-bin.001205 -rw-rw 1 mysql adm 104891552 2009-12-23 05:22 mysql-bin.001206 -rw-rw 1 mysql adm 104959626 2009-12-23 05:25 mysql-bin.001207 -rw-rw 1 mysql adm 104883048 2009-12-23 05:27 mysql-bin.001208 -rw-rw 1 mysql adm 104993511 2009-12-23 05:28 mysql-bin.001209 -rw-rw 1 mysql adm 104945974 2009-12-23 05:30 mysql-bin.001210 -rw-rw 1 mysql adm 35468892 2009-12-23 05:30 mysql-bin.001211 -rw-rw 1 mysql adm 21728 2009-12-23 05:30 mysql-bin.index -rw-r- 1 mysql adm 12836 2009-12-23 00:12 mysql-slow.log ... so I took the liberty of resetting them... http://dev.mysql.com/doc/refman/5.0/en/reset.html vince...@pse10 /var/log/mysql $ dbroot (r...@localhost) [(none)] RESET MASTER; vince...@pse10 /var/log/mysql $ ll total 2792 -rw-rw 1 mysql adm 2801618 2009-12-23 05:35 mysql-bin.01 -rw-rw 1 mysql adm 32 2009-12-23 05:35 mysql-bin.index -rw-r- 1 mysql adm 14987 2009-12-23 05:35 mysql-slow.log -rw-r- 1 mysql adm1102 2009-12-22 00:13 mysql-slow.log.1.gz -rw-r- 1 mysql adm 891 2009-12-21 00:02 mysql-slow.log.2.gz -rw-r- 1 mysql adm1318 2009-12-20 00:02 mysql-slow.log.3.gz -rw-r- 1 mysql adm 687 2009-12-19 00:02 mysql-slow.log.4.gz -rw-r- 1 mysql adm5246 2009-12-17 20:38 mysql-slow.log.5.gz -rw-r- 1 mysql adm 156 2009-12-16 06:25 mysql-slow.log.6.gz -rw-r- 1 mysql adm1114 2009-12-15 16:26 mysql-slow.log.7.gz Which freed up a tremendous amount of space again... However, a co-worker informed me that now our slaves are broken and replication is hosed! What did I do wrong or forget to do? I see no mention of something I was supposed to do for replication scenarios... Was I supposed to RESET SLAVE too? As of right now, /var/log/mysql has grown to mysql-bin.28 since last night when I reset it. How do I recover from this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HELP! RESET MASTER hosed replication
Yep, It might not be needed to sync, but its better to double check ;). Carlos On 12/23/2009 2:44 PM, Claudio Nanni wrote: In this case it should not be needed to sync the slave, Resetting the master basically broke the 'pipe' but events are still on the new binary logs, it might have lost some event in some unfortunate case, but anyway it is probably for now to put slave back on track, check and eventually resync. Ciao Claudio On 23 dec 2009 21:35, Carlos Proal carlos.pr...@gmail.com mailto:carlos.pr...@gmail.com wrote: The issue is that replication relies on this logs !!!, so when you deleted them . Generally speaking you have to: stop the slave sync the master with the slave (there are several ways to do this and depending how busy is your master) grab the master status (position) change the slave to point to the master new position start the slave Hope this helps. Carlos On 12/23/2009 2:20 PM, Daevid Vincent wrote: I got an alert that one of the drives was filling...
Slave is not updated
Hi, I am getting this information from slave mysql show slave status \G; *** 1. row *** Slave_IO_State: Connecting to master Master_Host: 201.107.236.77 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: ip-201-107-236-77-bin.42 Read_Master_Log_Pos: 4702 Relay_Log_File: ip-95-84-86-41-relay-bin.01 Relay_Log_Pos: 98 Relay_Master_Log_File: ip-201-107-236-77-bin.42 Slave_IO_Running: No Slave_IO_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4702 Relay_Log_Space: 98 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL The Slave_IO_Running is always No even after repeating below steps so many times stop slave CHANGE MASTER TO and start slave Please guide why this is always NO and how do i enable it ? Regards Jeetendra Ranjan
RE: Slave is not updated
You can see connecting to master means it is not able to establish the connection from slave to master, give the grants as (replication client etc..) and then try again. Thanks, Dilipkumar Database Engineering Pager mail:9884430...@nma.vodafone.in -Original Message- From: Jeetendra Ranjan [mailto:jeetendra.ran...@sampatti.com] Sent: Thursday, December 24, 2009 1:05 PM To: mysql@lists.mysql.com Subject: Slave is not updated Hi, I am getting this information from slave mysql show slave status \G; *** 1. row *** Slave_IO_State: Connecting to master Master_Host: 201.107.236.77 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: ip-201-107-236-77-bin.42 Read_Master_Log_Pos: 4702 Relay_Log_File: ip-95-84-86-41-relay-bin.01 Relay_Log_Pos: 98 Relay_Master_Log_File: ip-201-107-236-77-bin.42 Slave_IO_Running: No Slave_IO_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4702 Relay_Log_Space: 98 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL The Slave_IO_Running is always No even after repeating below steps so many times stop slave CHANGE MASTER TO and start slave Please guide why this is always NO and how do i enable it ? Regards Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
