MySQL on Docker: Introduction to Docker Swarm Mode and Multi-Host Networking

1月 28th, 2017

主要介绍多主机网络的基本概念和Docker的Swarm模式,一个用于管理多台物理机上容器的内置编排工具;

— Docker引擎-Swarm模式;
把MySQL容器运行到多个宿主机上会根据选择的集群技术不同而变的复杂;

在我们尝试把MySQL运行在容器+多主机网络的环境中,必须要理解image是怎么工作的;有多少的资源(磁盘,内存,CPU)需要分配,网络(overlay网络的选择:默认,flannel,weave等)和容错性(容器是怎么重新分布,故障转移和负载均衡的);所有的这些都会影响整体的数据库操作,启动时间和性能;非常推荐在Docker引擎集群的上面使用一个编排工具来获得更好的管理性和扩展性;最新的Docker引擎(v1.12,2016.06.14发布的)包含了原生管理Docker引擎集群的工具,叫Swarm模式;Docker引擎的Swarm模式和Docker Swarm是两个不同的项目,尽管两者的工作原理很像,但是使用不同的安装步骤;

一些必要的知识:
1.必须要打开的端口:
1)2377(TCP):用于集群管理;
2)7946(TCP and UDP):用于节点间通信;
3)4789(TCP and UDP):overlay网络传输;
2.两种类型的节点:
1)Manager:管理节点执行编排和集群管理函数从而维护swarm的期望状态(desired state),管理节点选举出来一个leader来构建编排任务;
2)Worker:工作节点接收并执行管理节点分派的任务,默认情况下管理节点也是工作节点,但是可以配置管理节点只做管理节点;

这篇文章,准备部署应用容器到三台Docker宿主机(docker1, docker2, docker3)上,并使用Galera集群的负载均衡,通过多主机环境间的overlay网络进行网络连接;将使用Docker引擎的Swarm模式作为编排工具;

— 启动Swarm模式;
首先需要配置Docker节点到Swarm模式;Swarm模式需要奇数个(推荐三个,节点数越多效率越低)管理节点来维护错误时的法定人数;使用三台物理机器作为管理节点,默认的管理节点也是工作节点;

0.测试环境;
docker1.htsec.com 192.168.10.201
docker2.htsec.com 192.168.10.202
docker3.htsec.com 192.168.10.203

1.首先在docker1上初始化Swarm模式,会使得节点变成为Manager和Leader;
[root@docker1 ~]# docker swarm init –advertise-addr 192.168.10.201
Swarm initialized: current node (385guihkvrmuuuftm34ubr9v2) is now a manager.

To add a worker to this swarm, run the following command:

docker swarm join \
–token SWMTKN-1-43yqmld4mddoddnaxpslsg9s6abxfkkzrbprizzqoofad4rma1-4370suyaolwu0ts8ujv3r7xy2 \
192.168.10.201:2377

To add a manager to this swarm, run ‘docker swarm join-token manager’ and follow the instructions.

2.准备把另外两台物理机也作为Manager,生成注册成为管理节点的命令;
[root@docker1 ~]# docker swarm join-token manager
To add a manager to this swarm, run the following command:

docker swarm join \
–token SWMTKN-1-43yqmld4mddoddnaxpslsg9s6abxfkkzrbprizzqoofad4rma1-2mnogcl6y71n85msftv40vbnb \
192.168.10.201:2377

3.在节点docker2和节点docker3分别执行命令注册为管理节点;
[root@docker2 ~]# docker swarm join \
> –token SWMTKN-1-43yqmld4mddoddnaxpslsg9s6abxfkkzrbprizzqoofad4rma1-2mnogcl6y71n85msftv40vbnb \
> 192.168.10.201:2377
This node joined a swarm as a manager.

[root@docker3 ~]# docker swarm join \
> –token SWMTKN-1-43yqmld4mddoddnaxpslsg9s6abxfkkzrbprizzqoofad4rma1-2mnogcl6y71n85msftv40vbnb \
> 192.168.10.201:2377
This node joined a swarm as a manager.

4.验证所有的节点;
[root@docker1 ~]# docker node ls
ID HOSTNAME STATUS AVAILABILITY MANAGER STATUS
385guihkvrmuuuftm34ubr9v2 * docker1.htsec.com Ready Active Leader
63o59qn38i70iej61h7edm1s1 docker3.htsec.com Ready Active Reachable
ahorta43as7m7yupv3bsnc7pg docker2.htsec.com Ready Active Reachable

此时docker1.htsec.com是Leader节点;

— overlay网络;
使运行在不同宿主机上的容器可以互相通信的唯一方法是使用overlay网络;可以认为,容器的网络是构建在其它的网络之上的(在这个环境中,是构建在宿主机网络上的);Docker Swarm模式的默认overlay网络实现了一个基于VxLAN的解决方案,底层依赖于libnetwork和libkv库,所以overlay网络需要一个key-value存储服务,用于在不同的docker引擎间交换信息;可以选择使用其他外部的overlay网络驱动(比如Flannel,Calico或者Weave),但是需要额外的安装步骤;

使用Docker引擎的Swarm模式,可以在管理节点创建一个overlay网络,不需要外部的key-value存储(比如etcd,consul或者Zookeeper);

Swarm会使overlay网络在需要运行服务的节点上都有效;当你创建一个使用overlay网络的服务,管理节点会自动扩展overlay网络到运行服务任务的节点;

首先创建一个overlay网络,来把Percona XtraDB Cluster和应用容器运行在不同的Docker服务器上,从而达到容灾的效果;这些容器必须运行在同一个overlay网络上,以至于它们可以互相通信;

1.首先创建一个叫”mynet”的overlay网络,这个网络只能在管理节点创建;
[root@docker1 ~]# docker network create –driver overlay mynet
32tayer754amlqnwti9a0c4ht

2.查看当前环境的网络状况;
[root@docker1 ~]# docker network ls
NETWORK ID NAME DRIVER SCOPE
9e1f395cbd90 bridge bridge local
3c63a1302938 docker_gwbridge bridge local
3996de1d0f28 host host local
3uvc0j5s4t1y ingress overlay swarm
32tayer754am mynet overlay swarm
3e1b264a2086 none null local

在swarm范围内有两个overlay网络,”mynet”网络是刚刚创建并用于部署MySQL集群的网络,”ingress”网络是默认的overlay网络;Swarm的Manager使用”ingress”来实现服务间的负载均衡;

— 使用服务和任务进行部署;
计划使用服务和任务来部署Galera Cluster容器;当创建一个服务,需要指定使用哪一个容器镜像和在运行的容器里面运行哪些命令;

有两种类型的服务:
1)Replicated services:基于设置的期望状态(desired state),会在集群的节点上分布指定数量的任务来达到扩展的目的,例如:”–replicas 3″;
2)Global services:集群中每一个可用的节点上都运行一个服务的任务,例如:”–mode global”;如果在swarm中有7个docker节点,则每一个节点都会运行一个容器;

Docker Swarm模式在管理持久性数据存储方面有一定的局限,当一个节点失败,Manager会除掉这个容器并且创建一个新的来替换旧的,从而达到期望状态;因为一个容器失败后会被丢弃,相应的也会失去数据卷;幸运的是,对于Galera集群来说,当新的MySQL容器可以自动的达到相应状态;

— 部署Key-Value存储;
使用的Docker镜像来自Percona,当集群初始化和引导时,这个镜像需要MySQL容器访问一个key-value存储(仅支持etcd),从而来发现相应的IP地址;这些容器会从etcd中查看其它的IP地址,如果保存的有IP地址,则会使用一个适合的wsrep_cluster_address参数来启动MySQL,否则,第一个容器会使用”gcomm://”来作为引导地址;

1.首先在docker1上运行一个etcd服务(也可以使用服务发现的方式);
[root@docker1 ~]# export HostIP=”192.168.10.201″
[root@docker1 ~]# docker run -d -p 2379:2379 -p 2380:2380 -p 4001:4001 –name etcd elcolio/etcd \
> -name etcd1 \
> -advertise-client-urls http://${HostIP}:2379,http://${HostIP}:4001 \
> -listen-client-urls http://0.0.0.0:2379,http://0.0.0.0:4001 \
> -initial-advertise-peer-urls http://${HostIP}:2380 \
> -listen-peer-urls http://0.0.0.0:2380 \
> -initial-cluster-token etcd-cluster \
> -initial-cluster etcd1=http://${HostIP}:2380 \
> -initial-cluster-state new
fc994bb85036b57bef06e55e1856ef1c6f12198b7700744a9be61c22ea62f170
[root@docker1 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
fc994bb85036 elcolio/etcd “/bin/run.sh -name et” 6 seconds ago Up 4 seconds 0.0.0.0:2379-2380->2379-2380/tcp, 0.0.0.0:4001->4001/tcp, 7001/tcp etcd

2.此时可以通过http://192.168.10.201:2379访问etcd服务(如果是使用服务启动的etcd,可以通过命令:docker service inspect etcd -f “{{ .Endpoint.VirtualIPs }}”查看);
[root@docker1 ~]# curl http://192.168.10.201:2379/v2/members
{“members”:[{“id”:”e4379b6d6b63f88c”,”name”:”etcd1″,”peerURLs”:[“http://192.168.10.201:2380″],”clientURLs”:[“http://192.168.10.201:2379″,”http://192.168.10.201:4001”]}]}

— 部署数据库集群;
1.在部署Galera(Percona XtraDB Cluster)容器的命令中指定etcd的服务地址;
[root@docker1 ~]# docker service create –name mysql-galera –replicas 3 \
> -p 3306:3306 –network mynet \
> –env MYSQL_ROOT_PASSWORD=mypassword \
> –env DISCOVERY_SERVICE=192.168.10.201:2379 \
> –env XTRABACKUP_PASSWORD=mypassword \
> –env CLUSTER_NAME=galera \
> percona/percona-xtradb-cluster
b8uica0izpvytrtw097csjfrz

2.查看服务的状态,会发现在三个节点上分别运行了percona-xtradb-cluster容器;
[root@docker1 ~]# docker service ps mysql-galera
ID NAME IMAGE NODE DESIRED STATE CURRENT STATE ERROR
eknbellxg7y3zpujxzmv05dd5 mysql-galera.1 percona/percona-xtradb-cluster docker1.htsec.com Running Running 30 seconds ago
67bs9tu87zt81mjn5salk4rtn mysql-galera.2 percona/percona-xtradb-cluster docker3.htsec.com Running Running 30 seconds ago
6njkyk7pj4r02rdef3ihikrcj mysql-galera.3 percona/percona-xtradb-cluster docker2.htsec.com Running Running 30 seconds ago

3.查看mysql-galera服务的状态,正在正常运行;
[root@docker1 ~]# docker service ls
ID NAME REPLICAS IMAGE COMMAND
b8uica0izpvy mysql-galera 3/3 percona/percona-xtradb-cluster

4.Swarm模式内部的DNS组件可以自动的给服务的每个任务分配IP,所以可以通过服务名查看到对应的虚拟IP地址;
[root@docker1 ~]# docker service inspect mysql-galera -f “{{ .Endpoint.VirtualIPs }}”
[{3uvc0j5s4t1yuzi9yfxmt4s22 10.255.0.6/16} {32tayer754amlqnwti9a0c4ht 10.0.0.2/24}]

Running Percona XtraDB Cluster in a multi-host Docker network


http://severalnines.com/blog/mysql-docker-introduction-docker-swarm-mode-and-multi-host-networking
https://hub.docker.com/r/percona/percona-xtradb-cluster/

2016-11-21T14:38:09.786753Z 0 [ERROR] WSREP: failed to open gcomm backend connection: 131: No address to connect (FATAL)
at gcomm/src/gmcast.cpp:connect_precheck():286
2016-11-21T14:38:09.786761Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -131 (State not recoverable)
2016-11-21T14:38:09.786921Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1407: Failed to open channel ‘galera’ at ‘gcomm://10.255.0.9,10.255.0.7,10.255.0.9’: -131 (State not recoverable)
2016-11-21T14:38:09.786930Z 0 [ERROR] WSREP: gcs connect failed: State not recoverable
2016-11-21T14:38:09.786933Z 0 [ERROR] WSREP: wsrep::connect(gcomm://10.255.0.9,10.255.0.7,10.255.0.9) failed: 7

— 相关技术;
1.VXLAN(Virtual eXtensible LAN,可扩展虚拟局域网):是基于IP网络之上,采用的是MAC in UDP技术,本来OSI七层模型里就是一层叠一层的,这种和GRE/IPSEC等tunnel技术是不是很像,这种封装技术对中间网络没有特殊要求,只要你能识别IP报文即可进行传送;

2.为何需要Vxlan:
1)虚拟机规模受到网络规格的限制,大L2网络里,报文通过查询MAC地址转发,MAC表容量限制了虚拟机的数量;
2)网络隔离的限制,普通的VLAN和VPN配置无法满足动态网络调整的需求,同时配置复杂;
3)虚拟器搬迁受到限制,虚拟机启动后假如在业务不中断基础上将该虚拟机迁移到另外一台物理机上去,需要保持虚拟机的IP地址和MAC地址等参数保持不变,这就要求业务网络是一个二层的网络;

标签:

MySQL on Docker: Multi-Host Networking for MySQL Containers

1月 28th, 2017

这篇文章会在基于Calico网络驱动的多台宿主机上部署MySQL Replication;

Docker v1.12版本的Swarm模式是一个原生的编排工具,然而它不支持其它的网络插件(比如Calico,Weave,Flannel);如果想要运行这些网络插件,必须在Swarm模式外面运行,并且使用其它的编排工具(Kubernetes, Mesos 或者 Docker Swarm);

Calico不能被称为”overlay network”,这意味着它不会将一个数据包封装在另一个数据包中;它使用纯第3层方法,并避免与第2层解决方案相关联的数据包封装,这简化了诊断,减少了传输开销并提高了性能;Calico还实现BGP协议,用于与纯IP网络相结合的路由,从而允许虚拟网络的因特网扩展;

— 测试环境,三台宿主机都安装了Docker引擎v1.12.3版本;
192.168.10.201 docker1.htsec.com docker1
192.168.10.202 docker2.htsec.com docker2
192.168.10.203 docker3.htsec.com docker3

— Key-Value存储(etcd);
etcd是一个开源的分布式键值存储,主要用于配置共享和服务发现;一个简单的应用场景是存储数据库的连接或者是特性标示;

Calico需要etcd进行操作;etcd可以使用多个实例进行集群,例子中使用三个节点的etcd集群来保证高可用性;

1.分别安装etcd包;
yum install -y etcd;

2.修改相应的配置文件;
[root@docker1 ~]# vi /etc/etcd/etcd.conf
ETCD_NAME=etcd1
ETCD_LISTEN_PEER_URLS=”http://0.0.0.0:2380″
ETCD_LISTEN_CLIENT_URLS=”http://0.0.0.0:2379″
ETCD_INITIAL_ADVERTISE_PEER_URLS=”http://192.168.10.201:2380″
ETCD_INITIAL_CLUSTER=”etcd1=http://192.168.10.201:2380,etcd2=http://192.168.10.202:2380,etcd3=http://192.168.10.203:2380″
ETCD_INITIAL_CLUSTER_STATE=”new”
ETCD_INITIAL_CLUSTER_TOKEN=”etcd-cluster-1″
ETCD_ADVERTISE_CLIENT_URLS=”http://0.0.0.0:2379″

[root@docker2 ~]# vi /etc/etcd/etcd.conf
ETCD_NAME=etcd2
ETCD_LISTEN_PEER_URLS=”http://0.0.0.0:2380″
ETCD_LISTEN_CLIENT_URLS=”http://0.0.0.0:2379″
ETCD_INITIAL_ADVERTISE_PEER_URLS=”http://192.168.10.202:2380″
ETCD_INITIAL_CLUSTER=”etcd1=http://192.168.10.201:2380,etcd2=http://192.168.10.202:2380,etcd3=http://192.168.10.203:2380″
ETCD_INITIAL_CLUSTER_STATE=”new”
ETCD_INITIAL_CLUSTER_TOKEN=”etcd-cluster-1″
ETCD_ADVERTISE_CLIENT_URLS=”http://0.0.0.0:2379″

[root@docker3 ~]# vi /etc/etcd/etcd.conf
ETCD_NAME=etcd3
ETCD_LISTEN_PEER_URLS=”http://0.0.0.0:2380″
ETCD_LISTEN_CLIENT_URLS=”http://0.0.0.0:2379″
ETCD_INITIAL_ADVERTISE_PEER_URLS=”http://192.168.10.203:2380″
ETCD_INITIAL_CLUSTER=”etcd1=http://192.168.10.201:2380,etcd2=http://192.168.10.202:2380,etcd3=http://192.168.10.203:2380″
ETCD_INITIAL_CLUSTER_STATE=”new”
ETCD_INITIAL_CLUSTER_TOKEN=”etcd-cluster-1″
ETCD_ADVERTISE_CLIENT_URLS=”http://0.0.0.0:2379″

3.分别启动docker1,docker2和docker3上的etcd服务;
systemctl start etcd.service
systemctl enable etcd.service

4.检查etcd集群状态;
[root@docker1 ~]# etcdctl member list
29868e609fc76b11: name=etcd3 peerURLs=http://192.168.10.203:2380 clientURLs=http://0.0.0.0:2379 isLeader=false
6ebf0a399d4f3850: name=etcd1 peerURLs=http://192.168.10.201:2380 clientURLs=http://0.0.0.0:2379 isLeader=true
af11bb15f5145af9: name=etcd2 peerURLs=http://192.168.10.202:2380 clientURLs=http://0.0.0.0:2379 isLeader=false
[root@docker1 ~]# etcdctl cluster-health
member 29868e609fc76b11 is healthy: got healthy result from http://0.0.0.0:2379
member 6ebf0a399d4f3850 is healthy: got healthy result from http://0.0.0.0:2379
member af11bb15f5145af9 is healthy: got healthy result from http://0.0.0.0:2379
cluster is healthy

— 安装Calico;
1.下载Calico并使它可执行;
$ wget http://www.projectcalico.org/builds/calicoctl -P /usr/local/bin
$ chmod +x /usr/local/bin/calicoctl

2.在docker1上创建calico节点,指定Docker宿主机的IP地址;

docker run -d –restart=always –net=host –privileged –name=calico-node \
-e HOSTNAME=docker1.htsec.com -e IP=192.168.10.201 \
-e IP6= -e CALICO_NETWORKING_BACKEND=bird -e AS= -e NO_DEFAULT_POOLS= \
-e ETCD_AUTHORITY=127.0.0.1:2379 -e ETCD_SCHEME=http \
-v /var/log/calico:/var/log/calico -v /lib/modules:/lib/modules \
-v /var/run/calico:/var/run/calico \
calico/node:latest

docker run -d –restart=always –net=host –privileged –name=calico-node \
-e HOSTNAME=docker2.htsec.com -e IP=192.168.10.202 \
-e IP6= -e CALICO_NETWORKING_BACKEND=bird -e AS= -e NO_DEFAULT_POOLS= \
-e ETCD_AUTHORITY=127.0.0.1:2379 -e ETCD_SCHEME=http \
-v /var/log/calico:/var/log/calico -v /lib/modules:/lib/modules \
-v /var/run/calico:/var/run/calico \
calico/node:latest

docker run -d –restart=always –net=host –privileged –name=calico-node \
-e HOSTNAME=docker3.htsec.com -e IP=192.168.10.203 \
-e IP6= -e CALICO_NETWORKING_BACKEND=bird -e AS= -e NO_DEFAULT_POOLS= \
-e ETCD_AUTHORITY=127.0.0.1:2379 -e ETCD_SCHEME=http \
-v /var/log/calico:/var/log/calico -v /lib/modules:/lib/modules \
-v /var/run/calico:/var/run/calico \
calico/node:latest

TODO:
http://severalnines.com/blog/mysql-docker-multi-host-networking-mysql-containers-part-2-calico

标签:

MySQL on Docker: Single Host Networking for MySQL Containers

1月 28th, 2017

对于MySQL来说,网络是非常关键的,它是管理客户端访问服务器和集群节点之间访问的基础资源;容器化的MySQL服务行为取决于MySQL镜像如何使用docker run命令衍生出容器;使用Docker单主机网络,MySQL容器可以运行于一个独立的环境(只能访问相同网络的容器),或者一个开放的环境(通过端口映射,MySQL服务完全暴露到外部),或者运行一个完全无网络的实例;

— 三种类型的网络;
默认情况下,Docker在安装的时候会在宿主机上创建三种类型的网络:
[root@docker ~]# docker network ls;
NETWORK ID NAME DRIVER SCOPE
4c18ec95ca54 bridge bridge local
5c10d699ceb7 host host local
e0ce99953647 none null local

1.主机网络;
主机网络在宿主机的网络堆栈上添加一个容器,可以想象为在此网络中运行的容器正在连接到与宿主机相同的网络接口;它有以下特点:
1)容器的网络接口与宿主机是相同的;
2)每个宿主机只能有一个主机网络,不能创建更多;
3)如果要分配一个容器使用此网络,需要在docker run命令中显示指定”–net=host”参数;
4)不支持容器链接(比如:–link mysql-container:mysql);
5)不支持端口映射(比如:-p 3307:3306);

使用”–net=host”参数在主机网络上创建一个容器:docker run -d –restart=always –name=mysql-host –net=host -e MYSQL_ROOT_PASSWORD=pwd mysql

进入容器并查看对应的网络接口,发现与宿主机一样:
[root@docker ~]# docker exec -it mysql-host /bin/bash
root@docker:/# ip addr
1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp0s3: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:9c:e6:2d brd ff:ff:ff:ff:ff:ff
inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic enp0s3
valid_lft 84565sec preferred_lft 84565sec
inet6 fe80::a00:27ff:fe9c:e62d/64 scope link
valid_lft forever preferred_lft forever
3: enp0s8:
mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:95:f6:b6 brd ff:ff:ff:ff:ff:ff
inet 192.168.10.100/24 brd 192.168.10.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe95:f6b6/64 scope link
valid_lft forever preferred_lft forever
4: docker0: mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:8c:49:90:73 brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 scope global docker0
valid_lft forever preferred_lft forever

这种设置下,容器不需要在防火墙中设置任何的转发规则,因为它已经与主机网络连接了;因此也就不再支持端口映射了;

如果想要查看主机上某个端口是否在监听:[root@docker ~]# netstat -tulpn | grep 3306 (yum install -y net-tools)
tcp6 0 0 :::3306 :::* LISTEN 3572/mysqld

把MySQL容器以主机网络模式运行在宿主机上与直接标准安装到宿主机上是相似的,唯一的好处是有一个独立的MySQL服务器,尽管是Docker管理的;

基于主机网络创建的容器可以被基于默认桥网络docker0和用户自定义的桥网络创建的容器直接访问;

2.桥网络;
桥接允许同一台物理主机上的多个网络保持分离并独立通信;可以想象成这类似于宿主机的另一个内部网络,只有处于同一网络的容器才能访问其它的容器和主机,如果主机可以连接广域网,则容器也可以;

有两种类型的桥网络:1)默认的桥网络(docker0);2)用户定义的桥网络;

1)默认的桥网络(docker0):
在Docker安装的时候会自动创建docker0,可以通过”ip addr”命令进行验证;默认的IP范围是172.17.0.1/16,可以通过配置文件(Debian:/etc/default/docker;RedHat:/etc/sysconfig/docker)进行修改;

一般的,如果运行docker run命令时不指定–net参数,Docker会默认使用docker0网络创建容器;
[root@docker ~]# docker run -d –restart=always –name=mysql-bridge -p 3307:3306 -e MYSQL_ROOT_PASSWORD=pwd mysql

进入容器并查看对应的网络接口,Docker创建了一个网络接口eth0:
[root@docker ~]# docker exec -it mysql-bridge /bin/bash
root@db0336cafef9:/# ip addr
1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
5: eth0@if6: mtu 1500 qdisc noqueue state UP group default
link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff
inet 172.17.0.2/16 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::42:acff:fe11:2/64 scope link
valid_lft forever preferred_lft forever

默认情况下,Docker使用iptables来管理转发到桥网络的包;每个传出连接将显示为源自某一个宿主机自己的IP地址;以下是上述容器启动后宿主机的NAT链:
[root@docker ~]# iptables -L -n -t nat
Chain POSTROUTING (policy ACCEPT)
target prot opt source destination
MASQUERADE all — 172.17.0.0/16 0.0.0.0/0
MASQUERADE tcp — 172.17.0.2 172.17.0.2 tcp dpt:3306

Chain DOCKER (2 references)
target prot opt source destination
RETURN all — 0.0.0.0/0 0.0.0.0/0
DNAT tcp — 0.0.0.0/0 0.0.0.0/0 tcp dpt:3307 to:172.17.0.2:3306

上面的规则允许3307端口基于docker run命令中端口映射选项”-p 3307:3306″暴露在宿主机上;如果查看主机上的netstat分析,会发现MySQL时监听在3307端口的,并且属于docker-proxy进程;
[root@docker ~]# netstat -tulpn | grep 3307
tcp6 0 0 :::3307 :::* LISTEN 3916/docker-proxy

默认的桥网络支持端口映射和容器链接,如果想要链接到其它的容器,可以使用docker run命令的”–link”选项,容器会自动暴露环境变量并且通过/etc/hosts文件自动配置主机映射;

2)用户自定义桥网络:
Docker允许创建自定义的桥网络(也可以创建自定义的overlay网络),它的行为与docker0网络一致,网络中每一个容器都可以立即与其它容器通信;网络本身与外部的网络是隔离的;

这种网络的最大的优点是所有的容器都有解析容器名字的能力:
s1:首先创建一个自定义桥网络:
[root@docker ~]# docker network create –driver=bridge mysql-network

s2:基于用户定义网络创建5个mysql容器;
[root@docker ~]# for i in {1..5}; do docker run -d –name=mysql$i –net=mysql-network -e MYSQL_ROOT_PASSWORD=pwd mysql; done

s3:登陆其中任意一个容器,然后ping其它所有的容器;
root@docker ~]# docker exec -it mysql3 /bin/bash
root@eb3ba9f5ffc5:/# for i in {1..5}; do ping -c 1 mysql$i ; done
PING mysql1 (172.18.0.2): 56 data bytes
64 bytes from 172.18.0.2: icmp_seq=0 ttl=64 time=0.099 ms
— mysql1 ping statistics —
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 0.099/0.099/0.099/0.000 ms
PING mysql2 (172.18.0.3): 56 data bytes
64 bytes from 172.18.0.3: icmp_seq=0 ttl=64 time=0.097 ms
— mysql2 ping statistics —
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 0.097/0.097/0.097/0.000 ms
PING mysql3 (172.18.0.4): 56 data bytes
64 bytes from 172.18.0.4: icmp_seq=0 ttl=64 time=0.055 ms
— mysql3 ping statistics —
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 0.055/0.055/0.055/0.000 ms
PING mysql4 (172.18.0.5): 56 data bytes
64 bytes from 172.18.0.5: icmp_seq=0 ttl=64 time=0.069 ms
— mysql4 ping statistics —
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 0.069/0.069/0.069/0.000 ms
PING mysql5 (172.18.0.6): 56 data bytes
64 bytes from 172.18.0.6: icmp_seq=0 ttl=64 time=0.067 ms
— mysql5 ping statistics —
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 0.067/0.067/0.067/0.000 ms

s4:查看DNS配置,发现Docker配置了一个内置的DNS服务器;
[root@docker ~]# docker exec -it mysql3 /bin/bash
root@eb3ba9f5ffc5:/# cat /etc/resolv.conf
search htsec.com
nameserver 127.0.0.11
options ndots:0
root@eb3ba9f5ffc5:/#

内置的DNS服务器维护了容器名称到它IP地址的映射关系,这个网络里面的所有容器都会指向此DNS服务器;这个特性可以用于网络中的节点发现,而且对于构建一个MySQL容器集群特别有用,可选的集群技术有MySQL复制,Galera集群或者MySQL集群;

Default vs User-defined Bridge
Area Default bridge (docker0) User-defined bridge
Network deployment Docker creates upon installation Created by user
Container deployment Default to this network Explicitly specify “–net=[network-name]” in
the “docker run” command
Container linking Allows you to link multiple containers Not supported
together and send connection information
from one to another by using
“–link [container-name]:[service-name]”.
When containers are linked, information
about a source container can be sent to
a recipient container.
Port mapping Supported e.g, by using “-p 3307:3306” Supported e.g, by using “-p 3307:3306”
Name resolver Not supported (unless you link them) All containers in this network are able to
resolve each other’s container name to IP address.
Version <1.10 use /etc/hosts, >=1.10 use embedded
DNS server.
Packet forwarding Yes, via iptables Yes, via iptables
Example usage for MySQL MySQL standalone MySQL replication, Galera Cluster, MySQL Cluster
(involving more than one MySQL container setup)

3.无网络;
同样可以利用docker run命令并指定”–net=none”选项创建一个不依附于任何网络的容器,这种容器只能通过交互的shell访问,容器里面不会配置任何附加的网络接口;
[root@docker ~]# docker run -d –restart=always –name=mysql-none –net=none -e MYSQL_ROOT_PASSWORD=pwd mysql

进入容器并查看相应的网络:
[root@docker ~]# docker exec -it mysql-none /bin/bash
root@3e0b1012db86:/# ip addr
1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever

无网络中的容器不能加入任何网络,然而MySQL容器依然可以运行并且可以直接使用mysql客户端进行访问;

使用场景:可以运行一个MySQL容器做备份恢复验证;

标签:

MySQL OCP-18-性能调节简介

1月 28th, 2017

— 影响性能的因素;
1.环境问题,MySQL的性能受主机的性能特征影响:
1.CPU速度和数量;
2.磁盘I/O;
3.网络性能;
4.操作系统争用;
2.MySQL配置:
1.数据库设计:索引,数据类型(合适且尽可能小),标准化;
2.应用程序性能:特定请求(仅请求特定的行和列),短时事务(根据主键访问的短事务);
3.配置变量:调整缓冲区,高速缓存,InnoDB设置;

— 监视;
1.要调节服务器的性能,必须了解其性能特征;可以针对整体性能进行基准测试,也可以使用日志和EXPLAIN逐个分析事件或者使用ERFORMANCE_SCHEMA按组分析事件;
2.基准测试:
1.mysqlslap:是标准MySQL分发的一部分,是一个基准测试工具,用来模拟MySQL服务器实例上客户机负载,并显示每个阶段的计时信息;
2.sql-bench:是MySQL源码分发的一部分,它是一系列Perl脚步,用于执行多个语句和手机状态计时数据;
3.分析:
1.日志:
1.一般查询日志;
2.慢查询日志;
2.语句:
1.EXPLAIN;
2.PROCEDURE ANALYSE;
3.SHOW STATUS:还可以使用mysqladmin extended-status查看;
4.PERFORMANCE_SCHEMA数据库;

— 性能模式;
“性能模式”是在较低级别监视MySQL服务器执行情况的功能:
1.该功能是使用PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现的;
2.性能模式监视并允许您检查MySQL服务器中的被检测代码的性能特征:
1.开发者将检测函数和其他编码事件以收集计时信息;
2.公开的性能数据对以下人员或任务很有帮助:
1.MySQL代码库的贡献者;
2.插件开发者;
3.识别低级别的性能瓶颈,如日志文件I/O等待或缓冲池互斥;
3.从Oracle下载的所有二进制版本的MySQL中都提供了性能模式;默认情况下,将启用性能模式,并在服务器启动时使用performance_schema变量对其进行控制;
mysql> SHOW VARIABLES LIKE ‘performance_schema’;
mysql> SHOW TABLE STATUS FROM performance_schema\G

— 检测,实例,事件和使用者;
性能模式数据库包含配置和事件信息:
1.性能模式中的“检测”是服务器代码中引发要监视的事件的点;
1.每个检测由其类型/所属的模块/该特定检测的变量或类组成,通过查performance_schema.setup_instruments表可查看所有可用的检测;
2.性能模式将记录实例表中的每个检测实例;例如,以下查询显示检测wait/io/file/sql/FRM记录文件实例/var/lib/mysql/mem/tags.frm上的事件;
mysql> SELECT file_name, event_name FROM file_instances LIMIT 1\G *************************** 1. row ***************************
FILE_NAME: /var/lib/mysql/mem/tags.frm
EVENT_NAME: wait/io/file/sql/FRM
2.每个被检测的对象都是该检测的一个“实例”,记录在一系列实例表中;
3.当线程执行检测实例中的代码时,MySQL将识别所发生的“事件”,将其记录在事件和汇总表中;
4.每个“使用者”都是性能模式中表的名称,用于记录和查询事件以及事件的摘要,在SETUP_CONSUMERS表中进行配置;每个使用者的NAME是性能模式中用于查询事件和摘要的表的名称,被禁用的使用者不记录信息,从而节省了系统资源;
mysql> SELECT * FROM setup_consumers;
5.当MySQL识别了发生在检测实例中的事件后,会将其记录在事件表中:
1.主事件表为events_waits_current,该表中存储了每个线程最近的事件;
2.events_waits_history存储每个线程的最近10个事件;
3.events_waits_history_long共存储10,000个最近事件;
6.当使用性能模式识别瓶颈或其他问题时,请执行以下操作:
1.确保已针对适用于您所遇到的问题类型的一系列检测和使用者启用了性能模式;例如,如果您确定问题出在I/O限制上,请使用wait/io/file/*检测;如果不确定根本原因,请使用更广范围的检测;
2.运行用于产生该问题的测试用例;
3.查询events_waits_*表等使用者,尤其是使用适用的WHERE子句过滤器查询events_waits_history_long,以便进一步缩小问题原因的范围;
4.禁用那些用于评估已排除的问题的检测;
5.重试该测试用例;

— 一般数据库优化;
1.对数据进行标准化可以:
1.标准化是移除数据库中的冗余和不当依赖关系(以避免将相同的数据存储在多个地方以及出现异常的风险)的行为;
2.标准化通常会产生以下结果:许多表的列变少,整体存储要求降低,I/O需求降低以及单次插入,更新和删除操作加快;
3.这提高了频繁执行小更新的事务性工作负荷的性能,但会使检索大量数据的查询变得复杂;
2.选择正确的数据类型和大小可以:
1.选择正确的数据类型是表设计中一个很重要却常常被忽视的部分,数据类型的大小可能会对表操作产生较大的影响;例如,选择将SMALLINT数字存储为INT会使该列所需的空 间翻倍,在包含一百万个行的表中,该决策将导致浪费额外的2MB存储空间,并且磁盘操作速度会变慢,缓冲区和高速缓存将需要使用更多内存;
2.使用INSERT…COMPRESS(field_name)…和SELECT…UNCOMPRESS(column_name)…可以在存储和检索字符串数据时对其进行压缩和解压缩;尽管也可以使用CHAR或VARCHAR字段来实现此目的,但是通过使用VARBINARY或BLOB列存储压缩数据可以避免字符集转换出现问题;
3.创建最佳索引可以:
1.如果您通过在WHERE子句中指定一个字段来查询表中的特定行,并且该表没有为该字段创建索引,MySQL将读取该表中的每一行以找到每个匹配的行;这将导致很多不必要的磁盘访问,并且对于大型表性能将显著降低;
2.索引是有序的成组数据,通过索引,MySQL可以更容易地找到查询行的正确位置;默认情况下,InnoDB将按主键排列表的顺序,该有序表称为群集索引;
3.InnoDB表上的每个附加索引或辅助索引会在文件系统中占用额外的空间,因为索引包含索引字段的额外副本以及主键的副本;
4.每次使用INSERT/UPDATE/REPLACE/DELETE操作修改数据时,MySQL也必须更新所有包含修改字段的索引,因此,向表中添加多个索引会降低影响该表的数据修改操作的性能;
5.不过,如果对索引进行了适当设计,依赖于索引字段的查询便会在性能上有较大的获益;如果查询无法使用索引找到特定行,则必须执行全表扫描;即,必须读取整个表来找到该行;

— PROCEDURE ANALYSE;
1.PROCEDURE ANALYSE分析给定查询中的列,并提供对每个字段的调节反馈:mysql> SELECT CountryCode, District, Population FROM City PROCEDURE ANALYSE(250,1024)\G
2.提供参数来调节如何建议ENUM值:
1.处理列时所使用的最大元素数和最大内存;
2.示例:…PROCEDURE ANALYSE(100,256);
3.用于最大程度地减小字段大小:
1.MySQL通常按最大的字段大小分配内存;
2.隐式MEMORY临时表,排序缓冲区等;
4.用于确定字段是否允许NULL;

补充:
1.默认设置通常建议使用ENUM类型来优化表的设计,如果确定不想在分析列时使用PROCEDURE ANALYSE()所建议的ENUM值,请使用非默认参数:
1.第一个参数是分析ENUM值是否适当时要考虑的不同元素数,此参数的默认值为256;
2.第二个参数是用于收集不同的值以供分析的最大内存量,此参数的默认值为8192,表示8KB;如果为此参数设置值0,则PROCEDURE ANALYSE()无法检查不同的值以建议使用ENUM类型;
2.如果PROCEDURE ANALYSE()无法存储可接受范围内的候选ENUM值(在参数设置的限制内),则不会建议对该列使用ENUM类型;
3.本幻灯片中的示例建议对City.CountryCode列使用CHAR(3)类型;另一方面,如果使用默认参数,则PROCEDURE ANALYSE()将建议ENUM(‘ABW’,’AFG’,…,’ZMB’,’ZWE’),这是一种包含超过200个元素的ENUM类型,其中针对每个相应的CountryCode值都包含一个不同值;

— EXPLAIN;
1.EXPLAIN命令:
1.描述MySQL打算如何执行特定的SQL语句;
2.不返回数据集的任何数据;
3.提供有关MySQL打算如何执行该语句的信息;
2.使用EXPLAIN可检查SELECT/INSERT/REPLACE/UPDATE/DELETE语句;
3.将EXPLAIN置于语句之前:
1.EXPLAIN SELECT …;
2.EXPLAIN UPDATE…;

补充:
1.EXPLAIN将为语句中使用的每个表生成一行输出,该输出包含以下列:
1.id:编号;若没有子查询和联合查询,id则都是1;MySQL会按照id从大到小的顺序执行query,在id相同的情况下,则从上到下执行;
2.select_type:查询中使用的选择类型;
3.table:输出的行所引用的表;有时看到的是,其中N对应的是id列的值;
4.partitions:输出行所对应的表分区;
5.type:访问类型,连接的方式;
6.possible_keys:在查询过程中可能用到的索引;在优化初期创建该列,但在以后的优化过程中会根据实际情况进行选择,所以在该列列出的索引在后续过程中可能没用;该列为NULL意味着没有相关索引,可以根据实际情况看是否需要加索引;
7.key:访问过程中实际用到的索引;有可能不会出现在possible_keys中(这时可能用的是覆盖索引,即使query中没有where);possible_keys揭示哪个索引更有效,key是优化器决定哪个索引可能最小化查询成本,查询成本基于系统开销等总和因素,有可能是“执行时间”矛盾;如果强制mysql使用或者忽略possible_keys中的索引,需要在query中使用FORCE INDEX,USE INDEX或者IGNORE INDEX;
8.key_len:显示使用索引的字节数;由根据表结构计算得出,而不是实际数据的字节数;如ColumnA(char(3)) ColumnB(int(11)),在utf-8的字符集下,key_len=3*3+4=13;计算该值时需要考虑字符列对应的字符集,不同字符集对应不同的字节数;
9.ref:显示了哪些字段或者常量被用来和key配合从表中查询记录出来;显示那些在index查询中被当作值使用的在其他表里的字段或者constants
10.rows:估计为返回结果集而需要扫描的行;不是最终结果集的函数,把所有的rows乘起来可估算出整个query需要检查的行数,有limit时会不准确;
11.filtered:根据条件过滤的行百分比;
12.Extra:优化程序提供的每个查询的附加信息;
2.有关输出列的完整论述:http://dev.mysql.com/doc/refman/5.6/en/explain-output.html;
3.使用EXPLAIN EXTENDED…可查看优化程序提供的其他信息:http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html;

select_type列:
1.SIMPLE:简单查询,没有使用UNION和子查询;
EXPLAIN SELECT * FROM actor WHERE actor_id = 1;
2.PRIMARY:包含子查询或联合查询的query中,最外层的select查询;
3.UNION:在联合查询中第二个及其以后的select对应的类型;
EXPLAIN SELECT actor_id, last_update FROM film_actor UNION ALL SELECT actor_id, last_update FROM actor;
4.DEPENDENT UNION:子查询中的union,且为union中第二个select开始的后面所有select,同样依赖于外部查询的结果集;
EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor UNION ALL SELECT actor_id FROM film_actor);
5.UNION RESULT:从UNION临时表获取结果集合;
6.SUBQUERY:子查询在SELECT的目标里,不在FROM中,子查询中的第一个SELECT;
EXPLAIN SELECT (SELECT actor_id FROM actor) FROM film_actor;
7.DEPENDENT SUBQUERY:子查询中的第一个查询,依赖于外部查询的结果集;
8.DERIVED:子查询在from子句中,执行查询的时候会把子查询的结果集放到临时表(衍生表);
EXPLAIN SELECT * FROM (SELECT * FROM actor) AS a;
9.MATERIALIZED:物化子查询;
10.UNCACHEABLE SUBQUERY:表示子查询,但返回结果不能被cache,必须依据外层查询重新计算;
11.UNCACHEABLE UNION:表示union第二个或以后的select,但结果不能被cache,必须依据外层查询重新计算;

type列:访问路径(效率依次降低):
0.NULL:在优化过程中就已得到结果,不用再访问表或索引;
EXPLAIN SELECT * FROM actor WHERE actor_id = -1;
1.system:当表只有一行时就会出现system类型;是const join类型的特例;在没有任何索引的情况下,只有一条数据,MyISAM会显示system,InnoDB会显示ALL;
use test;
CREATE TABLE t_sys1(id INT) ENGINE = InnoDB;
CREATE TABLE t_sys2(id INT) ENGINE = MyISAM;
INSERT INTO t_sys1 VALUES(1);
COMMIT;
INSERT INTO t_sys2 VALUES(1);
EXPLAIN SELECT * FROM t_sys1;
EXPLAIN SELECT * FROM t_sys2;
2.const:最多会有一条记录匹配,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数;const表很快,因为它们只读取一次,发生在有一个unique key或者主键,并且where子句给它设定了一个比较值;
eg:EXPLAIN SELECT * FROM actor WHERE actor_id = 1;
3.eq_ref:在做关联查询中,前一个表返回的值在此表中只有一条记录对应,这是关联查询的最好选择;所以,唯一性索引(Primary or UNIQUE NOT NULL)才会出现eq_ref(非唯一性索引会出现ref),因为唯一,所以最多只返回一条记录,找到后无需继续查找,因此比ref更快;
CREATE TABLE t_eq_ref(actor_id SMALLINT(5) PRIMARY KEY);
INSERT INTO t_eq_ref VALUES(1);
COMMIT;
EXPLAIN SELECT * FROM actor, t_eq_ref WHERE actor.actor_id = t_eq_ref.actor_id;
4.ref:这是一种索引访问;只有当使用一个非唯一性索引或者唯一性索引的非唯一性前缀(换句话说,就是无法根据该值只取得一条记录)时才会发生,将索引和某个值相比较,这个值可能是一个常数,也可能是来自前一个表里的多表查询的结果值;如果使用的键仅仅匹配少量行,该联接类型是不错的;
EXPLAIN SELECT * FROM film_actor, actor WHERE film_actor.actor_id=actor.actor_id AND film_actor.actor_id = 1;
5.ref_or_null:类似ref,不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录,他意味着MySQL必须进行二次查找,在初次查找的结果中找出NULL条目;
6.index_merge:查询中使用两个或多个索引,然后对索引结果进行合并;在这种情况下,key列包含所有使用的索引,key_len包含这些索引的最长的关键元素;
7.unique_subquery:用来优化有子查询的in,并且该子查询是通过一个unique key选择的,子查询返回的字段组合是主键或者唯一索引;
value IN (SELECT primary_key FROM single_table WHERE some_expr)
EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM actor);
8.index_subquery:与unique_subquery类似,子查询中的返回结果字段组合是一个索引或索引组合,但不是一个主键或者唯一索引;
value IN (SELECT key_column FROM single_table WHERE some_expr)
EXPLAIN SELECT * FROM film_actor WHERE film_id IN (SELECT film_id FROM film_actor);
9.range:使用索引扫描,只查询给定范围的记录;比如出现=, <>, >, >=, < , <=, IS NULL, <=>, BETWEEN, or IN()操作符时:
EXPLAIN SELECT * FROM actor WHERE id < 10; 10.index:按索引次序扫描数据,因为按照索引扫描所以会避免排序,但也会扫描整表数据,若随机读取开销会更大;如果extra列显示using index,说明使用的是覆盖索引(覆盖索引:包含所有满足查询需要的数据列的索引);对于InnoDB表特别有用,此时只访问索引数据即可,不用再根据主键信息获取原数据行,避免了二次查询;而MyISAM表优化效果相对InnoDB来说没有那么的明显; 11.ALL:全表扫描;一般情况下,应该添加索引来避免全表扫描;出了使用limit子句或者Extra列有distinct信息; Extra列: 1.Using index:此查询使用了覆盖索引(Covering Index),即通过索引就能返回结果,无需访问表;若没显示"Using index"表示读取了表数据; EXPLAIN SELECT id FROM City WHERE id = 1890; 2.Using where:表示MySQL服务器从存储引擎收到行后再进行“后过滤”(Post-filter);所谓“后过滤”,就是先读取整行数据,再检查此行是否符合where句的条件,符合就留下,不符合便丢弃;因为检查是在读取行后才进行的,所以称为“后过滤”; EXPLAIN SELECT id FROM City WHERE id < 10; 3.Using temporary:使用到临时表 EXPLAIN SELECT DISTINCT Continent FROM Country; 4.Using filesort:若查询所需的排序与使用的索引的排序一致,因为索引是已排序的,因此按索引的顺序读取结果返回;否则,在取得结果后,还需要按查询所需的顺序对结果进行排序,这时就会出现Using filesort; EXPLAIN SELECT id, CountryCode FROM City WHERE id < 10 ORDER BY CountryCode; latin1/utf8/gbk字符数/字节数/汉字的对应关系: latin1:1character=1byte,1汉字=2character;一个字段定义成varchar(200),可以存储100个汉字或者200个字符,占用200个字节;尤其是当字段内容是字母和汉字组成时,尽量假设字段内容都是由汉字组成,据此来设置字段长度; utf8:1character=3bytes,1汉字=1character;一个字段定义成varchar(200),则它可以存储200个汉字或者200个字母,占用600个字节; gbk:1character=2bytes,1汉字=1character一个字段定义成varchar(200),则它可以存储200个汉字或者200个字母,占用400个字节; -- EXPLAIN格式; EXPLAIN输出也提供其他格式: 1.可视化EXPLAIN:在MySQL Workbench中提供图形格式的输出; 2.EXPLAIN FORMAT=JSON: 1.JSON格式的输出;JSON(JavaScript Object Notation,JavaScript对象表示法)是一种简单的数据交换格式; 2.当要将EXPLAIN输出传递给程序以供进一步处理/分析时十分有用; mysql> EXPLAIN FORMAT=JSON SELECT * FROM City\G

— 检查服务器状态;
MySQL提供了多种查看服务器状态变量的方法:
1.在mysql提示符下:
1.STATUS;
2.SHOW STATUS;
2.在终端上:
1.mysqladmin –login-path=login-path status
2.mysqladmin -u user -p extended-status
3.对mysqladmin使用选项可提供附加功能;例如,–sleep(或-i)选项可指定在迭代之间等待的秒数,并在等待该时间后自动重新执行命令;–relative(或-r)选项显示自上次迭代后每个变量的差异,而不是变量值;
shell> mysqladmin -i 5 -r status

— 主要状态变量;
1.Created_tmp_disk_tables:显示磁盘上的内部临时表的数量;获取执行语句时服务器所创建的临时表数;如果该数值较高,则服务器已在磁盘上(而不是在内存中)创建多个临时表,从而导致查询执行较慢;
2.Handler_read_first:显示索引中第一个条目的读取次数;如果该数值较高,则服务器已执行多次完整索引扫描以完成查询请求;
3.Innodb_buffer_pool_wait_free:显示服务器等待干净页面的次数;等待InnoDB缓冲池中的页面刷新后才可以完成查询请求;如果该数值较高,则未正确设置InnoDB缓冲池的大小,因而查询性能受到影响;
4.Max_used_connections:显示自服务器启动以来的最大并发连接数;此变量提供非常有用的信息来确定服务器必须支持的并发连接数;
5.Open_tables:显示给定时间内打开的表的数量;将此变量与服务器系统变量table_cache比较,可提供有关应该为表高速缓存预留多少内存量的有用信息;如果Open_tables状态变量的值通常很低,请减小服务器系统变量table_cache的大小;如果该值很高(接近服务器系统变量table_cache的值),请增加分配给表高速缓存的内存量来缩短查询响应时间;
6.Select_full_join:显示执行表扫描而不是使用索引的联接数量;如果该值不是0,则应该仔细检查表的索引;
7.Slow_queries:显示用时比long_query_time系统变量所指定的秒数长的查询数;此状态变量取决于对long_query_time变量(默认值为10秒)设置的了解;如果Slow_queries状态变量不是0,请检查long_query_time的值和慢速查询日志,并改进所捕获的查询;
8.Sort_merge_passes:显示排序算法所执行的合并传递次数,排序操作需要内存中的缓冲区;此状态变量计算排序操作所需的经过排序缓冲区的传递次数;如果该值较高,则可能表明排序缓冲区大小不足以执行查询的一次通过排序;请考虑增大sort_buffer_size系统变量的值;
9.Threads_connected:显示当前打开的连接数;定期捕获该值可提供有关服务器何时最活跃的有用信息,使 用此变量可确定执行服务器维护的最佳时间,或者可将其作为为服务器分配更多资源的依据;
10.Uptime:显示服务器持续运行的秒数;该值可以提供有关服务器运行状况的有用信息,例如服务器需要重新启动的频率;

— 调节系统变量;
1.首先调节查询,模式和索引:
1.一个常见的误区是认为服务器变量配置是服务器调节中最重要的部分;
2.事实上,优化模式,常见查询和典型数据库的索引可获得比调节变量更多的好处;
2.针对服务器大小进行调节内存和I/O:
1.Oracle的MySQL工程师选择默认设置来适应大多数生产系统,这些系统常常要处理频繁的小事务,许多更新和少数大型慢速查询(如用于生成报告的查询);
2.然而,由于MySQL在从小型设备(如销售点系统和路由器)到具有大量内存和快速磁盘阵列的大型Web服务器等各种系统上都在使用,可能会发现,对于您的特定环境和工作负荷,可以从更改服务器的某些默认设置中获益;
3.针对应用程序配置进行调节:存储引擎设置
1.将物理RAM的70%–85%提供给InnoDB缓冲池;在仅使用InnoDB用户表的MySQL专用服务器上,可以将innodb_buffer_pool_size的值增大到占服务器总内存的较大比例(70%–85%),同时要记住操作系统的需要,如cron作业,备份,病毒扫描以及管理连接和任务;如果有几GB的RAM,则还可以通过使用多个innodb_buffer_pool_instances而获益,该设置可启用多个缓冲池,从而避免争用;
2.最小化MyISAM高速缓存和缓冲区;在不将MyISAM用作用户表的系统上,减小仅适用于MyISAM的选项的值(例如将key_buffer_size的值减小为16MB等较小值),同时要记住某些内部MySQL操作将使用MyISAM;
4.调节工作负荷类型:
1.连接数;
1.当为每个查询或每个连接的高速缓存和缓冲区设置较大的值时,会减少缓冲池的可用大小;
2.调节服务器的配置变量是一个平衡过程,需要从默认值开始,提供尽可能多的内存给缓冲池,然后调节与以下项最紧密相关的变量:调节目标,通过检查服务器状态识别出的问题以及通过查询性能模式识别出的瓶颈;
2.事务服务器:
1.在用于支持许多反复断开并重新连接的快速并发事务的服务器上,请将thread_cache_size的值设置为足够大的值,以便大多数新连接可以使用高速缓存的线程;
2.这可避免创建和断开每个连接的线程时的服务器开销;在支持多写入操作的服务器上,请提高innodb_log_file_size和innodb_log_buffer_size等日志设置,因为数据修改操作的性能在很大程度上依赖 于InnoDB日志的性能;
3.请考虑更改innodb_flush_log_at_trx_commit的值以提高每次提交的性能,但风险是:如果服务器出现故障,可能会丢失某些数据;
4.如果您的应用程序反复执行相同的查询(或多个相同的查询),请考虑启用查询高速缓 存,并根据常见查询的结果调节其大小,方法是为query_cache_type和query_cache_size设置适当的值;
3.报表服务器:在用于运行少数大型慢速查询(例如用于业务智能报表的查询)的服务器上,使用join_buffer_size和sort_buffer_size等设置增加专用于缓冲区的内存量;虽然默认服务器设置更适合事务系统,但默认的my.cnf文件包含这些变量适用于报表服务器的替代值;

— 主要服务器系统变量;
1.innodb_buffer_pool_size:定义InnoDB用于缓存表数据和索引的内存缓冲区大小;要获得最佳性能,请将此值设置为尽可能大,同时要记住值过高会导致操作系统交换页面,从而大大降低性能;如果在专用数据库服务 器上仅使用了InnoDB用户表,请考虑将此变量设置为介于物理RAM的70%到85%之间的值;
2.innodb_flush_log_at_trx_commit:定义InnoDB将日志缓冲区写入日志文件的频率,以及对日志文件执行刷新到磁盘操作的频率;此变量有三种可能的设置:
1.0:每秒将日志缓冲区写入磁盘一次;
2.1:每次提交时将日志刷新到磁盘;如果未发生提交,则每秒刷新一次;
3.2:将日志刷新到操作系统高速缓存中,并且每隔innodb_flush_log_at_timeout秒(默认为一秒)刷新到磁盘一次;
3.innodb_log_buffer_size:定义InnoDB用于写入磁盘上的日志文件的缓冲区的大小;此变量的默认值为8MB;事务超过此大小会导致InnoDB在事务提交之前将日志刷新到磁盘,从而降低性能;对于使用大量BLOB或者在更新活动中具有较大峰值的应用程序,可通过增大该值提高事务性能;
4.innodb_log_file_size:定义日志组中每个日志文件的大小;对于大型数据集上的写入密集型工作负荷,请设置此变 量以便所有日志文件的最大总大小(通过innodb_log_files_in_group设置)小于或等于缓冲池的大小;大型日志文件会减缓故障恢复,但可以通过减少检查点刷新活动来提高整体性能;
5.join_buffer_size:定义用于使用表扫描的联接的最小缓冲区大小;对于包含无法使用索引的联接的查询,请以默认值(256 KB)为起点增大该值;运行此类查询时请更改每个会话的值,以避免设置全局设置而使无需这么大值的查询浪费内存;
6.query_cache_size:定义为缓存查询结果而分配的内存量;通过使用查询高速缓存,提高针对极少更改的数据发出重复查询的应用程序的性能;作为基线,请根据重复查询的数量和所返回数据的大小将此变量设置为介于32MB和512MB之间的值,请监视高速缓存命中率以确定此变量的有效性,并根据您的观察调节其值;
7.sort_buffer_size:定义分配给需要进行排序的会话的最大内存量;如果Sort_merge_passes状态变量的值很高,请增大该值以提高ORDER BY和GROUP BY操作的性能;
8.table_open_cache:定义所有线程打开的表的数量;请设置该值以使其大于N * max_connections,其中,N是在应用程序的所有查询中所使用的最大表数量;该值过高会导致出现错误“Too many open files(打开的文件太多)”;Open_tables状态变量的值较高表示MySQL频繁打开和关闭表,因此应该增大table_open_cache;
9.thread_cache_size:义服务器应缓存以供重用的线程数;默认情况下,此变量将自动调节大小;评估Threads_created状态变量可确定是否需要更改thread_cache_size的值;

— 准备调节环境;
1.尽可能地复制生产系统;
1.要减小与正在调节的变量无关的已更改因素的影响;
2.请在停机期间对生产服务器执行调节,或者最好在复制的系统上进行调节;
2.决定调节目标:
1.每秒处理更多事务;
2.更快生成复杂报表;
3.通过并发连接的峰值提高性能;
3.选择适当的变量进行调节:缓冲区,高速缓存,日志设置;
4.为了最准确地模拟正在针对其进行调节的工作负荷,需要收集一组有代表性的语句:
1.从应用程序中选择查询和修改操作比例正确的语句序列;
2.在要优化的每天或每周期间内,使用一般查询日志从生产服务器收集实际语句;

— 练习调节;
1.查找每个变量的最佳值的基准测试:
1.mysqlslap或mysql来运行工作负荷并获取平均执行时间;
2.sql-bench来运行更一般的基准测试;
3.mysqladmin extended-status来获取工作负荷前后的状态变量的值;
4.top等操作系统工具或/proc文件系统来访问过程度量;
2.将变量设置为低于其默认值的设置;
3.进行基准测试,测量相关度量:
1.虚拟内存使用;
2.所花费的平均时间;
3.相关状态变量;
4.增大变量值并重复基准测试:如果需要,刷新状态变量;
5.将结果绘制成图:
1.查找收益的下降点和性能的高峰;
2.根据所用资源和性能之间的最佳平衡来决定最终变量值;
6.如果要针对特定变量使用多个不同值运行微调基准测试,或者如果要在很长一段时间内反复运行相同的基准测试,请考虑使用脚本语言来自动化基准测试中所使用的步骤;

— 调节示例:排序缓冲区大小;
1.本幻灯片中的示例显示了一系列针对具有繁重排序工作负荷的数据库的测试结果,其中,运行测试时更改了sort_buffer_size变量;
2.图表显示:
1.在sort_buffer_size从32KB增大到512KB时,Sort_merge_passes状态变量的值(可使用mysqladmin extended_status-r查看)急剧下降,在此之后又缓慢降低;
2.测试工作负荷所花的平均时间(可使用mysqlslap查看)在sort_buffer_size为512KB时降低,在4MB时达到极大峰值,然后在8MB时下降,最终在32MB时达到最佳性能;
3.mysqld进程的总虚拟内存(可使用top查看)在sort_buffer_size为512KB时最小,此后一直到16MB都稳步上升,在32MB时急剧上升;
3.查询的平均时间最短时,sort_buffer_size为32MB,该设置使用了大量内存,而缓冲池本来可以更好地利用这些内存;在本示例中,针对测试中所使用的工作负荷,服务器和数据库的特定组合,512KB设置可在性能和所用内存之间提供最佳平衡;

— 课后练习;

— 补充:Extra其它信息;
1.distinct:当MySQL找到第一条匹配的结果值时,就停止该值的查询,然后继续该列其他值的查询;
2.not exists:在左连接中,优化器可以通过改变原有的查询组合而使用的优化方法;当发现一个匹配的行之后,不再为前面的行继续检索,可以部分减少数据访问的次数;
3.const row not found:涉及到的表为空表,里面没有数据;
4.Full scan on NULL key:是优化器对子查询的一种优化方式,无法通过索引访问NULL值的时候会做此优化;
5.Impossible Having:Having子句总是false而不能选择任何列;例如having 1=0;
6.Impossible WHERE:Where子句总是false而不能选择任何列;例如where 1=0;
7.Impossible WHERE noticed after reading const tables:MySQL通过读取“const/system tables”,发现Where子句为false;也就是说:在where子句中false条件对应的表应该是const/system tables;这个并不是MySQL通过统计信息做出的,而是真的去实际访问一遍数据后才得出的结论;当对某个表指定了主键或者非空唯一索引上的等值条件,一个query最多只可能命中一个结果,MySQL在explain之前会优先根据这一条件查找对应记录,并用记录的实际值替换query中所有用到来自该表属性的地方;
8.No matching min/max row:没有行满足如下的查询条件;
9.no matching row in const table:对一个有join的查询,包含一个空表或者没有数据满足一个唯一索引条件;
10.No tables used:查询没有From子句,或者有一个From Dual(dual:虚拟表,是为了满足select…from…习惯)子句;
EXPLAIN SELECT VERSION()
11.Range checked for each record (index map: N):MySQL发现没有好的index,但发现如果进一步获取下一张join表的列的值后,某些index可以通过range等使用;MySQL没找到合适的可用的索引;取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引(如果有的话),并且使用这个索引来从表里取得记录;这个过程不会很快,但总比没有任何索引时做表连接来得快;
12.Select tables optimized away:当我们使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询;在使用某些聚合函数如min, max的query,直接访问存储结构(B树或者B+树)的最左侧叶子节点或者最右侧叶子节点即可,这些可以通过index解决;Select count(*) from table(不包含where等子句),MyISAM保存了记录的总数,可以直接返回结果,而Innodb需要全表扫描;Query中不能有group by操作;
13.unique row not found:对于SELECT … FROM tbl_name,没有行满足unique index或者primary key;从表中查询id不存在的一个值会显示Impossible WHERE noticed after reading const tables;
14.Using filesort:指MySQL将用外部排序而不是按照index顺序排列结果;数据较少时从内存排序,否则从磁盘排序;Explain不会显示的告诉客户端用哪种排序;
15.Using index:表示MySQL使用覆盖索引避免全表扫描,不需要再到表中进行二次查找数据;注意不要和type中的index类型混淆;
16.Using index for group-by:类似Using index,所需数据只需要读取索引,当query中有group by或distinct子句时,如果分组字段也在索引中,extra就会显示该值;
17.Using temporary:MySQL将创建一个临时表来容纳中间结果;在group by和order by的时,如果有必要的话;例如group by一个非键列,优化器会创建一个临时表,有个按照group by条件构建的unique key,然后对于每条查询结果(忽略group by),尝试insert到临时表中,如果由于unique key导致insert失败,则已有的记录就相应的updated;例如,name上没有索引,SELECT name,COUNT(*) FROM product GROUP BY name,为了排序,MySQL就需要创建临时表;此时一般还会显示using filesort;
18.Using where:表示MySQL将对storage engine提取的结果进行过滤;例如,price没有index,SELECT * FROM product WHERE price=1300.00;有许多where的条件由于包含了index中的列,在查找的时候就可以过滤,所以不是所有带where子句的查询会显示Using where;
19.Using join buffer:5.1.18版本以后才有的值;join的返回列可以从buffer中获取,与当前表join;
例如:explain select * from t1,t2 where t1.col < 10 and t2.col < 10 20.Scanned N databases:指在处理information_schema查询时,有多少目录需要扫描; EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES 21.Open_full_table:指示从information_schema查询信息时有关文件开启的优化; Skip_open_table:表信息已经获得,不需要打开; 22.Open_frm_only:只打开.frm文件; Open_trigger_only:只打开.trg文件; Open_full_table:没有优化;.frm,.myd和.myi文件都打开; 23.Using sort_union(…), Using union(…), Using intersect(…):都出现在index_merge读取类型中; 1.Using sort_union:用两个或者两个以上的key提取数据,但优化器无法确保每个key会提取到一个自然排好序的结果,所以为了排除多余的数据,需要额外的处理; 2.例如,customer的state,(lname,fname)是key,但lname不是key,SELECT COUNT(*) FROM customer WHERE (lname = ‘Jones') OR (state = ‘UT'),由于lname上面没有key,所以使用(lname,fname),使得结果可能不按照顺序,优化器需要额外的一些工作; 3.Using union:用两个或者两个以上的key提取数据,分别取得结果是已排序,通过合并就可以获得正确结果;例如,customer中的state和(lname,fname)是key,SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') OR (state = ‘UT'); Using intersect:用两个或者两个以上的key提取数据,分别取得结果是已排序,通过求交就可以获得正确结果; 4.例如,customer中的state和(lname,fname)是key,SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') AND (state = ‘UT'); 24.Using where with pushed condition:仅用在ndb上;MySQL Cluster用Condition Pushdown优化改善非索引字段和常量之间的直接比较;condition被pushed down到cluster的数据节点,并在所有数据节点同时估算,把不合条件的列剔除避免网络传输;

标签:

MySQL OCP-17-复制

1月 28th, 2017

— MySQL复制;
复制是MySQL的一项功能,允许服务器将更改从一个实例复制到另一个实例:
1.MySQL中的复制功能用于将更改从一个服务器(主服务器)复制到一个或多个从属服务器;
2.主服务器将所有数据和结构更改记录到二进制日志中;
3.从属服务器从主服务器请求该二进制日志并在本地应用其内容;
4.日志文件的格式影响从属服务器应用更改的方式,MySQL支持基于语句的,基于行的以及混合格式的日志记录;
5.从属服务器数量:
1.一个主服务器可以具有的从属服务器数量没有限制,但是,每个额外从属服务器使用主服务器上的较少资源,所以您应该仔细考虑生产设置中每个从属服务器的好处;
2.给定环境中主服务器的最佳从属服务器数量取决于许多因素:模式大小,写入次数,主服务器和从属服务器的相对性能以及CPU和内存可用性等因素;
3.一般准则是将每个主服务器的从属服务器数量限制为不超过30;
6.网络故障:
1.MySQL中的复制功能在网络故障时继续工作;
2.每个从属服务器跟踪其已经处理了多少日志并在网络连接恢复时自动继续处理,此行为是自动的,不需要特殊配置;

— 复制主服务器和从属服务器;
主/从属服务器关系是一对多关系:
1.每个从属服务器从一个主服务器读取日志;
2.一个主服务器可以将日志传送给许多从属服务器;
3.中继从属服务器:
1.最顶层主服务器的直接从属服务器请求并应用该主服务器处发生的更改,而该从属服务器将更改向下中继到其从属服务器,以此类推,直到复制到达该链的末尾;
2.这样可以通过多个级别的复制来传播更新,允许更复杂的拓扑,每个额外级别会向系统添加更多传播延迟,从而较浅设置遇到的复制滞后要比较深设置少;
3.每个从属服务器仅能具有一个主服务器,一个从属服务器不能从多个主服务器进行复制;
4.如果一个从属服务器用作其他服务器的主服务器,该从属服务器常常称为中继从属服务器;
4.使用BLACKHOLE存储引擎进行复制:
1.BLACKHOLE存储引擎无提示地放弃所有数据更改,而不发出警告,二进制日志继续成功记录这些更改;
2.当中继从属服务器将所有更改复制到深一层的从属服务器,但是自身不需要将数据存储在特定表中时,将BLACKHOLE用于这些表;
3.例如,如果您具有的中继从属服务器单独用来对少量表执行经常长时间运行的业务智能报表,您可以将其他所有复制的表配置为使用 BLACKHOLE,从而服务器不会存储其不需要的数据,同时将所有更改复制到其从属服务器;

— 复杂拓扑;
可以使用更复杂的拓扑:
1.双向拓扑具有两个主服务器,每个主服务器是另一个主服务器的从属服务器;
2.循环拓扑具有任意数量的服务器:
1.每个服务器是一个主服务器并且是另一个主服务器的从属服务器;
2.对任何主服务器的更改将复制到所有主服务器;
3.并非每个从属服务器都必须是主服务器;
4.MySQL复制不执行冲突解析;
1.在典型配置中,客户机仅将更改写入主服务器,但是从属服务器读取更改,在服务器允许数据进行并发更新的环境中,数据在多个服务器上的最终状态可能变得不一致;
2.应用程序负责防止或管理冲突操作,MySQL复制不执行冲突解决解析;包括多个主服务器的所有拓扑中都可能发生冲突,这包括诸如前面幻灯片中显示的简单分层结构(如果中继从属服务器接受客户机的更改);冲突在循环拓扑中特别常见
3.eg:程序的两个进程想要把一个价值600块的商品上涨20%和下降50块,就会因为执行的顺序不同产生670块和660块;

— 复制用例;
复制的常见用法:
1.水平向外扩展:在多个从属服务器中分布查询工作负荷;实现复制的最常见原因是在一个或多个从属服务器中分布查询工作负荷,从而提高整个应用程序中读取操作的性能,并通过减少主服务器的读取工作负荷来提高其上的写入操作性能;
2.业务智能和分析:业务智能报表和分析处理会使用大量资源,需要大量时间来执行;在复制的环境中,可以在从属服务器上运行此类查询,从而主服务器可以继续处理生产工作负荷,而不受长时间运行的I/O密集型报表的影响;
3.地理数据分布:具有分布式地理位置的公司可以受益于复制,在每个区域具有服务器,用于处理本地数据并在组织中复制该数据;这样可以向客户和员工提供地理相邻性的性能和管理优势,同时还使公司了解整个公司的数据;

— 高可用性复制;
1.受控切换:在硬件或系统升级期间使用副本来代替生产服务器;
2.服务器冗余:在系统故障时执行故障转移到副本服务器;
3.联机模式更改:在具有多个服务器的环境中执行滚动升级来避免整个系统故障;
4.软件升级:在环境升级过程中在不同版本的MySQL之间进行复制;
1.从属服务器运行的版本必须比主服务器新;
2.在升级过程中发出的查询必须受升级过程中使用的所有版本的支持;

— 配置复制;
1.为每个服务器配置唯一server-id;
1.复制拓扑中的每个服务器必须具有唯一的server-id,一个无符号的32位整数,值从0(默认)到4,294,967,295;
2.server-id为0的服务器(无论是从属服务器还是主服务器)拒绝使用其他服务器进行复制;
2.配置每个主服务器:
1.启用二进制日志并启用TCP/IP网络;
1.每个主服务器必须启用二进制日志记录,因为在复制过程中,每个主服务器将其日志内容发送到每个从属服务器;
2.每个主服务器必须分配有IP地址和TCP端口,因为复制无法使用UNIX套接字文件;
2.每个从属服务器必须登录到主服务器中才能从中进行复制,所以创建具有REPLICATION SLAVE特权的新用户;
1.GRANT REPLICATION SLAVE ON *.* TO @;
3.备份主数据库,并且如果需要则记录日志坐标;
1.如果您正使用已经包含已填充数据库的主服务器创建复制拓扑,必须首先为从属服务器创建该数据库的副本;
2.如果您使用全局事务标识符,则不需要记录日志坐标;
3.配置每个从属服务器:
1.从主服务器恢复备份;
2.在每个从属服务器上发出CHANGEMASTER TO语句,包含:
1.主服务器的网络位置;
2.复制帐户用户名和口令;
3.开始复制操作的日志坐标(如果需要);
3.使用START SLAVE开始复制;

— CHANGE MASTER TO;
1.在从属服务器上发出CHANGE MASTER TO…语句来配置复制主服务器连接详细信息:
mysql> CHANGE MASTER TO
-> MASTER_HOST = ‘host_name’,
-> MASTER_PORT = port_num,
-> MASTER_USER = ‘user_name’,
-> MASTER_PASSWORD = ‘password’,
-> MASTER_LOG_FILE = ‘master_log_name’,
-> MASTER_LOG_POS = master_log_pos;
2.CHANGE MASTER TO的后续调用保留每个未指定选项的值:
1.更改主服务器的主机或端口还会重置日志;
2.更改口令,但是保留所有其他设置: mysql> CHANGE MASTER TO MASTER_PASSWORD=’newpass’;
3.注意事项:
1.要提高安全性,还可以在启用SSL的服务器上使用MASTER_SSL和相关选项加密复制期间从属服务器和主服务器之间的网络通信;
2.可以通过执行SHOW MASTER STATUS语句从主服务器获取文件和位置:mysql> SHOW MASTER STATUS;
3.如果使用mysqldump执行主服务器的数据库备份作为从属服务器的起点,可以使用–master-data选项在备份中包括日志坐标:
mysqldump -uroot -p –master-data -B world_innodb > backup.sql;
4.如果您使用GTID,则指定MASTER_AUTO_POSITION=1,而不是日志坐标;

— 使用日志坐标进行故障转移;
1.当主服务器变为不可用后进行故障转移,从而选择某个从属服务器作为主服务器;需要查找每个从属服务器的新主服务器和正确的日志坐标,严密检查二进制日志:
1.查找应用于每个从属服务器的最近事件;
2.选择最新从属服务器作为新的主服务器;
1.如果新主服务器位于特定从属服务器后面(即,如果该从属服务器已经应用了该新主服务器的日志末尾的事件),则该从属服务器会重复那些事件;
2.如果新主服务器在特定从属服务器的前面(即,如果该新主服务器的二进制日志包含该从属服务器尚未应用的事件),该从属服务器将跳过那些事件;
3.确定新主服务器上的日志坐标来匹配每个其他从属服务器上最新应用的事件;
4.在每个从属服务器上发出正确的CHANGE MASTER TO…;
2.在循环拓扑中,查找每个二进制日志中的事件源变得非常困难:
1.因为每个从属服务器使用与其他从属服务器不同的顺序应用操作;
2.要避免此困难,请使用全局事务标识符(Global Transaction Identifier, GTID);MySQL实用程序还包括有助于使用GTID进行故障转移的工具;

— 全局事务标识符(Global Transaction Identifier, GTID);
1.全局事务标识符(Global Transaction Identifier, GTID)唯一地标识复制的网络中的每个事务;
1.UUID(universally unique identifier,通用唯一标识符)是每个事务的源服务器的UUID;
2.每个服务器的UUID存储在数据目录中的auto.cnf文件中,每次重启时会读取这个文件;如果该文件不存在,MySQL会创建该文件并生成新的UUID,将其放在该新文件中;
3.使用server_uuid变量查询服务器的UUID:mysql> SELECT @@server_uuid\G
4.客户机在主服务器上执行事务时,MySQL将创建新GTID并记录事务及其唯一GTID,从属服务器从主服务器读取并应用该事务时,该事务保持其原始GTID;即,复制到从属服务器的事务的服务器UUID是主服务器的UUID,而不是从属服务器的;复制链中的每个后续从属服务器都将记录该事务及其原始GTID,因此,复制拓扑中的每个从属服务器 可以确定第一个执行事务的主服务器;
2.每个GTID的形式为::0ed18583-47fd-11e2-92f3-0019b944b7f7:338;
3.GTID集包含一系列GTID:0ed18583-47fd-11e2-92f3-0019b944b7f7:1-338;
4.使用以下选项启用GTID模式:
1.gtid-mode=ON:与每个事务一起记录唯一的GTID;
2.enforce-gtid-consistency:禁止无法以事务安全方式记录的事件;
3.log-slave-updates:将复制的事件记录到从属服务器的二进制日志;
4.gtid_executed:记录事务的GTID;在全局上下文中,此变量包含记录到服务器的二进制日志的所有GTID集(表示此服务器和其他上游主服务器的所有事务);mysql> SELECT @@global.gtid_executed\G
5.gtid_purged:变量包含已经从二进制日志中清除的GTID集;在服务器上执行RESET MASTER时,gtid_purged和全局gtid_executed都将重置为空字符串;

— 使用GTID进行复制;
使用CHANGE MASTER TO…启用GTID复制:
1.告知从属服务器通过GTID标识事务:CHANGE MASTER TO MASTER_AUTO_POSITION=1;
2.您不需要提供日志坐标(eg:MASTER_LOG_FILE,MASTER_LOG_POS);
1.启用基于GTID的复制时,不需要指定主服务器的日志坐标,因为从属服务器将@@global.gtid_executed的值发送给主服务器;
2.因此,主服务器知道从属服务器已经执行了哪些事务,从而仅发送从属服务器尚未执行的那些事务;
3.不能在同一CHANGE MASTER TO…语句中提供MASTER_AUTO_POSITION和日志坐标;

— 使用GTID进行故障转移;
1.使用GTID时,循环拓扑中的故障转移很简单:
1.在发生故障的主服务器的从属服务器上,通过发出单个CHANGE MASTER TO语句绕过该主服务器;
2.每个服务器忽略或应用从拓扑中的其他服务器复制的事务,具体取决于是否看到了该事务的GTID;
2.非循环拓扑中的故障转移同样简单:
1.临时将新主服务器配置为最新从属服务器的从属服务器,直到该新主服务器变为最新;
3.虽然GTID可以防止源自单个服务器上的事件重复,但是它们不会防止源自不同服务器上的冲突操作,如标题为“复杂拓扑”的幻灯片中所述;

— 复制过滤规则;
1.过滤器是应用于主服务器或从属服务器的服务器选项:
1.主服务器写入二进制日志时应用binlog-*过滤器;
2.从属服务器读取中继日志时应用replicate-*过滤器;
3.当环境中的不同服务器用于不同目的时,使用过滤规则;例如,专用于显示Web内容的服务器不需要从主服务器复制重新进货信息或工资记录,而专用于生成关于销售量的管理 报表的服务器不需要存储Web内容或市场营销副本;
2.基于以下各项选择要复制的事件:
1.数据库:
1.replicate-do-db, binlog-do-db
2.replicate-ignore-db, binlog-ignore-db
2.表:
1.replicate-do-table, replicate-wild-do-table
2.replicate-ignore-table, replicate-wild-ignore-table
3.过滤规则具有按顺序应用的复杂优先级规则:
1.数据库过滤器先于表过滤器应用;
2.表通配符过滤器*-wild-*在不使用通配符的那些过滤器之后应用;
3.*-do-*过滤器先于各个*-ignore-*过滤器应用;
4.使用多个过滤器时要谨慎,由于应用过滤器的顺序复杂,非常容易出错;因为过滤器控制要复制的数据,所以很难从此类错误中恢复,因此,不要混用不同类型的过滤器;

— MySQL实用程序;
MySQL实用程序是提供许多有用功能的命令行工具:
1.随MySQL Workbench提供;
2.用于维护和管理MySQL服务器;
3.以Python编写,Python编程人员很容易使用提供的库对其进行扩展;
4.对于配置复制拓扑和执行故障转移非常有用;

— 用于复制的MySQL实用程序;
1.mysqldbcopy:将数据库以及复制配置从源服务器复制到目标服务器;
1.mysqldbcopy实用程序接受选项–rpl,其在目标服务器上运行CHANGE MASTER TO语句;它接受以下值:
1.master:目标服务器变为源的从属服务器;
2.slave:源已经是其他主服务器的从属服务器,目标服务器从源复制该主服务器信息并变为同一主服务器的从属服务器;
2.mysqldbcompare:比较两个数据库来查找区别并创建脚本来同步这两个数据库;
3.mysqlrpladmin:管理复制拓扑:
1.在主服务器故障后故障转移到最佳从属服务器;
2.切换以升级指定的从属服务器;
3.启动,重置或停止所有从属服务器;
4.mysqlfailover:持续监视主服务器,并执行故障转移到最佳可用从属服务器:
1.mysqlfailover实用程序通过ping操作定期检查主服务器状态,期间间隔和ping操作都是可配置的;
2.它使用GTID确保新的主服务器在变为主服务器时是最新的,通过以下操作实现此项:
1.从候选列表中选择新主服务器(如果列表中没有可行的候选项,则从所有从属服务器中选择),将其配置为所有其他从属服务器的从属服务器来收集所有未完成事务,最后使其成为新主服务器;
2.mysqlrpladmin的failover命令在选择新主服务器时执行相似的临时重新配置;
3.如果主服务器失败,您还可以执行运行状况监视而不执行自动重新配置;
5.mysqlrplcheck:检查主服务器和从属服务器之间进行复制的先决条件,包括:
1.二进制日志记录;
2.具有适当特权的复制用户–server_id冲突;
3.可能导致复制冲突的各种设置;
6.mysqlreplicate:在两个服务器之间启动复制,报告不匹配警告消息;
7.mysqlrplshow:显示主服务器与从属服务器之间的复制拓扑或者递归显示整个拓扑;
mysqlrplshow 实用程序显示如下所示的复制拓扑:
# Replication Topology Graph localhost:3311 (MASTER)
|
+— localhost:3312 – (SLAVE + MASTER)
|
+— localhost:3313 – (SLAVE + MASTER)
|
+— localhost:3311 < --> (SLAVE)
端口3311处的服务器出现两次:一次作为主服务器,一次作为从属服务器;< -->符号指示拓扑内的循环;

— 异步复制;
1.从属服务器请求二进制日志并应用其内容,从属服务器通常滞后于主服务器;
2.主服务器不关注从属服务器何时应用日志,主服务器继续运行而不等待从属服务器;
3.在单独的线程中,主服务器将二进制日志流处理到连接的从属服务器,因为主服务器提交更改而不等待任何从属服务器的响应,所以这称为异步复制;
4.最重要的是,这意味着在主服务器向应用程序报告成功时从属服务器尚未应用事务;通常,这不是个问题,但是,如果在主服务器提交事务之后而该事务复制到任何从属服务器之前,该主服务器出现故障并且数据丢失,则该事务将丢失,即使应用程序已经向用户报告成功也是如此;
5.如果主服务器在提交事务之前等待所有从属服务器应用其更改,则复制称为是同步的;虽然MySQL复制不是同步的,但MySQL Cluster在内部使用同步复制来确保整个群集中的数据一致性,并且MySQL客户机请求是同步的,因为客户机在向服务器发出查询后等 待服务器响应;

— 半同步复制;
半同步复制:
1.在主服务器和至少一个从属服务器上需要插件和启用选项:
1.插件:
1.rpl_semi_sync_master(在主服务器上)
2.rpl_semi_sync_slave(在从属服务器上)
2.选项:
1.rpl_semi_sync_master_enabled(在主服务器上)
2.rpl_semi_sync_slave_enabled(在从属服务器上)
3.如果在主服务器上启用半同步复制,它的行为是异步的,直到至少一个半同步从属服务器连接;
2.阻止每个主服务器事件,直到至少一个从属服务器接收该事件:
1.这意味着在主服务器向应用程序报告成功时至少一个从属服务器已经收到了每个事务;
2.如果主服务器在提交事务后出现故障且数据丢失并且应用程序已经向用户报告了成功,则该事务还存在于至少一个从属服务器上;
3.如果发生超时则切换到异步复制;
1.半同步复制需要您在性能和数据完整性之间进行权衡,使用半同步复制时事务速度比使用异步复制时慢,因为主服务器在提交之前等待从属服务器响应;
2.每个事务花费的额外时间至少是它为以下项花费的时间:
1.TCP/IP往返以将提交发送到从属服务器;
2.从属服务器在其中继日志中记录提交;
3.主服务器等待从属服务器确认该提交;
3.这意味着对于物理上位于同一位置,通过快速网络通信的服务器,半同步复制最有效;
4.如果主服务器在超时期间内没有收到半同步从属服务器的响应,该主服务器仍提交该事务,但恢复为异步模式;可以使用rpl_semi_sync_master_timeout变量配置超时,其包含以毫秒为单位的值,默认值是10000,表示十秒;

— 测验;
b

— 查看二进制日志记录;
二进制日志:
1.包含数据和模式更改及其时间戳:基于语句或基于行的日志记录;
2.用于从备份的时间点恢复,从备份的完全恢复以及复制;
3.在下列情况下轮转:
1.MySQL重新启动;
2.其达到max_binlog_size设置的最大大小;
3.您发出FLUSH LOGS语句;
4.可以各种方式进行检查:
1.元数据:SHOW BINARY LOGS,SHOW MASTER STATUS;
2.内容:mysqlbinlog;

— 复制日志;
从属服务器维护有关复制事件的信息:
1.中继日志集:
1.包括中继日志和中继日志索引文件;
2.包含主服务器的二进制日志事件的副本;
3.MySQL自动管理中继日志文件集,在其已经重放了所有事件时删除这些文件并在当前文件超过最大中继日志文件大小时创建新文件;
4.中继日志使用与二进制日志相同的格式存储;可以使用mysqlbinlog查看那些日志;
5.默认情况下,中继日志文件名为-relay-bin.,索引文件名为-relay-bin.index;要使服务器配置不受将来可能的主机名更改影响,请通过设置以下选项来进行这些更改:–relay-log和–relay-log-index;
2.从属服务器状态日志:
1.包含执行复制所需的信息:
1.存储关于如何连接到主服务器的信息;
2.主服务器的二进制日志和从属服务器的中继日志的最近复制的日志坐标;
2.存储在文件或表中:
1.master.info和relay-log.info文件(默认情况下);
2.mysql数据库中的slave_master_info和slave_relay_log_info表;
1.主服务器信息:此日志包含关于主服务器的信息,包括主机名和端口,用于连接的凭证以及主服务器二进制日志的最近下载的日志坐标等信息;
2.中继日志信息:此日志包含中继日志的最近执行的坐标以及从属服务器的已复制事件落后于主服务器的那些事件的秒数;

— 故障安全(Crash-Safe)复制;
1.二进制日志记录是故障安全的:
1.MySQL仅记录完成事件或事务;
2.使用sync-binlog提高安全性:
1.默认情况下,值是0,表示操作系统根据其内部规则向文件写入;
2.将sync-binlog设置为1,强制操作系统在每个事务之后写入文件,或者将其设置为任何较大数值以在该数量的事务 之后写入;
2.将从属服务器状态日志存储在表中以进行故障安全复制:
1.选项:master-info-repository和relay-log-info-repository;
2.可能值为FILE(默认值)和TABLE;
3.TABLE是故障安全的:复制使用事务存储引擎(例如InnoDB)的数据时,通过将master-info-repository和relay-log-info-repository的值从FILE(默认值)更改为TABLE,来将状态日志存储在事务表中以提高性能并确保故障安全复制;该表称为slave_master_info和slave_relay_log_info,都存储在mysql数据库中,并且都使用InnoDB引擎确保事务完整性和故障安全行为;

— 复制线程;
MySQL在主服务器和从属服务器上创建线程来执行复制工作:
1.主服务器创建Binlog转储线程:
1.从二进制日志读取事件并将其发送到从属服务器I/O线程;
2.从属服务器成功连接到主服务器时,主服务器启动称为Binlog转储线程的复制主服务器线程;
3.如果从属服务器配置为使用自动定位协议(CHANGE MASTER TO MASTER_AUTO_POSITION),则该线程显示为“Binlog转储GTID”;在从属服务器已连接时,此线程会在二进制日志内的事件到达时将其发送到从属服务器;
4.主服务器为每个连接的从属服务器创建一个Binlog转储线程;
2.从属服务器至少创建两个线程:
1.从属服务器I/O线程:从主服务器的Binlog转储线程读取事件并将其写入从属服务器的中继日志;
2.从属服务器SQL线程:
1.在单线程从属服务器上应用中继日志事件:
1.默认配置会导致从属服务器滞后;
2.如果主服务器具有多个客户机连接则并行应用更改,但是串行执行其二进制日志中的所有事件;
从属服务器在单个线程中顺序执行这些事件,在高流量环境中或者当从属服务器的硬件不足以处理单个线程中的通信流量时,这会成为瓶颈;
2.在多线程从属服务器上的工作线程之间分配中继日志事件;
3.从属服务器工作线程:在多线程从属服务器上应用中继日志事件;
1.MySQL支持多线程从属服务器以避免单线程从属服务器引起的一些滞后;
2.如果在从属服务器上将slave_parallel_workers变量设置为大于零的值,它会创建该数量的工作线程;
3.在这种情况下,从属服务器SQL线程不直接执行事件,相反,它按数据库将事件分配给工作线程,这使多线程从属服务器在要在多个数据库中复制数据的环境中特别有用;
4.如果工作线程执行并行操作的顺序与其在主服务器上的执行顺序不同,此选项可能导致数
据库之间不一致,所以您必须确保不同数据库中的数据是独立的,由单个线程复制的一个数据库中的数据将保证是一致的;
5.如果将slave_parallel_workers变量设置为大于复制中所用数据库数量的值,一些工作线程将保持空闲;

— 控制从属服务器线程;
1.控制从属服务器线程:
START SLAVE;
STOP SLAVE;
2.单独控制线程:
START SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
3.启动线程直到指定的条件:
START SLAVE UNTIL SQL_AFTER_MTS_GAPS;
START SLAVE IO_THREAD UNTIL SQL_AFTER_GTIDS = 0ed18583-47fd-11e2-92f3-0019b944b7f7:338;

— 监视复制;
mysql> SHOW SLAVE STATUS\G
***************** 1. row *********************
Slave_IO_State: Queueing master event to the relay log

Master_Log_File: mysql-bin.005
Read_Master_Log_Pos: 79
Relay_Log_File: slave-relay-bin.005
Relay_Log_Pos: 548
Relay_Master_Log_File: mysql-bin.004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Exec_Master_Log_Pos: 3769

Seconds_Behind_Master: 8

1.Slave_*_Running:Slave_IO_Running和Slave_SQL_Running列标识从属服务器的I/O线程和SQL线程当前正在运行,未运行还是正在运行但尚未连接到主服务器;可能值分别为Yes,No或Connecting;
2.主服务器日志坐标:Master_Log_File和Read_Master_Log_Pos列标识主服务器二进制日志中I/O线程已经传输的最近事件的坐标;这些列可与您在主服务器上执行SHOW MASTER STATUS时显示的坐标进行比较,如果Master_Log_File和Read_Master_Log_Pos的值远远落后于主服务器上的那些值,这表示主服务器与从属服务器之间事件的网络传输存在延迟;
3.中继日志坐标:Relay_Log_File和Relay_Log_Pos列标识从属服务器中继日志中SQL线程已经执行的最近事件的坐标;这些坐标对应于Relay_Master_Log_File和Exec_Master_Log_Pos列标识的主服务器二进制日志中的坐标;如果Relay_Master_Log_File和Exec_Master_Log_Pos列的输出远远落后于Master_Log_File和Read_Master_Log_Pos列(表示I/O线程的坐标),这表示SQL线程(而不是I/O线程)中存在延迟,即,它表示复制日志事件快于执行这些事件;在多线程从属服务器上,Exec_Master_Log_Pos包含任何未提交事务之前最后一点的位置,这并不始终与中继日志中的最近日志位置相同,因为多线程从属服务器在不同数据库上执行事务的顺序可能与二进制日志中显示的顺序不同;
4.Seconds_Behind_Master:此列提供中继日志中SQL线程执行的最近事件的时间戳(在主服务器上)与从属服务器的实际时间之间的秒数;当主服务器并行处理大量事件而从属服务器必须串行处理这些事件时,或者当高通信流量期间从属服务器的硬件不足以处理与主服务器可以处理的相同量的事件时,通常会发生这种类型的延迟;如果从属服务器未连接到主服务器,此列为NULL;

— 复制从属服务器I/O线程状态;
从属服务器I/O线程的SHOW PROCESSLIST输出的State列中所显示的最常见状态,这些状态还显示在SHOW SLAVE STATUS显示的Slave_IO_State列中;
1.Connecting to master:线程正尝试连接到主服务器;
2.Waiting for master to send event:线程已经连接到主服务器并且正在等待二进制日志事件到达;如果主服务器处于空闲状态,此状态可能持续很长时间;如果等待持续slave_read_timeout秒,则发生超时,此时,线程考虑断开连接并尝试重新连接;
3.Queueing master event to the relay log:线程已经读取事件并且正在将其复制到中继日志,从而SQL线程可以处理该事件;
4.Waiting to reconnect after a failed binlog dump request:如果二进制日志转储请求失败(由于断开连接),线程在其休眠时转入此状态,然后定期尝试重新连接;可以使用–master-connect-retry选项指定重试之间的时间间隔;
5.Reconnecting after a failed binlog dump request:线程正尝试重新连接到主服务器;
6.Waiting to reconnect after a failed master event read:读取时出错(由于断开连接),线程在尝试重新连接之前休眠master-connect-retry秒;
7.Reconnecting after a failed master event read:线程正尝试重新连接到主服务器;重新建立连接后,状态变为Waiting for master to send event;
8.Waiting for the slave SQL thread to free enough relay log space:该状态显示正在使用非零relay_log_space_limit值,并且中继日志已经增加得足够大,以至其合并大小超过了此值;I/O线程正在等待,直到SQL线程通过处理中继日志内容以便可以删除一些中继日志文件来释放足够空间;

— 复制从属服务器SQL线程状态;
从属服务器SQL线程和工作线程的State列中显示的最常见状态:
1.Waiting for the next event in relay log:这是Reading event from the relay log之前的初始状态;
2.Reading event from the relay log:线程已经从中继日志中读取了事件,从而可以处理该事件;
3.Making temp file:线程正在执行 LOAD DATA INFILE 语句,并且正在创建包含数据的临时文件,从属服务器从该数据中读取行;
4.Slave has read all relay log; waiting for the slave I/O thread to update it:线程已经处理了中继日志文件中的所有事件,现在正在等待I/O线程将新事件写入中继日志;
5.Waiting until MASTER_DELAY seconds after master executed event:SQL线程已经读取事件,但是正等待从属服务器延迟结束;通过CHANGE MASTER TO的MASTER_DELAY选项设置此延迟;
6.Waiting for an event from Coordinator:在多线程从属服务器上,工作线程正等待协调线程向工作队列分配作业;

— 排除MySQL复制故障;
1.查看错误日志:错误日志可以为您提供足够信息来确定和更正复制中的问题;
2.在主服务器上发出SHOW MASTER STATUS语句:如果位置值非零则启用日志记录;
3.确认主服务器和从属服务器都具有唯一的非零服务器ID值:主服务器和从属服务器必须具有不同的服务器ID;
4.在从属服务器上发出SHOW SLAVE STATUS命令:
1.如果从属服务器运行正常,Slave_IO_Running和Slave_SQL_Running显示Yes;
2.Last_IO_Error和Last_SQL_Error:分别导致I/O线程或SQL线程停止的最新错误的错误消息;在正常复制过程中,这些字段是空的,如果发生错误并导致消息显示在以上任一字段中,则错误值也显示在错误日志中;
3.Last_IO_Errno和Last_SQL_Errno:与分别导致I/O线程或SQL线程停止的最新错误关联的错误编号,在正常复制过程中,这些字段包含编号0;
4.Last_IO_Error_Timestamp和Last_SQL_Error_Timestamp:分别导致I/O线程或SQL线程停止的最新错误的时间戳,格式为YYMMDD HH:MM:SS,在正常复制过程中,这些字段是空的;

— 课后练习;

补充:
— Master-Slave模式:
1.修改master端的配置文件my.cnf,如果需要指定只复制某几个数据库,使用binlog-do-db参数;
server-id = 1 # 必须是一个1 - 2^32-1的值,默认是1;
log-bin = mysql-bin # 这个参数在备库不需要设置,但是推荐设置;
2.设置slave端的配置文件my.cnf,如果需要指定只复制某几个数据库,使用replicate-do-db参数;不推荐使用master-host这几个参数,实验中总是起不来mysql服务,貌似已经弃用了;
server-id = 2
log-bin = mysql-bin
3.启动master端和slave端的mysql服务;
4.在master端创建用户复制的用户:grant replication slave on *.* to ‘repl_slave’@’192.168.10.56’ identified by ‘mysql’;
5.查看master端二进制日志的状态:show master status;(如果有业务在写数据,可以先flush tables with read lock,把数据dump出来,然后unlock tables解锁,把数据同步到slave端);
6.在slave端执行CHAGE MASTER TO命令,然后开启slave模式:CHANGE MASTER TO MASTER_HOST=’192.168.10.66′, MASTER_PORT=3306, MASTER_USER=’repl_slave’, MASTER_PASSWORD=’mysql’, MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=265;
7.启动slave复制:start slave;然后查看slave端的状态:show slave status;(如果发生uuid冲突,删除$MYSQL_DATADIR/auto.cnf文件,MySQL会创建该文件并生成新的UUID,将其放在该新文件中;)
8.在slave端会生成保存master端状态的master.info文件,以及中继日志文件(mysql-relay-bin.NNNNNN);
9.查看slave端的进程,发现多了系统两个进程:show processlist;
10.在master端写入数据,slave端马上可以查看到,测试成功;

— Master-Master模式:
1.在Master-Slave基础上;
2.在slave端创建用户复制的用户:grant replication slave on *.* to ‘repl_slave’@’192.168.10.66’ identified by ‘mysql’;
3.查看slave端二进制日志的状态:show master status;
4.在master端执行CHAGE MASTER TO命令,然后开启slave模式:CHANGE MASTER TO MASTER_HOST=’192.168.10.56′, MASTER_PORT=3306, MASTER_USER=’repl_slave’, MASTER_PASSWORD=’mysql’, MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=265;
5.启动slave复制:start slave;然后查看slave端的状态:show slave status;(如果发生uuid冲突,手动修改auto.cnf中uuid的值)
6.在master端会生成保存slave端状态的master.info文件,以及中继日志文件(mysql-relay-bin.NNNNNN);
7.查看slave端的进程,发现多了系统两个进程:show processlist;
8.其实Master-Master模式就是把Master-Slave反向再做一次而已;

— MySQL半同步复制(Semi-Synchronous Replication)
1.配置Master服务器端:
1.安装插件:install plugin rpl_semi_sync_master soname ‘semisync_master.so’;(通过视图查看:SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME=’rpl_semi_sync_master’\G);
2.设置开启半同步复制:set global rpl_semi_sync_master_enabled=1;
3.设置延迟时间,即Master等待Slave响应的时间:set global rpl_semi_sync_master_timeout=1000;默认为10s,修改为1s;
4.并在配置文件my.cnf中添加rpl_semi_sync_master_enabled=1,rpl_semi_sync_master_timeout=1000设置,以后重启服务就不用重新配置;
5.查看设置的变量:show variables like ‘rpl%’;
2.配置Slave服务器端:
1.安装插件:install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;
2.设置开启版同步复制:set global rpl_semi_sync_slave_enabled=1;
3.在配置文件my.cnf中添加rpl_semi_sync_slave_enabled=1之后重启服务就不用重新配置;
4.查看设置的变量:show variables like ‘rpl%’;
3..取消插件:uninstall plugin rpl_semi_sync_master;

— 设置GTID复制:
1.设置数据库只读:SET @@global.read_only = ON;
2.停止停止所有的slave:stop slave;并关闭所有的数据库;
3.修改配置文件;
在my.cnf中添加配置:
[mysqld]
gtid_mode=ON # 开启gtid模式;
log-slave-updates=ON
enforce-gtid-consistency=ON # 强制GTID的一致性;
4.从库指向主库;
CHANGE MASTER TO MASTER_HOST=’192.168.10.66′, MASTER_PORT=3306, MASTER_USER=’repl_slave’, MASTER_PASSWORD=’mysql’, MASTER_AUTO_POSITION = 1;

START SLAVE;
5.设置数据库读写:SET @@global.read_only = OFF;

标签:

MySQL OCP-16-MySQL备份和恢复

1月 28th, 2017

— 备份基础知识;
1.最重要的备份原因:
1.完整系统恢复:如果系统发生故障,则拥有系统的备份至关重要,因为可以恢复系统;实施怎样的备份和恢复策略取决于被恢复数据要达到的完整性和时效性;
2.审计功能:对于某些系统及关联的流程,可能需要审计或分析独立于主生产环境的环境中的数据;可以使用备份创建这样一个独立的环境;
3.常见DBA任务:在需要执行常见DBA任务(例如将数据从一个系统传输到另一个系统,根据特定的生产服务器状态创建开发服务器,或者将系统的特定部分恢复到用户出错前的某个状态)时使用备份;
2.备份类型:
1.热备份:这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能;使用热备份时,系统仍可供读取和修改数据的操作访问;
2.冷备份:这些备份在用户不能访问数据时进行,因此无法读取或修改数据;这些脱机备份会阻止执行任何使用数据的活动,这些类型的备份不会干扰正常运行的系统的性能;但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据;
3.温备份:这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身;这种中途备份类型的优点是不必完全锁定最终用户,但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序;在备份过程中无法修改数据可能产生性能问题;
3.磁盘:
1.可以使用复制或RAID镜像之类流程,或者使用DRBD之类的外部应用程序,将数据直接备份到其他磁盘;
2.这些技术提供实时(或几乎实时)备份,以及快速恢复数据的方法;
4.二进制日志:
1.二进制日志记录对数据的修改;因此,二进制日志对恢复自上次完整备份以来发生的事件很有用;
2.备份二进制日志的优点是其中包含了各个时间对数据所做的所有更改的记录,而不是数据的快照;
3.可以按顺序创建多个二进制日志备份,根据修改的数据量以及完成完整备份的频率,决定要在备份之间创建的二进制日志备份的数量;
4.二进制日志的不足之处是必须恢复自序列中最后一次完整备份以来创建的所有按顺序的二进制日志;此外,从系统故障中恢复的速度可能会很慢,具体取决于必须恢复的二进制日志的数量;
5.逻辑/文本备份:
1.可以使用mysqldump进行完整数据转储,这些数据转储基于特定的时间点,但是是所有备份副本中速度最慢的;
2.使用mysqldump的优点是所创建的文件是简单的SQL脚本,其中包含可在MySQL服务器上运行的语句;
3.不足之处在于mysqldump会在转储过程中锁定表,这会阻止用户在备份过程中读写文件;

— 使用MySQL进行备份;
MySQL备份可以是下列备份之一:
1.逻辑备份:逻辑备份会产生一个文本文件,其中包含重构数据库的SQL语句;
2.物理备份:这是MySQL数据库文件的二进制副本;
3.基于快照的备份;
4.基于复制的备份;
5.增量备份:通过刷新MySQL二进制日志创建的备份;

— 测验;
b

— 逻辑(文本)备份;
1.逻辑备份:
1.将数据库和表的内容转换为SQL语句;
2.可移植:这些SQL语句包含重建MySQL数据库和表所需的全部信息,可以使用该文本文件在运行不同体系结构的其他主机上重新装入数据库;
3.要求MySQL服务器在备份期间运行:因为服务器在创建文件时要读取备份的表的结构和内容,然后将结构和数据转换为SQL语句;
4.可以备份本地和远程MySQL服务器:其他类型的备份(原始备份)只能在本地MySQL服务器上执行;
5.通常比原始(二进制)备份的速度慢:
1.因为MySQL服务器必须读取表并解释其内容,然后,将表内容转换成磁盘文件,或者将语句发送给客户机程序,由客户机程序将语句写出;
2.在恢复过程中,逻辑备份速度比原始备份慢;这是因为恢复的方法执行单个CREATE和INSERT语句来重新创建每个备份表和行;
2.逻辑备份文件的大小可能会超过备份的数据库大小;

— 物理(原始或二进制)备份;
1.物理备份:
1.生成数据库文件的完整二进制副本,这些副本以完全相同的格式保留数据库;可以使用标准命令,如tar/cp/cpio/rsync/xcopy;
2.必须恢复到同一个数据库引擎:因为原始备份是数据库文件位的完整表现形式;
3.可以在不同的计算机体系结构间恢复;
4.比逻辑备份和恢复的速度快:因为该过程是简单的文件复制,不需要了解文件的内部结构;
2.数据库文件在备份期间不能有更改:
1.实现这一点的方法取决于存储引擎;
2.对于InnoDB:需要关闭MySQL服务器;
3.对于MyISAM:锁定表以允许读取,但不允许更改;
4.可以使用快照,复制或专有方法:最大限度地减小对MySQL和应用程序的影响;

— 基于快照的备份;
1.创建数据的时间点“副本”;
2.提供一个逻辑上冻结的文件系统版本,可从中进行MySQL备份;
3.显著减少数据库和应用程序不可用的时间原始备份通常针对快照副本进行;
4.外部快照功能:
1.基于快照的备份使用MySQL外部的快照功能;
2.例如,如果MySQL数据库和二进制日志在具有相应文件系统的LVM2逻辑卷上,则可创建快照副本;
3.基于快照的备份最适合InnoDB之类的事务引擎,可以为InnoDB表执行热备份;对于其他引擎,可以执行温备份;
5.可伸缩:
1.快照备份是可伸缩的,因为执行快照所需的时间不会随数据库大小的增长而增加;
2.事实上,从应用程序的角度来看,备份期限几乎是零,但是,创建快照后,基于快照的备份几乎总是包括一个原始备份;

— 基于复制的备份;
1.MySQL复制可用于备份:
1.主服务器用于生产应用程序;
2.从属服务器用于备份目的;
2.这样可避免影响生产应用程序;
3.从属服务器的备份为逻辑备份或原始备份;
4.较高的成本:必须有另一台服务器和存储设备用于存储数据库的副本;
5.基于异步复制:
1.从属服务器相对于主服务器可能会有延迟;
2.如果在从属服务器读取二进制日志之前未清除二进制日志,这是可接受的;

— 测验;
a

— 二进制日志记录和增量备份;
1.在会话级别控制二进制日志记录,必须拥有SUPER特权:SET SQL_LOG_BIN = {0|1|ON|OFF};
2.执行逻辑备份或原始备份时刷新二进制日志:将二进制日志同步到备份;
3.逻辑备份和原始备份是完整备份:将备份所有表的所有行;
4.要执行增量备份,需复制二进制日志;
5.二进制日志可用于细粒度级恢复:可以标识导致损坏的事务并在恢复过程中跳过这些事务;

— 备份工具:概述;
1.逻辑备份的SQL语句;
2.执行SQL语句(用于锁定)与操作系统命令(用于生成二进制副本)组合的原始备份;
3.MySQL的其他原始备份工具:
1.MySQL Enterprise Backup:执行MySQL数据库热备份操作;该产品的设计目的就是为了高效且可靠地备份由InnoDB存储引擎创建的表,为完整起见,该产品还能备份其他存储引擎中的表;
2.mysqldump:该实用程序执行逻辑备份,可与任何数据库引擎一起使用;可以使用crontab(在Linux和UNIX中)和Windows任务调度程序(在Windows中)自动运行该实用程序;mysqldump没有任何跟踪或报告工具;
3.mysqlhotcopy:该实用程序执行原始备份,仅用于使用MyISAM或ARCHIVE数据库引擎的数据库;名称暗指mysqlhotcopy执行“热”备份,即不中断数据库可用性,但是,由于已对数据库进行了读取锁定,无法在备份过程中更改,因此最好将其描述为“温”备份;
4.第三方工具;
1.本课程主要是讲Oracle商业和社区工具;
2.补充Percona的Xtrabackup;

— MySQL Enterprise Backup;
1.热备份:
1.热备份是在数据库运行期间执行的,这种类型的备份不阻止正常的数据库操作,甚至能捕获备份进行期间发生的更改;
2.mysqlbackup是MySQL Enterprise Backup产品的基本命令行工具,对于InnoDB表,此工具可执行热备份操作;
2.温备份:
1.对于非InnoDB存储引擎,MySQL Enterprise Backup执行温备份;运行非InnoDB备份时,可以读取数据库表,但不能修改数据库;
3.增量备份:
1.备份自上一次备份以来有变化的数据;
2.主要用于InnoDB表或者只读或很少更新的非InnoDB表;
4.单文件备份:因为可以将单文件备份传输给其他进程(如磁带备份或scp之类的命令),因此可使用此技术将备份放在其他存储设备或服务器上,不会在原始数据库服务器上产生显著的存储开销;

— mysqlbackup;
使用mysqlbackup备份的原始文件
1.InnoDB数据:
1.ibdata*文件:共享表空间文件;
2..ibd文件:基于每个表的数据文件;
3.ib_logfile*文件:日志文件,从ib_logfile*文件提取的数据(代表在备份期间发生的更改的重做日志信息),存储在新备份文件ibbackup_logfile中;
2.要包括的数据目录中的所有文件:
1..opt文件:数据库配置信息;
2..TRG文件:触发器参数;
3..MYD文件:MyISAM数据文件;
4..MYI文件:MyISAM索引文件;
5..FRM文件:表数据字典文件;
6.TIPS:默认情况下,mysqlbackup备份数据目录中的所有文件,如果指定–only-known-file-types选项,则备份仅包括具有MySQL公认扩展名的其他文件;
3.mysqlbackup是一种易于使用的工具,适用于所有备份和恢复操作:
1.可以使用mysqlbackup联机备份InnoDB表以及生成对应于与InnoDB备份相同的binlog位置的MyISAM表的快照;
2.除了创建备份以外,mysqlbackup还可以将备份数据打包和解包,将在备份操作过程中对InnoDB表所做的任何更改应用于备份数据,以及将数据/索引和日志文件复制回其原始位置;
4.备份过程:
1.mysqlbackup打开到要执行备份的MySQL服务器的连接;
2.然后,mysqlbackup对InnoDB表执行联机备份;
3.当mysqlbackup运行几乎完成时,执行SQL命令FLUSH TABLES WITH READ LOCK,然后将非InnoDB文件(如MyISAM表和.frm文件)复制到备份目录;
1.如果此时未在数据库中长时间运行SELECT或其他查询,则MyISAM表很小,锁定阶段仅持续几秒钟;
2.否则,包括InnoDB类型表在内的整个数据库都会锁定,直到在备份之前开始的所有长时间查询完成;
4.mysqlbackup运行完成,并对表执行UNLOCK解锁;
5.基本用法:mysqlbackup -u -p –backup_dir= backup-and-apply-log
1.backup:执行备份初始阶段;
2.backup-and-apply-log:包括备份的初始阶段以及第二个阶段,即将InnoDB表放到最新的备份中,其中包括在备份运行期间对数据所做的任何更改;

— 使用mysqlbackup恢复备份;
基本用法:mysqlbackup –backup-dir= copy-back
1.
:指定备份文件的存储位置;
2.copy-back:指示mysqlbackup执行恢复操作;
1.恢复操作将
的内容(包括InnoDB和MyISAM索引)以及.frm文件恢复到其原始位置(由文件定义);
2.使用copy-back选项必须先关闭数据库服务器,然后才能使用mysqlbackup与copy-back选项;使用此
3.选项时,可将数据文件,日志及其他备份文件从备份目录复制回到其原始位置,并对其执行任何必需的后期处理;
4.在copy-back过程中,mysqlbackup无法从服务器查询其设置,因此从标准配置文件中读取datadir之类选项;
5.如果要恢复到不同的服务器,则可使用–defaults-file选项提供非标准默认设置文件;

补充:
— 使用mysqlbackup备份恢复:
1.完全备份还原:
1.备份数据库:
1.创建目录:mkdir -p /mysqlbackup; chown -Rf mysql:mysql /mysqlbackup;
2.创建备份:mysqlbackup –user=root –password= –with-timestamp –backup-dir=/mysqlbackup/ backup-and-apply-log;
2.还原数据库:
1.要把mysql服务关掉;
2.使用的备份必须是数据一致性的,即指定了–bakcup-and-apply-log参数的;
3.使用copy-back选项,这个操作会拷贝表/索引/元数据和其它恢复需要的文件恢复到原来的位置(定义在参数文件中);
4.还原数据库:mysqlbackup –defaults-file=/etc/my.cnf –backup-dir=/mysqlbackup/2015-08-25_17-20-34/ copy-back;
5.还原之后检查文件的权限(chown -Rf mysql:mysql data/ logs/),并开启mysql服务即可;
6.如果是需要还原到不同的目录的话,只需要修改–defaults-file参数指定的my.cnf文件即可;
2.增量备份还原:
1.增量备份分两种情况:
1.–incremental-base:使用这种方式,不需要知道两次备份的LSN(Log Sequence Number),只需要指定上一次备份(完全备份或者差异备份)的目录即可,mysqlbackup工具会从metadata文件中找到备份开始的位置.缺点是必须指定一系列目录的名称,可以使用应编码或者用shell实现,用–with-timestamp时需要制定规则;
2.–start-lsn:使用这种方式,就必须记录上次备份的LSN号,不需要关心上次备份的目录.缺点是需要知道上次备份的后的LSN,可以通过shell去获取,备份的目录可以使用–with-timestamp选项;
2.–incremental-base方式增量备份:mysqlbackup –defaults-file=/usr/local/mysql/my.cnf –incremental –incremental-base=dir:/mysqlbackup/2012-07-15_17-31-55/ –incremental-backup-dir=/mysqlbackup/incremental/sunday –with-timestamp backup;
3.–start-lsn方式增量备份:
1.查看上次备份结束的LSN号码:/path/meta/backup_variables.txt文件中end_lsn表示;
2.命令:mysqlbackup –defaults-file=/usr/local/mysql/my.cnf –incremental –start-lsn=xxxxx –with-timestamp –incremental-backup-dir=/mysqlbackup/incremental/sunday/ backup;
3.压缩备份功能:
1.数据压缩的功能只适用于InnoDb引擎的表;
2.压缩选项–compress只能用于完全备份,不能用于增量备份;
3.执行完全备份的命令:>mysqlbackup –compress –backup-dir=/mysqlbackup –with-timestamp backup;
然后执行apply-log选项;
4.部分备份(支持三种部分备份):
1.Leaving out files that are present in the MySQL data directory but not actually part of the MySQL instance. This operation involves the –only-known-file-types option;
2.Including certain InnoDB tables but not others. This operation involves the –include, –only-innodb, and –only-innodb-with-frm options;
3.Including certain database directories but not others. This operation involves the –databases and –databases-list-file options;
5.单文件备份:
1.可以通过指定backup-to-image参数来创建单文件备份,所有的源数据文件都必须在同一个目录下,所以尽量配置datadir, innodb_log_group_home_dir, and innodb_data_home_dir参数相同.
2.备份单一文件到绝对目录:mysqlbackup –backup-image=/backups/sales.mbi –backup-dir=/backup-tmp backup-to-image;
6.备份内存中数据:
1.mysqlbackup命令的–exec-when-locked选项可以在备份结束之前指定命令或者参数执行,而此时数据库是锁住的.这个命令可以创建或者拷贝一个附加的文件到备份目录;
2.可以用此选项来调用mysqldump命令备份MEMORY类型的表;

— mysqlbackup单文件备份;
1.基本用法:
mysqlbackup -u -p –backup-image= –backup_dir= backup-to-image
2.其他情形
1.标准输出:
… –backup-dir=
–backup-image= -backup-to-image
2.将现有的备份目录转换为单个文件:
… –backup-dir= –backup-image= backup-dir-to-image

— 恢复mysqlbackup单个文件备份;
1.提取选择的文件:
mysqlbackup -u -p –backup-image= –backup_dir= image-to-backup-dir
2.其他情形:
1.列出内容:
… –backup-image= list-image
2.将现有的备份目录转换为单个文件:
… –backup-image= –src-entry= –dst-entry= extract
1.–src-entry:确定要从单文件备份中提取的文件或目录;
2.–dst-entry:与单文件备份配合使用,将单个文件或目录提取到用户指定的路径;

— 测验;
d

— mysqlhotcopy;
1.Perl脚本:
1.备份MyISAM和ARCHIVE表;
2.使用FLUSH TABLES,LOCK TABLES以及cp或scp可以进行数据库备份;
3.在数据库目录所在的同一台计算机上运行:以便在表锁定期间复制表文件;
4.仅限Unix;
5.MySQL服务器必须处于运行状态:以便连接到服务器;
6.mysqlhotcopy的操作速度很快,因为它直接复制表文件,而不是通过网络备份表文件;
2.基本用法:mysqlhotcopy -u -p
3.选项:
1.–flush-log:在所有表都锁定后刷新日志;
2.–record_log_pos=db_name.tbl_name:在指定的数据库db_name和表tbl_name中记录主从服务器状态;

— 原始InnoDB备份;
1.备份过程:
1.在复制操作期间停止服务器;
2.验证服务器是否正常关闭,没有出错;
3.生成每个组件的副本:
1.每个InnoDB表一个.frm文件;
2.表空间文件;
1.系统表空间;
2.基于每个表的表空间;
3.InnoDB日志文件;
4.my.cnf文件;
4.重新启动服务器;
TIPS:所有数据库中的所有InnoDB表必须一起备份,因为InnoDB会在系统表空间中集中维护某些信息;
2.恢复:
1.要使用原始备份恢复InnoDB表,请停止服务器;
2.替换其副本在备份过程中生成的所有组件;
3.然后重新启动服务器;
TIPS:需要替换服务器上的所有现存的表空间文件,不能使用原始备份将一个表空间添加到另一个表空间;

— 原始MyISAM和ARCHIVE备份;
1.要生成MyISAM或ARCHIVE表,需复制MySQL用于代表该表的文件:
1.对于MyISAM,这些文件是.frm,.MYD和.MYI文件;
2.对于ARCHIVE表,这些文件是.frm和.ARZ文件;
3.在此复制操作过程中,其他程序(包括服务器)不能使用该表;
4.为了避免服务器交互问题,要在复制操作过程中停止服务器;
5.注:锁定表而不关闭服务器的做法在Linux系统上有效,在Windows上,文件锁定行为会导致可能无法复制被服务器锁定的表的表文件,在这种情况下,需要停止服务器后再复制表文件;
2.在服务器运行期间,锁定要复制的表:
mysql> USE mysql
mysql> FLUSH TABLES users WITH READ LOCK;
3.执行文件系统复制;
4.启动新的二进制日志文件:FLUSH LOGS;
1.新二进制日志文件包含在备份之后更改了数据的所有语句(以及所有后续的二进制日志文件);
5.在文件系统复制后解除锁定:UNLOCK TABLES;
6.恢复:要从原始备份中恢复MyISAM或ARCHIVE表,应停止服务器,将备份表文件复制到相应的数据库目录中,然后重新启动服务器;

— LVM快照;
1.在以下情况下,使用LVM快照执行原始备份:
1.主机支持LVM:例如,Linux支持LVM2;
2.包含MySQL数据目录的文件系统在逻辑卷上;
2.备份过程:
1.生成包含MySQL数据目录的逻辑卷的快照:在备份非InnoDB表时,使用FLUSH TABLES WITH READ LOCK;
2.从快照执行原始备份;
3.删除快照;

补充:LVM快照原理;
1.在支持LVM的系统(如Linux)上,可以创建要包含MySQL数据目录的逻辑卷;
2.可以创建该卷的快照,该快照的行为就像是逻辑卷的即时副本,LVM使用称为“写入时复制”(copy-on-write)的机制创建最初不含数据的快照;
3.在从新创建的快照读取文件时,LVM会从原始卷读取这些文件,当原始卷发生变化时,LVM会在原始卷上的数据发生变化之前,立即将其复制到快照,因此,在生成快照以来发生变化的任何数据都以其原始形式存储在快照中;
这样做的结果是,当从快照读取文件时,将获得在创建快照时存在的数据版本;
4.因为快照几乎是即时的,因此可以假定在生成快照过程中底层数据没有发生任何变化,这使得快照对于在不关闭服务器的情况下备份InnoDB数据库非常有用;
5.要创建快照,可使用以下语法:lvcreate -s -n -L ;选项-s指示lvcreate创建快照,其他选项指定新快照的名称和大小以及原始卷的位置;

1.例如,假定有一个卷组VG_MYSQL和一个逻辑卷lv_datadir:lvcreate -s -n lv_datadirbackup -L 2G /dev/VG_MYSQL/lv_datadir;前一条语句创建快照lv_datadirbackup,其保留大小为2GB;
2.如果只是短时间需要该快照,则保留大小可以比原始卷的大小少很多,因为快照的存储仅包含在原始卷中发生 更改的数据块;
3.例如,如果要使用快照执行备份,则保留大小仅存储在执行备份以及删除快照的时间内所做的更改,可以像挂载标准卷一样挂载快照;挂载了快照后,就像处理其他任何原始备份一样,从该卷执行原始备份(例如,通过使用tar或cp);
4.从快照备份时,数据库在备份过程中不能有更改,您肯定会获得与备份时一样的一致数据文件版本,无需停止服务器;
5.随着时间的推移,快照的空间要求通常会增长到原始卷的大小,此外,对原始卷上数据块的每项初始数据更改将导致两次向卷组写入数据:请求的更改和对快照的写入时复制;这可能会影响快照保留期间的性能;
6.由于以上原因,应在执行了备份之后尽快删除快照,要删除由前一条语句创建的快照,可使用以下语句:lvremove VG_MYSQL/lv_datadirbackup;

— 原始二进制可移植性;
1.可在MySQL服务器之间复制二进制数据库:当将一台计算机上生成的二进制备份拿到具有不同体系结构的另一台计算机上时,二进制可移植性会很有用;
2.InnoDB:数据库的所有表空间和日志文件都可直接复制,源系统与目标系统上的数据库目录名称必须相同;
3.MyISAM/ARCHIVE:单个表的所有文件都可直接复制;
4.Windows兼容性:
1.在Windows系统上,MySQL服务器在内部存储小写的数据库和表名称;
2.对于区分大小写的文件系统,可使用选项文件语句:lower_case_table_names=1;

— mysqldump;
1.将表内容转储到文件:
– 所有数据库,特定数据库或特定表;
– 允许备份本地服务器或远程服务器;
– 与存储引擎无关;
– 以文本格式写入:包含用于重新创建表的CREATE TABLE和INSERT语句的SQL格式转储文件;
– 可移植;
– 卓越的复制/移动策略;
– 适用于小规模导出,但不适用于完整备份解决方案;
2.基本用法:mysqldump –user= –password= –opt db_name > backup.file

— 与mysqldump保持一致;
1.仅限–master-data选项:
1.在备份过程中锁定表;
2.在备份文件中记录binlog位置;
2.–master-data和–single-transaction选项一起使用:不锁定表,仅保证InnoDB表一致性;
3.–lock-all-tables:通过锁定表实现一致性;
4.–flush-logs:启动新的二进制日志;

— mysqldump输出格式选项;
1.删除选项:
1.–add-drop-database:将一条DROP DATABASE语句添加到每条CREATE DATABASE之前;
2.–add-drop-table:将一条DROP TABLE语句添加到每条CREATE TABLE语句之前;
2.创建选项:
1.–no-create-db:不生成CREATE DATABASE语句;
2.–no-create-info:不生成CREATE TABLE语句;
3.–no-data:创建数据库和表结构,但不转储数据;
4.–no-tablespaces:指示MySQL服务器不写入任何CREATE LOGFILE GROUP或CREATE TABLESPACE语句到输出;
3.MySQL编程组件:
1.–routines:从已转储的数据库中转储存储例程(过程和函数);
2.–triggers:转储每个已转储表的触发器;
4.一个选项中的最高选项(–opt):这是用于创建高效完整的备份文件的最常用选项的快捷方式;

— 恢复mysqldump备份;
1.使用mysql重新装入mysqldump备份:mysql –login-path= < backup_file.sql 1.如果备份文件不存在,则指定数据库:USE db_name; 2.如果通过调用mysqldump与--database或--all-databases选项创建转储文件,则在从转储文件重新装入时,不需要指定目标数据库名称; 2.从一个数据库复制到另一个数据库(管道技术):mysqldump -u -p

| mysql –login-path= ;
3.mysqlimport:
1.如果调用mysqldump时使用–tab(与–fields-terminated-by,–fields-enclosed-by一起使用)选项,则将生成制表符分隔的数据文件:
1.包含CREATE TABLE语句的SQL文件;
2.包含表数据的文本文件;
2.要重新装入表,可将位置更改为备份目录,通过使用mysql处理.sql文件,然后使用mysqlimport装入.tsv文件:
shell> cd
shell> mysql –login-path= < table.sql shell> mysqlimport -u -p table.tsv

补充:
— mysqldump的参数
1.-A,–all-databases:导出所有的数据库,跟使用–databases后面跟上所有的数据库是一样的;
2.–add-drop-database:在创建数据库前添加drop database的语句;
3.–add-drop-table:在创建表之前添加drop table语句;
4.–add-locks:在插入语句前加锁;
5.–allow-keywords:创建的列允许使用关键字;
6.-i,–comments:写入附加信息,即添加注释;
7.-c,–complete-insert:使用完全插入语句,个人觉得还是-e参数好,数据量小用-e,数据量大用-c;
8.-B,–databases:备份多个数据库,把要备份的数据库跟在参数后面即可,当前数据库也会被包涵进来;
9.–delete-master-logs:备份完成后删除主机日志,自动打开–master-data选项;
10.-e,–extended-insert:使用multiple-row INSERT语句,即一个insert语句后面有多个值的列表,这是一种更高效的插入方式;
11.-F,–flush-logs:开始备份前切换一下日志,如果你一次备份多个数据库(使用–databases或者–all-databases选项时),则在备份每个数据库前都会切换日志.当使用–lock-all-tables or –master-data时,日志只会被切换一次,因为此时所有的表都被锁住,数据库保持一致.所以当你想要备份和日志组切换同时发生时,要用–lock-all-tables or –master-data和–flush-logs一起使用;
12.-h,–host=name:连接到主机;-u,–user-name:用户名;-p,–password:用户密码;
13.–ignore-table=name:不备份指定的表,如果要指定多个表,则要数据库和表明一起指定,如:–ignore-table=database.table;
14.-x,–lock-all-tables:会锁住所有数据库的表,会在备份期间加全局只读锁,自动关闭–single-transaction和–lock-tables选项;
15.–master-data[=#]:使得二进制日志的位置和和名称被添加到输出文件中,如果等于1,会像CHANGE MASTER命令一样打印它,如果等于2,命令会以注释的形式出现.这个选项会打开–lock-all-table选项,除非–single-transaction选项也被指定(此时全局只读锁知会在开始备份时有效),可以通过show master status命令查看当前日志信息,在恢复和复制功能时有用.
16.-n,–no-create-db:不包括创建数据库的语句;
17.-t,–no-create-info:不包括创建表结构语句;
18.-d,–no-data:只包含表定义,不包含表数据;
19.–order-by-primary:使每个表中的记录按照主键排序,如果没有主键,则使用第一个唯一索引.当导出一个MyISAM表到一个InnoDB表时有用,但是会延长导出时间;
20.–quick:不缓存query,直接导出到标准输出;
21.-R,–routines:导出stored routines(存储过程和函数);
22.–single-transaction:在一个事务中创建一个一致性的快照,只在支持多版本控制的引擎中起作用,目前只有innodb引擎.当–single-transaction进程工作时,为了保持数据一致性,则不能使用ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLEY语句,此选项自动关闭–lock-tables选项;
23.–opt:与同时指定–add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys相同.默认开启,要关闭使用–skip-opt;
24.-w,–where=name:只导出选择的记录;

— 如何使用mysqldump备份
1.非事务表的一致备份:mysqldump –opt –lock-all-tables –master-data=2 -B db_name1 db_name2 > /tmp/backup.sql,备份时表是只读的;
2.事务表的一致备份:mysqldump –opt –single-transaction –master-data=2 -B db_name1 db_name2 > /tmp/backup.sql,备份时表是可读写的;
3.只备份routines(存储过程和函数,-R参数,在information_schema.routines表中)和events(作业,-E参数,在information_schema.events表中)信息:mysqldump -n -t -d -R -E > /tmp/routines.sql;
4.备份表结构,视图,函数,存储过程和作业的信息:mysqldump -d -R -E -S /mysql/logs/mysqld.sock –databases db_name > /tmp/objects.sql;
5.备份数据库test,包括视图信息:mysqldump –opt –lock-all-tables -R -E –master-data=2 -B test > /tmp/test_backup.sql;(查看当前二进制日志的名称和位置:show master logs;);

— 测验;
a

— 备份日志和状态文件;
1.二进制日志存储在备份完成后所做的更新;
2.服务器使用的选项文件(my.cnf和my.ini文件):这些文件包含在系统崩溃后必须恢复的配置信息;
3.复制文件:
1.复制从属服务器创建一个包含连接到主服务器所需信息的master.info文件;
2.以及一个指示当前的中继日志处理进度的relay-log.info文件;
4.复制从属服务器数据文件:
1.复制从属服务器创建用于处理LOAD DATA INFILE语句的数据文件;
2.这些文件位于slave_load_tmpdir系统变量指定的目录中,在服务器启动时使用–slave-load-tmpdir选项可设置该变量;
3.如果不设置slave_load_tmpdir,则应用tmpdir系统变量的值;
4.要保护复制从属服务器数据,需要备份以SQL_LOAD-开头的文件;
5.MySQL二进制文件和库;
6.策略:
1.静态文件:使用常规系统工具在服务器运行的情况下备份;
2.动态文件:使用常规系统工具在服务器停止的情况下备份;

— 将复制用作备份的辅助工具;
1.主服务器可以继续运行:
1.使用从属服务器生成备份,而不备份主服务器;
2.主服务器不会被中断,备份过程不会对主服务器增加处理负荷,也不要求增加硬盘空间或进行额外处理;
2.可以停止从属服务器以生成备份:
1.停止服务器:关闭mysqld进程;
2.或者发出STOP SLAVE SQL_THREAD语句以停止服务器处理其从主服务器收到的更新,必须刷新表以强制挂起对磁盘的更改;
3.备份从属服务器的数据库:
1.停止的服务器可以使用系统工具;
2.从属服务器线程停止,但仍在运行的服务器可以使用任何MySQL工具;
4.启动服务器:
1.启动停止的服务器;
2.START SLAVE SQL_THREAD;

— 备份方法比较;
1.参看PPT的对比;
2.快照:并非所有引擎都以相同方式处理快照,例如,InnoDB表不需要FLUSH TABLES WITH READ LOCK就能启动快照,但MyISAM表却需要;

— 备份策略;
需要关心的问题:
1.我们的系统能否承受长时间停机(停机时间)?
2.有多少数据要备份?
3.使用哪些存储引擎来存储数据(InnoDB,MyISAM或两者)?

— 处理二进制日志内容;
1.确定在备份生成后写入哪些日志:
1.在恢复了二进制备份文件或重新装入了文本备份文件后,通过重新处理在服务器的二进制日志中记录的数据更改,完成恢复操作;
2.为此,必须确定在生成备份后写入哪些日志,然后,需要使用mysqlbinlog程序将这些二进制日志的内容转换成文本SQL语句,以便使用mysql处理结果语句;
2.使用mysqlbinlog转换内容:
1.mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql
2.使用一个命令处理所有binlog;
3.恢复部分binlog:
1.–start-datetime选项:指定开始提取的日期和时间,其中选项参数采用DATETIME格式;请注意,–start-datetime的粒度仅有一秒,因此可能不够精确,不能指定开始的确切位置;
2.–start-position选项:可用于指定在给定的日志位置开始提取;
3.对应的–stop-datetime和–stop-position选项,用于指定停止提取日志内容的位置;
4.mysqlbinlog –start-position=23456 binlog.000004 | mysql
5.如果不确定日志文件中对应于处理开始点的时间戳或位置,可使用mysqlbinlog(不带mysql)显示日志内容进行检查:
shell> mysqlbinlog file_name | more

补充:
— mysqlbinlog的参数
1.-d,–database=name:列出某一个数据库的日志,只用于本地日志;
2.-f,–force-read:如果mysqlbinlog读到它不能识别的二进制日志,会打印警告而忽略该事件并继续,如果没有该事件则停止;
3.-o,–offset=#:忽略前N个实体;
4.-R,–read-from-remote-server:从远程服务器读取二进制日志,如果没有指定此选项,则–host, –user, –password, –port, –protocal, –socket选项都被忽略;
5.-r,–result-file=name:直接输出到给定的文件;
6.–start-datetime=time:读取二进制日志的生成开始时间,可以使用任何mysql服务器的时间格式,datetime和timestamp类型,如:’YYYY-MM-DD HH24:MI:SS’;
7.–stop-datetime=time:读取二进制日志的生成结束时间;
8.-j,–start-position=#:读取二进制日志的生成开始位置,是一个整型参数;
9.–stop-position=#:读取二进制日志的生成结束位置,一个整型参数;
10.-t,–to-last-log:在mysql服务器中请求的二进制日志结尾处不停止,而是继续打印直到最后一个二进制日志的结尾,如果将输出发送给同一台mysql服务器,会导导致无限循环,要与–read-from-remote-server连用;
11.-D,–disable-log-bin:禁用二进制日志,如果使用–to-last-logs选项将输出发送给同一台mysql服务器,可以避免无限循环,该选项在崩溃恢复也很有用,可以避免复制已经记录的语句;

— 测验;
c

— 课后练习;

补充:
— 表结构的复制
1.第一种做法是:CREATE TABLE table_name AS SELECT * FROM tb_name;
1.可以复制表接口和表中数据,如果只想要表接口可以加一个false的过滤;
2.但是会丢失表中列上面的属性(如自增属性)和索引(主外键);
2.第二种做法是:CREATE TABLE table_name(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT * FROM table_name;
1.可以在创建表时指定所有的属性,并同步数据;
2.但是语法太麻烦;
4.第三种做法是:CREATE TABLE table_name LIKE table_name;
1.只复制表结构,而且保留列的属性和索引;
2.如果想要数据的话可以INSERT table_name SELECT * FROM table_name;

标签:

MySQL OCP-15-在MySQL中编程

1月 28th, 2017

— 存储例程;
1.存储例程是存储在服务器中的一组指定的SQL语句:
1.客户机无需不断重新发出单个语句,而可以改为引用存储例程;
2.存储例程类型:
1.存储过程:通过CALL语句调用过程,这些过程可以使用输出变量或结果集传回值;
2.存储函数:在语句中调用函数,这些函数可返回标量值;

— 存储例程的使用;
1.集中式客户机功能:通过存储例程,您可以在数据库中集中创建一个语句或一系列语句,以供使用不同编程语
言编写或在不同平台上运行的多个客户机应用程序使用;
2.安全性:
1.存储例程为需要最高安全级别的应用程序提供了一个解决方法;
2.例如,银行针对所有常用操作均使用存储过程和函数,这提供了一致,安全的环境;
3.可对例程进行编码,以确保正确记录了每个操作,在此类设置中,应用程序和用户无法直接访问数据库表,只能执行特定的存储例程;
4.在Oracle中还可以针对存储过程进行加密;
3.性能改进:
1.客户机按名称调用存储例程,而不发送例程中所包含的整组语句;
2.因为服务器和客户机之间需要发送的信息变少了,所以存储例程可提升性能;
3.哪岂不是数据库服务器端的压力变重了?
4.函数库:通过存储例程,可以在数据库服务器中使用函数库,这些库用作数据库的API;

— 存储例程:问题;
1.增加了服务器负载:
1.在数据库自身中执行存储例程可增加服务器负载并降低应用程序的性能;
2.可以运行测试并运用常识来确保在数据库本身中包含逻辑所带来的方便比可能引发的性能问题更为显著;
2.开发工具有限:
1.MySQL中支持存储例程的开发工具不像在更通用的编程语言中那样成熟和明确;
2.此局限性会使存储例程的编写和调试过程更加困难,在决策过程中需要加以考虑;
3.语言功能和速度有限:
1.虽然在许多情况下在数据库本身中包含逻辑具有很大的优势,但是与其他编程语言相比,在可实现的内容方面仍有局限;
2.存储例程在数据库上下文中执行,与客户机应用程序中的例程相比,在处理大量数据时性能较好,但是客户机应用程序语言可能具有更强大,更通用的处理,集成或其他库功能;
4.调试和概要分析功能有限;

— 执行存储例程;
1.执行过程:使用CALL语句来调用存储过程,存储过程使用输出变量或结果集传回值;
2.执行函数:从语句内部调用函数(即,通过调用相应函数的名称),函数返回标量值;
3.每个存储例程均与特定数据库相关联,有多重含义:
1.USE :调用例程时,MySQL会在该例程运行期间执行隐式USE ,不能在存储例程内发出USE语句;
2.限定名称:可使用例程的数据库名称限定例程名称;执行此操作可引用当前数据库以外的例程;例如,要调用与test数据库相关联的存储过程p或函数f,请使用CALL test.p()或test.f();
3.删除数据库时,也会删除与其关联的所有存储例程;
4.SELECT语句:
1.仅限存储过程:函数不可以使用;
2.直接将结果集发送到客户机;

— 存储过程:示例;
mysql> USE world;
mysql> DELIMITER //
mysql> CREATE PROCEDURE record_count ()
BEGIN
SELECT (SELECT COUNT(*) FROM Country) ‘CountryCount’, (SELECT COUNT(*) FROM City) ‘CityCount’, (SELECT COUNT(*) FROM CountryLanguage) ‘CLCount’;
END//
mysql> DELIMITER ;

mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1(IN v_name varchar(20))
BEGIN
SELECT t1.Name, sum(t2.population) FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode AND t1.name = v_name GROUP BY t1.Name;
END$$
mysql> DELIMITER ;
mysql> CALL p1(‘China’);

mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(IN v_name varchar(20), OUT v_population INT)
BEGIN
SELECT sum(t2.population) INTO v_population FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode AND t1.name = v_name GROUP by t1.Name;
SELECT 0;
END$$
mysql> call p2(‘China’, @v_1);
mysql> select @v_1;

1.复合语句:
1.通过在存储例程中使用BEGIN…END语法并使用触发器,可以创建复合语句;
2.BEGIN…END块可包含零个或多个语句,空复合语句是合法的,而且复合语句中的语句数量没有上限;
2.分隔符:
1.在BEGIN…END语法中,必须使用分号[;]终止每个语句;
2.由于MySQL客户机使用分号作为SQL语句的默认终止字符,在以交互方式或针对批处理使用MySQL命令行客户机时,必须使用DELIMITER语句更改此设置;
3.此更改可确保客户机不会将复合语句中的分号解释为语句分隔符,并确保客户机不会过早地将CREATEPROCEDURE语句发送到服务器;当创建存储例程的语句以[//]终止时,客户机会先将该语句发送到服务器,然后再发出第二个DELIMITER语句将语句分隔符重置为分号;

— 存储函数:示例;
mysql> USE world;
mysql> DELIMITER //
mysql> CREATE FUNCTION pay_check (gross_pay FLOAT(9,2), tax_rate FLOAT (3,2))
RETURNS FLOAT(9,2) NO SQL
BEGIN
DECLARE net_pay FLOAT(9,2) DEFAULT 0;
SET net_pay=gross_pay – gross_pay * tax_rate;
RETURN net_pay;
END//
mysql> DELIMITER ;

1.RETURNS子句:用于确定此函数要返回的值的类型;
2.特征:通过多个特征,可确定有关例程所使用的数据的性质;
1.CONTAINS SQL表示例程不包含用于读取或写入数据的语句(为默认值);
2.NO SQL表示例程不包含任何SQL语句;
3.READS SQL DATA表示例程包含用于读取数据的语句(例如,SELECT)而不包含用于写入数据的语句;
4.MODIFIES SQL DATA表示例程包含用于写入数据的语句(例如,INSERT/DELETE/UPDATE);
5.在启用了二进制日志记录后,如果创建函数时未指定特征项,则MySQL会产生一个错误;
3.DECLARE语句:
1.在存储例程中使用DECLARE语句来声明本地变量并初始化用户变量;
2.可将DEFAULT子句添加到DECLARE语句的结尾,以便为用户变量指定初始值;如果省去DEFAULT子句,则用户变量的初始值为NULL;
4.SET语句:通过SET语句,您可以使用=或:=作为赋值运算符来向定义的变量赋值;
5.RETURN语句:用于终止存储函数的执行,并将值表达式返回给函数调用方;

— 检查存储例程;
1.SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION:
1.这些语句为MySQL扩展,类似于SHOW CREATE TABLE;
2.这些语句返回可用于重新创建指定例程的具体字符串;
3.这些语句的主要限制之一是您必须知道过程或函数的名称,并且必须确定其为过程或函数,然后才能尝试查看相应信息;
2.SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS:
1.这些语句特定于MySQL;
2.它们可返回例程的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.这些语句有一个优点:可基于LIKE模式显示特定例程,如果未指定任何模式,则会根据所使用的语句,列出所有存储过程或所有存储函数的信息;
3.INFORMATION_SCHEMA.ROUTINES:包含存储例程(过程和函数)的相关信息,并返回可同时在SHOW CREATE …和SHOW … STATUS语句中找到的大部分详细信息,以包 含用于创建存储例程的实际语法;
mysql> SELECT routine_name, routine_schema, routine_type, definer
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_name LIKE ‘film%’;
4.MySQL系统数据库中与编程组件关联的表
1.mysql.event表,包含MySQL服务器中所存储事件的相关信息;
2.mysql.proc表,包含MySQL服务器中的存储过程和函数的相关信息;
3.mysql.procs_priv表,为引用存储过程的用户提供访问控制授予详细信息;

— 存储例程和执行安全性;
1.存储过程和函数的使用涉及多个特权:
1.CREATE ROUTINE:创建存储例程;
2.ALTER ROUTINE:更改或删除存储例程;
3.EXECUTE:执行存储例程;
4.GRANT OPTION:将特权授予其他帐户;
2.默认操作:
1.创建存储例程时,MySQL会自动向您的帐户授予对该例程的EXECUTE和ALTER ROUTINE特权;
2.拥有撤消特权以及GRANT OPTION特权的用户稍后可撤消或删除这些特权;在创建例程后,可以通过发出SHOW GRANTS语句来验证这些特权;
3.授予特权:
1.当在全局级别或数据库级别授予所有特权时,GRANT ALL语句包括除GRANT OPTION之外的所有存储例程特权;要授予GRANT OPTION特权,请在该语句结尾包含WITH GRANT OPTION子句;
2.可以在单个例程级别授予EXECUTE,ALTER ROUTINE和GRANT OPTION特权,但仅限于已经存在的例程;
3.要授予对单个例程的特权,可使用其数据库名称限定例程,并提供关键字PROCEDURE或FUNCTION以指示例程类型;
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO ‘magellan’@’localhost’ WITH GRANT OPTION;

— 测验;
A

— 触发器;
1.据库触发器是数据库中所维护的命名数据库对象,将在修改表中数据时被激活;
2.功能:
1.在插入或更新数据之前对数据进行检查并验证删除和更新;
2.充当数据过滤器,在插入或更新之前修改超出范围的数据;
3.修改INSERT/UPDATE/DELETE的行为方式;
4.对于不支持外键的存储引擎,模仿外键的行为;
5.提供日志记录功能;
6.自动创建汇总表;
3.功能概括:
1.触发器可以提高表中数据的功能和安全性级别;
2.可以使用触发器控制对特定数据的访问权限,执行特定日志记录或对数据本身进行审计;

— 创建触发器;
1.CREATE TRIGGER语句;
CREATE TRIGGER trigger_name
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } ON table_name
FOR EACH ROW triggered_statement
2.例子:
CREATE TRIGGER City_AD AFTER DELETE
ON City FOR EACH ROW
INSERT INTO DeletedCity(ID, Name) VALUES (OLD.ID, OLD.Name);

1.trigger_name是为触发器指定的名称;
2.table_name是要与触发器关联的表的名称;
3.BEFORE和AFTER指示激活触发器的时间(是在触发事件之前或是之后);
4.而INSERT/UPDATE/DELETE指示具体的事件;
5.表名OLD和NEW是指触发器可查看的虚拟表,这些表分别包含UPDATE或DELETE语句所修改的数据的旧版本,或INSERT或UPDATE语句所添加的数据的新版本;

补充:模拟物化视图的例子;
1.创建一个汇总表;
CREATE TABLE ps(name char(52), population BIGINT);
INSERT INTO ps
SELECT t1.NAME, sum(t2.population) FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode GROUP by t1.Name order by 2 desc;
2.创建更新触发器,一种动作时间的触发器只能创建一个;
DELIMITER $$
CREATE TRIGGER City_AU AFTER UPDATE
ON City FOR EACH ROW
UPDATE ps SET population = population + new.population – old.population;$$
DELIMITER ;
ERROR 1235 (42000): This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’
3.更新记录用来模拟记录发生变化;
SELECT * FROM City WHERE id = 1890;
UPDATE City SET population = population + 1 WHERE id = 1890;
4.查看汇总表反应变化结果;
SELECT * FROM ps WHERE name = ‘China’;
SELECT sum(population) FROM City WHERE CountryCode=’CHN’;

— 触发器事件;
1.BEFORE和AFTER关键字是指触发器的激活时间,相对于数据修改语句(INSERT/UPDATE/DELETE)将更改写入底层数据库的时间;
2.BEFORE关键字可使触发器在涉及的数据修改之前执行,可使用BEFORE触发器捕获无效数据条目并在写入表之前对其进行更正或拒绝;
3.AFTER关键字定义在数据修改成功后执行的触发器,可使用AFTER触发器记录或审计数据库中的数据修改;

— 触发器错误处理;
MySQL按如下方式处理触发器执行期间发生的错误:
1.BEFORE触发器失败:包含相应行操作的事务将回滚;
2.AFTER触发器执行:BEFORE触发器事件和行操作必须成功执行;
3.对于非事务表,事务不可用:只有触发了触发器的语句会回滚,但在发生错误之前执行的所有更改仍然会生效;

— 检查触发器;
1.SHOW CREATE TRIGGER trigger_name:
1.此语句返回可用于重新创建指定触发器的具体字符串;
2.您必须知道触发器的名称才能运行此语句;
3.对于SHOW CREATE TRIGGER语句,不存在LIKE或WHERE语法;
2.SHOW TRIGGERS:
1.此语句为MySQL扩展;
2.它可返回触发器的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.此语句有一个优点:可基于LIKE模式或WHERE子句中提供的条件来显示特定触发器;如果未指定条件,则此语句会显示所有触发器的信息;
3.INFORMATION_SCHEMA.TRIGGERS:
1.包含SHOW命令所显示的所有数据;
2.完整地呈现在所有数据库中可用的触发器;

— 删除触发器;
1.使用如下语法可显式删除触发器:
1.DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
2.使用IF EXISTS可防止因尝试删除不存在的触发器而出现的错误;
2.通过删除以下项,可隐式删除触发器:
1.针对其定义触发器的表;
2.包含触发器的数据库;

— 对触发器的限制;
1.不允许使用的语句包括:
1.SQL预处理语句;
2.显示或隐式COMMIT和ROLLBACK;
3.返回结果集的语句(无法返回结果集,请通过使用SELECT …INTO var_list,或者通过使用光标和FETCH语句方法在触发器中处理结果集);
4.FLUSH语句;
5.用于修改要应用触发器的表的语句;
2.以下更改不会触发触发器:
1.级联外键所导致的更改;
2.在基于行的复制过程中导致的更改;

— 触发器特权;
1.要执行CREATE TRIGGER和DROP TRIGGER命令,需要有TRIGGER特权;
2.需要有其他特权才能在触发器中使用OLD和NEW:
1.要使用SET NEW.col_name = value为列赋值,需要对该列拥有UPDATE特权;
2.要在表达式中使用NEW.col_name以引用新的列值,需要对该列拥有SELECT特权;

— 测验;
b

— 事件;
1.事件:
1.MySQL事件是按调度表运行的任务,这些事件可称为“调度事件”;
2.创建事件时,会将其创建为命名数据库对象,其中包含在特定时间执行或定期重复发生的SQL语句(或存储过程);
3.与UNIX crontab或Windows任务调度程序类似;
CREATE EVENT event_name
ON SCHEDULE schedule DO sql_statement
4.必须选项:
1.event_name:事件是模式对象,与表,存储过程和触发器一样;event_name必须是有效的标识符,且可以按正常方式通过模式名称进行引用和/或限定;
2.schedule:调度表是一种规则,用于指定MySQL执行与事件相关联的操作的时间;
3.sql_statement:必须包含按调度表执行的有效SQL语句或存储过程语句;此语句受到的限制与适用于存储函数和动态SQL的限制相同;通常会使用CALL语句调用过程来执行实际操作;
2.事件调度程序
1.创建事件后,该事件将存储在数据库中,以便按调度表执行;
2.event_scheduler线程会监视所有事件的调度表,在到达调度表中的时间时,该线程会启动一个新的线程来执行每个事件;
3.默认情况下,event_scheduler线程设置为OFF,您必须显式启用该线程,方法为修改全局event_scheduler服务器变量的值,将其设置为ON;还可以通过将服务器变量添加到选项文件中(以便更改在启动时生效)或动态使用SET语法来实现;
4.如果在event_scheduler设置为DISABLED的情况下启动服务器,则无法在MySQL正在运行时通过SET语句将其启用;而必须停止MySQL并在启用该选项的情况下重新启动MySQL;
5.启用event_scheduler线程后,您可以在SHOW PROCESSLIST的输出(以及INFORMATION_SCHEMA等效项PROCESSLIST)中看到该线程;
3.事件语句:
1.SET GLOBAL event_scheduler = {ON | OFF};
2.CREATE EVENT:创建事件;
3.ALTER EVENT:更改事件;
4.DROP EVENT:删除事件;

— 调度表;
1.调度表是用于指定事件执行时间的规则;
2.有两种类型的调度操作:
1.执行一次(使用AT关键字);
2.重复执行(使用EVERY关键字):必须定义事件的重复频率;还可以定义一个时间段,以确定应重复执行事件的期限;
3.由事件调度程序执行调度事件:
1.事件调度程序是mysqld进程中的一个单独线程,负责执行调度事件;
2.调度程序会检查是否应执行事件,如果应执行事件,则会创建新的连接来执行操作;
4.使用事件来自动地定期执行任务;
5.示例:
1.表数据的常规CSV转储:加载数据仓库或者将数据导出到文件中;
2.ANALYZE TABLE;
3.自动地定期执行(维护)任务,如更新汇总表或刷新查询中的表(物化视图仿真);

补充:
1.创建一个一次性执行的作业,2分钟10秒后插入一条数据到test.t中;
CREATE EVENT e_at_insert_to_table
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL ‘2:10’ MINUTE_SECOND
DO INSERT INTO test.t values(NOW());
2.创建一个重复执行的作业,每天3点执行一次;
CREATE EVENT e_every_insert_to_table
ON SCHEDULE EVERY 1 DAY STARTS date_add(curdate(), INTERVAL 3 HOUR)
DO INSERT INTO test.t values(NOW());
3.DO子句中使用BEGIN…END关键字;
DELIMITER $$
CREATE EVENT e_event_do
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
INSERT INTO test.t values(NOW());
COMMIT;
END$$
DELIMITER ;

补充:
1.SCHEDULE子句的语法:
AT timestamp [+ INTERVAL interval] | EVERY interval [STARTS timestamp [+ INTERVAL interval]] [ENDS timestamp [+ INTERVAL interval]]
2.SCHEDULE子句可包含以下变量元素:
1.timestamp:DATETIME或TIMESTAMP类型的表达式;
2.interval:用于指定持续时间,持续时间的表示方法为,指定一个整数数量,后跟用于定义特定种类持续时间的关键字;
– YEAR
– QUARTER
– MONTH
– DAY
– HOUR
– MINUTE
– WEEK
– SECOND
– YEAR_MONTH
– DAY_HOUR
– DAY_MINUTE
– DAY_SECOND
– HOUR_MINUTE
– HOUR_SECOND
– MINUTE_SECOND

— 事件调度程序和特权;
1.必须拥有SUPER特权才能设置全局event_scheduler变量;
2.必须拥有EVENT特权才能创建,修改或删除事件;
3.使用GRANT分配特权(仅限在模式级别):
mysql> GRANT EVENT ON myschema.* TO user1@srv1;
mysql> GRANT EVENT ON *.* TO user1@srv1;
4.使用REVOKE取消事件特权:
1.REVOKE EVENT ON myschema.* FROM user1@srv1;
2.撤消用户帐户的EVENT特权不会删除或禁用该帐户已创建的任何事件;
5.mysql表:
1.用户的EVENT特权存储在mysql.user和mysql.db表的Event_priv列中;
2.在这两种情况下,该列均存储值“Y”或“N”之一,“N”是默认值;仅当给定用户拥有全局EVENT特权时,该用户的mysql.user.Event_priv值才会设为“Y”;
3.对于模式级别的EVENT特权,GRANT会在mysql.db中创建一个行,并按如下所示设置该行的列值:
1.Db:模式的名称;
2.User:用户的名称;
3.Event_priv:“Y”;
4.不必直接操作这些表,因为GRANT EVENT和REVOKE EVENT语句会对其执行所需的操作;

— 事件执行特权;
1.事件使用事件定义者的特权进行执行:如果定义者无权执行某任务,则事件无法执行该任务;
2.示例
1.user1@srv1只能为myschema创建SELECT事件:
CREATE EVENT e_store_ts
ON SCHEDULE EVERY 10 SECOND
DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
2.由于缺少INSERT特权,该事件不会在表中创建任何行;
3.可以通过错误日志查看对应的信息;

— 检查事件;
1.SHOW CREATE EVENT event_name:
1.此语句显示重新创建给定事件所需的CREATE EVENT语句;
2.必须提供事件名称才能查看该事件的相关信息;
2.SHOW EVENTS:
1.此语句为MySQL扩展;
2.它可返回事件的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.此语句有一个优点:可基于LIKE模式或WHERE子句中提供的条件来显示特定事件
3.INFORMATION_SCHEMA.EVENTS;

— 删除事件;
1.使用如下语法可显式删除事件:DROP EVENT [IF EXISTS] [schema_name.]event_name;
2.使用IF EXISTS可防止因尝试删除不存在的事件而出现的错误;
3.您必须对包含要删除的事件的数据库拥有EVENT特权;

— 测验;
b

— SIGNAL和RESIGNAL;
1.MySQL支持使用SIGNAL和RESIGNAL:
1.可用于引发特定的SQLSTATE;
2.与其他语言中的异常处理类似;
3.在处理错误时用于高级流控制;
4.这些命令为存储例程和触发器提供了一种将错误返回给应用程序或最终用户的方法;
2.使用SIGNAL抛出错误或警告状态:
1.SIGNAL用于向处理程序,应用程序的外侧部分或客户机提供错误信息;
2.例如,在存储过程中使用SIGNAL向调用该存储过程的代码抛出错误;该代码可以以适当的方式处理该错误;
3.使用RESIGNAL转发先前的SIGNAL所引发的错误或警告状态;
1.RESIGNAL用于在条件处理程序执行期间传递可用的错误条件信息;
2.可在存储过程或函数,触发器或事件内的复合语句中使用RESIGNAL;
3.例如,如果存储过程p调用了存储过程q,且q使用SIGNAL抛出一个错误,则p可声明一个处理程序,以处理来自q的信号;p 中的处理程序可使用RESIGNAL语句向调用p的代码抛出相同的错误信息;

— 课后练习;

补充:动态执行sql语句:
1.在ORACLE中动态执行sql语句使用:EXECUTE IMMEDIATE ‘sql_statement’;
2.在MySQL中动态执行sql的语法:
1.PREPARE stmt_name FROM preparable_stmt;
2.EXECUTE stmt_name [USING@var_name [,@var_name ]…];
3.{DEALLOCATE | DROP} PREPARE stmt_name;
3.例子:
1.准备sql语句:SET v_sql= concat(‘SELECT’, ‘ * ‘, ‘ FROM ‘, ‘ DUAL;’);
2.SET @v_sql=v_sql; — 注意很重要,将连成成的字符串赋值给一个变量,可以之前没有定义,但要以@开头;
3.PREPARE stmt FROM @v_sql; — 预处理需要执行的动态SQL,其中stmt是一个变量;
4.EXECUTE stmt; — 执行SQL语句,如果sql语句中有变量的话,可以使用USING子句后面跟变量的值;
5.DEALLOCATE PREPARE stmt; — 释放掉预处理段;

标签:

MySQL OCP-14-导出和导入数据

1月 28th, 2017

— 导出和导入数据;
1.可用的导出/导入操作类型:
1.使用SELECT…INTO OUTFILE将数据导出到文件;
2.使用LOAD DATA INFILE语句从文件中导入数据;
2.数据导出操作的用途:
1.将数据库从一个服务器复制到另一个服务器:
1.从同一主机;
2.复制到另一主机;
2.使用实际数据测试新MySQL版本;
3.将数据从一个RDBMS传输到另一个RDBMS,异构数据库间同步;

— 通过同时使用SELECT和INTO OUTFILE来导出数据;
1.将SELECT与INTO OUTFILE一起使用:
1.将结果集直接写入文件;
2.除非另有说明,否则,假定文件路径位于数据库数据目录下:SELECT * INTO OUTFILE ‘/tmp/City.txt’ FROM City;
2.输出文件:
1.写入服务器主机,而不是通过网络写入客户机;文件不能已存在;
2.使用文件系统访问权限进行创建:用户必须要有创建文件的权限,生成的文件属于该用户,文件对所有用户可读;
3.针对语句所选的每一行,都包含对应的一行;默认情况下,列值由制表符分隔,而行在换行符处终止;
补充:
1.从服务器端导出文本文件到服务器:SELECT * FROM tbname INTO OUTFILE ‘/path’;
2.从客户端导出文本到客户端;mysql -e ‘SELECT * FROM tbname’ > ‘/path’;

— 使用数据文件格式说明符;
1.默认情况下,将SELECT和INTO OUTFILE一起使用将输出TSV(Tab-Separated Values);
1.“制表符分隔值”文件用制表符分隔列,用换行符分隔记录;
2.CSV(Comma-Separated Values):使用逗号[,]符号分割列,用换行符分隔记录;
2.您可以选择替代的分隔符:
FIELDS
TERMINATED BY ‘string’
ENCLOSED BY ‘char’
ESCAPED BY ‘char’
LINES TERMINATED BY ‘string’
– FIELDS子句指定如何显示列:
1.TERMINATED BY指定字段分隔符,默认情况下是制表符;
2.ENCLOSED BY指定如何引住列值,默认设置为不使用引号(即,默认值为空字符串);
3.ESCAPED BY指明当表示换行符或制表符之类的非打印字符时要使用的转义符,默认转义符是反斜杠(\)字符;
– LINES TERMINATED BY子句指定行分隔符,默认情况下是换行符;

— 转义序列;
1.行终结符说明符:
1.默认为换行符:命令行终结符包括换行符和回车/换行符对;(默认的换行符终结符常见于Linux系统,而回车/换行符对常见于Windows系统)
2.幻灯片中所显示的所有序列单独使用或者在较长的字符串中使用,但\N除外,该序列只有在单独出现时才用作NULL;
2.ESCAPED BY选项:
1.仅控制数据文件中值的输出,它不会更改MySQL解释语句中特殊字符的方式;
2.如果通过写入ESCAPED BY ‘@’指定数据文件转义符为“@”,并不表示必须使用“@”来转义语句中其他的特殊字符;您必须使用 MySQL的转义符(反斜杠:\)来转义语句中的特殊字符,使用LINES TERMINATED BY ‘\r\n'(而不是LINES TERMINATED BY ‘@r@n’)之类的语法;

— 使用LOAD DATA INFILE导入数据;
1.查看帮助:HELP LOAD DATA;
2.说明:
1.将文件中的行值读入表;
2.是SELECT…INTO OUTFILE的逆向操作;
3.使用相似的子句和格式说明符:
LOAD DATA INFILE ‘/tmp/City.txt’ FIELDS TERMINATED BY ‘\t’
INTO TABLE City;
4.假定文件位于服务器主机上的数据库数据目录中
补充:
1.从服务器端导入文本文件服务器:LOAD DATA INFILE ‘/path’;
2.从客户端导入文本文件到服务器:LOAD DATA LOCAL INFILE ‘/path’;

— 跳过或变换输入数据;
1.要忽略数据文件中的行,请使用IGNORE n LINES:
1.mysql> LOAD DATA INFILE /tmp/City.txt’ INTO TABLE City IGNORE 2 LINES;
2.忽略数据文件的开始部分;当文件以列名行(而不是数据值行)开始时,请使用此子句;
2.要忽略或变换列值,请执行以下操作:
1.在语句列的列表中指定用户变量(而不是列名称);
2.(可选)通过使用SET子句变换列(该子句的语法类似于UPDATE语句中的SET子句):
LOAD DATA INFILE ‘/tmp/City.txt’
INTO TABLE City ( @skip, @Name, CountryCode, @District, Population)
SET name=CONCAT(@Name,’ ‘,@District);
3.该语句将忽略SET表达式中未使用的变量的值;
4.在将从文件中读取的数据值插入表中之前,LOAD DATA INFILE将对其进行变换,处理用户变量中所包含的值;

eg:
1.创建表;CREATE TABLE City1 LIKE City;
2.修改列长度:ALTER TABLE City MODIFY COLUMN name char(100);
3.导入数据:
LOAD DATA INFILE ‘/tmp/City1.txt’
INTO TABLE City1 ( @skip, @Name, CountryCode, @District, Population)
SET name=CONCAT(@Name,’ ‘,@District);
4.查看数据:SELECT * FROM City1 LIMIT 10;

— 重复记录;
1.要控制LOAD DATA INFILE对包含重复的主键或唯一键的行的处理方式,请执行以下操作:
1.使用IGNORE关键字放弃包含重复键的行;
2.使用REPLACE关键字将这些行替换为文件中包含相同键的版本;
2.这与使用INSERT和REPLACE语句控制重复项的方法类似:
1.IGNORE;
2.ON DUPLICATE KEY UPDATE;
3.其重复项处理行为根据数据文件是位于服务器主机上还是位于客户机主机上而稍有不同,所以使用LOAD DATA INFILE时,必须考虑数据文件的位置:
1.从服务器主机装入文件:
1.默认情况下,输入记录造成重复键违规将产生一个错误,不会装入数据文件的剩余部分,该点之前的已处理记录将被装入表中;
2.如果在文件名后提供IGNORE关键字,将忽略造成重复键违规的新记录,并且语句不会生成错误;LOAD DATA INFILE将处理整个文件,装入所有不包含重复键的记录,并放弃剩余记录;
3.如果在文件名后提供REPLACE关键字,造成重复键违规的新记录将替换表中现存的包含重复键值的任何记录;LOAD DATA INFILE将处理整个文件,将文件中的所有记录装入表中;
2.从客户机主机装入文件:
1.默认情况下LOAD DATA INFILE将忽略包含重复键的记录;即,默认行为与指定IGNORE选项时相同;
2.这是因为客户机/服务器协议不允许在传输开始后中断从客户机主机到服务器的数据文件传输,因此不方便在操作过程中中止操作;

— 补充:
1.mysqldump:会在备份恢复时候讲;
2.mysqlimport:就是LOAD DATA INFILE的客户端版;
2.mysqlexp:就是SELECT INTO OUTFILE的客户端版;

— 课后练习;

标签:

MySQL OCP-13-表维护

1月 28th, 2017

— 表维护的实施;
1.表维护操作对于确定和更正数据库问题十分有用,如以下问题:
– 由于服务器崩溃而导致表损坏;
– 对表的查询处理速度较慢;
2.可使用多种工具执行表维护:
– MySQL Workbench;
– MySQL Enterprise Monitor;
– SQL(DML)维护语句;
– 实用程序:
— mysqlcheck;
— myisamchk;
– 服务器自动恢复;

— 用于表维护操作的SQL;
1.有多个SQL语句可用于执行表维护:
– ANALYZE TABLE:更新索引统计信息;
– CHECK TABLE:彻底检查完整性;
– CHECKSUM TABLE:彻底检查完整性 ;
– REPAIR TABLE:修复;
– OPTIMIZE TABLE:优化;
2.每个语句均包含一个或多个表名称和可选的关键字;
3.维护语句和输出的示例:
• Table:指示对其执行操作的表;
• Op:指出操作(检查,修复,分析或优化);
• Msg_type:指示成功或失败;
• Msg_text:提供其他信息;
mysql> CHECK TABLE City, City1;
+————+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+——-+———-+———-+
| world.City | check | status | OK |
+————+——-+———-+———-+

— ANALYZE TABLE语句;
1.分析并存储表的键分布统计信息:在对多个对象执行联接操作时,MySQL 使用所存储的键分布统计信息来确定优化程序联接表的顺序;
2.用于更好地进行查询执行选择:键分布确定了MySQL用于查询中的特定表的索引;
3.执行ANALYZE TABLE语句来分析并存储统计信息,或者配置InnoDB,以便在大量数据发生更改之后或者在查询表或索引元数据时自动收集统计信息:
1.在分析过程中,对于InnoDB和MyISAM,MySQL使用读取锁来锁定表;
2.此语句等效于使用mysqlcheck –analyze;
3.需要对表有SELECT和INSERT特权;
4.支持分区表,还可以使用ALTER TABLE…ANALYZE PARTITION检查一个或多分区;
5.如果自从运行上一个ANALYZE TABLE语句后表未发生任何更改,则MySQL不会分析该表;
4.默认情况下,MySQL会将ANALYZE TABLE语句写入二进制日志并将这些语句复制到复制从属角色中,禁止使用可选的NO_WRITE_TO_BINLOG关键字或其别名LOCAL执行日志记录;
5.控制MySQL收集和存储键分布统计信息方式的选项:
1.innodb_stats_persistent:此选项为ON时,MySQL将对新创建的表启用STATS_PERSISTENT设置;
1.使用CREATE TABLE或ALTER TABLE语句时,还可以对表设置STATS_PERSISTENT;
2.默认情况下,MySQL不会将键分布统计信息持久保留在磁盘上,因此有时必须生成这些信息(如服务器重新启动后);
3.对于启用了STATS_PERSISTENT的表,MySQL会将其键分布统计信息存储在磁盘上,从而不需要频繁地为这些表生成统计信息;
4.随着时间推移,通过此操作优化程序可以创建更一致的查询计划;
2.innodb_stats_persistent_sample_pages:MySQL通过读取STATS_PERSISTENT表的索引页样例(而并非整个表)重新计算统计信息;
1.默认情况下,将读取20页样例;
2.增大此数字可提高所生成的统计信息和查询计划的质量;
3.降低此数字可减少用于生成统计信息的I/O成本;
3.innodb_stats_transient_sample_pages:此选项用于控制对没有STATS_PERSISTENT设置的表的抽样索引页数量;
以下选项用于控制MySQL自动收集统计信息的方式:
4.innodb_stats_auto_recalc:启用此选项时,如果STATS_PERSISTENT表中10%的行自前一次重新计算后有所变化,则MySQL将自动为该表生成统计信息;
5.innodb_stats_on_metadata:启用此选项可在执行元数据语句(如SHOW TABLE STATUS)或查询INFORMATION_SCHEMA.TABLES时更新统计信息;默认情况下,此选项处于禁用状态;
6.ANALYZE TABLE 正常结果的示例;

— CHECK TABLE语句;
1.检查表结构的完整性,并检查内容中是否包含错误;对于MyISAM表,还将更新键统计信息;
2.验证视图定义:例如视图定义中引用的表不再存在;
3.支持分区表:支持分区表。还可以使用ALTER TABLE…CHECK PARTITION检查一个或多个分区;
4.处理InnoDB,CSV,MyISAM和ARCHIVE表:
5.CHECK TABLE选项:
1.FOR UPGRADE:服务器将检查每个表以确定表结构是否与当前的MySQL版本兼容,可能会因为某种数据类型的存储格式或排序顺序发生变化而出现不兼容的情况;如果出现潜在的不兼容情况,则服务器将对表运行全面检查,如果全面检查成功,则服务器会使用当前的MySQL版本号标记表的.frm文件;对.frm文件进行标记可以确保以后对于与服务器版本相同的表进行检查的速度会加快;
2.QUICK:不扫描行来检查错误链接;
6.如果CHECK TABLE发现InnoDB表出现问题:
1.服务器将关闭,以防止错误扩散;
2.MySQL会将错误写入错误日志;
7.如果CHECK TABLE的输出表明某个表出现问题,请修复该表;
1.可以先使用CHECK TABLE语句检测硬件问题(如内存故障或磁盘扇区损坏),然后再修复表;
2.Msg_text输出列通常为OK,如果输出不是OK或Table is already up to date,请对该表运行修复;
3.如果该表被标记为corrupted或not closed properly,但CHECK TABLE在表中未发现任何问题,则会将该表标记为OK;

— CHECKSUM TABLE语句;
1.报告表checksum:
1.CHECKSUM TABLE需要对表有SELECT特权;
2.用于验证表的内容在备份,回滚或其他操作前后是否相同;
3.对于不存在的表,CHECKSUM TABLE将返回NULL并生成警告;
2.逐行读取整个表以计算校验和:
1.默认的EXTENDED选项提供了此行为(将逐行读取整个表,并计算checksum);
2.QUICK选项对MyISAM表可用;
1.将报告实时表checksum(如果可用);否则将报告NULL,此操作非常快;
2.通过在创建表时指定CHECKSUM=1 表选项,对MyISAM表启用了实时checksum;
3.checksum值取决于表中的行格式,如果行格式发生了变化,则checksum也会更改;例如,VARCHAR的存储格式在MySQL 4.1之后的版本中有所变化,因此,在将4.1表升级到更高版本后,如果表中包含 VARCHAR字段,则checksum值将发生变化;
3.CHECKSUM TABLE语句的示例;
TIPS:如果两个表的checksums不同,则很可能这两个表存在某方面的差异;不过,因为CHECKSUM TABLE使用的散列函数无法保证不冲突,所以存在两个不同的表生成相同checksum的微弱可能性;

— OPTIMIZE TABLE语句;
1.通过对表进行碎片整理来清理表
1.通过重新构建表并释放未使用的空间对表进行碎片整理;
2.合并被分隔开的记录和以非连续方式存储的记录;
3.需要对表有SELECT和INSERT特权;
2.在优化过程中锁定表;
3.更新索引统计信息:
1.例如,修改大量行之后,可以使用OPTIMIZE TABLE语句在InnoDB中重构一个FULLTEXT索引;
2.对于InnoDB表,OPTIMIZE TABLE将映射到ALTER TABLE,后者将重构表以更新索引统计信息并释放群集索引中未使用的空间;
3.InnoDB不会像其他存储引擎一样受碎片影响,因此不需要经常使用OPTIMIZE TABLE;
4.最适用于完全填充的永久表:
5.处理InnoDB,MyISAM和ARCHIVE表:
1.对使用ARCHIVE存储引擎的表使用OPTIMIZE TABLE可以压缩该表;
2.由SHOW TABLE STATUS所报告的ARCHIVE表中的行数始终比较准确;
3.优化操作过程中可能会出现一个.ARN文件;
6.支持分区表:还可以使用ALTER TABLE…OPTIMIZE PARTITION检查一个或多个分区;
7.OPTIMIZE TABLE选项:
1.NO_WRITE_TO_BINLOG或LOCAL:禁用二进制日志;
8.已删除的行将保留在链接的列表中,而后续的INSERT操作将重用之前行的位置,OPTIMIZE TABLE对完全填充的表使用时效果最佳并且不会发生很大更改;如果数据更改较多并经常需要优化,则优化的优势将会大大降低;

— REPAIR TABLE语句;
1.修复可能已损坏的MyISAM或ARCHIVE表;
1.不支持InnoDB;
2.支持分区表;
3.REPAIR TABLE选项:
1.QUICK:尝试仅修复索引文件,而不修复数据文件;此类型的修复与myisamchk –recover –quick所执行的修复相似;
2.EXTENDED:MySQL将逐行创建索引,而不是一次性创建有序索引;此类型的修复与myisamchk –safe-recover所执行的修复相似;
3.USE_FRM:使用.FRM文件重新创建.MYI文件;选项不能用于分区表
4.NO_WRITE_TO_BINLOG或LOCAL:禁用二进制日志;
4.TIPS:
1.在执行表修复操作之前,最好对表进行备份;在某些情况下,该操作可能导致数据丢失;可能的原因包括(但不仅限于)文件系统错误;
2.如果服务器在REPAIR TABLE操作过程中崩溃,则为避免进一步的损坏,重启之后应立即执行另一REPAIR TABLE,然后再执行其他任何操作;
3.如果经常需要使用REPAIR TABLE从损坏的表进行恢复,请尝试找出根本原因,以防止相应损坏并避免使用REPAIR TABLE;

— mysqlcheck客户机程序;
1.是用于检查,修复,分析和优化表的命令行客户机;
2.比发出SQL语句更加方便;
1.例如,如果提供数据库名称作为其参数,则mysqlcheck将确定该数据库所包含的表,并发出语句处理所有这些表;不需要提供明确的表名称作为参数;
2.由于mysqlcheck是命令行程序,因此可以在执行计划维护的操作系统作业中轻松使用该程序;
3.处理InnoDB,MyISAM和ARCHIVE表;
4.三种检查级别:
1.特定于表;
2.特定于数据库;
3.所有数据库;
5.部分mysqlcheck维护选项:
1.–analyze:执行ANALYZE;
2.–check:执行CHECK TABLE(默认);
3.–optimize:执行OPTIMIZE TABLE;
4.–repair:执行REPAIR TABLE;
6.Oracle建议首先在不使用任何选项的情况下运行mysqlcheck,如果需要修复再重新运行;
7.部分mysqlcheck修改选项:
1.–repair –quick:尝试快速修复;
2.–repair:正常修复(如果快速修复失败);
3.–repair –force:强制修复;
8.mysqlcheck示例:
shell> mysqlcheck –login-path=admin world_innodb
shell> mysqlcheck -uroot -p mysql user –repair
shell> mysqlcheck -uroot -p –all-databases
shell> mysqlcheck –login-path=admin –analyze –all-databases
9.默认情况下,mysqlcheck将其第一个非选项参数解释为数据库名称,并检查该数据库中的所有表;如果数据库名称后面有其他任何参数,则会将这些参数视为表名称,从而只检查这些表;
本幻灯片中显示的 mysqlcheck 示例演示了以下内容:

— myisamchk实用程序;
1.是用于检查MyISAM表的非客户机实用程序;
2.myisamchk与mysqlcheck具有相似的用途,其差异包括:
1.可以启用或禁用索引;
2.不与MySQL服务器通信,直接(而不是通过服务器)访问表文件;
3.在使用myisamchk执行表维护的同时避免并发表访问:
1.确保服务器不会访问正在进行处理的表,一种实现方法是锁定表或停止服务器;
2.在命令提示符中,将位置更改为表所在的数据库目录;这是服务器数据目录的子目录,该目录的名称与要检查的表所在的数据库名称相同;(更改位置是为了更加便于引用表文件,可以跳过此步骤,但myisamchk必须包含表所在的目录;)
3.调用myisamchk,使用选项指示要执行的操作,后跟参数以指定myisamchk应对其执行操作的表;这些参数可以是表名称,也可以是表的索引文件的文件名(索引文件名与表名称相同,包含.MYI后缀);因此,可以通过table_name或table_name.MYI引用表;
4.重新启动服务器:请首先尝试–recover,因为–safe-recover比较慢;
3.部分myisamchk 选项:
1.–recover:修复表;
2.–safe-recover:修复–recover无法修复的表;
4.myisamchk示例:
shell> myisamchk /var/lib/mysql/mysql/help_topic
shell> myisamchk help_category.MYI
shell> myisamchk –recover help_keyword

— mysqlcheck和myisamchk的选项;
1.mysqlcheck和myisamchk均使用多个选项来控制所执行的表维护操作的类型;
2.两者都适用:
• –analyze:分析表中键值的分布,通过加快基于索引的查找,这可以提高查询的性能;
• –check 或 -c:检查表中是否存在问题,如果未指定其他任何操作,则为默认操作;
• –check-only-changed 或 -C:跳过表检查(自上一次检查后已更改的表或未正常关闭的表除外),如果服务器在表打开时崩溃,则会出现后一种情况;
• –fast 或 -F:跳过表检查(未正常关闭的表除外);
• –medium-check 或 -m:运行中等表检查;
• –quick 或 -q:对于mysqlcheck,不包含修复选项的–quick会导致只检查索引文件,而不检查数据文件;对于这两个程序,将–quick与修复选项结合使用都会导致程序只修复索引文件,而不修复数据文件;
3.mysqlcheck & myisamchk:
• –auto-repair:如果检查操作发现了问题,则自动修复出现问题的表;
• –repair、–recover 或 -r:运行表修复操作;
• –extended、–extend-check 或 -e:运行扩展表检查,对于mysqlcheck,将此选项与修复选项结合使用时,将执行比单独使用修复选项时更彻底的修复;即,–repair –extended执行的修复操作比–repair执行的操作更彻底;

— InnoDB表维护;
1.出现故障之后,InnoDB将自动恢复;
2.使用CHECK TABLE或客户机程序可找出不一致,不兼容和其他问题;
3.如果表检查表明存在问题,可以通过使用mysqldump对表进行转储来恢复该表:
1.备份表内容:shell> mysqldump > ;
2.然后,删除该表并从转储文件重新创建:shell> mysql < ;
4.如果MySQL服务器或其运行主机崩溃,则某些InnoDB表可能处于不一致状态,在InnoDB的启动序列中,会执行自动恢复;服务器很少因为自动恢复故障而无法启动,如果出现此情况,则可以:
1.重新启动服务器,将–innodb_force_recovery选项的值设置为1到6之间的值;这些值表示增加警告级别以避免崩溃,以及针对已恢复的表中可能存在的不一致状况增加容错级别,最好从值4开始,该值设置为只读模式;
1.1(SRV_FORCE_IGNORE_CORRUPT):即便检测到坏页也让服务正常运行,可以尝试使用SELECT * FROM tbl_name来跳过损坏的索引记录和页,对于导出数据比较有帮助;
2.2(SRV_FORCE_NO_BACKGROUND):阻止master线程和任何的purge线程运行;
3.3(SRV_FORCE_NO_TRX_UNDO):实例恢复后不运行事务回滚;
4.4(SRV_FORCE_NO_IBUF_MERGE):阻止插入缓存合并操作;不会统计表的统计信息;这个值会永久性的破坏数据文件,在使用这个值之后需要导出数据,删掉对象然后再重新创建对象;在MySQL 5.6.15之后,会设置InnoDB为只读模式;
5.5(SRV_FORCE_NO_UNDO_LOG_SCAN):在数据库启动时不会去查看UNDO日志,InnoDB会把未提交事务处理为已提交;这个值会永久性的破坏数据文件,在MySQL 5.6.15之后,会设置InnoDB为只读模式;
6.6(SRV_FORCE_NO_LOG_REDO):在实例恢复时不会前滚REDO日志;这个值会永久性的破坏数据文件,使数据库的页为过期状态;在MySQL 5.6.15之后,会设置InnoDB为只读模式;
2.当在–innodb_force_recovery设置为非零值的情况下启动服务器时,InnoDB将阻止INSERT,UPDATE或DELETE操作;因此,您应转储InnoDB表,然后在该选项生效时将这些表删除,再在不使用–innodb_force_recovery选项的情况下重新启动服务器;服务器启动之后,将从转储文件恢复InnoDB表;
3.如果前述步骤失败,则从前一个备份恢复表;
5.使用ALTER TABLE进行优化时,将重构表并释放群集索引中未使用的空间;

— MyISAM表维护;
1.使用CHECK TABLE…MEDIUM(默认选项):
1.对于动态格式表和静态格式表,默认的CHECK TABLE检查类型均为MEDIUM;如果将静态格式表类型设置为CHANGED或FAST,则默认选项为QUICK;对于CHANGED和FAST,将跳过行扫描,因为这些行很少损坏;
1.如果表被标记为“已损坏”或“未正常关闭”,则CHECK TABLE将更改表;
2.如果未在表中发现任何问题,则会将表的状态标记为“最新”;
3.如果表已损坏,则问题最有可能存在于索引而不是数据中;
2.使用客户机程序运行myisamchk:shell> myisamchk –medium-check ;
3.表损坏通常发生在索引中,因而执行这些检查很有帮助;
4.如果表已损坏,则实用程序将修复该表;
2.设置服务器以运行检查并自动修复表:
1.使用–myisam-recover选项启用自动修复;选项值可以包含以逗号分隔的值列表,由以下一个或多个值组成:
1.DEFAULT:默认检查;
2.BACKUP:指示服务器对必须进行更改的所有表进行备份;
3.FORCE:执行表恢复,即使可能导致多行数据丢失也是如此;
4.QUICK:执行快速恢复;恢复将跳过一些不包含因删除或更新而产生的行间隔(也称为“洞”)的表;
2.服务器将在启动之后第一次访问每个MyISAM表时进行检查,以确保这些表前一次正确关闭;
3.强制从config文件恢复MyISAM表;
eg:要指示服务器对发现问题的MyISAM表执行强制恢复,但同时要备份其更改的所有表,请向选项文件中添加以下内容:
[mysqld] myisam-recover=FORCE,BACKUP;

补充:
MySQL在创建表的时候定义表的性质,共有三种状态:静态表,动态表,压缩表;
1.默认是静态表;
2.动态表:如果存在varchar,blob,text字段,表类型就是动态表;
3.压缩表:只读,使用很少的空间,使用myisampack工具创建;

— MEMORY表维护;
1.使用DELETE…WHERE语句删除多个行时,MEMORY表不会释放内存;
2.要释放内存,必须执行空值ALTER TABLE操作;

— ARCHIVE表维护;
1.ARCHIVE压缩问题:
1.插入表行时将对其进行压缩;
2.检索时,将根据需要对行进行解压缩;
3.一些SELECT语句可能会减弱压缩功能;
2.使用OPTIMIZE TABLE或REPAIR TABLE可以实现更好的压缩;
3.在未对表进行访问(读或写)时,OPTIMIZE TABLE有效;

— 课后练习;

标签:

MySQL OCP-12-安全

1月 28th, 2017

— 安全风险;
• 当多个用户同时访问MySQL服务器,尤其当这些用户通过Internet进行连接时,MySQL服务器将有安全风险;
• 不仅MySQL服务器处于危险中,整个服务器主机也可能受到损害;
• 有多种类型的安全攻击:
– 窃听
– 更改
– 播放
– 拒绝服务
• 对于所有连接,查询和其他操作,MySQL使用基于ACL(访问控制列表,access control list)的安全;
• ACL也支持MySQL客户机和服务器之间的SSL加密连接;

— MySQL安装安全风险;
最常见的安装安全风险为:
1.网络安全
1.MySQL服务器允许客户机通过网络进行连接并发出请求;
2.如果未限制特权,则用户可以查看客户机帐户信息:为每个帐户设置的特权应该仅提供对该帐户需要查看或修改的数据的访问权限;
3.任何没有口令的帐户都很容易受到攻击;
2.操作系统安全:通常使用专用的登录帐户管理MySQL;但是,该帐户所在的主机上可能还有其他登录帐户,将与MySQL不相关的帐户数量减至最少可以尽可能地降低此风险;
3.文件系统安全:服务器上的目录,数据库文件和日志文件可能会被不应具有访问权限的用户打开;

— 网络安全;
风险预防任务:
1.投资设置防火墙;
2.确保仅供授权的客户机进行访问;
3.限制服务器所使用的网络接口;
4.使用安全安装脚本:mysql_secure_installation;
5.遵守常规特权安全事项;
6.不通过Internet传输明文(未加密)数据:未加密的数据可被任何有时间和能力拦截该数据并使用它来达到自己目的的用户访问,因此,您应该使用SSL或SSH等加密协议;

— 口令安全;
风险预防任务:
1.使用强口令保护初始MySQL帐户;
2.不在数据库中存储任何纯文本口令:
1.mysql数据库将口令存储在user表中;
2.最好使用单向散列存储这些口令:MD5(),SHA1(),SHA2()。
3.不从字典中选择口令:有一些特殊程序可破译口令,应对这些程序的方法之一是使用由句子中每个单词的第一个字符组成的口令(例如,“Mary had a little lamb”可以生成口令“Mhall”);这种口令易于记忆和输入,但不知道句子的人很难猜到;

— 操作系统安全;
1.与配置的复杂程度有关:
1.将服务器主机上与MySQL不相关的任务数减至最少;
2.当为主机配置的任务较少时,与运行复杂配置以支持多个服务的主机相比,前一个主机更易于确保安全;
2.其他使用会导致潜在风险:所分配的MySQL服务器最好是主要用于MySQL或专供其用;
3.登录帐户对于专用于MySQL的计算机不是必需的;
4.完全专用于MySQL的系统可以在性能方面获益;

— 文件系统安全;
MySQL安装(目录和文件)风险预防任务:
1.更改所有权和访问权限后,再启动服务器:
1.将多用户系统的所有权设置为具有管理特权的帐户。
2.将与MySQL相关的目录和文件以及user和group表所有权设置为mysql,其中包括:
1.MySQL程序;
2.数据库目录和文件;
3.日志,状态和配置文件;
2.不要在保护文件之前设置口令,否则,将允许未经授权的用户替换文件;
3.设置一个专用于MySQL管理的帐户:
1.对于Linux之类的多用户系统,请将MySQL安装的所有组件的所有权设置为具有正确管理特权的专用登录帐户,这将保护安装免受不负责数据库管理的用户的访问;
2.设置此帐户的另外一个好处是,可以使用该帐户运行MySQL服务器,而不是从Linux root帐户运行服务器;具有root登录帐户特权的服务器拥有不必要的文件系统访问权限,从而成为一个安全风险;

— 保护数据;
用户可通过多种方法来损坏数据,必须采取措施以保护数据免受如SQL注入等攻击:
1.请勿信任应用程序用户输入的任何数据:
1.用户可以使用具有特殊意义的字符(如引号或转义序列)获取应用程序代码;
2.如果用户输入类似于DROP DATABASE mysql;的内容,请确保应用程序保持安全;
3.如果用户输入值234时,应用程序生成一个类似于SELECT * FROM table WHERE ID=234的查询,则用户可以输入值234 OR 1=1使应用程序生成查询SELECT * FROM table WHERE ID=234 OR 1=1;使用不会将值解释为SQL表达式的存储例程或预处理语句;
2.保护数值和字符串数据的值:
1.在数值字段中输入字符,空格和特殊符号而不是数字;应用程序应删除这些内容再将其传递给MySQL;
2.否则,用户可以获取对安全数据的访问权限,然后提交可以销毁数据或导致服务器负载过高的查询;
3.甚至需要保护公开可用的数据:
1.攻击会浪费服务器资源;
2.保护Web表单(在客户端校验),URL名称,特殊字符等;

— 使用安全连接;
1.默认情况下,MySQL使用未加密的客户机/服务器连接;
2.未加密连接无法满足在网络上安全传输数据的要求:
1.网络通信容易受到监视和攻击;
2.在客户机和服务器之间传输的数据可能会被更改;
3.使用加密算法可抵御大多数威胁:
1.这些算法会使所有类型的数据无法读取;
2.这可抵御多种类型的攻击;
4.某些应用程序需要加密连接所提供的安全性,对于这些连接,必须进行额外的计算(数据加密是CPU密集型操作,需要计算机执行额外工作);

— SSL协议;
1.MySQL支持MySQL客户机和服务器之间的SSL(secure sockets layer,安全套接字层)连接:MySQL可以针对单个连接启用加密,根据各个应用程序的需要,您可以选择常规的未加密连接或安全的加密SSL连接;
2.SSL连接协议具有以下特点:
1.使用不同的加密算法确保公共网络上数据的安全;
2.检测任何数据更改,丢失或重放;
3.结合了使用X509标准提供身份验证的算法;
3.需要额外安全保护(加密)的应用程序应使用SSL。
4.安全连接基于OpenSSL API。

补充:
X509使得在Internet上进行身份识别成为可能,这主要用于电子商务应用程序中;基本上,应该有一个受信任的证书颁发机构(Certificate Authority, CA),该机构将电子证书分配给任何需要它们的人;证书依赖于包含两个加密密钥(公钥和私钥)的非对称加密算法;

— 对MySQL服务器使用SSL;
1.SSL使用要求:
1.系统必须支持yaSSL(已随MySQL提供)或OpenSSL(http://www.openssl.org);
2.使用的MySQL版本必须包含SSL支持;
2.要获取安全连接以便使用MySQL和SSL,必须首先执行以下操作:
1.装入OpenSSL(如果使用的不是预编译的MySQL);
2.为MySQL配置SSL支持,要将MySQL源代码分发配置为使用SSL,请调用CMake: shell> cmake . -DWITH_SSL=bundled;
1.该操作会将分发配置为使用附带的yaSSL库;
2.要改用系统SSL库,请将相应选项指定为-DWITH_SSL=system;
3.确保MySQL数据库中的user表包含与SSL相关的列(ssl_*和x509_*),否则必须使用mysql_upgrade程序对其进行升级;
4.使用–ssl选项检查服务器二进制文件是否使用SSL支持进行编译;
shell> mysqld –ssl –help
060525 14:18:52 [ERROR] mysqld: unknown option ‘–ssl’
3.使用允许客户机使用SSL的选项启动服务器;

— 使用SSL启动MySQL服务器;
通过以下选项,使mysqld服务器客户机可以使用SSL进行连接:
1.–ssl-ca:确定要使用的证书颁发机构(Certificate Authority, CA)证书(加密必需);
2.–ssl-key:确定服务器公钥;
3.–ssl-cert:确定服务器私钥;
eg:shell> mysqld –ssl-ca=ca-cert.pem –ssl-cert=server-cert.pem –ssl-key=server-key.pem

— 要求SSL加密连接;
1.使用GRANT语句的REQUIRE SSL选项可仅允许某个帐户使用SSL加密连接:
GRANT ALL PRIVILEGES ON test.* TO ‘root’@’localhost’ IDENTIFIED BY ‘goodsecret’ REQUIRE SSL;
2.通过使用–ssl-ca选项启动mysql客户机可获得加密连接;
3.(可选)可以为X509连接指定–ssl-key和–ssl-cert选项:
shell> mysql –ssl-ca=ca-cert.pem –ssl-cert=server-cert.pem –ssl-key=server-key.pem

补充:
有很多方法可限制给定帐户的连接类型:
1.REQUIRE NONE:指示帐户没有SSL或X509要求;如果未指定与SSL相关的REQUIRE选项,则这是默认选项;如果用户名和口令有效,则允许未加密的连接;但是,如果客户机具有相应的证书和密钥文件,则客户机也可以通过指定一个选项来要求加密连接,即,客户机无需指定任何SSL命令选项,在这种情况下,连接是未加密的;
2.REQUIRE SSL:指示服务器仅允许帐户使用SSL加密连接;
3.REQUIRE X509:客户机必须具有有效的证书,但具体的证书,颁发者和主题无关紧要;唯一的要求是,应该可以使用其中一个CA证书验证其签名;

— 检查SSL状态;
1.检查正在运行的mysqld服务器是否支持SSL:mysql> SHOW VARIABLES LIKE ‘have_ssl’;
1.如果have_ssl的值为YES,则服务器支持SSL连接;
2.如果值为DISABLED,则服务器支持SSL连接,但在启动时未提供相应的–ssl-*选项;
2.使用ssl_cipher状态变量的值检查当前服务器连接是否使用了SSL:mysql> SHOW STATUS LIKE ‘ssl_cipher’;

例子:
1.Create clean environment
shell> /tmp
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts
2.Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
3.Create server certificate, remove passphrase, and sign it server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
4.Create client certificate, remove passphrase, and sign it client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
5.After generating the certificates, verify them:
shell> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
6.修改my.cnf并且重新启动数据库服务器;
[mysqld]
ssl-ca=/tmp/newcerts/ca-cert.pem
ssl-cert=/tmp/newcerts/server-cert.pem
ssl-key=/tmp/newcerts/server-key.pem
7.检查是否支持SSL:mysql> SHOW VARIABLES LIKE ‘have_ssl’;
8.创建用户:GRANT ALL PRIVILEGES ON *.* TO ‘ssl_user’@’localhost’ IDENTIFIED BY ‘mysql’ REQUIRE SSL;
9.客户端连接:
shell> mysql -ussl_user -pmysql
ERROR 1045 (28000): Access denied for user ‘ssl_user’@’localhost’ (using password: YES)
shell> mysql -ussl_user -pmysql –ssl-ca=/tmp/newcerts/ca-cert.pem

— 使用SSL的优点和缺点;
1.优点:
1.提高了有需求的应用程序的安全性;
2.可以针对单个连接启用;
3.可用于复制操作;
2.缺点:
1.占用大量CPU资源;
2.降低了客户机/服务器协议的速度;
3.可能会推迟其他SQL任务;

— 与MySQL的安全远程连接;
1.MySQL支持与远程MySQL服务器的SSH(secure shell,安全shell)连接,这一功能要求:
– 客户机上存在 SSH 客户机;
– 通过 SSH 隧道进行从客户机到服务器的端口转发;
– 具有 SSH 客户机的计算机上存在客户机应用程序;
2.设置完成后,将有一个本地端口托管到 MySQL 的 SSH 连接并使用 SSH 进行加密;

— MySQL安全常见问题;
file:///Users/royalwzy/Applications/refman-5.6-en.html-chapter/faqs.html#faqs-security

— 课后练习;

标签: