No, there isn't (I just checked 1 hour ago). You can comment the bind-address in my.cnf:
Note: « 1 hour ago » is now more than 10 years ago.
#skip-networking
#bind-address = 127.0.0.1
If you want only 2 IPs, you will then have to use a firewall.
For MySql version 8.0.13 and above, you can specify a list of comma-separated IP addresses.
bind-address = 10.0.0.1,10.0.1.1,10.0.2.1
Relevant MySql documentation.
Remember to restart your MySQL instance after changing the config file.
You need to set the MyISAM Key Cache and the InnoDB Buffer Pool to accommodate the data you have. I published two queries in the DBA StackExchange to compute the recommended size for noth of those caches.
For MyISAM, since only index pages are cached, this query will recommend the size of key_buffer_size based on the sum of your .MYI files and cap it at 4G if it exceeds 4G:
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo)))+0.4999),
SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+1,1)) recommended_key_buffer_size
FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM
(SELECT SUM(index_length) KBS1 FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA
) A,(SELECT 2 PowerOfTwo) B;
For InnoDB, since data and index pages are cached, this query will recommend the size of innodb_buffer_pool_size based on the sum of your data and index pages:
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo)))+0.49999),
SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tablesM
WHERE engine='InnoDB') A,
(SELECT 2 PowerOfTwo) B;
According to your display of mysqltuner.pl, you have about 24GB of RAM, 530MB id InnoDB, 7.2GB of MyISAM indexes. No matter what the recommendations come up as, please just use common sense by setting innodb_buffer_pool_size to a number about 530MB but under 1GB. Since MyISAM doesn't really take advantage of memory in certain instances, you could leave the key_buffer_size to 1024M because mysqltuner.pl says 90% of RAM is used by the DB Connections. The OS needs RAM too.
I would lower these:
[mysqld]
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M
Restart mysql afterwards and run mysqltuner.pl. Your goal here is to get the Maximum possible memory usage under 80% so the OS has breathing room for memory.
CAVEAT : Please excuse the Column called 'PowerOfTwo'. It should really be called PowerOf1024. The purpose of PowerOfTwo is to display recommended answers in GB,MB,KB
- (SELECT 0 PowerOfTwo) displays the value in Bytes
- (SELECT 1 PowerOfTwo) displays the value in KiloBytes
- (SELECT 2 PowerOfTwo) displays the value in MegaBytes
- (SELECT 3 PowerOfTwo) displays the value in GigaBytes
Best Answer
Yes, the performance will deteriorate - but we can't tell you how quickly, or what level of deterioration is acceptable - since you are already doing this why don't you measure the impact yourself.
Akber is correct in saying that using a cluster would allow you to optimize one system while the other still serves up data - but why not set them up as a master-master pair - then you don't need to upgrade and downgrade when you switch. And there's no need to write temporary records - just wait for the server lag to recover then switch over. It's also a really good solution for backups and, of course, high availability.
Another solution already in wide use for this kind of exercise is zero-downtime schema changes - there are 2 good implementations I know of: one written in Perl by the guys at Percona, and one written in PHP by the Facebook people.