大数据学习2：mysql安装笔记

前期准备：
1、CentOS6.5
2、MySQL安装包（mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz）


安装步骤：
1、检查环节
[root@hadoop001 ~]# ps -ef | grep mysql
root       1161   1146  0 20:29 pts/1    00:00:00 grep mysql




2、上传，解压，重命名
[root@hadoop001 ~]# cd /usr/local/
[root@hadoop001 local]# mv /root/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz /usr/local/
==============================
可能会遇到这个错误
gzip: stdin: invalid compressed data--format violated
tar: Unexpected EOF in archive
tar: Unexpected EOF in archive
tar: Error is not recoverable: exiting now
是由于上传包没有选二进制导致，重新上传。
==============================
[root@hadoop001 local]# mv mysql-5.6.23-linux-glibc2.5-x86_64 mysql


3、建用户和用户组
[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)


[root@hadoop001 local]# 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.


4、copy 环境变量配置文件至mysqladmin用户的home目录中,为了以下步骤配置个人环境变量
[root@hadoop001 local]# cp /etc/skel/.* /usr/local/mysql
cp: omitting directory `/etc/skel/.'
cp: omitting directory `/etc/skel/..'


5、创建 /etc/my.cnf(640) 
#mysql的默认配置文件读取顺序为
#defualt start: /etc/my.cnf->/etc/mysql/my.cnf->SYSCONFDIR/my.cnf->$MYSQL_HOME/my.cnf-> --defaults-extra-file->~/my.cnf 
[root@sht-sgmhadoopnn-01 ~]# vi /etc/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 = 512M 根据自身内存修改#
innodb_buffer_pool_size = 512M   
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


6、修改权限
[root@hadoop001 local]# chown  mysqladmin:dba /etc/my.cnf 
[root@hadoop001 local]# chmod  640 /etc/my.cnf
[root@hadoop001 local]# ll /etc/my.cnf 
-rw-r-----. 1 mysqladmin dba 2217 Aug 16 21:23 /etc/my.cnf


[root@hadoop001 local]# chown -R mysqladmin:dba /usr/local/mysql
[root@hadoop001 local]# chmod -R 755 /usr/local/mysql 
[root@hadoop001 local]# su - mysqladmin
[mysqladmin@hadoop001 ~]$ pwd 
/usr/local/mysql
[mysqladmin@hadoop001 ~]$ mkdir arch 


7、mysql安装
[mysqladmin@hadoop001 ~]$ scripts/mysql_install_db  --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
==========================================
-bash: scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory
是由于使用最小系统安装，由于缺少依赖包导致的，经查询后安装
[root@hadoop001 ~]# yum install -y perl gcc kernel-devel perl-devel
==========================================
输入后跳出以下信息：
Installing MySQL system tables...2017-08-16 21:30:48 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2017-08-16 21:30:48 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...2017-08-16 21:31:17 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2017-08-16 21:31:17 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


8、配置mysql service自启动
在root用户下：
[root@hadoop001 ~]# cd /usr/local/mysql
#拷贝mysql.server 到系统自启动目录
[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 
#删除原mysql服务
[root@hadoop001 mysql]# chkconfig --del mysql
#添加mysql服务
[root@hadoop001 mysql]# chkconfig --add mysql
[root@hadoop001 mysql]# chkconfig --level 345 mysql on 


9、启动mysql 并检查监听是否正常
[mysqladmin@hadoop001 ~]$ pwd 
删除多余的my.cnf 
[mysqladmin@hadoop001 ~]$ rm -rf my.cnf 
[mysqladmin@hadoop001 ~]$ bin/mysqld_safe &
[1] 1372
[mysqladmin@hadoop001 ~]$ 170816 21:38:05 mysqld_safe Logging to '/usr/local/mysql/data/hostname.err'.
170816 21:38:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
[mysqladmin@hadoop001 ~]$ 
[mysqladmin@hadoop001 ~]$ ps -ef|grep mysqld
514        1372   1256  0 21:38 pts/1    00:00:00 /bin/sh bin/mysqld_safe
514        2015   1372  1 21:38 pts/1    00:00:00 /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
514        2038   1256  0 21:38 pts/1    00:00:00 grep mysqld


[root@hadoop001 mysql]# netstat -nlp| grep mysql
tcp        0      0 :::3306                     :::*                        LISTEN      2015/mysqld         
unix  2      [ ACC ]     STREAM     LISTENING     41433  2015/mysqld         /usr/local/mysql/data/mysql.sock


[mysqladmin@hadoop001 ~]$ service mysql status 
 SUCCESS! MySQL running (2015)
 
10、登陆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> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.11 sec)


mysql> use mysql
Database changed


mysql> select host,user,password from user;
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | root |          |
| hadoop001 | root |          |
| 127.0.0.1 | root |          |
| ::1       | root |          |
| localhost |      |          |
| hadoop001 |      |          |
+-----------+------+----------+
6 rows in set (0.00 sec)


mysql> update user set password=password('password') where user='root';
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4  Changed: 4  Warnings: 0


mysql> delete from user where user='';
Query OK, 2 rows affected (0.03 sec)


mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| hadoop001 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| ::1       | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)


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


下次登陆mysql ：
[mysqladmin@hadoop001 ~]$ mysql -uroot -ppassword


11、配置mysqladmin用户的 .bash_profile  清空后直接贴入
[mysqladmin@sht-sgmhadoopnn-01 ~]$ cat .bash_profile 
# .bash_profile
# Get the aliases and functions


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


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


unset USERNAME


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


alias l="ls -ltr"
alias la="ls -la"
alias d="df -lk"
#alias rm="rm -i"
alias bg="ps -aef|grep mysql"
alias base="cd /usr/local/mysql"
alias bin="cd /usr/local/mysql/bin"
alias dba="cd /usr/local/mysql/dba"
alias sh="cd /usr/local/mysql/dba/sh"
alias sql="cd /usr/local/mysql/dba/sql"
alias config="cd /usr/local/mysql/dba/config"
alias dbalog="cd /usr/local/mysql/dba/log"
alias arch="cd /usr/local/mysql/arch"
alias data="cd /usr/local/mysql/data"
## endendend


[mysqladmin@hadoop001 ~]$ source .bash_profile 
hadoop001:mysqladmin:/usr/local/mysql:>


至此，mysql安装结束。