Linux环境下安装部署MySQL（编译包）

安装环境：CentOS Linux release 7.3.1611

MySQL版本：5.7.17

一、下载MySQL编译包以及Boost库

---

[root@192 ~]# wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17.tar.gz
[root@192 ~]# wget  https://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

 

二、创建一个MySQL用户组以及账户

---

[root@192 ~]# groupadd mysql
[root@192 ~]# useradd -r -g mysql mysql

 

三、将MySQL安装包解压到指定目录

---

[root@192 ~]#tar xvf mysql-5.7.17.tar.gz -C /usr/local/

 

四、上传Boost安装包到/usr/local/目录下

---

五、安装MySQL相关依赖包

---

[root@192 ~]#yum install -y ncurses-devel

[root@192 ~]#yum install -y bison

[root@192 ~]#yum install -y gcc gcc-c++

 

六、设置预编译参数

---

1.相关编译参数说明
MySQL编译安装常用参数:
DCMAKE_INSTALL_PREFIX mysql的安装路径
DMYSQL_DATADIR 数据库存放目录
DMYSQL_USER mysql的所有者
DMYSQL_TCP_PORT tcp协议使用的端口（默认为3306）
DMYSQL_UNIX_ADDR 连接数据库socket路径
DDEFAULT_CHARSET 默认字符
DDEFALUT_CHARSETS 支持所有字符集(默认为all)
DDEFAULT_COLLATION 默认字符集(默认为Italian，不支持中文)
DWITH_EXTRA_CHARSETS=all 校验字符
DWITH_MYISAM_STORAGE_ENGINE=1 安装MYISAM存储引擎
DWITH_INNOBASE_STORAGE_ENGINE=1 安装 innodb 存储引擎
DWITH_MEMORY_STORAGE_ENGINE=1 安装 memory 存储引擎
DWITH_ARCHIVE_STORAGE_ENGINE=1 安装Archive引擎
DWITH_BLACKHOLE_STORAGE_ENGINE=1 安装BLACKHOLE引擎
DWITH_FEDERATED_STORAGE_ENGINE=1 安装FEDERATED引擎
DWITH_PARTITION_STORAGE_ENGINE=1 安装分区存储引擎
DWITH_READLINE=1 使用绑定的readline
DENABLED_LOCAL_INFILE=1 允许从本地导入数据
DWITH_DEBUG 是否开启debug模式
DWITH_SSL=yes 支持SSL
PS：从MySQL 5.7.5开始Boost库是必需的(-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/)

2.编译安装MySQL

[root@192 mysql]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local \
> -DMYSQL_DATADIR=/data/mysql \
> -DMYSQL_TCP_PORT=3306\
> -DDEFAULT_CHARSET=utf8mb4 \
> -DDEFAULT_COLLATION=utf8mb4_general_ci \
> -DEXTRA_CHARSETS=all \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_MYISAM_STORAGE_ENGINE=1 \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_MEMORY_STORAGE_ENGINE=1 \
> -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
> -DWITH_FEDERATED_STORAGE_ENGINE=1 \
> -DDOWNLOAD_BOOST=1 \
> -DWITH_BOOST=/usr/local

[root@192 mysql]#make && make install

 

PS:如果没有安装cmake,执行yum -y install cmake，如果编译失败rm -rf CMakeCache.txt

七、改变目录所有者 

---

[root@192 mysql]#chown -R mysql.mysql /usr/local/mysql

 

八、注册服务

---

1.注册服务

[root@192 mysql]#cd /usr/local/mysql/support-files

[root@192 support-files]cp mysql.server /etc/rc.d/init.d/mysqld

 

2.指定配置文件

[root@192 support-files]cp my-default.cnf /etc/my.cnf

 

3.检查mysql是否在开机启动中

[root@192 support-files]chkconfig

 

4.设置开机启动

[root@192 support-files]chkconfig mysql on

 

九、编辑配置文件my.cnf

---

[root@192 support-files]vim /etc/my.cnf

[mysqld]
socket = /tmp/mysql.sock

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
#The server ID must be modified
server-id = 100

log_bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates

sync_binlog=1
#setting offset
auto_increment_offset=1
auto_increment_increment=2

#setting log path

log_error = /data/mysql/mysql-error.log
slow_query_log_file = /data/mysql/mysql-slow.log

 

十、初始化数据库

---

[root@192 mysql]ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

[root@192 mysql]ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin

[root@192 mysql]/usr/local/mysql/bin/mysqld --initialize

 

初始化数据库需注意以下两点:

1.保证mysql是关闭的,关闭方法:

(1)service mysql stop

(2)/usr/local/mysql/bin/mysqld stop

(3)/usr/local/mysql/support-files/mysql.server stop

(4)找到mysql进程，执行kill命令

2.清除/usr/local/mysql/data文件下的数据

十一、设置root相关权限以及新建账号

---

1.查看root临时密码

[root@192 mysql]cat /data/mysql/mysrql-error.log

 

2.登陆数据库

[root@192 mysql]mysql -uroot -p

 

3.设置root密码

mysql> SET PASSWORD = PASSWORD('7SlN*2AtWRzR%0mZ');

 

4.新增账号

mysql> grant select,insert,update,delete,create,drop on *.* to admin@"%" identified by '7SlN*2AtWRzR%0mZ';

mysql> flush privileges;

 

此时，编译安装的MySQL数据库就搭建好了，使用编译包安装时，编译过程较长，系统内存要求2G以上。

附：

---

MySQL命令:
①设置密码
SET PASSWORD = PASSWORD(‘your password’);
②查看所有数据库
show databases;
③选择数据库
use mysql
④查看所有数据表
show tables;
⑤查看数据表中的信息
select * from user;
或者
select * from user\G
⑥修改表属性
alter 表名称 modify 列表名称 varchar(20);

一键安装部署脚本:

#!/bin/bash
#copyright
#
#wang_chongsheng@163.com
#
#2017-07-05
#
#MySQL Automatic deployment of script files
echo "==========================================="
echo "            MySQL installation             "
echo "==========================================="

base_dir="/opt/install/src/"
data_dir="/data/mysql/"
tar_dir="${base_dir}mysql-5.7.17/"
ins_dir="${base_dir}mysql/"
mysqld="/etc/rc.d/init.d/mysqld"
db_user="admin"
db_pwd="7SlN*2AtWRzR%0mZ"

#Add user and group
g_info=`grep mysql /etc/group`
u_info=`grep mysql /etc/passwd`
if [ $g_info -ne " " ];then
        echo "The group already exists."
else;then
        groupadd mysql
fi

if [ $u_info -ne " " ]
        echo "The user already exists."
else;then
        useradd -r -g mysql mysql -s /sbin/nologin
fi

#create data catalog
mkdir -p $data_dir

#Installation dependency package
yum install -y ncurses-devel bison gcc gcc-c++ libaio

#download files
dl_dir="${base_dir}mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz"
dl2_dir="${base_dir}boost_1_59_0.tar.gz"
if [ -d $dl_dir ];then
        wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17.tar.gz
else
        echo "$mysql tar file already exists."
fi

if [ -d $dl2_dir ];then
        wget https://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
else
        echo "boost_1_59_0.tar.gz already exist."

#unzip mysql

if [ -d "$ins_dir" ] && [ ! -d "$tar_dir" ] ;then
        echo mysql directory already exist !
elif [ ! -d "$ins_dir" ] && [ -d "$tar_dir" ];then
        mv $tar_dir $ins_dir
        echo Change catalog mysql-5.7.17 to mysql.
elif [ ! -d "$ins_dir" ] && [ ! -d "$tar_dir" ];then
        cd ${base_dir};tar xvf mysql-5.7.17.tar.gz
        mv $tar_dir $ins_dir
        echo  "unzip ...... done."
fi

#cmake compile
cd $ins_dir
sleep 1
cmake . -DCMAKE_INSTALL_PREFIX=$ins_dir \
-DMYSQL_DATADIR=/data/mysql \
-DMYSQL_TCP_PORT=2011 \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci  \
-DEXTRA_CHARSETS=all  \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=$base_dir
sleep 2
make && make install
sleep 1

#Modify the catalog
chown -R mysql.mysql $ins_dir
chown -R mysql.mysql $data_dir
#cd ${ins_dir}support-files


sleep 2
ln -s /opt/sygamer/src/mysql/bin/mysql /usr/bin/mysql
ln -s /opt/sygamer/src/mysql/bin/mysqladmin /usr/bin/mysqladmin
ln -s /opt/sygamer/src/mysql/bin/mysqldump /usr/bin/mysqldump
#Modify my.cnf file
cat > /etc/my.cnf <<EOF
[mysqld]
port = 3306
socket = /tmp/mysql.sock
 
basedir = ${base_dir}
datadir = ${data_dir}
pid-file = ${data_dir}mysql.pid
user = mysql
#The server ID must be modified
server-id = 91
 
log_bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates
 
sync_binlog=1
#setting offset
auto_increment_offset=1
auto_increment_increment=1
 
#setting log path
 
log_error = ${data_dir}mysql-error.log
slow_query_log_file = ${data_dir}mysql-slow.log
EOF

#register service
rm -rf ${data_dir}
killall mysqld
yes|cp ${ins_dir}support-files/mysql.server /etc/rc.d/init.d/mysqld

chmod 755 /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on

#initialize msyql
/opt/sygamer/src/mysql/bin/mysqld --initialize

#建立临时账户文件
tmp_pwd=`grep "A temporary password is generated for root@localhost" /data/mysql/mysql-error.log |awk -F "t: " '{print$2}'`
cat > /tmp/mysqluser << EOF
[client]

user=root

password="${tmp_pwd}"
EOF

#
service mysqld start

#setting  user info
mysql --defaults-file=/tmp/mysqluser --connect-expired-password <<EOF
set password = password('${db_pwd}');
grant all privileges on *.* to '${db_user}'@'%' identified by '${db_pwd}';
flush privileges;
EOF

echo "username:root   password:$db_pwd"
echo "username:$db_user  password:$db_pwd"
rm -rf /tmp/mysqluser