CentOS 6.8 数据库安装5.5.32

1：安装数据库
1）检查版本，内核
[root@mysql1 ~]# cat /etc/redhat-release 
CentOS release 6.8 (Final)
[root@mysql1 ~]# una
unalias  uname    
[root@mysql1 ~]# uname -r
2.6.32-642.el6.x86_64
[root@mysql1 ~]# uname -m
x86_64
2）创建文件夹
[root@mysql1 ~]# cd /home/hubo/tools
-bash: cd: /home/hubo/tools: 没有那个文件或目录
[root@mysql1 ~]# mkdir -p /home/hubo/tools
3）上传软件包解压mysql-5.5.32.tar  cmake-2.8.8.tar
[root@mysql1 tools]# ll
总用量 29580
-rw-r--r--. 1 root root  5691656 11月 30 21:08 cmake-2.8.8.tar.gz
-rw-r--r--. 1 root root 24596474 11月 30 21:08 mysql-5.5.32.tar.gz
[root@mysql1 tools]# tar xf cmake-2.8.8.tar.gz 
4）安装cmake
[root@mysql1 tools]# cd cmake-2.8.8
[root@mysql1 cmake-2.8.8]# ./configure 
报错
Log of errors: /home/hubo/tools/cmake-2.8.8/Bootstrap.cmk/cmake_bootstrap.log
解决
[root@mysql1 cmake-2.8.8]# yum install gcc-c++
[root@mysql1 cmake-2.8.8]# make
[root@mysql1 cmake-2.8.8]# make install
[root@mysql1 cmake-2.8.8]# echo $?
0
[root@mysql1 cmake-2.8.8]# cd ../
安装完毕


5）安装依赖包
[root@mysql1 tools]# yum install ncurses-devel -y




6创建用户和组
[root@mysql1 mysql-5.5.32]# groupadd mysql
[root@mysql1 mysql-5.5.32]# useradd mysql -s /sbin/nologin -M -g mysql


7）上传软件包解压mysql-5.5.32.tar
[root@mysql1 tools]# tar xf mysql-5.5.32.tar.gz 
[root@mysql1 tools]# cd mysql-5.5.32
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
[root@mysql1 mysql-5.5.32]# echo $?
0
[root@mysql1 mysql-5.5.32]# make
[root@mysql1 mysql-5.5.32]# make install
[root@mysql1 mysql-5.5.32]# echo $?
0
[root@mysql1 mysql-5.5.32]# ln -s /application/mysql-5.5.32/ /application/mysql
[root@mysql1 mysql-5.5.32]# ls /application/mysql
bin      data  include         lib  mysql-test  scripts  sql-bench
COPYING  docs  INSTALL-BINARY  man  README      share    support-files
安装完成




[root@mysql1 tools]# cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf 
cp：是否覆盖"/etc/my.cnf"？ y
授权
[root@mysql1 tools]# chown -R mysql.mysql /application/mysql/data/
[root@mysql1 tools]# chmod -R 1777 /tmp/
初始化
[root@mysql1 tools]# cd /application/mysql/scripts/
[root@mysql1 scripts]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql
成功了的话是2个OK
Installing MySQL system tables...
OK
Filling help tables...
OK
[root@mysql1 scripts]# cd ../
[root@mysql1 mysql]# cd /home/hubo/tools/mysql-5.5.32
[root@mysql1 mysql-5.5.32]# /bin/cp support-files/mysql.server /etc/init.d/mysqld
[root@mysql1 mysql-5.5.32]# chmod +x /etc/init.d/mysqld
[root@mysql1 mysql-5.5.32]# /etc/init.d/mysqld start
Starting MySQL..                                           [确定]
安装成功


检查3306端口
[root@mysql1 mysql-5.5.32]# netstat -lntup|grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      28246/mysqld 


进入数据库
[root@nginx bin]# ln -s /application/mysql/bin/mysql /usr/bin/
[root@mysql1 tools]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.32 Source distribution


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@mysql1 tools]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.32 Source distribution


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> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
|      | mysql1    |
| root | mysql1    |
+------+-----------+
6 rows in set (0.00 sec)


mysql> 
========================================================
删除空的用户
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)


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


mysql> 
========================================================
删除用户名为名字的
mysql> delete from mysql.user where host='mysql1';
Query OK, 1 row affected (0.00 sec)


mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)
=======================================================
删除：：1的这个用户
mysql> delete from mysql.user where host='::1';
Query OK, 1 row affected (0.00 sec)


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


mysql> 
==========================================================
把test库干掉
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
[root@mysql1 tools]# chkconfig mysqld on
[root@mysql1 tools]# chkconfig --list mysqld
mysqld         0:关闭1:关闭2:启用3:启用4:启用5:启用6:关闭








===========================建立安装Mysql多实例，以Mysql 5.5为例===============================
1）安装好Mysql依赖包
yum install ncurses-devel -y
yum install libaio-devel -y


2）以root建立Msql账号和组(已完成)
groupadd mysql
useradd mysql -s /sbin/nologin -M -g mysql




3）获得Mysql软件：（已完成）


4）安装Mysql软件
1.解压软件包，移动到规范的软件安装目录。（已完成）
[root@mysql1 tools]# tar xf mysql-5.5.32.tar.gz 
[root@mysql1 tools]# mkdir -p /application/
[root@mysql1 tools]# mv mysql-5.5.32-linux6.8-x86_64 /application/mysql-5.5.32


2.生成不带版本号的软链接/application/mysql,即mysql安装路径
[root@mysql1 tools]# ln -s /application/mysql-5.5.32/ /application/mysql


5）创建Mysql多实例的数据文件目录
我们采用/data 目录作为mysql多实例总的根目录
先把进程关掉
[root@mysql1 ~]# pkill mysqld
[root@mysql1 ~]# ps -ef|grep mysql
root       4341   1709  0 04:07 pts/0    00:00:00 grep mysql
干掉启动命令
[root@mysql1 ~]# rm -f /etc/init.d/mysqld 


创建3306,33072个目录
[root@mysql1 ~]# mkdir -p /data/{3306,3307}/data
看下2个目录结构
[root@mysql1 ~]# tree /data/
/data/           <===总的多实例根目录
├── 3306         <===3306实例的目录
│   └── data     <===3306实例的数据文件目录
└── 3307         <===3307实例的目录
    └── data     <===3307实例的数据文件目录


创建Mysql多实例的配置文件
上传包到cd /home/hubo/tools/
[root@mysql1 ~]# cd /home/hubo/tools/
[root@mysql1 tools]# unzip data.zip 
Archive:  data.zip
   creating: data/
   creating: data/3306/
  inflating: data/3306/my.cnf        
  inflating: data/3306/mysql         
   creating: data/3307/
  inflating: data/3307/my.cnf        
  inflating: data/3307/mysql  
拷贝文件
[root@mysql1 tools]# cp data/3306/my.cnf /data/3306/
[root@mysql1 tools]# cp data/3307/my.cnf /data/3307/
[root@mysql1 tools]# tree /data/
/data/
├── 3306
│   ├── data
│   └── my.cnf
└── 3307
    ├── data
    └── my.cnf


4 directories, 2 files




创建Mysql多实例的启动文件
  inflating: data/3307/mysql  
拷贝文件脚本
[root@mysql1 tools]# cp data/3306/mysql /data/3306/
[root@mysql1 tools]# cp data/3307/mysql /data/3307/
[root@mysql1 tools]# tree data
data
├── 3306
│   ├── my.cnf
│   └── mysql
└── 3307
    ├── my.cnf
    └── mysql


2 directories, 4 files


授权
[root@mysql1 tools]# chown -R mysql.mysql /data
[root@mysql1 tools]# find /data/ -type f -name "mysql"|xargs ls -l
-rw-r--r--. 1 mysql mysql 1305 12月  1 05:52 /data/3306/mysql
-rw-r--r--. 1 mysql mysql 1305 12月  1 05:52 /data/3307/mysql
[root@mysql1 tools]# find /data/ -type f -name "mysql"|xargs chmod +x
[root@mysql1 tools]# find /data/ -type f -name "mysql"|xargs ls -l
-rwxr-xr-x. 1 mysql mysql 1305 12月  1 05:52 /data/3306/mysql
-rwxr-xr-x. 1 mysql mysql 1305 12月  1 05:52 /data/3307/mysql




配置Mysql命令全局使用路径
配置环境变量
[root@mysql1 ~]# vim /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@mysql1 ~]# source /etc/profile


初始化Mysql多实例的数据库文件
[root@mysql1 tools]# cd /application/mysql/scripts
[root@mysql1 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data/ --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK


[root@mysql1 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data/ --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK


[root@mysql1 scripts]# tree /data/3306/data/
[root@mysql1 scripts]# /data/3306/mysql start
Starting MySQL...
[root@mysql1 scripts]# /data/3307/mysql start
Starting MySQL...
数据库起来了
[root@mysql1 scripts]# netstat -lntup|grep 330[6-7]
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      49673/mysqld        
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      50392/mysqld  
安装完成


如果启动不了。就看下错误日志
[root@mysql1 ~]# tail /data/3306/mysql_oldboy3306.err 
171202  4:55:16 InnoDB: 5.5.32 started; log sequence number 0
171202  4:55:17 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
171202  4:55:17 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
171202  4:55:17 [Note] Server socket created on IP: '0.0.0.0'.
171202  4:55:17 [Warning] 'user' entry 'root@mysql2' ignored in --skip-name-resolve mode.
171202  4:55:17 [Warning] 'user' entry '@mysql2' ignored in --skip-name-resolve mode.
171202  4:55:17 [Warning] 'proxies_priv' entry '@ root@mysql2' ignored in --skip-name-resolve mode.
171202  4:55:17 [Note] Event Scheduler: Loaded 0 events
171202  4:55:17 [Note] /application/mysql-5.5.32/bin/mysqld: ready for connections.
Version: '5.5.32-log'  socket: '/data/3306/mysql.sock'  port: 3306  Source distribution




登入3306
[root@mysql1 3306]# mysql -S /data/3306/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32 Source distribution


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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)


mysql> create database d3306;
Query OK, 1 row affected (0.00 sec)


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


mysql> exit
Bye


========================================================================================
登入3307
[root@mysql1 3306]# mysql -S /data/3307/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32 Source distribution


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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)


mysql>


mysql> create database d3307;
Query OK, 1 row affected (0.00 sec)


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


mysql> 




从3307跳到3306数据库里面
mysql> system mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.32-log Source distribution


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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d3306              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)


mysql> 


登入数据库
mysql -S /data/3306/mysql.sock 
mysql -S /data/3307/mysql.sock 


重启数据库
/data/3306/mysql stop 
/data/3306/mysql start




为root增加密码
[root@mysql1 3306]# mysqladmin -u root -S /data/3306/mysql.sock password 'hubo2017'
为mysql设置了密码
[root@mysql2 3306]# mysql -S /data/3306/mysql.sock 
无法直接登录了


停止MYSQL不用密码
进入3306编译器
[root@mysql1 3306]# vi mysql
把密码改为空
mysql_pwd="hubo2017"
改为
mysql_pwd=""
停止成功
[root@mysql1 3306]# /data/3306/mysql stop
Stoping MySQL...
Enter password: 
只有3307了
[root@mysql1 3306]# netstat -lntup|grep 330
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      50392/mysqld 
重启
[root@mysql1 3306]# /data/3306/mysql start
Starting MySQL...
[root@mysql2 3306]# netstat -lntup|grep 330
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      51289/mysqld        
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      50392/mysqld   




授权
[root@mysql1 3307]# find /data -type f -name "mysql" -exec ls -l {} \;
-rwxr-xr-x. 1 mysql mysql 1309 12月  2 05:40 /data/3307/mysql
-rwxr-xr-x. 1 mysql mysql 1309 12月  2 05:39 /data/3306/mysql
[root@mysql1 3307]# find /data -type f -name "mysql" -exec chmod 700 {} \;
[root@mysql1 3307]# find /data -type f -name "mysql" -exec chown root.root {} \;
[root@mysql1 3307]# find /data -type f -name "mysql" -exec ls -l {} \;
-rwx------. 1 root root 1309 12月  2 05:40 /data/3307/mysql
-rwx------. 1 root root 1309 12月  2 05:39 /data/3306/mysql
干掉进程
[root@mysql1 3307]# pkill mysqld
[root@mysql1 3307]# netstat -lntup|grep 330
重启数据库
[root@mysql1 3307]# /data/3306/mysql start
Starting MySQL...
[root@mysql1 3307]# /data/3307/mysql start
Starting MySQL...
[root@mysql1 3307]# netstat -lntup|grep 330
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      53556/mysqld        
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      54273/mysqld 




如何再增加一个MYSQL3308实例
2个实例
[root@mysql1 3307]# ps -ef|grep mysql
root      52834      1  0 05:46 pts/0    00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --defaults-file
=/data/3306/my.cnfmysql     53556  52834  0 05:46 pts/0    00:00:00 /application/mysql-5.5.32/bin/mysqld --defaults-file=/data
/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/data/3306/mysql_oldboy3306.err --open-files-limit=1024 --pid-file=/data/3306/mysqld.pid --socket=/data/3306/mysql.sock --port=3306root      53575      1  0 05:46 pts/0    00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --defaults-file
=/data/3307/my.cnfmysql     54273  53575  0 05:46 pts/0    00:00:00 /application/mysql-5.5.32/bin/mysqld --defaults-file=/data
/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/data/3307/mysql_oldboy3307.err --open-files-limit=1024 --pid-file=/data/3307/mysqld.pid --socket=/data/3307/mysql.sock --port=3307root      54328   1671  0 05:58 pts/0    00:00:00 grep mysql


创建一个3308文件夹
[root@mysql1 3307]# mkdir /data/3308/data -p
拷贝3306里面的my.cnf和mysql
[root@mysql1 ~]# cp /data/3306/my.cnf /data/3308/
[root@mysql1 ~]# cp /data/3306/mysql /data/3308/
授权，一定要记得
[root@mysql1 ~]# chown -R mysql.mysql /data/3308/
[root@mysql1 ~]# cd /data/3308/
[root@mysql1 3308]# ll
总用量 12
drwxr-xr-x. 2 mysql mysql 4096 12月  2 06:00 data
-rw-r--r--. 1 mysql mysql 1899 12月  2 06:02 my.cnf
-rwx------. 1 mysql mysql 1309 12月  2 06:02 mysql
进入编译器
[root@mysql1 3308]# vi my.cnf
改3306为3308
改下server-id = 
[root@mysql1 3308]# vi mysql 
改3306为3308


[root@mysql1 3308]# cd /application/mysql/scripts/
[root@mysql1 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3308/data/
Installing MySQL system tables...
OK
Filling help tables...
OK


启动数据库
[root@mysql1 scripts]# /data/3308/mysql start
Starting MySQL...
[root@mysql1 scripts]# netstat -lntup|grep 330
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      53556/mysqld        
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      54273/mysqld        
tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      55185/mysqld 


设置密码
[root@mysql2 3308]# mysqladmin -u root -S /data/3307/mysql.sock password 'hubo2017'


多实例做完






===============================================================================================