图片来自网络

文/Bruce.Liu1

1.安装简介

MySQL安装在网上的帖子再多不过了，但是真正能将网上的文章无缝的使用在生产环境中的寥寥无几，并且网上的文章一般都是局限于都一个知识点，并没有一个系统整体性的文章，本文即系统的讲解MySQL各种方式安装规划及步骤；首先我们先了解一下MySQL常见的安装方式：

• 源码编译安装方式

与二进制发行版本相比，如果我们选择了通过源代码进行安装，那么在安装过程中我们能够对MySQL所做的调整将会更多更灵活一些。

优点：

1. 针对自己的硬件平台选用合适的编译器来优化编译后的二进制代码；
2. 根据不同的软件平台环境调整相关的编译参数；
3. 针对我们特定应用场景选择性地对组件进行裁剪；
4. 根据我们的所需要存储的数据内容选择只安装我们需要的字符集；
5. 等等其他一些可以根据特定应用场景所作的各种调整。

缺点：

1. 对编译参数的不够了解造成编译参数使用不当可能使编译出来的二进制代码不够稳定；
2. 对自己的应用环境把握失误而使用的优化参数可能反而使系统性能更差；
3. 还有一个并不能称之为隐患的小问题就是源码编译安装将使安装部署过程更为复杂，所花费的时间更长；

建议：如果没有对编译器、glibc、系统API有足够的了解，不建议大家使用源码编译安装。因为编译安装、编译器、编译参数优化、兼容性等工作是MySQL开发部门的一个Team完成的。据传言通过源码编译安装能够大部分提升性能的话题，我就不加以评论了。

• 通用二进制安装方式

二进制安装方式，是刚才我们提到的，MySQL开发团队将源代码进行编译安装后的一个发行格式，这种发行格式中又分为两种：

• Linux二进制通用版本：

由于Linux的分支过多，每个Linux软件包的管理方式也各有不同：常见的RHEL、CentOS是RPM包管理方式、Ubuntu是DEB的管理方式，有的同学就会问了，那SUSE Linux也是RPM包管理方式，和RHEL是否通用呢？ 答案是：肯定不可以，因为即使是RPM包后缀名一致，但这些开源软件在不同的linux中的软件本身版本、编译环境以及编译参数都各有不同。MySQL想要在这么多不同的Linux版本中做一个通用的二进制格式，即解压就能用的就是Linux二进制通用版本；类似于windows上的绿色免安装的软件。

• Linux\Unix发行商订制版本：

以上二进制通过版本虽然好，但却做不到向源代码方式一样万能，因为二进制版本所在的编译环境是适合于大部分平台，但总有不兼容的平台，因此MySQL还为不同平台定制化了专属自己格式的软件，常见的版本如：

图片来自原创

1. Red Hat Enterprise Linux / Oracle Linux /CentOS Linux
2. Ubuntu Linux
3. Debian Linux
4. SUSE Linux
5. FreeBSD 10.0
6. Windows

2. MySQL安装

2.1.源码编译安装(5.6)

2.1.1.软件参考文档

参考文档：https://dev.mysql.com/doc/refman/5.7/en/source-installation.html
编译参数：https://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html

软件下载：
https://downloads.mysql.com/archives/get/file/mysql-5.6.12.tar.gz
http://www.cmake.org/files/v2.8/cmake-2.8.11.1.tar.gz
http://ftp.gnu.org/gnu/bison/bison-2.7.tar.gz
http://ftp.gnu.org/gnu/m4/m4-1.4.16.tar.gz

2.1.2.系统环境介绍

• 系统版本
  CentOS release 6.5 (Final) x86_64

• MySQL版本
  mysql-5.6.12.-x86_64(源代码包)

2.1.3.安装系统要求

2.1.3.1.系统基本库

• ncurses-devel
  用于终端操作的开发包。

• zlib
  MySQL使用zlib进行压缩

• libxml
  用于XML输入输出方式的支持。

• openssl
  使用openssl安全套接字方式通信。

• dtrace
  用于诊断MySQL问题。

2.1.3.2.必要的编译环境

• gcc/g++
  MySQL 5.6开始，需要使用g++进行编译。

• cmake
  MySQL 5.5开始，使用cmake进行工程管理，cmake需要2.8以上版本。

• bison
  MySQL语法解析器需要使用bison进行编译。

• m4
  或预处理器一般用作文本替换工具。

2.1.4.安装基础环境

• 检查基础包

# rpm -qa | grep gcc-c++
# rpm -qa | grep gcc
# rpm -qa | grep zlib
# rpm -qa | grep ncurses-devel
# rpm -qa | grep libxml 
# rpm -qa | grep openssl

• 安装cmake编译器

# cd /usr/local/src
# tar -xvf cmake-2.8.11.1.tar.gz
# cd cmake-2.8.11.1
# ./bootstrap
# make && make install

• 安装m4

# cd /usr/local/src
# tar -xvf m4-1.4.16.tar.gz
# cd m4-1.4.16
# ./configure && make && make install

• 安装bison

# cd /usr/local/src
# tar -xvf bison-2.7.tar.gz
# cd bison-2.7
# ./configure && make && make install

2.1.5.编译安装MySQL

2.1.5.1.创建用户及目录

# /usr/sbin/groupadd mysql
# /usr/sbin/useradd -g mysql mysql
# mkdir /usr/local/mysql
# chown -R mysql:mysql /usr/local/mysql
# mkdir /var/run/mysqld
# chown -R mysql:mysql /var/run/mysqld

2.1.5.2.编译MySQL

# cd /usr/local/src
# tar -xvf mysql-5.6.12.tar.gz 
# export C_FLAGS="-fPIC -Wall -Wextra -Wformat-security -Wwrite-strings -Wdeclaration-after-statement -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -I/export/home/pb2/build/sb_0-14808650-1427302228.24/release/include -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/include -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/sql -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/regex -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/zlib -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/extra/yassl/include -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/extra/yassl/taocrypt/include -I/export/home/pb2/build/sb_0-14808650-1427302228.24/release/sql    -DHAVE_YASSL -DYASSL_PREFIX -DHAVE_OPENSSL -DMULTI_THREADED"
# export C_DEFINES="-DHAVE_CONFIG_H -DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER"
# export CXX_FLAGS="-fPIC -Wall -Wextra -Wformat-security -Woverloaded-virtual -Wno-unused-parameter -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -I/export/home/pb2/build/sb_0-14808650-1427302228.24/release/include -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/include -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/sql -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/regex -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/zlib -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/extra/yassl/include -I/export/home/pb2/build/sb_0-14808650-1427302228.24/mysql-5.6.24/extra/yassl/taocrypt/include -I/export/home/pb2/build/sb_0-14808650-1427302228.24/release/sql    -DHAVE_YASSL -DYASSL_PREFIX -DHAVE_OPENSSL -DMULTI_THREADED"
# export CXX_DEFINES="-DHAVE_CONFIG_H -DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER"
# cd mysql-5.6.12
# cmake \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DDEFAULT_CHARSET=utf8 \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DWITH_EXTRA_CHARSETS=utf8,gbk \
> -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
> -DWITH_FEDERATED_STORAGE_ENGINE=1 \
> -DWITH_PARTITION_STORAGE_ENGINE=1\
> -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
> -DMYSQL_DATADIR=/usr/local/mysql/data \
> -DWITH_SSL=bundled \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
> -DENABLE_DOWNLOADS=1 
# make && make install

2.1.6.初始化MySQL实例

准备一个默认的my.cnf配置文件

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
user=mysql
# port=13306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

初始化MySQL实例(注意初始化是否成功)

# cd /usr/local/mysql/
# ./scripts/mysql_install_db --defaults-file=/etc/my.cnf

......
2017-08-03 16:02:46 29894 [Note] InnoDB: 5.6.12 started; log sequence number 0
2017-08-03 16:02:46 29894 [Note] Binlog end
2017-08-03 16:02:46 29894 [Note] InnoDB: FTS optimize thread exiting.
2017-08-03 16:02:46 29894 [Note] InnoDB: Starting shutdown...
2017-08-03 16:02:48 29894 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK

Filling help tables...2017-08-03 16:02:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-08-03 16:02:48 29917 [Note] InnoDB: The InnoDB memory heap is disabled
2017-08-03 16:02:48 29917 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-08-03 16:02:48 29917 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-08-03 16:02:48 29917 [Note] InnoDB: Using CPU crc32 instructions
2017-08-03 16:02:48 29917 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-08-03 16:02:48 29917 [Note] InnoDB: Completed initialization of buffer pool
2017-08-03 16:02:48 29917 [Note] InnoDB: Highest supported file format is Barracuda.
2017-08-03 16:02:48 29917 [Note] InnoDB: 128 rollback segment(s) are active.
2017-08-03 16:02:48 29917 [Note] InnoDB: Waiting for purge to start
2017-08-03 16:02:48 29917 [Note] InnoDB: 5.6.12 started; log sequence number 1625977
2017-08-03 16:02:48 29917 [Note] Binlog end
2017-08-03 16:02:48 29917 [Note] InnoDB: FTS optimize thread exiting.
2017-08-03 16:02:48 29917 [Note] InnoDB: Starting shutdown...
2017-08-03 16:02:49 29917 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
......

2.1.7.启动数据库

# /usr/local/mysql/bin/mysqld_safe &
# mysql -S /var/lib/mysql/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.12 Source distribution

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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>

关闭数据库

# mysqladmin  -S /var/lib/mysql/mysql.sock shutdown

• 添加$PATH环境变量

# vi /etc/profile

......    ......
export PATH=$PATH:/usr/local/mysql/bin

2.2.通用二进制安装(5.7)

2.2.1.软件参考文档

参考文档：https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
软件下载：https://downloads.mysql.com/archives/get/file/mysql-5.7.17-linux-glibc2.5-x86_64.tar

图片来自网络

2.2.2.系统环境介绍

• 系统版本
  CentOS release 6.5 (Final) x86_64

• MySQL版本
  mysql-5.7.17-linux-glibc2.5-x86_64(Linux - Generic)

2.2.3.安装系统要求

MySQL依赖异步IO

# rpm -qa | grep libaio

2.2.4.安装MySQL

2.2.4.1.创建用户目录

# groupadd mysql
# useradd -r -g mysql -s /bin/false mysql

2.2.4.2.创建软连接

# cd /opt
# tar -zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
# cd /usr/local/
# ln -s /opt/mysql-5.7.17-linux-glibc2.5-x86_64 mysql

2.2.4.3.创建目录授权

# cd mysql
# mkdir mysql-files
# chmod 750 mysql-files
# chown -R mysql .
# chgrp -R mysql .

2.2.4.4.初始化数据库

准备一个my.cnf配置文件

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
user=mysql
# port=13306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

包含MySQL 5.7.5之前使用该命令初始化数据库：bin/mysql_install_db --user=mysql
5.7.6以后初始化被集成到mysqld命令中bin/mysqld --initialize --user=mysql

# bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
2017-08-03T08:56:23.581602Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-08-03T08:56:23.885630Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-08-03T08:56:23.940039Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-08-03T08:56:23.998870Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a0827f27-7829-11e7-ba31-549f350fdab8.
2017-08-03T08:56:23.999501Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-08-03T08:56:24.000670Z 1 [Note] A temporary password is generated for root@localhost: hk)V!i*t(2AN
# bin/mysql_ssl_rsa_setup 
Generating a 2048 bit RSA private key
................................................................+++
.......................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
...............................+++
..+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
..................................................................+++
.........+++
writing new private key to 'client-key.pem'
-----

2.1.4.5.启动MySQL

# bin/mysqld_safe &

• 可选(将MySQL服务脚本放到/etc/init.d下.)

# cp -p support-files/mysql.server  /etc/init.d/mysql

3.多实例部署

随着硬件层面的发展，linux系统多核已经是普通趋势，而mysql是单进程多线程，所以先天上对多进程的利用不是很高，虽然5.6版本已经在这方面改进很多，但是也没有达到100%，所以为了充分的利用系统资源，mysql有自己的补充，那就是可以部署多实例，一个实例一个端口。

• MySQL多实例也有两种方式：

1. 基于mysqld_safe程序分散管理
2. 基于mysqld_multi程序统一管理

3.1.mysqld_multi多实例方式

多实例安装方式和单机稍微有些调整，为避免环境造成混乱，我们从头开始部署。并且我们要实现用mysqld_multi程序同时管理5.6、5.7数据库。

3.1.1.软件参考文档

5.6参考文档：https://dev.mysql.com/doc/refman/5.6/en/binary-installation.html
5.7参考文档：https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

软件下载：
https://downloads.mysql.com/archives/get/file/mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz
https://downloads.mysql.com/archives/get/file/mysql-5.7.9-linux-glibc2.5-x86_64.tar

3.1.2.系统环境介绍

• 系统版本
  CentOS release 6.5 (Final) x86_64

• MySQL版本
  mysql-5.6.24.-x86_64(二进制通用版)
  mysql-5.7.9.-x86_64(二进制通用版)

3.1.3.创建用户目录

# groupadd mysql
# useradd -r -g mysql -s /bin/false mysql

3.1.4.创建软连接

• 5.6

# tar -zxvf mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz
# ln -s /opt/mysql-5.6.24-linux-glibc2.5-x86_64 /usr/local/mysql5.6

• 5.7

# tar -zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
# ln -s /opt/mysql-5.7.9-linux-glibc2.5-x86_64 /usr/local/mysql

3.1.5.创建目录授权

• 5.6

# chown -R mysql:mysql /usr/local/mysql5.6
# chown -R mysql:mysql /usr/local/mysql5.6/

• 5.7

# cd /usr/local/mysql
# mkdir mysql-files
# chmod 750 mysql-files
# chown -R mysql .
# chgrp -R mysql .

3.1.6.初始化数据库

3.1.6.1.准备/etc/my.cnf

• mysql5.6标签用于抒写5.6和5.7不兼容的参数

[mysqld]
# GENERAL #
user                           = mysql
port                           =3306
default_storage_engine         = InnoDB
socket                         = /data1/db3306/my3306.sock
pid_file                       = /data1/db3306/mysql.pid
#read-only =0
tmpdir                  = /data1/tmp
#key_buffer_size                = 128M
max_allowed_packet             = 32M
max_connect_errors             = 1000000
datadir          = /data1/db3306/
log_bin = 133063306-bin
relay-log=  133063306-relay-bin
expire_logs_days               = 7
#sync_binlog                    = 0
tmp_table_size                 = 32M
max_heap_table_size            = 32M
max_connections                = 5000
thread_cache_size              = 512
table_definition_cache         = 4096
table_open_cache               = 4096
wait_timeout            = 28800
interactive_timeout     = 28800
transaction-isolation = READ-COMMITTED
binlog-format=row
character-set-server=utf8
skip-name-resolve
back_log=1024
explicit_defaults_for_timestamp=true
server_id=133063306

# INNODB #
innodb_flush_method            = O_DIRECT
#innodb_data_home_dir = /data1/db3306
innodb_data_file_path = ibdata1:100M:autoextend
#redo log
#innodb_log_group_home_dir=./
innodb_log_files_in_group      = 3
innodb_log_file_size           = 128M
#innodb performance
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_instances   = 8
innodb_io_capacity             = 2000
innodb_lock_wait_timeout       = 30
binlog_error_action = ABORT_SERVER
innodb_buffer_pool_size        = 128M
innodb_max_dirty_pages_pct=90
innodb_file_format=Barracuda
innodb_support_xa=0
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

log_error                      = error.log
#log_queries_not_using_indexes = 1
slow_query_log                 = 1
slow_query_log_file            = slow-queries.log
long_query_time=2
gtid_mode=ON
enforce-gtid-consistency
log-slave-updates
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync_master_info = 10000
slave_sql_verify_checksum=1
skip-slave-start
init-connect='SET NAMES utf8'
character-set-server=utf8
skip-character-set-client-handshake
bind-address=0.0.0.0
skip-external-locking
slave-parallel-workers=6

[mysql5.6]
myisam_recover                 = FORCE,BACKUP

3.1.6.2.创建数据目录

# mkdir /data1/db3306
# mkdir /data1/tmp
# chown -R mysql:mysql /data1/*

3.1.6.3.初始化5.7数据库

# bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
# bin/mysql_ssl_rsa_setup 
Generating a 2048 bit RSA private key
.......+++
....+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
........+++
...................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
..+++
............+++
writing new private key to 'client-key.pem'
-----

3.1.6.5.配置环境变量

# vim /etc/profile

export PATH=/usr/local/mysql/bin:$PATH

3.1.6.6.启动数据库

# cp support-files/mysql.server  /etc/init.d/mysql
# service mysql start
Starting MySQL...                                          [  OK  ]

3.1.7.配置5.7版本多实例

3.1.7.1.添加/etc/my.cnf参数

• mysqld_multi标签用于mysqld_multi程序启动时初始化的参数
  默认不加mysqld参数,那么mysqld_mulit程序不会使用mysqld_safe启动数据库
• mysql3307属于mysqld_mulit程序子标签,一般用于配置多实例在配置文件中不同的参数
  如：端口、数据文件目录等

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
log = /var/log/mysqld_multi.log

[mysqld3307]
port = 3307
datadir = /data1/db3307/
socket = /data1/db3307/my3307.sock
pid_file = /data1/db3307/mysql.pid

3.1.7.2.创建目录

# mkdir /data1/db3307
# chown -R mysql:mysql /data1/db3307/

3.1.7.3.初始化数据库

# bin/mysqld --initialize --user=mysql --datadir=/data1/db3307

3.1.7.4.启动多实例

# mysqld_multi  report
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
# mysqld_multi start 3307
# mysqld_multi  report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running

3.1.8.配置5.6版本多实例

3.1.8.1.添加/etc/my.cnf参数

注意：现在是不同版本，所以要加上参数basedir，让5.7mysqld_multi程序去以5.6的软件去加载5.6的数据库

[mysqld3308]
port = 3308
basedir = /usr/local/mysql5.6/
datadir = /data1/db3308/
socket = /data1/db3308/my3308.sock
pid_file = /data1/db3308/mysql.pid

3.1.8.2.创建目录

# mkdir /data1/db3308
# chown -R mysql:mysql /data1/db3308

3.1.8.3.初始化数据库

# cd /usr/local/mysql5.6/
# ./scripts/mysql_install_db --user=mysql --datadir=/data1/db3308

3.1.8.4.启动多实例

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is not running
# mysqld_multi start 3308
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running

# ps -ef | grep mysql | grep 3308
root       9023      1  0 23:57 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3308 --basedir=/usr/local/mysql5.6/ --datadir=/data1/db3308/ --socket=/data1/db3308/my3308.sock --pid_file=/data1/db3308/mysql.pid
mysql      9924   9023  5 23:57 pts/1    00:00:04 /usr/local/mysql5.6/bin/mysqld --basedir=/usr/local/mysql5.6/ --datadir=/data1/db3308/ --plugin-dir=/usr/local/mysql5.6//lib/plugin --user=mysql --log-error=/data1/db3308//error.log --pid-file=/data1/db3308/mysql.pid --socket=/data1/db3308/my3308.sock --port=3308

3.1.9.mysqld_multi常用命令

3.1.9.1.查看多实例状态

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running

3.1.9.2.关闭mysql实例

• mysqld_multi程序也是调用mysqladmin去关闭数据库，由于5.7有密码，所以我们要将mysqld_multi所管理的所有数据库改成统一密码

• 修改多实例密码
  5.7改密码

# cat /data1/db3307/error.log | grep temp
2017-08-07T15:40:48.714494Z 1 [Note] A temporary password is generated for root@localhost: 82u_G(K?M933

# mysql -S /data1/db3307/my3307.sock -p"82u_G(K?M933"
mysql> set password='123';

5.6改密码

# mysql -S /data1/db3308/my3308.sock -e "set password=password('123')"

• 将用户名/密码信息配置到mysqld_multi标签中

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
log = /var/log/mysqld_multi.log
user=root
pass=123

• 验证

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
# mysqld_multi stop
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running

补充：
由于是mysqld_multi程序管理不同版本的多实例，最好还是将每个多实例的basedir指定一下。

附录my.cnf

[mysqld]
# GENERAL #
user                           = mysql
port                           =3306
default_storage_engine         = InnoDB
socket                         = /data1/db3306/my3306.sock
pid_file                       = /data1/db3306/mysql.pid
#read-only =0
tmpdir                  = /data1/tmp
#key_buffer_size                = 128M
max_allowed_packet             = 32M
max_connect_errors             = 1000000
datadir          = /data1/db3306/
log_bin = 133063306-bin
relay-log=  133063306-relay-bin
expire_logs_days               = 7
#sync_binlog                    = 0
tmp_table_size                 = 32M
max_heap_table_size            = 32M
max_connections                = 5000
thread_cache_size              = 512
table_definition_cache         = 4096
table_open_cache               = 4096
wait_timeout            = 28800
interactive_timeout     = 28800
transaction-isolation = READ-COMMITTED
binlog-format=row
character-set-server=utf8
skip-name-resolve
back_log=1024
explicit_defaults_for_timestamp=true
server_id=133063306

# INNODB #
innodb_flush_method            = O_DIRECT
#innodb_data_home_dir = /data1/db3306
innodb_data_file_path = ibdata1:100M:autoextend
#redo log
#innodb_log_group_home_dir=./
innodb_log_files_in_group      = 3
innodb_log_file_size           = 128M
#innodb performance
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_instances   = 8
innodb_io_capacity             = 2000
innodb_lock_wait_timeout       = 30
binlog_error_action = ABORT_SERVER
innodb_buffer_pool_size        = 128M
innodb_max_dirty_pages_pct=90
innodb_file_format=Barracuda
innodb_support_xa=0
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

log_error                      = error.log
#log_queries_not_using_indexes = 1
slow_query_log                 = 1
slow_query_log_file            = slow-queries.log
long_query_time=2
gtid_mode=ON
enforce-gtid-consistency
log-slave-updates
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync_master_info = 10000
slave_sql_verify_checksum=1
skip-slave-start
init-connect='SET NAMES utf8'
character-set-server=utf8
skip-character-set-client-handshake
bind-address=0.0.0.0
skip-external-locking
slave-parallel-workers=6


[mysql5.6]
myisam_recover                 = FORCE,BACKUP

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
log = /var/log/mysqld_multi.log
user=root
pass=123

[mysqld3307]
port = 3307
basedir=/usr/local/mysql/
datadir = /data1/db3307/
socket = /data1/db3307/my3307.sock
pid_file = /data1/db3307/mysql.pid

[mysqld3308]
port = 3308
basedir = /usr/local/mysql5.6/
datadir = /data1/db3308/
socket = /data1/db3308/my3308.sock
pid_file = /data1/db3308/mysql.pid

4.Docker部署

依赖系统基础库

bridge-utils
libcgroup
rsync

阿里的yum下载docker

rpm -Uvh http://dl.Fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum -y install docker-io

找到dockermysql镜像

docker run hello-world
docker search mysql
docker search mysql:5.6
docker pull mysql:5.6

安装

docker run --name db001 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6
docker ps