MYSQL如何搭建
这篇文章将为大家详细讲解有关MySQL如何搭建,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
成都创新互联公司专注于乌尔禾网站建设服务及定制,我们拥有丰富的企业做网站经验。 热诚为您提供乌尔禾营销型网站建设,乌尔禾网站制作、乌尔禾网页设计、乌尔禾网站官网定制、小程序制作服务,打造乌尔禾网络公司原创品牌,更为您提供乌尔禾网站排名全网营销落地服务。
mysql安装可以分为 1.rpm方式,2.tar压缩包方式,两者的差别rpm类似“一键安装”模式,tar方式则是”高级定义“,企业中大多数为“高级定义”方式安装。
rpm方式安装
一:前提准备
1.检测是否已经安装
rpm -qa | grep mysql
2.检测是否有mysql服务
ps -ef | grep mysql
3.检测是否可连接外网
ping www.baidu.com
4. 配置yum源
自带的yum源可以满足需要,如不满足需自行配置
二:安装步骤
1.搜索到源,如果报错可能是网络不通。
yum search mysql
2.在线安装
yum install -y mysql-server.x86_64 --服务端
yum install -y mysql.x86_64 --客户端
三:启动服务
1.service mysqld start (备注: mysqld ‘D’表示后台运行的进程)
2.查看服务,端口号:
ps -ef | grep mysql
netstat -nls | grep 3306
备注:查看到3306默认端口号即mysql服务启动
四: 进入用户
安装好后悔默认创建root用户,密码为空
[root@hadoop001 ~]# mysql -uroot -p
Enter password: 直接回车 -
备注:生成采用交互式输密码,防止明文history命令将密码明文。
tar方式安装mysql包
企业里专门的软件会有专门的用户去部署、运行
oa系统 oaadmin
erp系统 erpadmin
规范用户可以规范权限,易于管理
mysql 一个关系型数据库被oracle收购,有免费版有收费版。
oracle
postgresql
sqlserver
id name age
1 ruoze 18
2 20
二:tar方式安装
1.在联网情况下可以网络直接下载安装包
[root@sht-sgmhadoopnn-01 ~]# cd /usr/local
[root@sht-sgmhadoopnn-01 local]# wget https://downloads.mariadb.com/archives/mysql-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
[root@sht-sgmhadoopnn-01 local]# wget https://downloads.mariadb.com/archives/mysql-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz.md5
--这里我们用专用包下载
mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
2.本次安装采用现成的安装包进行安装
secureCRT中输入rz命令
1. root用户进入目录
[root@sht-sgmhadoopnn-01 ~]# cd /usr/local
2.用md5检测包的完整性。
[root@sht-sgmhadoopnn-01 local]# cat mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz.md5 --原始包中自带的md5值
61affe944eff55fcf51b31e67f25dc10 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
[root@sht-sgmhadoopnn-01 local]# md5sum mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
61affe944eff55fcf51b31e67f25dc10 mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
2. 检测是否已经安装
[root@sht-sgmhadoopnn-01 local]# ps -ef|grep mysqld
root 2493 2423 0 19:48 pts/3 00:00:00 grep mysqld
[root@sht-sgmhadoopnn-01 local]# rpm -qa |grep -i mysql
3.解压包到指定目录,然后重命名
[root@sht-sgmhadoopnn-01 local] cd /usr/local
[root@sht-sgmhadoopnn-01 local] tar xzvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz --路径是root用户下/usr/local
[root@sht-sgmhadoopnn-01 local]# mv mysql-5.6.23-linux-glibc2.5-x86_64 mysql --将解压后的包重命名为mysql(因为之后的mysqladmin用户家目录是 /usr/loca/mysql,这样在su - mysqladmin切换到家目录后,直接进入解压后的包目录,便于安装.)
4.创建用户组
[root@sht-sgmhadoopnn-01 local]# groupadd -g 101 dba
[root@sht-sgmhadoopnn-01 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. --由于用户已经存在会导致环境变量不会在家目录中存在,坑!
## if user mysqladmin is existing,please execute the following command of usermod.
##[root@sht-sgmhadoopnn-01 local]# usermod -u 514 -g dba -G root -d /usr/local/mysql
[root@sht-sgmhadoopnn-01 local]# userdel mysqladmin --如用户已存在则删除
5.为用户创建密码
[root@sht-sgmhadoopnn-01 local]# id mysqladmin
uid=514(mysqladmin) gid=101(dba) groups=101(dba),0(root)
[root@sht-sgmhadoopnn-01 local]# passwd mysqladmin
6.拷贝环境变量.bash_profile等,如果ls -lart有,此步骤可以省略
## copy 环境变量配置文件至mysqladmin用户的home目录中,为了以下步骤配置个人环境变量
[root@sht-sgmhadoopnn-01 local]# cp /etc/skel/.* /usr/local/mysql --将环境变量拷贝到mysqladmin家目录下
cp: omitting directory `/etc/skel/.'
cp: omitting directory `/etc/skel/..'
cp: omitting directory `/etc/skel/.mozilla'
6.1配置环境变量
[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
export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH
export PATH
ps1=`uname -n`":"$USER":"$PWD":>"; --注意变量名要大写
export PS1
#‘:’是分隔符,每次加环境变量要放到$PATH的前面(新变量放在前面是因为linux命令执行是按$PATH的先后顺序查找的,如果$PATH中已经包含老版本的变量值,优先寻找的还是老版本的环境变量),$PATH的意思原先的环境变量+新的变量
使变量生效
. .bash_profile
su - mysqladmin
拷贝环境变量后exit用户
重新进入su - mysqladmin 可以看到当前光标处用户路径已经带出来
7.配置mysql主配置文件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/ --在etc目录下建立my.cnf
[root@sht-sgmhadoopnn-01 etc]# touch my.cnf
配置文件内容:
[root@sht-sgmhadoopnn-01 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
#生产上 机械硬盘
#sata盘 5000r 7200r(大众生产) 10000r(大众生产) 15000r 有条件用ssd
# innodb_buffer_pool_size 生产调优调大 8G
innodb_buffer_pool_size = 1024M #free命令查看服务器内存,再适度分配
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
---------------------------
8.更改my.cnf权限
[root@sht-sgmhadoopnn-01 local]# chown mysqladmin:dba /etc/my.cnf
[root@sht-sgmhadoopnn-01 local]# chmod 640 /etc/my.cnf
[root@sht-sgmhadoopnn-01 etc]# ll my.cnf
-rw-r----- 1 mysqladmin dba 2201 Aug 25 23:09 my.cnf
9.更改家目录下用户、组的权限
[root@sht-sgmhadoopnn-01 local]# chown -R mysqladmin:dba /usr/local/mysql
[root@sht-sgmhadoopnn-01 local]# chmod -R 755 /usr/local/mysql
10. 切换到mysql家目录,并创建arch目录,arch目录是在my.cnf指定的
root@sht-sgmhadoopnn-01 local]# su - mysqladmin
[mysqladmin@sht-sgmhadoopnn-01 ~]$ pwd
/usr/local/mysql
创建arch目录用来存储binlog二进制归档日志 mysql--maxwell--kafka--sparkstrm--hbase 实时
cd /usr/local/mysql
mkdir arch
11.开始安装,用家目录下的脚本
[mysqladmin@sht-sgmhadoopnn-01 ~]$ scripts/mysql_install_db
Installing MySQL system tables..../bin/mysqld:
error while loading shared libraries: libaio.so.1: cannot open shared object file:
No such file or directory #缺少libaio.so 包
====================
缺少包解决方法:
1.查看版本
[root@sht-sgmhadoopnn-01 local]# cat /proc/version
Linux version 2.6.18-164.11.1.el5 (mockbuild@builder10.centos.org) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)) #1 SMP Wed Jan 20 07:32:21 EST 2010
2.查看是否有gcc包
[root@sht-sgmhadoopnn-01 local]# rpm -qa |grep gcc
libgcc-4.1.2-46.el5_4.2
libgcc-4.1.2-46.el5_4.2
3.安装对应包
[root@sht-sgmhadoopnn-01 local]# yum -y install libaio
假设前面安装错误再次安装,需要清空两个目录遗留文件:
rm -rf data/*
rm -rf arch/*
=====================
12.开始安装,为保险起见,指定了执行路径,实在是不用指定的。
[mysqladmin@sht-sgmhadoopnn-01 ~]$ scripts/mysql_install_db \
--user=mysqladmin \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
查看data/hostname.err文件 仔细错误
或者:
scripts/mysql_install_db \
--user=mysqladmin \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--defaults-file=/etc/my.cnf --手工指定defaults-file方法
或者:
[root@bogon scripts]# /usr/local/mysql/scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data &
备注:因为mysql家目录为/usr/local/mysql,而我们上面步骤已经将tar包更名为mysql,在su - mysqladmin切换到家目录时,已经进入tar解压后的安装包,此时可以看到script目录
13.配置mysql开机自启
[root@sht-sgmhadoopnn-01 ~]# cd /usr/local/mysql
#将服务文件拷贝到init.d下,并重命名为mysql
[root@sht-sgmhadoopnn-01 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysql
#赋予可执行权限
[root@sht-sgmhadoopnn-01 mysql]# chmod +x /etc/rc.d/init.d/mysql
#删除服务
[root@sht-sgmhadoopnn-01 mysql]# chkconfig --del mysql
#重新添加服务
[root@sht-sgmhadoopnn-01 mysql]# chkconfig --add mysql
#添加开机启动服务
[root@sht-sgmhadoopnn-01 mysql]# chkconfig --level 345 mysql on
[root@sht-sgmhadoopnn-01 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"
---------------
[root@hadoop ~]# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql
[root@hadoop ~]# chmod +x /etc/rc.d
rc.d/ rc.local rc.sysinit
[root@hadoop ~]# chmod +x /etc/rc.d/init.d/mysql
[root@hadoop ~]# ll /etc/rc.d/init.d/mysql
-rwxr-xr-x 1 root root 10880 Jan 29 12:47 /etc/rc.d/init.d/mysql
---------------
9. 启动mysql
[root@sht-sgmhadoopnn-01 mysql]# su - mysqladmin
[mysqladmin@sht-sgmhadoopnn-01 ~]$ pwd
/usr/local/mysql
[mysqladmin@sht-sgmhadoopnn-01 ~]$ rm -rf my.cnf #保留也可以,因为目录下已经有了
[mysqladmin@hadoop001 ~]$ service mysql start #开启服务
Starting MySQL. [ OK ]
[mysqladmin@hadoop001 ~]$ service mysql status
MySQL running (3625) [ OK ]
或者:
#另一种安全模式启动,同样是可以的
[mysqladmin@sht-sgmhadoopnn-01 ~]$ mysqld_safe & #安全模式开启,另一种开启方式,执行后光标处回车
[1] 11802
[mysqladmin@sht-sgmhadoopnn-01 ~]$ 150825 22:53:38 mysqld_safe Logging to '/usr/local/mysql/data/hostname.err'.
150825 22:53:38 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/
150825 22:53:39 mysqld_safe mysqld from pid file /usr/local/mysql/data/hostname.pid ended
[mysqladmin@sht-sgmhadoopnn-01 ~]$ which mysqld_safe
~/bin/mysqld_safe #可以看到mysqld_safe的目录
10. 查看服务监听
[mysqladmin@sht-sgmhadoopnn-01 ~]$ ps -ef|grep mysqld --是mysqld不是mysql
514 6247 6219 0 17:30 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
514 6902 6247 2 17:30 pts/1 00:00:01 /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 #3306说明已经启动
514 6927 6219 0 17:31 pts/1 00:00:00 grep mysqld
查看监听
[mysqladmin@sht-sgmhadoopnn-01 ~]$ netstat -tulnp | grep mysql #查看端口
(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 11541/mysqld
[root@sht-sgmhadoopnn-01 local]# service mysql status
MySQL running (21507) [ OK ]
至此部署完成
备注:
mysql会创建两个用户
1.root用户 密码为空 要更改密码
2.空用户 密码为空 要删除该用户
登录方式:
#注意事项1.不用明密码登录。2.-p等参数不能加空格
bin/mysql -uroot -p -h227.0.0.1 #回车输入密码 此方式安全不会被history记录
bin/mysql -uroot -p123456 -h227.0.0.1 #此方式不安全的 会被history记录
bin/mysql -u root -p 123456 -h 127.0.0.1 -P3306 #加端口号方式,此方式不安全,127.0.0.1是自己的电脑ip
11. 登录mysql
[mysqladmin@sht-sgmhadoopnn-01 ~]$ bin/mysql -uroot -p
也可以进入/usr/local/mysql/bin目录,./mysql -uroot -p表示执行当前路径下的mysql ./是相对路径 。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
12.更改root密码,删除空用户
mysql> use mysql
Database changed
安全起见要做两件事
1.更改root的密码不为空
2.删除空用户
mysql> update user set password=password('123456') where user='root'; #将root用户更改密码,passwodr('123456')是加密
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select host,user,password from user;
+----------------+------+-------------------------------------------+
| host | user | password |
+----------------+------+-------------------------------------------+
| localhost | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F |
| sht-sgmhadoopnn-01 | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F |
| 127.0.0.1 | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F |
| ::1 | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F |
| localhost | | |
| sht-sgmhadoopnn-01 | | |
+----------------+------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> delete from user where user=''; #删除空用户
mysql> select host,user,password from user;
+----------------+------+-------------------------------------------+
| host | user | password |
+----------------+------+-------------------------------------------+
| localhost | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F |
| sht-sgmhadoopnn-01 | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F |
| 127.0.0.1 | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F |
| ::1 | root | *6340BE3C15D246B0D74BAF3F135915ED19E0069F |
+----------------+------+-------------------------------------------+
4 rows in set (0.00 sec)
#针对用户 权限的操作语句最后一步执行刷新权限
mysql> flush privileges;
13.重新登录
bin/mysql -uroot -p -hlocalhost
14.Configure .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
export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH
export PATH
ps1=`uname -n`":"$USER":"$PWD":>"; --注意变量名要大写
export PS1
#‘:’是分隔符,每次加环境变量要放到$PATH的前面(新变量放在前面是因为linux命令执行是按$PATH的先后顺序查找的,如果$PATH中已经包含老版本的变量值,优先寻找的还是老版本的环境变量),$PATH的意思原先的环境变量+新的变量
使变量生效
. .bash_profile
su - mysqladmin
关于“MYSQL如何搭建”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。
本文名称:MYSQL如何搭建
网站URL:http://myzitong.com/article/jgpdoh.html