在Linux系统上部署Mysql

评论

在Linux系统上部署Mysql

在Linux上部署Mysql的两种方式：

  • rpm包部署：操作简单，适合学习的场景
  • tar包部署：定制化配置，生产上一般用tar包部署

一、rpm包部署

0.查看机器上是否已经部署

1
2
3
4
[root@hadoop001 mysql]# rpm -qa|grep mysql
mysql-5.1.73-3.el6_5.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-server-5.1.73-3.el6_5.x86_64

此处已经部署，先卸载

1
[root@hadoop001 mysql]# rpm -e --nodeps mysql*

1.用yum安装

1
2
[root@hadoop001 mysql]# yum search mysql
[root@hadoop001 mysql]# yum install -y mysql-server.x86_64 mysql.x86_64

安装完毕根据需要修改配置文件:/etc/my.cnf

#不用yum安装的话，可到官网下载rpm包，然后通过命令rpm -ivh rpm包进行安装，大同小异

2.启动与停止

1
2
[root@hadoop001 mysql]# service mysqld start
[root@hadoop001 mysql]# service mysqld stop

二、tar包部署

1
2
[root@hadoop001 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.23, for linux-glibc2.5 (x86_64) using  EditLine wrapper

我的linux系统上已经部署mysql 5.6.23版本，现在以部署mysql 5.7.11为例再部署一次。

0.前期准备：tar包

官方网站选择需要的版本下载：https://downloads.mysql.com/archives/community/

我们这里用到的是mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz，点击下载：mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz

然后通过rz命令上传至linux系统

1
2
3
4
[root@hadoop001 ~]# cd /usr/local/
[root@hadoop001 local]# rz
[root@hadoop001 local]# ll|grep mysql
-rw-r--r--.  1 root       root 548193637 Nov 21 03:58 mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz

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
[root@hadoop001 local]# tar -xzvf mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz 
[root@hadoop001 local]# ln -s mysql-5.7.11-linux-glibc2.5-x86_64 mysql
[root@hadoop001 local]# cd mysql
[root@hadoop001 mysql]# ll
total 52
drwxr-xr-x.  2 7161 wheel  4096 Feb  2  2016 bin
-rw-r--r--.  1 7161 wheel 17987 Feb  2  2016 COPYING
drwxr-xr-x.  2 7161 wheel  4096 Feb  2  2016 docs
drwxr-xr-x.  3 7161 wheel  4096 Feb  2  2016 include
drwxr-xr-x.  5 7161 wheel  4096 Feb  2  2016 lib
drwxr-xr-x.  4 7161 wheel  4096 Feb  2  2016 man
-rw-r--r--.  1 7161 wheel  2478 Feb  2  2016 README
drwxr-xr-x. 28 7161 wheel  4096 Feb  2  2016 share
drwxr-xr-x.  2 7161 wheel  4096 Feb  2  2016 support-files
[root@hadoop001 mysql]# mkdir arch data tmp
[root@hadoop001 mysql]# ll
total 64
drwxr-xr-x.  2 root root   4096 Nov 24 09:38 arch
drwxr-xr-x.  2 7161 wheel  4096 Feb  2  2016 bin
-rw-r--r--.  1 7161 wheel 17987 Feb  2  2016 COPYING
drwxr-xr-x.  2 root root   4096 Nov 24 09:38 data
drwxr-xr-x.  2 7161 wheel  4096 Feb  2  2016 docs
drwxr-xr-x.  3 7161 wheel  4096 Feb  2  2016 include
drwxr-xr-x.  5 7161 wheel  4096 Feb  2  2016 lib
drwxr-xr-x.  4 7161 wheel  4096 Feb  2  2016 man
-rw-r--r--.  1 7161 wheel  2478 Feb  2  2016 README
drwxr-xr-x. 28 7161 wheel  4096 Feb  2  2016 share
drwxr-xr-x.  2 7161 wheel  4096 Feb  2  2016 support-files
drwxr-xr-x.  2 root root   4096 Nov 24 09:38 tmp

arch:binlog日志存储的文件夹

2.创建配置文件/etc/my.cnf

#defualt start: /etc/my.cnf->/etc/mysql/my.cnf->SYSCONFDIR/my.cnf->$MYSQL_HOME/my.cnf-> –defaults-extra-file->~/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
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
[root@hadoop001 mysql]#vi /etc/my.cnf

[client]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock
default-character-set=utf8mb4

[mysqld]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock
#user 			= mysqladmin

skip-slave-start

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  = 1739
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 = ROW
log_bin_trust_function_creators=1
log-error  = /usr/local/mysql/data/hostname.err
log-bin = /usr/local/mysql/arch/mysql-bin
expire_logs_days = 7

innodb_write_io_threads=16

relay-log  = /usr/local/mysql/relay_log/relay-log
relay-log-index = /usr/local/mysql/relay_log/relay-log.index
relay_log_info_file= /usr/local/mysql/relay_log/relay-log.info

log_slave_updates=1
gtid_mode=OFF
enforce_gtid_consistency=OFF

# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

#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
sync_binlog = 500


#for innodb options 
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend

innodb_log_group_home_dir = /usr/local/mysql/arch
innodb_log_files_in_group = 4
innodb_log_file_size = 1G
innodb_log_buffer_size = 200M

#根据生产需要，调整pool size 
innodb_buffer_pool_size = 2G
#innodb_additional_mem_pool_size = 50M #deprecated in 5.6
tmpdir = /usr/local/mysql/tmp

innodb_lock_wait_timeout = 1000
#innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 2

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 = 128M

[mysql]
no-auto-rehash
default-character-set=utf8mb4

[mysqlhotcopy]
interactive-timeout

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

根据生产需要，调整pool size，生产上：innodb_buffer_pool_size = 2G

补充：

配置MySQL的环境变量：/etc/profile:

1
2
export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH

3.创建用户组及用户

1
2
3
4
[root@hadoop001 mysql]# groupadd -g 101 dba
[root@hadoop001 mysql]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin
[root@hadoop001 mysql]# id mysqladmin
uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root)

一般不需要设置mysqladmin的密码，直接从root或者LDAP用户sudo切换

1
2
3
4
5
6
7
[root@hadoop001 mysql]# passwd mysqladmin
Changing password for user mysqladmin.
New password: 
BAD PASSWORD: it is too simplistic/systematic
BAD PASSWORD: is too simple
Retype new password: 
passwd: all authentication tokens updated successfully.

如果mysqladmin用户已经存在，更改用户组及home目录地址：

1
[root@hadoop001 mysql]# usermod -u 514 -g dba -G root -d /usr/local/mysql mysqladmin

copy 环境变量配置文件至mysqladmin用户的home目录中,为了以下步骤配置个人环境变量

1
2
3
4
5
6
7
8
9
[root@hadoop001 mysql]# cp /etc/skel/.* /usr/local/mysql
cp: omitting directory `/etc/skel/.'
cp: omitting directory `/etc/skel/..'
cp: omitting directory `/etc/skel/.gnome2'
cp: omitting directory `/etc/skel/.mozilla'
[root@hadoop001 mysql]# su - mysqladmin
[mysqladmin@hadoop001 ~]$ exit
logout
[root@hadoop001 mysql]#

4.配置环境变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@hadoop001 mysql]# vi .bash_profile
# .bash_profile
# Get the aliases and functions

if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export MYSQL_BASE=/usr/local/mysql
export PATH=${MYSQL_BASE}/bin:$PATH


unset USERNAME

#stty erase ^H
set umask to 022
umask 022
PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1

5.赋权限和用户组

1
2
3
4
5
6
7
8
9
10
11
12
[root@hadoop001 mysql]# chown mysqladmin:dba /etc/my.cnf
[root@hadoop001 mysql]# chmod  640 /etc/my.cnf  
[root@hadoop001 mysql]# ll /etc/my.cnf
-rw-r-----. 1 mysqladmin dba 2218 Nov 15 01:07 /etc/my.cnf

[root@hadoop001 mysql]# chown -R mysqladmin:dba /usr/local/mysql
[root@hadoop001 mysql]# chown -R mysqladmin:dba /usr/local/mysql/*
[root@hadoop001 mysql]# chown -R mysqladmin:dba /usr/local/mysql-5.7.11-linux-glibc2.5-x86_64

[root@hadoop001 mysql]# chmod -R 755 /usr/local/mysql 
[root@hadoop001 mysql]# chmod -R 755 /usr/local/mysql/*
[root@hadoop001 mysql]# chmod -R 755 /usr/local/mysql-5.7.11-linux-glibc2.5-x86_64

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
#将服务文件拷贝到init.d下，并重命名为mysql
[root@hadoop001 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql 
#赋予可执行权限
[root@hadoop001 mysql]# chmod +x /etc/rc.d/init.d/mysql
#删除服务
[root@hadoop001 mysql]# chkconfig --del mysql
#添加服务
[root@hadoop001 mysql]# chkconfig --add mysql
[root@hadoop001 mysql]# chkconfig --level 345 mysql on
#开机自启动
[root@hadoop001 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.安装

安装libaio及安装mysql的初始db

1
2
3
4
5
6
7
8
9
[root@hadoop001 mysql]# yum -y install libaio
[root@hadoop001 mysql]# su - mysqladmin
hadoop001:mysqladmin:/usr/local/mysql:>bin/mysqld \
> --defaults-file=/etc/my.cnf \
> --user=mysqladmin \
> --basedir=/usr/local/mysql/ \
> --datadir=/usr/local/mysql/data/ \
> --initialize
hadoop001:mysqladmin:/usr/local/mysql:>

在初始化时如果加上 –initial-insecure，则会创建空密码的 root@localhost 账号，否则会创建带密码的 root@localhost 账号，密码直接写在 log-error 日志文件中（在5.6版本中是放在 ~/.mysql_secret 文件里，更加隐蔽，不熟悉的话可能会无所适从）

bin/mysqld –defaults-file=/etc/my.cnf –user=mysqladmin –basedir=/usr/local/mysql/ –datadir=/usr/local/mysql/data/ –initialize

查看临时密码：

1
2
hadoop001:mysqladmin:/usr/local/mysql/data:>cat hostname.err | grep pass
2021-11-24T06:05:28.300539Z 1 [Note] A temporary password is generated for root@localhost: uhsa*57>hacF

  • 启动Mysql时报错：mysqld_safe mysqld from pid file /usr/local/mysql/data/Linux.pid ended。参考文章Linux The server quit without updating PID file的几种解决方法发现，是之前在运行5.6版本的Mysql仍在后台运行，占用pid文件，解决方法：ps -ef|grep mysql找出进程，然后kill掉进程后再重新安装。

  • 若遇到报错，需要重新安装

    1
    2
    rm -rf /usr/local/mysql/arch/*
    rm -rf /usr/local/mysql/data/*

    然后跳到第7步

8.启动

1
2
3
4
[root@hadoop001 mysql]# mysql --version
mysql  Ver 14.14 Distrib 5.7.11, for linux-glibc2.5 (x86_64) using  EditLine wrapper
hadoop001:mysqladmin:/usr/local/mysql:>service mysql start
Starting MySQL..                                           [  OK  ]

9.登录及修改用户密码

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
hadoop001:mysqladmin:/usr/local/mysql:>mysql -uroot -p'>Wo>kh(GL7;D'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11-log

Copyright (c) 2000, 2016, 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> alter user root@localhost identified by 'syncdb123!';//rd1
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'syncdb123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

重要的三句话：

1
2
3
mysql>create database ruozedata;
mysql>grant all privileges on ruozedata.* to ruoze@'localhost' identified by '123456';
mysql>flush privileges;

10.重启

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
hadoop001:mysqladmin:/usr/local/mysql:>service mysql restart
Shutting down MySQL..                                      [  OK  ]
rm: cannot remove `/var/lock/subsys/mysql': Permission denied
Starting MySQL.                                            [  OK  ]
hadoop001:mysqladmin:/usr/local/mysql:>
hadoop001:mysqladmin:/usr/local/mysql:>mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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并不能直接service mysql start启动Mysql服务，查看data文件夹内发现有

-rw-r-----. 1 mysql dba 53440 Nov 25 00:40 hostname.err

即用户权限出现问题。然后切回root用户修改data/*的用户：用户组为mysqladmin:dba再启动，问题解决，过程如下：

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
[root@hadoop001 ~]# service mysql status
MySQL is not running, but lock file (/var/lock/subsys/mysql[FAILED]
[root@hadoop001 ~]# service mysql start
Starting MySQL.The server quit without updating PID file (/usr/local/mysql/data/hadoop001.pid).                                                          [FAILED]
[root@hadoop001 ~]# su - mysqladmin
hadoop001:mysqladmin:/usr/local/mysql:>service mysql start
Starting MySQL.The server quit without updating PID file (/usr/local/mysql/data/hadoop001.pid).                                                          [FAILED]
hadoop001:mysqladmin:/usr/local/mysql:>ll
total 68
drwxr-xr-x.  2 mysqladmin dba  4096 Nov 25 00:35 arch
drwxr-xr-x.  2 mysqladmin dba  4096 Feb  2  2016 bin
-rwxr-xr-x.  1 mysqladmin dba 17987 Feb  2  2016 COPYING
drwxr-xr-x.  5 mysqladmin dba  4096 Nov 25 00:40 data
drwxr-xr-x.  2 mysqladmin dba  4096 Feb  2  2016 docs
drwxr-xr-x.  3 mysqladmin dba  4096 Feb  2  2016 include
drwxr-x---.  2 mysqladmin dba  4096 Nov 24 13:15 keyring
drwxr-xr-x.  5 mysqladmin dba  4096 Feb  2  2016 lib
drwxr-xr-x.  4 mysqladmin dba  4096 Feb  2  2016 man
-rwxr-xr-x.  1 mysqladmin dba  2478 Feb  2  2016 README
drwxr-xr-x. 28 mysqladmin dba  4096 Feb  2  2016 share
drwxr-xr-x.  2 mysqladmin dba  4096 Feb  2  2016 support-files
drwxr-xr-x.  2 mysqladmin dba  4096 Nov 25 00:35 tmp
hadoop001:mysqladmin:/usr/local/mysql:>ll data/
total 2097248
-rw-r-----. 1 mysqladmin dba         56 Nov 24 14:55 auto.cnf
-rw-r-----. 1 mysql      dba      53440 Nov 25 00:40 hostname.err
-rw-r-----. 1 mysqladmin dba        294 Nov 25 00:35 ib_buffer_pool
-rw-r-----. 1 mysqladmin dba 1073741824 Nov 25 00:35 ibdata1
-rw-r-----. 1 mysqladmin dba 1073741824 Nov 24 14:55 ibdata2
drwxr-x---. 2 mysqladmin dba       4096 Nov 24 14:55 mysql
drwxr-x---. 2 mysqladmin dba       4096 Nov 24 14:55 performance_schema
drwxr-x---. 2 mysqladmin dba      12288 Nov 24 14:55 sys
hadoop001:mysqladmin:/usr/local/mysql:>exit
logout
[root@hadoop001 ~]# chown mysqladmin:dba /usr/local/mysql/*
[root@hadoop001 ~]# chown mysqladmin:dba /usr/local/mysql/data/*
[root@hadoop001 ~]# su - mysqladmin
hadoop001:mysqladmin:/usr/local/mysql:>ll data/
total 2097248
-rw-r-----. 1 mysqladmin dba         56 Nov 24 14:55 auto.cnf
-rw-r-----. 1 mysqladmin dba      53440 Nov 25 00:40 hostname.err
-rw-r-----. 1 mysqladmin dba        294 Nov 25 00:35 ib_buffer_pool
-rw-r-----. 1 mysqladmin dba 1073741824 Nov 25 00:35 ibdata1
-rw-r-----. 1 mysqladmin dba 1073741824 Nov 24 14:55 ibdata2
drwxr-x---. 2 mysqladmin dba       4096 Nov 24 14:55 mysql
drwxr-x---. 2 mysqladmin dba       4096 Nov 24 14:55 performance_schema
drwxr-x---. 2 mysqladmin dba      12288 Nov 24 14:55 sys
hadoop001:mysqladmin:/usr/local/mysql:>service mysql start
Starting MySQL.                                            [  OK  ]
hadoop001:mysqladmin:/usr/local/mysql:>ll data/
total 2109552
-rw-r-----. 1 mysqladmin dba         56 Nov 24 14:55 auto.cnf
-rw-r-----. 1 mysqladmin dba          5 Nov 25 00:41 hadoop001.pid
-rw-r-----. 1 mysqladmin dba      57597 Nov 25 00:41 hostname.err
-rw-r-----. 1 mysqladmin dba        294 Nov 25 00:35 ib_buffer_pool
-rw-r-----. 1 mysqladmin dba 1073741824 Nov 25 00:41 ibdata1
-rw-r-----. 1 mysqladmin dba 1073741824 Nov 24 14:55 ibdata2
-rw-r-----. 1 mysqladmin dba   12582912 Nov 25 00:41 ibtmp1
drwxr-x---. 2 mysqladmin dba       4096 Nov 24 14:55 mysql
-rw-rw----. 1 mysqladmin dba          5 Nov 25 00:41 mysqld_safe.pid
srwxrwxrwx. 1 mysqladmin dba          0 Nov 25 00:41 mysql.sock
-rw-------. 1 mysqladmin dba          5 Nov 25 00:41 mysql.sock.lock
drwxr-x---. 2 mysqladmin dba       4096 Nov 24 14:55 performance_schema
drwxr-x---. 2 mysqladmin dba      12288 Nov 24 14:55 sys
hadoop001:mysqladmin:/usr/local/mysql:>exit
logout
[root@hadoop001 ~]#

再次重启，问题再次出现，hostname.err的用户用户组为：mysql:mysqladmin

经查阅，发现my.cnf中[mysqld]下有默认启动用户user = mysql，指定为user = mysqladmin后重启，问题不再出现，且mysql服务开机自启动

1
2
3
4
5
6
[mysqld]
port            = 3306
socket          = /usr/local/mysql/data/mysql.sock
#以下注释为默认参数，不指定的话，user=mysql
#user 			= mysql
user 			= mysqladmin

k12大数据技术

个人简介。