lnmp学习之mysql5.5.32安装优化

一、基础知识
mysql数据库
分类
1、社区版（咋们用的）
2、商业版
3、cluster集群（国内几乎没有）

mysql版本：
5.1====lamp===》5.1.72编译configure，make，make install
5.5====lnmp===》5.5.32编译cmake，make，make install       http://oldboy.blog.51cto.com/2561410/1240412
5.6
5.7

mysql安装方法
1、编译 5.1  5.5不同
2、yum/rpm   简单，但是不能定制
3、二进制包，直接解压，初始化数据库即可，无需编译，dba===lnmp

数据库和web分离，web服务器上如何安装mysql？
编译：到make install结束即可
二进制：解压就可以了

二、安装配置
useradd mysql -s /sbin/nologin -M
[root@nginx soft]# ll
total 205428
-rw-r--r--. 1 root root     53081 Nov 28 04:19 19-lnmp-conf.zip
-rw-r--r--. 1 root root   4984397 Nov 28 04:19 libiconv-1.14.tar.gz
-rw-r--r--. 1 root root   1335178 Nov 28 04:19 libmcrypt-2.5.8.tar.gz
-rw-r--r--. 1 root root    471915 Nov 28 04:19 mcrypt-2.6.8.tar.gz
-rw-r--r--. 1 root root    931437 Nov 28 04:19 mhash-0.9.9.9.tar.gz
-rw-r--r--. 1 root root 186722932 Nov 28 04:19 mysql-5.5.32-linux2.6-x86_64.tar.gz
lrwxrwxrwx. 1 root root        11 Nov 16 07:58 nginx -> nginx-1.8.1
drwxr-xr-x. 9 1001 1001      4096 Nov 16 07:57 nginx-1.8.1
-rw-r--r--. 1 root root    833473 Jan 27  2016 nginx-1.8.1.tar.gz
-rw-r--r--. 1 root root  15008639 Nov 28 04:19 php-5.3.27.tar.gz
[root@nginx soft]# tar -zxvf mysql-5.5.32-linux2.6-x86_64.tar.gz
[root@nginx soft]# mv mysql-5.5.32-linux2.6-x86_64 ../
[root@nginx soft]# cd ..
[root@nginx data]# ln -s mysql-5.5.32-linux2.6-x86_64/ mysql
[root@nginx data]# ls
mysql  mysql-5.5.32-linux2.6-x86_64  nginx  nginx1.8.1  soft
[root@nginx mysql]# mkdir -p /data/db
[root@nginx mysql]# chown -R mysql.mysql /data/
[root@nginx mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/data/mysql/ --datadir=/data/db
WARNING: The host 'nginx' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
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:


/data/mysql//bin/mysqladmin -u root password 'new-password'
/data/mysql//bin/mysqladmin -u root -h nginx password 'new-password'


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


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


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


[root@nginx mysql]# 
[root@nginx mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@nginx mysql]# vi /etc/init.d/mysqld 
basedir=/data/mysql
datadir=/data/db
[root@nginx support-files]# cp my-large.cnf /etc/my.cnf
找到相关目录改上即可
[root@nginx bin]# ./mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log 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> 
[root@nginx bin]# /data/mysql//bin/mysqladmin -u root password '123'
[root@nginx bin]# vi /etc/profile  
# /etc/profile


# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
。。。。。。。。。。。。。。。
export PATH=/data/mysql/bin:$PATH
"/etc/profile" 79L, 1830C written
[root@nginx bin]# . /etc/profile


库优化
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |   比5.1多了一个性能库
| test               |
+--------------------+
4 rows in set (0.00 sec)


mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)


mysql> 


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


mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)


用户优化


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


mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)


mysql> drop user ''@'nginx';
Query OK, 0 rows affected (0.00 sec)


mysql> drop user 'root'@'nginx';
Query OK, 0 rows affected (0.00 sec)


mysql> drop user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)


mysql> drop user ''@'::1';      
ERROR 1396 (HY000): Operation DROP USER failed for ''@'::1'
mysql> drop user 'root'@'::1';
Query OK, 0 rows affected (0.00 sec)


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


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


建用户 ，删用户也可以用delete
mysql> grant all on *.* to nginx@'A' identified by '123'; 
Query OK, 0 rows affected (0.00 sec)


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


mysql> delete from mysql.user where user='nginx' and host='a';
Query OK, 1 row affected (0.00 sec)


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