Mysql – Reloading MySQL the.cnf changes

my.cnfMySQL

Do you have to restart MySQL to edit changes? Or can you edit them on the fly and restart MySQL or make the changes inside MySQL?

This server runs 100s of website that are active and I don't really want to bring mysql down for a restart unless I have to.

Best Answer

If you want to change some of the global configuration setting of MySQL There are two ways.

1. By Changing variable using SET GLOBAL option on running MySQL instance,this does not require server restart we can change variables like Ex : I need to set my key buffer to 128 MB

SET GLOBAL key_buffer_size = 128*1024*1024;

But using this technique we can not change or configure all variables and once we restart MySQL these GLOBAL settings are lost.

2. By Changing variables in config file (my.cnf)

This requires a server restart but changes are permanent.

Related Solutions

MySQL the.cnf vs the-huge.cnf

If you are doing replication and you are just practicing, the my.cnf you choose is not that important. However, the smaller the config, the better. For example, if you DB Server has 2GB RAM, you should not choose my-innodb-heavy-4G.cnf.

If you started up mysql with a my.cnf, you create the default 10M or 18M ibdata1 and 2 5M InnoDB log files (ib_logfile0, ib_logfile1). The medium cnf has the following InnoDB settings:

[root@******** ~]# cat /usr/share/mysql/my-medium.cnf | grep innodb
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

Since everything related to InnoDB is commented out, you should OK to choose my-medium.cnf

Simply run this:

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
service mysql restart

Now, as for MySQL Replication, I have Good News : I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with all the steps you need to automate a Slave's creation.

Here is a couple more of my posts with more advanced replication setups

May 07, 2012 : Setting Circular Replication in mysql
Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave?

Mysql – Finding swap causes of MySQL

You seem to over-allocate memory. You allow relatively large number of connections (600) along with larger session buffers (such as tmp_table_size=128M, max_heap_table_size=128M). So in worst case, when all the 600 parallel sessions use up the allowed 128M for MEMORY engine temporary tables, you need 600x128M= 77G memory. But even with a sort_buffer_size=2M there is a need for 1.2GB, however you have 1GB only.

Having swap used is not a surprise this case. Check status variable Max_used_connections to see if use up to 600 connections, decrease the max_connections variable. Check status vars Created_tmp_disk_tables and Created_tmp_tables, and you might safely decrease the tmp_table_size until Created_tmp_disk_tables is not increasing dramatically.

Also check whether kernel option vm.swappiness is not too high (such as 100), so that Linux starts swapping earlier, and more aggressively if getting out of memory

Best Answer

Related Solutions

MySQL the.cnf vs the-huge.cnf

Mysql – Finding swap causes of MySQL

Related Question