Setting up MySQL in CentOS

This setup is fairly basic and is for CentOS 6.

Packages

mysql, mysql-server

First time setup

Start the database service mysqld start

Run /usr/bin/mysql_secure_installation

This script will walk you through the basic securing of mysql.  It will also set the root password for mysql.  The defaults should be fine here.

Hardware

This part is for users who want to run mysql on a separate disk from the OS.  You will need to have created a filesystem and mounted it some place else on the system to do this.  I mount my mysql partition at /mysql1 for example.  I’m not including partitioning or filesystem creation here because you really should know that if you are reading and doing this.   Also I realize my.cnf can be changed to point to this new location but it’s easier to just use a symlink from the default location.

service mysqld stop to stop the database.

Now copy all the files from the default mysql location to the new mount point.

find /usr/lib/mysql -print |cpio -pdvm /mysql1/mysql

Move old files to a different location in case we break something.

cd /var/lib

mv mysql mysql.os

Now create a symlink to the new location.

cd /var/lib

ln -s /mysql1/mysql

service mysqld start

Configuration

I’m not going to put specifics here.  Configuring mysql depends a lot on your hardware and how large your database is.  I set mine up to use innodb.  For a small database I setup a single data file set to autoextend.  For a larger database I use the file per table setting.  Most other settings depend on how much ram you want to give mysql.  On my server I have it setup to use about 1GB of ram.  Typically mysql will use less than what you configure it for if it doesn’t need it.

/etc/my.cnf

This is an example of my mysql config file.  My database is about 1GB in size.  The server has a lot of ram so I give MySQL a decent amount to use.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K

innodb_data_file_path = ibdata1:4096M:autoextend
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

 

Upgrading

Before doing this backup your databases using mysqldump.

service mysqld stop

yum update I usually update the entire system at the same time.

service mysqld start

/usr/bin/mysql_upgrade  This script updates all of the mysql internal tables.  It is not always needed but it is good to run after an update.

Leave a Reply