MYSQL 数据库

最新推荐文章于 2024-07-11 21:41:59 发布

weixin_33725807

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量326

点赞数

文章标签：数据库开发工具运维

原文链接：http://blog.51cto.com/10957928/2160063

版权

数据库管理系统的优点

相互关联的数据的集合

较少的数据冗余

程序与数据相互独立

保证数据的安全、可靠

最大限度地保证数据的正确性

数据可以并发使用并能同时保证一致性

数据库管理系统

数据库是数据的汇集，它以一定的组织形式存于存储介质上

DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系

统的核心

DBA：负责数据库的规划、设计、协调、维护和管理等工作

应用程序指以数据库为基础的应用程序

数据库管理系统的基本功能

数据定义

数据处理

数据安全

数据备份

数据库系统的架构

单机架构

大型主机/终端架构

主从式架构（C/S）

分布式架构

关系型数据库

关系：关系就是二维表。并满足如下性质：

表中的行、列次序并不重要

行row：表中的每一行，又称为一条记录

列column：表中的每一列，称为属性，字段

主键（Primary key）：用于惟一确定一个记录的字段

域domain：属性的取值范围，如，性别只能是‘男’和‘女’两个值

centos6安装MySQL yum install mysql-server

centos7安装MySQL yum install mariadb-server

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-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=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 的安全脚本

############################################################33

用脚本安装

#!/bin/bash

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

#用二进制安装mysql数据库

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

iptables -F &> /dev/null #清除防火墙

sed -i -r 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config #更改/etc/selinux/config里面的SELINUX=disabled，关闭selinux

yum install -y libaio &> /dev/null #安装libaio包

getent passwd mysql || echo `useradd -r -d /data/mysqldb -s /sbin/nologin mysql` #查看在/etc/passwd文件里面有没有mysql用户，如果没有创建mysql用户

ls -l /usr/local/mariadb-10.2.15-linux-x86_64 &> /dev/null || echo `tar xvf /root/mariadb-10.2.15-linux-x86_64.tar.gz -C /usr/local/ &> /dev/null`

#查看/usr/local/目录下有没有解压完成的包，如果没有解压二进制包

cd /usr/local #切换到/usr/local/目录

ln -s mariadb-10.2.15-linux-x86_64/ mysql #设置软链接

chown -R root:root mysql/ #设置mysql目录下的所有文件的属主和属组是root

echo PATH=/usr/local/mysql/bin:$PATH > /etc/profile.d/mysql.sh #在/etc/profile.d/mysql.sh文件下加入/usr/local/mysql/bin的PATHA变量

chmod +x /etc/profile.d/mysql.sh #给/etc/profile.d/mysql.sh文件加执行权限

cd #切换到家目录

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

mkdir /data/mysqldb -p #在data目录创建mysqldb目录

chown mysql.mysql /data/mysqldb #设置/data/mysqldb目录的的属主和属组是mysql用户

cd /usr/local/mysql #切换到/usr/local/mysql目录

scripts/mysql_install_db --datadir=/data/mysqldb --user=mysql #在/data/mysqldb下生成数据库

cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf -a #覆盖系统自带的配置文件

sed -i '/\[mysqld\]/a\datadir = /data/mysqldb' /etc/my.cnf &> /dev/null #在配置文件里面的[mysqld]下面添加一行datadir = /data/mysqldb

cp support-files/mysql.server /etc/init.d/mysqld -a #拷贝启动文件到/etc/init.d/mysqld

chkconfig --add mysqld

systemctl start mysql.service #启动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

}

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

}

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';

也可以插入数据，但是用的不多

3.通过查找一个表里面的特定字段的数据，插入到新表中

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

+------+---------------+-------------------------------------------+

| user | host | password |

+------+---------------+-------------------------------------------+

| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| root | zhouyafei.com | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| root | ::1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+------+---------------+-------------------------------------------+

desc emp; 查看emp表的结构

MariaDB [db1]> desc emp;

+---------+------------------+------+-----+---------+----------------+

+---------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| sex | char(1) | YES | | m | |

+---------+------------------+------+-----+---------+----------------+

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;

+----+--------+-------+-------+------+-------+

+----+--------+-------+-------+------+-------+

| 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;

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

备份恢复

二进制日志的位置

show master logs;

备份用户数据配置文件，系统数据库相关配置/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目录下并改名

恢复指定的数据库

备份所有的数据库

重新启动服务并恢复数据库

备份和压缩一起执行

解压缩

还原

*****************************************************************************************************************88

每个数据库单独备份，并备份到不同的文件

备份

或

解压

还原

备份数据库，并显示数据库备份的位置时间，data后=2

查看备份的时间位置

备份全部数据库

-F 切换日志，生成新的日志

查看备份的时间位置

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

查看

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'

##########################################################################################################################3

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

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

集联服务器，降低mysql服务器的压力

一台mysql主服务器用于写入

一台集联中间服务器用于转发

一台从服务器用于读

主mysql服务器

vim /etc/my.cnf

server-id = 1

log-bin=mysql-bin

server_id=1 唯一的id

log_bin 启用二进制日志

重启服务

systemctl restart mysqld

设置账号只读

grant replication slave on *.* to repluser@'192.168.63.%' identified by 'centos';

查看二进制日志的位置

show master logs;

集联中间服务器

vim /etc/my.cnf

log_slave_updates

log-bin=mysql-bin

server-id = 2

read_only

server_id=2 唯一的id

log_bin 启用二进制日志

log_slave_updates记录从主服务器同步过来的数据到二进制日志

read_only 只读权限

设置可以复制主服务器的数据

help CHANGE MASTER TO 查看模板

mysql

CHANGE MASTER TO

MASTER_HOST='192.168.63.132',

MASTER_USER='repluser',

MASTER_PASSWORD='centos',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=529,

MASTER_CONNECT_RETRY=10;

查看状态

show slave status\G;

reset slave; 删除复制的内容

start slave: 开始复制主服务器的内容

彻底清除复制的内容，用于发现设置错误，从头开始

stop slave;

reset slave all;

设置账号只读

grant replication slave on *.* to repluser@'192.168.63.%' identified by 'centos';

查看二进制日志位置

show master logs;

从服务器

vim /etc/my.cnf

read_only

server-id = 3

启动服务

systemctl restart mysqld

设置可以复制主服务器的数据

目标是集联中间服务器

CHANGE MASTER TO

MASTER_HOST='192.168.63.131',

MASTER_USER='repluser',

MASTER_PASSWORD='centos',

MASTER_PORT=3306,

MASTER_LOG_FILE='contes7-bin.000001',

MASTER_LOG_POS=531;

查看状态

show slave status\G;

start slave: 开始复制主服务器的内容

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

主主复制不经常用，容易造成数据不一致

第一个主

vim /etc/my.cnf

启始是auto_increment_offser=1第一台主机

如果是两个主auto_increment_increment=2增符是2，如果是3太

查看状态

show master logs

创建用户账号

两边互相同步

查看状态

start slave: 开始复制主服务器的内容

第二个主

启始是auto_increment_offser=2第二台主机

如果是两个主auto_increment_increment=2增符是2

2018/06/15 10:29

两边互相同步

查看状态

start slave: 开始复制主服务器的内容

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

第二节课

主从设置完成

半同步，就是当调度器向主服务器修改了一个数据，当这个数据同步到至少一个从服务器上，并成功，主服务器才会显示成功。

主从安装插件

install plugin rpl_semi_sync_master soname 'semisync_master.so';

查看没有启用

show global variables like '%semi%';

查看状态变量没有启用

show status like '%semi%';

启用状态变量

set global rpl_semi_sync_master_enabled=1;

从服务器安装插件

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

查看相关变量

show status like '%semi%';

启用变量

停止启用同步服务

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

下午第一节课

在主从复制的时候进行加密复制

如何用CA给主从颁发证书

实验：一台CA ，一台master主服务器一台 slave从服务器

一台CA

证书文件放在ssl，可以随意放，现在放在ssl目录下

mkdir /etc/my.cnf.d/ssl/

切换到新建的目录

cd /etc/my.cnf.d/ssl/

生成CA的私钥文件

openssl genrsa 2048 > ckey.pem

利用私钥自签名的证书有效期是3650

openssl req -new -x509 -key ckey.pem -out cacert.pem -days 3650

查看私钥文件

替master主服务器生成私钥

先生成私钥文件，在生成请求文件

openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr

2048为的私钥有效期是365 master.key是私钥文件

颁发证书

openssl x509 -req -in master.csr -CA cacert.pem -CAkey ckey.pem -set_serial 01 > master.crt

替salve从服务器生成私钥文件和请求文件

openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr

颁发证书

openssl x509 -req -in slave.csr -CA cacert.pem -CAkey ckey.pem -set_serial 02 > slave.crt

验证证书是否合法

openssl verify -CAfile cacert.pem master.crt

验证多个

openssl verify -CAfile cacert.pem master.crt slave.crt

将各自的文件复制到各自的主机，将整个目录复制

一台master 主服务器

可以将用不到的文件删除

cd /etc/my.cnf.d/ssl/

rm -rf ckey.pem master.csr slave.*

vim /etc/my.cnf

ssl

ssl-ca=/etc/my.cnf.d/ssl/cacert.pem

ssl-cert=/etc/my.cnf.d/ssl/master.crt

ssl-key=/etc/my.cnf.d/ssl/master.key

log-bin=mysql-bin

server-id = 1

启动服务

systemctl restart mysqld

mysql

查看变量，显示加密成功

show variables like '%ssl%';

创建用户，要求用户只能加密的方式执行

grant replication slave on *.* to repluser@'192.168.63.%' identified by 'centos';

查看二进制日志

show master logs;

salve 从服务器

可以将用不到的文件删除

cd /etc/my.cnf.d/ssl/

rm -rf ckey.pem master.* slave.csr

暂时测试

用加密的方式连接主服务器

mysql -urepluser -pcentos -h192.168.63.132 --ssl-ca=cacert.pem --ssl-cert=slave.crt --ssl-key=slave.key

自己的证书和自己的私钥私钥

测试完成

第一种方法

vim /etc/my.cnf

server-id = 2

ssl

启动服务

systemctl restart mysqld

查看相关变量

show variables like '%ssl%';

设置可以复制主服务器的数据

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=729,

MASTER_SSL=1,

MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',

MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',

MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';

查看状态

show slave status\G;

同步

start slave;

第二种方法

先清除原有的

stop slave;

reset slave all;

将私钥证书写入文件

vim /etc/my.cnf

log-bin=mysql-bin

server-id = 2

ssl

ssl-ca=/etc/my.cnf.d/ssl/cacert.pem

ssl-cert=/etc/my.cnf.d/ssl/slave.crt

ssl-key=/etc/my.cnf.d/ssl/slave.key

主服务器的二进制日志

show master logs;

在从服务器设置可以同步主服务器的设置

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=729,

-> MASTER_SSL=1;

启动同步

start slave;

查看状态

show slave status\G;

#######################################################################################################################3

MySQL的高可用

如何将一个主服务器dong机，如何自动提升一台从服务器为主服务器

实现主从高可用MHA

一台管理者：可以管理多个主从的集群

一台主服务器

两台从服务器

manager监控管理， 192.168.63.131一旦发现主服务器出现问题，提升一台最新的从服务器为主服务器

master主服务器 192.168.63.132

salve1 从服务器 192.168.63.134

salve2从服务器 192.168.63.135

前期环境

注意时间，确定所有服务器时间同步

vim /etc/ntp.conf

同步服务器

server 172.20.0.1 iburst

启动ntpd服务

清除防火墙和selinux

1. manager监控

创建基于ssh的key验证

ssh-keygen

复制到自己

ssh-copy-id 192.168.63.132

将整个目录复制到其他主机

scp -pr .ssh 192.168.63.131:/root/

scp -pr .ssh 192.168.63.134:/root/

scp -pr .ssh 192.168.63.135:/root/

测试

ssh 192.168.63.134

安装管理包，

mha4mysql-manager-0.56-0.el6.noarch.rpm

mha4mysql-node-0.56-0.el6.noarch.rpm

yum install mha* -y

管理节点的配置文件

[root@contes7 ~]# mkdir /etc/mha/

[root@contes7 ~]# vim /etc/mha/app1.conf

[server default]

user=mhauser

password=centos

manager_workdir=/data/mastermha/app1/

manager_log=/data/mastermha/app1/manager.log

master_binlog_dir=/data/mysqldb/ #绑定二进制日志的目录

remote_workdir=/data/mastermha/app1/

ssh_user=root

repl_user=repluser

repl_password=centos

ping_interval=1

[server1]

hostname=192.168.63.132

candidate_master=1

[server2]

hostname=192.168.63.134

candidate_master=1

[server3]

hostname=192.168.63.135

检查环境

检查ssh是否可以连接

masterha_check_ssh --conf=/etc/mha/app1.conf

检查复制是否正常

masterha_check_repl --conf=/etc/mha/app1.conf

如果上一步报错，参考下面的链接

https://blog.csdn.net/qq_34457768/article/details/79476670

在机器面前执行，不要远程执行，开启集群

这个命令是前台命令

masterha_manager --conf=/etc/mha/app1.conf

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

如果master主服务器忽然断电，调度器会自动把主服务器调到从服务器上

如果master主服务器重新启动，不会抢主的位置，需要降级成从服务器

vim /etc/my.cnf

relay_log_purge=0

skip_name_resolve=1

CHANGE MASTER TO

MASTER_HOST='192.168.63.134',

MASTER_USER='repluser',

MASTER_PASSWORD='centos',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=715;

master主服务器

vim /etc/my.cnf

2018/06/15 15:36

binlog_format=row

skip_name_resolve

log-bin

server-id = 1

创建只读账号

grant replication slave on *.* to repluser@'192.168.63.%' identified by 'centos';

查看二进制日志的位置

show master logs;

授权所有的权限，给一个用户，方便管理者manager监控用

grant all on *.* to mhauser@'192.168.63.%' identified by 'centos';

确认授权

安装包node

yum install mha* -y

salve1

vim /etc/my.cnf

read_only=1

relay_log_purge=0

skip_name_resolve=1

log-bin=mysql-bin

server-id = 2

启动服务

systemctl restart mysqld

mysql

设置可以同步数据

查看模板

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.000004',

MASTER_LOG_POS=529;

启动同步

start slave;

查看状态

show slave status\G;

安装包node

yum install mha* -y

salve2

vim /etc/my.cnf

read_only=1

relay_log_purge=0

skip_name_resolve=1

log-bin=mysql-bin

server-id = 3

启动服务

systemctl restart mysqld

设置可以同步数据

目标是主服务器

CHANGE MASTER TO

MASTER_HOST='192.168.63.132',

MASTER_USER='repluser',

MASTER_PASSWORD='centos',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=529;

启动同步

start slave;

查看同步状态

show slave status\G;

安装包node

yum install mha* -y

Galera Cluster

 Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据

不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分

别是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多

主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一

致性、完整性及高性能方面有出色表现的高可用解决方案

Galera Cluster特点

多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的。

同步复制：集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据

不会丢失。

并发复制：从节点APPLY数据时，支持并行执行，更好的性能

故障切换：在出现数据库故障时，因支持多点写入，切换容易

热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务

时间就会非常少。在节点故障期间，节点本身对集群的影响非常小

自动节点克隆：在新增节点，或者停机维护时，增量数据或者基础数据不需要

人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为

一致

对应用透明：集群的维护，对应用程序是透明的

实验：实现Galera Cluster

主主模型

galera cluster

每个主都可以写入

三个主机

[mariadb] name = MariaDB baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.60/yum/centos7-amd64/gpgcheck=0

yum源

3台主机

yum install MariaDB-Galera-server -y

3台主机

vim /etc/my.cnf.d/server.cnf

[galera]

# Mandatory settings

wsrep_provider= /usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address= "gcomm://192.168.63.132,192.168.63.131,192.168.63.136"

binlog_format=row

启动第一个节点主机，由于3台主机是平等的关系，所以可以在任意第一个节点执行

/etc/init.d/mysql start --wsrep-new-cluster

其他两个节点主机

/etc/init.d/mysql start

查看数据库相关的变量

show status like 'wsrep%';

已经同步到其他主机

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

vim /etc/my.cnf 更改字符集语言

###################3#########################################################

转载于:https://blog.51cto.com/10957928/2160063

weixin_33725807

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL 数据库

数据库管理系统的优点相互关联的数据的集合较少的数据冗余程序与数据相互独立保证数据的安全、可靠最大限度地保证数据的正确性数据可以并发使用并能同时保证一致性数据库管理系统数据库是数据的汇集，它以一定的组织形式存于存储介质上DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核心DBA：负责数据库的规划、设计、协调、维护和管理等工作应用程序...
复制链接

扫一扫