opus:interactive | If These Servers Could Talk

This write-up makes the following assumptions:

• Working copy of FreeBSD 6.2.
• Build: 6.2-RELEASE.

Which should work on 6.2-STABLE and 7.0 as well.

Please let me know if you run into typos or other technical issues when implementing this.

1. Download the latest binaries from mysql.com.

At write-up time this was:

mysql-5.0.45-freebsd6.0-i386.tar.gz
mysql-standard-4.1.22-unknown-freebsd6.0-i386.tar.gz
2. Install MySQL 4.

Uncompress the binary source.

# cd /usr/local
# gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
# ln -s full-path-to-mysql-VERSION-OS mysql4

Create the daemon user and group.

# pw groupadd mysql4
# pw useradd -n mysql4 -c "" -g mysql4 -d /nonexistent -s /usr/sbin/nologin

Set the file permissions.

# cd /usr/local/mysql4
# chown -R root:mysql4 .

Run the setup database setup script.

# scripts/mysql_install_db --user=mysql4

Copy the startup script to the proper location.

# cp /usr/local/mysql4/support-files/mysql.server /usr/local/etc/rc.d/mysql4.server.sh

Change two variables in the start script.

# pico /usr/local/etc/rc.d/mysql4.server.sh

Change “basedir=” to “basedir=/usr/local/mysql4″
Change “datadir=/usr/local/mysql/data” to “datadir=/usr/local/mysql4/data”
Change “pid_file=” to “pid_file=/var/run/mysql4/mysql4.pid”

Create configuration files.

Copy one of the sample configuration files based on your server usage replacing xxxx in the line below.

# cp /usr/local/mysql4/support-files/my-xxxx.cnf /usr/local/mysql4/data/my.cnf

Change variables in the configuration file.

# pico /usr/local/mysql4/data/my.cnf

Add a variable at the top of the [mysqld] section - “user = mysql5″
Change the “port = 3306″ variables to “port = 3304″
Change the “socket = /tmp/mysql.sock” variables to “socket = /tmp/mysql4.sock”

Create a run directory for the MySQL 4 process and set permissions on it.

# mkdir /var/run/mysql4
# chown -R mysql4:mysql4 /var/run/mysql4
3. Install MySQL 5.

Uncompress the binary source.

# cd /usr/local
# gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
# ln -s full-path-to-mysql-VERSION-OS mysql5

Create the daemon user and group.

# pw groupadd mysql5
# pw useradd -n mysql5 -c "" -g mysql5 -d /nonexistent -s /usr/sbin/nologin

Set the file permissions.

# cd /usr/local/mysql5
# chown -R root:mysql5 .

Run the setup database setup script.

# scripts/mysql_install_db --user=mysql5

Copy the startup script to the proper location.

# cp /usr/local/mysql5/support-files/mysql.server /usr/local/etc/rc.d/mysql5.server.sh

Change a few variables in the start script.

# pico /usr/local/etc/rc.d/mysql5.server.sh

Change “basedir=” to “basedir=/usr/local/mysql5″
Change “datadir=” to “datadir=/usr/local/mysql5/data”
Change “pid_file=” to “pid_file=/var/run/mysql5/mysql5.pid”
Change “server_pid_file=” to “server_pid_file=/var/run/mysql5/mysql5.pid”
Change “user=mysql” to “user=mysql5″

Create configuration files.

Copy one of the sample configuration files based on your server usage replacing xxxx in the line below.

# cp /usr/local/mysql5/support-files/my-xxxx.cnf /usr/local/mysql5/my.cnf

Change variables in the configuration file.

# pico /usr/local/mysql5/my.cnf

Add a variable at the top of the [mysqld] section - “user = mysql5″
Change the “port = 3306″ variables to “port = 3305″
Change the “socket = /tmp/mysql.sock” variables to “socket = /tmp/mysql5.sock”

Create a run directory for the MySQL 5 process and set permissions on it.

# mkdir /var/run/mysql5
# chown -R mysql5:mysql5 /var/run/mysql5
4. Start the daemons.
# /usr/local/etc/rc.d/mysql4.server.sh start
# /usr/local/etc/rc.d/mysql5.server.sh start
5. Post installation configuration.

MySQL4:

Connect to MySQL

# /usr/local/mysql4/bin/mysql -u root -P3304 -S/tmp/mysql4.sock

Remove the anonymous account.

mysql> DELET FROM mysql.user WHERE Host='localhost' AND User='';

Set the root password

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('difficultpassword1');
mysql> SET PASSWORD FOR 'root'@'server.domain.com' = PASSWORD('difficultpassword1');

Create a backupoperator account for backup script access to all databases.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'backupoperator'@'localhost' IDENTIFIED BY 'difficultpassword2';

Apply changes.

mysql> FLUSH PRIVILEGES;

Quit.

mysql> QUIT;

MySQL5:

Connect to MySQL

# /usr/local/mysql5/bin/mysql -u root -P3305 -S/tmp/mysql5.sock

Remove the anonymous account.

mysql> DROP USER '';

Set the root password.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('difficultpassword1');
mysql> SET PASSWORD FOR 'root'@'server.domain.com' = PASSWORD('difficultpassword1');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('difficultpassword1');

Create a backupoperator account for backup script access to all databases

mysql> GRANT ALL PRIVILEGES ON *.* TO 'backupoperator'@'localhost' IDENTIFIED BY 'difficultpassword2';

Quit

mysql> QUIT;

That’s it! Create your mysql users and databases and connect your applications.

Notes:

1. Command line connections to mysql now require additional connection info to specify port and socket:
   MySQL4 - # /usr/local/mysql4/bin/mysql -u username -ppassword -P3304 -S/tmp/mysql4.sock
MySQL5 - # /usr/local/mysql5/bin/mysql -u username -ppassword -P3305 -S/tmp/mysql5.sock
2. Remote connections to mysql will require specifying the correct port in the connection string instead of assuming port 3306.