RHEL install and configure MySQL database

moonstar2k

Q. How do I install MySQL database server under Redhat Enterprise Linux 5?

A. You have two choices:

a) Install MySQL package using RHN

b) Install MySQL rpm from CD/DVD

Red hat Enterprise Install Mysql Database server

The best way to install mysql package is to use RHN (Red Hat Network):

Redhat Enterprise Linux - RHEL 5 MySQL installation

Type the following command as root user:
# yum install mysql-server mysql
Note above yum command works with Fedora core and Cent OS Linux.

Redhat Enterprise Linux - RHEL 4/3 MySQL installation

Type the following command as root user:
# up2date mysql-server mysql

Start MySQL Service

To start mysql server type the following command:
# chkconfig mysqld on
# /etc/init.d/mysqld start

Setup mysql root password

Type the following command to setup a password for root user:
# mysqladmin -u root password NEWPASSWORD

Test mysql connectivity

Type the following command to connect to MySQL server:
$ mysql -u root -p

Fix: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

if you are new to installing mysql server you might probably face this error quite often if you type mysql in the shell prompt.

> mysql
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

To fix:

First start the mysql daemon, then type mysql

> /etc/init.d/mysqld start
> mysql

Bingo! It worked for me!

To update mysql root password

mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD(‘new-password’) WHERE user=’root’;
mysql> FLUSH PRIVILEGES;

To add a new user to mysql

1. First login as root then create a database ‘demo’

> mysql -u root -p
Enter password:
mysql> create database demo;

After that create a new user named ‘guest’ with password ‘guest’ with all previleges assigned to demo database;

mysql> GRANT ALL PRIVILEGES ON mytest.* TO ‘guest’@'localhost’ IDENTIFIED BY ‘guest’ WITH GRANT OPTION;

Then exit mysql and connect through username guest;
That should work!

Correct syntax to start / stop / restart with sudo:
sudo /etc/init.d/mysqld start
sudo /etc/init.d/mysqld status
sudo /etc/init.d/mysqld restart
sudo /etc/init.d/mysqld stop
OR
sudo /sbin/service mysqld start
What is the output of the following command?
netstat -tulnp | grep mysql

啟動mysql時報錯：

Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
071112 00:22:06 mysqld ended

查看日志：

＃less /var/log/mysqld.log

其中有一段如下：


071112 0:22:06 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)
071112 0:22:06 [ERROR] Can't start server: can't create PID file: No such file or directory
071112 00:22:06 mysqld ended

#cd /var/run/
#ls


mysqld目錄不存在，創(chuàng)建它：


#mkdir /var/run/mysqld
#cd /var/run/mysqld


創(chuàng)建文件mysqld.pid：


#touch mysqld.pid
#cd..
#chown -R mysql mysqld .

#cd /usr/local/mysql/
#bin/mysqld_safe --user=mysql &
nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /var/lib/mysql

能正常啟動

#bin/mysqladmin -u root password root

又出錯

[root@localhost mysql]# bin/mysqladmin -u root password root
bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!

[root@localhost mysql]# bin/mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

分析：是/tmp/mysql.sock 不存在


# cd /var/lib/mysql/

由于mysql 默認(rèn)的mysql.sock 是在/var/lib/mysql/mysql.sock，創(chuàng)建符號連接：


# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
# bin/mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.0.45 MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

我將s9當(dāng)眾原來的mysql4.0刪除后，重新裝了個mysql5.0，啟動過程中報一下錯誤，啟動失敗，查了一下群里面的老帖子也沒有個具體的明確說明，還請那位高手說明一下怎么解決。謝謝
error 日志當(dāng)中的記錄：

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist



從發(fā)了帖子，只有人看，沒有人回復(fù)，看到這種情況只能自己解決問題了，自己動手豐衣足食嗎， 嗯。。。 你還別說，還真讓我解決了問題，人啊，都是逼出來的,....沒有辦法，這個問題對于高手可能是很簡單的問題 ，但是對我第一次摸mysql，確實怎么也弄不懂的問題。
好了閑話不多說 說一下我解決的過程：

按照mysql的安裝步驟：
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root  /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> /usr/local/mysql/bin/mysqld_safe --user=mysql &

這樣標(biāo)準(zhǔn)進(jìn)行下來是沒有問題的，但在最后一步啟動mysql的時候我希望將數(shù)據(jù)庫的數(shù)據(jù)文件放在另外一個目錄下面，啟動命令修改為：
shell>/usr/local/mysql/bin/mysqld_safe --user=mysql --log-error=/u01/mysql/mysql_error/error --datadir=/u01/mysql/data

這個時候就出現(xiàn)問題了， 因為在進(jìn)行初始化數(shù)據(jù)庫權(quán)限表的那一步的時候默認(rèn)創(chuàng)建的權(quán)限表在默認(rèn)目錄/usr/local/mysql/var下面，這就造成了，上面的錯誤無法找到 權(quán)限表：Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

解決辦法 在運(yùn)行初始化權(quán)限表的時候使用增加參數(shù)--datadir ，命令格式為：
shell> scripts/mysql_install_db --user=mysql --datadir=/u01/mysql/data
這樣問題就解決了。
這樣問題同樣也會出現(xiàn) 你將自己的數(shù)據(jù)庫的數(shù)據(jù)文件修改為別的目錄的時候，因為你沒有在相應(yīng)的目錄下創(chuàng)建數(shù)據(jù)庫權(quán)限表，解決辦法就是你重新運(yùn)行mysql_install_db 文件，重新生成數(shù)據(jù)庫權(quán)限表，但是相應(yīng)的你也要重新建里用戶，以及設(shè)置權(quán)限； 第二個辦法應(yīng)該將老的權(quán)限表的文件拷貝倒相應(yīng)數(shù)據(jù)庫表對應(yīng)的目錄先即可，但是第二個辦法我沒有用過只是根據(jù)自己的想象猜的。如果有高手請給確認(rèn)一下是否正確