MySQL
关系型数据库以一行作为一个记录,列数据库以一列为一个记录
一行是一个记录,一列是一个字段
一行是一个实体,一列是一个属性
MySQL引擎:
MySQL引擎：
可以理解为，MySQL的“文件系统”，只不过功能更加强大。
MySQL引擎功能：
除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。和磁盘打交道，mysql中组织。
1.什么是外键：外键的主要作用是保持数据的一致性、完整性。
2.什么是索引:索引相当于书中的目录，可以提高数据检索的效率，降低数据库的IO。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的
3.什么是事务：事务是由一步或几步数据库操作这系列操作要么全部执行，要么全部放弃执行。程序和事务是两个不同的概念。
事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolatio >）和持续性（Durability）。这四个特性也简称ACID性。
（1）原子性：事务是应用中最小的执行单位，就如原子是自然界最小颗粒，具有不可再分>的特征一样。事务是应用中不可再分的最小执行体。（最小了，不可再分了）
（2）一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态>。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性
来保证的。（说罢了就是白狗变成了黑狗，不能出现斑点狗！）
（3）隔离性：各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都>是隔离的。也就是说：并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间
不能相互影响。（说白了，就是你做你的，我做我的！）
（4）持续性：持续性也称为持久性，指事务一旦提交，对数据所做的任何改变，都要记录>到永久存储器中，通常是保存进物理数据库。（说白了就是一条道跑到黑）
4.什么是行锁与锁表：可以将一张表锁定和可以单独锁一行的记录。为了防止你在操作的
同时也有别人在操作。
常见的MySQL引擎
InnoDB
MyIsam
Memory（也叫HEAP）堆内存
Blackhole（黑洞引擎）异地主从
Archive(归档引擎)
MySQL类型:
数值类型
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
浮点类型
FLOAT
DOUBLE
DECIMAL
字符串类型
char
varchar
binary
varbinary
text
blob
enum 枚举类型
set 集合类型
日期时间类型
date
time
datetime
timestamp 自动存储记录插入或更新的日期和时间
约束条件:
表完整性约束
作用：用于保证数据的完整性和一致性
==============================================================
约束条件 说明
NOT NULL 标识该字段不能为null
DEFAULT 为该字段设置默认值
UNSIGNED 无符号(正数)
ZEROFILL 使用0填充，例如0000001
UNIQUE KEY (UK) 标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
说明：
1. 是否允许为空，默认NULL；可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
安装
yum安装MySQL:
yum安装MySQL
1.官方获取yum源---->MySQL.com
1).清理缓存
yum clean all && yum makecache && yum repolist
2.修改yum源
1).使用vim交互修改
2).yum -y install yum-utils
使用yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
3).临时生效(不需要安装)
yum -y install mysql-community-server --disablerepo mysql80-community --enablerepo msyql57-community
3.yum安装MySQL
yum -y isntall msyql-community-server
4.启动数据库(第一次启动,初始化数据库)
systemctl start mysqld
5.配置文件
/etc/my.cnf
数据目录
/var/lib/mysql
日志文件
/var/log/mysqld.log
6.获取临时密码
grep password /var/log/mysqld
7.修改密码
mysqladmin -uroot -p'临时密码' password '新密码'
8.修改密码强度策略
vim /etc/my.cnf
validate-password=OFF
9.忘记MySQL数据库root用户密码：
vim /etc/my.cnf
添加：skip-grant-tables（跳过验证表，它和修改密码强度策略不能同时存在）
desc mysql.user
select * from mysql.user
update mysql.user set authentication_string=password("QianFeng012345") where User='root' and Host="localhost";
源码安装:
源码安装
1. 获取源码包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.24.tar.gz
2. 准备编译环境和依赖
yum -y install ncurses ncurses-devel openssl-devel bison libgcrypt gcc gcc-c++ make cmake
3. 添加MySQL用户
useradd -M -s /bin/nologin mysql
4. 解压
tar xf mysql-boost-5.7.24.tar.gz
tar -xf 源码包
5. 创建自定义路径
mkdir -p /opt/{mysql,config,data,log,tmp}
chown -R mysql:mysql /opt/{mysql,config,data,log,tmp}
6.cmake ...
cmake . \
-DWITH_BOOST=boost/boost_1_59_0/ \
-DCMAKE_INSTALL_PREFIX=/opt/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/opt/data \
-DINSTALL_MANDIR=/usr/share/man \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/opt/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1
7.编译
make -j `lscpu | awk 'NR==4{print $2}'`
8.编译安装
make install
9.准备配置文件
vim /opt/config/my.cnf
[mysqld]
basedir = /opt/mysql #安装目录
datadir = /opt/data #数据存放目录
tmpdir = /opt/tmp #/tmp缓存目录
socket = /opt/tmp/mysql.sock #指定socket文件的位置
pid_file = /opt/tmp/mysqld.pid #指定pid文件的位置
log_error = /opt/log/mysql_error.log #错误日志的位置
slow_query_log_file = /opt/log/slow_warn.log #慢日志查询
10.初始化
cd /opt/mysql/
./bin.mysqld --user=mysql --default-file=/opt/config/my.cnf --initialize
11.获取密码
grep password /opt/log/mysql_error.log
12.启动
./bin/msyql_safe--user=mysql &
13.修改密码
./bin/mysqladmin -uroot -p'临时密码' password '新密码'
14..停止数据库服务
./bin/msyqladmin -uroot -p'新密码' shutdown
15.cp /opt/mysql/support-files/mysql.server /etc/init.d/mysqld
systemctl daemon-reload
systemctl start msyqld
16.配置环境变量
echo 'export PATH=$PATH:/opt/mysql/bin' >> /etc/profile
source /etc/profile
自己写文件
vim /etc/profile.d/mysql.sh
export MYSQL_HOME=/opt/mysql
export PATH=$PATH:$MYSQL_HOME/bin
source /etc/profile.d/mysql.sh
内网环境安装MySQL
内网环境安装MySQL
1.配置本地源
vim config_loacl_yum.sh
rm -f /etc/yum.repos.d/*
echo '10.36.178.2 package.qf.com package2.qf.com' >> /etc/hosts
curl -o /etc/yum.repos.d/centos.repo http://langlangago.org.cn/repo/centos7.repo
yum clean all &>/dev/null && yum repolist
2.yum -y install mysql-server
MYSQL语句:
show databases; 查看数据库表
use mysql; 切换数据库
selcet database();查看当前所在库
create database sq_name default charset ['utf8' | 'utfmbi']; 创建库
show create database sq_name; 展示创建数据库时是怎么创的
use sq_name; 切换数据表
create table teacher (name varchar(20),age int);
desc teacher
MySQL结构语言介绍
数据查询语言（DQL:Data Query Language）： 其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词。
数据操作语言（DML：Data Manipulation Language）： 其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
数据控制语言（DCL）:（Data Control Language）： 它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
数据定义语言（DDL）：（Data Define Language）： 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
基础操作:
授权远端登录
1.创建用户并设置密码
create user 'root'@'%' identified by 'password'
2.给创建的用户所有权限
grant all on *.* to 'root'@'%';
3.刷新表结构
flush privileges
基础命令:
-- 数据库的基本操作
-- 连接数据库
mysql -u用户名 -p密码 -h主机地址 -P端口 -S 套接字文件mysql.sock /var/lib/mysql/mysql.sock
-- 查看数据库
SHOW DATABASES;
-- 查看表
SHOW TABLES;
-- 查看数据库版本
SELECT VERSION();
-- 查看当前所在库
SELECT DATABASE();
-- 查看当前登录的用户
SELECT USER();
-- 切换库
USE db1;
-- 查看表
SHOW TABLES;
-- 创建库
CREATE DATABASE db1 DEFAULT CHARSET 'utf8';
-- 创建表
CREATE TABLE teacher (id INT);
-- 修改表名 （在业务高峰期间不用做此操作）
RENAME TABLE teacher TO tb_teacher;
ALTER TABLE tb_teacher RENAME teacher;
-- 查询表里的数据
SELECT * FROM teacher;
-- 删除表
DROP TABLE teacher;
-- 删除库
DROP DATABASE db1;
DDL表的操作:
DDL --- 数据定义语言,其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。
-- 创建sanguo数据库
CREATE DATABASE sanguo DEFAULT CHARSET 'utf8';
-- 在sanguo数据库中创建shuguo表
CREATE TABLE sanguo.shuguo (id INT PRIMARY KEY AUTO_INCREMENT COMMENT "自增id",
`name` CHAR(20) NOT NULL COMMENT "人物姓名",
`alias`VARCHAR(20) NOT NULL COMMENT "人物的字",
`arms` VARCHAR(20) NOT NULL COMMENT "人物的武器",
`birth`DATE COMMENT "人物出生日期",
`daed` int COMMENT "人物死亡年龄")
-- 切换库
use sanguo
-- 查看字段
DESC shuguo
-- 增加字段
ALTER TABLE shuguo ADD gender ENUM('男','女') NOT NULL DEFAULT '男';
alter table shuguo add hobby set ('方天画戟专捅义父','捅你一万个透明窟窿') not null first
-- 给字段添加索引
alter table shuguo add index 索引名字(字段);
-- 更改字段位置
alter table shuguo add age int not null after `name`;
--修改字段
alter table shuguo modify name varchar(20)
alter table shuguo modify `name` varchar(20)
alter table shuguo change `name` `xiaoming` varchar(20);
alter table shuguo change `xingming` `name` char(20)
alter table shuguo change `name` `name` varchar(20)
--更换字段位置不改字段名
ALTER TABLE shuguo CHANGE `daed` `dead` int AFTER gender
-- 删除字段
alter table shuguo drop `hobby`
-- 复制表结构
create table weiguo select * from shuguo where 1==2;
-- 复制一模一样的表sql
create table weiguo select * from shuguo;
-- 复制某一些字段
create table weiguo1 select id,`name`,age from shuguo where 1=2;
DML操作表的数据
DML ---数据操作语言(动作查询语言)
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。
-- 插入数据
insert into shuguo (`name`,age,alias,arms,birth,dead)
values('刘备','30','玄德','双股剑','2024-05-23','60')
-- 插入多个数据
insert into shuguo (`name`,age,alias,arms,birth,dead)
values('关羽','25','云长','青龙偃月刀','2024-04-23','50'),
('张飞','20','翼德','丈八蛇矛','2024-03-23','40')
-- 修改数据
update shuguo set age=30 where id=1
-- 清空所有数据
delete from shuguo
truncate table shuguo(比较快)-----table 可加可不加
-- 清空指定数据
delete from shuguo where id=1;
DQL表的查询
单表查询
CREATE TABLE company.employee5(
`id` int primary key AUTO_INCREMENT not null,
`name` varchar(30) not null,
`sex` enum('male','female') default 'male' not null,
`hire_date` date not null,
`post` varchar(50) not null,
`job_description` varchar(100),
`salary` double(15,2) not null,
`office` int,
`dep_id` int
);
-- 查询工资5000的员工
select `name`,salary from company.employee5 where salary=5000;
-- 查询工资大于5000的员工
select `name`,salary from company.employee5 where salary>5000;
-- 查询每个员工的一年总工资
select `name`,salary *15 from company.employee5
-- 查询每一个员工一年总工资并把打印出来的表头设为sum_salary
select `name`,salary *15 as sum_salary from company.employee5;
select `name`,salary *15 sum_salary from company.employee5;
-- 查询2017年入职公司的员工
-- 遍历整张表 %通配符
select `name`,hire_date from company.employee5 where hire_date like %2017%
-- 大于2016-12-31并小于2018-01-01
select `name`,hire_date from company.employee5 where hire_date > "2016-12-31" and hire_date < "2018-01-01";
-- 在2016-12-31和2018-01-01之间
select `name`,hire_date from company.employee5 where hire_date between "2016-12-31" and "2018-01-01"
-- 取字段左边四个字符=2017
select `name`,hire_date from company.employee5 where left(hire_date,4)="2017"
-- 正则表达式regexp 匹配字段中数据含有"2017"的字符
select `name`,hire_date from company.employee5 where regexp "2017+"
-- 排序
-- 将字段中数据升序排列
select `name`,`salary` from company.employee5 order by salary;
-- 将字段给中数据降序排列
select `name`,`salary` from company.employee5 order by salary desc;
-- 去字段中数据的前三行可以结合降序使用
select `name`,`salary` from company.employee5 order by salary limit 3
-- 拼接字段
-- as month_salary 将查询出来的表头修改为 month_salary
select concat(`naem`,一个月工资是:,`salary`) as month_salary from company.employee5
-- 查询字段中数据有多少种
-- 去重
select distinct(post) from company.employee5;
-- 分组查询
-- 以第一个字段分组将第二个字段中数据相同的合并查询出来
select sex,group_concat(`name`) from company.employee5 group by sex
-- in从集合里面取值
-- 查询id为1,3,5
-- or 只要该字段能找到就会展示
select id,`name`from company.employee5 where id=1 or id=3 or id=5;
-- in 只取in集合里的
select id,`name`from company.employee5 where id in (1,3,5);
-- not in 不包含in集合里的
select id,`name`from company.employee5 where not in (1,3,5);
-- null
-- 查询某些字段中某个字段为null
select id,`name`,job_description from company.employee5 where `job_description` is null
-- 查询某些字段中某个字段不为null
select id,`name`,job_description from company.employee5 where `job_description` is not null
-- 函数
-- 查询表里有都少条数据
select count(1) from company.employee5
-- 查询某个字段中最大的数据
select max(salary) from company.employee5
-- 查询某个字段中最小的数据并设置表头
select min(salary) as `min`from company.employee5
-- 查询某个字段中数据的平均值
select avg(salary) from company.employee5
-- 查询某个字段中数据的总值
select sum(salary) from company.employee5
-- 查询当前所在的库
select database();
-- 查询当前所登录的用户
select user();
-- 查询当前的时间
select now();
-- 查询某个字段中数据最大的并且是那个
select `name`,salary from company.employee5 where salary=(select max(salary) from company.employee5)
多表查询
多表查询
多表连接查询
交叉连接：生成笛卡尔积，它不使用任何匹配条件 了解就好，这个生产用会把数据库跑死
内连接 ：只连接匹配的行
外连接：（了解）
左连接：会显示左边表内所有的值，不论在右边表内匹不匹配
右连接：会显示右边表内所有的值，不论在左边表内匹不匹配
全外连接：（了解） 包含左、右两个表的全部行
复合条件连接查询
子查询
内连接:
-- 内连接
-- 使用where
select 表1.字段,表2.字段 from 表1,表2 where 表1.字段=表2.字段
eg : select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name
from employee6,department6
where employee6.dept_id = department6.dept_id;
-- 别名
select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a,department6 b where a.dept_id = b.dept_id;
-- 使用inner join (inner可以省略)
select 表1.字段,表2.字段 from 表1 inner join 表2 on 表1.字段=表2.字段
eg : select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a inner join department6 b on a.dept_id = b.dept_id;
外连接
-- 外连接
-- 左外连接
select 字段列表 from 表1 left join 表2 on 表1.字段=表2.字段;
eg : select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;
-- 右外连接
select 字段列表 from 表1 right join 表2 on 表1.字段=表2.字段;
eg : select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;
复合条件连接查询
-- 复合条件连接查询
-- 以内连接的方式查询表1,表2,并且表1中某个字段值必须大于25
select 表1.字段,表2.字段 from 表1,表2 where 表1.字段=表2.字段 and 表1.字段>25;
eg : select emp_id,emp_name,age,dept_name FROM employee6,department6 WHERE employee6.dept_id=department6.dept_id AND age > 25;
-- 以内连接的方式查询表1,表2 并且表1中某个字段以升序方式显示
select 表1.字段,表2.字段 from 表1,表2 where 表1.字段=表2.字段 order by 表1.字段 asc
eg : SELECT emp_id,emp_name,age,dept_name FROM employee6,department6 where employee6.dept_id=depaartment6.dept_id ORDER BY age asc;
子查询
-- 子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等
-- 查询表2.字段数据1,数据2的表1.字段数据
select 表1.字段,表2.字段 from 表1 join 表2 on 表1.字段=表2.字段
eg1 : select e.emp_name,d.dept_name from employee6 e join department6 d on e.dept_id=d.dept_id;
eg2 : select * from employee6 where dept_id in (select dept_id from department6 where dept_name in ("hr","it"));
-- 1. 带IN关键字的子查询
-- 查询表1,但表2.字段必须在表1中出现过
select * from 表1 where 表1.字段 in(select 表2.字段 from 表2)
eg : SELECT * FROM employee6 WHERE dept_id IN (SELECT dept_id FROM department6);
-- 2.带比较运算符的子查询
-- =,!=,>,>=,<,<=,<>
-- 查询表1字段数据大于25岁的字段2所在表2的字段数据
select 表2.字段,表2.字段 from 表2 where 表2.字段 in(select distinct 表1.字段 from表1 where 表1.字段>=25 );
eg : SELECT dept_id,dept_name FROM department6 WHERE dept_id IN (SELECT DISTINCT dept_id FROM employee6 WHERER age >= 25);
DCL（数据库控制语言)
用于数据库授权、角色控制等操作
`GRANT` 授权，为用户赋予访问权限
`REVOKE` 取消授权，撤回授权权限
用户管理:
创建用户
create user '用户名'@'客户端来源IP地址' identified by '密码';
mysql> create user newrain@'192.168.62.%' identified by '123';
删除用户
drop user '用户名'@'客户端来源IP地址';
mysql> drop user newrain@'192.168.62.%';
修改用户
rename user '用户名'@'客户端来源IP地址' to '新用户名'@'客户端来源IP地址' ;
mysql> rename user newrain@'192.168.62.%' to ehome@'%';
修改密码
// 第一种方法：
set password for '用户名'@'IP地址'=Password('新密码')
mysql> set password for ehome@'%'=Password('123');
// 第二种方法：
alter user '用户名'@'客户端来源IP地址' identified by '新密码';
// 第三种方法(忘记密码时，必须使用此方法修改密码)：
UPDATE mysql.user SET authentication_string=password('QFedu123!') WHERE user='root' and host='localhost';
＝＝＝root修改自己密码
# mysqladmin -uroot -p'123' password 'new_password' //123为旧密码
案例:
# mysqladmin -uroot -p'123' password 'qf@123';
权限管理:
grant 权限 on 数据库.表 to '用户'@'客户端来源IP地址' identified by '密码'; -- 授权并设置密码
revoke 权限 on 数据库.表 from '用户'@'客户端来源IP地址' -- 取消权限
grant select on Company.employee to 'ehome'@'%' identified by 'Qianfeng@123'
revoke select on Company.employee from 'ehome'@'%'
grant all privileges on company.* to ehome@'%';
revoke all privileges on company.* from ehome@'%';
flush privileges; #关于权限方面的修改，注意刷新权限，否则有可能不生效
查看授权信息
查看授权语句
show grants for '用户'@'客户端来源IP地址';
show grants for ehome@'%';
查看生效的授权信息
针对所有库和表的权限，比如 `*.*` 。 去 `mysql.user` 中查看
SELECT * from mysql.user WHERE user='root'\G
SELECT * from mysql.user WHERE user='ehome'\G
针对具体到库的权限，比如`db_name.*` 。 去 `mysql.db` 中查看sql
SELECT * from mysql.db WHERE user='ehome'\G
MySQL安全控制:
1.确保MySQL运行用户为一般用户
groupadd mysql
# useradd -M -s /sbin/nologin -g mysql mysql
# vim /etc/my.cnf
user = mysql
#注意点:
改变拥有者和所属组对于mysql的安装目录
2.建议修改默认端口3306,改为其他的一些端口
# vim /etc/my.cnf
port = 3306 false
port = 10086 true
3.开启mysql二进制日志,在误删除数据的情况下,可以通过二进制日志恢复到某个时间点
# vim /etc/my.cnf
log_bin = othername
4.删除空口令账号
drop user 'newrain'@'localhost';
MySQL备份
备份
show variables 查看mysql所有的变量
show variables like 'sec%' 查看备份数据路径
1.逻辑备份 [MySQLdump]<50G
myqsldump -u用户 -p密码 -h主机 (ip) -P端口 -S 套接字文件
-- 全库备份
mysqldump -uroot -p'pd' [-A | --all-datebases ] > all_db.sql
-- 库级备份 -B 必须加
mysqldump -uroot -p'pd' [-B | --databasae] db1_name db2_name > db.sql
-- 表级备份
mysqldump -uroot -p'pd' db_name tb1_name tb2_name > tb.sql
-- 备份表结构 -d
mysqldump -uroot -p'pwd' db_name tb1_name tb2_name -d > tb.sql
-- 备份表数据
select * from 库.表 into outfile '/var/lib/mysql-files/user.xlsx'
-- 远程备份
-- 注意：远程备份数据库，需要远程服务器也有mysql服务
mysqldump -h ip -uroot -p'pwd' -B db_name >db_sql
恢复
1.恢复
-- 恢复库
1.1 mysql -uroot -p'pwd' < db.sql
1.2 msyql -uroot -p'pwd'
mysql > source /root/db.sql
1.3 cat db.sql | mysql -uroot -p'pwd'
-- 恢复表
1.1 mysql -uroot -p'pwd' db_name < tb.sql
1.2 mysql -uroot -p'pwd'
mysql> use db_name
mysql> source /root/tb.sql
1.3 cat tb.sql | mysql -uroot -p'pwd' db_name
-- 恢复表结构
1.1 mysql -uroot -p'pwd' db_name < tb.sql
1.2 mysql -uroot -p'pwd'
mysql> use db_name
mysql> source /root/tb.sql
1.3 cat tb.sql | mysql -uroot -p'pwd' db_name
-- 恢复表数据
msyql> 准备表结构 (可以用复制表结构来准备)
mysql> load data infile '/var/lib/mysql-files/user.xlsx' into table 表名;
binlog日志
-- binlog日志备份恢复
show master status\G -- 查看当前存储binlog日志是那个文件
flush logs; -- 手动刷新binlog日志
reset master -- 重置bin-log
1.开启binlog日志
vim /etc/my.cnf
[mysqld]
log-bin=/var/lib/mysql/mysql-bin
server_id=1
2.恢复
先把丢失之前备份的数据恢复出来
解析
mysqlbinlog /var/lib/mysql/mysql-bin.000001 --base64-output=decode-rows -v
查找pos
BEGIN
at 123
....
commit
at 456
mysqlbinlog --start-position 123 --stop-position 456 /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p'pwd' 库名
物理备份
全量备份恢复
全量备份
innobackupex --user=root --password=密码 /路径
全量恢复
1.停止数据库
systemctl stop mysqld
2.重演
--apply-log --redo-only
innobackupex --user=root --password=密码 --apply-log --redo-only /全量备份数据目录
3.清理数据
rm -rf /var/lib/mysql/*
4.拷贝数据
--copy-back
innobackupex --user=root --password=密码 --copy-back /全量目录
5.修改权限
chown -R mysql.mysql *
6.启动数据库
增量备份恢复
增量备份
第一次增量
innobackupex --user=root --password=密码 --incremental /增量备份数据目录 --incremental-basedir=/基于上次全量备份目录
第二次增量
innobackupex --user=root --password=密码 --incremental /第二次增量备份数据目录 --incremental-basedir=/基于第一次增量备份目录
增量恢复
1.停止数据库
systemctl stop mysqld
2.重演
--apply-log --redo-only
2.1)重演全量备份数据
innobackupex --user=root --password=密码 --apply-log --redo-only /全量备份数据目录
2.2)将增量备份数据重演到已经重演的全量备份数据里
innobackupex --user=root --password=密码 --apply-log --redo-only /重演过的全量备份数据目录 --incremental-dir=/曾量备份数据目录
3.清理数据
rm -rf /var/lib/mysql/*
4.拷贝数据
--copy-back
innobackupex --user=root --password=密码 --copy-back /重演过的全量目录
5.修改权限
chown -R mysql.mysql *
6.启动数据库
差异备份恢复
差异备份
第一次备份
innobackupex --user=root --password=密码 --incremental /差异备份数据目录 --incremental-basedir=/基于全量备份目录
第二次差异备份
innobackupex --user=root --password=密码 --incremental /第二次差异备份数据目录 --incremental-basedir=/基于第一次差异备份目录
差异恢复
1.停止数据库
systemctl stop mysqld
2.重演
--apply-log --redo-only
2.1)重演全量备份数据
innobackupex --user=root --password=密码 --apply-log --redo-only /全量备份数据目录
2.2)将差异备份数据重演到已经重演的全量备份数据里
innobackupex --user=root --password=密码 --apply-log --redo-only /重演过的全量备份数据目录 --incremental-dir=/差异备份数据目录
3.清理数据
rm -rf /var/lib/mysql/*
4.拷贝数据
--copy-back
innobackupex --user=root --password=密码 --copy-back /重演过的全量目录
5.修改权限
chown -R mysql.mysql *
6.启动数据库
MySQL主从复制
原理:
无数据
1.所需环境
1.1 准备两台服务器 关闭防火墙和selinux
1.2 修改主机名
hostnamectl set-hostname 主机名
1.3 配置主机名解析
vim /etc/hosts
1.4 添加
主库ip 主库主机名
从库ip 从库主机名
1.5 同步时间
ntpdate ntp.aliyun.com
time.windos.com
1.6 固定ip
curl langlangago.org.cn/os/static_ip.sh | sh
1.7 配置本地源
rm -rf /etc/yum.repos.d/
curl -o /etc/yum.repos.d/centos.repo
http://www.langlangago.org.cn/repo/centos7.repo && yum clean all && yum repolist
1.8 安装数据库
yum -y install mysql-server
1.9 启动mysql
systemctl start mysqld
1.10 修改密码
grep password /var/lib/mysqld.log
mysqladmin -uroot -p'原始密码' password '新密码'
[主库]
开启bin-log日志
创建存放bin-log日志目录
mkdir -p /目录名
修改目录的所属者和所属主
chown -R mysql.mysql /目录
进入配置文件添加
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/目录名/mysql-bin
进入数据库授权主从复制的账号
mysql> grant replication slave on *.* to '用户名'@'%' identified by '密码';
刷新授权表
mysql> flush privileges;
查看当前存放bin-log在那个文件
mysql> show master status;
[从库]
开启bin-log日志
创建存放bin-log日志目录
mkdir -p /目录名
修改目录的所属者和所属主
chown -R mysql.mysql /目录
进入配置文件添加
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/目录名/mysql-bin
进入数据库编辑配置文件
mysql> change master to
> master_host='主机名',
> master_user='用户名',
> master_password='密码',
> master_port='端口',
> master_log_file='存放bin-log的文件',
> master_log_pos=postion的开始数字;
启动主从服务
mysql> start slave;
[重新设置slave]
reset slave;
查看是否配置成功
mysql> show slave status\G
[注意]
第13.14行两个YES表示主从复制正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
第21.22行 表示错误码和错误信息
Last_Errno: 0
Last_Error:
第35行 表示主从复制是否有延迟
Seconds_Behind_Master: 0
有数据
[主库]
1.开启bin-log日志
创建存放bin-log日志目录
mkdir -p /目录名
修改目录的所属者和所属主
chown -R mysql.mysql /目录
进入配置文件添加
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/目录名/mysql-bin
进入数据库授权主从复制的账号
mysql> grant replication slave on *.* '用户名'@'%' identified by '密码';
刷新授权表
mysql> flush privileges;
查看当前存放bin-log在那个文件
mysql> show master status;
另开一个终端备份
mysqldump -uroot -p'密码' -B 库名 > /路径/文件名.sql
[锁表]
flush table with read lock;
[注意 : 锁表面试有可能会问到 比如 : 做主从配置如何保证数据的完整性并数据不丢失]
远程拷贝
scp /路径/文件名 ip: /路径
[从库]
1. 开启bin-log日志
创建存放bin-log日志目录
mkdir -p /目录名
修改目录的所属者和所属主
chown -R mysql.mysql /目录
进入配置文件添加
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/目录名/mysql-bin
[导入备份主库的数据]
mysql> source /路径/文件名
进入数据库编辑配置文件
mysql> change master to
> master_host='主机名',
> master_user='用户名',
> master_password='密码',
> master_port='端口',
> master_log_file='存放bin-log的文件',
> master_log_pos=postion的开始数字;
[解锁]
关闭终端
启动主从服务
mysql> start slave;
查看是否配置成功
mysql> show slave status\G
gtid主从配置
1.干净的环境
2.
[主库]
进入/etc/my.cnf添加
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
#打开gtid模式
gtid_mode=ON
enforce_gtid_consistency=1
重启服务
systemctl start mysqld
[从库]
vim /etc/my.cnf #在配置文件中添加配置
[mysqld]
server-id=2
#打开gtid模式
gtid_mode=ON
enforce_gtid_consistency=1
重启服务
systemctl start mysqld
3.其他操作和主从配置一样
[注意]
开启gtid后导入和导出数据需要添加参数
[--set-gtid-purged=OFF]
mysqldump -uroot -p'密码' --set-gtid-purged=OFF 库名> /路径/文件名
MySQL读写分离
原理:
Mycat(2主2从的数据库结构)
1.从官网下载jdk,mycat并解压
修改解压后包的名字
mv 解压后报的名字 /jdk
2.配置jdk全局变量
vim /etc/profile/
添加
export PATH=/解压的目录/jdk/bin:$PATH
3.生效
source /etc/profile
4.部署mycat
进入配置文件
4.1 vim /mycat/conf/server.xml
##删除注释和其余,留下面的
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
[下面的用户和密码是应用程序连接到 MyCat 使用的.schemas 配置项所对应的值是逻辑数据库的名字,这个名字需要和后面 schema.xml 文件中配置的一致。]
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
</mycat:server>
4.2 vim /mycat/conf/schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/">
[name='库名'假的库名,给应用程序java来连接 逻辑库名称,与server.xml的一致]
[checkSQLschema="false" 不检查sql ]
[sqlMaxLimit="100" 最大连接数 ]
[dataNode="dn1" 数据节点名称 ]
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
[ name="dn1" 此数据节点的名称]
[dataHost="localhost1" 主机组]
[database="test" 真实的数据库名称]
<dataNode name="dn1" dataHost="localhost1" database="test" />
[name="localhost1" 主机组]
[maxCon="1000" minCon="10" 最大最小连接数]
[balance="2" 负载均衡策略]
[writeType="0" 写模式的配置 ]
[dbType="数据库类型" dbDriver="native"数据库驱动 switchType="1" slaveThreshold="100" 数据库的配置]
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
[用来检查数据库是否正常]
<heartbeat>select user()</heartbeat>
[读写的配置]
[writeHost host='主数据库名称' url='主数据库地址' user='用来连接的用户 ' password='用来连接用户的密码']
<writeHost host="master" url="master:3306" user="mycat"
password="Qianfeng@123">
[readHost host='从数据库的名称' url='主数据库地址' user='用来连接的用户' password='用来连接用户的密码']
<readHost host="s1" url="s1:3306" user="mycat" password="Qianfeng@123" />
</writeHost>
[writeHost host='主数据库2的名称' url='主数据库2的地址' user='用来连接的用户' password='用来连接用户的密码']
<writeHost host="slave" url="slave:3306" user="mycat"
password="Qianfeng@123">
[readHost host='从数据库2的名称' url='主数据库2的地址' user='用来连接的用户' password='用来连接用户的密码']
<readHost host="s2" url="s2:3306" user="mycat" password="Qianfeng@123" />
</writeHost>
</dataHost>
</mycat:schema>
5.启动mycat
/存放mycat的路径 start
[支持以下参数]
start | restart |stop | status
6.在真是的master数据库给用户权限
[注意 这里的用户和密码和schema.xml配置文件里读写配置用来连接的用户密码一致]
mysql> grant all on *.* to mycat@'%' identified by 'Qianfeng@123';
mysql> flush privileges;
7.测试
[-u和-p是server.xml写的用户和密码 -h是mycat的主机地址 -P是mycat监听的端口]
mysql -uroot -p123456 -h -P8066
用SQL语句去测试
balance和writeType属性
[balance 属性]
负载均衡类型,目前的取值有 3 种:
1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。
2. balance="1", 全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2
互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。
3. balance="2", 所有读操作都随机的在 writeHost、readhost 上分发。
4. balance="3", 所有读请求随机的分发到 writerHost 对应的 readhost 执行,writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。
[writeType 属性]
负载均衡类型
1. writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准.
2. writeType="1",所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐。