第2章：MySQL之部署实施
第2章：MySQL之部署实施
修订日期：2020-10-23
一、MySQL版本选择
MySQL5.6以后的版本，推荐使用官方版本。- Percona：在5.6版本以后，MySQL将Percon之前优化集成到官方版本中。
- MariaDB：10.3版本和MySQL官方InnoDB差异很大。
- MySQL在5.6以后不断重构源码，安装包越来越大，功能和性能在持续改进。
二、MySQL版本下载
-
推荐下载
Linux-Generic版本 -
Source Code版本主要作用是为了让开发人员研究源码使用，自己编译对性能提升不明显 -
不推荐Version：
5.5.X有部分bug 、5.6.X支持周期到2021年2月 -
推荐Version
5.7.18和8.0.16之后版本 -
下载地址：
三、MySQL单机安装步骤
1. 安装通用步骤
- 安装依赖包
yum install libaio - 创建系统账号
- 解压缩mysql-
VERSION-linux-glibc2.xx-x86_64.tar.gz到/usr/local目录下，并建立链接 - 授权mysql用户访问
/usr/local/mysql*文件的权限 - 将
export PATH=/安装路径/mysql/bin:$PATH添加到/etc/profile - 配置
chkconfig mysqld on或者systemctl enable mysql.service - 创建数据目录，修改配置文件
- 初始化mysql
- 启动mysql
- 添加开机启动服务
- 验证mysql
2. MySQL 5.6.X 安装
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false 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> scripts/mysql_install_db --user=mysql
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 -s /bin/false 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> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql #该步骤中会产生临时root@localhost密码
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
4. MySQL 8.0.X 安装：
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar xvf /path/to/mysql-VERSION-OS.tar.xz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
5. 验证安装
mysql-files-data目录在安装之前是空目录，安装完成后应该有ib_XXX、ibdata、binlog.000001等文件- 安装过程中输出的信息中，不应该含有
ERROR信息，错误信息默认会写入到$HOSTNAME.err或者配置文件指定的error的文件中 - 通过
bin/mysql命令（5.7.X含有临时密码）可以正常登录
6. 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 : V2.2
# mail: ：gczheng@139.com
# data : 2019-10-29
# file_name : my.cnf
# update : 调整wait_timeout设置值，长连接无法释放，导致连接数爆满，保持一年调整为一天
#### 注意 : 建议参数根据实际情况作调整
#### 本配置文件主要适用于MySQL 5.7.18以上版本
# ********* 以下重要参数必须修改核对 *********
# 1.innodb_flush_log_at_trx_commit=1
# 2.sync_binlog = 1
# 3.innodb_strict_mode = OFF #关闭InnoDB严格检查模式
# 4.innodb_flush_method = O_DIRECT
# 5.lower_case_table_names = 0 #设置区分大小写
# 6.character-set-server = utf8mb4
# 7.sql_mode #配置为空值
# 8.server-id =1 #修改成对应数值
# 9.innodb_buffer_pool_size = 10G #纯mysql server 配置50%和 混合内存配置不低于10G~40%
#10.key_buffer_size=1G #如果有myisam表请配置为1G，没有请配置64M
#11.innodb_data_file_path = ibdata1:1G:autoextend #确认配置是否跟原来一样,之前已配置好请维持原样,如未配置请注释掉，新版本请取消注释
#12.log_bin = /data/mysqldata/binlog #旧版本或者之前已配置好如：log_bin =/data/mysqldata/slave-bin，请维持原样
#13.slave-parallel #从库开启并行复制，并行复制参数取消注释
#14.undolog #确认配置是否跟原来一样,之前已配置好请维持原样,如未配置请注释掉，新版本（包括升级版本）请取消注释并创建目录并授权
#15.open_files_limit = 65535 #注意/usr/lib/systemd/system/mysqld.service下LimitNOFILE会影响open_files_limit
#16.wait_timeout = 86400 #长连接无法释放，导致连接数爆满，保持一年调整为一天
# ********************************************
[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 = 1
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 = 0
character-set-server = utf8mb4
#skip-character-set-client-handshake #忽略客户端的字符集，使用服务器的设置
#auto_increment_increment = 1
#auto_increment_offset = 1
log_timestamps = SYSTEM
init_connect= 'SET NAMES utf8mb4'
max_allowed_packet = 128M
######################### 性能参数 ####################
open_files_limit = 65535 #open_files_limit->max_connections->table_open_cache->table_definition_cache四个参数时存在严格的顺序和依赖关系
max_connections = 10000
table_open_cache = 1024
table_definition_cache=1024
max_user_connections=9990
max_connect_errors = 100000
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线程数量
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 = 32G #系统物理内存50%
innodb_open_files = 5120
innodb_flush_log_at_trx_commit = 1 #线上服务器必须配置为1
innodb_file_per_table = 1
innodb_lock_wait_timeout = 5
innodb_flush_neighbors = 0 #对于HDD硬盘配置为1，对于SSD配置为0关闭
innodb_io_capacity = 2000 #根据您的服务器IOPS能力适当调整innodb_io_capacity，配SSD盘可调整到 1000 - 10000
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_large_prefix = 0
innodb_thread_concurrency = 64
innodb_strict_mode = OFF
innodb_sort_buffer_size = 4194304
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_page_cleaners = 4
innodb_online_alter_log_max_size=4294967296
#用以指定对InnoDB表做在线DDL操作时所使用的临时日志文件的最大大小（以字节为单位，默认128M）。
#在创建索引或者ALTER表时会使用该临时文件，该日志文件记录了DDL操作期间插入、更新、删除的数据。
#每次扩展innodb_sort_buffer_size的大小直至达到innodb_online_alter_log_max_size指定的最大值。
#若日志的大小超出此上限则ALTER表的操作会失败，当前所有未提交的DML操作会回滚，但如果日志文件太大会可能会导致DDL操作最后锁定表(Waiting for table metadata lock)的时间更长（锁定表，应用日志到表上）。
#****************** 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 = 1
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
2. my.cnf 参数配置和说明
innodb_log_file_size = 4G做实验可以更改的小点，线上环境推荐用4G，以前5.5和5.1等版本之所以官方给的值很小，是因为太大后有bug，现在bug已经修复innodb_undo_logs = 128和innodb_undo_tablespaces = 2建议在安装之前就确定好该值，后续修改比较麻烦[mysqld]、[mysqld-5.7]、[mysqld-8.0]这种group表明了下面的配置在什么版本下才生效,[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. MySQL部署脚本
1、MySQL5.7脚本
- 注意：脚本、my.cnf文件和mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz放在同一目录下
#!/bin/bash
# line: V1.8
# mail: gczheng@139.com
# data: 2018-09-04
# script_name: installation_of_single_mysql.sh
# function: Install mysql5.7
#=======================================================================
#配置信息
#=======================================================================
MYSQL_DATADIR=/data/mysqldata
MYSQL_UNDODIR=/data/mysqlundo
MYCNF=template_install-my.cnf
MYSQL_SOURCE_PACKAGES=/software/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
MYSQL_SOURCE_PACKAGES_MD5=26657121c08451718e0de687f423eb09
MYSQL_SOURCE_PACKAGES_NAMES=`echo $MYSQL_SOURCE_PACKAGES |awk -F '/' '{print $NF}' |awk -F ".tar.gz" '{printf $1}'`
MYSQL_DOWNLOAD_LINK='https://downloads.mysql.com/archives/get/file/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 tar包是否存在
if [ ! -f "$MYSQL_SOURCE_PACKAGES" ];then
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') $MYSQL_SOURCE_PACKAGES is not exits, Make a copy to the directory or please download it from $MYSQL_DOWNLOAD_LINK "
read -p "Download the package from mysql(y/n):" dn
case $dn in
y|Y)
wget -O $MYSQL_SOURCE_PACKAGES $MYSQL_DOWNLOAD_LINK
md5=`md5sum $MYSQL_SOURCE_PACKAGES | awk '{print $1}'`
if [ "$MYSQL_SOURCE_PACKAGES_MD5" != "$md5" ];then
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') Download $MYSQL_SOURCE_PACKAGES md5 error"
exit 1
fi
;;
n|N)
exit 1
;;
*)
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') Input ERROR."
esac
echo_color green "$(date +'%Y-%m-%d %H:%M:%S') $MYSQL_SOURCE_PACKAGES is exits ."
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
if [ ! -d "${MYSQL_DATADIR}" ];then
mkdir -p ${MYSQL_DATADIR} && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL data directory is created .."
chown -R mysql:mysql ${MYSQL_DATADIR}
chmod 750 ${MYSQL_DATADIR}
elif [ "$(ls -A ${MYSQL_DATADIR})" = "" ];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
if [ ! -d "${MYSQL_UNDODIR}" ];then
mkdir -p ${MYSQL_UNDODIR} && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL undo directory is created .."
chown -R mysql:mysql ${MYSQL_UNDODIR}
chmod 750 ${MYSQL_UNDODIR}
elif [ "$(ls -A ${MYSQL_UNDODIR})" = "" ];then
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL undo directory is exits."
else
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') MySQL undo directory is not empty. Please check it."
exit 1
fi
}
#=======================================================================
#检查是否有旧的mysql/mariadb版本存在
#=======================================================================
function chk_old_mysql_version()
{
mysqlNum=$(rpm -qa | grep -Ei '^mysql|^mariadb' |wc -l)
if [ "${mysqlNum}" -gt "0" ];then
rpm -qa | grep -Ei '^mysql|^mariadb'
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') The system has mariadb or 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 -iE "mariadb|mysql" | grep -v MySQL-python | xargs yum remove -y &> /dev/null
source /etc/profile
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."
source /etc/profile
tar_install
fi
}
#=======================================================================
# 解压安装
#=======================================================================
function installPackage()
{
count=0
package=(gcc gcc-c++ bzip2 bzip2-devel bzip2-libs python-devel libaio libaio-devel ncurses ncurses-devel cmake numactl-libs)
nums01=${#package[@]}
for((i=0;i<nums01;i++));
do
char=${package[$i]}
rpm -qa | grep "^$char"
if [ $? != 0 ] ; then
error[$count]=${package[$i]}
count=$(($count+1))
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') The ${package[$i]} is not installed.Please check it.."
fi
done
if [ $count -gt "0" ];then
echo "You have $count patchs are not installed."
echo "the not installed patch is:"
nums02=${#error[@]}
for((ii=0;ii<nums02;ii++));
do
echo "${error[$ii]}^"
done
echo -e "Are you sure to install the patch[yes or no]:\c"
read select
if [ $select == "yes" ]; then
for((is=0;is<nums02;is++));
do
var=${error[$is]}
echo $var
yum install -y $var
done
fi
else
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Check pass!.."
fi
count=0
rpm -q gcc gcc-c++ bzip2 bzip2-devel bzip2-libs python-devel libaio libaio-devel ncurses ncurses-devel cmake numactl-libs | grep "not installed"
}
function tar_install()
{
installPackage
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/${MYSQL_SOURCE_PACKAGES_NAMES} /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /usr/local/${MYSQL_SOURCE_PACKAGES_NAMES}
chmod 750 /usr/local/mysql
chmod 750 /usr/local/${MYSQL_SOURCE_PACKAGES_NAMES}
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/${MYSQL_SOURCE_PACKAGES_NAMES} /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /usr/local/${MYSQL_SOURCE_PACKAGES_NAMES}
chmod 750 /usr/local/mysql
chmod 750 /usr/local/${MYSQL_SOURCE_PACKAGES_NAMES}
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 add_mysql_ldconfig()
{
cat > /etc/ld.so.conf.d/mysql.conf <<EOF
/usr/local/mysql/lib
EOF
ldconfig
}
function add_libmysqlclient()
{
if [ -f /etc/ld.so.conf.d/mysql.conf ];then
LDNUMS=`grep -i "/usr/local/mysql/lib" /etc/ld.so.conf.d/mysql.conf |wc -l`
if [ $LDNUMS -eq 0 ];then
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') The configuration file is empty!"
add_mysql_ldconfig
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Flush ldconfig done .."
else
LDEXISTS=`grep -i "/usr/local/mysql/lib" /etc/ld.so.conf.d/mysql.conf |grep -e "^#" |wc -l`
if [ $LDEXISTS -gt 0 ];then
add_mysql_ldconfig
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Has been configured!"
fi
fi
else
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') /etc/ld.so.conf.d/mysql.conf is not exits!"
add_mysql_ldconfig
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Flush ldconfig done .."
fi
}
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}' ${MYSQL_DATADIR}/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 root@'%' identified by 'iforgot';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予root用户通过任意主机操作所有数据库的所有权限成功."
p2=$?
${MYSQL} -uroot -piforgot -e "grant RELOAD,REPLICATION SLAVE, REPLICATION CLIENT on *.* to repl@'%' identified by 'repl';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予repl用户通过任意主机对所有数据库进行主从复制的权限成功."
p3=$?
${MYSQL} -uroot -piforgot -e "grant SELECT, PROCESS, REPLICATION CLIENT, SHOW DATABASES on *.* to monitor@'%' identified by 'monitor';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予monitor用户通过任意主机对所有数据库的读取权限成功."
p4=$?
${MYSQL} -uroot -piforgot -e "grant SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS,SUPER,CREATE,SHOW DATABASES,SHOW VIEW, EVENT, TRIGGER, create tablespace on *.* to dbbackup@'%' identified by 'dbbackup';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予dbbackup用户通过localhost主机对所有数据库进行备份的权限成功."
p5=$?
${MYSQL} -uroot -piforgot -e "grant insert,update,delete,select,create,drop,index,trigger,alter on *.* to producer@'%' identified by 'iforgot';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予producer用户通过任意主机对所有数据库进行常规操作的权限成功."
p6=$?
${MYSQL} -uroot -piforgot -e "grant insert,update,delete,select,create,drop,index,trigger,alter on *.* to producer@'localhost' identified by 'iforgot';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予producer用户通过localhost主机对所有数据库进行常规操作的权限成功."
p7=$?
${MYSQL} -uroot -piforgot -e "flush privileges" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 权限刷新成功."
p8=$?
if [[ "${p1}" == "0" && "${p2}" == "0" && "${p3}" == "0" && "${p4}" == "0" && "${p5}" == "0" && "${p6}" == "0" && "${p7}" == "0" && "${p8}" == "0" ]];then
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 现在可以登录mysql数据库，root和producer用户的默认密码是\033[41;37m iforgot\033[0m."
${MYSQL} -uroot -piforgot -e "select user,host,authentication_string from mysql.user;"
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
add_libmysqlclient
el6_create_mysql_service
sleep 5
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
add_libmysqlclient
el7_create_mysql_service
sleep 5
modify_mysql_account
fi
}
mysql_install
echo_color blue "$(date +'%Y-%m-%d %H:%M:%S') \033[42;37m installation_of_single_mysql.sh执行完成 \033[0m"
2、MySQL8.0脚本
[root@ali01 scripts]# tree install_single_mysql80/
install_single_mysql80/
├── base_my.cnf
├── installation_of_single_mysql.sh
├── single_install.conf
└── template_install-my.cnf
0 directories, 4 files
[root@ali01 scripts]#
# line :V2.3
# mail :gczheng@139.com
# data :2020-08-10
# file_name :my.cnf
# update :
#### 注意 :建议参数根据实际情况作调整
#### 本配置文件主要适用于MySQL 5.7.18、8.0.21 版本
# ********* 以下重要参数必须修改核对 *********
# 1.innodb_flush_log_at_trx_commit=1
# 2.sync_binlog = 1
# 3.innodb_strict_mode = OFF #关闭InnoDB严格检查模式
# 4.innodb_flush_method = O_DIRECT
# 5.lower_case_table_names = 0 #设置区分大小写,1表示不区分大小写，0表示区分大小写
# 6.character-set-server = utf8mb4
# 7.sql_mode #默认配置
# 8.server-id =1 #修改成对应数值（1~4294967295），建议使用使用ip的最后两个网段组合（192.168.100.199，设置成100199）
# 9.innodb_buffer_pool_size = 10G #纯mysql server 配置50%和 混合内存配置不低于10G~40%
#10.slave-parallel #从库开启并行复制，并行复制参数取消注释
#11.innodb_data_file_path = ibdata1:1G:autoextend #确认配置是否跟原来一样,之前已配置好请维持原样,如未配置请注释掉，新版本请取消注释
#12.log_bin = ${MYSQL_DATADIR}/binlog
#13.open_files_limit = 65535 #注意/usr/lib/systemd/system/mysqld.service下LimitNOFILE会影响open_files_limit
#14.wait_timeout = 86400 #长连接无法释放，导致连接数爆满，调整为一天
#15.innodb_online_alter_log_max_size=1G #由于DDL执行时间很长，期间又产生大量的dml操作，容易引起DB_ONLINE_LOG_TOO_BIG 错误
# ********************************************
[client]
port = 3306
socket = ${MYSQL_DATADIR}/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 = ${SERVER_ID} #(mgr必要配置)
tmpdir = ${MYSQL_DATADIR}
datadir = ${MYSQL_DATADIR}
socket = ${MYSQL_DATADIR}/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 = 65535 #open_files_limit->max_connections->table_open_cache->table_definition_cache四个参数时存在严格的顺序和依赖关系
max_connections = 5000
max_user_connections=4999
max_connect_errors = 100000
table_open_cache = 1024
thread_cache_size = 64
max_heap_table_size = 32M
###global cache ###
tmp_table_size = 32M #内存临时表
binlog_cache_size = 4M #二进制日志缓冲
#******************session cache ******************#
sort_buffer_size = 1M #排序缓冲
join_buffer_size = 1M #表连接缓冲
read_buffer_size = 1M #顺序读缓冲
read_rnd_buffer_size = 4M #随机读缓冲
thread_stack = 256KB #线程的堆栈的大小
######################### binlog设置 #####################
binlog_format = ROW #(mgr必要配置)
log_bin = ${MYSQL_DATADIR}/binlog #(mgr必要配置)
binlog_row_image = FULL
binlog_rows_query_log_events = ON
max_binlog_size = 1G
binlog_expire_logs_seconds = 2592000 #binlog保留30天，时间单位（秒）
sync_binlog = 1 #重要参数必须修改为1
#binlog_checksum = NONE #禁用写入二进制日志的事件的校验值(mgr必要配置)
######################### 复制设置 ########################
log_slave_updates = 1 #(mgr必要配置)
#replicate-do-db = test
#binlog-ignore-db = mysql
### GTID 配置 ###
gtid_mode=ON #(mgr必要配置)
enforce-gtid-consistency=true #(mgr必要配置)
#****************** 开启并行复制（从库）******************
slave-parallel-type=LOGICAL_CLOCK #基于组提交的并行复制方式
slave-parallel-workers= ${CPU_CORE_NUMS} #并行的SQL线程数量（cpu核数）
master-info_repository=TABLE #master信息以表的形式保存(mgr必要配置)
relay_log_info_repository=TABLE #slave信息以表的形式保存(mgr必要配置)
relay_log_recovery=ON #relay_log自我修复
#****************** 开启组复制（从库）******************
# transaction_write_set_extraction = XXHASH64 #指示 server 必须为每个事务收集写集合,并使用 XXHASH64 哈希算法将其编码为散列
# group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #给加入或创建的组命名，必须为"uuid"的格式
# group_replication_start_on_boot = off #配置插件在 server 启动时不自动启动组复制
# group_replication_local_address = '192.168.222.171:24901' #使用IP地址或本地主机名,端口24901用于接受来自组中其他成员的传入连接。
# group_replication_group_seeds ='192.168.222.171:24901,192.168.222.172:24901,192.168.222.173:24901,192.168.222.174:24901' #MGR组成员的所有IP及端口
# group_replication_bootstrap_group = off #启动不自己加入组
# group_replication_single_primary_mode=on #配置为多主模式配置为off
# group_replication_enforce_update_everywhere_checks=false #开启强制检查(单主模式必须将此选项设置为FALSE)
######################### innodb ##########################
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_size = ${BUFFER_POOL_SIZE} #系统内存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 = 10000 #根据您的服务器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_thread_concurrency = 64
innodb_strict_mode = OFF
innodb_sort_buffer_size = 4194304
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_online_alter_log_max_size=1073741824
#****************** undolog设置 ******************
innodb_undo_directory = ${MYSQL_UNDODIR} #undolog空间的目录位置
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 = ${MYSQL_DATADIR}/error.log
slow_query_log = 1
long_query_time = 10
slow_query_log_file = ${MYSQL_DATADIR}/slow.log
#=======================================================================
# MySQL mysqldump配置
#=======================================================================
[mysqldump]
quick
max_allowed_packet = 128M
#=======================================================================
# MySQL mysqld_safe配置
#=======================================================================
[mysqld_safe]
log_error = ${MYSQL_DATADIR}/error.log
pid_file = ${MYSQL_DATADIR}/mysqldb.pid
[root@ali01 scripts]# cat install_single_mysql80/installation_of_single_mysql.sh
#!/bin/bash
# line: V2.3
# mail: gczheng@139.com
# data: 2020-08-08
# script_name: installation_of_single_mysql.sh
# function: Install MySQL
. ./single_install.conf
#=======================================================================
#配置信息
#=======================================================================
MYSQL_SOURCE_PACKAGES_NAMES=`echo $MYSQL_SOURCE_PACKAGES |awk -F '/' '{print $NF}' |awk -F ".tar.xz" '{printf $1}'`
MYSQL=/usr/local/mysql/bin/mysql
#MEM_SIZE=`dmidecode -t memory | sed 's/^[ \t]*//g'|grep -i '^size'|egrep -iv "NOT|NO|Installed|Enabled"|awk -F : '{print $2}'|awk '{sum += $1};END {print sum/1024}'`
MEM_SIZE=`free -b |grep 'Mem' |awk -F ' ' '{print $2}'|awk '{print $1/2}'`
BUFFER_POOL_SIZE=`expr ${MEM_SIZE} / 2`
SERVER_ID=`ip route | awk '/src/ && !/docker/{for(i=1;i<=NF;++i)if($i == "src"){print $(i+1)}}' |head -n 1 |awk -F '.' '{print $3$4}'`
CPU_CORE_NUMS=`cat /proc/cpuinfo |grep 'processor'|wc -l`
#=======================================================================
# 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()
{
#判断 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 tar包是否存在
if [ ! -f "$MYSQL_SOURCE_PACKAGES" ];then
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') $MYSQL_SOURCE_PACKAGES is not exits, Make a copy to the directory or please download it from $MYSQL_DOWNLOAD_LINK "
read -p "Download the package from mysql(y/n):" dn
case $dn in
y|Y)
wget -O $MYSQL_SOURCE_PACKAGES $MYSQL_DOWNLOAD_LINK
;;
n|N)
exit 1
;;
*)
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') Input ERROR."
exit 1
esac
echo_color green "$(date +'%Y-%m-%d %H:%M:%S') $MYSQL_SOURCE_PACKAGES is exits ."
fi
# 判断md5值是否正确
md5=`md5sum $MYSQL_SOURCE_PACKAGES | awk '{print $1}'`
if [ "$MSYQL_PACKAGES_MD5" == "$md5" ];then
echo_color green "$(date +'%Y-%m-%d %H:%M:%S') $MYSQL_SOURCE_PACKAGES md5 ok"
else
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') $MYSQL_SOURCE_PACKAGES md5 error"
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
if [ ! -d "${MYSQL_DATADIR}" ];then
mkdir -p ${MYSQL_DATADIR} && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL data directory is created .."
chown -R mysql:mysql ${MYSQL_DATADIR}
chmod 750 ${MYSQL_DATADIR}
elif [ "$(ls -A ${MYSQL_DATADIR})" = "" ];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
if [ ! -d "${MYSQL_UNDODIR}" ];then
mkdir -p ${MYSQL_UNDODIR} && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL undo directory is created .."
chown -R mysql:mysql ${MYSQL_UNDODIR}
chmod 750 ${MYSQL_UNDODIR}
elif [ "$(ls -A ${MYSQL_UNDODIR})" = "" ];then
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') MySQL undo directory is exits."
else
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') MySQL undo directory is not empty. Please check it."
exit 1
fi
}
#=======================================================================
#检查是否有旧的mysql/mariadb版本存在
#=======================================================================
function chk_old_mysql_version()
{
mysqlNum=$(rpm -qa | grep -Ei '^mysql|^mariadb' |wc -l)
if [ "${mysqlNum}" -gt "0" ];then
rpm -qa | grep -Ei '^mysql|^mariadb'
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 rpm -e --nodeps
source /etc/profile
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."
source /etc/profile
tar_install
fi
}
#=======================================================================
# 解压安装
#=======================================================================
function installPackage()
{
count=0
package=(gcc gcc-c++ bzip2 bzip2-devel bzip2-libs python-devel libaio libaio-devel ncurses ncurses-devel cmake numactl-libs)
nums01=${#package[@]}
for((i=0;i<nums01;i++));
do
char=${package[$i]}
rpm -qa | grep "^$char"
if [ $? != 0 ] ; then
error[$count]=${package[$i]}
count=$(($count+1))
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') The ${package[$i]} is not installed.Please check it.."
fi
done
if [ $count -gt "0" ];then
echo "You have $count patchs are not installed."
echo "the not installed patch is:"
nums02=${#error[@]}
for((ii=0;ii<nums02;ii++));
do
echo "${error[$ii]}^"
done
echo -e "Are you sure to install the patch[yes or no]:\c"
read select
if [ $select == "yes" ]; then
for((is=0;is<nums02;is++));
do
var=${error[$is]}
echo $var
yum install -y $var
done
fi
else
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Check pass!.."
fi
count=0
rpm -q gcc gcc-c++ bzip2 bzip2-devel bzip2-libs python-devel libaio libaio-devel ncurses ncurses-devel cmake numactl-libs | grep "not installed"
}
function tar_install()
{
installPackage
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Starting unzip $MYSQL_SOURCE_PACKAGES .."
tar xvJf $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/${MYSQL_SOURCE_PACKAGES_NAMES} /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /usr/local/${MYSQL_SOURCE_PACKAGES_NAMES}
chmod 750 /usr/local/mysql
chmod 750 /usr/local/${MYSQL_SOURCE_PACKAGES_NAMES}
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
sed -i 's:${MYSQL_UNDODIR}:'"${MYSQL_UNDODIR}:g"'' /etc/my.cnf
sed -i 's:${MYSQL_DATADIR}:'"${MYSQL_DATADIR}:g"'' /etc/my.cnf
sed -i 's:${BUFFER_POOL_SIZE}:'"${BUFFER_POOL_SIZE}:g"'' /etc/my.cnf
sed -i 's:${SERVER_ID}:'"${SERVER_ID}:g"'' /etc/my.cnf
sed -i 's/${CPU_CORE_NUMS}/'"${CPU_CORE_NUMS}/g"'' /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/${MYSQL_SOURCE_PACKAGES_NAMES} /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /usr/local/${MYSQL_SOURCE_PACKAGES_NAMES}
chmod 750 /usr/local/mysql
chmod 750 /usr/local/${MYSQL_SOURCE_PACKAGES_NAMES}
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
sed -i 's:${MYSQL_UNDODIR}:'"${MYSQL_UNDODIR}:g"'' /etc/my.cnf
sed -i 's:${MYSQL_DATADIR}:'"${MYSQL_DATADIR}:g"'' /etc/my.cnf
sed -i 's:${BUFFER_POOL_SIZE}:'"${BUFFER_POOL_SIZE}:g"'' /etc/my.cnf
sed -i 's:${SERVER_ID}:'"${SERVER_ID}:g"'' /etc/my.cnf
sed -i 's/${CPU_CORE_NUMS}/'"${CPU_CORE_NUMS}/g"'' /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 add_mysql_ldconfig()
{
cat > /etc/ld.so.conf.d/mysql.conf <<EOF
/usr/local/mysql/lib
EOF
ldconfig
}
function add_libmysqlclient()
{
if [ -f /etc/ld.so.conf.d/mysql.conf ];then
LDNUMS=`grep -i "/usr/local/mysql/lib" /etc/ld.so.conf.d/mysql.conf |wc -l`
if [ $LDNUMS -eq 0 ];then
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') The configuration file is empty!"
add_mysql_ldconfig
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Flush ldconfig done .."
else
LDEXISTS=`grep -i "/usr/local/mysql/lib" /etc/ld.so.conf.d/mysql.conf |grep -e "^#" |wc -l`
if [ $LDEXISTS -gt 0 ];then
add_mysql_ldconfig
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Has been configured!"
fi
fi
else
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') /etc/ld.so.conf.d/mysql.conf is not exits!"
add_mysql_ldconfig
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') Flush ldconfig done .."
fi
}
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()
{
passwd=`awk '/A temporary password/ {print $NF}' ${MYSQL_DATADIR}/error.log`
#echo_color cyan "mysql temp password is ${password}"
if [ "${passwd}" != "" ];then
${MYSQL} -uroot --password=${passwd} --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 "CREATE USER root@'%' IDENTIFIED BY 'iforgot';GRANT ALL ON *.* TO root@'%' ;" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予root用户通过任意主机操作所有数据库的所有权限成功."
p2=$?
${MYSQL} -uroot -piforgot -e "CREATE USER repl@'%' IDENTIFIED BY 'repl';GRANT RELOAD,REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'%' ;" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予repl用户通过任意主机对所有数据库进行主从复制的权限成功."
p3=$?
${MYSQL} -uroot -piforgot -e "CREATE USER monitor@'%' IDENTIFIED BY 'monitor';GRANT SELECT, PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO monitor@'%';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予monitor用户通过任意主机对所有数据库的读取权限成功."
p4=$?
${MYSQL} -uroot -piforgot -e "CREATE USER dbbackup@'%' IDENTIFIED BY 'dbbackup';GRANT SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS,SUPER,CREATE,SHOW DATABASES,SHOW VIEW, EVENT, TRIGGER,
CREATE TABLESPACE ON *.* TO dbbackup@'%';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予dbbackup用户通过任意主机对所有数据库进行备份的权限成功."
p5=$?
${MYSQL} -uroot -piforgot -e "CREATE USER producer@'%' IDENTIFIED BY 'iforgot';GRANT INSERT,UPDATE,DELETE,SELECT,CREATE,DROP,INDEX,TRIGGER,ALTER ON *.* TO producer@'%';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予producer用户通过任意主机对所有数据库进行常规操作的权限成功."
p6=$?
${MYSQL} -uroot -piforgot -e "CREATE USER producer@'localhost' IDENTIFIED BY 'iforgot';GRANT INSERT,UPDATE,DELETE,SELECT,CREATE,DROP,INDEX,TRIGGER,ALTER ON *.* TO producer@'localhost';" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 授予producer用户通过localhost主机对所有数据库进行常规操作的权限成功."
p7=$?
${MYSQL} -uroot -piforgot -e "flush privileges" &> /dev/null && echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 权限刷新成功."
p8=$?
if [[ "${p1}" == "0" && "${p2}" == "0" && "${p3}" == "0" && "${p4}" == "0" && "${p5}" == "0" && "${p6}" == "0" && "${p7}" == "0" && "${p8}" == "0" ]];then
echo_color cyan "$(date +'%Y-%m-%d %H:%M:%S') 现在可以登录mysql数据库，root和producer用户的默认密码是\033[41;37m iforgot\033[0m."
${MYSQL} -uroot -piforgot -e "select user,host,authentication_string from mysql.user;"
else
echo_color red "$(date +'%Y-%m-%d %H:%M:%S') 获取随机密码（$passwd）授权失败,请手动执行授权操作."
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
add_libmysqlclient
el6_create_mysql_service
sleep 5
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
add_libmysqlclient
el7_create_mysql_service
sleep 5
modify_mysql_account
fi
}
mysql_install
echo_color blue "$(date +'%Y-%m-%d %H:%M:%S') \033[42;37m installation_of_single_mysql.sh执行完成 \033[0m"
五、 MySQL多实例安装
1. 多实例介绍
- 一台服务器上安装多个MySQL数据库实例
- 可以充分利用服务器的硬件资源（注意io资源）
- 通过mysqld_multi进行管理
- 可以同版本或异版本
2. 配置文件要点
-
[mysqld_multi]是否需要配置-
my.cnf上直接配置[mysqld1]、[mysqld2]、[mysqld3]实例标签，而不配置[mysqld_multi],使用mysqld_multi start 1也是可以启动数据库实例的，但是没有mysqld_safe的守护进程。所以该标签需要配置 -
如果在
[client]和[mysqld_multi]标签中同时存在user和password, 则在关闭数据库实例中会使用[mysqld_multi]中的user去关闭。
-
-
(
存在精确匹配的标签，则优先使用精确匹配标签下的配置项) -
multi_admin用户的作用
通过官方文档中我们看到，'multi_admin'@'localhost'这个用户主要的作用是用来关闭数据库实例，因为文档中只授权了SHUTDOWN权限。所以在[mysqld_multi]标签下，我们需要配置user和password来进行关闭数据库实例。但是实际上有bug，还不如用（mysqld_multi --user=root --password=iforgot stop 1）授权有足够权限，这个权限还在摸索中，哪位有验证提供一下，谢谢！
3. 环境说明
mysqld1-- MySQL 5.6.49mysqld2-- MySQL 5.7.31mysqld3-- MySQL 8.0.21
4. 配置说明
-
MySQL实例1
mysqld1(MySQL 5.6.49)- port = 3307
- datadir = /data/mysqldata56
- socket = /data/mysqldata56/mysql.sock
-
MySQL实例2
mysqld2(MySQL 5.7.31)- port = 3308
- datadir = /data/mysqldata57
- socket = /data/mysqldata57/mysql.sock
-
MySQL实例3
mysqld3(MySQL 8.0.21)- port = 3309
- datadir = /data/mysqldata80
- socket = /data/mysqldata80/mysql.sock
这三个参数必须定制且必须不同 (port / datadir / socket)
server-id和多数据库实例没有关系，和数据库复制有关系。
注意MySQL5.6.49的plugin_dir的路径
-
配置说明：
- 1：配置的标签顺序没有关系，不会影响最终配置的有效性。
- 2：同类型标签中的配置项会合并，形成一个大的配置项
- 3：
匹配度高的标签中的配置项的值，会覆盖掉匹配度低的标签中的配置项的值
[mysqldN]中的配置项会和[mysqld]中的配置项进行合并，并且[mysqldN]中已有的配置项的值，会覆盖掉[mysqld]中的配置项的值,如datadir, port等
- 配置文件
[root@ali01 bin]# cat /etc/my.cnf
#==========================================================
# 异版本多实例配置文件
#==========================================================
[client]
# 这个标签如果配置了用户和密码，
# 并且[mysqld_multi]下没有配置用户名密码，
# 则mysqld_multi stop时, 会使用这个密码
# 如果没有精确的匹配，则匹配[client]标签
user = root
password = iforgot
#prompt = "(\u@\h) [\v] \\R:\\m:\\s [\d]> "
[mysqld_multi]
# 官方文档中写的password，但是存在bug，需要改成pass(v5.7.18)
# 写成password，start时正常，stop时，报如下错误
# Access denied for user 'multi_admin'@'localhost' (using password: YES)
mysqld =/usr/local/mysql80/bin/mysqld
mysqladmin =/usr/local/mysql80/bin/mysqladmin
log = /var/log/mysqld_multi.log
#user = multi_admin
#pass = 123456
################################################################
################################################################
[mysqld56]
# mysqld后面的数字为GNR, 是该实例的标识（mysqld_multi start 1）
server-id = 5649
mysqld = /usr/local/mysql56/bin/mysqld
mysqladmin = /usr/local/mysql56/bin/mysqladmin
group_concat_max_len = 102400
user = mysql
port = 3307
socket = /data/mysqldata56/mysql.sock
basedir = /usr/local/mysql56
datadir = /data/mysqldata56/data
pid_file = /data/mysqldata56/data/mysql.pid
log_error = /data/mysqldata56/logs/mysql_error.log
log_bin = /data/mysqldata56/logs/binlog
slow_query_log_file = /data/mysqldata56/logs/slow.log
plugin_dir = /usr/local/mysql56/lib/plugin
################################################################
################################################################
[mysqld57]
server-id = 5731
mysqld = /usr/local/mysql57/bin/mysqld
mysqladmin = /usr/local/mysql57/bin/mysqladmin
user = mysql
port = 3308
socket = /data/mysqldata57/mysql.sock
basedir = /usr/local/mysql57
datadir = /data/mysqldata57/data/
pid_file = /data/mysqldata57/data/mysql.pid
log_error = /data/mysqldata57/logs/mysql_error.log
log_bin = /data/mysqldata57/logs/binlog
slow_query_log_file = /data/mysqldata57/logs/slow.log
log_timestamps = system
################################################################
################################################################
[mysqld80]
server-id = 8021
mysqld = /usr/local/mysql80/bin/mysqld
mysqladmin = /usr/local/mysql80/bin/mysqladmin
user = mysql
port = 3309
socket = /data/mysqldata80/mysql.sock
basedir = /usr/local/mysql80
datadir = /data/mysqldata80/data
pid_file = /data/mysqldata80/data/mysql.pid
log_error = /data/mysqldata80/logs/mysql_error.log
log_bin = /data/mysqldata80/logs/binlog
slow_query_log_file = /data/mysqldata80/logs/slowlogs/slow.log
################################################################
################################################################
[mysqld]
wait_timeout = 86400
#interactive_timeout = 600
sql_mode = ''
#sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
skip_name_resolve = 1
lower_case_table_names = 0
character-set-server = utf8
#auto_increment_increment = 1
#auto_increment_offset = 1
#log_timestamps = system
tmpdir = /tmp
#==========================================================
#bin setting
#==========================================================
binlog_format = ROW
log_bin_trust_function_creators = 1
binlog_cache_size = 64M
max_binlog_cache_size = 512M
max_binlog_size = 128M
expire_logs_days = 7
#==========================================================
# replication relay-log
#==========================================================
log-slave-updates = 1
slave-net-timeout = 60
sync-master-info = 1
sync-relay-log = 1
sync-relay-log-info = 1
#==========================================================
#no need to sync database
#==========================================================
binlog-ignore-db = test
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
#==========================================================
#performance setting
#==========================================================
open_files_limit = 1024000
max_connections = 500
max_user_connections = 9990
max_connect_errors = 100000
table_open_cache = 1024
max_allowed_packet = 32M
thread_cache_size = 64
max_heap_table_size = 16M
#query_cache_type = 0
###global cache ###
key_buffer_size = 16M
#query_cache_size = 0
###session cache ###
sort_buffer_size = 8M #排序缓冲
join_buffer_size = 4M #表连接缓冲
read_buffer_size = 8M #顺序读缓冲
read_rnd_buffer_size = 8M #随机读缓冲
tmp_table_size = 32M #内存临时表
binlog_cache_size = 4M #二进制日志缓冲
thread_stack = 256KB #线程的堆栈的大小
#==========================================================
#innodb setting
#==========================================================
default-storage-engine = InnoDB
innodb_buffer_pool_size = 128M
innodb_open_files = 1000
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_instances = 8
innodb_lock_wait_timeout = 5
innodb_io_capacity = 1000
innodb_io_capacity_max = 20000
innodb_thread_concurrency = 64
innodb_strict_mode = OFF
innodb_sort_buffer_size = 4194304
#==========================================================
#slow setting
#==========================================================
slow-query-log = on
long_query_time = 1
[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 128M # 该参数减小到1G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery = 1
log_timestamps = system
transaction_write_set_extraction= MURMUR32
show_compatibility_56 = on
[mysqld-8.0]
sql_mode=TRADITIONAL
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 1G # 该参数减小到1G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery = 1
log_timestamps = system
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 604800
log_error_verbosity = 3
5. 安装多实例
yum remove MariaDB-common MariaDB-compat MariaDB-server --rhel7删除mariadb
yum install gcc gcc-c++ bzip2 bzip2-devel bzip2-libs python-devel -y --安装mysql环境包
groupadd mysql && useradd -r -g mysql -s /bin/false mysql --添加用户
tar zxvf mysql-8.0.21-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ --解压mysql到/usr/local目录
ln -s /usr/local/mysql-8.0.21-linux-glibc2.12-x86_64 /usr/local/mysql80 --创建软连接
chown -R mysql.mysql /usr/local/mysql* --修改文件所有者
--下面添加到环境变量
cat <<EOF >>/etc/profile
export PATH=\$PATH:/usr/local/mysql80/bin/
EOF
source /etc/profile
echo $PATH
--使用mysqld_multi启动，mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR...]
- 1、初始化mysql5.6
tar zxvf mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ --解压mysql到/usr/local目录
ln -s /usr/local/mysql-5.6.49-linux-glibc2.12-x86_64 /usr/local/mysql56 --创建软连接
chown -R mysql.mysql /usr/local/mysql* --修改文件所有者
#rm -rf /data/mysqldata56
mkdir -p /data/mysqldata56/{logs,data} --创建主目录
chown -R mysql.mysql /data/mysqldata56 --修改文件所有者
chmod 755 /data/mysqldata56 --添加权限
cd /usr/local/mysql56/
#（mysql5.6初始化密码是空）
./scripts/mysql_install_db --defaults-file=my.cnf --user=mysql --basedir=/usr/local/mysql56 --datadir=/data/mysqldata56/data --explicit_defaults_for_timestamp
mysqld_multi start 56 && sleep 3 && mysqld_multi report 56 --启动mysql并查看是否运行
mysql -uroot -p --socket=/data/mysqldata56/mysql.sock
#设置root@localhost账号密码
SET Password=PASSWORD('iforgot');flush privileges;
#关闭mysql
#usr/local/mysql56/bin/mysqladmin -uroot -piforgot -S /data/mysqldata56/mysql.sock shutdown
- 2、初始化mysql5.7
tar zxvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ --解压mysql到/usr/local目录
ln -s /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql57 --创建软连接
chown -R mysql.mysql /usr/local/mysql* --修改文件所有者
#rm -rf /data/mysqldata57
mkdir -p /data/mysqldata57/{logs,data}
chown -R mysql.mysql /data/mysqldata57
chmod 755 /data/mysqldata57
cd /usr/local/mysql57/bin/
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysqldata57/data --explicit_defaults_for_timestamp
#（mysql5.7临时密码在日志输出上面有提示）初始化时显示随机密码t0jik,dIlbXD
mysqld_multi start 57 && sleep 3 && mysqld_multi report 57
mysql -uroot -p --socket=/data/mysqldata57/mysql.sock
#设置root@localhost账号密码
alter user root@localhost identified by 'iforgot';flush privileges;
#/usr/local/mysql57/bin/mysqladmin -uroot -piforgot -S /data/mysqldata57/mysql.sock shutdown
- 3、初始化mysql8.0
rm -rf /data/mysqldata80
mkdir -p /data/mysqldata80/{logs,data}
chown -R mysql.mysql /data/mysqldata80
chmod 755 /data/mysqldata80
cd /usr/local/mysql80/bin/
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql80 --datadir=/data/mysqldata80/data --explicit_defaults_for_timestamp
#（mysql8.0临时密码在日志输出上面有提示）初始化时显示随机密码g?jVu?)!D8T=
mysqld_multi start 80 && sleep 3 && mysqld_multi report 80
mysql -uroot -p --socket=/data/mysqldata80/mysql.sock
alter user root@localhost identified by 'iforgot';flush privileges;
/usr/local/mysql80/bin/mysqladmin -uroot -piforgot -S /data/mysqldata80/mysql.sock shutdown
-- 安装后，需要检查mysql_error.log 确保没有错误出现
- 启动数据库，并修改root账号，授权其他账号
[root@ali01 /]# mysqld_multi start 56
[root@ali01 /]# mysqld_multi start 57
[root@ali01 /]# mysqld_multi start 80
[root@ali01 /]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld56 is running
MySQL server from group: mysqld57 is running
MySQL server from group: mysqld80 is running
[root@ali01 /]#
--查看三个mysql端口
[root@ali01 /]# netstat -tunlp | grep mysql
tcp6 0 0 :::3307 :::* LISTEN 25257/mysqld
tcp6 0 0 :::3308 :::* LISTEN 25097/mysqld
tcp6 0 0 :::3309 :::* LISTEN 25291/mysqld
tcp6 0 0 :::33060 :::* LISTEN 25097/mysqld
[root@ali01 /]# ps -ef|grep mysql |grep -v grep
mysql 25097 1 0 19:21 pts/0 00:00:04 mysqld --server-id=5731 --user=mysql --port=3308 --socket=/data/mysqldata57/mysql.sock --basedir=/usr/local/mysql57 --datadir=/data/mysqldata57/data/ --pid_file=/data/mysqldata57/data/mysql.pid --log_error=/data/mysqldata57/logs/mysql_error.log --log_bin=/data/mysqldata57/logs/binlog --slow_query_log_file=/data/mysqldata57/logs/slow.log --log_timestamps=system
mysql 25257 1 0 19:22 pts/0 00:00:00 /usr/local/mysql56/bin/mysqld --server-id=5649 --group_concat_max_len=102400 --user=mysql --port=3307 --socket=/data/mysqldata56/mysql.sock --basedir=/usr/local/mysql56 --datadir=/data/mysqldata56/data --pid_file=/data/mysqldata56/data/mysql.pid --log_error=/data/mysqldata56/logs/mysql_error.log --log_bin=/data/mysqldata56/logs/binlog --slow_query_log_file=/data/mysqldata56/logs/slow.log --plugin_dir=/usr/local/mysql56/lib/plugin
mysql 25291 1 0 19:22 pts/0 00:00:03 mysqld --server-id=8021 --user=mysql --port=3309 --socket=/data/mysqldata80/mysql.sock --basedir=/usr/local/mysql80 --datadir=/data/mysqldata80/data --pid_file=/data/mysqldata80/data/mysql.pid --log_error=/data/mysqldata80/logs/mysql_error.log --log_bin=/data/mysqldata80/logs/binlog --slow_query_log_file=/data/mysqldata80/logs/slowlogs/slow.log
[root@ali01 /]#
-- 使用sock进行登录，并输入临时密码后，修改密码，如下：
[root@ali01 software]# mysql --prompt="(\u@\h) [\v] \\R:\\m:\\s [\d]> " -uroot -p --socket=/data/mysqldata56/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.49-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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.
(root@localhost) [5.6.49-log] 19:52:56 [(none)]> set password=password('iforgot');flush privileges;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [5.6.49-log] 19:54:17 [(none)]> create user 'multi_admin'@'localhost' identified by 'multi_pass';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [5.6.49-log] 19:54:17 [(none)]> grant shutdown on *.* to 'multi_admin'@'localhost';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [5.6.49-log] 19:54:17 [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [5.6.49-log] 19:54:17 [(none)]> exit;
Bye
--mysql57
[root@ali01 bin]# mysql --prompt="(\u@\h) [\v] \\R:\\m:\\s [\d]> " -uroot -p --socket=/data/mysqldata57/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31-log
Copyright (c) 2000, 2020, 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.
(root@localhost) [5.7.31-log] 20:03:59 [(none)]> alter user 'root'@'localhost' identified by 'iforgot' ;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [5.7.31-log] 20:04:14 [(none)]> create user 'multi_admin'@'localhost' identified by 'multi_pass';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [5.7.31-log] 20:04:14 [(none)]> grant shutdown on *.* to 'multi_admin'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [5.7.31-log] 20:04:14 [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [5.7.31-log] 20:04:14 [(none)]> exit;
Bye
--mysql8.0
[root@ali01 bin]# mysql --prompt="(\u@\h) [\v] \\R:\\m:\\s [\d]> " -uroot -p --socket=/data/mysqldata80/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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.
(root@localhost) [8.0.21] 20:08:51 [(none)]> alter user 'root'@'localhost' identified by 'iforgot' ;
create user 'multi_admin'@'localhost' identified by 'multi_pass';
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [8.0.21] 20:08:58 [(none)]> create user 'multi_admin'@'localhost' identified by 'multi_pass';
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [8.0.21] 20:08:58 [(none)]> grant shutdown on *.* to 'multi_admin'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)
(root@localhost) [8.0.21] 20:08:58 [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [8.0.21] 20:08:58 [(none)]> exit;
Bye
[root@ali01 bin]#
- 检测port和socket
[root@ali01 bin]# ps -ef|grep mysql |grep -v grep
mysql 25291 1 0 19:22 pts/0 00:00:13 mysqld --server-id=8021 --user=mysql --port=3309 --socket=/data/mysqldata80/mysql.sock --basedir=/usr/local/mysql80 --datadir=/data/mysqldata80/data --pid_file=/data/mysqldata80/data/mysql.pid --log_error=/data/mysqldata80/logs/mysql_error.log --log_bin=/data/mysqldata80/logs/binlog --slow_query_log_file=/data/mysqldata80/logs/slowlogs/slow.log
mysql 25594 1 0 19:44 pts/0 00:00:01 /usr/local/mysql56/bin/mysqld --server-id=5649 --group_concat_max_len=102400 --user=mysql --port=3307 --socket=/data/mysqldata56/mysql.sock --basedir=/usr/local/mysql56 --datadir=/data/mysqldata56/data --pid_file=/data/mysqldata56/data/mysql.pid --log_error=/data/mysqldata56/logs/mysql_error.log --log_bin=/data/mysqldata56/logs/binlog --slow_query_log_file=/data/mysqldata56/logs/slow.log --plugin_dir=/usr/local/mysql56/lib/plugin
mysql 25723 1 0 20:02 pts/1 00:00:00 /usr/local/mysql57/bin/mysqld --server-id=5731 --user=mysql --port=3308 --socket=/data/mysqldata57/mysql.sock --basedir=/usr/local/mysql57 --datadir=/data/mysqldata57/data/ --pid_file=/data/mysqldata57/data/mysql.pid --log_error=/data/mysqldata57/logs/mysql_error.log --log_bin=/data/mysqldata57/logs/binlog --slow_query_log_file=/data/mysqldata57/logs/slow.log --log_timestamps=system
root 25934 22583 0 20:05 pts/0 00:00:00 mysql --prompt=(\u@\h) [\v] \R:\m:\s [\d]> -uroot -p --socket=/data/mysqldata56/mysql.sock
[root@ali01 bin]#
-- 上面是mysqld_safe的进程
- 设置login-path
设置login-path主要为了能够简化登录，同时还可以让每个数据库的密码都不同，避免使用[client]下的统一用户名密码
[root@ali01 /]# mysql_config_editor set -G mysqld56 -u root -p -S /data/mysqldata56/mysql.sock
Enter password:
[root@ali01 /]# mysql_config_editor set -G mysqld57 -u root -p -S /data/mysqldata57/mysql.sock
Enter password:
[root@ali01 /]# mysql_config_editor set -G mysqld80 -u root -p -S /data/mysqldata80/mysql.sock
Enter password:
[root@ali01 /]# mysql --login-path=mysqld56
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.49-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> exit
Bye
[root@ali01 /]#
-- 然后可以使用mysql --login-path=mysql1 这种方式登录
六. MySQL版本升级
1. 环境说明：
一般说来，MySQL数据库的二进制数据文件，也就是my.cnf中的配置项datadir所在的位置，和我们MySQL应用程序安装的位置，是分开的，仅仅通过配置项告诉MySQL，数据库的数据存在datadir这个目录下。当程序和数据分离以后，方便我们对数据库应用程序做版本的升级或者回退。
2. 环境举例：
-
MySQL安装目录：
- MySQL 5.6.27: /usr/local/mysql-5.6.27-linux-glibc2.5-x86_64
- MySQL 5.7.18: /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64
-
datadir目录：
- /r2/mysqdata/
-
初始环境：
shell> ll /usr/local/
lrwxrwxrwx 1 root root 34 Nov 16 11:30 mysql -> mysql-5.6.27-linux-glibc2.5-x86_64
lrwxrwxrwx 1 root root 46 Nov 22 22:56 mysql-5.6.27-linux-glibc2.5-x86_64
drwxr-xr-x 9 root root 120 Nov 22 22:54 mysql-5.7.18-linux-glibc2.5-x86_64
shell> ll /data/mysqldata/
-- 1 mysql mysql 259234780 11月 22 11:40 binlog.000269
-rw-r----- 1 mysql mysql 1007 11月 19 13:06 binlog.index
-rw-r----- 1 mysql mysql 2274867 11月 22 14:53 error.log
-rw-r----- 1 mysql mysql 4676173 11月 19 10:57 ib_buffer_pool
-rw-r----- 1 mysql mysql 3087007744 11月 22 14:12 ibdata1
-rw-r----- 1 mysql mysql 2147483648 11月 22 14:12 ib_logfile0
-rw-r----- 1 mysql mysql 2147483648 11月 22 14:12 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 11月 22 16:11 ibtmp1
drwxr-x--- 2 mysql mysql 4096 11月 14 17:59 mysql
-rw-r----- 1 mysql mysql 6 11月 19 12:29 mysqldb.pid
srwxrwxrwx 1 mysql mysql 0 11月 19 12:29 mysql.sock
-rw------- 1 mysql mysql 6 11月 19 12:29 mysql.sock.lock
drwxr-x--- 2 mysql mysql 4096 11月 14 17:59 performance_schema
drwxr-x--- 2 mysql mysql 4096 11月 19 13:19 sbtest
-rw-r----- 1 mysql mysql 5930475516 11月 19 13:07 slow.log
drwxr-x--- 2 mysql mysql 12288 11月 14 17:59 sys
drwxr-x--- 2 mysql mysql 4096 11月 17 08:58 sysbench
3. 版本升级
shell> /etc/init.d/mysqld stop #安全的停止数据库的运行
shell> cd /usr/local/
shell> unlink mysql
shell> ln -s mysql-5.7.18-linux-glibc2.5-x86_64 mysql
#此时，MySQL的应用程序版本已经升级完成
#/etc/init.d/mysqld
#/etc/profile中PATH增加的/usr/local/mysql/bin
#都不需要做任何的改变，即可将当前系统的mysql版本升级完成
#注意：此时只是应用程序升级完成，系统表仍然还是5.6的版本
shell> cd /usr/local/mysql
shell> chown root.mysql . -R
#5.7.x -> 5.6.X 降级存在问题，这里暂且注释掉
#shell> cp -r /data/mysql_data/mysql /你的备份路径/mysql_5_6_27.backup
#该步骤将mysql5.6.27版本的系统表进行了备份，以便将来可以回退
shell> /etc/init.d/mysqld start
#此时 /etc/init.d/mysqld start # 可以启动
# 且可以使用 mysql -u root -p （原密码） 进入数据库
# show databases;存在test表，而没有sys表（数据的二进制文件兼容）
# 但是如果去看error.log会发现好多的WARNNING
# 所以，这个时候我们要去 upgrade 去升级
shell> mysql_upgrade -p -s
#参数 -s 一定要加,表示只更新系统表，-s: upgrade-system-tables
#如果不加-s,则会把所有库的表以5.7.18的方式重建，线上千万别这样操作
#因为数据库二进制文件是兼容的，无需升级
#什么时候不需要-s ? 当一些老的版本的存储格式需要新的特性，
#来提升性能时，不加-s
#即使通过slave进行升级，也推荐使用该方式升级，速度比较快
Enter password:
The --upgrade-system-tables option was used, databases wont be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
shell> mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 232942
Server version: 5.7.18-log MySQL Community Server (GPL)
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 |
| sys | # 5.7 新的sys库
| test | # 5.6 中的test库
+--------------------+
5 rows in set (0.00 sec)
5.1.X、5.5.X、5.6.X是可以直接通过该方式升级到5.7.X;5.0.X未知，需要测试
注意：
如果原来数据二进制文件保存在/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data目录下,在升级之前，要么将该目录的数据拷贝到新的你指定的data目录（比如/usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/data ），要么修改my.cnf，将datadir指向/usr/local/mysql-5.6.27-linux-glibc2.5-x86_64/data，总之一定要确保my.cnf中的数据位置和你实际的数据位置是一致的，不管是默认的也好，还是你datadir指定的也好
4.关于降级问题的说明
通过覆盖mysql系统表的方式存在问题，会导致启动不起来。官方建议如下：
官方MySQL5.7降级建议
上述建议中使用的SQL语句可在mysql5.7的源码的srcipts/mysql_system_tables_fix_for_downgrade.sql中找到，或者直接运行这个sql脚本。
测试后发现，有bug; 可以启动，但是原来的用户表，无法访问。
