MySQL Setup

After compile & install MySQL there still something need to do.
In this case, MySQL install to /media/disk/usr/mysql
So, the $BASEDIR is /media/disk/usr/mysql

1.Create $BASEDIR/etc/my.cnf

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
user    = mysql
port        = 3306
socket        = /tmp/mysqld.sock
datadir        = /media/disk/usr/mysql/var
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
character-set-server=utf8
collation-server=utf8_general_ci


# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
server-id    = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# binary logging format - mixed recommended
#binlog_format=mixed

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /media/disk/usr/mysql/var
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /media/disk/usr/mysql/var
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

2.Create user mysql & group mysql

3.Change owner to user & group mysql
chown -R mysql:mysql /media/disk/usr/mysql/*

4.Install database
The datadir in my.cnf is the path to install database which is /media/disk/usr/mysql/var

Execute

# mysql_install_db --defaults-file=/media/disk/usr/mysql/etc/my.cnf --user=mysql

Installing MySQL system tables...
700101  0:34:10 [Warning] option 'myisam_max_extra_sort_file_size': unsigned value 2147483648 adjusted to 2147483647
OK
Filling help tables...
700101  0:34:10 [Warning] option 'myisam_max_extra_sort_file_size': unsigned value 2147483648 adjusted to 2147483647
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:

/media/disk/usr/mysql/bin/mysqladmin -u root password 'new-password'
/media/disk/usr/mysql/bin/mysqladmin -u root -h localhost password 'new-password'

Alternatively you can run:
/media/disk/usr/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /media/disk/usr/mysql ; /media/disk/usr/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /media/disk/usr/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /media/disk/usr/mysql/bin/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

5.Setup MySQL user root password

# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

# mysql -u root mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.4.2-beta Source distribution

No entry for terminal type "vt102";
using dumb terminal settings.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATE user SET Password=PASSWORD('1234') where USER='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> exit
Bye

# killall -9 mysqld_safe mysqld
[2] - Killed                     bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking

6.Run MySQL server
# mysqld_safe --defaults-file=/media/disk/usr/mysql/etc/my.cnf &

7.Login MySQL

# mysql -u root -p

8.Example MySQL & php

mysql_connect.php

mysql_connect("localhost", "root", "1234") or die(mysql_error());
echo "Connected to MySQL
";
?>

mysql_select_db.php

mysql_connect("localhost", "root", "1234") or die(mysql_error());
echo "Connected to MySQL
";
mysql_select_db("test") or die(mysql_error());
echo "Connected to Database";
?>

Reference
http://amzshar.blogspot.com/2007/11/dba-mysqlsql.html