MySQL

timg

数据库管理系统
数据库是数据的汇集，它以一定的组织形式存于存储介质上
DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系
统的核心
DBA：负责数据库的规划、设计、协调、维护和管理等工作
应用程序指以数据库为基础的应用程序

数据库管理系统的基本功能
数据定义
数据处理
数据安全
数据备份

数据库系统的架构
单机架构
大型主机/终端架构
主从式架构（C/S）
分布式架构

关系型数据库
关系：关系就是二维表。并满足如下性质：
表中的行、列次序并不重要
行row：表中的每一行，又称为一条记录
列column：表中的每一列，称为属性，字段
主键（Primary key）：用于惟一确定一个记录的字段
域domain：属性的取值范围，如，性别只能是‘男’和‘女’两个值

mysql_secure_installation MySQL的安全脚本，设置MySQL的用户名密码

mysql -uroot -p 登录MySQL输入用户密码

show databases；查看表

use mysql；切换到MySQL

MySQL默认不能远程连接

mysql -uroot -pcentos -h192.168. 远程连接MySQL

在文件里面更改提示符
[mysql]
prompt=(\\u@\\h) [\\d]>\\_

数据库安装
yum安装
二进制安装
编译安装

#############################################################################
yum安装mysql
添加yum仓库
1. https://downloads.mariadb.org/ 打开MySQL网站

2. 选择一个版本

vim /etc/yum.repos.d/base.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

yum clean all 清除yum缓存
yum repolist       更新yum库
yum install mariadb-server    centos7安装
yum install mysql-server         centos6安装

#############################################################################

2.二进制安装MySQL
1. https://downloads.mariadb.org/ 打开MySQL网站

下载

1检查环境
iptables -vnL 查看防火墙 iptables -F 清除防火墙
vim /etc/selinux/config 关闭seLinux
SELINUX=disabled
mariadb-serve 卸载原来的数据库包

wget https://downloads.mariadb.org/interstitial/mariadb-10.2.15/bintar-linux-x86_64/mariadb-10.2.15-linux-x86_64.tar.gz/from/http%3A//sfo1.mirrors.digitalocean.com/mariadb/
3.创建MySQL的用户
useradd -r -d /data/mysqldb -s /sbin/nologin mysql -r系统用户 -d家目录 -s shell类型

4.解压
tar xvf mariadb-10.2.15-linux-x86_64.tar.gz -C /usr/local/

cd /usr/local

5.创建软链接
ln -s mariadb-10.2.15-linux-x86_64/ mysql

chown -R root:root mysql/ 更改MySQL目录下的文件目录所有者所有组
echo PATH=/usr/local/mysql/bin:$PATH > /etc/profile.d/mysql.sh 设置环境变量

. /etc/profile.d/mysql.sh 执行mysql脚本

6.用户的数据库存放的位置，例如逻辑卷
[root@localhost local]# echo ‘- – -‘ > /sys/class/scsi_host/host0/scan

yum install lvm2 安装逻辑卷的包
pvcreate /dev/sdb

创建物理卷

vgcreate vg0 /dev/sdb
创建卷组

lvcreate -n lv_mysql -l 100%FREE vg0
创建100%的逻辑卷

mkfs.xfs /dev/vg0/lv_mysql
创建文件系统
lvs 查看
blkid
在配置文件/etc/fstab文件挂载逻辑卷

mount -a 挂载

mkdir /data/mysqldb 创建存放数据库的目录
chown mysql.mysql /data/mysqldb 修改目录所有者所属组是mysql用户，便于管理

cd /usr/local/mysql
scripts/mysql_install_db –datadir=/data/mysqldb –user=mysql #生成数据库

文件会自动生成

MySQL配置文件
cp support-files/my-huge.cnf /etc/my.cnf覆盖系统自带的MySQL的配置文件

vim /etc/my.cnf #修改配置文件
[mysqld]
port            = 3306
datadir         = /data/mysqldb    #添加这一行
socket          = /tmp/mysql.sock

cp support-files/mysql.server /etc/init.d/mysqld 复制启动脚本

chkconfig –add mysqld
chkconfig –list

启动MySQL服务
systemctl start mysql.service

mysql_secure_installation
mysql 的安全脚本

***************************************************************************************************************8
源码编译安装MySQL
下载源码包
wget https://downloads.mariadb.org/interstitial/mariadb-10.2.15/source/mariadb-10.2.15.tar.gz/from/http%3A//sfo1.mirrors.digitalocean.com/mariadb/

1.安装包
yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel -y

useradd -r -s /sbin/nologin mysql 创建mysql系统用户

tar xvf mariadb-10.2.15.tar.gz 解压源码包

mkdir -pv /data/mysqldb 创建存放数据库的目录

chown mysql.mysql /data/mysqldb 更改目录的所有者所属组为mysql用户

cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysqldb/ \
-DSYSCONFDIR=/etc \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

make -j 4 && make install

配置环境变量并修改安装目录的权限

[root@zhouyafei ~]# echo PATH=/app/mysql/bin:$PATH > /etc/profile.d/mysql.sh
[root@zhouyafei ~]# . /etc/profile.d/mysql.sh 执行mysql.sh脚本
setfacl -R -m u:mysql:rwx /app/mysql/ 用ACL修改目录的权限

初始化数据库
cd /app/mysql
scripts/mysql_install_db –datadir=/data/mysqldb/ –user=mysql –basedir=/app/mysql/

准备配置文件
cp support-files/my-huge.cnf /etc/my.cnf

服务启动脚本
cp support-files/mysql.server /etc/init.d/mysqld
[root@zhouyafei mysql]# chkconfig –add mysqld

启动mysqld服务
service mysqld start

mysql_secure_installation
mysql 的安全脚本

###########################################################3
多实例多版本，安装多个版本MySQL，每个数据库管理自己的文件
可以yum安装可以编译安装，可以二进制安装

在yum安装的多实例
根据已有的mysql数据库，生成多实例
1. yum install mariadb-server

2. 在mysqldb目录下不同版本的数据库
mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv 存放3个不同版本的数据库，以端口号为目录名

3. 设置所有者所属组，让mysql用户能管理
chown -R mysql.mysql /mysqldb/

4. 生成数据库
mysql_install_db –datadir=/mysqldb/3306/data –user=mysql –basedir=/usr
mysql_install_db –datadir=/mysqldb/3307/data –user=mysql
mysql_install_db –datadir=/mysqldb/3308/data –user=mysql
注：因为 –basedir=/usr 默认是usr所以不用写

注：如果是源码编译要查看路径–basedir=
查看路径的方法
mysql
MariaDB [(none)]> show variables like ‘%basedir%’;

5. 生成自己的配置文件
把现有的配置文件拷贝过来并修改
[root@zhouyafei ~]# cp /etc/my.cnf /mysqldb/3306/etc/
[root@zhouyafei ~]# cp /etc/my.cnf /mysqldb/3307/etc/
[root@zhouyafei ~]# cp /etc/my.cnf /mysqldb/3308/etc/

重复上面的动作3307 3306 3308

6. 配置启动脚本
cp mysqld /mysqldb/3306/
cp mysqld /mysqldb/3307/
cp mysqld /mysqldb/3308/

vim mysqld
#!/bin/bash
port=3306 #在不同的实例更改不同的端口号
mysql_user=”root”
mysql_pwd=””
cmd_path=”/usr/bin”
mysql_basedir=”/mysqldb”
mysql_sock=”${mysql_basedir}/${port}/socket/mysql.sock”

function_start_mysql()
{
if [ ! -e “$mysql_sock” ];then
printf “Starting MySQL…\n”
${cmd_path}/mysqld_safe –defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf “MySQL is running…\n”
exit
fi
}

function_stop_mysql()
{
if [ ! -e “$mysql_sock” ];then
printf “MySQL is stopped…\n”
exit
else
printf “Stoping MySQL…\n”
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}

function_restart_mysql()
{
printf “Restarting MySQL…\n”
function_stop_mysql
sleep 2
function_start_mysql
}

case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf “Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n”
esa

7. 先关闭现有的mysql数据库
systemctl stop mariadb

8. 执行 mysqld启动脚本
chmod 700 /mysqldb/3306/mysqld   给root执行权限
chmod 700 /mysqldb/3307/mysqld   给root执行权限
chmod 700 /mysqldb/3308/mysqld   给root执行权限

/mysqldb/3306/mysqld start   执行启动脚本
/mysqldb/3307/mysqld start   执行启动脚本
/mysqldb/3308/mysqld start   执行启动脚本

9. 启动mysql数据库
mysql -S /mysqldb/3308/socket/mysql.sock
-S 指定3308实例的socket路径

10 ，加mysql的口令
mysql -S /mysqldb/3308/socket/mysql.sock 进入数据库
select user,host,password from mysql.user; 查看数据库有没有口令
update mysql.user set password=password(“centos”) where user=’root’ ;
更新数据库，设置口令为centos
flush privileges; 生效权限
quit 退出数据库

11. 停止3308的数据库
/mysqldb/3308/mysqld stop 已经要输入口令了

12.启动数据库
/mysqldb/3308/mysqld start

13.用登录名密码登录
mysql -S /mysqldb/3308/socket/mysql.sock -uroot -p

#############################################################################

dml 操作数据
dcl 权限
dql 搜索

show databases; 查看数据库

创建数据库 create database 数据库名；
create databases db1;

删除数据库 drop database 数据库名；
show databases;

show character set；字符集，语言
show collation；所有字符集排序

#############################################################################

help 帮助
help create 查看create的用法
use 数据库名切换到当前的数据库，不用加分号
show tables；查看当前数据库的表
create table 表名创建表

创建表的方法一
例如：创建学生的信息表
create table students ( id tinyint unsigned not null primary key ,name char(10) not null ,phone char(11) ,swx char(1) ); 创建表
id 这一列的表头
tinyint 列的数据类型很小的整数。带符号的范围是-128到127。无符号的范围是0到255。
unsigned 只能是正整数
not null 当前的值不能为空
primary key 主键，当前的所有值不能重复

name 名字一列的表头
char 字符串要存储多少字符，名字的长度是多少
char(10) 存储10个字符
not null 当前的值不能为空

phone 电话号码的列
char 字符串要存储多少字符，电话号码的长度是多少
char(11) 存储11个字符

swx 性别的一列
char 字符串要存储多少字符，性别的长度是多少
char(1) 存储1个字符

show table status like ‘students’ \G
查看表的基本信息
\G 竖状显示

desc students; 查看students表的定义

show table status from db1\G;
查看db1数据库的所有表的信息

创建emp表
create table emp ( id int unsigned auto_increment primary key, name varchar(30) not null, sex char(1) default ‘m’, address char(100) ) engine=innodb;

id 编号
int 整数
unsigned 正整数
auto_increment 自动递增，适用于整数类型，自动递增id编号
primary key 主键，当前的值不能出现重复的

name 新一列的名字
varchar(30) 存储的字符长度是30
not null 当前字符不能为空

sex 性别
char(1) 当前存储的字符长度为1
default ‘m’ 定义字符为m

address 地址
char(100) 存储的字符长度为100

engine=innodb 定义的存储引擎为innodb

show table status like ’emp’\G
查看表的基本信息

show create table emp\G
查看表的定义

********************************************************************************************************************8888
创建表的方法二
用现有的表，创建新的表
表结构和数据都会一同复制过来
create table user select user,host,password from mysql.user;
用mysql数据库的user表，选择表中的user，host，password 结构，在db1(因为在db1数据库里面)数据库里面创建一个user的表，但是数据也会一同复制过来
create table 创建表
user 表名
select 选择
user，host，password
from 来源
mysql.user mysql数据库的user表

用现有的表，创建新的表，并在里面加一个不存在的条件，这样就不会复制表里面的内容
create table user2 select user,host,password from mysql.user where 1 = 0;

****************************************************************************************************************************
创建表的方法三
创建一个和现有表结构像的表
create table user3 like mysql.user;
create table 创建表
user3 表名
like 像
mysql.user 用mysql数据库现有的user表

****************************************************************************************************************************
desc user3;
查看表的结构

select * from user；
查看表的数据

drop table user；
删除表

show tables;
显示当前数据库的所有表

复合主键
create table t1 ( name char(20), city char(20), sex char(1), primary key(name,city) );
create table 创建表
t1 表名
primary key(name,city) 将多个主键放一起

desc t1；查看表的结构

#############################################################################

DML 操作数据，修改，创建，删除数据
insert 插入数据
students表的结构

1. insert students values(1,’wang’,’10089′,’m’);

insert 插入数据
students 表名
valuser值：所有的字段都赋值，并按数据赋值，数字不加引号，字符加引号
1代表id的编号
wang 代表name列的名字
10089 代表phone电话一列的号码
m 代表swe性别一列

select * from students；查看表的数据

加字段，对指定的一些字段插入数据
insert students(id,name) values (2,’zhou’);
students(id,name) 在表名后面加入要插入数据的字段

select * from students；查看表的数据

其中null代表空值

2.连续插入多条数据
insert students(id,name) values (3,’fei’),(4,’lin’);

select * from students；查看表的数据

insert students set id=5,name=’ya’;
也可以插入数据，但是用的不多

desc emp; 查看emp表的结构
MariaDB [db1]> desc emp;
+———+——————+——+—–+———+—————-+
| Field   | Type             | Null | Key | Default | Extra          |
+———+——————+——+—–+———+—————-+
| id           | int(10) unsigned | NO    | PRI | NULL    | auto_increment |
| name     | varchar(30)        | NO    |        | NULL    |                          |
| sex        | char(1)                | YES |        | m          |                          |
| address | char(100)            | YES |        | NULL    |                          |
+———+——————+——+—–+———+—————-+

insert emp(name,address) select user,host from user;
通过在user查找到的数据插入到emp表中

select * from emp；查看emp表中的数据

************************************************************************************************************************8
update 修改数据
注意：用update修改数据的时候，必须用where，指定修改的位置
注意：修改的时候备份备份备份！！！
update emp set name=’admin’,address=’beijing’ where id=1;
update 修改数据
emp 表名
name和address是表字段的名
where 指定修改的位置

select * from emp; 查看emp表的数据

limit 限定修改记录数
例如修改emp表name字段的root为admin，并修改2行
MariaDB [db1]> select * from emp;    #每修改前
+—-+——-+——+—————+
| id | name | sex | address       |
+—-+——-+——+—————+
| 1 | admin | m    | beijing       |
| 2 | root | m    | zhouyafei.com |
| 3 | root | m    | 127.0.0.1     |
| 4 | root | m    | ::1           |
+—-+——-+——+—————+

update emp set name=’admin’,address=’beijing’ where name=’root’ limit 2;

where 指定修改的位置
name=’root’ 修改name字段里面数据是root的数据
limit 2 修改name字段里面数据是root的数据的2行

select * from emp; 查看emp表的数据
MariaDB [db1]> select * from emp;    #修改后
+—-+——-+——+———+
| id | name | sex | address |
+—-+——-+——+———+
| 1 | admin | m    | beijing |
| 2 | admin | m    | beijing |
| 3 | admin | m    | beijing |
| 4 | root | m    | ::1     |
+—-+——-+——+———+

****************************************************************************************************************************
delete 删除数据
注意在生产中不建议用
delete emp where id=4; 删除第4条记录
truncate table emp; 快速删除emp表中的所有数据

#############################################################################
安全选项，这样就不会被删除数据和误修改数据
mysql –safe-updates 用这个命令登录mysql，将不会被误修和误删数据
如果用了加密脚本，可以在后面加-u -p登录
例如：修改数据的时候没有加where就会提示
update user set user=’admin’
update修改数据
user 表名
set 后面跟表的字段
user=‘admin’ user表的字段，后面是修改后的数据

当然也可以加到配置文件
vim /etc/my.cnf.d/mysql-clients.cnf
在mysql一行的下面加入safe-updates
[mysql]
safe-updates

#############################################################################
select 查询
单表操作
select * from user4; 查询user4表

select ‘hello world’; select还可以显示字符串

select 1+2; 还可以数学运算

select ‘1+2’,1+2; 可以显示1+2字符串，还可以运算

select user,password from user4; 查询指定的例，user和password

select ‘number’,user,password from user4; 也可以在后面加一个不存在的字符串number

精确查找
select * from user4 where host=’localhost’;
查找user4表中的host一例中是localhost的一行
where限定查找范围

select * from user4 where host=’localhost’ and user=’root’;
查找user4表中的host一例中是localhost的一行并且 user是root的一行

select * from students where swx is null;
查找students表中 swx字段为空的行

select * from students where id >=2 and id<=5;
查找id大于等于2和id小于等于5的行

select * from students where swx in (‘f’,’m’);
in是包含的意思
查找students表中swx字段包含f或m的行

select * from students where swx in (‘f’,’m’) or swx is null;
in是包含的意思
查找students表中swx字段包含f或m的行，并且显示null空行

字段显示别名，并在表头显示
select id as 编号,name as 姓名 from students as st where swx in (‘f’,’m’) or swx is null;
把id变成编号显示，把name变成汉字姓名显示，把表名替换成st，查找students表中swx字段包含f或m的行，并且显示null空行

****************************************************************************************************************************
模糊查询
select * from students where name like ‘w%’;
查找students表name字段以w开头的行

select * from students where name like ‘%i%’;
查找students表name字段包含i字符的行

select * from students where name like ‘___’;
用3个下划线查找students表中name字段是三个字符的行

select * from students where name like ‘__’;
用3个下划线查找students表中name字段是2个字符的行

select * from students where swx != ‘m’;
显示在students表中swx字段不等于m的行
或
select * from students where not swx = ‘m’;

分组查询
select swx from students group by swx;
根据不by后面的swx统计swx有几种
select name from students group by name;
根据不by后面的name统计name有几种

select swx,min(id) from students group by swx;
在students表中，查找swx中有几种类型，并显示id的最小值

select swx,max(id) from students group by swx;
max最大值
在students表中，查找swx中有几种类型，并id的最大值

select swx,max(score) from students group by swx;
max 最大值
在students表中，以by后面的swx字段查找，以swx中最大的max，score是多少

select swx,avg(score) from students group by swx;
avg 平均值

students表的数据
MariaDB [db1]> select * from students;
+—-+——–+——-+——-+——+——-+
| id | name   | phone | score | swx | class |
+—-+——–+——-+——-+——+——-+
| 1 | adc     | 110      |    86   | m     |     1 |
| 2 | zhou   | NULL   |    80   | f       |     1 |
| 3 | fei       | NULL   |   100 | f       |     2 |
| 4 | lin        | NULL |    88   | m     |     2 |
| 5 | wang   | 1000   |    99   | m     |     2 |
| 6 | 飞哥    | 10000 |    99   | m    |     2 |
+—-+——–+——-+——-+——+——-+

class代表班级，1班和2班，swx代表性别 m是男生 f是女生 score是成绩。分别求每个班级的男生和女生的平均成绩
select class,swx,avg(score) from students group by class,swx;
avg代表平均

求每个班级男生和女生的数量
select class,swx,count(id) from students group by class,swx;
count是总数
class是班级
swx性别

分组统计每个班每个性别大于80分的进行查看
select class,swx,avg(score) from students group by class,swx having avg(score) >80;

成绩从小到大排序
select * from students order by score;

成绩从大到小排序
select * from students order by score desc;

select * from students order by score limit 3;
成绩从小到大排序，只取前3个

#############################################################################
练习题
在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
select * from students where age >25 and gender in (‘m’);

以ClassID为分组依据，显示每组的平均年龄
select classid,avg(age) from students group by classid;

显示第2题中平均年龄大于30的分组及平均年龄
select classid,avg(age) from students group by classid having avg(age) >30;

显示以L开头的名字的同学的信息
select * from students where name like ‘L%’;

显示TeacherID非空的同学的相关信息
select * from students where not teacherid is null;

#############################################################################
修改表的字段
alter table students add score tinyint after phone;
在students表中的phone后面加score的一列
alter table 修改
students 表名
add score 新增加score字段
tinyint 列的数据类型很小的整数。带符号的范围是-128到127。无符号的范围是0到255。
phone 加到phone后面

###########################################################################
MySQL授权管理
查看用户信息

select user,password,host from mysql.user;

创建用户并只能wang用户 30的网段登录口令centos

create user wnag@’192.168.63.%’ identified by ‘centos’;

修改用户密码
set password for zhou@’%’ = password(‘123456′);
zhou是用户
% 是用户信息host一例的内容

远程连接

mysql -uwang -pcentos -h192.168.63.132

授权可以访问hellodb数据库下面的表

grant all on hellodb.* to wang@’192.168.%.%’;

删除用户

drop user wnag@’192.168.63.%’;

授权某个数据库某个权限，可以select查看，insert插入数据库hellodb

grant select,insert on hellodb.* to mage@’%’ identified by ‘centos’;

针对特定的列授权，只能查看stuid 和name例，只能在hellodb数据库的students表查看

grant select(stuid,name) on hellodb.students to zhou@’%’ identified by ‘123456’;

查看用户的权限，查看wang用户的网段的权限

show grants for mage@’%’;
%代表全部网段

取消授权，取消wang用户网段的查看hellodb数据库的权限

revoke delete on hellodb.* from mage@’%’;

查看自己的权限

show grants for current_user();

权限放在
用户信息放在use mysql 数据库user表中
show tables查看mysql数据库的所有表

列的权限，select * from colums_priv；

查看用户信息

select user,password,host from mysql.user;

删除空的用户

delete from user where user=’mage’;

mysql> FLUSH PRIVILEGES;
flush privileges;
刷新授权

****************************************************************************************************************************
取两张表的共同信息
select s.name as studname,t.name as teachname from students as s, teachers as t where s.teacherid=t.tid;
或
select s.name as studname,t.name as teachname from students as s inner join teachers as t on s.teacherid=t.tid;
inner join 内连接
查找students学生表的名字和teachers老师表的名字，放在一起查看
select 查找
students 学生表
teachers 老师表
as 设置别名
s 是 students 的别名
t 是 teachers的别名
s.name 是学生表的名字
t.name   是老师表的名字
studname     as设置的是s.name的别名
teachname     设置的是t.name的别名
where 条件
s.teacherid    代表students表的teacherid列
t.tid     代表teachers表的tid列

select * from students cross join teachers;
查看教室表的学生表
cross join 交叉连接

左外连接
select s.name as studname,t.name as teachname from students as s left outer join teachers as t on s.teacherid=t.tid;

右外连接
select s.name as studname,t.name as teachname from teachers as t right outer join students as s on s.teacherid=t.tid;

右内连接
select s.name as studname,t.name as teachname from teachers as t right outer join students as s on s.teacherid=t.tid and t.name is null;

左内连接
select s.name as stdents_name,t.name as teachers_name from teachers as t right outer join students as s on s.teacherid=t.tid and t.name is null;

#############################################################################
查看存储引擎

myisam 存储引擎，用在老的版本centos6.9以前的
innodb 存储引擎，用在比较新的版本centos7

#############################################################################
服务器的配置
服务器的系统变量，服务器的状态变量

##########################################################################
索引
2018/06/08 14:58

创建索引

查看索引

#############################################################################
事物

关闭事物自动提交，
2018/06/11 09:19
永久关闭在vim /etc/my.cnf

插入这一行

查看autocommit的状态
show variables like ‘autocommit’;

如果没有关闭事物自动提交可以临时开启事务
开启事务，就是还没有提交，只是显示
start transaction;

例如：插入一条数据insert students values(26,’Zhou’,’99’,’M’,4,4);

撤销还没有提交的命令，还没有关闭的事务
rollback; 撤销上面还没有提交的插入命令

关闭事务，就是执行的命令提交，不能撤销了
commit; 关闭事务，并提交

开启一个事务，可以撤销指定的命令
例如删除students表的第25行
delete from students where stuid=25;

定义一个标记，这个标记是标记上一条命令
savepoint sp25;

撤销指定的标记命令，例如撤销标记是25的命令
rollback to sp25;\

撤销还没有提交的命令，还没有关闭的事务。撤销全部命令
rollback;

事务隔离级别
事务隔离级别：从上至下更加严格
 READ UNCOMMITTED 可读取到未提交数据，产生脏读
 READ COMMITTED 可读提交数据，但未提交数据不可读，产生不可重
复读，即可读取到多个提交数据，导致每次读取数据不一致
 REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读
取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前
的旧数据。此为MySQL默认设置
 SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交
的修改事务阻塞读事务。导致并发性能差
MVCC: 多版本并发控制，和事务级别相关

默认是重复读多次读取数据都一致
select @@tx_isolation;

更改级别READ UNCOMMITTED，另一个终端的同一个mysql可以看见脏数据，最不严谨
set tx_isolation=”READ-UNCOMMITTED”;

更改隔离级别READ COMMITTED，只能看见提交的事务
set tx_isolation=”READ-COMMITTED”;

更改隔离级别REPEATABLE READ 可重复读，另一个终端的同一个mysql每次读的数据都一样，会出现幻读，实际数据已经更改
可重复读：就是上一次查看数据是什么样，服务端更改数据，客户端再次查看数据还和上次一样
set tx_isolation=”REPEATABLE-READ”;

更改隔离级别 SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差
set tx_isolation=”SERIALIZABLE”;

如果要永久更改事务级别更改配置文件
vim /etc/my.cnf
最好是REPEATABLE READ 这个级别

插入这一行

如果一个人打开一个事务，不提交，就加了一个锁，别不能操作数据库
show processlist;

查看进程事务进程

杀死对应的进程
kill 10;

############################################################################3
日志
预写日志，先写日志在执行操作
日志
事务日志：transaction log
错误日志：error log
查询日志：query log
慢查询日志：slow query log
二进制日志：binary log
中继日志：reley log

查看mysql的执行过的命令
cat .mysql_history

事务日志
建议事务日志的存放路径存放在别的位置
建议增加事务日志的数量的大小
事务日志有关的变量
show variables like ‘%innodb_log%’;

事务日志的大小5242800
事务日志存放有2个
事务日志存放的位置./
cat /data/mysqldb/ib_logfile0
cat /data/mysqldb/ib_logfile1

更改事务存放日志的文件有3个,最多存放的日志文件能设置成100个日志文件
vim /etc/my.cnf
innodb_log_files_in_group=3

更改事务日志存放的目录

vim /etc/my.cnf
innodb_log_group_home_dir=/data/mysqllogs 更改事务日志存放的目录为/data/mysqllogs

systemctl restart mysqld 重启服务
如果不成功，把用来的事务日志，移动到别的地方，或者删除

错误日志
错误日志是存放执行错误的命令日志
vim /etc/my.cnf
更改错误日志的存放路径
log-error=/data/mysqllofs/mysql.log

通用日志
建议如果没有排错需求，建议别开启
存放的是用户输入命令的日志
查看通用日志的状态
show variables like ‘general_log’;

2018/06/11 11:30

更改通用日志以表的方式存放
set global log_output=”table”;

查看通用日志的存放位置
show variables like ‘log_output’;

在数据库用表查看通用日志
select * from general_log;

慢查询日志
比如那条数据拖慢了数据库
查看慢查询的状态
show variables like ‘slow%’;

启用慢查询日志
set global slow_query_log=on;

global是全局

怎么才算慢，超过10秒才算慢
show variables like ‘long%’;

存放的位置
cat /data/mysqldb/zhouyafei-slow.log

log_queries_not_using_indexes=ON 不使用索引也没有达到慢查询阀值的语句是否记
录日志，默认OFF，即不记录

可以启用，用于优化数据库
查看哪个步骤比较慢
查看状态
show variables like ‘profil%’;

启用
set profiling=on;

查询命令的时间
show profiles;

查看这个编号的命令，里面具体什么拉慢了速度

二进制日志，默认是没有启用的
强烈建议启用二进制日志
记录的是事务已经提交的命令
2018/06/11 14:37
查看二进制日志的状态
show variables like ‘%binlog_format%’;

查看是否记录二进制日志，默认启动
select @@sql_log_bin;

查看二进制日志的状态，off代表没有启用二进制日志
show variables like ‘log_bin’;

vim /etc/my.cnf
启用二进制日志
建议把二进制日志存放到别的硬盘
将二进制日志文件存放到/data/mysqllogs目录下，文件名叫mysql-bin
注意/data/mysqllogs目录的属主属组是mysql
log-bin=/data/mysqllogs/mysql-bin
binlog_format=row

2018/06/11 15:03
基于每行

启用mysql服务
systemctl restart mysqld

如果二进制日志的文件大于1G会自动生成另一个文件

expire_logs_days=N：二进制日志可以自动删除的天数。默认为0，即不自动
删除，可以设置的时间大于1月

临时关闭二进制日志

sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操
作系统负责同步日志到磁盘

查看二进制的列表名称的大小
show master logs;

或
show binary logs;

查看当前的二进制文件的写入的文件位置
show master status;

查看所有的二进制日志记录用的不多
show binlog events in ‘mysql-bin.000001’

查看所有的二进制日志记录，指定一个位置，用的不多

查看所有的日志，加-v显示详细信息
mysqlbinlog /data/mysqllogs/mysql-bin.000001 -vvvvv

切换日志，生成新的二进制日志文件
flush logs;

导出到sql文件
mysqlbinlog /data/mysqllogs/mysql-bin.000001 > bin.sql

导入sql文件
mysql < bin.sql

查看440和564之间的内容，这个数字是二进制数据库里面的at 后面的数字
mysqlbinlog –start-position=440 –stop-position=564 -vvv /data/mysqllogs/mysql-bin.000001

输入出的时候按行显示
mysqlbinlog /data/mysqllogs/mysql-bin.000001 –base64-output=decode-row -vvv

清除二进制日志
查看数据库文件列表
show master logs;

删除000002之前的日志，不包括000002
purge binary logs to “mysql-bin.000002″;

彻底删除二进制日志文件，会自动生成一个新的二进制日志文件
reset master;

#############################################################################
备份恢复

备份用户数据配置文件，系统数据库相关配置/etc/my.cnf

2018/06/11 16:30

查看所有的数据库
mysql -e ‘show databases’

冷备份
冷备份就是将数据库的服务停止，然后把要备份的文件拷贝的其他的地方，就完成了冷备份
停止服务
systemctl stop mysqld

拷贝数据库文件
2018/06/11 17:22
查看hellodb数据的大小
du -sh /data/mysqldb/hellodb/

备份单个数据库
tar Jcvf /data/hellodb.tart.xz /data/mysqldb/hellodb

备份所有的数据库
tar Jcvf /data/mysqldb_all.tart.xz /data/mysqldb/

如果数据库被破坏
rm -rf /data/mysqldb/*

还原
tar xvf /data/mysqldb_all.tart.xz -C /data/mysqldb/

默认将所有的文件放到lib下的nysql目录下，拷贝到原来的目录
mv /data/mysqldb/data/mysqldb/* /data/mysqldb/

**************************************************************************************************************8
mysqldump 备份温备
mysqldump 后面加-uroot -p密码

备份
mysqldump -uroot -p hellodb students > /data/hellodb_students.sql

备份hellodb数据库下的students表

备份指定的数据库

备份hllo数据库并重定向到backup目录下并改名

***************************************************************************************************

mysqldump 的选项
-F 刷新数据库，就是生成一个新的日志，配合-A或-B
-A 备份所有的数据库
-B 备份指定的数据库或多个数据库或里面的表，备份的时候是
2018/06/13 10:03

myisam支持温备，必须先锁定数据库加全局锁
innodb支持热备

备份脚本
#!/bin/bash
BACKUP=/backup
BACKUP_TIME=`date +%F_%T`
mysqldump -A -F -E -R –single-transaction –master-data=1 –flush-privileges –triggers –hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

禁止别人访问
vim /etc/my.cnf
skip-networking 只能自己登录mysql数据库，其他人不能登录

还原
mysql < /backup/fullbak_2018-06-13_20\:10\:47.sql

****************************************************************************************************************************
LVM 几乎热备，先加锁，在备份，在解锁。配合cp tar压缩使用
1.设置逻辑卷
fdisk /dev/sdb
partprobe同步命令

如果是新的硬盘创建文件系统mkfs.xfs

创建物理卷
pvcreate /dev/sdb

创建卷组
vgcreate vg0 /dev/sdb

创建逻辑卷，将逻辑卷加入卷组
vgcreate vg0 /dev/sdb
vgcreate vg0 /dev/sdb

查看
lvs

创建逻辑卷文件系统
mkfs.xfs /dev/vg0/lv_mysql

mkfs.xfs /dev/vg0/lv_binlog

创建两个用来存放数据库和二进制日志的目录
mkdir /data/{mysql,binlog} -pv

将逻辑卷挂载到新建的目录下
UUID=4e7ce471-7ae9-4264-9238-9f9f6cff1ace /data/mysql xfs defaults 0 0
UUID=44c9be64-9294-4dc8-8589-452b1bad432a /data/binlog xfs defaults 0 0

mount -a

设置权限
chown -R mysql.mysql /data/mysql
chown -R mysql.mysql /data/binlog

迁移数据库，和二进制日志
vim /etc/my.cnf
datadir = /data/mysql
log_bin=/data/binlog/mysql-bin

启动服务
systemctl restart mysql

导入数据
mysql < hellodb_innodb.sql

临时用写锁锁定表
flush tables with read lock;

查看二进制日志的位置，和生成一个新的日志
show master logs;
flush logs;

创建快照
lvcreate -L 5G -n lv_mysql_snap -s -p r /dev/vg0/lv_mysql

解写锁i
unlock tables;

将快照挂载到一个目录
mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt/

将目录里面的数据拷贝出来
cp -a /mnt/* /backup

先取消挂载，删除快照，不然用户访问会变慢
umount /mnt
lvremove /dev/vg0/lv_mysql_snap

假如，数据库被删，
rm -rf /data/mysql/*

停止服务
systemctl stop mysqld

还原
利用快照还原，将原来备份的数据拷贝到数据库目录下
cp -a /backup/* /data/mysql/

启动服务
systemctl start mysqld

锁定数据库，暂时不能修改
flush tables with read lock;

恢复二进制日志
查看现在的二进制日志存放的位置
show master logs;

000001是原来的配置，000002是刚才恢复的配置
mysqlbinlog –start-position=8993 /data/binlog/mysql-bin.000001 > /backup/bin.sql
mysqlbinlog /data/binlog/mysql-bin.000002 > /backup/bin.sql

要确保现在没有人写入数据
只能自己链接
vim /etc/my.cnf
skip-networking 只能自己登录mysql数据库，其他人不能登录

导入数据库
mysql < /backup/bin.sql

重启服务
systemctl restart mysqld

****************************************************************************************************************************

mysqldump 适合备份小的数据库
完全备份数据库
mysqldump -A -F –single-transaction –master-data=2 >/backup/bak_`date +%F`.sql

如果服务器的硬件出现问题，系统崩溃
假如数据已经备份到其他主机
scp /backup/bak_2018-06-13.sql 192.168.63.131:/

禁止其他人访问
vim /etc/my.cnf
skip-networking 只能自己登录mysql数据库，其他人不能登录

恢复数据库
mysql < /backup/bak_2018-06-13.sql

允许其他人访问
vim /etc/my.cnf
skip-networking 禁用这一项

*************************************************************************
假如单独一个数据被破坏
被drop删除了
如果已经全局备份了
mysqldump -A -F –single-transaction –master-data=2 >/backup/bak2`date +%F_%T`.sql

1.禁止任何人读
flush tables with read lock;

2.查看日志状态
show master logs;

3.查看全局备份的日志放在哪个日志里面
less 全局备份的日志查看
less /backup/bak22018-06-14_10\:42\:19.sql
这一行显示了二进制日志的位置还有哪儿以后的没有复制
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000007′, MASTER_LOG_POS=385;

4.生成新的日志
flush logs;

将备份的数据库和二进制日志复制到其他主机
scp /data/mysqldb/mysql-bin.000007 /backup/bak22018-06-14_10\:42\:19.sql 192.168.63.131:/

还原
1.禁止别人访问
vim /etc/my.cnf
skip-networking

2.重启服务
systemctl restart mysqld

3.还原备份全部数据
mysql < /bak22018-06-14_10\:42\:19.sql

4.将需要用的二进制日志导入到一个文件里面
mysqlbinlog –start-position=385 /mysql-bin.000007 > bin.sql
–start-position 是要开始执行后续命令要指定的开始位置
385是在全局备份里面指定的

5.还原二进制日志
mysql < bin.sql

6.允许用户访问
vim /etc/my.cnf
#skip-networking 禁用这一行

####################################################################################################

安装服务
yum install percona-xtrabackup-24* -y

xtrabackup 适合大的数据库备份
wget https://www.percona.com/downloads/XtraBackup/LATEST/percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

vim /etc/my.cnf

完全备份、如果用用户密码 -u -p
2018/06/13 14:39
innobackupex -uroot -p123456 /data/mysqldb/

二进制日志的位置
show binary logs;

备份到远程主机
将全部备份拷贝到远程主机
scp -r /data/mysqldb/2018-06-14_14-08-46 192.168.63.132:/
将二进制日志拷贝到远程主机
scp /data/mysqldb/mysql-bin.000003 192.168.63.132:/

还原

1新机器停止服务
systemctl stop mysqld

2.vim /etc/my.cnf
innodb_file_per_table
skip_name_resolve=ON
log-bin=mysql-bin
里面的数据和备份的机器原来的配置一样

3.删除新机器原来的数据
rm -rf /data/mysqldb/*

5.整理原来备份数据
innobackupex –apply-log /2018-06-14_14-08-46/

6.复制
innobackupex –copy-back /2018-06-14_14-08-46/
会复制到数据库的目录下

2018/06/13 14:50

7.修改所有者所属组
chown -R mysql.mysql /data/mysqldb/
修改数据库目录下所有文件的文件的所属主，属组

8.启动服务
systemctl start mysqld

*****************************************************************************************************
增量备份

周日完全备份，周一，二增量备份，周二的摸一个时间数据被破坏

完全备份
innobackupex -uroot -p123456 /backup/
将数据库备份到/backup/目录

修改数据库：备份完成以后可能数据会有变化

增量备份是依据已有的备份的基础上在进行备份
增量备份，最好放在独立的文件夹
mkdir /backup/inc{1,2}

第一次增量备份，写完全备份的路径
innobackupex -uroot -p123456 –incremental /backup/inc1/ –incremental-basedir=/backup/2018-06-14_15-00-56
–incremental /backup/inc1/ 要备份到的路径
–incremental-basedir 在谁的基础上备份
/backup/2018-06-14_15-00-56 原有的备份

第二次增量备份，写上一次增量备份的路径
innobackupex -uroot -p123456 –incremental /backup/inc2/ –incremental-basedir=/backup/inc1/2018-06-14_15-05-38/

将备份的数据放到远程主机
scp -r /backup/ 192.168.63.131:/

安装数据库
建新机器的源数据库清空
停止服务
systemctl stop mysqld
rm -rf /data/mysqldb/*

在新机器进行整理
第一次，完全备份的目录
innobackupex –apply-log –redo-only /backup/2018-06-14_15-00-56

第二次将增量备份的数据整合到完全备份里面
innobackupex –apply-log –redo-only /backup/2018-06-14_15-00-56/ –incremental-dir /backup/inc1/2018-06-14_15-05-38/

第三次将第三个增量备份整合到完整备份里面
innobackupex –apply-log –redo-only /backup/2018-06-14_15-00-56/ –incremental-dir /backup/inc2/2018-06-14_15-10-19/

将数据复制到数据库的目录
innobackupex –copy-back /backup/2018-06-14_15-00-56/

更改属性
chown -R mysql.mysql /data/mysqldb/*

启动服务

****************************************************************************************************************************
单表操作备份
mysql必须是新版的数据库
2018/06/13 15:55
vim /etc/my.cnf
每个表的独立数据文件
innodb_file_per_table

安装工具xtrabackup
yum install percona-xtrabackup-24* -y

备份单表
innobackupex -uroot -p123456 –include=”hellodb.students” /backup/
备份hellodb数据库的students表

备份表结构
查看表的定义，并重定向到文件
mysql -e ‘show create table hellodb.students’ > students.sql

建备份数据，备份到远程主机

假如表被破坏删除

还原

整理导出
innobackupex –apply-log –export /backup/2018-06-14_17-09-39/

创建表结构
修改备份的表结构
mysql hellodb < students.sql

存放用户数据的文件，叫表空间文件students.ibd，要删除这个文件，不然我们就没办法恢复，原有的表了
mysql -e ‘alter table hellodb.students discard tablespace’
discard tablespace删除表空间

还原数据
将备份的表数据复制到数据库路径
[root@contes7 ~]# cp /backup/2018-06-14_19-10-51/hellodb/students.cfg /data/mysqldb/hellodb/
[root@contes7 ~]# cp /backup/2018-06-14_19-10-51/hellodb/students.exp /data/mysqldb/hellodb/
[root@contes7 ~]# cp /backup/2018-06-14_19-10-51/hellodb/students.ibd /data/mysqldb/hellodb/

更改刚复制过去的文件属组属主
chown mysql.mysql /data/mysqldb/hellodb/*

导入刚才复制的表
mysql -e ‘alter table hellodb.students import tablespace’

########################################################################

MySQL的复制
scale up 纵向扩展加硬盘内存
scale out横向扩展拆读写分离

主从复制
主的数据可以同步到从服务器

主设置
vim /etc/my.cnf
server_id=1 #为全局设置唯一ID
log-basename=master #定义名字
log-bin=mysql-bin #二进制日志

启动服务
systemctl restart mysqld

查看有没有生成
ll /data/mysqldb/

查看。二进制日志的位置
show master logs;

授权用户有复制权限
grant replication slave on *.* to repluser@’192.168.63.%’ identified by ‘centos’;
replication 复制
192.168.63.%’ 远程ip
repluser 用户
centos 口令

导入数据
mysql < hellodb_innodb.sql

从服务器设置
vim /etc/my.cnf
server_id=2 #设置一个id

启动服务
systemctl restart mysqld

设置可以远程复制
由于命令太长
help change master to
复制
CHANGE MASTER TO MASTER_HOST=’192.168.63.132′, MASTER_USER=’repluser’, MASTER_PASSWORD=’centos’, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000002′, MASTER_LOG_POS=328;
MASTER_HOST=’192.168.63.132′ 是主服务器的ip
MASTER_USER=’repluser’ 主服务器设置的允许复制权限的用户名
MASTER_PASSWORD=’centos’ 用户名的口令
MASTER_PORT=3306 端口号
MASTER_LOG_FILE=’mysql-bin.000002′ 二进制日志的位置
MASTER_LOG_POS=328 开始复制的位置

查看状态
show slave status\G;

***************************************************************************************************
初期只有一个主服务器，后来要加从服务器
主服务器
server_id=1 #为全局设置唯一ID
log-basename=master #定义名字
log-bin=mysql-bin #二进制日志

重启服务

从服务器
vim /etc/my.cnf
server_id=2

启动服务

主服务器备份
mysqldump -A -F –single-transaction –master-data=1 > all.sql

备份的文件复制到从服务器
scp all.sql 192.168.63.131:

从服务器
打开备份的数据库
CHANGE MASTER TO MASTER_HOST=’192.168.63.132′, MASTER_USER=’repluser’, MASTER_PASSWORD=’centos’, MASTER_PORT=3306, MASTER_LOG_FILE=’zhouyafei-bin.000003′, MASTER_LOG_POS=393;

2018/06/13 17:51
mysql < all.sql

启动同步
start slave;

***************************************************************************************************
从服务器基于安全考虑要在配置文件加一行，加入只读权限，普通用户不能写入数据
vim /etc/my.cnf
read_only #只读，可以不启用服务的情况下启用只读

mysql
set global read_only=ON;

测试，在从服务器设置一个账号，可以读写修改
grant select,update,delete,insert on *.* to test@’192.168.63.%’ identified by ‘centos’;

测试在另一台远程主机连接测试
mysql -utest -pcentos -h192.168.63.131

测试删除一个表，显示不能删除
MariaDB [hellodb]> delete from teachers;
ERROR 1290 (HY000): The MariaDB server is running with the –read-only option so it cannot execute this statement

本文来自投稿，不代表Linux运维部落立场，如若转载，请注明出处：http://www.178linux.com/101080