MySQL的使用
CentOS7安装
首先卸载操作系统可能会自带的mariadb-libs
1
|
yum -y remove mariadb-libs
|
开始安装
1
2
3
4
5
6
7
8
9
|
# 官方下载地址
https://dev.mysql.com/downloads/mysql/5.7.html#downloads
tar -xvf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
rpm -ivh mysql-community-common-5.7.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.24-1.el7.x86_64.rpm
|
默认配置文件路径
1
2
3
4
|
配置文件:/etc/my.cnf
日志文件:/var/log/mysqld.log
服务启动脚本:/usr/lib/systemd/system/mysqld.service
socket文件:/var/run/mysqld/mysqld.pid
|
启动服务
查看初始密码
1
|
grep password /var/log/mysqld.log
|
登录mysql修改root密码
1
2
|
mysql -uroot -p
mysql> set password = password('Password_123');
|
设置远程登录权限
1
2
|
mysql> grant all privileges on *.* to 'root'@'%' identified by 'Password_123';
mysql> flush privileges;
|
修改mysql数据库默认编码
1
2
3
4
5
|
查看原数据库编码:mysql> SHOW VARIABLES LIKE 'char%';
可以看到数据库和服务端的编码都还不是utf8:
编辑/etc/my.cnf文件,在[mysqld]下面添加一行character-set-server=utf8
重启mysql服务:systemctl restart mysqld,再次登录数据库查看编码,修改成功
|
查看数据版本
1
|
show variables like '%version%';
|
基本操作
1
|
查询所有的用户 SELECT User, Host, Password FROM mysql.user; 快速创建数据库和用户CREATE DATABASE whatpulse DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci; GRANT ALL ON whatpulse.* TO 'whatpulse'@'%' IDENTIFIED BY 'whatpulse'; FLUSH PRIVILEGES;创建超级管理员 GRANT ALL ON *.* TO 'admin'@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION; FLUSH PRIVILEGES;
|
日期函数
1
|
-- 时间差计算-- FRAC_SECOND 表示间隔是毫秒-- SECOND 秒-- MINUTE 分钟-- HOUR 小时-- DAY 天-- WEEK 星期-- MONTH 月-- QUARTER 季度-- YEAR 年select timestampdiff(MINUTE,'2011-09-30','2015-05-04')
|
查看信息
1
|
-- 查看数据库的存储资源情况selecttable_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'from information_schema.tablesgroup by table_schemaorder by sum(data_length) desc, sum(index_length) desc;-- 查看data数据库存储路径show global variables like "%datadir%";
|
转移数据库文件
1
|
# 拷贝默认的数据库文件到自定义目录cp -a -R /var/lib/mysql /data# 修改配置文件vim /etc/my.cnf# 修改路径文件#datadir=/var/lib/mysql#socket=/var/lib/mysql/mysql.sockdatadir=/data/mysqlsocket=/data/mysql/mysql.sock# 重启服务systemctl restart mysqld# 修改配置,不然会出现没有权限操作/etc/selinux/configSELINUX=disabled
|