大数据学习之MySQL部署

1. 部署

• rpm包部署：
  yum install mysql-server，
  yum install mysql-client
  其部署简单，但是目录固化，不适合企业使用
• 二进制部署：部署麻烦但是目录定制化，适用于企业

2. 进行二进制部署

首先，通过rz命令上传MySQL和JDK到/usr/local目录下

2.1 Download and Check MD5

mysql安装包下载：详情链接
MD5下载：详情链接

首先，在线安装mysql和md5,使用 wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz和 wget http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.6/mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz.md5分别进行安装；其次使用命令cat mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz.md5 和md5sum mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz进行验证，若两者结果一致，则 no problem!

2.2 java部署

通过rz命令将JDK上传后，对其进行解压：：[root@hadoopoo1 java]# tar -xzvf jdk-8u45-linux-x64.gz
修改用户和用户组（坑）：[root@hadoopoo1 java]# ll
[root@hadoopoo1 java]# chown -R root:root jdk1.8.0_45
配置全局环境变量：[root@hadoop001 java]# vi /etc/profile
按G键到行尾添加两行命令语句：
export JAVA_HOME=/usr/java/jdk1.8.0_45
export PATH=$ JAVA_HOME/bin:$ RZDATA_HOME/bin:$PATH
编辑完后按Esc键退出编辑模式进入命令模式，输入wq保存退出。然后输入命令source /etc/profile生效环境变量。
[root@hadoop001 java]# which java
/usr/bin/java
[root@hadoop001 java]# source /etc/profile
[root@hadoop000 java]# which java
/usr/java/jdk1.8.0_45/bin/java
[root@hadoop000 java]# java -version （查看java版本）

2.3 Check isnot install

[root@hadoop001 local]# ps -ef|grep mysqld
[root@hadoop001 local]# rpm -qa |grep -i mysql

2.4 tar and mv （解压和重命名）

[root@hadoopoo1 local]# tar -xzvf mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
[root@hadoopoo1 local]# mv mysql-5.6.41-linux-glibc2.12-x86_64 mysql

2.5 Create group and user

[root@hadoop001 local]# groupadd -g 101 dba
[root@hadoop001 local]# useradd -u 514 -g dba -G root -d /usr/local/mysql mysqladmin
[root@hadoop001 local]# id mysqladmin
uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root)
if user mysqladmin is existing,please execute the following command of usermod.

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

copy 环境变量配置文件至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/.mozilla’

2.6 Create /etc/my.cnf(640)

#defualt start: /etc/my.cnf->/etc/mysql/my.cnf->SYSCONFDIR/my.cnf->$MYSQL_HOME/my.cnf-> --defaults-extra-file->~/my.cnf
[root@hadoop001 mysql]# cd /etc
[root@hadoop001 etc]# touch my.cnf

[root@hadoop001 etc]# vi my.cnf
按dG清空后，按i键进入编辑模式粘贴下面内容：

[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 = 1024M
然后Esc键退出编辑模式进入命令模式，输入wq保存退出，不正常退出的话会生成swp文件，可通过命令rm .my.cnf.swp -f 删除

2.7 chown and chmod privileges and try first install

[root@hadoop001 local]# chown mysqladmin:dba /etc/my.cnf

[root@hadoop001 local]# chmod 640 /etc/my.cnf
[root@hadoop001 etc]# ll my.cnf
-rw-r----- 1 mysqladmin dba 2201 Aug 25 23:09 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

创建binlog文件

查看版本

2.8 Again install

[mysqladmin@hadoop001 ~]$ scripts/mysql_install_db  --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
Installing MySQL system tables...2018-09-16 11:48:29 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2018-09-16 11:48:29 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-09-16 11:48:29 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-09-16 11:48:29 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.41-log) starting as process 12313 ...
OK

Filling help tables...2018-09-16 11:48:32 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2018-09-16 11:48:32 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-09-16 11:48:32 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-09-16 11:48:32 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.41-log) starting as process 12336 ...
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

WARNING: Found existing config file /usr/local/mysql/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql/my-new.cnf,
please compare it with your file and take the changes you need.

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 

2.9 Configure mysql service and boot auto start

#!/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 --federated"  

2.10 Start mysql and to view process and listening

[root@hadoop001 ~]# su - mysqladmin
[mysqladmin@hadoop001 ~]$ pwd
/usr/local/mysql
[mysqladmin@hadoop001 ~]$ rm -rf my.cnf
[mysqladmin@hadoop001 ~]$ mysqld_safe &
[1] 13655
[mysqladmin@hadoop001 ~]$ 180916 18:09:32 mysqld_safe Logging to '/usr/local/mysql/data/hostname.err'.
180916 18:09:32 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

显示以上错误是要回到root目录下进行