mysqldump problem

[nomankhn]

Hello All,

 

Good Evening,

 

I am having a problem that i am not able to take dump of database which is around 8GB in size,

our mysql server is separate from web server, having 4GB of RAM and hardware is Intel® Xeon CPU 3.06GHz. Please suggest how to mysqldump slowly so it will not give too many connections issue on live website.

 

[client]

port = 3306

socket = /tmp/mysql.sock

 

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

datadir = /var/lib/mysql

#key_buffer = 16M

max_allowed_packet = 16M

table_cache = 2048

sort_buffer_size = 16M

read_buffer_size = 2M

myisam_sort_buffer_size = 128M

thread_cache = 8

query_cache_size = 128M

query_cache_limit = 2M

max_connections = 500

slave-skip-error = 1062

bulk_insert_buffer_size = 64M

key_buffer_size = 384M

max_heap_table_size = 64M

tmp_table_size = 64M

 

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 4

 

pid-file=/var/run/mysqld/mysqld.pid

log-error=/var/log/mysqld.log

long_query_time = 10

log_slow_queries=/var/log/mydb-slow_queries.log

#log-long-format

skip-bdb

#memlock

server-id = 3

relay-log = /var/lib/mysql/db-relay-bin

relay-log-index = /var/lib/mysql/db-relay-index

 

 

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/

innodb_buffer_pool_size = 256M

innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 250M

innodb_log_files_in_group = 2

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

 

[mysqldump]

quick

max_allowed_packet = 500M

 

[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

[Mchl]

I'm not really sure what to suggest, but I also couldn't help noticing, that your InnoDB could use some tuning (that is if your application uses InnoDB at all).

 

Back to backup: perhaps you should consider using other tools like http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html