Top Nav

Archive | MySQL

Setup Alternate MySQL Instance

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.

Add a line like this:

to the “[mysqld]” section.

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

to

Change:

to:

Change the following lines:

to:

Change server-id to a unique value:

2. Setup a new service control script.

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

directly after:

so that you have:

Next change this function:

to:

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:

3. Setup directories

4. Set service to start on boot

5. Start the new instance:

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

6. Set MySQL root 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:

or

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.

0

MySQL Binary Log Rotation

Came across a handy configuration setting for mysql:

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

0

MySQL Import Problem – Autoincrement

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:

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:

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.

0

Easy MySql Install on RHEL AS4

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!

0

MySQL 4.1.13 Binary Install To RHEL 3 Taroon

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.

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

Taking these one at a time:

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

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

Complete this steps as provided.

Complete these steps as provided.

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:

And then set the service to start on boot:

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

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:

And change it to read:

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:

And we can set the initial passwords with:

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.

And that’s it!

0