001：介绍和安装

目录

• 一. MySQL版本选择
• 二. MySQL官方网站介绍
  • 1. Developer Zone： MySQL开发工程师板块
  • 2. Downloads：MySQL下载
  • 3. Documentation：MySQL文档
• 三. MySQL下载
• 四. MySQL安装
  • 1. 安装通用步骤：
  • 2. MySQL 5.6.X 安装：
  • 3. MySQL 5.7.X 安装
  • 4. 验证安装
  • 5. MySQL启动
• 五. 附录
  • 1. 配置文件my.cnf
  • 2. 几个重要的参数配置和说明
  • 3. my.cnf问题
  • 4. 安装脚本 run_02_install_new_mysql_version.sh

一. MySQL版本选择

1. MySQL5.6 以后的版本，推荐使用官方版本。
2. Percona：在5.6版本以后，MySQL将Percon之前优化集成到官方版本中;
3. MariaDB：无INNODB;且核心代码较老
4. MySQL在5.6以后不断重构源码，安装包越来越大，功能和性能在持续改进

---

二. MySQL官方网站介绍

官方网站：http://www.mysql.com

1. Developer Zone： MySQL开发工程师板块

• Articles： Oracle工程师自己的博客
• Plant MySQL： 和MySQL相关从业人员的博客
• Bugs：MySQL BugList
• Worklog：开发记录
• Labs：MySQL实验性项目

2. Downloads：MySQL下载

• Enterprise：MySQL企业版本相关，略过
• Community：社区版，我们下载和使用社区版
  • MySQL Community Server：MySQL Server
  • MySQL Fabric : 和管理相关的工具
  • MySQL Router：路由中间件
  • MySQL Utilities：MySQL应用程序包
  • MySQL Workbench：官方图型化管理界面
  • MySQL Proxy：MySQL代理。Alpha版本，不推荐

3. Documentation：MySQL文档

• 官方文档 版面更改，下载离线文档在左侧Menu的下面
  • PDF A4
  • EPUB
  • HTML

---

三. MySQL下载

1. 推荐下载Linux-Generic版本

2. Source Code版本主要作用是为了让开发人员研究源码使用，自己编译对性能提升不明显

3. 不推荐Version 5.5.X，有部分bug

4. 推荐使用Version 5.6.X和Version 5.7.X

5. 下载地址：

   • MySQL Community Server 5.7.18 Linux Generic x86-64bit

   • MySQL Community Server 5.6.27 Linux Generic x86-64bit

---

四. MySQL安装

1. 安装通用步骤：

• 解压缩mysql-VERSION-linux-glibc2.5-x86_64.tar.gz
• 打开INSTALL_BINARY 文件，按照shell>开头的步骤进行操作
• 将export PATH=/安装路径/mysql/bin:$PATH添加到/etc/profile
• chkconfig mysqld on或者chkconfig mysqld.server on因你的环境而定。

2. MySQL 5.6.X 安装：

shell> yum install libaio # Debain系用户:apt-get install libaio1
shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server 

3. MySQL 5.7.X 安装

shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chmod 770 mysql-files
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> bin/mysqld --initialize --user=mysql #该步骤中会产生临时root@localhost密码
shell> bin/mysql_ssl_rsa_setup          
shell> chown -R root .
shell> chown -R mysql data mysql-files
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

4. 验证安装

• data目录在安装之前是空目录，安装完成后应该有ibXXX等文件
• 安装过程中输出的信息中，不应该含有ERROR信息，错误信息默认会写入到$HOSTNAME.err的文件中
• 通过bin/mysql命令（5.7.X含有临时密码）可以正常登录

5. MySQL启动

• mysqld_safe --user=mysql & 即可启动，mysqld_safe是一个守护mysqld进程的脚本程序，旨在mysqld意外停止时，可以重启mysqld进程
• 也可以通过INSTALL_BINARRY中的的步骤，使用/etc/init.d/mysql.server start进行启动（启动脚本以你复制的实际名字为准，通常改名为mysqld,即/etc/init.d/mysqld start）

---

五. 附录

1. 配置文件my.cnf

# line          :V1.9
# mail          :gczheng@139.com
# data          :2018-07-19
# file_name     :my.cnf
# update        :

#### 注意     :建议参数根据实际情况作调整
#### 本配置文件主要适用于MySQL 5.7.18版本

[client]
port    = 3306
socket  = /data/mysqldata/mysql.sock
#=======================================================================
# # MySQL客户端配置
#=======================================================================
[mysql]
prompt="(u@h) \R:\m:\s [d]> "
no-auto-rehash
default-character-set = utf8mb4
#=======================================================================
# MySQL服务器全局配置
#=======================================================================
[mysqld]
user = mysql
port = 3306
server-id = 49180
tmpdir = /data/mysqldata
datadir = /data/mysqldata
socket  = /data/mysqldata/mysql.sock
wait_timeout = 31536000
#interactive_timeout = 600
#sql_mode =                             #sql_mode 配置为空值
#skip_name_resolve = 1
lower_case_table_names = 1
character-set-server = utf8mb4
log_timestamps = SYSTEM
init_connect='SET NAMES utf8mb4'
max_allowed_packet = 128M
######################### 性能参数 ####################
open_files_limit = 10240
max_connections = 1000
max_user_connections=999
max_connect_errors = 100000
table_open_cache = 1024
thread_cache_size = 64
max_heap_table_size = 32M
query_cache_type = 0
###global cache ###
key_buffer_size = 1G
query_cache_size = 0
tmp_table_size = 32M        #内存临时表
binlog_cache_size = 4M      #二进制日志缓冲
###session cache ###
sort_buffer_size = 8M       #排序缓冲
join_buffer_size = 4M       #表连接缓冲
read_buffer_size = 8M       #顺序读缓冲
read_rnd_buffer_size = 8M   #随机读缓冲
thread_stack = 256KB        #线程的堆栈的大小
######################### binlog设置 #####################
binlog_format = ROW
log_bin = /data/mysqldata/binlog
max_binlog_size = 1G
expire_logs_days = 15       #binlog比较占空间，注意磁盘空间
sync_binlog = 1             #重要参数必须修改为1
######################### 复制设置 ########################
log_slave_updates = 1
#replicate-do-db = test
#binlog-ignore-db = mysql
### GTID 配置 ###
gtid_mode=ON
enforce-gtid-consistency=true
#****************** 开启并行复制（从库）******************
slave-parallel-type=LOGICAL_CLOCK     #基于组提交的并行复制方式
slave-parallel-workers= 8             #并行的SQL线程数量（cpu核数）
master-info_repository=TABLE          #master信息以表的形式保存
relay_log_info_repository=TABLE       #slave信息以表的形式保存
relay_log_recovery=ON                 #relay_log自我修复
######################### innodb ##########################
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_size = 1G          #系统内存50%
innodb_open_files = 5120              #调整innodb_open_files设置值，必须小于open_files_limit的设置值
innodb_flush_log_at_trx_commit = 1    #线上服务器必须配置为1
innodb_file_per_table = 1
innodb_lock_wait_timeout = 5
innodb_io_capacity = 400              #根据您的服务器IOPS能力适当调整innodb_io_capacity，配SSD盘可调整到 10000 - 20000
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_large_prefix = 0
innodb_thread_concurrency = 64
innodb_strict_mode = OFF
innodb_sort_buffer_size = 4194304
#****************** undolog设置 ******************
innodb_undo_directory = /data/undolog            #undolog空间的目录位置
innodb_undo_tablespaces = 2                      #undolog日志文件个数，mysql8之后将弃用
innodb_undo_logs = 128                           #回滚段的数量， 至少大于等于35，默认128。
innodb_max_undo_log_size = 1G                    #当超过这个阀值（默认是1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M。
innodb_purge_rseg_truncate_frequency = 128       #控制回收（收缩）undolog的频率
innodb_undo_log_truncate = 1                     #即开启在线回收undolog日志文件
######################### log 设置 #####################
log_error = /data/mysqldata/error.log
slow_query_log = 1
long_query_time = 10
slow_query_log_file = /data/mysqldata/slow.log
#=======================================================================
# MySQL mysqldump配置
#=======================================================================
[mysqldump]
quick
max_allowed_packet = 128M
#=======================================================================
# MySQL mysqld_safe配置
#=======================================================================
[mysqld_safe]
log_error = /data/mysqldata/error.log
pid_file = /data/mysqldata/mysqldb.pid

[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

2. 几个重要的参数配置和说明

• innodb_log_file_size = 4G :做实验可以更改的小点，线上环境推荐用4G，以前5.5和5.1等版本之所以官方给的值很小，是因为太大后有bug，现在bug已经修复
• innodb_undo_logs = 128和innodb_undo_tablespaces = 3建议在安装之前就确定好该值，后续修改比较麻烦
• [mysqld]，[mysqld-5.7]这种tag表明了下面的配置在什么版本下才生效,[mysqld]下均生效
• datadir, innodb_log_group_home_dir, innodb_undo_directory一定要注意他的权限是 mysql:mysql

3. my.cnf问题

• 使用mysqld --help -vv | grep my.cnf 查看mysql的配置文件读取顺序
• 后读取的my.cnf中的配置，如果有相同项，会覆盖之前的配置
• 使用--defaults-files可指定配置文件

4. 安装脚本 run_02_install_new_mysql_version.sh

• 注意：脚本、my.cnf文件和mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz放在同一目录下

#!/bin/bash
# line:           V1.0
# mail:           gczheng@139.com
# data:           2018-06-21
# script_name:    run_02_install_new_mysql_version.sh
# function:       Install mysql5.7.18


#=======================================================================
#配置信息
#=======================================================================
MYCNF=my.cnf
MYSQL_SOURCE_PACKAGES=mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
MYSQL_DOWNLOAD_LINK='http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz'
MYSQL=/usr/local/mysql/bin/mysql

#=======================================================================
# echo添加颜色
#=======================================================================
echo_color(){
    color=${1} && shift
    case ${color} in
        black)
            echo -e "e[0;30m${@}e[0m"
            ;;
        red)
            echo -e "e[0;31m${@}e[0m"
            ;;
        green)
            echo -e "e[0;32m${@}e[0m"
            ;;
        yellow)
            echo -e "e[0;33m${@}e[0m"
            ;;
        blue)
            echo -e "e[0;34m${@}e[0m"
            ;;
        purple)
            echo -e "e[0;35m${@}e[0m"
            ;;
        cyan)
            echo -e "e[0;36m${@}e[0m"
            ;;
        *)
            echo -e "e[0;37m${@}e[0m"
            ;;
    esac    # --- end of case ---
}

#=======================================================================
#检查安装包、脚本、my.cnf是否齐全
#=======================================================================

function chk_install_resource()
{
    #判断 template_install-my.cnf 是否存在
    if [ ! -f "$MYCNF" ];then
        echo_color red "$(date +'%Y-%m-%d %H:%M:%S') $MYCNF file is not exits!$(echo_warning)"
        exit 1
    fi
    #判断 MySQL Community Server 5.7.18 tar包是否存在
    if [ ! -f "$MYSQL_SOURCE_PACKAGES" ];then
        echo_color red "$(date +'%Y-%m-%d %H:%M:%S') $MYSQL_SOURCE_PACKAGES is not exits, please download it from $MYSQL_DOWNLOAD_LINK"
        exit 1
    fi
}
#=======================================================================
# 添加帐号和目录
#=======================================================================
function create_sys_user()
{
    #添加mysql用户信息
    if id mysql &> /dev/null;then
        echo_color red "$(date +'%Y-%m-%d %H:%M:%S') MySQL user is exits."
    else
        useradd -r -s /bin/false mysql && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') The system user is added to success .."
    fi
    #添加mysql数据目录/r2/mysqldata和权限
    if [ -d "/r2" ];then
        if [ ! -d "/r2/mysqldata" ];then
            mkdir -p /r2/mysqldata && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL data directory is created .."
            chown -R mysql:mysql /r2/mysqldata
            chmod 750 /r2/mysqldata
        elif [ "$(ls -A /r2/mysqldata)" = "" ];then
            echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL data directory is exits."
        else
            echo_color red "$(date +'%Y-%m-%d %H:%M:%S') MySQL data directory is not empty. Please check it."
            exit 1
        fi
    else
        echo_color red "$(date +'%Y-%m-%d %H:%M:%S') /r2 directory is not exits. Please check the system config."
        exit 1
    fi
}
#=======================================================================
#检查是否有旧的mysql/mariadb版本存在
#=======================================================================

function chk_old_mysql_version()
{
    mysqlNum=$(rpm -qa | grep -Ei 'mysql|mariadb'|wc -l)
    if [ "${mysqlNum}" -gt "0" ];then
        echo_color red "$(date +'%Y-%m-%d %H:%M:%S') The system has MySQL other version. There may be a conflict in the version！If it continues, the original database will be uninstall."
        read -p "Do you continue to install it(y/n):" cn
        case $cn in
            y|Y)
                rpm -qa | grep -Ei 'mysql|mariadb' | xargs yum remove -y &> /dev/null
                tar_install
                ;;
            n|N)
                exit 1
                ;;
            *)
                echo_color red "$(date +'%Y-%m-%d %H:%M:%S') Input ERROR."
        esac
    else
        echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') No old version was found."
        tar_install
    fi
}

#=======================================================================
# 解压安装
#=======================================================================

function tar_install()
{
    echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Starting unzip $MYSQL_SOURCE_PACKAGES .."
    tar zxvf $MYSQL_SOURCE_PACKAGES -C /usr/local/ 
    echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Unzip $MYSQL_SOURCE_PACKAGES SUCCESS .."
    if [ ! -d "/usr/local/mysql" ];then
        ln -s /usr/local/`echo $MYSQL_SOURCE_PACKAGES |awk -F ".tar.gz" '{printf $1}'`  /usr/local/mysql
        chown -R mysql:mysql /usr/local/mysql
        chown -R mysql:mysql /usr/local/`echo $MYSQL_SOURCE_PACKAGES |awk -F ".tar.gz" '{printf $1}'`
        chmod 750 /usr/local/mysql
        chmod 750 /usr/local/`echo $MYSQL_SOURCE_PACKAGES |awk -F ".tar.gz" '{printf $1}'`
        echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL package has been placed in the right position .."
        cp -f $MYCNF /etc/my.cnf
    else
        read -p "/usr/local/mysql install directory already exists, delete it, and continue(y/n):" dn
        case $dn in
            y|Y)
                rm -rf /usr/local/mysql
                ln -s /usr/local/`echo $MYSQL_SOURCE_PACKAGES |awk -F ".tar.gz" '{printf $1}'`  /usr/local/mysql
                chown -R mysql:mysql /usr/local/mysql
                chown -R mysql:mysql /usr/local/`echo $MYSQL_SOURCE_PACKAGES |awk -F ".tar.gz" '{printf $1}'`
                chmod 750 /usr/local/mysql
                chmod 750 /usr/local/`echo $MYSQL_SOURCE_PACKAGES |awk -F ".tar.gz" '{printf $1}'`
                echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL package has been placed in the right position .."
                cp -f $MYCNF /etc/my.cnf
                ;;
            n|N)
                exit 1
                ;;
            *)
            echo_color red "$(date +'%Y-%m-%d %H:%M:%S') /usr/local/mysql is exits.Please check it."
        esac
    fi

    echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Starting initialization .."
    /usr/local/mysql/bin/mysqld --initialize --user=mysql  &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Initialization ......SUCCESS"
}

#=======================================================================
# 修改环境变量
#=======================================================================
function add_system_profile()
{
cat >> /etc/profile <<EOF
export PATH=$PATH:/usr/local/mysql/bin/
EOF
source /etc/profile
}

function modify_system_env()
{
#egrep "/usr/local/mysql/bin/" /etc/profile  &> /dev/null
PROFILES=`grep -i  "/usr/local/mysql/bin/" /etc/profile |wc -l`
if [ $PROFILES -eq 0 ];then
    add_system_profile
    echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Flush profile done .."
else
    EXISTS=`grep -i  "/usr/local/mysql/bin/" /etc/profile |grep -e "^#" |wc -l`
    if [ $EXISTS -gt 0 ];then
        add_system_profile
        echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Flush profile done .."
    fi
fi
}

#=======================================================================
#创建MySQL服务
#=======================================================================

function el7_create_mysql_service()
{
    cat > /usr/lib/systemd/system/mysql.service <<EOF
[Unit]
Description=mysql
After=syslog.target network.target remote-fs.target nss-lookup.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecReload=/usr/local/mysql/support-files/mysql.server restart
ExecStop=/usr/local/mysql/support-files/mysql.server stop
LimitNOFILE = 65535
PrivateTmp=false

[Install]
WantedBy=multi-user.target
EOF

    systemctl daemon-reload && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Reload systemd services .."
    systemctl enable mysql.service && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Enable MySQL systemd service .."
    systemctl start mysql.service && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Starting MySQL......SUCCESS!" || echo_color red "$(date +'%Y-%m-%d %H:%M:%S') Starting MySQL......FAILED!."
}

function el6_create_mysql_service()
{
    cd /usr/local/mysql/support-files/
    cp mysql.server /etc/init.d/mysql  
    chmod +x /etc/init.d/mysql
    chkconfig --add mysql   && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Add MySQL service for management .."
    chkconfig --list mysql  && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') List MySQL service .."
    /etc/init.d/mysql start && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Starting MySQL......SUCCESS!" || echo_color red "$(date +'%Y-%m-%d %H:%M:%S') Starting MySQL......FAILED!."
}

#=======================================================================
# 添加帐号
#=======================================================================

function modify_mysql_account()
{
    password=$(awk '/A temporary password/ {print $NF}' /r2/mysqldata/error.log)
    #echo_color cyan "mysql temp password is ${password}"
    if [ "${password}" != "" ];then
        ${MYSQL} -uroot -p"${password}" --connect-expired-password -e "alter user root@localhost identified by 'iforgot';flush privileges;" &> /dev/null && echo_color cyan  "$(date +'%Y-%m-%d %H:%M:%S') 系统随机密码修改成功."
        p1=$?
    else
        echo_color red  "$(date +'%Y-%m-%d %H:%M:%S') MySQL密码获取失败，请排查/清除数据目录重新安装."
        exit 1
    fi
    ${MYSQL} -uroot -piforgot -e "grant all privileges on *.* to gcdb@'%' identified by 'iforgot';" &> /dev/null && echo_color cyan  "$(date +'%Y-%m-%d %H:%M:%S') 授予gcdb@'%'用户通过任意主机操作所有数据库的所有权限成功."
    p2=$?
    if [[ "${p1}" == "0" && "${p2}" == "0" ]];then
        echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 现在可以登录mysql数据库，root@localhost和gcdb@'%'用户的默认密码是33[41;37m iforgot 33[0m."
    else
        echo_color red "$(date +'%Y-%m-%d %H:%M:%S') 授权失败,请手动执行授权操作."
    fi
}

#=======================================================================
# 开始安装mysql
#=======================================================================
function mysql_install()
{
    version=$(uname -r |awk -F '.' '{ print $(NF-1) }')
    if [ "${version}" != "el7" ];then
        echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Start install mysql for el6."
        chk_install_resource
        create_sys_user
        chk_old_mysql_version
        modify_system_env
        el6_create_mysql_service
        modify_mysql_account
    else 
        echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Start install mysql for el7."
        chk_install_resource
        create_sys_user
        chk_old_mysql_version
        modify_system_env
        el7_create_mysql_service
        modify_mysql_account
    fi
}

mysql_install
echo_color blue "$(date +'%Y-%m-%d %H:%M:%S') 33[42;37m run_02_install_new_mysql_version.sh执行完成 33[0m"