MySQL数据库

TOC

一、安装MySQL数据库

编译安装

• 准备工作
  安装包：mysql-boost-5.7.36.tar.gz
  mysql下载地址：https://downloads.mysql.com/archives/community/
  安装epel源，这样方便安装一些依赖包，要不然yum会没有一些包，再安装所需要的编译工具

yum install -y epel-release
yum clean all && yum makecache
yum -y install gcc gcc-g++ cmake make

如果是Ubuntu系统则需要apt安装编译工具

apt -y install cmake gcc make

现在就可以正式开始安装了。。。。。。

• 安装MySQL
  1.yum和apt安装mysql所需要的依赖包
  CentOS需要yum安装mysql所需要的依赖包

yum -y install openssl openssl-devel prce pcre-devel bison bison-devel ncurses-devel

如果是ubuntu则需要apt安装mysql所需要的依赖包

apt -y install libncurses5-dev bison openssl libssl-dev pkg-config

2.创建mysql用户给mysql程序使用

groupadd mysql && useradd -M -s /sbin/nologin mysql -g mysql

3.解压安装包，使用cmake工具编译安装mysql

#解压安装包
tar zxf mysql-boost-5.7.36.tar.gz && cd mysql-5.7.36
#cmake编译安装mysql
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_BOOST=boost -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
make && make install

注意：在编译的时候注意一些参数跟下面第4点和第5点的参数和目录有联系，修改需谨慎，免得服务起不来找问题麻烦

编译的时间可能会等的有点久.。。。。。。就耐心等待吧！！！

4.使用mysql自带的mysqld命令初始化数据库,准备工作

/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/run/mysql.pid --initialize

这里初始化完成之后你会发现在你安装mysql的目录会自动生成data目录，这里还加载了mysql的pid文件的生成目录，记载mysql的pid进程号

5.创建准备mysql的一些工作目录

#创建日志目录并创建错误日志文件
mkdir -p /usr/local/mysql/run /usr/local/mysql/logs
touch /usr/local/mysql/logs/mysql_error.log
#把mysql的安装目录属主改为mysql，授权mysql程序pid运行目录755
chown -R mysql.mysql /usr/local/mysql
chmod 755 /usr/local/mysql/run

6.编辑mysql的配置文件

vim /etc/my.cnf
-----------------------------------------
[client] 　
socket = /usr/local/mysql/mysql.sock
[mysqld]
skip-grant-tables
port = 3306
user = mysql
character-set-server=utf8
socket = /usr/local/mysql/mysql.sock
pid-file = /usr/local/mysql/run/mysql.pid
log-error = /usr/local/mysql/logs/mysql_error.log
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
[mysqld_safe]
pid-file = /usr/local/mysql/run/mysql.pid
log-error = /usr/local/mysql/logs/mysql_error.log

7.启动服务并初始化服务密码
启动脚本在/usr/local/mysql/support-files这个目录下

/usr/local/mysql/support-files/mysql.server start

查看服务启动状态（mysql默认是3306端口）
netstat -anptu |grep 3306
登录数据库设置root密码

mysql -uroot -p

因为配置文件设置了skip-grant-tables参数，所以不需要密码直接进入，开始设置密码

flush privileges;
#进入mysql库下，修改root用户密码为123456
use mysql;
set password for root@localhost=password('123456');
#默认是只能本地登录，授权root用户所有地址都能登录
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;

修改配置文件把skip-grant-tables这个参数注释掉（前面加上#号就行）或者删除，再重启服务
#skip-grant-tables
这里大家可以先去完成第8步直接使用systemctl命令重启

pkill mysqld
/usr/local/mysql/support-files/mysql.server start

这里我们就会发现不要密码的话就无法登录了，提示密码错误如下图
mysql安装就完成了，可以使用了！！！
8.添加mysqld服务到systemctl并优化服务命令路径（补充，也可以不要这一步骤，看个人）

#mysqld服务加入systemctl方便管理,跟上面httpd服务原理一样
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
systemctl restart mysqld
systemctl status mysqld
#优化命令路径
ln -s /usr/local/mysql/bin/* /usr/bin

三、MySQL用户和密码管理

mysql登陆与修改密码

登陆数据库命令：mysql -h hostname -P port -u username -p password
-h:ip地址，不加该选项默认本地为127.0.0.1
-P:端口号，不加该选项默认端口号为3306
-u:登陆用户名      -p:登陆密码
初始化密码命令：mysqladmin -uroot password ‘123456’</fon**t>
修改mysql密码

• 方法1（mysql命令工具修改）:mysqladmin -uroot -p123456 password ‘654321’
• 方法2（登陆mysql修改表信息）:
  1.登陆mysql进入mysql库

mysql -uroot -p123456 -D mysql

2.修改表数据

set password for root@localhost=password('654321');
或者
update user set password=PASSWORD('654321') where User='root';

用户以及密码管理

新建用户：create user ‘username’@’%’ identified by ‘password’
删除用户：drop user ‘username’@’%’
重命名用户：rename user ‘old_user’@‘host’  to ‘new_user’@'host
设置当前登录用户的密码：set password=password(‘123456’)
设置其他用户的密码：set password=‘username’@‘host’=password(‘123456’)
用户授权
使用grant进行授权，语法格式：
grant【权限列表】on【库名】.【表名】to【用户名】@【地址限制】
权限列表有多个时用“,”符号分隔，库名和表名所有用"*“符号表示比如test库的所有表：test.*，地址限制所有用”%"符号表示比如192.168.1.0的网段：192.168.1.%
权限列表：

• usage：连接登陆权限，建立一个用户，就会自动授予其usage权限，该权限只能用于数据库的登陆，不能执行任何操作，且usage权限不能被回收，也即revoke用户并不能删除用户
• file：file不给，有严重的安全隐患
• super：super不给，有严重的安全隐患
• select：查询表数据
• insert：插入权限
• update：修改表数据
• delete：删除行权限
• alter：修改表的结构
• alter routine：更改或者删除存储函数或者存储过程，隐式包含drop的权限
• create：创建表的权限
• drop：删除库，表，索引，视图的前戏
• create routine：创建存储函数或者存储过程的权限
• create temporay tables：创建临时表
• create view：创建视图
• create user：创建用户
• show database：查看拥有的数据库
• index：必须拥有index权限，才能执行【create|drop index】
• show view：查看视图
• excute：以用户执行存储过程的权限
• event：表示拥有创建，修改，执行和删除事件（event）的权限
• lock tables：锁表的权限
• references：用户可以将其他的一个字段作为某一个表的外键约束
• reload：必须拥有reload权限，才能flush[tables|logs|privileges]
• replication client：拥有此权限可以查询到master server，slave server状态
• replication slave：拥有此权限可以查看从服务器，从主服务器读取二进制日志
• shutdown：关闭mysql的权限
• grant option：拥有grant权限，就可以将自己拥有的权限授予给其他用户
• process：查看所有用户线程/连接的权限
• all privileges：所有权限

比如：赋予test用户对test库所有表有增删改查的权限，限制地址为所有

grant select,insert,update,delete on test.* to 'test'@'%';

后面也可以加上identified by 'password’设定密码

破解root密码

1.停止服务修改数据库配置文件

systemctl stop mariadb
vim /etc/my.cnf
-------------------------
skip-grant-tables

2.启动服务登陆数据库

systemctl start mariadb
mysql -uroot –p

3.修改root密码

use mysql
update user set password=PASSWORD('123456') where User='root';
flush privileges;

4.修改配置文件注销skip-grant-tables选项重启服务

systemctl restart mariadb

★错误集锦★

1.大量删除表数据不但磁盘空间没有降，反而涨了不少!!!

当我们在执行删除大量数据的时候，虽然我们删除了数据库上面的数据，磁盘上的冗余数据依然存在，所以并没有释放本地的磁盘空间，所以导致以上问题。
解决以上问题的方法就是删除完大量数据后释放空间。
释放空间方式：

# 删除表中的所有数据且不影响表的结构，也不会记录日志，因此速度快
TRUNCATE TABLE table_name;
# 删除整个表，并释放所有空间
DROP TABLE table_name;
# 简化表，删除没有使用的空间，可以减少表的大小，释放表空间，此命令会锁定表，直到它完成
OPTIMIZE TABLE table_name;

注意：以上方法都无法撤回，需要谨慎使用