How php.ini limits connections to MySQL

php.ini contains two variables that you need to be aware of:

mysql.max_links = -1

mysql.max_persistent = -1

Setting these values to a number other than -1 (unlimited) in fact enforces a maximum number of connections PER php instance. For example:

If max_links is set to 3 and your script tries to establish more than 3 simultaneous links to various mysql databases, the script will throw a connection error after the 3rd link.

This can be a nasty one to diagnose when all else seems to be working.

binlogs filling up disk

Problem: binlogs fill up storage space
Solution: manage your binlogs

Ever notice your disk utilization fly off the handle due to binlogs? There are a few commands and settings you need to be aware of.

Important SQL Commands

RESET MASTER

Removes all the binary logs listed in the index file, resets the index, and creates a new binary log file. Basically, it starts over the logging from scratch. This command is not safe for Replication environments.

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

Removes binary logs older than a certain date. This command is safe in Replication environments.

Important Settings in /etc/my.cnf

To limit the size of the individual binlog files:

max_binlog_size=200M

To automatically clean log files older than a certain number of days:

expire_logs_days = 14

How to resolve "Host 'host_name' is blocked"

Problem: "Host 'host_name' is blocked"

Solution:

1. increase your max_connect_errors setting in /etc/my.cnf
2. investigate and resolve the reason for your connection failures.

The way that I discovered this MySQL "feature" is by observing a strange phenomenon. Periodically, during peak traffic hours, our servers would lose connection to the database.

The database would continue running, the firewall rules were clear, but for some unknown reason, MySQL would simply refuse the connection.

Apparently, according to the MySQL Documentation for max_connect_errors:

"If there are more than this number of interrupted connections from a host, that host is blocked from further connections. "

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connect_errors

The default value for max_connect_errors is 10, that means that your application will get BANNED after 10 connection failures. Well, a good question to ask is: what exactly is meant by connect errors?

It seems that connections that take more than connect-timeout seconds to complete are considered as connect errors,

therefore setting the connect-timeout setting too low will cause an increase in connection failures, and therefore cause your application to get banned.

MySQL - Sql Compatibility Modes

SQL Modes

Aparently as of MySQL 4.1, MySQL can operate in different SQL modes. You can select the mode at either config time (my.cfg) and runtime (using SQL).

Runtime Example: (Setting SQL Mode @ runtime)
SET SQL_MODE='MYSQL323'

Config Time:
Start mysqld with the --sql-mode="modes" option, or by using sql-mode="modes" in my.cnf (Unix operating systems) or my.ini (Windows). (where modes is a comma separated list of mode values)


In case you're an admin migrating up to a newer server and are losing sleep over what's going to happen to all your legacy code, you can rest guilt free, at least when it comes to sql compatibility.

This capability enables each application to tailor the server's operating mode to its own requirements.

SQL Compatibility Modes (SQL Modes)
sql modes

MySql 5, mysqlmanager, mysqld_safe, init script

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)

Upgrading MySQL 3.23 to MySQL 5.0.27 on FC3

RPM Mirror: ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/MySQL-5.0/


su
cd ~/
wget ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/MySQL-5.0/MySQL-server-5.0.27-0.i386.rpm
wget ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/MySQL-5.0/MySQL-client-5.0.27-0.i386.rpm
wget ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/MySQL-5.0/MySQL-Max-5.0.27-0.i386.rpm
wget ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/MySQL-5.0/MySQL-shared-compat-5.0.27-0.i386.rpm
wget ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/MySQL-5.0/MySQL-shared-5.0.27-0.i386.rpm
wget ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/MySQL-5.0/MySQL-devel-5.0.27-0.i386.rpm
mysqldump -A > all-databases.sql
/sbin/service httpd stop
/sbin/service mysqld stop
yum remove mysql
mv /etc/my.cnf ~/
rpm -iv MySQL-server-5.0.27-0.i386.rpm
rpm -iv MySQL-client-5.0.27-0.i386.rpm
rpm -iv MySQL-shared-5.0.27-0.i386.rpm MySQL-shared-compat-5.0.27-0.i386.rpm MySQL-devel-5.0.27-0.i386.rpm
chown mysql:mysql /var/run/mysqld
chown mysql:mysql /var/lib/mysql
yum install php-mysql
/usr/bin/mysql_fix_privilege_tables
/sbin/service mysql restart


Notes:

Starting from MySQL 5.0.4 the default behaviour is for mysql to start using a new tool called mysqlmanager
mysqlmanager manages multiple instances of mysql on the same machine.

Since previous configurations specified multiple instance configurations in /etc/my.cnf mysql will attempt to start using mysqlmanager - and will fail because mysqlmanager needs to be configured first. solution used here moves the mysql configuration file to the root home dir. this cause mysql to start mysql in safe mode like it used to using default precompiled settings. since new settings need to be introduced anyway it does not make sense to use an old configuration file anyway.