Changing Hostnames
Ran into a problem today while trying to change the hostname on a live server. Apparently mysql init script spawns itself using a PID file which, if unspecified, defaults to the server's hostname .pid, hence causing a problem when trying to restart the mysql server after the name change.
MySql Bugs
While attempting to resolve the issue, I discovered that there was no running instance of mysqlmanager, hence the init script was rendered useless. It appears that there are some known major problems with the default Fedora installer of mysql 5...
http://bugs.mysql.com/14537
http://bugs.mysql.com/13630
Temporary Resolution
The temporary resolution to get your DB back up is to
1. kill all running instances of mysqld
2. restart mysql using the old an tried method of calling mysqld_safe yourself.
/usr/bin/mysqld_safe &
The official startup configuration recommend by MySQL is the use of the mysql instance manager. However the mysql instance manager configuration needs to be followed exactly, which means that if you are migrating from an older version, you are likely to have some config files missing or using the old conventions.
Consequently, the solution taken was to revert the init script to an earlier version (which i called "mysqld") . I kept the new version of the script (named "mysql") for future use.
Finally, I modified the default startup settings to automatically start the new init script instead of the old one on all default system run-levels.
/sbin/chkconfig mysql off
/sbin/chkconfig mysqld on
Setting Up Mysql Instance Manager
During the upgrade process, you may have noticed the following message:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h yiju password 'new-password'
See the manual for more instructions.
NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!
Now that your server is back online, it's a good time to go ahead and setup mysqlmanager.
/usr/bin/mysqladmin -u root password 'new-password'
Update /etc/my.cnf
According to the MySQL website, Starting with MySQL 5.0.19, you can use Instance Manager if you modify the my.cnf configuration file by adding use-manager to the [mysql.server] section:
[mysql.server]
use-manager
Do not place instance manager specifics in the mysqld section. Use the [manager] section to specify instance manager settings, for example:
# MySQL Instance Manager options section
[manager]
default-mysqld-path = /usr/local/mysql/libexec/mysqld
socket=/tmp/manager.sock
pid-file=/tmp/manager.pid
password-file = /home/cps/.mysqlmanager.passwd
monitoring-interval = 2
port = 1999
bind-address = 192.168.1.5
New Startup / Shutdown cycle for MySQL Server with MySQL Instance Manager
The typical Unix startup/shutdown cycle for a MySQL server with the MySQL Instance Manager enabled is as follows:
1. The /etc/init.d/mysql script starts MySQL Instance Manager.
2. Instance Manager starts the guarded server instances and monitors them.
3. If a server instance fails, Instance Manager restarts it.
4. If Instance Manager is shut down (for example, with the /etc/init.d/mysql stop command), it shuts down all server instances.
Test the new init script
As you recall, we replace the init script. This is a good time to test it out to make sure that you can start / stop your DB server using the instance manager. If all works well, remember to change the preferred to the new init style using
/sbin/chkconfig mysql on
/sbin/chkconfig mysqld off
(Where mysql is the new init script, and mysqld is the old init script)
