mysql离线安装

Mysql源

http://mirrors.sohu.com/mysql/ （当然，包含其他资源，很不错 ^ _ ^）

当前选择Mysql5.1.72版本：MySQL-community-5.1.72-1.rhel5.x86_64.rpm-bundle.tar

1、安装

1.1、卸载mariadb、mysql

** 如果存在，则删除即可 **
[root@mysql]# rpm -qa |grep -E 'mysql|MYSQL|MySQL'
[root@mysql]# rpm -qa|grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@mysql]# rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
[root@mysql]# rpm -qa|grep mariadb
 No packages ...

** 杀残留进程 **
[root@mysql]# netstat -tanlp|grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      73482/mysqld
tcp        0      0 10.47.85.205:3306       10.47.101.11:54488      ESTABLISHED 73482/mysqld
[root@mysql]# kill -9 73482

1.2、RPM安装

1.2.1、建用户

#添加mysql组
[root@mysql]# groupadd mysql
#添加mysql用户
[root@mysql]# useradd -g mysql mysql 

1.2.2、解压

#上传Mysql（下方路径）
[root@mysql]# mkdir /home/packages/mysql-5.1.72;cd /home/packages/mysql-5.1.72 
#解压
[root@mysql]# tar -xvf MySQL-community-5.1.72-1.rhel5.x86_64.rpm-bundle.tar

1.2.3、安装

【5.1版本】

检查是否有mariadb：
[root@mysql]# rpm -qa|grep mariadb
[root@mysql]# for list in $(rpm -qa|grep mariadb);do rpm -e --nodeps ${list};done

开始安装：
[root@mysql]# rpm -ivh MySQL-server-community-5.1.72-1.rhel5.x86_64.rpm
准备中...                          ################################# [100%]
正在升级/安装...
1:MySQL-server-community-5.1.72-1.r################################# [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h test216 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
Starting MySQL. SUCCESS!
Giving mysqld 2 seconds to start

[root@mysql]# rpm -ivh MySQL-client-community-5.1.72-1.rhel5.x86_64.rpm
准备中...                          ################################# [100%]
正在升级/安装...
1:MySQL-client-community-5.1.72-1.r################################# [100%]

检查Mysql服务：
[root@mysql]# netstat -an|grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN
[root@mysql]# service mysql status
 SUCCESS! MySQL running (27115)

【5.7版本】

注意：如果下载的离线rpm安装包，安装时出现缺失libsaal等模块则安装包缺失模块，需重新下载其他版本。
rpm安装结束后执行初始化命令：

检查是否有mariadb：
[root@mysql]# rpm -qa|grep mariadb
[root@mysql]# for list in $(rpm -qa|grep mariadb);do rpm -e --nodeps ${list};done

开始解压安装：
[root@mysql]# tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
[root@mysql]# rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm
[root@mysql]# rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm
[root@mysql]# rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm
[root@mysql]# rpm -ivh mysql-community-devel-5.7.22-1.el7.x86_64.rpm
[root@mysql]# rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm

1.2.4 卸载

[root@mysql]# rpm -qa|grep mysql
[root@mysql]# for list in $(rpm -qa|grep mysql);do rpm -e --nodeps ${list};done

1.3 Tar包安装

1.3.1 下载版本

1.3.1.1 MySQL5.7+

地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

安装脚本（Python）：

def InstallMysql57(mysqlInstallPath, pathMysqlData, mysqlPort, packageMysql):
    configMysql57 = """[mysqld]
explicit_defaults_for_timestamp=true
basedir = %s/mysql
datadir = %s
socket=/var/lib/mysql/mysql.sock
#log-error = /var/log/mysql-error.log
lower_case_table_names=1
default-storage-engine=INNODB
character-set-server=utf8
user=mysql
collation-server=utf8_general_ci
port=%s
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
lc-messages-dir=%s/mysql/share
lc-messages=en_US

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set = utf8

[client]
default-character-set=utf8
socket = /var/lib/mysql/mysql.sock
port=%s""" % (mysqlInstallPath, pathMysqlData, mysqlPort, mysqlInstallPath, mysqlPort)
    logger.info("Start install mysql5.7")
    os.system('tar -xzvf %s.tar.gz' % packageMysql)
    os.system('rm -rf %s' % mysqlInstallPath)
    os.system('mkdir -p %s' % mysqlInstallPath)
    os.system('mv %s %s/mysql' % (packageMysql, mysqlInstallPath))
    os.system('groupadd mysql')
    os.system('useradd -r -g mysql -s /bin/false mysql')
    os.system("chmod 775 /var/log")
    if not os.path.exists('/var/lib/mysql'):
        os.system("mkdir /var/lib/mysql")
    os.system("chmod 777 /var/lib/mysql")
    output = commands.getstatusoutput('%s/mysql/bin/mysqld --initialize --user=mysql --basedir=%s/mysql --datadir=%s' % (mysqlInstallPath, mysqlInstallPath, pathMysqlData))[-1]
    pwd = output[output.find(': ', -20)+2:].strip()
    print 'password: ', pwd
    os.system('rm -rf /etc/my.cnf;')
    os.system('echo "%s" >> /etc/my.cnf' % configMysql57)
    os.system('nohup %s/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=root &>/dev/null &' % mysqlInstallPath)
    time.sleep(10)
    arg = '%s/mysql/bin/mysql -uroot -p' % mysqlInstallPath
    expectCommand = """
                    expect -c "
                    set timeout 1;
                    spawn %s;
                    expect password:;
                    send \\\"%s\n\\\";
                    expect mysql>;
                    send \\\"set password=password('U_tywg_2013');\n\\\";
                    expect mysql>;
                    send \\\"use mysql;\n\\\";
                    expect mysql>;
                    send \\\"delete from user where user<>'root' or host<>'localhost';\n\\\";
                    expect mysql>;
                    send \\\"update user set host='%%';\n\\\";
                    expect mysql>;
                    send \\\"flush privileges;\n\\\";
                    expect mysql>;
                    send \\\"quit;\n\\\"
                    expect Bye
                    "
                    """ % (arg, pwd)

    os.system(expectCommand)
    os.system("rm -rf /etc/init.d/mysql")
    os.system("cp %s/mysql/support-files/mysql.server /etc/init.d/mysql" % mysqlInstallPath)

1.3.1.2 MySQL5.5+

安装脚本：

【Python部分】
def InstallMysql55(mysqlInstallPath, pathMysqlData):
    logger.info("Start install mysql5.5")
    os.system('rm -rf %s' % mysqlInstallPath)
    os.system('mkdir -p %s' % mysqlInstallPath)
    os.system("cp mysql-5.5.35-linux2.6-x86_64.tar.gz dap_silence_install")
    os.system("sh dap_silence_install/bin/mysql_setup_start.sh silence_install %s %s/silence_install/conf/my.cnf 3306" % (mysqlInstallPath, os.getcwd()))
    os.system("echo PATH=$PATH:%s/mysql/bin >> /etc/profile" % mysqlInstallPath)

【Shell部分】
mysql_setup_start.sh
#!/bin/sh
######################################################
#1、check parameter
######################################################
#第一个参数为安装包文件所在目录
INSTALL_FILE_PATH=$1
#第二个参数为mysql安装目的目录
INSTALL_DST_PATH=$2
#第三个参数为my.cnf文件的绝对路径，比如/export/home/test/my.cnf
INSTALL_CONF_FILE=$3
#第四个参数为my.cnf中配置的mysql启动端口号
INSTALL_PORT=$4

#如果没有指定上述四个参数，提示错误，直接退出
if [ "$INSTALL_FILE_PATH" = "" ] ; then
  echo "Error:parameter error, the install file path is null!"
  exit 1;
fi
if [ "$INSTALL_DST_PATH" = "" ] ; then
  echo "Error:parameter error, the install dest path is null!"
  exit 1;
fi
if [ "$INSTALL_CONF_FILE" = "" ] ; then
  echo "Error:parameter error, the install conf file is null!"
  exit 1;
fi
if [ "$INSTALL_PORT" = "" ] ; then
  echo "Error:parameter error, the mysql listen port is null!"
  exit 1;
fi

#如果安装文件目录和安装目标目录不存在，提示错误，直接退出;如果配置文件不存在，也直接退出
if [ ! -d $INSTALL_FILE_PATH ] ; then
  echo "Error:env error, the install file path of mysql does not exist!"
  exit 1;
fi
if [ ! -d $INSTALL_DST_PATH ] ; then
  echo "Error:env error, the install dest path of mysql does not exist!"
  exit 1;
fi
if [ ! -e $INSTALL_CONF_FILE ] ; then
  echo "Error:env error, the install conf file of mysql does not exist!"
  exit 1;
fi

#记录操作时间
show_time()
{
echo "[`date '+%Y-%m-%d %H:%M:%S'`]"
}

OSNAME=`uname -s`
#判断当前系统是否为Linux，如果不是Linux，提示错误，直接退出
if [ $OSNAME != "Linux" ] ; then
  echo "Error:current os is not Linux!"
  exit 1;
fi


######################################################
#2、check install file whether matches the current system
######################################################
#进入安装文件目录，检测环境对应的MySQL安装包是否存在：如果不存在，提示错误，然后退出;如果有多个符合版本的包，优先使用最新版本的
PROCESSORTYPE=`uname -p`
OSBIT=`getconf LONG_BIT`
SIXFOUR="x86_64"
THREETWO="i686"
cd "$INSTALL_FILE_PATH"
if [ $OSNAME = "Linux" ] ; then
    tar_gz_zip=`ls | grep mysql-5.5.*-linux2.6-$PROCESSORTYPE.tar.gz$ | tail -1`
    if [ $OSBIT = "32" ]; then
    		tar_gz_zip=`ls | grep mysql-5.5.*-linux2.6-$THREETWO.tar.gz$ | tail -1`
    fi
    if [ $OSBIT = "64" ]; then
         tar_gz_zip=`ls | grep mysql-5.5.*-linux2.6-$SIXFOUR.tar.gz$ | tail -1`  
         if [ $OSBIT = "64" -a  "$tar_gz_zip" = "" ]; then
             tar_gz_zip=`ls |grep mysql-5.5.*-linux2.6-$THREETWO.tar.gz$ | tail -1`
         fi
    fi   
    if [ "$tar_gz_zip" = "" ]; then
        echo "Error: The current system is $OSNAME $OSBIT-bit, and the mysql install package matching the current system does not exist!"
        exit 1
    fi
fi

######################################################
#3、install the tar.gz
######################################################
#执行解压操作，比如解压目录为/export，实际的MySQL目录目录为/export/mysql-5.5.35-linux2.6-x86_64
#tar解压后生成的文件夹
LENGTH=${#tar_gz_zip}
TAR_DIR_LENGTH=`expr $LENGTH - 7`
TAR_DIR=${tar_gz_zip:0:$TAR_DIR_LENGTH}

#执行tar命令
echo "`show_time`====start to unzip..."
tar -zxvf $tar_gz_zip  -C $INSTALL_DST_PATH > /dev/null 2>&1
TAR_RESULT=$?
if [ $TAR_RESULT != 0 ] ; then
	echo "Error:Failed to unzip $tar_gz_zip!"
	exit 1
fi
MYSQL_PATH=`printf "$INSTALL_DST_PATH"/"$TAR_DIR"`
if [ ! -d $MYSQL_PATH ] ; then
  echo "Error:Failed to unzip $tar_gz_zip!"
  exit 1;
fi
#重命名解压包，改成mysql
cd "$INSTALL_DST_PATH"
mv $TAR_DIR mysql
MYSQL_PATH=`printf "$INSTALL_DST_PATH"/mysql`
echo "mysql install path:$MYSQL_PATH"
echo "`show_time`====end to unzip..."

#修改Mysql安装目录执行权限
chmod -R 777 "$MYSQL_PATH"

#从外部拷贝my.cnf参数文件到MYSQL_PATH路径,因为配置文件的权限要求是644（-rw-r–r–）
\cp -f "$INSTALL_CONF_FILE" "$MYSQL_PATH"
MY_CNF_FILE=`printf "$MYSQL_PATH"/my.cnf`
echo "my.cnf path:$MY_CNF_FILE"
chmod 644 $MY_CNF_FILE

######################################################
#4、set env
######################################################
#先检查系统现有环境中是否存在MYSQL_HOME变量
env_cnt=`cat /root/.bashrc | grep -i "MYSQL_HOME=$MYSQL_PATH" | wc -l`
if [ $env_cnt = 0 ] ; then
		echo "set env to bashrc!"
		#备份原有的.bashrc文件
		\cp -f /root/.bashrc /root/.bashrc_mysql_bak
		#将MYSQL_HOME添加到环境变量中
		echo -e "MYSQL_HOME=$MYSQL_PATH\nexport MYSQL_HOME\nPATH=\$MYSQL_HOME/bin:\$PATH\nexport PATH" >> /root/.bashrc
		#立即使修改的环境变量生效
		source /root/.bashrc
fi

######################################################
#5、initializ the mysql server
######################################################
echo "`show_time`====start to initializ mysql..."
cd "$MYSQL_PATH"
./scripts/mysql_install_db --user=root --defaults-file=$MY_CNF_FILE
INIT_RESULT=$?
if [ $INIT_RESULT != 0 ] ; then
	echo "Error:Failed to initinalize mysql!"
	exit 1
fi
echo "`show_time`====end to initializ mysql..."

#####################################################
#6、install mysql service
#####################################################
echo "`show_time`====start to install mysql service..."
MYSQL_SERVICE_NAME=`printf mysql"$INSTALL_PORT"`
#拷贝文件到系统启动目录,并重新命名为MYSQL服务名，服务名格式mysql+端口号，比如mysql3308
\cp -f support-files/mysql.server /etc/init.d/$MYSQL_SERVICE_NAME
MYSQL_DATA_PATH=`printf "$MYSQL_PATH"/data`
#sed替换$MYSQL_SERVICE_NAME文件中的basedir和datadir，并且在启动命令中增加--user=root &选项，用于root启动
#sed替换shell变量时，用单引号括起来；如果替换字符串中含有/，则s///改成使用s###
#sed中：^锚定行的开始，$锚定行的结束，&保存搜索字符用来替换其他字符；如果字符串中有这些字符，需要使用\转义
sed -i 's#^basedir=$#&'$MYSQL_PATH'#g' /etc/init.d/$MYSQL_SERVICE_NAME
sed -i 's#^datadir=$#&'$MYSQL_DATA_PATH'#g' /etc/init.d/$MYSQL_SERVICE_NAME
#不执行my_print_defaults解析，防止使用/etc/my.cnf选项文件
sed -i 's/^parse_server_arguments /#&/g' /etc/init.d/$MYSQL_SERVICE_NAME
#给启动脚本添加--defaults-file参数，注意该参数必须是mysqld_safe命令的第一个参数
sed -i 's#mysqld_safe #&--defaults-file='$MY_CNF_FILE' #g' /etc/init.d/$MYSQL_SERVICE_NAME
#mysqld_safe指定root用户执行
sed -i 's/other_args /&--user=root \&/g' /etc/init.d/$MYSQL_SERVICE_NAME
#赋予/etc/init.d/mysql3308执行权限
chmod +x /etc/init.d/$MYSQL_SERVICE_NAME
#向系统添加服务$MYSQL_SERVICE_NAME，之后可以使用service $MYSQL_SERVICE_NAME start/stop命令来启动服务
chkconfig --add $MYSQL_SERVICE_NAME
INSTALL_S_RESULT=$?
if [ $INSTALL_S_RESULT != 0 ] ; then
    if [ -f "/etc/SuSE-release" ]; then
        RECHECK_RESULT=`chkconfig --add $MYSQL_SERVICE_NAME`
        if [ `echo $RECHECK_RESULT | grep -i "0:off 1:off 2:off 3:off 4:off 5:off 6:off" | wc -l` -ne 0 ]; then
            echo "`show_time`====SuSE system, re install mysql service"
            `ln -sf /etc/rc.d/$MYSQL_SERVICE_NAME /etc/rc.d/rc2.d/S11$MYSQL_SERVICE_NAME | ln -sf /etc/rc.d/$MYSQL_SERVICE_NAME /etc/rc.d/rc3.d/S11$MYSQL_SERVICE_NAME | 
            ln -sf /etc/rc.d/$MYSQL_SERVICE_NAME /etc/rc.d/rc4.d/S11$MYSQL_SERVICE_NAME | ln -sf /etc/rc.d/$MYSQL_SERVICE_NAME /etc/rc.d/rc5.d/S11$MYSQL_SERVICE_NAME`
            LN_RESULT=$?
            if [ $LN_RESULT != 0 ] ; then
                echo "====Failed to install mysql service..."
                exit 1
            fi    
        else
            echo "Error:Failed to install mysql service!"
            exit 1
        fi
    else 
        echo "Error:Failed to install mysql service!"
        exit 1
    fi
fi
echo "`show_time`====end to install mysql service..."

#####################################################
#7、start the mysql server
#####################################################
#通过mysql服务启动mysql
echo "`show_time`====start to start mysql..."
service $MYSQL_SERVICE_NAME start
START_RESULT=$?
if [ $START_RESULT != 0 ] ; then
	echo "Error:Failed to start mysql!"
	exit 1
fi
echo "`show_time`====end to start mysql..."

#连接mysql，执行授权访问范围和修改root密码等操作
echo "`show_time`====start to modify password..."
$MYSQL_PATH/bin/mysql -u root --protocol=TCP -P $INSTALL_PORT<<EOF
DELETE FROM mysql.user WHERE user = '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'U_tywg_2013' WITH GRANT OPTION;
UPDATE mysql.user set password=password('U_tywg_2013') where user='root';
FLUSH PRIVILEGES;
exit
EOF
SQL_RESULT=$?
if [ $SQL_RESULT != 0 ] ; then
   echo "Error:Failed to modify the user and password!"
	 exit 1
fi
echo "`show_time`====end to modify password..."

echo "`show_time`====Install and start mysql successfully!"
exit 0

2、配置

2.1、测试连接 (5.1版本初始root的密码是空，5.7看2.2章节)

[root@mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73-community 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> exit
Bye

2.2、设置密码

【5.1版本】

[root@mysql]# mysqladmin -u root password "flow123"

【5.7版本】【5.7.6以后的5.7系列版本】

注意：rpm安装结束后会自动初始化，此时如果查找日志中无初始密码，则重新手动初始化。
查找命令

[root@mysql]# grep 'temporary password' /var/log/mysqld.log

手动初始化（如无密码）

[root@mysql]# mysqld --initialize
此时如果报错：
[ERROR] --initialize specified but the data directory has files in it. Aborting.

解决方案：
[root@mysql]# rm -rf /var/lib/mysql/*     （仅限于新安装mysql，会删除所有库文件）
[root@mysql]# mysqld --initialize
[root@mysql]# grep 'temporary password' /var/log/mysqld.log
[Note] A temporary password is generated for root@localhost: sipy&NC*b9=l
此时已随机生成初始密码！
还需修改初始化的数据库文件属组：
[root@mysql]# chown -R mysql:mysql /var/lib/mysql

启动数据库

[root@mysql]# service mysqld start
[root@mysql]# mysql -uroot -p"sipy&NC*b9=l"
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
修改密码：登陆后强制修改密码
mysql> alter user 'root'@'localhost' identified by 'flow123';      

报错socket文件不存在

[root@mysql]# mysql -uroot -p"DisiVm#HB4uM"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

[解决方案]
[root@mysql]# service mysqld restart

[如报错/tmp/mysql.sock不存在]
[root@mysql]# mysql -uroot -p"DisiVm#HB4uM" --socket=/var/lib/mysql/mysql.sock [临时]
[root@mysql]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock [永久]

修改密码报错解决

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 

解决方法：
mysql> set global validate_password_policy=0;
validate_password_length(密码长度)参数默认为8，我们修改为1
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

完成之后再次执行修改密码语句即可成功
mysql> alter user 'root'@'localhost' identified by 'flow123';

2.3、远程访问

mysql修改root 可以远程访问，两种方式
1.通过授权命令
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'flow123' WITH GRANT OPTION;
刷新权限
mysql> flush privileges;
此操作会在user表插入一条新的记录。

2.直接改表
mysql> UPDATE user SET Host='%' WHERE User='root' AND Host='localhost' LIMIT 1;
刷新权限
mysql> flush privileges;

2.4、客户端登录

[root@mysql]# mysql -uroot -pjetflow123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.72-community 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              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

2.5、拷贝配置

官方rpm包安装的MySQL默认没有/etc/my.cnf。
为什么没有这个文件而MySQL却也能正常启动和使用，这个有两说法：
1、my.cnf只是MySQL启动时的一个参数文件，可以没有它，这时MySQL会用内置的默认参数启动；
2、MySQL在启动时自动使用/usr/share/mysql/my-medium.cnf文件，这种说法仅限于rpm包安装的MySQL。
解决方法很简单，只需复制/usr/share/mysql/my-medium.cnf文件到/etc目录，并改名为my.cnf即可：

[root@mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

配置模板：

[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 128
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 8M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 8M
character_set_server=utf8
max_connections=300
lower_case_table_names = 1
max_connect_errors = 100000
event_scheduler=ON
expire_logs_days=3
innodb_file_format = barracuda
innodb_file_per_table = true
innodb_large_prefix = true
log-bin=mysql-bin
binlog_format=mixed
server-id       = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
default-character-set = utf8
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

2.6、创建证书 （5.1版本实验不可用）

创建路径
# mkdir -p /var/lib/mysql/certs;cd /var/lib/mysql/certs
1、生成CA证书
# openssl genrsa 2048 > ca-key.pem
# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
2、服务器证书生成,CA证书签名
# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
# openssl rsa -in server-key.pem -out server-key.pem
# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
3、客户端证书生成,CA证书签名
# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
  RSA Key写入
# openssl rsa -in client-key.pem -out client-key.pem
  CA证书给客户端证书签名
# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
4、验证证书
用ca证书、服务器证书、客户端证书
# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
授权
# chown -R mysql:mysql /var/lib/mysql/certs
更改权限
# chmod 644 client-key.pem server-key.pem ca-key.pem
客户端证书导出
# sz ca.pem
# sz client-cert.pem
# sz client-key.pem
查看Mysql SSL状态
mysql> show global variables like '%ssl%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | YES                                  |
| ssl_ca        | /var/lib/mysql/certs/ca.pem          |
| ssl_capath    |                                      |
| ssl_cert      | /var/lib/mysql/certs/server-cert.pem |
| ssl_cipher    |                                      |
| ssl_key       | /var/lib/mysql/certs/server-key.pem  |
+---------------+--------------------------------------+
7 rows in set (0.00 sec)

mysql> \s;
--------------
SSL:   Cipher in use is DHE-RSA-AES256-SHA

增加/etc/my.cnf配置：
[mysqld]
ssl-ca=/var/lib/mysql/certs/ca.pem
ssl-cert=/var/lib/mysql/certs/server-cert.pem
ssl-key=/var/lib/mysql/certs/server-key.pem

[client]
ssl-ca=/var/lib/mysql/certs/ca.pem
ssl-cert=/var/lib/mysql/certs/client-cert.pem
ssl-key=/var/lib/mysql/certs/client-key.pem

使用我的主机的MySQLWorkbench GUI进行测试。

以下是我的配置 - 设置参数。

配置连接MySQLWorkbench

SSL配置 - 设置SSL。

并在SSL客户端证书文件的连接结果之下。

MySQLWorkbench与SSL连接

3、故障解决

3.1 SSL

错误：
SSL error: Unable to get certificate from '/var/lib/mysql/server-cert.pem'
190404 10:57:52 [Warning] Failed to setup SSL
190404 10:57:52 [Warning] SSL error: Unable to get certificate
解决：
创建ssl证书

3.2 Can’t create PID file

错误：
190404 10:57:52 [ERROR] /usr/sbin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)
190404 10:57:52 [ERROR] Can't start server: can't create PID file: No such file or directory
190404 10:57:52 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
解决：
手动创建/var/run/mysqld目录

3.3 Table ‘mysql.plugin’ doesn’t exist

错误：
160714 14:51:30 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist 160714 14:51:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
解决：
原因：
table ‘mysql.host’不存在的原因是因为新安装的mysql服务后，一般需要执行数据库初始化操作 ，从而生成与权限相关的表，执行命令如下：
# /usr/bin/mysql_install_db --user=mysql
注：以上命令中的mysql_install_db与你安装的mysql服务位置有关，如果不知道在哪，可以使用find / -name mysql_install_db找到其位置，然后执行上面的命令。

FAQ

【故障】：Mysql 启动报错 “mysqld.service holdoff time over, scheduling restart”

【解决】：# chown mysql:mysql -R /var/lib/mysql
                 # service mysqld restart

【故障】： yum install -y cyrus-sasl-sql.x86_64
报错需要：libmysqlclient.so.18(libmysqlclient_18)(64bit)

【解决】：rpm -ivh /home/mysql/mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm
                 yum install -y cyrus-sasl-sql.x86_64