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

要確?，F在沒有人寫入數據
只能自己鏈接
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.www58058.com/101080