Install and first time config mysql database

Objective : This post will show you how to install and config mysql service at the first time.

Requirement :

• No special requirement.

Primary steps :

• Install mysql with yum command.
• First time starting and config.
• Populate with some data.

Steps in detail :

1. Install mysql :

Check if the mysql-server has been installed ?

Server# rpm -qa | grep mysql-server

If not, install it.

Server# yum install mysql

Dependencies Resolved
=================================================================
Package Arch Version Repository Size
=================================================================
Installing:
mysql-server i686 5.1.61-4.el6 base 8.8 M
Installing for dependencies:
mysql i686 5.1.61-4.el6 base 892 k
perl-DBD-MySQL i686 4.013-3.el6 base 134 k
perl-DBI i686 1.609-4.el6 base 705 k
Updating for dependencies:
mysql-libs i686 5.1.61-4.el6 base 1.2 M
Transaction Summary
=================================================================
Install 4 Package(s)
Upgrade 1 Package(s)
Total download size: 12 M

Type "Y" to continue installing ...

2. Config the mysql service.

Starting the service, because this is the first time mysql-server run so it will generate some needed data. 

When done, it will ask you to set the root password !!!

Server# /etc/init.d/mysqld start

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 VMhost password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation which will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly 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.plcd /usr/mysql-test ; perl mysql-test-run.plPlease report any problems with the /usr/bin/mysqlbug script!
Starting mysqld: [ OK ]

Set the mysql root password. Replace the 'secret' with you real password.

Server# /usr/bin/mysqladmin -u root password secret

Try login with the new password.

Server# mysql -u root -p'secret'

Search for some sample config files.

Server# rpm -ql mysql-server | grep cnf

/usr/share/doc/mysql-server-5.1.61/my-huge.cnf
/usr/share/doc/mysql-server-5.1.61/my-innodb-heavy-4G.cnf
/usr/share/doc/mysql-server-5.1.61/my-large.cnf
/usr/share/doc/mysql-server-5.1.61/my-medium.cnf
/usr/share/doc/mysql-server-5.1.61/my-small.cnf

According to your server configuration, pick up the approriate config file and paste it to the /etc/my.cnf.

In my case, I used the my-huge.cnf

Server# vim /etc/my.cnf

[mysqld]
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

Restart the service for apply the new config.

Server# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

3. Populate with some data.

Let's create a sample database with one table account and some sample records.

Login to the mysql console :

Server# mysql -u root -p'secret'

mysql> create database sample;
Query OK, 1 row affected (0.00 sec)
mysql> use sample;
Database changed
mysql> create table account (username nvarchar(50) primary key, password varchar(50));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into account() values ("JohnSmith",password("JohnSmith"));
Query OK, 1 row affected (0.00 sec)
mysql> insert into account() values ("BrianAdam",password("BrianAdam"));
Query OK, 1 row affected (0.00 sec)
mysql> select * from account;
+-----------+-------------------------------------------+
| username | password |
+-----------+-------------------------------------------+
| JohnSmith | *4003D375C77AD7C81D3EF977C94277CBEAAD15FA |
| BrianAdam | *2AAAE6A6EF4DAA217B361329395DCE34C0530DAC |
+-----------+-------------------------------------------+
2 rows in set (0.01 sec)
mysql> exit