Force index command in query
Hi, After analysing slow query log i found that some queries are not using index and so i used the force index command in query and test it and now it starts using index properly.Accordingly i implemented the same query with force index in my application code and regeneratet the slow query log. Now i found that the same queries having force index clause are againg not using index and surprisingly its starting using index without any force index clause. Please suggest how it happened and should i continue with the force index command in that query or remove the force index clause from those queries ? Thanks Regards Jeetendra Ranjan
innodb_data_file_path
Hi, I am using MySQL 5.0.85-community on Fedora 8. When i set innodb_data_home_dir = innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw and restart the server it gives an error Starting MySQL.Manager of pid-file quit without updating file.[FAILED] Please suggest how to resolve this issue ? Thanks 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
Re: innodb_data_file_path
Thanks Suresh for your response. MySQL is already installed and will update you with the error log details. Thanks Jeetendra Ranjan - Original Message - From: Suresh Kuna To: Jeetendra Ranjan Cc: mysql@lists.mysql.com Sent: Monday, January 04, 2010 10:28 AM Subject: Re: innodb_data_file_path Hi Jeetendra, What is the error it it saying in error log ? Is this the fresh installation or already installed MySQL ? Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 10:16 AM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I am using MySQL 5.0.85-community on Fedora 8. When i set innodb_data_home_dir = innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw and restart the server it gives an error Starting MySQL.Manager of pid-file quit without updating file.[FAILED] Please suggest how to resolve this issue ? Thanks Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks, Suresh Kuna
Re: innodb_data_file_path
Hi Suresh, The error log show the related error as below InnoDB: File name /dev/sda3 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 100102 1:18:32 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=ip-xx-xx-xx-xx-bin' to avoid this problem. 100102 1:18:32 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. Thanks Jeetendra Ranjan - Original Message - From: Suresh Kuna To: Jeetendra Ranjan Cc: mysql@lists.mysql.com Sent: Monday, January 04, 2010 10:28 AM Subject: Re: innodb_data_file_path Hi Jeetendra, What is the error it it saying in error log ? Is this the fresh installation or already installed MySQL ? Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 10:16 AM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I am using MySQL 5.0.85-community on Fedora 8. When i set innodb_data_home_dir = innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw and restart the server it gives an error Starting MySQL.Manager of pid-file quit without updating file.[FAILED] Please suggest how to resolve this issue ? Thanks Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks, Suresh Kuna
Re: innodb_data_file_path
Hi Suresh, Thanks, this link we already have but it has no information right from creating the raw disk partition. Jeetendra Ranjan - Original Message - From: Suresh Kuna To: Jeetendra Ranjan Cc: mysql@lists.mysql.com Sent: Monday, January 04, 2010 12:12 PM Subject: Re: innodb_data_file_path Hi Jeetendra, Check the below url for details with the raw partition. http://dev.mysql.com/doc/refman/5.4/en/innodb-raw-devices.html Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 11:06 AM, Suresh Kuna sureshkumar...@gmail.com wrote: Jeetendra, Give the MySQLD user permissions to the new directory which you are specifying. On Mon, Jan 4, 2010 at 10:57 AM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi Suresh, The error log show the related error as below InnoDB: File name /dev/sda3 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 100102 1:18:32 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=ip-xx-xx-xx-xx-bin' to avoid this problem. 100102 1:18:32 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. Thanks Jeetendra Ranjan - Original Message - From: Suresh Kuna To: Jeetendra Ranjan Cc: mysql@lists.mysql.com Sent: Monday, January 04, 2010 10:28 AM Subject: Re: innodb_data_file_path Hi Jeetendra, What is the error it it saying in error log ? Is this the fresh installation or already installed MySQL ? Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 10:16 AM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I am using MySQL 5.0.85-community on Fedora 8. When i set innodb_data_home_dir = innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw and restart the server it gives an error Starting MySQL.Manager of pid-file quit without updating file.[FAILED] Please suggest how to resolve this issue ? Thanks Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks, Suresh Kuna -- Thanks Suresh Kuna MySQL DBA -- Thanks Suresh Kuna MySQL DBA
Re: innodb_data_file_path
This is the second step and before that we have to stop the server and set the parameter in my.cnf innodb_data_home_dir = innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw and when i restart the server it is giving error Starting MySQL.Manager of pid-file quit without updating file.[FAILED] - Original Message - From: Suresh Kuna To: Jeetendra Ranjan Cc: mysql@lists.mysql.com Sent: Monday, January 04, 2010 12:45 PM Subject: Re: innodb_data_file_path Try this : /dev/sda3:10Graw;/dev/sda1:5Graw Change the newraw to raw and start the MySQL. and paste the error log ouput. Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 12:37 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi Suresh, Thanks, this link we already have but it has no information right from creating the raw disk partition. Jeetendra Ranjan - Original Message - From: Suresh Kuna To: Jeetendra Ranjan Cc: mysql@lists.mysql.com Sent: Monday, January 04, 2010 12:12 PM Subject: Re: innodb_data_file_path Hi Jeetendra, Check the below url for details with the raw partition. http://dev.mysql.com/doc/refman/5.4/en/innodb-raw-devices.html Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 11:06 AM, Suresh Kuna sureshkumar...@gmail.com wrote: Jeetendra, Give the MySQLD user permissions to the new directory which you are specifying. On Mon, Jan 4, 2010 at 10:57 AM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi Suresh, The error log show the related error as below InnoDB: File name /dev/sda3 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 100102 1:18:32 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=ip-xx-xx-xx-xx-bin' to avoid this problem. 100102 1:18:32 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. Thanks Jeetendra Ranjan - Original Message - From: Suresh Kuna To: Jeetendra Ranjan Cc: mysql@lists.mysql.com Sent: Monday, January 04, 2010 10:28 AM Subject: Re: innodb_data_file_path Hi Jeetendra, What is the error it it saying in error log ? Is this the fresh installation or already installed MySQL ? Thanks Suresh Kuna MySQL DBA On Mon, Jan 4, 2010 at 10:16 AM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I am using MySQL 5.0.85-community on Fedora 8. When i set innodb_data_home_dir = innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw and restart the server it gives an error Starting MySQL.Manager of pid-file quit without updating file.[FAILED] Please suggest how to resolve this issue ? Thanks Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks, Suresh Kuna -- Thanks Suresh Kuna MySQL DBA -- Thanks Suresh Kuna MySQL DBA -- Thanks Suresh Kuna MySQL DBA
innodb_data_file_path
Hi, I am using MySQL 5.0.85-community on Fedora 8. When i set innodb_data_home_dir = innodb_data_file_path = /dev/sda3:10Gnewraw;/dev/sda1:5Gnewraw and restart the server it gives an error Starting MySQL.Manager of pid-file quit without updating file.[FAILED] Please suggest how to resolve this issue. Thanks Jeetendra Ranjan
Global variable for only one database
Hi, We have number of mysql database in one box. How can we set the variables that applies to only one database. Like when i set the query_cache_size it should be used by only those queries for which the parameter is set ? Thanks
Re: Aborted_connects is incresing repidly
Thanks , but i want to solve this problem without shutting down the db server. - Original Message - From: Parikh, Dilip Kumar dilipkumar.par...@eds.com To: Jeetendra Ranjan jeetendra.ran...@sampatti.com Cc: mysql@lists.mysql.com Sent: Wednesday, December 30, 2009 2:56 PM Subject: RE: Aborted_connects is incresing repidly Try using this param in ur my.cnf file and restart the mysql to take the new changes. # Troubleshoot for MySQL error 2013 Error or Host blockout set-variable = connect_timeout=60 set-variable = max_connect_errors=1000 Thanks, Dilipkumar Database Engineering Mphasis, an EDS company phone: +91-44-6637 3167 mailto: dilipkumar.par...@eds.com Pager mail:9884430...@nma.vodafone.in -Original Message- From: RaMeSh [mailto:rames...@gmail.com] Sent: Wednesday, December 30, 2009 2:37 PM Cc: mysql@lists.mysql.com Subject: Re: Aborted_connects is incresing repidly You can use this --max_connect_errors=9 to avoid this error 2009/12/30 Parikh, Dilip Kumar dilipkumar.par...@eds.com Jetendra, Can u attach your my.cnf files and also your variable status (show variable status). This is required because we need to tune some params. Thanks, Dilipkumar -Original Message- From: Jeetendra Ranjan [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, December 30, 2009 11:43 AM To: mysql@lists.mysql.com Subject: Aborted_connects is incresing repidly Hi, My MySQL server Aborted_connects status is showing 8692 and is rapidly increasing. What are reasons and how do i decrease the same? We are using connect() method in PHP code and have tried below command mysqladmin flush-hosts but still the value is same. Thanks Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=rames...@gmail.com -- Ramesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Aborted_connects is incresing repidly
Hi, The server where i faced this problem was master server(Replication) but it was not in use. I simply comment the server-id=1 in my.cnf and run the command revoke replication slave on. And now i see the increasing aborted_connects stopped. anyway, thanks to all for your support Regards Jeetendra Ranjan - Original Message - From: Claudio Nanni To: Walter Heck - OlinData.com Cc: mysql@lists.mysql.com ; Jeetendra Ranjan Sent: Thursday, December 31, 2009 1:32 AM Subject: Re: Aborted_connects is incresing repidly @Walter By the way your guess is the most likely, good shot! Claudio On 30 dec 2009 20:59, Claudio Nanni claudio.na...@gmail.com wrote: Waltet, I think you are not the only one shooting in the dark, in this case reading the manual can really help. aborted_connects is increased by a failed attempt to connect to MySQL (wrong password?) but NOT by a timed-out client. Could be a cron job setup with some credentials that were changed without updating it, or other similar things. There is a very bad habit of not setting the root password (or to put it in the my.cnf) so sometimes mysql client is considered just a bash command and included as it in scripts. As long as you dont experience problems the simple increase of the value is not a critical issue. Check the logs of the application(s) ,if any, to see if some part of it is actually affected. Cheers Claudio On 30 dec 2009 20:31, Walter Heck - OlinData.com li...@olindata.com wrote: Random shot ... Hi, My MySQL server Aborted_connects status is showing 8692 and is rapidly increasing. Wha... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: ht...
Aborted_connects is incresing repidly
Hi, My MySQL server Aborted_connects status is showing 8692 and is rapidly increasing. What are reasons and how do i decrease the same? We are using connect() method in PHP code and have tried below command mysqladmin flush-hosts but still the value is same. Thanks Jeetendra Ranjan
Re: Slave is not updated
Hi Dilip, Thanks for your response I already run the below command at master: grant replication slave on *.* to 'mysqladm'@'95.84.86.41' identified by 'admmysql'; Thanks Jeetendra Ranjan - Original Message - From: Parikh, Dilip Kumar dilipkumar.par...@eds.com To: Jeetendra Ranjan jeetendra.ran...@sampatti.com; mysql@lists.mysql.com Sent: Thursday, December 24, 2009 1:10 PM Subject: 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
Re: Slave is not updated
and has his hostname changed!! Please use '--relay-log=ip-97-74-85-42-relay-bin' to avoid this problem. 091224 0:00:16 [Note] Slave SQL thread initialized, starting replication in log 'ip-208-109-238-76-bin.42' at position 4436, relay log './ip-97-74-85-42-relay-bin.01' position: 4 091224 0:00:16 [ERROR] Slave I/O thread: error connecting to master 'r...@201.107.236.77:3306': Error: 'Access denied for user 'root'@'ip-97-74-85-42.ip.secureserver.net' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400 091224 1:04:48 [Note] Slave I/O thread killed while connecting to master 091224 1:04:48 [Note] Slave I/O thread exiting, read up to log 'ip-208-109-238-76-bin.42', position 4436 091224 1:04:48 [Note] Error reading relay log event: slave SQL thread was killed 091224 1:07:59 [Note] Slave SQL thread initialized, starting replication in log 'ip-208-109-238-76-bin.42' at position 4702, relay log './ip-97-74-85-42-relay-bin.01' position: 4 091224 1:07:59 [ERROR] Slave I/O thread: error connecting to master 'r...@201.107.236.77:3306': Error: 'Access denied for user 'root'@'ip-97-74-85-42.ip.secureserver.net' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400 Thanks Jeetendra Ranjan - Original Message - From: Shanmugam, Dhandapani dhandapani.shanmu...@eds.com To: Jeetendra Ranjan jeetendra.ran...@sampatti.com; Parikh, Dilip Kumar dilipkumar.par...@eds.com; mysql@lists.mysql.com Sent: Thursday, December 24, 2009 2:15 PM Subject: RE: Slave is not updated Jeetendra, Can you paste the error log here. -D -Original Message- From: Jeetendra Ranjan [mailto:jeetendra.ran...@sampatti.com] Sent: Thursday, December 24, 2009 1:49 PM To: Parikh, Dilip Kumar; mysql@lists.mysql.com Subject: Re: Slave is not updated Hi Dilip, Thanks for your response I already run the below command at master: grant replication slave on *.* to 'mysqladm'@'95.84.86.41' identified by 'admmysql'; Thanks Jeetendra Ranjan - Original Message - From: Parikh, Dilip Kumar dilipkumar.par...@eds.com To: Jeetendra Ranjan jeetendra.ran...@sampatti.com; mysql@lists.mysql.com Sent: Thursday, December 24, 2009 1:10 PM Subject: 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=dhandapani.shanmu...@eds.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slave is not updated
Here is the action i performed: mysql GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'xx'@'xx.xx.xx.xx' IDENTIFIED BY ''; Query OK, 0 rows affected (0.00 sec) mysql Flush privileges; Query OK, 0 rows affected (0.00 sec) mysql select * from mysql.user where user='adconsent' \G; *** 1. row *** Host: xx.xx.xx.xx User: xxx Password: *02BF3AAD3EDAAD0A1C45A98092B24BDC96172E16 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 1 row in set (0.00 sec) mysql show slave status \G; *** 1. row *** Slave_IO_State: Connecting to master Master_Host: xx.xx.xx.xx. Master_User: x Master_Port: 3306 Connect_Retry: 60 Master_Log_File: ip-xxx-xxx-xxx-xx-bin.42 Read_Master_Log_Pos: 7220 Relay_Log_File: ip-xx-xx-xx-xx-relay-bin.01 Relay_Log_Pos: 98 Relay_Master_Log_File: ip-xxx-xxx-xxx-xx-bin.42 Slave_IO_Running: No Slave_SQL_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: 7220 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 Thanks Jeetendra Ranjan - Original Message - From: Shanmugam, Dhandapani dhandapani.shanmu...@eds.com To: Parikh, Dilip Kumar dilipkumar.par...@eds.com; Jeetendra Ranjan jeetendra.ran...@sampatti.com; mysql@lists.mysql.com Sent: Thursday, December 24, 2009 2:47 PM Subject: RE: Slave is not updated by the way did you flush the privileges on the master? What is the output you get, when you log in as root user on the mysql master server and issue the below command: select * from mysql.user where user='replication'\G; -D -Original Message- From: Parikh, Dilip Kumar [mailto:dilipkumar.par...@eds.com] Sent: Thursday, December 24, 2009 2:43 PM To: Jeetendra Ranjan; mysql@lists.mysql.com Subject: RE: Slave is not updated You can see in the logs, access denied So give trying for hostname or with full domain or Grant replication slave on *.* to 'mysqladm'@'95.%' identified by 'xx'; Grant replication slave on *.* to 'mysqladm'@'hostname identified by 'xx'; Flush privileges; it should work. Thanks, Dilipkumar Database Engineering mailto: dilipkumar.par...@eds.com Pager mail:9884430...@nma.vodafone.in -Original Message- From: Jeetendra Ranjan [mailto:jeetendra.ran...@sampatti.com] Sent: Thursday, December 24, 2009 2:31 PM To: Shanmugam, Dhandapani; Parikh, Dilip Kumar; mysql@lists.mysql.com Subject: Re: Slave is not updated 091223 5:55:22 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/lib/mysql/mysql-relay-bin.01' position: 4 091223 5:55:22 [ERROR] Slave I/O thread: error connecting to master 'r...@201.107.236.77:3306': Error: 'Access denied for user 'root'@'ip-97-74-85-42.ip.secureserver.net' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400 091223 6:55:42 [Note] /usr/sbin/mysqld: Normal shutdown 091223 6:55:42 [Note] Slave I/O thread killed while connecting to master 091223 6:55:42 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4 091223 6:55:42 [Note] Error reading relay log event: slave SQL thread was killed 091223 6:55:42 InnoDB: Starting shutdown... 091223 6:55:45 InnoDB: Shutdown completed; log sequence number 0 407843552 091223 6:55:45 [Note] /usr/sbin/mysqld: Shutdown complete 091223 6:58:30 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts
Re: Slave is not updated
Please ignore the user name and ip. i am passing the right information here - Original Message - From: Parikh, Dilip Kumar dilipkumar.par...@eds.com To: Jeetendra Ranjan jeetendra.ran...@sampatti.com; mysql@lists.mysql.com Sent: Thursday, December 24, 2009 2:51 PM Subject: RE: Slave is not updated User is not the replication it is 'root' (or) mysqladm Thanks, Dilipkumar Database Engineering mailto: dilipkumar.par...@eds.com Pager mail:9884430...@nma.vodafone.in -Original Message- From: Shanmugam, Dhandapani Sent: Thursday, December 24, 2009 2:48 PM To: Parikh, Dilip Kumar; Jeetendra Ranjan; mysql@lists.mysql.com Subject: RE: Slave is not updated by the way did you flush the privileges on the master? What is the output you get, when you log in as root user on the mysql master server and issue the below command: select * from mysql.user where user='replication'\G; -D -Original Message- From: Parikh, Dilip Kumar [mailto:dilipkumar.par...@eds.com] Sent: Thursday, December 24, 2009 2:43 PM To: Jeetendra Ranjan; mysql@lists.mysql.com Subject: RE: Slave is not updated You can see in the logs, access denied So give trying for hostname or with full domain or Grant replication slave on *.* to 'mysqladm'@'95.%' identified by 'xx'; Grant replication slave on *.* to 'mysqladm'@'hostname identified by 'xx'; Flush privileges; it should work. Thanks, Dilipkumar Database Engineering mailto: dilipkumar.par...@eds.com Pager mail:9884430...@nma.vodafone.in -Original Message- From: Jeetendra Ranjan [mailto:jeetendra.ran...@sampatti.com] Sent: Thursday, December 24, 2009 2:31 PM To: Shanmugam, Dhandapani; Parikh, Dilip Kumar; mysql@lists.mysql.com Subject: Re: Slave is not updated 091223 5:55:22 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/lib/mysql/mysql-relay-bin.01' position: 4 091223 5:55:22 [ERROR] Slave I/O thread: error connecting to master 'r...@201.107.236.77:3306': Error: 'Access denied for user 'root'@'ip-97-74-85-42.ip.secureserver.net' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400 091223 6:55:42 [Note] /usr/sbin/mysqld: Normal shutdown 091223 6:55:42 [Note] Slave I/O thread killed while connecting to master 091223 6:55:42 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4 091223 6:55:42 [Note] Error reading relay log event: slave SQL thread was killed 091223 6:55:42 InnoDB: Starting shutdown... 091223 6:55:45 InnoDB: Shutdown completed; log sequence number 0 407843552 091223 6:55:45 [Note] /usr/sbin/mysqld: Shutdown complete 091223 6:58:30 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=ip-97-74-85-42-bin' to avoid this problem. 091223 6:58:30 InnoDB: Started; log sequence number 0 407843552 091223 6:58:30 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.85-community-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition (GPL) 091223 6:58:30 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/lib/mysql/mysql-relay-bin.01' position: 98 091223 6:58:30 [ERROR] Slave I/O thread: error connecting to master 'r...@201.107.236.77:3306': Error: 'Access denied for user 'root'@'ip-97-74-85-42.ip.secureserver.net' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400 091223 7:03:32 [Note] /usr/sbin/mysqld: Normal shutdown 091223 7:03:32 [Note] Slave I/O thread killed while connecting to master 091223 7:03:32 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4 091223 7:03:32 [Note] Error reading relay log event: slave SQL thread was killed 091223 7:03:32 InnoDB: Starting shutdown... 091223 7:03:33 InnoDB: Shutdown completed; log sequence number 0 407843552 091223 7:03:33 [Note] /usr/sbin/mysqld: Shutdown complete 091223 7:03:38 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=ip-97-74-85-42-bin' to avoid this problem. 091223 7:03:38 InnoDB: Started; log sequence number 0 407843552 091223 7:03:38 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.85-community-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition (GPL) 091223 7:03:38 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/lib/mysql/mysql-relay-bin.02' position: 98 091223 7:03:38 [ERROR] Slave I/O thread: error connecting to master 'r...@201.107.236.77:3306': Error: 'Access denied for user 'root'@'ip-97-74-85-42.ip.secureserver.net' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400 091223 7:03:44 [Note] /usr/sbin/mysqld: Normal
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
Error in running trigger
Hi, I have created the below trigger from root user with definer: CREATE definer=`roo...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Uninsall mysql 5.0.45 from RHEL 5
Hi, I have mysql 5.0.45 on Red Hat EL 5. How do i completely uninstall the same ? Thanks in advance. Jeetendra Ranjan
How to create new mysql instance
Hi, Can any body help me how to create new instance at the same mysql databas server in 5.0.85 community version ? Thanks Jeetendra Ranjan
error while creating trigger
Hi, I am getting error while creating a simple trigger mysql CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW - begin - INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) - VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); - INSERT INTO DELETED_CONSUMER_ACTION_AUDIT(CONSUMER_ID,CAMPAIGN_ID,CAMPAIGN_ACTION_ID,STATE_ID,LAST_MOD_TIME) VALUES(old.CONSUMER_ID,old.CAMPAIGN_ID,old.CAMPAIGN_ACTION_ID,old.STATE_ID,old.LAST_MOD_TIME); - END; - // ERROR 1054 (42S22): Unknown column 'CAMPAIGN_ID' in 'OLD' But this column is exist in DELETED_CONSUMER_ACTION_AUDIT table. What are the possible causes of this error ? Thanks in advance Jeetendra Ranjan
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
Hi, I just would like to know which version of MySQL support the creation of database triggers with the same action and event for one table. Because if i create the the triggers as below i am getting error. First trigger (Succesfull) ++ CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); END Second trigger (generating error) +++ CREATE TRIGGER AUDIT_REPOSITORY_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,AFFECTED_CONSUMER_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,NEW_VALUE_ID,NEW_VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTED_CONSUMER_ID,old.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.OLD_VALUE,old.NEW_VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old.LAST_MOD_BY );END - // ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' Or any other alternate to do the same ? Thanks Regards Jeetendra Ranjan
Force index command in sql query
Hi, After analysing slow query log i found that some queries are not using index and so i used the force index command in query and test it and now it starts using index properly.Accordingly i implemented the same query with force index in my application code and regeneratet the slow query log. Now i found that the same queries having force index clause are againg not using index and surprisingly its starting using index without any force index clause. Please suggest how it happened and should i continue with the force index command in that query or remove the force index clause from those queries ? Thanks Regards Jeetendra Ranjan
How to enable partition in MySQL version 5.0.81-community
Hi, We are using MySQL version 5.0.81-community.We need to partition few tables but the have_partition option is not available that means this version doesn't support partition feature. Does MySQL provide any patch to enable the feature of partition in the same version of MySQL or how to enable the partition option in MySQL version 5.0.81-community? -- Regards, Jeetendra Ranjan SynapseIndia http://www.synapse.in I.T. Outsourcing @ Peace of Mind --
mysqlbinlog is not running
Hi, When i run mysqlbinlog to convert binary log file to text file it is giving error which i could not understand. Command [/]# /usr/bin/mysqlbinlog /var/lib/mysql/ip-hostname-bin.27 \ /mysql_backup/a.sql Error /usr/bin/mysqlbinlog: relocation error: /usr/bin/mysqlbinlog: symbol my_init_time, version libmysqlclient_15 not defined in file libmysqlclient.so.15 with linktime reference Please guide me what is the solution of this error ? -- Regards, Jeetendra Ranjan SynapseIndia http://www.synapse.in I.T. Outsourcing @ Peace of Mind --
Re: Req. suitable .cnf file for Server used by 2000 users daily
mysql is successfull in login with same user and password but when the same user and password is given at mysqltuner end it always shows the same error [!!] Attempted to use login credentials, but they were invalid. Thanks Jeetendra Ranjan SynapseIndia http://www.synapse.in I.T. Outsourcing @ Peace of Mind - Original Message - From: Bruce Ferrell bferr...@baywinds.org To: Jeetendra Ranjan jeetendra.ran...@sampatti.com Cc: mysql@lists.mysql.com Sent: Tuesday, October 20, 2009 9:27 PM Subject: Re: Req. suitable .cnf file for Server used by 2000 users daily this error indicates either root doesn't have sufficient privilege or the password entered was bad. try running it this way: ./mysqltuner.pl --user root --pass password Jeetendra Ranjan wrote: Hi, I run the mysqltuner at my server as below and i got error like below. [r...@127 /]# ./mysqltuner.pl MySQLTuner 1.0.1 - Major Hayden ma...@mhtx.net Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [!!] Attempted to use login credentials, but they were invalid. On some other server this script is running absolutely fine without any change in mysqltuner.pl. Please guide me how can i run this script Thanks Regards Jeetendra Ranjan - Original Message - From: Bruce Ferrell bferr...@baywinds.org To: jeetendra.ran...@sampatti.com Cc: mysql@lists.mysql.com Sent: Thursday, October 15, 2009 12:20 PM Subject: Re: Req. suitable .cnf file for Server used by 2000 users daily Have a look at mysqltuner. It reads the stats from a running mysql instances and makes suggestions for what can be changed http://blog.mysqltuner.com/ Gavin Towey wrote: Hi, This script might help with some tuning suggestions, run it after you have some production traffic running against your database. https://launchpad.net/mysql-tuning-primer Also you should enable the slow query log, so you can capture queries to be optimized: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Regards, Gavin Towey -Original Message- From: jeetendra.ran...@sampatti.com [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, October 14, 2009 3:21 AM To: mysql@lists.mysql.com Subject: Req. suitable .cnf file for Server used by 2000 users daily Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you
Re: Req. suitable .cnf file for Server used by 2000 users daily
Hi, I run the mysqltuner at my server as below and i got error like below. [r...@127 /]# ./mysqltuner.pl MySQLTuner 1.0.1 - Major Hayden ma...@mhtx.net Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [!!] Attempted to use login credentials, but they were invalid. On some other server this script is running absolutely fine without any change in mysqltuner.pl. Please guide me how can i run this script Thanks Regards Jeetendra Ranjan - Original Message - From: Bruce Ferrell bferr...@baywinds.org To: jeetendra.ran...@sampatti.com Cc: mysql@lists.mysql.com Sent: Thursday, October 15, 2009 12:20 PM Subject: Re: Req. suitable .cnf file for Server used by 2000 users daily Have a look at mysqltuner. It reads the stats from a running mysql instances and makes suggestions for what can be changed http://blog.mysqltuner.com/ Gavin Towey wrote: Hi, This script might help with some tuning suggestions, run it after you have some production traffic running against your database. https://launchpad.net/mysql-tuning-primer Also you should enable the slow query log, so you can capture queries to be optimized: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Regards, Gavin Towey -Original Message- From: jeetendra.ran...@sampatti.com [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, October 14, 2009 3:21 AM To: mysql@lists.mysql.com Subject: Req. suitable .cnf file for Server used by 2000 users daily Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Req. suitable .cnf file for Server used by 2000 users daily
Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan
Re: Re: Req. suitable .cnf file for Server used by 2000 users daily
Hi Shawn, Thanks for your reply. This is basically a marketing website for different products. The site will be visited by the marketer and the customer. Here is answer as you asked: 1) The customer and the marketer will visit only one database and they have no right to create database by them. 2) Its multiple query per visitor which uses tables in only one database. 3) Few tables will be very haeavy and there may be millions of rows in one tables. 4) Few heavy tables are Innodb and few are MyISAM 5) Yes, MySQL database sharing the same box as web server. I mostly concern about connectoins. Initially 2000 visitor including marketer and customer will visit this site. Thanks in advance. Regards Jeetendra Ranjan At 21:21 14/10/2009, Shawn Green wrote: jeetendra.ran...@sampatti.com wrote: Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * ... snip ... Unfortunately, you have not asked us an answerable question. There are too many unknowns in your setup for us to estimate how much database usage per customer your site will create. If it's a single query per visitor, then you probably can omit any tuning at all. If each visit requires dozens of queries each using multiple tables then you have a totally different usage profile. It all boils down to what you are doing to the database, not how many people are doing it. What can you tell us about your application, how many rows of data you plan to manage, if your data is mostly MyISAM or InnoDB, and what performance numbers you have achieved so far using your lab equipment and a simulated user load. Also, is your MySQL database sharing the same box as your web server or are they located in separate machines? At that point, we may be able to make a reasonable guess about your situation. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN
