MySQL 安装及配置
MySQL 安装
二进制包安装
#1.下载二进制包
#2.环境准备(创建目录、用户等)
mkdir -p /beicen/db/mysql/data/{data,log,tmp}
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
ln -s /beicen/db/mysql/mysql-5.7.34-el7-x86_64/ /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /beicen/db/mysql
vim /beicen/db/mysql/my.conf
/usr/local/mysql/bin/mysqld --defaults-file=/beicen/db/mysql/my.conf --initialize --user='mysql' --log_error_verbosity --explicit_defaults_for_timestamp
mysqld_safe --defaults-file=/beicen/db/mysql/my.conf &
RPM包安装(redhat系列系统)
# 下载安装包
# 可以自行选择下载的版本 https://repo.mysql.com/yum/
wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.31-1.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.31-1.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.31-1.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.31-1.el7.x86_64.rpm
# 安装(U:升级安装,如果安装检查依赖失败:--nodeps参数)
rpm -Uvh --nodeps mysql-community-libs-5.7.31-1.el7.x86_64.rpm
rpm -Uvh --nodeps mysql-community-common-5.7.31-1.el7.x86_64.rpm
rpm -Uvh --nodeps mysql-community-client-5.7.31-1.el7.x86_64.rpm
rpm -Uvh --nodeps mysql-community-server-5.7.31-1.el7.x86_64.rpm
初始化配置
数据目录修改
#数据目录默认为: /var/lib/mysql,因数据存储量等原因修改
#1.方式一:创建软连接,指向具体磁盘位置
mkdir /data/dbs/mysql57/data_local #创建新的数据存储目录
chown mysql:mysql /data/dbs/mysql57/data_local #修改目录权限用户
systemctl stop mysqld.service #停mysql服务
mv /var/lib/mysql/* /data/dbs/mysql57/data_local
#启动服务,查看环境变量
systemctl start mysqld.service
mysql -uroot -p
> show variables like '%data%'; #查看指定变量
账号权限管理
#1.查看安装时提供的root临时密码
grep 'temporary password' /var/log/mysqld.log
#2.通过mysql进行登录
mysql -u root -p'临时密码'
#3.修改root@localhost密码(密码规则:大小字母+小写字母+特殊符+数字)
set password for 'root'@'localhost' = password('XXXX');
#4.创建账号或添加作用域(如:root%),根据实际情况设置host访问
create user 'root'@'%' identified by 'xxxx';
#5.赋予账号权限
grant all on *.* to 'root'@'%';
#6.刷新权限
FLUSH PRIVILEGES;
参数配置及优化
[mysql]
default-character-set = utf8
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
performance_schema_max_table_instances=800
table_definition_cache=800
table_open_cache=512
#服务端编码
character_set_server=utf8
#不区分大小写
lower_case_table_names=1
#最大连接数
max_connections=1024
key_buffer_size=512M
innodb_buffer_pool_size=512M
数据库基础操作
#创建数据库
create database xxx character set utf8mb4;
#指示命令
show databases/tables
desc table
数据备份及初始化
数据备份
- mysqldump
mysqldump -h 127.0.0.1 -u root -p 数据库名称 > 文件名称.sql
数据还原
- source 说明:如果文件过大建议使用,修改相关参数提高还原效率
#参数优化
#客户端/服务器之间通信的缓存区的最大大小
set global max_allowed_packet=100000000;
#TCP/IP 和套接字通信缓冲区大小,创建长度达 net_buffer_length 的行
set global net_buffer_length=100000;
#对后续起的交互链接有效时间
set global interactive_timeout=28800000;
#对当前交互链接有效时间
set global wait_timeout=28800000;
#1.进入数据库
mysql -uroot -p
#2.切换需要还原数据的库
use xxx
#3.使用source
source xxx/xxx.sql
常见问题解决
Can't start server: Bind on TCP/IP port: Permission denied
#1.修改后重启服务器
vim /etc/selinux/config
SELINUX=disabled
#2.临时修改
setenforce 0
