一、环境准备

mysql安装包:

2019-06-24_095204.png

二、环境搭建

1.上传mysql安装包到linux虚拟机中

[root@Hadoop001 ~]# rz -be
[root@Hadoop001 ~]# ll
-rwxr--r--. 1 root root 311771412 Jun 14 11:20 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz

2.检查是否环境是否已经安装mysql

[root@Hadoop001 ~]# ps -ef | grep mysql
root      1823  1780  0 05:42 pts/0    00:00:00 grep mysql
[root@Hadoop001 ~]# rpm -qa | grep -i mysql

3.解压mysql安装包到/usr/local/目录下,并构建一个软连接

[root@Hadoop001 ~]# tar -xzvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@Hadoop001 local]# ln -s mysql-5.6.23-linux-glibc2.5-x86_64 mysql
[root@Hadoop001 local]# ll
lrwxrwxrwx.  1 root root   34 Jun 24 05:49 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64
drwxr-xr-x. 13 root root 4096 Jun 24 05:46 mysql-5.6.23-linux-glibc2.5-x86_64

4.创建用户和用户组

创建用户组时-g 101参数:指定组的GID为101
创建用户时-u 514 -g dba -G root -d /usr/local/mysql参数:指定用户的uid为514、指定主组为dba、所有组添加root、指定用户的home目录为/usr/local/mysql

[root@Hadoop001 local]# groupadd -g 101 dba
[root@Hadoop001 local]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin
useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.
[root@Hadoop001 local]# id mysqladmin
uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root)

5.拷贝环境变量配置文件到mysqladmin的home目录

[root@Hadoop001 local]# 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'

6.创建/etc/my.conf文件

默认开始路径: /etc/my.cnf->/etc/mysql/my.cnf->SYSCONFDIR/my.cnf->$MYSQL_HOME/my.cnf-> --defaults-extra-file->~/my.cnf

[root@Hadoop001 etc]# touch my.cnf
[root@Hadoop001 etc]# vi my.cnf
[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

生产上可以将innodb_buffer_pool_size的值改为8G到12G

7.改变文件的权限和用户、用户组

[root@Hadoop001 etc]# chown mysqladmin:dba /etc/my.cnf
[root@Hadoop001 etc]# chmod 640 /etc/my.cnf
[root@Hadoop001 etc]# ll my.cnf 
-rw-r-----. 1 mysqladmin dba 2218 Jun 24 06:12 my.cnf
[root@Hadoop001 local]# chown -R mysqladmin:dba /usr/local/mysql
[root@Hadoop001 local]# chmod 755 /usr/local/mysql
[root@Hadoop001 local]# chown -R mysqladmin:dba /usr/local/mysql/*
[root@Hadoop001 local]# chown -R mysqladmin:dba mysql-5.6.23-linux-glibc2.5-x86_64

文件夹的权限和用户、用户组的修改之后一定要cd进去检查下

[root@Hadoop001 local]# ll
lrwxrwxrwx.  1 mysqladmin dba    34 Jun 24 05:49 mysql -> mysql-5.6.23-linux-glibc2.5-x86_64
drwxr-xr-x. 13 mysqladmin dba  4096 Jun 24 06:01 mysql-5.6.23-linux-glibc2.5-x86_64
[root@Hadoop001 mysql]# ll
total 168
drwxr-xr-x.  2 mysqladmin dba   4096 Jun 24 05:46 bin
-rwxr-xr-x.  1 mysqladmin dba  17987 Jan 19  2015 COPYING
drwxr-xr-x.  3 mysqladmin dba   4096 Jun 24 05:45 data
drwxr-xr-x.  2 mysqladmin dba   4096 Jun 24 05:46 docs
drwxr-xr-x.  3 mysqladmin dba   4096 Jun 24 05:46 include
-rwxr-xr-x.  1 mysqladmin dba 102315 Jan 19  2015 INSTALL-BINARY
drwxr-xr-x.  3 mysqladmin dba   4096 Jun 24 05:45 lib
drwxr-xr-x.  4 mysqladmin dba   4096 Jun 24 05:46 man
drwxr-xr-x. 10 mysqladmin dba   4096 Jun 24 05:46 mysql-test
-rwxr-xr-x.  1 mysqladmin dba   2496 Jan 19  2015 README
drwxr-xr-x.  2 mysqladmin dba   4096 Jun 24 05:45 scripts
drwxr-xr-x. 28 mysqladmin dba   4096 Jun 24 05:45 share
drwxr-xr-x.  4 mysqladmin dba   4096 Jun 24 05:46 sql-bench
drwxr-xr-x.  2 mysqladmin dba   4096 Jun 24 05:45 support-files

8.创建binlog日志存储的文件夹

[root@Hadoop001 mysql]# su - mysqladmin
[mysqladmin@Hadoop001 ~]$ mkdir arch

9.正式安装mysql

[mysqladmin@Hadoop001 ~]$ scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
Installing MySQL system tables...2019-06-24 06:43:13 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2019-06-24 06:43:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK

Filling help tables...2019-06-24 06:43:57 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2019-06-24 06:43:57 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /usr/local/mysql/bin/mysqladmin -u root password 'new-password'
  /usr/local/mysql/bin/mysqladmin -u root -h Hadoop001 password 'new-password'

Alternatively you can run:

  /usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/local/mysql/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

9.配置mysql服务和开机自启动

将服务文件拷贝到init.d下，并重命名为mysql.并赋予可执行性权限

[root@Hadoop001 ~]# cd /usr/local/mysql
[root@Hadoop001 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql
[root@Hadoop001 init.d]# chmod +x /etc/rc.d/init.d/mysql
#删除服务
[root@Hadoop001 init.d]# chkconfig --del mysql
#添加服务
[root@Hadoop001 init.d]# chkconfig --del mysql
[root@Hadoop001 init.d]# chkconfig --add mysql
[root@Hadoop001 init.d]# chkconfig --level 345 mysql on
[root@Hadoop001 init.d]# vi /etc/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

su - mysqladmin -c '/etc/init.d/mysql start'

10.开启mysql，查看进程和端口

[mysqladmin@Hadoop001 ~]$ mysqld_safe &
[mysqladmin@Hadoop001 ~]$ ps -ef | grep mysqld | grep -v grep
514       2525  2273  0 07:06 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
514       3168  2525  2 07:06 pts/0    00:00:02 /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@Hadoop001 ~]$ netstat -nlp | grep 3168     
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::3306                     :::*                        LISTEN      3168/mysqld         
unix  2      [ ACC ]     STREAM     LISTENING     28918  3168/mysqld         /usr/local/mysql/data/mysql.sock
[mysqladmin@Hadoop001 ~]$ service mysql status
MySQL running (3168)                                       [  OK  ]

11.登入mysql

[mysqladmin@Hadoop001 ~]$ mysql
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> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

12.更新密码和清除用户

mysql> use mysql;
Database changed
mysql> select user,password,host from user;
+------+----------+-----------+
| user | password | host      |
+------+----------+-----------+
| root |          | localhost |
| root |          | hadoop001 |
| root |          | 127.0.0.1 |
| root |          | ::1       |
|      |          | localhost |
|      |          | hadoop001 |
+------+----------+-----------+
6 rows in set (0.00 sec)
mysql> update user set password=password('123456') where user='root';
mysql> delete from user where user='';
mysql> select user,password,host from user;
+------+-------------------------------------------+-----------+
| user | password                                  | host      |
+------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | hadoop001 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 127.0.0.1 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | ::1       |
+------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
mysql> flush privileges;

特别注意:只要关于用户的操作，最后都要刷新下权限。

13.配置个人环境变量

[mysqladmin@Hadoop001 ~]$ vi .bash_profile
export MYSQL_HOME=/usr/local/mysql
PATH=$MYSQL_HOME/bin:$PATH:$HOME/bin

export PATH

14.重新部署

[mysqladmin@Hadoop001 ~]$ service mysql stop
[mysqladmin@Hadoop001 ~]$ rm -rf /usr/local/mysql/arch/*
[mysqladmin@Hadoop001 ~]$ rm -rf /usr/local/mysql/data/*
[mysqladmin@Hadoop001 ~]$ scripts/mysql_install_db  \
--user=mysqladmin \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

15.mysql必知三句话

1）创建数据库

mysql> create database data;
Query OK, 1 row affected (0.01 sec)

2）创建用户，用户名:test,密码:123456,访问路径:%代表任意ip都可以访问。并赋予data数据库所有的权限。
例如:
192.168.174.%
192.168.%.%

mysql> grant all privileges on data.* to test@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

3)刷新权限

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