nomankhn Posted May 6, 2010 Share Posted May 6, 2010 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 Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 6, 2010 Share Posted May 6, 2010 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.