MySQL手册

MySQL手册

大白菜

简介

本手册指导MySQL相关组件的安装及设置，操作系统环境为RedHat 7.4 64bit

MySQL安装与设置

安装

• 下载MySQL安装包

wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar.gz
tar -zxvf mysql-8.0.18-el7-x86_64.tar.gz -C /usr/local
cd /usr/local/
mv mysql-8.0.18-el7-x86_64 mysql 

• 执行安装

vim /etc/my.cnf

修改为如下内容：

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

• 增加用户和组

groupadd mysql
useradd -g mysql -d /usr/local/mysql mysql

• 初始化安装目录

cd /usr/local/mysql
mkdir data
mkdir -p /usr/local/mysql/log
cd /usr/local/mysql/log
touch mysql.log
chown -R mysql:mysql /usr/local/mysql

• 执行数据库初始化

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
# 会回显数据库初始密码
root@localhost: xo-vbwd,6)4E

• 启动服务

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
service mysqld start

• 将mysql加到环境变量

vim ~/.bash_profile
# 增加如下内容
PATH=$PATH:/usr/local/mysql/bin
export PATH
# 设置生效
source ~/.bash_profile

• 修改密码

ALTER USER USER() IDENTIFIED BY '1qaz2wsx@dbc';

设置

增加可访问IP

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.101' IDENTIFIED BY '1qaz2wsx@dbc' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1qaz2wsx@dbc' WITH GRANT OPTION;
flush privileges;

设置编码及环境变量等

修改数据库密码

• mysqladmin命令

mysqladmin -u USER -p password

该命令之后会提示输入原密码，输入正确后即可修改。

• UPDATE user 语句

#UPDATE user SET password=PASSWORD('1qaz2wsx@dbc') WHERE user='root';
#FLUSH PRIVILEGES;

UPDATE user SET authentication_string = PASSWORD('1qaz2wsx@dbc') WHERE User='root';
commit;
FLUSH PRIVILEGES;

• SET PASSWORD

SET PASSWORD FOR root=PASSWORD('1qaz2wsx@dbc');

历史数据库导出

执行如下语句导出数据库表结构及数据

/usr/local/mysql/bin/mysqldump -u root -p ApolloConfigDB > /Users/dabaicai/Documents/ideaworkspace/apollo/ApolloConfigDB_20200214.sql

导入数据库建库脚本

# 创建数据库
mysql> create database IF NOT EXISTS mintleaf_fast DEFAULT CHARACTER SET UTF8;
# 使用已创建的数据库
mysql> use mintleaf_fast;
# 设置编码
mysql> set names utf8;
# 导入备份数据库
mysql> source /Users/dabaicai/Documents/ideaworkspace/mintleaf-fast/db/mintleaf_fast.sql;
mysql> source /Users/dabaicai/Documents/ideaworkspace/mintleaf-fast/db/database_reportcase.sql
# 或
mysql> source /Users/bobo_rb/Documents/ideaworkspace/mintleaf-fast/db/database_reportcase.sql