MySql Database errors keeps coming !!!
I usually monitor mysql database on Linux 9 server frequently.For past two months I have observerd some downs in database .
Someday unexpectdly my mysql server died.When I tried to open website it gave error Mysql development error.I faced this error before.I managed to up the mysql and site.
But again I got to see this error.Again I managed to up the server.
But recetly again I got this error I tried to check the err file of mysql server.It has something to say like this:
******************************
040618 16:30:33 mysqld started
040618 16:30:33 Can't start server: Bind on TCP/IP port: Address already in use
040618 16:30:33 Do you already have another mysqld server running on port: 3306 ?
040618 16:30:33 Aborting
**************************************
These are very frequent message in err file for last 1 month.
Recently I have copied my-huge.cnf to my.cnf to cope with the requirement of growing mysql load on server.
I don't understand why I get this error frequently and why my websit goes down so often!!!
***************************************
Below is my.cnf file:
# Example mysql config file for very large systems.
#
# This is for large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_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 = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
***************************************
Below my variables output:
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 176 |
| Bytes_received | 3987459491 |
| Bytes_sent | 3777487646 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 5924923 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 212521 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 18 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 174695 |
| Com_insert_select | 38 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 29706 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 579 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 12801255 |
| Com_set_option | 1536 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 1536 |
| Com_show_databases | 10 |
| Com_show_fields | 1536 |
| Com_show_grants | 63 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 601 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1575 |
| Com_show_variables | 10 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 29706 |
| Com_update | 14539614 |
| Connections | 3364731 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 3323 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 2455 |
| Handler_read_first | 12097 |
| Handler_read_key | 42109809 |
| Handler_read_next | 38519972 |
| Handler_read_prev | 9084265 |
| Handler_read_rnd | 2830293 |
| Handler_read_rnd_next | 2300393087 |
| Handler_rollback | 0 |
| Handler_update | 13526441 |
| Handler_write | 188906 |
| Key_blocks_used | 11863 |
| Key_read_requests | 275385206 |
| Key_reads | 11750 |
| Key_write_requests | 397673 |
| Key_writes | 395171 |
| Max_used_connections | 100 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 |
| Open_files | 910 |
| Open_streams | 0 |
| Opened_tables | 2250 |
| Questions | 55318158 |
| Qcache_queries_in_cache | 3582 |
| Qcache_inserts | 12330763 |
| Qcache_hits | 18023020 |
| Qcache_lowmem_prunes | 109121 |
| Qcache_not_cached | 470533 |
| Qcache_free_memory | 4955296 |
| Qcache_free_blocks | 2088 |
| Qcache_total_blocks | 10989 |
| Rpl_status | NULL |
| Select_full_join | 16639 |
| Select_full_range_join | 0 |
| Select_range | 3699 |
| Select_range_check | 0 |
| Select_scan | 2978630 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 14257 |
| Sort_rows | 2834300 |
| Sort_scan | 1946185 |
| Table_locks_immediate | 21352850 |
| Table_locks_waited | 7627314 |
| Threads_cached | 3 |
| Threads_created | 463511 |
| Threads_connected | 12 |
| Threads_running | 5 |
| Uptime | 180404 |
+--------------------------+------------+
It's RED hat 9 with mysql version version | 4.0.18-standard-log
2 GB RAM on server and 4Xeon processors.
Can anybody help me out!!!
It's urgent before next mysql down happens!!
Thank you
