mysql部署

由于mysql用yum安装的话，虽然很方便，但可定制型就很差，如果用编译安装的话，虽然可定制型好，但安装比较复杂而且安装速度很慢

这里我们用二进制包来安装

二进制包下载地址为：wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.32-linux2.6-x86_64.tar.gz

1、创建数据库用户mysql:useradd  mysql -s /sbin/nologin -M

2、切换到工具目录、下载并解压mysql二进制包，将解压完的二进制包移动到/application目录下，做软连接

[root@lnmp01 ~]# cd  /home/oldboy/tools/

[root@lnmp01 tools]# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.32-linux2.6-x86_64.tar.gz

[root@lnmp01 tools]# tar xf mysql-5.5.32-linux2.6-x86_64.tar.gz 

[root@lnmp01 tools]# mv mysql-5.5.32-linux2.6-x86_64  /application/mysql-5.5.32

[root@lnmp01 tools]# ln -s /application/mysql-5.5.32  /application/mysql

 3、接下来初始化数据库：

[root@lnmp01 tools]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/  --user=mysql
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:

/application/mysql//bin/mysqladmin -u root password 'new-password'
/application/mysql//bin/mysqladmin -u root -h lnmp01 password 'new-password'

Alternatively you can run:
/application/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 /application/mysql/ ; /application/mysql//bin/mysqld_safe &

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

Please report any problems with the /application/mysql//scripts/mysqlbug script!

4、授权mysql管理数据库文件

[root@lnmp01 tools]# chown  -R mysql.mysql /application/mysql/

5、生成mysql配置文件

[root@lnmp01 tools]# \cp /application/mysql/support-files/my-small.cnf /etc/my.cnf

6、配置启动mysql

默认的mysql服务启动目录是/usr/local/mysql，可以从 /application/mysql/bin/mysqld_safe启动服务中看出，所以这里要改。

[root@lnmp01 tools]# sed -i "s#/usr/local/mysql#/application/mysql#g"  /application/mysql/bin/mysqld_safe 

启动mysql:　　[root@lnmp01 tools]# /application/mysql/bin/mysqld_safe &

这里的mysql服务的启动是非传统的方式，应该用传统的方式平滑启动mysql。如下配置：

[root@lnmp01 ~]# cp  /application/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@lnmp01 ~]# pkill  mysqld

[root@lnmp01 ~]# /etc/init.d/mysqld start


[root@lnmp01 ~]# lsof -i :3306           
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  13327 mysql   10u  IPv4  49690      0t0  TCP *:mysql (LISTEN)


[root@lnmp01 ~]# chkconfig  mysqld on　

7、配置环境变量

vim  /etc/profile

PATH="/application/mysql/bin:$PATH"

source  /etc/profile

8、运行mysql进入数据库配置

[root@lnmp01 tools]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

9、设置和更改密码：

[root@lnmp01 ~]# mysqladmin -uroot password "123456"

修改密码为：[root@lnmp01 ~]# mysql -uroot -p123456  password "abc"

这时候再用mysql命令直接登录就不可以了，这时候就登录就需要指定用户名和密码才能登录

[root@lnmp01 ~]# mysql -uroot -p123456

10、数据库优化

进入数据库后，应删除test数据库，还要删除mysql数据库中user表的多余的账号

mysql> select  user,host  from user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | lnmp01    |
| root | lnmp01    |
|      | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)

比如删除ipv6这行：mysql> drop  user 'root'@'::1'; 

同理还要删除空用户和lnmp01主机这行，最后的结果为：

mysql> select  user,host  from user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

如果主机名为大写或者特殊字符组成，那么用drop 删除账号是删不了的，要用delete.比如：delete  from user where  user="root"  and  host="A";　

修改后要用flush刷新，要权限生效

mysql> flush  privileges; 

 

在mysql中查帮助要用help,比如查看drop命令的使用：

mysql> help drop;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   ALTER TABLESPACE
   DEALLOCATE PREPARE
   DROP DATABASE
   DROP EVENT
   DROP FUNCTION
   DROP FUNCTION UDF
   DROP INDEX
   DROP LOGFILE GROUP
   DROP PROCEDURE
   DROP SERVER
   DROP TABLE
   DROP TABLESPACE
   DROP TRIGGER
   DROP USER
   DROP VIEW

mysql> help  drop user;
Name: 'DROP USER'
Description:
Syntax:
DROP USER user [, user] ...

The DROP USER statement removes one or more MySQL accounts and their
privileges. It removes privilege rows for the account from all grant
tables. To use this statement, you must have the global CREATE USER
privilege or the DELETE privilege for the mysql database. Each account
name uses the format described in
http://dev.mysql.com/doc/refman/5.5/en/account-names.html. For example:

DROP USER 'jeffrey'@'localhost';

If you specify only the user name part of the account name, a host name
part of '%' is used.

URL: http://dev.mysql.com/doc/refman/5.5/en/drop-user.html

查看当前使用的数据库：mysql> select  database();

查看当前用户：mysql> select user();