大数据之MySQL部署及常规使用

最新推荐文章于 2022-08-18 18:31:57 发布

仙人掌仙人

最新推荐文章于 2022-08-18 18:31:57 发布

阅读量1k

点赞数

分类专栏：零基础大数据 Bigdata learning 文章标签：零基础大数据 MySQL部署

本文链接：https://blog.csdn.net/Sylvia_D507/article/details/82830851

版权

Bigdata learning 同时被 3 个专栏收录

30 篇文章 0 订阅

订阅专栏

零基础

28 篇文章 0 订阅

订阅专栏

大数据

28 篇文章 0 订阅

订阅专栏

啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊???????????
刚刚没有保存，得重写，抓狂×3
终于补完了Linux命令，今天继续补MySQL???????????
MySQL环境部署，内容很多?????????????

一、部署

最简单的方式是rpm包部署：优点：部署简单；缺点：路径（目录）固化。
yum install mysql-server
yum install mysql-client
企业里面最常用的是：二进制部署：优点：路径（目录）定制化；缺点：部署复杂。

二、二进制部署如何进行

先将MySQL和JDK安装包上传到/usr/local目录

在进行文件传输的时候，可以使用sftp (alt+p)方式进行传输文件，也经常使用rz命令进行图形化的方式传输文件。
当rz命令无效时，运行如下指令进行安装即可：yum install lrzsz -y（免输）

Rz mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
Rz jdk-8u45-linux-x64.gz
进入正题：
(1)Download and Check MD5
[root@hadoopooo local]# cd /usr/local （切换到local目录下安装）
[root@hadoopooo local]# wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz （网页在线下载安装）
[root@hadoopooo local]# wget http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.6/mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz.md5
MySQL下载地址：https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
对应md5下载地址：http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.6/mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz.md5
在这里插入图片描述
用md5检查安装包是否完整：
[root@hadoopooo local]# cat mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz.md5
b0ac6851908b5c17b6a283d10709fcfd mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
[root@hadoopooo local]# md5sum mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
b0ac6851908b5c17b6a283d10709fcfd mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
在这里插入图片描述
接下来是下进行JAVA环境部署：

1、将JDK上传完之后解压：[root@hadoopooo java]# tar -xzvf jdk-8u45-linux-x64.gz
2、修改用户和用户组（坑）：[root@hadoopooo java]# ll
total 169216
drwxr-xr-x 8 uucp 143 4096 Apr 11 2015 jdk1.8.0_45（修改此用户和用户组）
-rw-r–r-- 1 root root 173271626 Sep 16 18:46 jdk-8u45-linux-x64.gz
[root@hadoopooo java]# chown -R root:root jdk1.8.0_45（修改命令）
在这里插入图片描述
3、配置全局环境变量：[root@hadoop000 java]# vi /etc/profile
按G键进入行尾输入两行命令：
export JAVA_HOME=/usr/java/jdk1.8.0_45

export PATH=$ JAVA_HOME/bin:$ RZDATA_HOME/bin:$PATH
保存退出
4、生效环境变量：
[root@hadoopooo java]# source /etc/profile
[root@hadoopooo java]# which java
/usr/java/jdk1.8.0_45/bin/java
[root@hadoopooo java]# java -version
java version “1.8.0_45”
Java( TM ) SE Runtime Environment (build 1.8.0_45-b14)
Java HotSpot( TM ) 64-Bit Server VM (build 25.45-b02, mixed mode)
在这里插入图片描述
（2）Check isnot install
[root@hadoopooo local]# ps -ef|grep mysqld
root 15392 15372 0 22:00 pts/0 00:00:00 grep mysqld
[root@hadoopooo local]# rpm -qa |grep -i mysql
mysql-libs-5.1.73-8.el6_8.x86_64
（3）tar and mv
[root@hadoopooo local]# tar -xzvf mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
[root@hadoopooo local]# mv mysql-5.6.41-linux-glibc2.12-x86_64 mysql （重命名）
（4）Create group and user
[root@hadoopooo local]# groupadd -g 101 dba
[root@hadoopooo 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.（说明缺乏隐藏文件，需copy Home 目录的隐藏文件）
[root@hadoopooo 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@hadoopooo local]]# usermod -u 514 -g dba -G root -d /usr/local/mysql mysqladmin

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

[root@sht-sgmhadoopnn-01 local]# cp /etc/skel/.* /usr/local/mysql
[root@hadoopooo local]# cp /etc/skel/.* /usr/local/mysql
cp: omitting directory `/etc/skel/.’

cp: omitting directory `/etc/skel/…’
在这里插入图片描述
（5）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@sht-sgmhadoopnn-01 mysql]# cd /etc/
[root@sht-sgmhadoopnn-01 etc]# touch my.cnf
[root@sht-sgmhadoopnn-01 etc]# vi my.cnf （dG删除已有的内容，黏贴以下内容）
[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
不正常退出会产生swp文件，删除：rm .my.cnf.swp -f
（6）chown and chmod privileges and try first install
[root@hadoopooo etc]# chown mysqladmin:dba /etc/my.cnf
[root@hadoopooo etc]# chmod 640 /etc/my.cnf
[root@hadoopooo etc]# ll my.cnf
-rw-r----- 1 mysqladmin dba 2218 Sep 17 23:34 my.cnf
[root@hadoopooo etc]# chown -R mysqladmin:dba /usr/local/mysql
[root@hadoopooo etc]# chmod -R 755 /usr/local/mysql
[root@hadoopooo etc]# su - mysqladmin
[mysqladmin@hadoopooo ~]$ pwd
/usr/local/mysql
在这里插入图片描述
创建binlog（归档）文件
[mysqladmin@hadoopooo ~]$ mkdir arch
[mysqladmin@hadoopooo ~]$ scripts/mysql_install_db
[root@hadoopooo local]# cat /proc/version （看版本）
Linux version 2.6.32-696.6.3.el6.x86_64 (mockbuild@c1bl.rdu2.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-18) (GCC) ) #1 SMP Wed Jul 12 14:17:22 UTC 2017
[root@hadoopooo local]# rpm -qa |grep gcc
gcc-4.4.7-18.el6.x86_64
libgcc-4.4.7-18.el6.x86_64
[root@hadoopooo local]# yum -y install libaio
Loaded plugins: fastestmirror
Setting up Install Process
Loading mirror speeds from cached hostfile
base | 3.7 kB 00:00
epel | 3.2 kB 00:00
extras | 3.4 kB 00:00
updates | 3.4 kB 00:00
Package libaio-0.3.107-10.el6.x86_64 already installed and latest version
Nothing to do

（7）Again install
[mysqladmin@hadoopooo ~]$ scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
WARNING: The host ‘hadoopooo’ could not be looked up with /usr/local/mysql/bin/resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !

Installing MySQL system tables…2018-09-17 23:55:46 0 [Warning] ‘THREAD_CONCURRENCY’ is deprecated and will be removed in a future release.
2018-09-17 23:55:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-09-17 23:55:46 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-09-17 23:55:46 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.41-log) starting as process 15700 …
OK

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

（8）Configure mysql service and boot auto start（开机自启动）
[root@hadoopooo ~]# cd /usr/local
[root@hadoopooo local]# cd /usr/local/mysql
[root@hadoopooo mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql （#将服务文件拷贝到init.d下，并重命名为mysql）
[root@hadoopooo mysql]# chmod +x /etc/rc.d/init.d/mysql （#赋予可执行权限）
[root@hadoopooo mysql]# chkconfig --add mysql （#添加服务）
[root@hadoopooo mysql]# chkconfig mysql on
[root@hadoopooo mysql]# 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 --federated”（添加这组命令）
在这里插入图片描述

（9）Start mysql and to view process and listening
[root@hadoopooo mysql]# su - mysqladmin
[mysqladmin@hadoopooo ~]$ pwd
/usr/local/mysql
[mysqladmin@hadoopooo ~]$ rm -rf my.cnf
[mysqladmin@hadoopooo ~]$ which mysqld_safe
~/bin/mysqld_safe
[mysqladmin@hadoopooo ~]$ vi .bash_profile
#.bash_profile

#Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

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

[root@hadoopooo ~]# cd /usr/local
[root@hadoopooo local]# cd mysql
[root@hadoopooo mysql]# su - mysqladmin
[mysqladmin@hadoopooo ~]$ echo $ MYSQL_HOME
/usr/local/mysql
[mysqladmin@hadoopooo ~]$ mysqld_safe &（enter后 enter）
[mysqladmin@hadoopooo ~]$ ps -ef|grep mysql
[mysqladmin@hadoopooo ~]$ netstat -tulnp | grep 15866
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
[root@hadoopooo ~]# netstat -tulnp | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 16509/mysqld
在这里插入图片描述

（10）Login mysql
[mysqladmin@hadoopooo ~]$ mysql（直接输入mysql）
然后输入show databases;
在这里插入图片描述

（11）Update password and Purge user
mysql> use mysql;（输入）
Database changed
mysql> show tables;（输入）
在这里插入图片描述
mysql> select user,password,host from user; （输入）

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

mysql> select user,password,host from user;
mysql> delete from user where user=’’;
Query OK, 2 rows affected (0.00 sec)

mysql> select user,password,host from user;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在这里插入图片描述

（12）Configure .bash_profile
#.bash_profile

#Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

#User specific environment and startup programs
export MYSQL_HOME=/usr/local/mysql
export PATH=$ MYSQL_HOME/bin:$PATH
在这里插入图片描述

三、常规使用：

1、create database Sylviadb;
2、grant all privileges on Sylviadb. to Sylvia@’%’ identified by ‘123456’;
3、 flush privileges;*
[mysqladmin@hadoopooo ~]$ mysql
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
[mysqladmin@hadoopooo ~]$ mysql -uroot -p123456
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 6
Server version: 5.6.41-log MySQL Community Server (GPL)

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> create database Sylviadb;
Query OK, 1 row affected (0.00 sec)

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

在这里插入图片描述

（13）下载dbeaver工具
对于云主机，web界面防火墙规则，入站出站添加3306