Category: MySQL

By default most MySQL RPM packages do not set the MySQL server to start automatically on boot so this needs to be done manually. On RedHat/CentOS the chkconfig command should be used as follows:

To simplify administration of MySQL I like to create a /root/.my.cnf file with the mysql root credentials. This way I can run the mysql command line utility without having to authenticate. Just edit /root/.my.cnf and add the following:

After install MySQL, it’s important that you secure the installation by (a) removing anonymous accounts and (b) setting a password on the admin account. This can be done with mysqladmin or with direct SQL queries. I prefer the SQL queries so that’s what I’ll show here. Start mysql command line utility and select the mysql database. Remove anonymous accounts. Set a password on the mysql root account. Flush privileges.

1. Create the tmp directory:

2. Set permissions:

3. Determine mysql user id:

4. Edit /etc/fstab and add the following line:

5. Mount the new tmpfs partition:

6. Edit /etc/my.cnf and set location of tmp directory:

7. Restart mysqd

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