[ MySQL 小學堂 ] MySQL 5.0 Installation

轉載自 這裡 
Preface : 
Since Apache and MySQL servers must be installed prior to the PHP installation, I recommend installing the triad in this order: MySQL, Apache, PHP. You may well have some MySQL server already installed – in that case you can skip directly to the Apache 2 installation. However, it's a good idea to reinstall everything, in order to have the most recent versions of the software. 
There are several options for how to install MySQL : 

* using YaST – the easiest and fastest way. However, the version of MySQL bundled with SuSE installation is usually NOT the best (i.e. the most recent) available,
* RPM installation – supposedly also fast and simple, I've never tried though. The only drawback here is that MySQL is not installed into a single destination – it's scattered across several directories. I like to keep things tidy, so I skipped this option,
* installing binaries – downloading precompiled files from the mysql.com website, copying them into a directory of your choice, and doing some simple configuration. I tried this, but it didn't work for me – for some reason the MySQL server wouldn't start,
* installing from source – I would recommend this. Yes, it takes some time and effort, but you will get the most recent MySQL installed in a single location on your system, and everything will be configured according to your needs.

The rest of this chapter deals with the 4th option – the installation of MySQL from the source. 

Prerequisites : 
Make sure you have superuser (root) privileges and user "mysql" already exists in your system. If not, create one : 

# groupadd mysql
# useradd -g mysql mysql

This will be the default user under which the MySQL server will be running. 

Download The Source : 
First, download MySQL source . You need the mysql-5.x.xx.tar.gz tarball file. 

Unpack, Configure, Compile : 
So you have downloaded the mysql-5.x.xx.tar.gz file. You know the drill: unpack, configure, make, make install : 

# tar -xzf mysql-5.0.37.tar.gz
# cd mysql-5.0.37
# ./configure --prefix=/usr/local/mysql-5.0.37 --with-charset=utf8 --with-collation=utf8_general_ci
# make
# make install

We used the --with-charset and --with-collation options to set the default character set and collation – otherwise it would have been the default Swedish collation. 
I recommend creating a symbolic link called "mysql" pointing to the MySQL installation directory, in order to refer to it from elsewhere easier : 
# ln -s /usr/local/mysql-5.0.37/ /usr/local/mysql 

This way we can always refer to MySQL installation directory as /usr/local/mysql . 

Create My.Cnf File : 
To complete MySQL server installation, you have to create a configuration file. It offers several security and control options (here you can limit system resources to be used by MySQL server, set the default collation and character set etc.). You need not to create a brand new configuration file – there are 4 pre-made files in the support-files/ directory. Read the information in those files to determine which one to use. For small servers (e.g. testing servers, or servers of a limited performance), my-small.cnf file is the best option. Copy the file of your choice to /etc/my.cnf : 

# cp support-files/my-small.cnf /etc/my.cnf
# chown root /etc/my.cnf
# chgrp root /etc/my.cnf
# chmod 644 /etc/my.cnf

We have made sure both the owner and user group of the my.cnf file are "root" and the access privileges are properly set. Finally edit the file : 

# vi /etc/my.cnf

Search for [mysqld] clause, and add immediately below it : 

user = mysql

We have specified that MySQL service is to be run with user "mysql" privileges. 
If you want to use InnoDB databases (what you probably will), uncomment (and perhaps edit) all innodb options in the my.cnf file. Save all changes ( :wq). 

Additional Settings : 
For proper functioning, MySQL needs a "mysql" database. To create this database, simply run : 

# /usr/local/mysql/bin/mysql_install_db --user=mysql

The script will create /usr/local/mysql/var/ directory containing the necessary databases. This directory serves as a default storage for all databases you will create. Make sure it is writable by "mysql" system user! 

Start Server, Check It, Connect : 
Now you are ready to start your MySQL server for the first time. 

# /usr/local/mysql/bin/mysqld_safe --user=mysql &

Hit enter again to get your prompt back. The MySQL server should now be running. To check that server is running and works properly enter : 

# /usr/local/mysql/bin/mysqladmin version
mysqladmin Ver 8.42 Distrib 5.1.36, for suse-linux-gnu on i686
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.

You should get some response about the server software version. Let's Connect to MySQL server : 

# /usr/local/mysql/bin/mysql -u root

If you get a welcome message and the prompt changes to mysql>, the server works and everything is fine. If this failed for any reason, it may indicate some problems with your installation/configuration. 

Set The Root Password : 
Now, before you do anything else, set root user's password (!). Stay connected to MySQL and enter : 

DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;
SELECT Host, User FROM mysql.user;

Look for the record that has root in the User column and something other than localhost in the Host column. This is the host_name. 

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
SET PASSWORD FOR 'root'@'host_name' = PASSWORD('new_password');

Remember, this is the MySQL superuser for all databases. Therefore you should use a strong password and keep it safe. After you are finished, exit MySQL : 

quit

Restart MySQL Server : 
After everything is set up, restart MySQL server : 

# /usr/local/mysql/bin/mysqladmin -u root -p shutdown
# /usr/local/mysql/bin/mysqld_safe --user=mysql &

Voila, your MySQL server is up and running! 

Automatic Startup : 
Set up an automatic startup so you don't need to start MySQL server manually after each system reboot. Go back to the directory where you extracted the downloaded mysql tarball file. Enter : 

# cp support-files/mysql.server /etc/init.d/mysql
# chmod 755 /etc/init.d/mysql
# chkconfig --add mysql
# chkconfig --level 35 mysql on

Supplement : 
* MySQL Reference Manual 
* [Linux 命令] chkconfig : 設定系統在不同的runlevel之下所執行的服務 
* 數據庫預設使用 utf-8 設定 : 

1. 用 vi /etc/my.cnf 設定MySQL中的語系為:( (記得restart)

[mysqld]
init_connect='SET NAMES utf8'
default-character-set=utf8
[client]
default-character-set = utf8

2. 建立資料庫時選擇語系: (記得清除DB Cache)

DROP DATABASE IF EXISTS `aa`;
CREATE DATABASE `aa` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `aa`;

CREATE TABLE IF NOT EXISTS `aat` (
`id` char(1) NOT NULL default '1',
`myStr` varchar(200) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;