MySQL 5.6离线二进制部署
1、下载压缩包并上传至liunx
下载 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz压缩包
百度云链接：https://pan.baidu.com/s/14I_i-i7D1S5rIfSmbeNpVg
提取码：fktz
使用rz命令上传至/usr/local目录。
[root@localhost local]# pwd
/usr/local
[root@localhost local]# ll mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 root root 311771412 11月 21 09:39 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
2、检查当前系统是否有安装mysql其他版本
[root@localhost local]# ps -ef|grep mysqld #检查是否安装mysql
root 2493 2423 0 19:48 pts/3 00:00:00 grep mysqld
[root@localhost local]# rpm -qa |grep -i mysql #卸载已有的mysql，即使没有安装mysql也可以执行该命令
3、创建mysql专用的用户及用户组
mysql服务--》mysql用户去维护
[root@localhost local]# groupadd -g 101 dba #创建名为 dba的用户主组
[root@localhost local]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin #创建用户id=514 主组=dba 附加组=root 家目录= /usr/local/mysql（注：不指定家目录系统会默认家目录为home目录），用户名=mysqladmin
新增的mysqladmin会提示：
[root@localhost local]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin
useradd：警告：此主目录已经存在。
不从 skel 目录里向其中复制任何文件。
出现这个提示表示这个创建的用户没有样式，切换用户会显示如下状态。
[root@localhost local]# su - mysqladmin
-bash-4.2$
解决方法：手工将/etc/skel/目录下的文件拷贝到mysqladmin的家目录下。
[root@localhost local]# cp /etc/skel/.* /usr/local/mysql
cp: 略过目录"/etc/skel/."
cp: 略过目录"/etc/skel/.."
[root@localhost ~]# id mysqladmin #查看用户信息
uid=514(mysqladmin) gid=101(dba) 组=101(dba),0(root)
4、解压mysql，创建软链接及修改软链接所属用户组和授权。
tar -xvzf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
ln -s mysql-5.6.23-linux-glibc2.5-x86_64 mysql #在当前目录创建名为“mysql”的软链接
修改软链接和mysql-5.6.23-linux-glibc2.5-x86_64的所属用户和组，默认是所属root用户及用户组，需要改为mysqladmin用户dba组。
chown -R mysqladmin:dba /usr/local/mysql
chown -R mysqladmin:dba /usr/local/mysql/*
chown -R mysqladmin:dba /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64
修改软链接和mysql-5.6.23-linux-glibc2.5-x86_64的权限
chmod -R 755 /usr/local/mysql #修改mysql文件夹的权限为755
chmod -R 755 /usr/local/mysql/* #修改mysql文件夹中所有文件的权限为755
chmod -R 755 /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64 #修改原文件夹的权限为755
5、修改mysql配置文件及权限
my.cnf文件所在的文件目录
#defualt start: /etc/my.cnf->/etc/mysql/my.cnf->SYSCONFDIR/my.cnf->$MYSQL_HOME/my.cnf-> --defaults-extra-file->~/my.cnf
[root@localhost skel]# cd /etc/
[root@localhost etc]# ll my.cnf
-rw-r-----. 1 mysqladmin dba 2218 11月 21 11:54 my.cnf
建议修改my.cnf文件时，先做备份
[root@localhost etc]# cp my.cnf my.cnf2019
使用vi命令编辑my.cnf文件
[root@localhost etc]# vi my.cnf
按dG清空my.cnf文件配置信息，将下列参数复制粘贴到my.cnf文件（注意：在insert状态下粘贴）
[client]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
skip-external-locking
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
query_cache_size= 32M
max_allowed_packet = 16M
myisam_sort_buffer_size=128M
tmp_table_size=32M
table_open_cache = 512
thread_cache_size = 8
wait_timeout = 86400
interactive_timeout = 86400
max_connections = 600
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 32
#isolation level and default engine
default-storage-engine = INNODB
transaction-isolation = READ-COMMITTED
server-id = 1
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/hostname.pid
#open performance schema
log-warnings
sysdate-is-now
binlog_format = MIXED
log_bin_trust_function_creators=1
log-error = /usr/local/mysql/data/hostname.err
log-bin=/usr/local/mysql/arch/mysql-bin
#other logs
#general_log =1
#general_log_file = /usr/local/mysql/data/general_log.err
#slow_query_log=1
#slow_query_log_file=/usr/local/mysql/data/slow_log.err
#for replication slave
#log-slave-updates
#sync_binlog = 1
#for innodb options
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:500M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/arch
innodb_log_files_in_group = 2
innodb_log_file_size = 200M
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 50M
innodb_log_buffer_size = 16M
innodb_lock_wait_timeout = 100
#innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 1
innodb_locks_unsafe_for_binlog=1
#innodb io features: add for mysql5.5.8
performance_schema
innodb_read_io_threads=4
innodb-write-io-threads=4
innodb-io-capacity=200
#purge threads change default(0) to 1 for purge
innodb_purge_threads=1
innodb_use_native_aio=on
#case-sensitive file names and separate tablespace
innodb_file_per_table = 1
lower_case_table_names=1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
修改my.cnf文件权限
[root@localhost etc]# chown mysqladmin:dba /etc/my.cnf
[root@localhost etc]# chmod 640 /etc/my.cnf
[root@localhost etc]# ll my.cnf
-rw-r-----. 1 mysqladmin dba 2218 11月 21 11:54 my.cnf #所属用户mysqladmin 有读写权限 所属dba组只读权限。
6、切换用户创建归档文件，运行初始化脚本
[root@localhost etc]# su - mysqladmin
[mysqladmin@localhost ~]$ mkdir arch
[mysqladmin@localhost ~]$ scripts/mysql_install_db \
--user=mysqladmin \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
[mysqladmin@localhost ~]$ scripts/mysql_install_db
-bash: scripts/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录（提示缺失perl 依赖组件）
mysql_install_db是一个默认放在.../mysql/scripts的一个初始化脚本。
该脚本可以在任何装有perl的操作系统上被使用，在5.6.8之前的版本，该脚本是一个shell脚本，并只能在unix平台上使用。
5.7.6以后的版本，该脚本也将被移除。
官方对它的介绍是：
Initialize MySQL Data Directory
安装完mysql server时，一般要执行这个脚本对数据库初始化。
运行这个脚本，会初始化mysql的data目录，并且创建那些系统表。
同样也会初始化系统表空间并且关联innodb表与数据结构。
[mysqladmin@localhost ~]$ scripts/mysql_install_db \
> --user=mysqladmin \
> --basedir=/usr/local/mysql \
> --datadir=/usr/local/mysql/data
FATAL ERROR: please install the following Perl modules before executing scripts/mysql_install_db:
Data::Dumper （提示缺失 autoconf依赖组件）
（注意：执行以上命令会出现提示缺失的依赖组件，请根据提示切换到root用户安装依赖组件）
yum install -y perl
yum install -y autoconf
yum install -y libaio
将服务文件拷贝到init.d下，并重命名为mysql
[mysqladmin@localhost ~]$ su - root #切换到root用户
上一次登录：四 11月 21 13:08:32 CST 2019从 219.135.174.166pts/4 上
[root@localhost ~]#
[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]#
[root@localhost mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql ##将服务文件拷贝到init.d下，并重命名为mysql
[root@localhost mysql]# chmod +x /etc/rc.d/init.d/mysql ##赋予可执行权限
[root@localhost mysql]# chkconfig --del mysql #删除mysql服务，如果之前部署过mysql，请先删除服务再重新添加
[root@localhost mysql]# chkconfig --add mysql ##添加服务
[root@localhost mysql]# chkconfig --level 345 mysql on ##设置mysql服务开机自启动
通过脚本设置开机自启动，在rc.local文件添加 su - mysqladmin -c "/etc/init.d/mysql start --federated"
##大概意思：启动后切换到mysqladmin用户，同时执行/etc/init.d/mysql start --federated 命令行##
[root@localhost mysql]# vi /etc/rc.local
#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.
touch /var/lock/subsys/local
su - mysqladmin -c "/etc/init.d/mysql start --federated"
7、启动mysql并查看进程和侦听
[root@localhost ~]# su - mysqladmin
上一次登录：四 11月 21 15:26:38 CST 2019pts/4 上
[mysqladmin@localhost ~]$ rm -rf my.cnf my-new.cnf
[mysqladmin@localhost ~]$ service mysql start #启动mysql服务
Starting MySQL SUCCESS!
[mysqladmin@localhost ~]$ service mysql status #查看mysql状态
SUCCESS! MySQL running (79648)
[mysqladmin@localhost ~]$ mysql -uroot -p #登录mysql，默认密码为空，提示输入密码直接按回车
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
查看进程
[mysqladmin@localhost ~]$ ps -ef |grep mysqld
mysqlad+ 40858 85885 0 15:13 pts/4 00:00:00 grep --color=auto mysqld
mysqlad+ 79001 78701 0 12:07 pts/3 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysqlad+ 79648 79001 0 12:07 pts/3 00:00:05 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/hostname.err --pid-file=/usr/local/mysql/data/hostname.pid --socket=/usr/local/mysql/data/mysql.sock --port=3306
[mysqladmin@localhost ~]$ netstat -tulnp | grep mysql
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp6 0 0 :::3306 :::* LISTEN 79648/mysqld
8、登录mysql并设置密码
[mysqladmin@localhost ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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 databeses;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databeses' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql #切换到mysql数据库
Database changed
mysql> update user set password=password('centos') where user='root'; #设置密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select user,password,host from user;
+-------+--------------------------------------------------------------------------+-----------------------+
| user | password | host |
+-------+---------------------------------------------------------------------------+-----------------------+
| root | *128977E278358FF80A246B5046F51043A2B1FCED | localhost |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | localhost.localdomain |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | 127.0.0.1 |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | ::1 |
| | | localhost |
| | | localhost.localdomain |
+------+-------------------------------------------+-----------------------+
6 rows in set (0.00 sec)
mysql> delete from user where user=''; #删除为空的用户
Query OK, 2 rows affected (0.00 sec)
mysql> select user,password,host from user;
+------+-----------------------------------------------------------------------+-----------------------+
| user | password | host |
+------+-----------------------------------------------------------------------+-----------------------+
| root | *128977E278358FF80A246B5046F51043A2B1FCED | localhost |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | localhost.localdomain |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | 127.0.0.1 |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | ::1 |
+------+-----------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)
mysql> flush privileges; #刷新权限
Query OK, 0 rows affected (0.00 sec)
mysql> exit
9、配置环境变量
[mysqladmin@localhost ~]$ vi .bashrc #个人变量文件
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1 #加上这个配置会有惊喜哦。
[mysqladmin@localhost ~]$ source .bashrc #生效环境变量
localhost.localdomain:mysqladmin:/usr/local/mysql:> #这个显示的格式就是配置文件中的惊喜。
至此我们的mysql部署已经完成。
mysql部署出现问题的解决方法：
a、检查执行的目录和用户
b、重新部署
1.1删除归档和数据文件夹：rm -rf /usr/local/mysql/arch/* rm -rf /usr/local/mysql/data
arch文件夹存放的是binlog，里面的文件有按时间自动删除机制。 data文件夹存放数据文件
1.2重新执行以下命令：
scripts/mysql_install_db \
--user=mysqladmin \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
