MySQL · 安装

发表于 | 更新于 | 分类于 | 阅读次数：

MySQL 5.7

  • 创建用户
1
2
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
  • 下载二进制包
1
2
3
wget http://devops-files.oss-cn-qingdao.aliyuncs.com/mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
ln -s /usr/local/mysql-5.7.23-linux-glibc2.12-x86_64 /usr/local/mysql
  • 配置文件 my.cnf
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
[client]
port                      = 3306
socket                    = /data/mysql/mysql.sock

[mysqld]
port                      = 3306
socket                    = /data/mysql/mysql.sock
pid_file                  = /data/mysql/mysql.pid
basedir                   = /usr/local/mysql
datadir                   = /data/mysql
character_set_server      = utf8

# LOG
slow_query_log            = 1
long_query_time           = 3
log_error                 = /data/mysql/error.log
slow_query_log_file       = /data/mysql/slow.log
log_queries_not_using_indexes  = 1
sync_binlog                    = 0
innodb_flush_log_at_trx_commit = 0

# INNODB
innodb_buffer_pool_size   = 10240M
innodb_buffer_pool_instances = 4
innodb_data_home_dir      = /data/mysql
innodb_log_file_size      = 1G
innodb_data_file_path     = ibdata1:1G:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

# OTHER
max_connections           = 3000
back_log                  = 1024
interactive_timeout       = 600
wait_timeout              = 600
lower_case_table_names    = 1
log_timestamps            = SYSTEM
max_allowed_packet        = 32M
sort_buffer_size          = 4M
join_buffer_size          = 4M
tmp_table_size            = 32M
explicit_defaults_for_timestamp = 1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
skip-ssl
skip-name-resolve

# REPLICATION
server-id                 = 3306100
log-bin                   = /data/mysql/mysql-bin
max_binlog_size           = 1G
binlog-format             = row
expire_logs_days          = 7

[mysqldump]
quick
max_allowed_packet        = 32M
  • 创建数据目录
1
2
3
mkdir -p /data/mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /data/mysql
  • 初始化 & 启动
1
2
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql 2>&1> /dev/null &
  • 开启 3306 端口
1
2
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
  • 设置开机自启动
1
2
3
4
5
6
7
8
9
cd /usr/local/mysql/bin
cp mysql mysqladmin mysqlbinlog mysqld mysqld_safe mysqldump /usr/bin/

vi /usr/local/mysql/support-files/mysql.server # mysqld_pid_file_path=/data/mysql/mysql.pid
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
chkconfig --list
  • 设置用户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ cat /data/mysql/error.log | grep password

# root
mysql> SET PASSWORD = 'xx';
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

# sre
mysql> CREATE USER 'sre'@'%' IDENTIFIED BY 'xx';
mysql> GRANT all privileges on *.* to 'sre'@'%' WITH GRANT OPTION;

# hdm
mysql> CREATE USER 'hdm'@'%' IDENTIFIED BY 'xx';
mysql> GRANT SELECT, SHOW DATABASES, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'hdm'@'%';

mysql> flush privileges;
mysql> select user,host,authentication_string from mysql.user;

MySQL 5.6

  • 初始化
1
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --defaults-file=/etc/my.cnf --user=mysql
  • 启动
1
$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql 2>&1 > /dev/null &
  • 设置用户
1
2
3
4
5
mysql
mysql> update mysql.user set password=password('xx') where user='root';
mysql> delete from mysql.user where user!='root' or host!='localhost';
mysql> flush privileges;
mysql> select user,host,password from mysql.user;

配置主从

  • 创建用户
1
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'x.x.x.%' IDENTIFIED BY 'xx';
  • 修改从库 my.cnf 文件
1
2
3
4
5
server_id         = 3306101
log_bin           = mysql-bin
relay_log         = relay-log
read_only         = 1
log_slave_updates = 1
  • 传输数据至从库
  • 配置复制
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
# 主库查看日志和位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     3778 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 从库配置复制
CHANGE MASTER TO
MASTER_HOST='x.x.x.x',
MASTER_USER='repl',
MASTER_PASSWORD='xx',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=3778;

# 验证
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.138.98.179
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 3778
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 1621
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...

启停

停止

  • 使用 init.d，mysqld 脚本来自于 /usr/local/mysql/support-files/mysql.server
1
$ /etc/init.d/mysqld stop
  • 使用 mysqladmin
1
$ mysqladmin -u'xx' -p'xx' shutdown
  • 使用 service
1
2
$ systemctl stop mysqld
$ service mysqld stop

启动

  • 使用 init.d
1
$ /etc/init.d/mysqld start
  • 使用 mysqld_safe
1
$ mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
  • 使用 service
1
2
$ systemctl start mysqld
$ service mysqld start

工具命令

  • mysql：MySQL 客户端访问管理工具。

  • mysqld：这个可执行文件就代表着 MySQL 服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。

  • mysqld 启动配置文件优先级：

  • 1
    /etc/my.cnf -> /etc/mysql/my.cnf -> /usr/etc/my.cnf -> ~/.my.cnf

  • mysqld_safe：是一个启动脚本，它会间接的调用 mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。使用 mysqld_safe 启动 mysqld 时，mysqld_safe 可以使用参数选项，此时可以使用其他配置文件，相当于 mysqld_safe 把参数传递给 mysqld。

  • mysql.server：也是一个启动脚本，主要作用就是为了方便启动和关闭 mysqld 服务，只能使用默认的 /etc/my.cnf 配置文件，mysql.server 调用 mysqld_safe，然后 mysql_safe 调用了 mysqld。

FAQ

现象：使用 mysqld_safe 启动失败，启动错误日志未记录 error.log，报错如下：

1
Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/COSMOPlat-WMS-App01.pid)

原因：经检查发现，数据库安装时是以 haieradmin 用户启动，默认使用 mysql 用户启动，启动失败。

解决：使用 haieradmin 用户启动成功。

安装脚本 mysql_install.sh

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
#!/bin/bash

# ###########################################################################
# MySQL安装包名字为mysql-5.7.23-el7-x86_64.tar.gz，位置放在/usr/local/
# ###########################################################################

cmd=$2    # start or stop or status or install
inst=$4

service_startup_timeout=30

log_failure_msg() {
    echo " ERROR! $@"
}

log_success_msg() {
    echo " SUCCESS! $@"
}

case `echo "testing\c"`,`echo -n testing` in
    *c*,-n*) echo_n=   echo_c=     ;;
    *c*,*)   echo_n=-n echo_c=     ;;
    *)       echo_n=   echo_c='\c' ;;
esac

wait_for_pid () {
  verb="$1"           # created | removed
  pid="$2"            # process ID of the program operating on the pid-file
  pid_file_path="$3"  # path to the PID file.

  i=0
  avoid_race_condition="by checking again"

  while test $i -ne $service_startup_timeout ; do

    case "$verb" in
      'created')
        # wait for a PID-file to pop into existence.
        test -s "$pid_file_path" && i='' && break
        ;;
      'removed')
        # wait for this PID-file to disappear
        test ! -s "$pid_file_path" && i='' && break
        ;;
      *)
        echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"
        exit 1
        ;;
    esac

    if test -n "$pid"; then
      if kill -0 "$pid" 2>/dev/null; then
        :
      else
        if test -n "$avoid_race_condition"; then
          avoid_race_condition=""
          continue
        fi

        log_failure_msg "The server quit without updating PID file ($pid_file_path)."
        return 1
      fi
    fi

    echo $echo_n ".$echo_c"
    i=`expr $i + 1`
    sleep 1

  done

  if test -z "$i" ; then
    log_success_msg
    return 0
  else
    log_failure_msg
    return 1
  fi
}

# ############################################################################################
# 支持MySQL版本：5.6/5.7，CentOS 6系统建议安装MySQL 5.6版本；CentOS 7系统建议安装MySQL 5.7版本
# 支持MySQL模式：单机/主从
# #############################################################################################

install_mysql() {

    echo $echo_n "Installing MySQL..."

    local basedir="/usr/local/mysql"
    local datadir="/data/mysql"
    local defaults_file="/etc/my.cnf"
    local sock_file="$datadir/mysql.sock"
    local pid_file="$datadir/mysql.pid"
    local array=(${inst//@/ })
    local port=${array[1]}

    # 创建MySQL用户，指定MySQL所在的用户组
    groupadd mysql >/dev/null 2>&1
    useradd -g mysql mysql -s /sbin/nologin >/dev/null 2>&1

    # 解压MySQL软件包，并对mysql目录授权
    cd /usr/local
    tar -zxvf mysql-5.7.23-el7-x86_64.tar.gz >/dev/null 2>&1
    ln -s mysql-5.7.23-el7-x86_64 mysql
    chown mysql:mysql -R mysql
    chown mysql:mysql -R mysql-5.7.23-el7-x86_64

    # 创建数据目录
    mkdir -p $datadir
    chown mysql:mysql -R $datadir

    # 初始化数据库
    cd /usr/local/mysql/bin
    ./mysqld --defaults-file="$defaults_file" --basedir="$basedir" --datadir="$datadir" --user=mysql --initialize-insecure >/dev/null 2>&1

    # 启动数据库
    ./mysqld_safe --defaults-file="$defaults_file" --pid-file="$pid_file" --user=mysql >/dev/null &
    wait_for_pid created "$!" "$pid_file"; return_value=$?

    # 打开防火墙端口
    firewall-cmd --permanent --zone=public --add-port=3306/tcp >/dev/null 2>&1
    firewall-cmd --reload >/dev/null 2>&1

    cp mysql* /usr/bin >/dev/null 2>&1
    # mv /usr/local/mysql_tool /usr/bin >/dev/null 2>&1
    mysql -S $sock_file mysql -e "SET PASSWORD = 'xx';ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;flush privileges;"
    mysql -S $sock_file mysql -e "SET PASSWORD = 'xx';ALTER USER 'hdm'@'%' PASSWORD EXPIRE NEVER;flush privileges;"
    mysql -S $sock_file mysql -e "SET PASSWORD = 'xx';ALTER USER 'sre'@'%' PASSWORD EXPIRE NEVER;flush privileges;"
    exit $return_value
}

case "$cmd" in
    'install') install_mysql ;;
    'uninstall') mysql_uninstall ;;
    'start') mysql_start ;;
    'stop') mysql_stop ;;
    'restart') mysql_restart ;;
    'config') mysql_config ;;
    'monitor') mysql_monitor ;;
*)
    # usage
    basename=`basename "$0"`
    echo "Usage: $basename -cmd {start|stop|restart|install|uninstall|status|monitor} -inst {instance name} [ MySQL tool options ]"
    exit 1
    ;;
esac
exit 0