database►Mysql►mysql standard

mysql standard

1. 创建mysql用户

1
[root@10 app]# useradd -s /sbin/nologin mysql

2. 编译安装mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@192 ~]# wget http://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.35.tar.gz
[root@192 ~]# tar xzf mysql-5.6.35.tar.gz
[root@192 ~]# cd mysql-5.6.35
[root@192 mysql-5.6.35]# cmake \
> -DCMAKE_INSTALL_PREFIX=/mnt/app/mysql \
> -DWITH_MYISAM_STORAGE_ENGINE=1 \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_PARTITION_STORAGE_ENGINE=1 \
> -DEXTRA_CHARSETS=all \
> -DDEFAULT_CHARSET=utf8 \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DENABLE_DOWNLOADS=1
[root@192 mysql-5.6.35]# make
[root@192 mysql-5.6.35]# make install

cmake \
-DCMAKE_INSTALL_PREFIX=/mnt/app/mysql \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLE_DOWNLOADS=1
make
make install
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
cmake ./
# -DCMAKE_INSTALL_PREFIX=/usr/local/mysql          \    #安装路径
# -DMYSQL_DATADIR=/usr/local/mysql/data            \    #数据文件存放位置
# -DSYSCONFDIR=/etc                                \    #my.cnf路径
# -DWITH_MYISAM_STORAGE_ENGINE=1                   \    #支持MyIASM引擎
# -DWITH_INNOBASE_STORAGE_ENGINE=1                 \    #支持InnoDB引擎
# -DWITH_MEMORY_STORAGE_ENGINE=1                   \    #支持Memory引擎
# -DWITH_READLINE=1                                \    #快捷键功能(我没用过)
# -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock               \    #连接数据库socket路径
# -DMYSQL_TCP_PORT=3306                            \    #端口
# -DENABLED_LOCAL_INFILE=1                         \    #允许从本地导入数据
# -DWITH_PARTITION_STORAGE_ENGINE=1                \    #安装支持数据库分区
# -DEXTRA_CHARSETS=all                             \    #安装所有的字符集
# -DDEFAULT_CHARSET=utf8                           \    #默认字符
# -DDEFAULT_COLLATION=utf8_general_ci

3. 设置环境变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@10 mysql-5.6.35]# echo 'export MYSQL_BASE=/mnt/app/mysql' |tee /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# echo 'export MYSQL_BIN=$MYSQL_BASE/bin' |tee -a /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# echo 'export PATH=$MYSQL_BIN:$PATH'     |tee -a /etc/profile.d/mysql.sh
[root@10 mysql-5.6.35]# source /etc/profile

[root@10 mysql-5.6.35]# echo '/mnt/app/mysql/lib' | tee /etc/ld.so.conf.d/mysql.conf
[root@10 mysql-5.6.35]# ldconfig

echo 'export MYSQL_BASE=/mnt/app/mysql' |tee /etc/profile.d/mysql.sh
echo 'export MYSQL_BIN=$MYSQL_BASE/bin' |tee -a /etc/profile.d/mysql.sh
echo 'export PATH=$MYSQL_BIN:$PATH'     |tee -a /etc/profile.d/mysql.sh
source /etc/profile

echo '/mnt/app/mysql/lib' | tee /etc/ld.so.conf.d/mysql.conf
ldconfig

4. 创建数据和日志存放目录

1
2
3
4
5
6
7
[root@10 mysql-5.6.35]# mkdir -p /mnt/{data,log}/mysql
[root@10 mysql-5.6.35]# touch /mnt/log/mysql/mysqld.log
[root@10 mysql-5.6.35]# chown -R mysql.mysql /mnt/{data,log}/mysql

mkdir -p /mnt/{data,log}/mysql
touch /mnt/log/mysql/mysqld.log
chown -R mysql.mysql /mnt/{data,log}/mysql

5. 初始化数据库

1
2
3
4
5
[root@10 mysql-5.6.35]# chmod +x scripts/*
[root@10 mysql-5.6.35]# ./scripts/mysql_install_db --basedir=/mnt/app/mysql/ --datadir=/mnt/data/mysql --user=mysql

chmod +x scripts/*
./scripts/mysql_install_db --basedir=/mnt/app/mysql/ --datadir=/mnt/data/mysql --user=mysql

6. 编辑配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[root@10 mysql-5.6.35]# mkdir -p /mnt/app/mysql/conf
[root@10 mysql-5.6.35]# cat > /mnt/app/mysql/conf/my.cnf <<EOF
[mysqld]
user    = mysql
port    = 3306
basedir = /mnt/app/mysql
datadir = /mnt/data/mysql
socket  = /mnt/data/mysql/mysql.sock
skip-name-resolve
default-storage-engine = InnoDB
innodb_file_per_table  = 1
character_set_server   = utf8
max_connections        = 5000
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/mnt/log/mysql/mysqld.log
pid-file =/mnt/data/mysql/mysqld.pid

[client]
default-character-set=utf8
socket=/mnt/data/mysql/mysql.sock

[mysql]
default-character-set=utf8
socket=/mnt/data/mysql/mysql.sock
EOF

7. 启动,关闭,连接服务

1
2
3
[root@10 mysql-5.6.35]# /mnt/app/mysql/bin/mysqld_safe --defaults-file=/mnt/app/mysql/conf/my.cnf &
[root@10 mysql-5.6.35]# /mnt/app/mysql/bin/mysqladmin -S /mnt/data/mysql/mysql.sock shutdown   
[root@10 mysql-5.6.35]# /mnt/app/mysql/bin/mysql -S /mnt/data/mysql/mysql.sock

8. mysql启动脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
[root@10 mysql-5.6.35]# cat > /etc/init.d/mysql <<EOF
#!/bin/bash

if [ $# != 1 ];then
  echo "Please input:
/etc/init.d/$0 start
/etc/init.d/$0 stop
/etc/init.d/$0 status
"
  exit 0
fi

STATUS=$1
ROLE='mysql'
SQLAPP="/mnt/app/${ROLE}/bin/mysqld_safe"
SQLSHT="/mnt/app/${ROLE}/bin/mysqladmin"
SOCKET="/mnt/data/${ROLE}/mysql.sock"
COFIG="/mnt/app/${ROLE}/conf/my.cnf"


if [ $STATUS == "start" ];then
  ${SQLAPP} --defaults-file=${COFIG} &
  if [ $? == 0 ];then
    echo "$ROLE start success."
  else
    echo "$ROLE start fail."
  fi
fi

if [ $STATUS == "stop" ];then
  ${SQLSHT} -S ${SOCKET} shutdown
  if [ $? == 0 ];then
    echo "$ROLE stoped success."
  else
    echo "$ROLE stoped fail."
  fi
fi

if [ $STATUS == "kill" ];then
  PID=$(ps -ef|grep -w ${ROLE}|grep -v grep|grep -v bash|awk '{print $2}'|tr '\n' ' ')
  kill -9 $PID
  if [ $? == 0 ];then
    echo "$ROLE killed success."
  else
    echo "$ROLE killed fail."
  fi
fi


if [ $STATUS == "status" ];then
  NUM=$(ps -ef|grep -w ${ROLE}|grep root|grep -v grep|grep -v bash|awk '{print $2}'|wc -l)

  if [ $NUM == 0 ];then
    echo "$ROLE is stopped."
    exit 0
  fi

  if [ $NUM != 1 ];then
    echo "Please check $ROLE, There are $NUM processes."
  fi

  PID=$(ps -ef|grep -w ${ROLE}|grep -v grep|grep -v bash|awk '{print $2}'|tr '\n' ' ')
  echo "$ROLE is running. PID: $PID"
fi
EOF

[root@10 mysql-5.6.35]# chmod +x /etc/init.d/mysql
[root@10 mysql-5.6.35]# /etc/init.d/mysql start

[root@10 mysql-5.6.35]# echo '/etc/init.d/mysql start' |tee -a /etc/rc.local
[root@10 mysql-5.6.35]# chmod +x /etc/rc.d/rc.local