🗺️博客地图
一、MySQL的安装方式
（一）RPM包安装
yum -y install mysql mysql-server
（二）源码包安装
# 安装ncurses
注：ncurses提供字符终端处理库，包括面板和菜单。它提供了一套控制光标，建立窗口，改变前景背景颜色以及处理鼠标操作的函数。使用户在字符终端下编写应用程序时绕过了那些恼人的底层机制。简而言之，ncurses是一个可以使应用程序直接控制终端屏幕显示的函数库。
# 若不安装ncurses编译MySQL时会报错
yum -y install ncurses-devel
tar -zxvf ncurses-5.9.tar.gz
cd ncurses-5.9/
./configure --with-shared --without-debug --without-ada --enable-overwrite
• 参数解释：
--without-ada 为设定不编译为ada绑定，因进入chroot环境不能使用ada
--enable-overwrite 为定义把头文件安装到/tools/include目录下而不是 /tools/include/ncurses目录
--with-shared 生成共享库
# 编译安装
make -j 6 && make install -j 6
# 安装cmake和bison
yum -y install cmake bison
# 安装MySQL
useradd -s -M /sbin/nologin mysql
tar -zxvf mysql-5.5.48.tar.gz
cd mysql-5.5.48/
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306
• 参数解释：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 安装位置
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock 指定socket（套接字）文件位置
-DEXTRA_CHARSETS=all 扩展字符支持
-DDEFAULT_CHARSET=utf8 默认字符集
-DDEFAULT_COLLATION=utf8_general_ci 默认字符校对
-DWITH_MYISAM_STORAGE_ENGINE=1 安装myisam存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 安装innodb存储引擎
-DWITH_MEMORY_STORAGE_ENGINE=1 安装memory存储引擎
-DWITH_READLINE=1 支持readline库
-DENABLED_LOCAL_INFILE=1 启用加载本地数据
-DMYSQL_USER=mysql 指定mysql运行用户
-DMYSQL_TCP_PORT=3306 指定mysql端口
# 编译安装
make -j 6 && make install -j 6
# 修改MySQL目录权限
cd /usr/local/mysql/
chown -R root .
chown -R mysql data/
# 复制mysql配置文件
cp /root/mysql-5.5.48/support-files/my-medium.cnf /etc/my.cnf
# 创建数据库授权表，初始化数据库
/usr/local/mysql/scripts/mysql_install_db --user=mysql
# 启动MySQL服务并放入后台运行
/usr/local/mysql/bin/mysqld_safe --user=mysql &
# 设置开机自启动MySQL
vim /etc/rc.local
/usr/local/mysql/bin/mysqld_safe --user=mysql &
# 设定MySQL密码
/usr/local/mysql/bin/mysqladmin -uroot password 123
# 为调用方便，建立软链接文件
ln -s /usr/local/mysql/bin/* /usr/local/bin
#测试MySQL数据库能否使用
mysql -uroot -p123
mysql> show databases;• 基本信息
①主配置文件：
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql #数据库所在位置
socket=/var/lib/mysql/mysql.sock #套接字文件位置
user=mysql #用户名
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0 #是否允许链接
skip-grant-table #跳出权限表功能（忘记数据库root登录密码时使用）
[mysqld_safe]
log-error=/var/log/mysqld.log #启动日志位置（主要记录启动时的报错信息）
pid-file=/var/run/mysqld/mysqld.pid #pid文件保存位置
②默认端口：3306
③数据目录：/var/lib/mysql
ibdata1 #数据库索引文件
ib_logfile0和ib_logfile1 #mysql和test数据库的索引文件
mysql和test #数据库mysql和test（测试数据库）
mysql.sock #套接字文件
# MYSQL创建的库就相当于在 /var/lib/mysql下创建一个目录（mysql与test）
④MySQL数据库中的一张表在文件系统中会以3个文件展示：
• frm：当前表的结构
• MYD：当前数据表的数据
• MYI：当前数据表的索引
二、SQL 语句
（一）用户级别的操作
选项：
-p 密码
-P 端口，默认3306
-h 登录地址
-S 指定套接字
-u 指定用户
#登录
mysql -u用户名 -p密码 -h 服务器地址 -P 端口 -S 套接字
例：mysql -uroot -p123 -h 127.0.0.1 -P 3306 -S /var/lib/mysql/mysql.sock
#创建用户
create user '用户名'@'登录地址' identified by '密码';
例：create user 'user1'@'%' identified by '123';
• 登录地址支持内容：
①所有地址都可以访问，设置两条
localhost
%
②本地访问：
localhost 127.0.0.1
③远程访问：
%
④固定IP访问：
192.168.66.12
#root修改密码
①修改root密码
登录mysql之后
mysql> set password=password("456");
②修改普通用户密码
登录mysql之后
mysql> set password for '用户名'@'登录地址'=password("密码");
例：set password for 'user2'@'%'=password("789");
#普通用户修改密码
登录mysql之后
mysql> set password=password("qwe");
#管理员的密码找回
物理机：备份完全一致的数据库内容，在备份上进行破解
虚拟机：直接导出虚拟机模板进行破解，或拍摄快照进行破解
• 破解步骤:
①关闭数据库服务
service mysqld stop
②修改主配置文件，在mysqld区域中添加记录
vim /etc/mycnf
[mysqld]
skip-grant-table
③重启服务
service mysqld start
④登录mysql使用命令修改密码
mysql #回车直接登录
mysql> update mysql.user set password=password("456") where user='root'; #更换root密码
mysql> exit
⑤恢复配置文件
vim /etc/mycnf
删除skip-grant-table
⑥重启服务用新密码登录
service mysqld startmysql -uroot -p456
（二）数据级别的操作
1.增
#创建数据库
create database 数据库名;
#创建表
create table 表名 (字段1 int(3)，字段2 char(200));
#向指定表内插入数据
insert into 表名 (字段1,字段2) values (值1,"值2");
#向表中插入数据，只为字段2的插入数据
insert into 表名 (字段2) values ("值2");
#省略输入字段插入数据，必须与字段对应吻合
insert into 表名 values (值1，"值2");
#批量插入
insert into 表名 values (值1，"值2"),(值3，"值4");
#例子：创建表
create table users (id int unsigned not null auto_increment, name char(40) not null default '未知'，
age int not null default '0',
primary key (id));
• 字段解释：
#int unsigned 正整型
#not null 非空
#auto_increment 自增长
#char(40) 字符串类型（限制40字符长度）
#default '未知' 不填写默认填入未知
#primary key (id) 设置id为主键
#将表2指定字段插入表1，字段名必须相同
insert into 表1 (字段1) select 字段1 from 表2;
2.删
#删除数据库
drop database 库名;
#删除表
drop table 表名;
delet from 表名;
#删除表中字段等于数值的记录，删除行
delete from 表名 where 字段=数值;例：删除id=1时的数据行
delete from users where id=1;
#删除字段，删除列
alter table 表名 drop 字段;
3.改
#更新表字段2等于数值2的所有字段1的数值为数值1
update 表名 set 字段1=数值1 where 字段2=数值2;
例：update users set age=20 where id=5;
#更改users表中id=5的age字段
#更新表字段2在1到5之间的所有字段1的数值为数值1
update 表名 set 字段1=数值1 where 字段2 between 1 and 5;
例：update users set age=25 where id=5;
#更改users表中id在1-4范围内的age字段
#修改表名
alter table 表1名 rename 表2名;
#修改表指定字段类型
alter table 表名 modify 字段 类型;
例：alter table users modify age int(20);
#修改users表中age字段类型为整数型，限制20字符长度
#修改表中字段名
alter table 表名 change 原字段名 字段名 char(40) not null default ' ';
#在表中添加一个字段
alter table 表名 add 字段1 字段类型;
例：alter table users add addr char(200);
#删除表中的date字段
alter table 表名 drop 字段名;
例：alter table users drop addr;
#添加字段到表中的第一列
alter table 表名 add 字段1 字段类型 first;
例：alter table users add addr char(200) first;
#添加一个字段到某一字段之后
alter table 表名 add 字段1 字段类型 after 字段2;
例：alter table users add date year after age;
#在age字段后添加date字段
4.查
#查看数据库
show databases;
#查看数据表
show tables;
#查看表结构
describe 表名;
#查看表数据
select * from 表名;
#在本库下查看其他库下的表
select * from 其他库.其他库下的表;
5.授权
#授权一个已存在的用户zhangsan可以远程登录并给予最大权限
grant all on *.* to 'zhangsan'@'%';
#创建并授权lisi用户允许在任何地方登陆给予最大权限并设置密码为123
grant all on *.* to 'lisi'@'%' identified by '123';
#授权一个已存在的用户可以远程登录并给予create，delete，drop，select，all，grant权限
grant create,delete,drop,select on *.* to 'lisi'@'%' identified by '123';
#查看zhangsan用户在远程登陆的权限
show grants for 'zhangsan'@'%';
#取消zhangsan在远程创建使用test库的权限
revoke create on test.* from 'lisi'@'%';
• 项目赋权建议
权限：replication slave或all
数据库.表：合理同步数据库下的表
用户：建议使用master
登录地址：建议使用%
密码：符合密码设置三原则
三、数据库备份
（一）文件级别备份
注意：需要离线执行，必须终止数据库读写执行操作
文件级别备份的工具：cp、scp、rsync、raid5、lvm快照功能
#先停止数据库服务
service mysqld stop
#cp备份
cp -a /var/lib/mysql /home/mysqlbak
#启动服务
service mysqld start
#删除数据库
mysql -uroot -p123
mysql> show databases;
mysql> drop database muzi;
mysql> exit
#恢复数据库
rm -rf /var/lib/mysql/*
cp -a /home/mysqlbak/mysql/* /var/lib/mysql/
#查看是否恢复
service mysqld start
mysql -uroot -p123
mysql> show databases;
mysql> select * from muzi.users;
（二）逻辑备份
实质是将数据库文件转储为sql文件
1.Navicat备份
#在192.168.43.20备份旧库
#windows主机登录Navicat:192.168.43.20
选中指定数据库 > 状态栏点击备份 > 新建备份 > 写入注释 > 点击开始 > 等待备份完成
备份文件所在位置：此电脑\文档\Navicat\MySQL\servers\mysql\muzi\***.psc
#在192.168.43.30主机上安装数据库
yum -y install mysql mysql-server
mysqladmin -uroot password 123
mysql -uroot -p123
#授权root用户远程登录
mysql> grant all on *.* to 'root'@'%' identified by '123';
#还原库
注：用.psc文件还原库需要先创建库
create database muzi;
#Windows主机登录Navicat：192.168.43.30
#muzi——备份——还原备份——选择要还原的.psc文件——开始——确定
#在192.168.43.30主机测试：
mysql> select * from muzi.users;
#创建一个代码连接数据库的用户
grant all muzi.* to ‘php’@‘192.168.43.40’ identified by ‘123’;
2.mysqldump
支持所有存储引擎，备份会消耗CPU
#备份数据库
mysqldump -uroot -p123 数据库名 > 备份文件名.sql
#还原数据库到test
mysql -uroot -p123 数据库名 < 备份文件.sql
#备份数据库并且之备份表1
mysqldump -uroot -p 数据库名 表1 > 备份文件名.sql
#将多个数据库同时备份到备份文件
mysqldump -uroot -p --databases 数据库1 数据库2 > 备份文件.sql
#将所有的数据库进行备份
mysqldump -uroot -p --all-databases > 备份文件.sql
• mysqldump的备份恢复实验：
#实验前准备
mysql -uroot -p123
mysql> use test
mysql> 创建表，插入数据
#备份数据库
mysqldump -uroot -p123 test > test.sql
#删除数据库
mysql -uroot -p123
mysql> use test
mysql> select * from users;
mysql> drop database test;
#还原库
mysql -uroot -p123 test < test.sql
3.mysqlhotcopy
通过拷贝文件的方式备份数据库，不支持innodb存储引擎，支持myisam存储引擎，支持速度依赖磁盘i/o，速度快
注意：表名不能用中文，否则mysqlhotcopy会报错
mysqlhotcopy --flushlog -u='root' -p='123' --regexp=^a /root
• 参数说明：
#--flushlog #刷新日志信息
#-u='root' #指定用户
#-p='123' #指定密码
#--regexp=^a #指定正则表达为名字开头为a的库
#/root #指定备份文件目录
（三）日志级别备份
#查询开启的日志信息
mysql> show global variables like '%log%';
log_bin 二进制日志：通过二进制存储数据，会记录对数据库数据结构发生改变的SQL语句，也会记录进入数据库（use 数据库名）的SQL语句
general_log 查询日志：一般查询日志，会记录所有的SQL语句
正常情况下关闭，开发环境开启
show_query_log 慢查询日志：记录较慢的查询，根据时间情况指定慢查询时间，记录查询速度较慢的SQL语句，默认10s
log_error 错误日志：启动日志，记录服务器MYSQLD守护进程的报错信息、启动和关闭信息
innodb_log 事务日志：innodb提供实现事物操作
relay_log 中继日志：二进制日志的翻版，在mysql主从结构中出现
#启用一般查询日志
vim /etc/my.cnf
[mysqld]
#在[mysqld]区域下添加
general_log=ON
log=ON
#启动mysql服务
service mysqld restart
#
mysql -uroot -p123
mysql> create database li;
mysql> use li
mysql> create table users (id int,name char(200));
mysql> insert into users values (1,"zhang");
mysql> select * from users;
mysql> show database;
#查看一般查询日志
vim /etc/lib/mysql/ON
#启用慢查询日志
vim /etc/my.cnf
[mysqld]
long-show-queries=/var/mysql-show.log
long_query_time=4service mysqld restart
mysql -uroot -p123
mysql> show global variables like '%long%';
#查询是否开启慢查询，并确认慢查询时间已变为4s
#启用二进制日志
vim /etc/my.cnf
[mysqld]
log-bin=mysql-binservice mysqld start
mysql -uroot -p123
mysql> show binary logs;#查看当前正在使用的二进制日志文件名及文件大小（单位pos）
mysql> show binlog events in 'mysql-bin.000001';#查看二进制日志
#查看二进制日志文件内容
mysqlbinlog mysql-bin.000001
也可以mysqlbinlog mysql-bin.000001 > 1.txt，将二进制日志文件内容写入文件中，用vim查看
#用二进制日志文件恢复数据，二进制日志文件恢复会将时间也恢复为之前的
#先登录mysql将users表删除
cd /var/lib/mysql
mysqlbinlog mysql-bin.000001 > /root/1.sql
vim /root/1.sql#查看pos值
#恢复到pos值为209时的数据状态
mysqlbinlog --start-position 106 --stop-position 209 mysql-bin.000001 | mysql -uroot -p123
#恢复到时间为2020-07-04 10:24:44的数据状态
mysqlbinlog --start-datetime '2020-07-04 10:24:07' --stop-datetime '2020-07-04 10:24:44' mysql-bin.000001 | mysql -uroot -p123
扫一扫
356
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
