Reliable Penguin

To setup an alternate instance of MySQL listening on TCP port 3307 on a RHEL5 server follow these steps.

1. Setup a new MySQL config file.

cp /etc/my.cnf /etc/my-3307.cnf


Add a line like this:

port = 3307

to the "[mysqld]" section.

Edit /etc/my-3307.cnf and change:

datadir=/var/lib/mysql


to

datadir=/var/lib/mysql-3307


Change:


socket=/var/lib/mysql/mysql.sock


to:


socket=/var/lib/mysql-3307/mysql.sock


Change the following lines:


log-slow-queries=/var/lib/mysqllogs/slow-log
log-bin=/var/lib/mysqllogs/bin-log
log-bin-index=/var/lib/mysqllogs/bin-log.index
relay-log=/var/lib/mysqllogs/relay-log
relay-log-index=/var/lib/mysqllogs/relay-log.index
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


to:


log-slow-queries=/var/lib/mysqllogs-3307/slow-log
log-bin=/var/lib/mysqllogs-3307/bin-log
log-bin-index=/var/lib/mysqllogs-3307/bin-log.index
relay-log=/var/lib/mysqllogs-3307/relay-log
relay-log-index=/var/lib/mysqllogs-3307/relay-log.index
log-error=/var/log/mysqld-3307.log
pid-file=/var/run/mysqld/mysqld-3307.pid


Change server-id to a unique value:


server-id=2


2. Setup a new service control script.


cp /etc/init.d/mysqld /etc/init.d/mysqld-3307


Edit /etc/init.d/mysqld-3307 and add this line:


MYCNF=/etc/my-3307.cnf


directly after:


# Source networking configuration.
. /etc/sysconfig/network


so that you have:


# Source networking configuration.
. /etc/sysconfig/network

MYCNF=/etc/my-3307.cnf


Next change this function:


get_mysql_option(){
result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}


to:


get_mysql_option(){
result=`/usr/bin/my_print_defaults -c $MYCNF "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}


Notice that the change is to add "-c $MYCNF" to the call to "my_print_defaults".

Finally run the following search/replace commands to fixup the program name, add defaults file to mysqld_safe call and set unique pid and subsys files:


replace 'prog="MySQL"' 'prog="MySQL-3307"' -- /etc/init.d/mysqld-3307
replace '/usr/bin/mysqld_safe' '/usr/bin/mysqld_safe --defaults-file=$MYCNF' \
-- /etc/init.d/mysqld-3307
replace 'mysqld.pid' 'mysqld-3307.pid' -- /etc/init.d/mysqld-3307
replace '/var/lock/subsys/mysqld' '/var/lock/subsys/mysqld-3307' -- /etc/init.d/mysqld-3307


3. Setup directories

mkdir /var/lib/mysql-3307 /var/lib/mysqllogs-3307
chown mysql.mysql /var/lib/mysql-3307/ /var/lib/mysqllogs-3307
chmod o-rwx /var/lib/mysqllogs-3307


4. Set service to start on boot


/sbin/chkconfig mysqld-3307 on


5. Start the new instance:


/sbin/service mysqld-3307 start


On the first startup you should see some output like this:


Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

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 244418-web3.www.idtweet.com password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[ OK ]

Starting MySQL-3307: [ OK ]



6. Set MySQL root password.

/usr/bin/mysqladmin -P 3307 -h 127.0.0.1 -u root --password="" password password 'new-password'

And that's it. You now have an new instance of MySQL listening on port 3307.

Remember that you must tell the mysql command line utilities where to find the instance. For example:

mysql -P 3307 -h 127.0.0.1


or

mysql -S /var/lib/mysql-3307/mysql.sock

Also keep in mind that by default any .my.cnf file in your home directory will be used. You may have to override settings in the .my.cnf file and explicitly provide the user and password when connecting.

Came across a handy configuration setting for mysql:

expire_logs_days = 7

Controls how many days of binary log files should be retained. Very good way to keep the binary logs from over flowing available storage.

Trying to dump and import a mysql database from an old MySQL 3.x server to a 4.x server. Here's an exerpt from the dump file:

DROP TABLE IF EXISTS ds_categories;
CREATE TABLE ds_categories (
catID int(11) NOT NULL auto_increment,
catName varchar(80) default NULL,
catDesc text NOT NULL,
catPhoto varchar(200) NOT NULL default '',
catPhotoWidth int(11) NOT NULL default '0',
catPhotoHeight int(11) NOT NULL default '0',
PRIMARY KEY  (catID),
UNIQUE KEY catID (catID)
) TYPE=MyISAM;

INSERT INTO ds_categories VALUES (0,'DEFAULT','','',0,0);
INSERT INTO ds_categories VALUES (1,'Products','','',0,0);

What's happening, is when it inserts the first row, it's
auto-incrementing the key value to 1, so the next row that tries to
import gets a "Duplicate entry '1' for key 1" error.

The solution was to add the following line to the top of your sql dump file:

SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

Later versions of mysql do this automatically.

Problem is the first insert tries to put a 0 into the autoincrement
field. Normal behavior is for the 0 to be converted to the next
available increment which is one. Then the second row fails because
there is already a 1 in the table. The mode setting above tells mysql to
allow the 0 in the auto increment field.

The RHEL AS4 server had an active RHN membership so installing MySQL server was a quick and easy. Here are the commands:

# up2date --install mysql-server

# /sbin/service mysqld start

# mysqladmin -u root password "my_root_password"

# mysqladmin -u root -h localhost -p password "my_root_password"

The last command will prompt for a password. Enter the value for "my_root_password".

And that's all there was to it!

Recent I had to install MySQL on a RHEL 3 server. Normally I'd do this with RPMs but in this case the client needed an install that exactly matched another server which had been installed using the TAR/GZ binary distribution.

Here are the steps used for the install:

1. Download the binary TAR/GZ distribution. In this case it was named mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz and I downloaded it to /root/archive.

2. Unpack the distribution.

cd /root/archive
tar -xvzf mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz

3. The steps provided in the INSTALL-BINARY text file included in the distribution outline the following steps:


shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf -
shell> ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql &


Taking these one at a time:

shell> groupadd mysql

Skip this step. The mysql group already exists so there is no need to add it.

shell> useradd -g mysql mysql

Skip this step. The mysql user already exists so there is no need to add it.


shell> cd /usr/local
shell> gunzip < /root/archive/mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz | tar xvf -
shell> ln -s mysql-standard-4.1.13-pc-linux-gnu-i686 mysql


Complete this steps as provided.


shell> cd mysql
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .


Complete these steps as provided.

shell> bin/mysqld_safe --user=mysql &

We'll skip this step for now since we need to setup an init script for the server.

That's it for the basic install. Now we need to:

1. Setup an init script and set the mysql server to start on boot.
2. Setup the my.cnf file.
3. Start the mysql server and set initial passwords.
4. Replace default mysql binary with a symlink to the new install.

Now one step at a time:

1. Setup an init script and set the mysql server to start on boot.

The mysql distribution includes a usable init script so we'll copy it into place:

cp support-files/mysql.server /etc/init.d/mysql

And then set the service to start on boot:

/sbin/chkconfig --add mysql

Now verify that the service will start at runlevels 2 to 5:

/sbin/chkconfig --list mysql
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off


Of course it's never that easy. You'll also need to edit the /etc/init.d/mysql script, find the line near the top like:

basedir=.

And change it to read:

basedir=/usr/local/mysql

2. Setup the my.cnf file.

The default RHEL install of mysql which we are not using provided an default my.cnf file. It is workable but we want to be consistent with our production server so we'll copy it's my.cnf file to /etc/my.cnf.

If you don't want to copy the file from another server you can use one of the files provided as part of the mysql distribution in the support-files/ folder.

The my.cnf file that we are using moves the mysql data directroy to /var/lib/mysql.

3. Start the mysql server and set initial passwords.

Now we can start the mysql server with:

/sbin/service mysql start

And we can set the initial passwords with:


./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h localhost.localdomain password 'new-password'


4. Replace default mysql binary with a symlink to the new install.

We want to make sure that we use the new mysql binary instead of the old one. So we'll remove the old binary and symlink the new one in it's place.


rm /usr/bin/mysql
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql


And that's it!