mysql线上安装部署

线上部署考虑因素：
版本选择，5.1、5.5还是5.6？

5.1官方已不再维护，不建议
5.5是现在线上使用最多的版本
5.6最新的稳定版，已发布3年多，现在使用的也很多

分支选择,官方社区版?persona server ? Mariadb ?

官方版 推荐使用
persona server 管理性能上有改进，并且完全兼容mysql
Mariadb 是mysql原班人马开发的

安装方式，包安装？二进制包安装源码安装？

线上推荐使用二进制包安装

路径配置，参数配置（尽量模板化、标准化）

一个实例多个库或多个实例单个库？

如果只部署一个实例，因为mysql是单进程的，对多核的利用不充分，导致资源浪费
多实例的方式，是现在主流的线上部署方式

二进制安装mysql
1，下载软件安装包：

[root@miles software]# wget http://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz
[root@miles software]# ll
total 305520
-rw-r--r--. 1 root root 312845162 Nov  2 16:09 mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz

2．解压放到指定目录（比如Usmoca）
[root@miles software]# tar zxf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz

[root@miles software]# mv mysql-5.6.27-linux-glibc2.5-x86_64 /usr/local/

[root@miles local]# mv mysql-5.6.27-linux-glibc2.5-x86_64/ mysql5.6.27
3．将MySQL目录放到PATH中

[root@miles bin]# vi /etc/profile
export MYSQL_HOME=/usr/local/mysql5.6.27
export PATH=$MYSQL_HOME/bin:$PATH
export C_INCLUDE_PATH=$MYSQL_HOME/include
export LIBDIR=/usr/local/lib
export LD_LIBRARY_PATH=/usr/local/lib:$MYSQL_HOME/lib
export LD_RUN_PATH=/usr/local/lib

[root@miles bin]# source /etc/profile

4，初始化实例，编辑配置文件并启动

初始化实例
[root@miles scripts]# pwd
/usr/local/mysql5.6.27/scripts
[root@miles scripts]# ./mysql_install_db  -h  
--basedir=path       The path to the MySQL installation directory.
--datadir=path       The path to the MySQL data directory.
                       If missing, the directory will be created, but its
[root@miles scripts]# ./mysql_install_db  --basedir=/usr/local/mysql5.6.27/  --datadir=/data/mysql/
生成的实例的数据文件
[root@miles mysql]# pwd
/data/mysql
[root@miles mysql]# ll
total 110604
-rw-rw----. 1 root root 12582912 Dec  1 20:11 ibdata1
-rw-rw----. 1 root root 50331648 Dec  1 20:11 ib_logfile0
-rw-rw----. 1 root root 50331648 Dec  1 20:11 ib_logfile1
drwx------. 2 root root     4096 Dec  1 20:11 mysql
drwx------. 2 root root     4096 Dec  1 20:11 performance_schema
drwx------. 2 root root     4096 Dec  1 20:11 test

编辑配置文件
[root@miles mysql]# cp /usr/local/mysql5.6.27/my.cnf ../
[root@miles mysql]# cd ..
[root@miles data]# vi my.cnf 
basedir =/usr/local/mysql5.6.27
datadir =/data/mysql/
port =3333
server_id =3333
socket =/data/mysql.sock

join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M  

character-set-server=utf-8
max_connections=2000

log_bin=/data/mysql/mysql-bin
expire_logs_days=10
max_binlog_size=100M
binlog_format=ROW

innodb_flush_log_trx_commit=2
sync_binlog=0

tmpdir=/data/mysql/
log-error=/data/mysql/error.log

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
port =3333
socket =/data/mysql/mysql.sock

[mysqld_safe]
ledir=/usr/local/mysql5.6.27/bin
nice=0
user=mysql

[mysqldump]
quick
quote-names
max_allowed_packet=16M
default-character-set=utf8

[mysql]
default-character-set=utf8
"my.cnf" 62L, 1428C written

启动mysql
[root@miles data]# /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my.cnf  &

[root@miles ~]# ps -ef |grep mysql
root       3083   1545  0 07:59 pts/1    00:00:00 /bin/sh /usr/local/mysql5.6.27/bin/mysqld_safe --defaults-file=/data/my.cnf
mysql      3431   3083  0 07:59 pts/1    00:00:04 /usr/local/mysql5.6.27/bin/mysqld --defaults-file=/data/my.cnf --basedir=/usr/local/mysql5.6.27 --datadir=/data/mysql/ --plugin-dir=/usr/local/mysql5.6.27/lib/plugin --user=mysql --log-error=/data/mysql/error.log --pid-file=/data/mysql//miles.pid --socket=/data/mysql.sock --port=3333

5，账户安全设置

[root@miles ~]# mysql -uroot --socket=/data/mysql.sock
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | miles     |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |
|      | miles     |          |
+------+-----------+----------+
6 rows in set (0.01 sec)


mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.01 sec)

mysql> delete from mysql.user where host <>'localhost';
Query OK, 3 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
+------+-----------+----------+
1 row in set (0.00 sec)


mysql> set password for root@'localhost' = password('beijing');
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 可以看到test数据库存在安全隐患，任何用户都可以在test进行操作
mysql> select * from mysql.db \G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
2 rows in set (0.00 sec)

mysql> delete from mysql.db;
Query OK, 2 rows affected (0.00 sec)

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

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

编译安装MySQL
1．下载Mysql源码安装包

[root@miles software]# ll
total 32548
-rw-r--r--. 1 root root 33327156 Dec  2 11:58 mysql-5.6.27.tar.gz

2 . 安装必要包( make cmake bison-devel ncurses-devel build-essential)

[root@miles software]# yum install make cmake bison ncurses build-essential

3．Cmake配置mysq编译选项,可以定制需要安装的功能，编译选项参考：
cmake 使用说明

[root@miles mysql-5.6.27]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql  \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DMYSQL_TCP_PORT=3307   \
-DMYSQL_UNIX_ADDR=/data/mysql.sock  \
-DWITH_INNOBASE_STORAGE_ENGINE=1    \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1   \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1  \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1

4 . make & & make Install

[root@miles mysql-5.6.27]# make && make install

以下两部与二进制安装相同，请参照上文
5．初始化实例，编辑配置文件并启动
6．账户安全设置