mysql 5.6 二进制包部署

目前官网仅存在 5.7 和 8.0 两个版本的在线文档，5.6 的PDF文档。
https://dev.mysql.com/doc/refman/5.7/en/
https://dev.mysql.com/doc/refman/8.0/en/
https://downloads.mysql.com/docs/refman-5.6-en.pdf

一、软件包准备

历史版本下载地址 https://downloads.mysql.com/archives/community/

wget https://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz
tar xzvf mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz

Directory	Contents of Directory
bin, scripts	mysqld server, client and utility programs
data	Log files, databases
docs	MySQL manual in Info format
include	Include (header) files
lib	Libraries
mysql-test	Test suite
man	Unix manual pages
share	Error messages, dictionary, and SQL for database installation
sql-bench	Benchmarks
support-files	Miscellaneous support files, including sample configuration files

安装依赖

yum install libaio

二、简单运行

1. 创建运行用户

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

2. 可选解压路径

cd /usr/local
tar zxvf /path/to/mysql-VERSION-OS.tar.gz
ln -s full-path-to-mysql-VERSION-OS mysql

3. 创建表

# 执行脚本需要安装依赖
yum install perl-Data-Dumper.x86_64
# This scripts creates the MySQL Server system tables
cd mysql
scripts/mysql_install_db --user=mysql

mysql_install_db 在安装目录创建默认配置文件 my.cnf ，该文件源自安装包的 my-default.cnf 模板。
mysql_install_db 安装数据文件到 /var/lib/mysql/。

如果使用自定义配置文件，在创建表的时候，请增加 --defaults-file 配置

4. 启动 mysql

bin/mysqld_safe --user=mysql &

--defaults-file argument to mysqld_safe when starting the server

You can test the MySQL daemon with mysql-test-run.pl，执行

   cd mysql-test ; perl mysql-test-run.pl

5. 配置文件（可选）

# Next command is optional
cp support-files/mysql.server /etc/init.d/mysql.server

三、设置 root 用户密码

设置MySQL root用户的密码

  ./bin/mysqladmin -u root password 'new-password'
  ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

生产环境建议

  ./bin/mysql_secure_installation

四、启动 - 高级配置

1. 参考PDF文档 4.3.2 mysqld_safe — MySQL Server Startup Script

# cd mysql_installation_directory
bin/mysqld_safe &
bin/mysqld_safe --user=mysql &
bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf

默认配置下，需要手动创建 --log-error 和 --pid-file 文件，并修改用户权限为 mysql。
非默认配置，自行配置。

执行 bin/mysqld_safe 启动 mysql 服务 , 进程显示如下

/root/mysql-5.6.51-linux-glibc2.12-x86_64/bin/mysqld \
--basedir=/root/mysql-5.6.51-linux-glibc2.12-x86_64 \
--datadir=/var/lib/mysql \
--plugin-dir=/root/mysql-5.6.51-linux-glibc2.12-x86_64/lib/plugin \
--user=mysql \
--log-error=/var/log/mariadb/mariadb.log \
--pid-file=/var/run/mariadb/mariadb.pid \
--socket=/var/lib/mysql/mysql.sock

如上默认配置都可以在 my.cnf 内进行修改

2. 参考PDF文档 4.3.3 mysql.server — MySQL Server Startup Script
   将support-files/mysql.server（MySQL daemon start/stop script）拷贝到对应位置

mysql.server start
mysql.server stop

cp mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
chkconfig --level 345 mysql on

五、配置文件说明

参考 http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
参考 4.2.2.2 Using Option Files
参考 MariaDB option-file-syntax
https://cloud.tencent.com/developer/article/1533528

范例

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
local-infile=1
query_cache_limit=4M
query_cache_size=256M
query_cache_type=1
max_connections=400
interactive_timeout=30
wait_timeout=30
connect_timeout=10
thread_cache_size=128
key_buffer=200M
join_buffer=1M
max_allowed_packet=16M
table_cache=26536
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=10
myisam_sort_buffer_size=64M
tmp_table_size=256M
max_heap_table_size=256M
innodb_buffer_pool_size=2000M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql.server]
user=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Option file Sections:
配置文件常见 Section - mysqld, mysql

Section	Description	Other
[mysqld]	服务器参数 [server options]
[mysqld_safe]	define server settings like MySQL user, pid file and error log	MySQL和MariaDB都接受
[mysql.server]	define server settings like MySQL user, pid file and error log	逐渐过时
[mysqldump]	备份参数 [mysqldump options]
[mysql]	命令行参数 [mysql and client options]
[client]	客户端参数，包含 mysql cli [mysql and client options]
[mysqladmin]	mysqladmin
[mysqlhotcopy]	mysqlhotcopy

mysqld
mysqld是关于服务器端的程序，要想使用客户端程序,该程序必须运行,因为客户端通过连接服务器来访问数据库。
mysql_safe
mysql服务启动脚本，它是mysqld的父进程，它调用mysqld启动数据库服务，并在启动MySQL服务器后继续监控其运行情况，并在其死机时重新启动它。
使用mysqld_safe命令可以防止mysql服务的意外终止。
mysql.server
服务器启动的shell脚本，主要作用就是为了方便启动和关闭mysql服务，它使用mysql_safe来启动mysql服务器。

常用配置文件

[mysqld]
datadir = /mysql_data/data
user = mysql 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error = /mysql_data/mysql.log
pid-file = /mysql_data/mysql.pid

自定义配置，需要执行 scripts/mysql_install_db --defaults-file=my.cnf 安装表文件到指定data路径。

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES