MYSQL20天魔鬼入门训练!

外在因素：网络、主从硬件差异大、数据库版本差异、参数因素

SQL线程功能分类及故障情况：

从库执行SQL：slave_IO_Running: NO

重新搭建主从（建议使用，防止一些不容易发现的问题被忽略掉）
跳过此次报错（有风险，容易造成后期主从故障）
提前设置从库只读，防止出现问题
read-only=1（仅对普通用户起作用，管理员无效）
读写分离（利用中间件）

P113- MySQL-主从延时-原因解析

内在因素：

主库：

二进制日志写入不及时
binlogSync_binlog=1;（事务提交时，立即写入）
CR模式的主从复制中，binlog_dump线程，（5.5版本）以事件为单元串行传递binlog

情况一、主库并发事务量大，主库可以并行，传送时串行传递
解决方法：（5.6主从必须同时开启、5.7）开启GTID，实现GC（group commit）
并行传输日志给从库
情况二、主库单个事务过大，串行传递时堵塞后续事务
解决方法：将大事务拆解为小事务传递

情况三、一主好多从

从库：

SQL执行慢
从库串行，且默认只有一个SQL线程，只能串行回放事务
解决方法：开启GTID实现多线程并发回放
5.6：针对不同库下的事务进行并发回放
5.7：（MTS）binlog加入了序列号，实现基于事务的完全并发回放

P115- MySQL-延时从库-配置及使用思路

延时从库：针对逻辑故障，用于主库备份专用，不做其他用途

配置方法：

逻辑故障恢复思路：

P116- MySQL-延时从库-处理逻辑损坏

故障恢复案例：

从库开启延时
stop slave;
CHANGE MASTER TO MASTER_DELAY=300;
start slave;
show slave status\G

SQL_Delay: 300

SQL_Remaining_Delay: NULL

模拟数据变更
create database test;
use test;
create table t1(id int);
insert into t1 values(1),(2),(3);
drop test;
发现故障并立即停止从库SQL线程
stop slave sql_thread;
截取relay-log日志（找到起点和终点）
起点：
        show slave status\G
            Relay_Log_File: www-relay-bin.000014
            Relay_Log_Pos: 1368
        vim /data/3308/data/relay-log.info
            ./www-relay-bin.000014
1368

终点：

show relaylog events in 'www-relay-bin.000014';

截取：

mysqlbinlog --start-position=1368 --stop-position=1848 /data/3308/data/www-relay-bin.000014 >/tmp/relay.sql

恢复从库数据
 set sql_log_bin=0;
source /tmp/relay.sql
set sql_log_bin=1;
提升从库为主库（重新构建主从：3308主、3307从）
解除从库身份：
stop salve;
reset slave;

P118- MySQL-半同步主从复制

半同步复制：解决主从数据一致性问题，

异步复制：5.7.17后新增无损（MGR）复制功能，

过滤复制：通过设定黑、白名单来控制是否被复制（建议从库按需设定，不适用主库）

从库：show slave status\G查看；配置文件中，参数小写，多个库需写多行

P119- MySQL-GTID主从复制-环境准备

核心特性：全局唯一，具备幂等性

核心参数：

主库开启GTID：gtid_mode=on 强一致性：enforce-gtid-consistency=true

强制从库记录主库二进制日志（保证全局唯一）：log-slave-updates=1

准备三台虚拟机：

清理环境：

rm -rf /data/mysql/*

mv /etc/my.cnf my.cnf.bak

准备配置文件：

主库db02：

cat > /etc/my.cnf <<EOF

server_id=1

log_bin=/data/mysql/binlog/mysql-bin

secure-file-priv=/tmp

autocommit=0

gtid-mode=on

cat > /etc/my.cnf <<EOF

server_id=2

log_bin=/data/mysql/binlog/mysql-bin

secure-file-priv=/tmp

autocommit=0

gtid-mode=on

cat > /etc/my.cnf <<EOF

server_id=3

log_bin=/data/mysql/binlog/mysql-bin

secure-file-priv=/tmp

autocommit=0

gtid-mode=on

mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql/data

启动服务；

/etc/init.d/mysqld start

确认配置成功：

select @@server_id;

P121- MySQL-GTID主从复制-配置搭建

GTID和传统主从复制的区别：

GTID主从的特点：

主库创建用户：

grant replication slave on *.* to rep@'%' identified by '123';

从库配置连接信息：

change master to

master_host='192.168.3.123',

master_user='rep',

master_password='123',

master_auto_position=1;

启动从库服务：

注入空事务（不建议使用：解决从库误写入发生的报错）：

P124- MySQL-主从复制架构演变

P125- MySQL-MHA高可用架构-环境准备

基于GTID主从部署MHA高可用架构：一主两从

软链接：（MHA程序内定访问路径）

ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

ln -s /app/mysql/bin/mysql /usr/bin/mysql

配置SSH互信：(在主库服务器创建 122 => 123\124 )

作用：所有互信服务器间，配置文件实时同步

清空历史文件：rm -rf /root/.ssh

生成密钥文件：ssh-keygen（一路回车执行）

修改文件名：

cd /root/.ssh

mv id_rsa.pub authorized_keys

添加其他主机连接信息：（yes => root用户登录密码）

scp -r /root/.ssh 192.168.3.123:/root

scp -r /toot/.ssh 192.168.3.124:/root

测试互信：（所有服务器上执行，实现无密码登录）

ssh 192.168.3.122 date

Tue Mar 17 21:07:31 EDT 2020

ssh 192.168.3.123 date

Tue Mar 17 21:07:21 EDT 2020

ssh 192.168.3.124 date

Tue Mar 17 21:07:27 EDT 2020

安装MHA软件：

下载软件：

官网：https://code.google.com/archive/p/mysql-master-ha/

Github：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

安装Node节点：（所有需要被管理节点都要安装）

安装依赖包：yum install perl-DBD-MySQL -y

安装rpm包：rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

安装Manager节点：（建议单独准备一台管理服务器，或安装在从库上124）

安装依赖包：yum install -y perl-Config-Tiny perl-Log-Dispatch epel-releaseperl-

Parallel-ForkManager perl-Time-HiRes

安装rpm包：rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

数据源中没有要下载文件的资源：

P126- MySQL-MHA高可用架构-架构搭建

架构搭建：

创建MHA专用监控管理用户：（主库创建122，主从环境会自动复制到从库）

grant all privileges on *.* to mha@'%' identified by '123';

准备Manager配置文件：(从库124)

创建配置文件目录：mkdir -p /etc/mha

创建日志目录：mkdir -p /var/log/mha/app1

编辑mha配置文件：

cat > /etc/mha/app1.cnf <<EOF

[server default]

manager_log=/var/log/mha/app1/manager

manager_workdir=/var/log/mha/app1

master_binlog_dir=/data/mysql/binlog

hostname=192.168.3.122

[server2]

hostname=192.168.3.123

[server3]

hostname=192.168.3.124

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

验证SSH互信：

验证通过：All SSH connection tests passed successfully.

验证主从状态：

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

主从状态正常：MySQL Replication Health is OK.

启动Manager管理：

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

检查MHA工作状态：

masterha_check_status --conf=/etc/mha/app1.cnf

状态正常：app1 (pid:4649) is running(0:PING_OK), master:192.168.3.122

P128- MySQL-MHA高可用架构-工作原理

站在开发角度分析MHA应该具备的功能：

主库宕机处理过程：（一次性架构，只可恢复一次）

① 监控节点（通过配置文件获取所有节点信息）：

系统、网络、SSH连接主库、主从状态（重点是主库）

② 选主：

多slave数据有差异（gtid、position），按照差异最小化选主

多slave数据无差异（gtid、position），按照配置文件中顺序选主

设定有权重（candidate_master=1），按照权重设定选主：

应用场景：两地三中心、keepalived实现VIP

a. 权重指定slave不得落后master 100兆relay_log，否则权重失效；

b. 除非设定check_repl_delay=0，则会强制权重选主

③ 数据补偿：

SSH能连接时，slave对比master的GTID或Position号，立即将缺失的

二进制日志保存到各从节点，并恢复应用（save_binary_logs）

SSH不能连接时，对比从库间的relay_log的差异（apply_diff_relay_logs）

④ Failover（masterha_master_switch）：

进行主从切换，对外提供服务；其余从库和新主库重新确立主从关系

⑤ 应用透明（VIP，MHA自带功能）

⑥ 故障切换通知（send_reprt）

通知管理员，重新搭建MHA；因为MHA是一次性架构

⑦ 二次数据补偿（binlog_server）

⑧ 自愈（待开发。。。。。）

P129- MySQL-MHA高可用架构-工具介绍

MHA软件包结构介绍：

MHA配置文件介绍：

额外配置参数介绍：为slave配置权重

a. candidate_master=1；

指定slave为权重模式，且不得落后master 100兆relay_log，否则权重失效；

b. check_repl_delay=0；

无条件，强制执行权重

应用场景：两地三中心、keepalived实现VIP

masterha_check_status --conf=/etc/mha/app1.cnf

mha停止工作：app1 is stopped(2:NOT_RUNNING)

② 查看manager程序是否退出

masterha_

进程已退出：[1]+ Done nohup masterha_manager –conf=/etc/mha/

app1.cnf –remove_dead_master_conf --ignore_last_failover < /dev/null >

/var/log/mha/app1/manager.log 2>&1

③ 查看master线程是否切换

主库切换成功：Master_Host: 192.168.3.123

④ 查看故障库[server1]节点是否丢失

cat vim /etc/mha/app1.cnf

故障库节点丢失：

自动恢复失败：

可查看manager工作日志，查找恢复异常原因

cat /var/log/mha/app1/manager

2、修复MHA架构：

① 修复故障服务器

systemctl start mysqld

② 修复主从：（manager工作日志中可找到change master to 语句）

vim /var/log/mha/app1/manager

CHANGE MASTER TO MASTER_HOST='192.168.3.122',MASTER_PORT=3306,

MASTER_AUTO_POSITION=1,MASTER_USER='rep',MASTER_PASSWORD='123';

③ 在manager配置文件中加入节点

vim /etc/mha/app1.cnf

[server1]

hostname=192.168.3.122

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

④ 验证主从状态：

主从状态正常：MySQL Replication Health is OK.

⑤ 启动Manager管理：

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

⑥ 检查MHA工作状态：

masterha_check_status --conf=/etc/mha/app1.cnf

状态正常：app1 (pid:4649) is running(0:PING_OK), master:192.168.3.122

P131- MySQL-MHA高可用架构-VIP实现应用透明

创建VIP脚本文件：（本地有写好的文件可以直接上传）

vim /usr/local/bin/master_ip_failover

#!/usr/bin/env perl

use strict;

use warnings FATAL => 'all';

use Getopt::Long;

my (

$command, $ssh_user, $orig_master_host, $orig_master_ip,

$orig_master_port, $new_master_host, $new_master_ip, $new_master_port

);

my $vip = '192.168.3.120/24';

my $key = '1';

my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";

my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

GetOptions(

'command=s' => \$command,

'ssh_user=s' => \$ssh_user,

'orig_master_host=s' => \$orig_master_host,

'orig_master_ip=s' => \$orig_master_ip,

'orig_master_port=i' => \$orig_master_port,

'new_master_host=s' => \$new_master_host,

'new_master_ip=s' => \$new_master_ip,

'new_master_port=i' => \$new_master_port,

);

exit &main();

sub main {

print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

my $exit_code = 1;

eval {

print "Disabling the VIP on old master: $orig_master_host \n";

warn "Got Error: $@\n";

elsif ( $command eq "start" ) {

my $exit_code = 10;

eval {

print "Enabling the VIP - $vip on the new master - $new_master_host \n";

elsif ( $command eq "status" ) {

print "Checking the Status of the script.. OK \n";

`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;

return 0 unless ($ssh_user);

sub stop_vip() {

`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;

"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip

sub usage {

--orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";

chmod +x /usr/local/bin/master_ip_failover

脚本文件修改项：

授予执行权限：

中英文字符转换：

dos2unix /usr/local/bin/master_ip_failover

添加VIP脚本路径到manager配置文件中：

vim /etc/mha/app1.cnf

[server default]

master_ip_failover_script=/usr/local/bin/master_ip_failover

注意：第一次配置VIP时，需要在主库手工绑定VIP，默认centos7没有ifconfig命

安装ifconfig命令：yum install -y net-tools

绑定VIP：ifconfig eth0:1 192.168.3.120/24 解绑VIP：ifconfig eth0:1 down

生成完成后ifconfig // ip addr可以看到虚拟IP已存在!!

重启manager服务：

关闭：masterha_stop --conf=/etc/mha/app1.cnf

开启：nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

P132- MySQL-MHA高可用架构-模拟VIP故障恢复

模拟 P129的故障恢复实验。。。

转移成功后VIP会出现在新主机上。。。

P133- MySQL-MHA高可用架构-故障邮件提醒

以下所有脚本都需授予执行权限：(脚本已下载到本地，不知道哪里有问题邮件发送不成功)

chmod +x 脚本名

安装邮件服务器：（sendEmail解压后即可使用）

wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz -P /

tar xf /sendEmail-v1.56.tar.gz -C /

cp /sendEmail-v1.56/sendEmail /usr/local/bin/

创建手工发送邮件测试脚本：

vim test

#!/bin/bash

/usr/local/bin/sendEmail -o tls=no -f g_lee0916@126.com -t 2387077588@qq.com -s stmp.126.com:25 -xu g_lee0916 -xp ybbhlkg1dddf -u "MHA Waring" -m "YOUR MHA NAY BE FAILOVER" &>/tmp/sendmail.log

创建报警邮件发送脚本：

vim send

#!/usr/bin/perl

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;

use warnings FATAL => 'all';

use Mail::Sender;

use Getopt::Long;

#new_master_host and new_slave_hosts are set only when recovering master succeeded

my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body );

my $smtp='smtp.126.com';

my $mail_from='g_lee0916@126.com';

my $mail_user='g_lee0916';

my $mail_pass='ybbhlkg1dddf';

my $mail_to='2387077588@qq.com';

#my $mail_to=['to1@qq.com','to2@qq.com'];

GetOptions(

'orig_master_host=s' => \$dead_master_host,

'new_master_host=s' => \$new_master_host,

'new_slave_hosts=s' => \$new_slave_hosts,

'subject=s' => \$subject,

'body=s' => \$body,

);

# Do whatever you want here

mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body);

sub mailToContacts {

my ($smtp, $mail_from, $mail_user, $mail_pass, $mail_to, $subject, $msg ) = @_;

open my $DEBUG, ">/var/log/masterha/app1/mail.log"

or die "Can't open the debug file:$!\n";

my $sender = new Mail::Sender {

ctype => 'text/plain;charset=utf-8',

encoding => 'utf-8',

smtp => $smtp,

from => $mail_from,

auth => 'LOGIN',

TLS_allowed => '0',

authid => $mail_user,

authpwd => $mail_pass,

to => $mail_to,

subject => $subject,

debug => $DEBUG

};

$sender->MailMsg(

{

msg => $msg,

debug => $DEBUG

}

) or print $Mail::Sender::Error;

return 1;

}

exit 0;

添加邮件发送脚本路径到manager配置文件中：

vim /etc/mha/app1.cnf

[server default]

report_script=/usr/local/bin/send

重启manager服务：

关闭：masterha_stop --conf=/etc/mha/app1.cnf

开启：nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

模拟故障：

依照P129执行。。。

P134- MySQL-MHA高可用架构-Binlog Server

Binlog server：数据补偿功能，专用存储binlog日志

binlogserver配置：

找一台额外的机器，必须要和原库版本一致，我们直接用的第二个slave（db03）

在manager配置文件中加入参数：

vim /etc/mha/app1.cnf

[binlog1]

no_master=1 #禁止参与选主

hostname=192.168.3.124

master_binlog_dir=/data/mysql/binlogserver

创建相应目录：

mkdir -p /data/mysql/binlogserver

chown -R mysql.mysql /data/*

拉取binlog：binlog号根据同步最慢的从库中Master_Log_File: mysql-bin.000002决定

cd /data/mysql/binlogserver

mysqlbinlog -R --host=192.168.3.122 --user=mha --password=123 --raw --stop-never mysql-bin.000002 &

重启manager服务：

关闭：masterha_stop --conf=/etc/mha/app1.cnf

开启：nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover </dev/null> /var/log/mha/app1/manager.log 2>&1 &

模拟故障：

依照P129执行。。。

重新配置数据补偿功能：

依照P134执行。。。

P136- MySQL-MHA高可用架构-管理员职责

1. 搭建：MHA+VIP+SendReport+BinlogServer

2. 监控及故障处理（依照P129+P134执行）

3. 高可用架构的优化

核心是：尽可能降低主从的延时，让MHA花在数据补偿上的时间尽量减少。

5.7 版本，开启GTID模式，开启从库SQL并发复制。

P137- MySQL-MHA+Atlas读写分离应用

Atlas介绍：简单说就是让数据库任何操作都通过Atlas执行，再通过配置达到读写分离

安装配置Atlas：（yum或下载rpm包都可以；这里是下载的rpm包）

yum install -y Atlas*

编辑配置文件：（跳转到mysql-proxy读写分离目录，先把原配置文件注释；在建新的）

cd /usr/local/mysql-proxy/conf

mv test.cnf test.cnf.bak

vi test.cnf

[mysql-proxy]

admin-username = user

admin-password = pwd

proxy-backend-addresses = 192.168.3.120:3306

proxy-read-only-backend-addresses = 192.168.3.123:3306,192.168.3.124:3306

pwds = rep:3yb5jEku5h4=,mha:3yb5jEku5h4=

log-path = /usr/local/mysql-proxy/log

sql-log=ON

proxy-address = 0.0.0.0:33060

admin-address = 0.0.0.0:2345

charset=utf8

启动Atlas：

/usr/local/mysql-proxy/bin/mysql-proxyd test start

ps -ef |grep proxy

Atlas功能测试：

mysql -umha -p123 -h192.168.3.120 -P33060

测试读操作：

select @@server_id;

测试写操作：（执行事务假装是写操作）

begin;select @@server_id;commit;

制作加密密码：/usr/local/mysql-proxy/bin/encrypt 123

重启服务：/usr/local/mysql-proxy/bin/mysql-proxyd test restart

管理Atlas:

连接管理端口：mysql -uuser -ppwd -h127.0.0.1 -P2345

打印帮助：select * from help;

查询后端所有节点信息：select * from backends;

动态删除节点：remove backend 3;

动态添加节点：add slave 10.0.0.53:3306;

同步保存配置：save config;

读写分离工具建议：

MySQL-Router ---> MySQL官方

ProxySQL --->Percona

Maxscale ---> MariaDB

P139- MySQL-Mycat环境准备

环境准备：

两台虚拟机 db01 db02

每台创建四个mysql实例：3307 3308 3309 3310

删除历史环境：

pkill mysqld

rm -rf /data/330*

mv /etc/my.cnf /etc/my.cnf.bak

创建初始化目录：

mkdir -p /data/{3307,3308,3309,3310}/data

初始化：

mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/mysql

mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/mysql

mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/mysql

mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/app/mysql

准备配置文件和启动脚本：

========db01==============

cat >/data/3307/my.cnf<<EOF

socket=/data/3307/mysql.sock

datadir=/data/3307/data

port=3307

log-error=/data/3307/mysql.log

log_bin=/data/3307/mysql-bin

cat >/data/3308/my.cnf<<EOF

socket=/data/3308/mysql.sock

datadir=/data/3308/data

port=3308

log-error=/data/3308/mysql.log

log_bin=/data/3308/mysql-bin

cat >/data/3309/my.cnf<<EOF

socket=/data/3309/mysql.sock

datadir=/data/3309/data

port=3309

log-error=/data/3309/mysql.log

log_bin=/data/3309/mysql-bin

cat >/data/3310/my.cnf<<EOF

socket=/data/3310/mysql.sock

datadir=/data/3310/data

port=3310

log-error=/data/3310/mysql.log

log_bin=/data/3310/mysql-bin

cat >/etc/systemd/system/mysqld3307.service<<EOF

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

cat >/etc/systemd/system/mysqld3308.service<<EOF

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

cat >/etc/systemd/system/mysqld3309.service<<EOF

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

cat >/etc/systemd/system/mysqld3310.service<<EOF

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf

========db02===============

cat >/data/3307/my.cnf<<EOF

socket=/data/3307/mysql.sock

datadir=/data/3307/data

port=3307

log-error=/data/3307/mysql.log

log_bin=/data/3307/mysql-bin

cat >/data/3308/my.cnf<<EOF

socket=/data/3308/mysql.sock

datadir=/data/3308/data

port=3308

log-error=/data/3308/mysql.log

log_bin=/data/3308/mysql-bin

cat >/data/3309/my.cnf<<EOF

socket=/data/3309/mysql.sock

datadir=/data/3309/data

port=3309

log-error=/data/3309/mysql.log

log_bin=/data/3309/mysql-bin

cat >/data/3310/my.cnf<<EOF

socket=/data/3310/mysql.sock

datadir=/data/3310/data

port=3310

log-error=/data/3310/mysql.log

log_bin=/data/3310/mysql-bin

cat >/etc/systemd/system/mysqld3307.service<<EOF

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

cat >/etc/systemd/system/mysqld3308.service<<EOF

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

cat >/etc/systemd/system/mysqld3309.service<<EOF

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

cat >/etc/systemd/system/mysqld3310.service<<EOF

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf

chown -R mysql.mysql /data/*

修改权限，启动多实例：

systemctl start mysqld3307

systemctl start mysqld3308

systemctl start mysqld3309

systemctl start mysqld3310

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"

mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"

mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"

mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"

P140- MySQL-Mycat主从搭建

1、节点主从规划：（四主四从 =>> 2对双主+4对单从）

2、分片规划：

3、开始配置：

shard1：

192.168.3.122:3307 <-----> 192.168.3.123:3307

grant replication slave on *.* to repl@'192.168.3.%' identified by '123';

grant all on *.* to root@'192.168.3.%' identified by '123' with grant option;

CHANGE MASTER TO MASTER_HOST='192.168.3.123', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

CHANGE MASTER TO MASTER_HOST='192.168.3.122', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

192.168.3.122:3309 ------> 192.168.3.122:3307

mysql -S /data/3309/mysql.sock

CHANGE MASTER TO MASTER_HOST='192.168.3.122', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

192.168.3.123:3309 ------> 192.168.3.123:3307

mysql -S /data/3309/mysql.sock

CHANGE MASTER TO MASTER_HOST='192.168.3.123', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

192.168.3.123:3308 <-----> 192.168.3.122:3308

shard2：

grant replication slave on *.* to repl@'192.168.3.%' identified by '123';

grant all on *.* to root@'192.168.3.%' identified by '123' with grant option;

CHANGE MASTER TO MASTER_HOST='192.168.3.122', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

CHANGE MASTER TO MASTER_HOST='192.168.3.123', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

192.168.3.123:3310 -----> 192.168.3.123:3308

mysql -S /data/3310/mysql.sock

CHANGE MASTER TO MASTER_HOST='192.168.3.123', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

192.168.3.122:3310 -----> 192.168.3.122:3308

mysql -S /data/3310/mysql.sock

CHANGE MASTER TO MASTER_HOST='192.168.3.122', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes

4、检测主从状态：

mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes

mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes

mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes

注：如果中间出现错误，在每个节点进行执行以下命令

mysql -S /data/3307/mysql.sock -e "stop slave; reset slave all;"

mysql -S /data/3308/mysql.sock -e "stop slave; reset slave all;"

mysql -S /data/3309/mysql.sock -e "stop slave; reset slave all;"

mysql -S /data/3310/mysql.sock -e "stop slave; reset slave all;"

P141- MySQL-Mycat分布式架构演变

P142- MySQL- Mycat安装配置

MyCAT安装：（安装包需下载http://dl.mycat.io/）122

查看是否配置环境变量：echo $JAVA_HOME

查找安装路径：whereis java（查看当前路径直到根路径）

编辑：vim /etc/profile

export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b08-0.el7_7.x86_64

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib

PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin

刷新配置：source /etc/profile

上传并解压mycat（解压就能用）：

tar xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

启动和连接：

配置环境变量：

vim /etc/profile

export PATH=/app/mycat/bin:$PATH

source /etc/profile

错误：修改环境变量后，导致一些常用命令失效，如ll,ls,vi不能用

/bin/vi /etc/profile注释错误项后，重启

启动：

mycat start

连接mycat：

mysql -uroot -p123456 -h 127.0.0.1 -P8066（mycat默认虚拟账号）

grant all on *.* to root@'192.168.3.%' identified by '123';

source /root/world.sql

grant all on *.* to root@'192.168.3.%' identified by '123';

source /root/world.sql

读写分离配置文件处理：

注意：写节点中包含读节点；默认写节点不可用时,读节点也不可用；防止数据二次损坏

cd /app/mycat/conf

mv schema.xml schema.xml.bak

vim schema.xml （一主一从，读写分离）

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

mysql -uroot -p -h 127.0.0.1 -P8066

读：show variables like 'server_id';

写：begin;

select @@server_id;

配置读写分离及高可用：

vim schema.xml（两主两从，读写分离；；默认第一个写节点写，其余节点轮询读）

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

mysql -uroot -p -h 127.0.0.1 -P8066

show variables like 'server_id';

begin;

show variables like 'server_id';

对db01 3307节点进行关闭和启动,测试读写操作

配置中<dataHost>的属性介绍:

balance属性：

负载均衡类型，目前的取值有3种：

1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。

2. balance="1"，全部的readHost与standby writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。

3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。

writeType属性：

负载均衡类型，目前的取值有2种：

1. writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中:dnindex.properties .

2. writeType="1"，所有写操作都随机的发送到配置的writeHost，但不推荐使用

switchType属性：

-1 表示不自动切换

1 默认值，自动切换

2 基于MySQL主从同步的状态决定是否切换，心跳语句为 show slave status

其他属性：

maxCon="1000"：最大的并发连接数

minCon="10" ：mycat在启动之后，会在后端节点上自动开启的连接线程

tempReadHostAvailable="1"：一主一从中，主库不可用时，临时使用从库读功能

<heartbeat>select user()</heartbeat> 监测心跳

P145- MySQL- Mycat垂直分表

注意：生产库做分表，必须按配置重新导入一遍数据

vim schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

</writeHost>

</writeHost>

</dataHost>

创建测试库和表：（因为是双主模式，所以在其中一个主机执行就可以；可以参考 P140图）

create database taobao charset utf8;

create table user(id int,name varchar(20));

create database taobao charset utf8;

create table order_t(id int,name varchar(20));

P146- MySQL- Mycat核心特性——分片（水平拆分）

P147- MySQL- Mycat核心特性——分片（范围分片）

假设：需要对t3表分片

(1)行数非常多，2000w（1-1000w:sh1 1000w01-2000w:sh2）

(2)访问非常频繁，用户访问较离散

vim schema.xml（基于垂直分表）

<function name="rang-long"

class="io.mycat.route.function.AutoPartitionByLong">

<property name="mapFile">autopartition-long.txt</property>

</function>

vim autopartition-long.txt（range范围分片）

0-4=0

5-10=1

创建测试表：

create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"

create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat：

测试范围分片：（插入成功后，登录到两个物理节点上，可以看到数据分开存放）

mysql -uroot -p123456 -h 127.0.0.1 -P 8066

use TESTDB;

insert into t3(id,name)

values(1,'a'), (2,'b'), (3,'c'), (4,'d'), (11,'aa'), (12,'bb'), (13,'cc'), (14,'dd');

P148- MySQL- Mycat核心特性——分片（取模分片）

取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点

vim schema.xml（基于垂直分表）

vim rule.xml

创建测试表：

create table t4 (id int not null primary key auto_increment,name varchar(20) not null);

create table t4 (id int not null primary key auto_increment,name varchar(20) not null);

重启mycat：

测试取模分片：（插入成功后，登录到两个物理节点上，可以看到数据分开存放）

mysql -uroot -p123456 -h10.0.0.52 -P8066

use TESTDB;

insert into t4(id,name)

values(1,'a'), (2,'b'), (3,'c'), (4,'d'), (11,'aa'), (12,'bb'), (13,'cc'), (14,'dd');

P149- MySQL- Mycat核心特性——分片（枚举分片）

<property name="mapFile">partition-hash-int.txt</property>

</function>

Vim partition-hash-int.txt：

bj=0

sh=1

DEFAULT_NODE=1

columns 标识将要分片的表字段，algorithm 分片函数，其中分片函数配置中，mapFile标识配置文件名称

准备测试环境

create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat ：

测试取模分片：（插入成功后，登录到两个物理节点上，可以看到数据分开存放）

mysql -uroot -p123456 -h192.168.3.122 -P8066

use TESTDB

insert into t5(id,name) values(1,'bj');

insert into t5(id,name) values(2,'sh');

insert into t5(id,name) values(3,'bj');

insert into t5(id,name) values(4,'sh');

insert into t5(id,name) values(5,'tj');

P150- MySQL- Mycat核心特性——全局表（ER分片）

a b c d

join

select t1.name ,t.x from t1

join t

select t2.name ,t.x from t2

join t

select t3.name ,t.x from t3

join t

使用场景：

如果你的业务中有些数据类似于数据字典，比如配置文件的配置，

常用业务的配置或者数据量不大很少变动的表，这些表往往不是特别大，

而且大部分的业务场景都会用到，那么这种表适合于Mycat全局表，无须对数据进行切分，

要在所有的分片上保存一份数据即可，Mycat 在Join操作中，业务表与全局表进行Join聚合会优先选择相同分片内的全局表join，

避免跨库Join，在进行数据插入操作时，mycat将把数据分发到全局表对应的所有分片执行，在进行数据读取时候将会随机获取一个节点读取数据。

vim schema.xml

后端数据准备

create table t_area (id int not null primary key auto_increment,name varchar(20) not null);

use taobao

create table t_area (id int not null primary key auto_increment,name varchar(20) not null);

use taobao

重启mycat

mysql -uroot -p123456 -h10.0.0.52 -P8066

测试：

use TESTDB

insert into t_area(id,name) values(1,'a');

insert into t_area(id,name) values(2,'b');

insert into t_area(id,name) values(3,'c');

insert into t_area(id,name) values(4,'d');

16. E-R分片

join

为了防止跨分片join，可以使用E-R模式

P151- MySQL-优化-优化哲学

P152- MySQL-优化-Top（CPU指标）

CPU的作用：

计算器：计算 ---> 计算二进制代码语言

控制器：资源管理 ---> 通过OS kernel（系统内核）调用，来管理硬件资源

寄存器：缓存 ---> 1-3级缓存

CPU分配方式：

以时间段来划分每个程序的占用的时间片段长短

CPU各项指标说明：

%Cpu(s): 0.0 us, 0.3 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st

查看CPU每个核心的分别使用的情况（按1）

P153- MySQL-优化-Top（MEM-IO指标）

MEM内存各项指标说明：

KiB Mem: 1003432 total, 940184 used, 63248 free, 0 buffers

Swap虚拟内存：

KiB Swap: 2097148 total, 582400 used, 1514748 free. 60024 cached Mem

Linux默认MEM使用到一定比例时，就会自动优先使用Swap；

但是对于数据库来说，要尽量避免使用Swap，等MEM耗尽后，让系统释放buffer cache来继续工作，以免使用Swap卡死

显示当前磁盘IO水平（kb/ms）：iostat -dk 1（yum install -y sysstat）

Top+iostat查看性能情况现象说明：

① IO高、cpu us也高；属于正常现象

② CPU us高，IO很低；MySQL不在做增删改查，有可能是存储过程数、排序、分组、

多表连接

③ Wait,SYS高，IO低；IO出问题了，锁等待过多的几率比较大

④ IOPS达到最大值（IOPS：每秒磁盘最多能够发生的IO次数，这是个定值）；

频繁小事务，IOPS很高，达到阈值；可能IO吞吐量没超过IO最大吞吐量；但也无法

产生新的IO了

存储规划有问题

（事务提交数据量太小，应该按磁盘最佳IO吞吐量，分段提交达到最佳效果）

show engine innodb status

P154- MySQL-优化-硬件（主机建议）

网络：保证网络的稳定持久可用，配置网络的高可用（建议主备模式）

P156- MySQL-优化-硬件（系统建议）

虚拟机vm12.5，OS centos 6.9（系统已优化），cpu*4（I5 4440 3.1GHZ）,MEM*4GB ,HardDisk:SSD

模拟数据库数据

drop database if exists oldboy;

create database oldboy charset utf8mb4 collate utf8mb4_bin;

use oldboy;

create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);

delimiter //

create procedure rand_data(in num int)

begin

declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

declare str2 char(2);

declare str4 char(4);

declare i int default 0;

while i<num do

set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));

set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));

set i=i+1;

insert into t_100w values (i,floor(rand()*num),str2,str4,now());

call rand_data(10000000);

commit;

mysqlslap --defaults-file=/etc/my.cnf \

--concurrency=100 --iterations=1 --create-schema='oldboy' \

--query="select * from oldboy.t1000w where k2='FGCD'" engine=innodb \

--number-of-queries=200 -uroot -p123 -verbose

P158- MySQL-优化-参数优化-1

Max_connections *****

Mysql的最大连接数，mysql会为每个连接提供内存缓冲区，随着服务器的并发请求量增大，可以调高这个值，当然这是要建立在机器能够支撑的情况下

（2）调整依据

查看当前设定最大连接数（一般高于实际连接数10% 即可）

show variables like 'max_connections';

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

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

| max_connections | 151 |

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

查看当前实际最大连接数

show status like 'Max_used_connections';

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

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

| Max_used_connections | 101 |

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

1.开启数据库时,我们可以临时设置一个比较大的测试值

2.观察show status like 'Max_used_connections';变化

3.如果max_used_connections跟max_connections相同,

那么就是max_connections设置过低或者超过服务器的负载上限了，

低于10%则设置过大.

back_log ***

Mysql可暂存的连接数量，当mysql线程在短时间内得到非常多的连接请求时，且mysql的连接数据达到max_connections时，back_log指定短时间内还可以有多少个请求被存在推栈中，等待某一连接释放资源；若等待连接数超过back_log，将不被授予连接资源。

（2）判断依据

查看当前连接数

show full processlist

发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值

（3）修改方式举例

vim /etc/my.cnf

back_log=1024

wait_timeout和interactive_timeout ****

wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数

interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，

默认的是28800秒/8小时，可调优为7200/2分钟。

注：wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接

不起作用

（2）设置建议

根据业务情况合理设置wait_timeout、interactive_timeout

（3）修改方式举例

wait_timeout=60（1分钟）

interactive_timeout=1200（20分钟）

key_buffer_size *****

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度

① 此参数与myisam表的索引有关

② 临时表的创建有关（多表链接、子查询中、union）

在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃

临时表有两种创建方式：

内存中------->key_buffer_size

磁盘上------->ibdata1(5.6)

ibtmp1 (5.7）

（2）设置依据

通过key_read_requests和key_reads可以知道key_baffer_size设置是否合理。

mysql> show variables like "key_buffer_size%";

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

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

| key_buffer_size | 8388608 |

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

mysql> show status like "key_read%";

| Key_read_requests | 10 |

| Key_reads | 2 |

一共有10个索引读取请求，有2个请求在内存中没有找到直接从硬盘中读取索引

控制在 5%以内（requests/reads*100）

注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。

可以使用检查状态值created_tmp_disk_tables得知：

mysql> show status like "created_tmp%";

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

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

| Created_tmp_disk_tables | 0 |==》磁盘

| Created_tmp_files | 6 |

| Created_tmp_tables | 1 |==》内存

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

通常地，我们习惯以：

Created_tmp_tables/(Created_tmp_disk_tables+Created_tmp_tables)

Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会

比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。

Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

控制在5%-10%以内

看以下例子：

在调用mysqldump备份数据时，大概执行步骤如下：

180322 17:39:33 7 Connect root@localhost on

7 Query /*!40100 SET @@SQL_MODE='' */

7 Init DB guo

7 Query SHOW TABLES LIKE 'guo'

7 Query LOCK TABLES `guo` READ /*!32311 LOCAL */

7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1

7 Query show create table `guo`

7 Query show fields from `guo`

7 Query show table status like 'guo'

7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`

7 Query UNLOCK TABLES

7 Quit

其中，有一步是：show fields from `guo`。从slow query记录的执行计划中，可以知道

它也产生了 Tmp_table_on_disk。

所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的

Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。

（3）配置方法（8M跨度微调，，最大一般在 128-256之间）

key_buffer_size=64M

P159- MySQL-优化-参数优化-2

query_cache_size ***（MYSQL-5.7默认关闭，8.0取消该参数）

（1）简介：

查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样

的select语句（区分大小写）,将直接从缓冲区中读取结果。

mysql> show variables like '%query_cache%' ;

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

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

| have_query_cache | YES |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 1048576 |

| query_cache_type | OFF |

| query_cache_wlock_invalidate | OFF |

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

6 rows in set (0.00 sec)

-------------------配置说明-------------------------------

以上信息可以看出query_cache_type为off表示不缓存任何查询

各字段的解释：

query_cache_limit：超过此大小的查询将不缓存

query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄"双刃剑"，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)

query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。

如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。

如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=128M

query_cache_type=1

max_connect_errors ***

max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

max_connect_errors=2000

sort_buffer_size ***（以SQL优化为主，这个参数值有点就好）

（1）简介：

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速

Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。

例如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存

（3）配置方法

修改/etc/my.cnf文件，在[mysqld]下面添加如下：

sort_buffer_size=1M

max_allowed_packet *****

（1）简介：

mysql根据配置文件会限制，server接受的数据包大小。

（2）配置依据：

有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数

（3）配置方法：

max_allowed_packet=32M

join_buffer_size ***

select a.name,b.name from a join b on a.id=b.id where xxxx

用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享；用完会释放。

尽量在SQL与方面进行优化，效果较为明显。

优化的方法：在on条件列加索引，至少应当是有MUL索引

sort_buffer_size=3M

thread_cache_size *****

(1)简介

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上

限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.

（2）配置依据

通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。

服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

试图连接到MySQL(不管是否连接成功)的连接数

mysql> show status like 'threads_%';

| Threads_connected | 2 |

| Threads_cached | 8 |

| Threads_created | 4783 |

| Threads_running | 1 |

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。

4 rows in set (0.00 sec)

Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。

Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现 Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

(3)配置方法：

thread_cache_size=32

整理：

Threads_created ：一般在架构设计阶段，会设置一个测试值，做压力测试。

结合zabbix监控，看一段时间内此状态的变化。

如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。

如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）

P160- MySQL-优化-参数优化-3

innodb_buffer_pool_size *****

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于 MyISAM表的作用一样。

（2）配置依据：

InnoDB使用该参数指定大小的内存来缓冲数据和索引。

对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。

（3）配置方法

innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit ******

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；

1，

每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的 ACID；

2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。

（2）配置依据

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。

根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0 或2。

（3）配置方法

innodb_thread_concurrency ***（多并发下的最优并发数量）

双1标准中的一个1

此参数用来设置innodb线程的并发数量，默认值为0表示不限制。

（2）配置依据

在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：

如果一个工作负载中，并发用户线程的数量小于64，建议设置 innodb_thread_concurrency=0；

如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置 innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到

发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数

量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，

如果高于这个数，性能反而下降。在这种情况下，建议设置

innodb_thread_concurrency参数为80，以避免影响性能。

如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20 个虚拟CPU），建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，

这取决于性能体现），如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定

mysqld进程到专有的虚拟CPU。

但是需要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟 CPU，允许

其他应用程序使用虚拟CPU的一部分或全部。

在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。

定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对 innodb_thread_concurrency参数的设置进行调整。

show status like 'threads_%';

show processlist;

（3）配置方法：

innodb_thread_concurrency=8

方法:

1. 看top ,观察每个cpu的各自的负载情况

2. 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值

3. 一直观察top状态,直到达到比较均匀时,说明已经到位了.

innodb_log_buffer_size（用内存换IO）

此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

innodb_log_buffer_size=128M

设定依据：

1、大事务：存储过程调用 CALL

2、多事务

innodb_log_file_size = 100M *****

设置 ib_logfile0 ib_logfile1

此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.

innodb_log_file_size = 100M

##innodb_log_files_in_group = 3 *****

为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3组

read_buffer_size = 1M **

MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

read_rnd_buffer_size = 1M **

MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

bulk_insert_buffer_size = 8M **

批量插入数据缓存大小，可以有效提高插入效率，默认为8M

log-bin=/data/mysql-bin

binlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M

max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小

max_binlog_size= 512M //指定binlog日志文件的大小，如果当前的日志大小达到 max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。

expire_logs_days = 7 //定义了mysql清除过期日志的时间。

二进制日志自动删除的天数。默认值为0,表示"没有自动删除"。

log-bin=/data/mysql-bin

sync_binlog=1 什么时候刷新binlog到磁盘，每次事务commit

sync_binlog=1

双1标准(基于安全的控制)：

show status like 'com_%';

set sql_log_bin=0;

安全参数 *****

Innodb_flush_method=(O_DIRECT, fsync)

1、fsync ：

（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘

（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么写入磁盘；但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit 直接写入磁盘

2、 Innodb_flush_method=O_DIRECT

（1）在数据页需要持久化时，直接写入磁盘

（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘；但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次 commit直接写入磁盘

最安全模式：

innodb_flush_method=O_DIRECT

最高性能模式：

innodb_flush_log_at_trx_commit=0

innodb_flush_method=fsync

一般情况下，我们更偏向于安全。

"双一标准"

innodb_flush_log_at_trx_commit=1 ***************

sync_binlog=1 ***************

innodb_flush_method=O_DIRECT

##参数优化结果##

basedir=/data/mysql

log-error=/var/log/mysql.log

log_bin=/data/binlog/mysql-bin

innodb_buffer_pool_size=1024M

interactive_timeout=7200

key_buffer_size=16M

query_cache_size=64M

query_cache_type=1

query_cache_limit=50M

max_connect_errors=20

sort_buffer_size=2M

max_allowed_packet=32M

join_buffer_size=2M

thread_cache_size=200

innodb_log_buffer_size=32M

innodb_log_file_size=128M

innodb_log_files_in_group=3

binlog_cache_size=2M

max_binlog_cache_size=8M

max_binlog_size=512M

expire_logs_days=7

read_buffer_size=2M

read_rnd_buffer_size=2M

bulk_insert_buffer_size=8M

[client]

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

再次压力测试：

P162- MySQL-优化-锁的监控及处理

锁监控思维导图：

锁类型概念：

X ==》排它锁：保证数据一致性，防止幻读

Record Lock 行级锁（又叫记录锁）

UPDATE t_100w SET k1='av' WHERE id=10;

## tx2:

USE oldboy

UPDATE t_100w SET k1='az' WHERE id=10;

监控锁状态：

① 查看有没有锁等待

SHOW STATUS LIKE 'innodb_row_lock%';

Innodb_row_lock_current_waits：当前锁等待个数

Innodb_row_lock_waits：发生过锁等待数

② 查看哪个事务在等待(被阻塞了)：

SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';

trx_id : 事务ID号

trx_state : 当前事务的状态

trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )

trx_query : 当前被阻塞的操作(记录下来，一般是要丢给开发的)

③ 查看锁源,谁锁的我：

SELECT * FROM sys.innodb_lock_waits; ## ====>被锁的和锁定它的之间关系

locked_table：哪张表出现的等待

waiting_trx_id：等待的事务(与上个视图trx_id 对应)

waiting_pid：等待的线程号(与上个视图trx_mysql_thread_id)

blocking_trx_id：锁源的事务ID

blocking_pid：锁源的线程号（连接线程id号）

④ 找到锁源的thread_id（SQL线程）

SELECT * FROM performance_schema.threads WHERE processlist_id=15;

====> 41

⑤ 找到锁源的SQL语句：

-- 当前在执行的语句

SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;

-- 执行语句的历史

SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;

硬件环境: DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10

在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)

项目的职责：

1、通过top详细排查,发现mysqld进程占比达到了700-800%

2、其中有量的CPU是被用作的SYS和WAIT,us处于正常

3、怀疑是MySQL 锁或者SQL语句出了问题

4、经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句

(1) pt-query-diagest 查看慢日志

(2) 锁等待有没有?

db03 [(none)]>show status like 'innodb_row_lock%';

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

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

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 0 |

| Innodb_row_lock_time_avg | 0 |

| Innodb_row_lock_time_max | 0 |

| Innodb_row_lock_waits | 0 |

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

情况一:

有100多个current_waits,说明当前很多锁等待情况

情况二:

1000多个lock_waits,说明历史上发生过的锁等待很多

(1) 开发人员描述,此语句是事务挂起导致

我们提出建议是临时kill 会话,最终解决问题

(2) 开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待

临时解决方案：将阻塞事务的会话kill掉.

最终解决方案：修改代码中的业务逻辑

项目结果:

经过排查处理,锁等待的个数减少80%.解决了CPU持续峰值的问题.

锁监控设计到的命令：

show status like 'innodb_rows_lock%'

select * from information_schema.innodb_trx;

select * from sys.innodb_lock_waits;

select * from performance_schema.threads;

select * from performance_schema.events_statements_current;

select * from performance_schema.events_statements_history;

死锁监控：

① 在线查看死锁情况（只会显示最后一次）

show engine innodb status\G

② 将死锁记录到MySQL错误日志中：

show variables like '%deadlock%';

vim /etc/my.cnf

innodb_print_all_deadlocks = 1

P163- MySQL-优化-主从优化

enforce_gtid_consistency=ON

log_slave_updates=ON

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16 ## cpu核心数作为标准

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

P164- MySQL课程梳理

运维：

① SQL：

Select from where group by having order by limit

Join on 函数 concat group concat

1. 熟悉各版本及分支MySQL数据库产品的特性

2. 精通SQL语句及Schema设计规范（库表设计规范）

3. 精通B树索引查找算法及索引优化原理

4. 精通视图、存储过程\函数、触发器、事件（高级DBA）

5. 精通innodb\tokudb\rocksdb等存储引擎原理

6. 精通mysqldump\xtrabackup生产备份、恢复、迁移及备份策略设计与实现

7. 精通Replication原理及主从故障，延时分析及处理

8. 精通主流高可用MHA、MyCAT技术

9. 精通主流读写分离架构架构Atlas\Mycat\proxySQL

10. 精通MyCAT企业级分布式架构设计与实现

11. 精通MySQL架构、系统、硬件、参数、锁、索引、主从全方位优化

12. 精通MongoDB、Redis、ES主流NoSQL分布式架构

13. 精通Linux操作系统及shell编程

14. 精通 Percona-toolkit监控第三方管理优化工具

15. 精通zabbix数据库监控系统

P165-NoSQL-Redis介绍

优点：高性能读写、单一数据类型、支持客户端式分布式集群、一致性hash

多核结构、多线程读写性能高。

缺点：无持久化、节点故障可能出现缓存穿透、分布式需要客户端实现、跨机房数据同步困难、架构扩容复杂度高

Redis:

优点：高性能读写、多数据类型支持、数据持久化、高可用架构、支持自定义虚拟内存、支持分布式分片集群、单线程读写性能极高

缺点：多线程读写较Memcached慢

新浪、京东、直播类平台、网页游戏

memcache与redis在读写性能的对比：

memcached 适合多用户访问,每个用户少量的rw（读写）

redis 适合少用户访问,每个用户大量rw（读写）

Tair：

优点：高性能读写、支持三种存储引擎（ddb、rdb、ldb）、支持高可用、支持分布式分片集群、支撑了几乎所有淘宝业务的缓存。

缺点：单机情况下，读写性能较其他两种产品较慢

Redis使用场景介绍：

Memcached：多核的缓存服务，更加适合于多用户并发访问次数较少的应用场景

Redis：单核的缓存服务，单节点情况下，更加适合于少量用户，多次访问的应用场景。

Redis一般是单机多实例架构，配合redis集群出现。

P166-NoSQL-精通Redis安装部署

Redis安装部署：

下载：

wget http://download.redis.io/releases/redis-3.2.12.tar.gz

解压：

上传至 /data

tar xzf redis-3.2.12.tar.gz

mv redis-3.2.12 redis

安装：（需预装gcc编译环境）

yum -y install gcc automake autoconf libtool make

export PATH=/data/redis/src:$PATH

source /etc/profile

启动：（启动后命令行刷新后直接回车，会出现输入命令行）

redis-server &

连接测试：（插入键、值）

redis-cli

127.0.0.1:6379> set num 10

127.0.0.1:6379> get num

P167-NoSQL-精通Redis基本配置应用

创建基础配置文件：

mkdir -p /data/6379

cat > /data/6379/redis.conf<<EOF

logfile /data/6379/redis.log

port 6379

redis-cli shutdown // shutdown

启动启动：

127.0.0.1:6379> set name 张三

netstat -lnp|grep 63

连接测试：（插入键、值）

redis-cli

127.0.0.1:6379> get name

张三

P168-NoSQL-精通Redis安全管理

redis安全配置：

redis默认开启了保护模式，只允许本地回环地址登录并访问数据库。

禁止protected-mode

protected-mode yes/no （保护模式，是否只允许本地访问）

指定IP进行监听并配置密码：（密码主要控制连接后进行的操作）

vim /data/6379/redis.conf

bind 192.168.3.122 127.0.0.1

requirepass 123456

重启redis服务：

redis-cli shutdown

127.0.0.1:6379> set name zhangsan

本地连接测试：

方法一：连接时输入密码

redis-cli -a 123456

方法二：连接后输入密码

[root@db03 ~]# redis-cli

127.0.0.1:6379> auth 123456

127.0.0.1:6379> set a b

远程连接测试：

redis-cli -a 123456 -h 192.168.3.122 -p 6379

192.168.3.122:6379> set b 2

P169-NoSQL-精通Redis在线查看和修改配置

在线查看//修改配置信息（重新登录后生效，重启后以配置文件为准）：（共70个配置）

CONFIG GET *

CONFIG GET requirepass

CONFIG GET r*

CONFIG SET requirepass 123

redis持久化（内存数据保存到磁盘）：两种持久化方式同时存在默认使用AOF

RDB 持久化（基于时间点快照备份备份以覆盖形式存在）

可以在指定的时间间隔内生成数据集的时间点快照（point-in-time snapshot）。

优点：速度快，适合于用做备份，主从复制也是基于RDB持久化功能实现的。

缺点：会有数据丢失（服务不启动的情况无法执行定时备份）

在线手动快照：save

rdb持久化核心配置参数：（设置定时快照备份）

vim /data/6379/redis.conf

AOF 持久化(append-only log file) 相当于MySQL中的binlog日志

记录服务器执行的所有写操作命令，并在服务器启动时，通过重新执行这些命令

来还原数据集。 AOF 文件中的命令全部以 Redis 协议的格式来保存，新命令

vim /data/6379/redis.conf

redis-cli -a 123456 shutdown

rdb：基于快照的持久化，速度更快，一般用作备份，主从复制也是依赖于rdb持久化功能

查看配置是否配置成功：

P171-NoSQL-精通Redis数据类型种类

面试题：

redis 持久化方式有哪些？有什么区别？

aof：以追加的方式记录redis操作日志的文件。可以最大程度的保证redis数据安全，类似于mysql的binlog

Redis数据类型(笔试)：

KEY的通用操作：

KEYS * keys a keys a* 查看已存在所有键的名字 ****

TYPE 返回键所存储值的类型 ****

EXPIRE\ PEXPIRE 以秒\毫秒设定生存时间 ***

TTL\ PTTL 以秒\毫秒为单位返回生存时间 ***

批量删数：DECRBY num 10000

key:value

set name zhangsan

hash（字典类型）（一般使用工具由MySQL直接将数据同步到redis中）

hmset stu id 101 name zhangsan age 20 gender m

hmset stu1 id 102 name zhangsan1 age 21 gender f

取数据：

hmget stu id name age gender

hmget stu1 id name age gender

手动编写MySQL脚本录入Redis脚本：(时间类型需要函数变化)

select concat("hmset t_",id," id ",id," name ",name," country ",country," dis ",dis,

" pop ",pop) from test where id<100 limit 10 into outfile '/tmp/hmset.txt'

cat /tmp/hmset.txt | redis-cli -a 123456

在Redis中我们的最新微博ID使用了常驻缓存，这是一直更新的。

但是做了限制不能超过5000个ID，因此获取ID的函数会一直询问Redis。

只有在start/count参数超出了这个范围的时候，才需要去访问数据库。

系统不会像传统方式那样"刷新"缓存，Redis实例中的信息永远是一致的。

SQL数据库（或是硬盘上的其他类型数据库）只是在用户需要获取"很远"的数据时才会被触发，

而主页或第一个评论页是不会麻烦到硬盘上的数据库了。

SET 集合类型（join union）

应用场景：

案例：在微博应用中，可以将一个用户所有的关注人存在一个集合中，将其所有粉丝存在一个集合。

Redis还为集合提供了求交集、并集、差集等操作，可以非常方便的实现如共同关注、共同喜好、二度好友等功能，对上面的所有集合操作，你还可以使用不同的命

令选择将结果返回给客户端还是存集到一个新的集合中。

SortedSet（有序集合）

应用场景：

P177-NoSQL-精通Redis发布订阅

SUBSCRIBE channel [channel ...]

① 订阅频道，可以同时订阅多个频道

PUBLISH channel msg

② 将信息 message 发送到指定的频道 channel

UNSUBSCRIBE [channel ...]

③ 取消订阅指定的频道, 如果不指定频道，则会取消订阅所有频道

PSUBSCRIBE pattern [pattern ...]

##订阅一个或多个符合给定模式的频道，每个模式以 * 作为匹配符，比如 it* 匹配

所有以 it 开头的频道( it.news 、 it.blog 、 it.tweets 等等)

PUNSUBSCRIBE [pattern [pattern ...]]

##退订指定的规则, 如果没有参数则会退订所有规则

PUBSUB subcommand [argument [argument ...]]

##查看订阅与发布系统状态

注意：使用发布订阅模式实现的消息队列，当有客户端订阅channel后只能收到后续发布到该频道的消息，之前发送的不会缓存，必须Provider和Consumer同时在线。

发布订阅例子：

窗口1：

127.0.0.1:6379> SUBSCRIBE baodi

窗口2：

127.0.0.1:6379> PUBLISH baodi "jin tian zhen kaixin!"

订阅多频道：

窗口1：

127.0.0.1:6379> PSUBSCRIBE wang*

窗口2：

127.0.0.1:6379> PUBLISH wangbaoqiang "jintian zhennanshou "

P178-NoSQL-精通Redis发布订阅

redis的事务是基于队列实现的。

mysql的事务是基于事务日志和锁机制实现的。

redis是乐观锁机制。

开启事务功能时（multi）

开启事务：multi

加入队列：（事务开启后，所有操作自动加入队列等待提交事务）

提交事务：exec （对列中所有操作，要么全成功要么全失败）

窗口1：（在开启事务前监控键值，提交事务时根据键值是否改变，来执行事务或取消队列）

P179-NoSQL-精通Redis主从复制

redis（Master-Replicaset） *****

原理：

1. 副本库通过slaveof 192.168.3.122 6379命令,连接主库,并发送SYNC给主库

2. 主库收到SYNC,会立即触发BGSAVE,后台保存RDB,发送给副本库

3. 副本库接收后会应用RDB快照

4. 主库会陆续将中间产生的新的操作,保存并发送给副本库

5. 到此,我们主复制集就正常工作了再此以后,主库只要发生新的操作,都会以命令传播

的形式自动发送给副本库.

都在.

8. 如果发生主从关系断开时,从库数据没有任何损坏,在下次重连之后,从库发送PSYNC

给主库

9. 主库只会将从库缺失部分的数据同步给从库应用,达到快速恢复主从的目的

主从数据一致性保证：

min-slaves-to-write 1 ##最少保证一台从库执行了操作，才认为当前事务成功

min-slaves-max-lag 3 ##延迟超过3秒，就认为此次操作失败

主库是否要开启持久化？

开启！！如果不开有可能，主库重启操作=数据全丢失，造成从库复制主库造成数据丢失！

主从复制实现：（准备两个或两个以上redis实例）

创建多实例文件目录：

mkdir -p /data/638{0..2}

配置文件示例：

cat >> /data/6380/redis.conf <<EOF

port 6380

pidfile /data/6380/redis.pid

logfile "/data/6380/redis.log"

cat >> /data/6381/redis.conf <<EOF

port 6381

pidfile /data/6381/redis.pid

logfile "/data/6381/redis.log"

cat >> /data/6382/redis.conf <<EOF

port 6382

pidfile /data/6382/redis.pid

logfile "/data/6382/redis.log"

redis-server /data/6380/redis.conf

redis-server /data/6381/redis.conf

redis-server /data/6382/redis.conf

主节点：6380

从节点：6381、6382

开启主从：（6381/6382命令行）

redis-cli -p 6381 -a 123 SLAVEOF 127.0.0.1 6380

redis-cli -p 6382 -a 123 SLAVEOF 127.0.0.1 6380

查询主从状态：

redis-cli -p 6380 -a 123 info replication

redis-cli -p 6381 -a 123 info replication

redis-cli -p 6382 -a 123 info replication

P181-监控锁及优化

参考P162。。。

P182-MySQL+ redis-sentinel（哨兵）

作用：基于redis主从环境；相当于MHA

① 监控

② 自动选主，切换（6381 slaveof no one）

③ 2号从库（6382）指向新主库（6381）

④ 应用透明

⑤ 自动处理故障节点

sentinel搭建过程：

mkdir -p /data/26380

cat>> /data/26380/sentinel.conf <<EOF

port 26380

dir "/data/26380"

sentinel monitor mymaster 127.0.0.1 6380 1

sentinel down-after-milliseconds mymaster 5000

sentinel auth-pass mymaster 123

EOF

启动：（记录到日志里方便查看）

redis-sentinel /data/26380/sentinel.conf &>/tmp/sentinel.log &

Sentinal功能测试：

关掉主库：

redis-cli -p 6381 -a 123 shutdown

查看主从（关掉主库5秒后完成切换）：

redis-cli -p 6381 -a 123 info replication

重启6380：

redis-server /data/6380/redis.conf

查看主从（6380自动恢复为从库）：

redis-cli -p 6381 -a 123 info replication

Sentinel管理命令：

redis-cli -p 26380

PING ：返回 PONG 。

SENTINEL masters ：列出所有被监视的主服务器

SENTINEL slaves <master name>

SENTINEL get-master-addr-by-name <master name> ：返回给定名字的主服务器的 IP 地址和端口号。

SENTINEL reset <pattern> ：重置所有名字和给定模式 pattern 相匹配的主服务器。

SENTINEL failover <master name> ：当主服务器失效时，在不询问其他 Sentinel 意见的情况下，强制开始一次自动故障迁移。

P183-MySQL+ redis-redis cluster（分布式）

高性能：

1、在多分片节点中，将16384个槽位，均匀分布到3个分片节点中

2、存数据时，将key做hash运算,然后和16384进行取模，得出槽位值（0-16383之间）

3、根据计算得出的槽位值，找到相对应的分片节点的主节点，存储到相应槽位上

4、如果客户端当时连接的节点不是将来要存储的分片节点，分片集群会将客户端连接切换至真正存储节点进行数据存储

高可用：

在搭建集群时，会为每一个分片的主节点，对应一个从节点，实现slaveof的功能，同时当主节点down，实现类似于sentinel的自动failover的功能。

Redis集群规划、搭建过程：

6个redis实例，一般会放到3台硬件服务器（端口号：7000-7005）

注：在企业规划中，一个分片的两个分到不同的物理机，防止硬件主机宕机造成的整个分片数据丢失。

安装集群插件：

EPEL源安装ruby支持

yum install ruby rubygems -y

使用国内源：（添加新源，删除旧源，下载插件）

gem sources -l

gem sources -a http://mirrors.aliyun.com/rubygems/

gem sources --remove https://rubygems.org/

gem sources -l

gem install redis -v 3.3.3

集群节点准备：

mkdir -p /data/700{0..5}

cat > /data/7000/redis.conf <<EOF

port 7000

daemonize yes

pidfile /data/7000/redis.pid

loglevel notice

logfile "/data/7000/redis.log"

dbfilename dump.rdb

dir /data/7000

protected-mode no

cluster-enabled yes

cluster-config-file nodes.conf

cluster-node-timeout 5000

appendonly yes

EOF

cat >> /data/7001/redis.conf <<EOF

port 7001

daemonize yes

pidfile /data/7001/redis.pid

loglevel notice

logfile "/data/7001/redis.log"

dbfilename dump.rdb

dir /data/7001

protected-mode no

cluster-enabled yes

cluster-config-file nodes.conf

cluster-node-timeout 5000

appendonly yes

EOF

cat >> /data/7002/redis.conf <<EOF

port 7002

daemonize yes

pidfile /data/7002/redis.pid

loglevel notice

logfile "/data/7002/redis.log"

dbfilename dump.rdb

dir /data/7002

protected-mode no

cluster-enabled yes

cluster-config-file nodes.conf

cluster-node-timeout 5000

appendonly yes

EOF

cat >> /data/7003/redis.conf <<EOF

port 7003

daemonize yes

pidfile /data/7003/redis.pid

loglevel notice

logfile "/data/7003/redis.log"

dbfilename dump.rdb

dir /data/7003

protected-mode no

cluster-enabled yes

cluster-config-file nodes.conf

cluster-node-timeout 5000

appendonly yes

EOF

cat >> /data/7004/redis.conf <<EOF

port 7004

daemonize yes

pidfile /data/7004/redis.pid

loglevel notice

logfile "/data/7004/redis.log"

dbfilename dump.rdb

dir /data/7004

protected-mode no

cluster-enabled yes

cluster-config-file nodes.conf

cluster-node-timeout 5000

appendonly yes

EOF

cat >> /data/7005/redis.conf <<EOF

port 7005

daemonize yes

pidfile /data/7005/redis.pid

loglevel notice

logfile "/data/7005/redis.log"

dbfilename dump.rdb

dir /data/7005

protected-mode no

cluster-enabled yes

cluster-config-file nodes.conf

cluster-node-timeout 5000

appendonly yes

EOF

启动节点：

redis-server /data/7000/redis.conf

redis-server /data/7001/redis.conf

redis-server /data/7002/redis.conf

redis-server /data/7003/redis.conf

redis-server /data/7004/redis.conf

redis-server /data/7005/redis.conf

验证节点是否启动：

ps -ef |grep redis

将节点加入集群管理：（按提示输入yes；；默认前三IP为主，后三IP为从；；

从库要变换位置不与主库同IP）

redis-trib.rb create --replicas 1 127.0.0.1:7000 127.0.0.1:7001 \

127.0.0.1:7002 127.0.0.1:7003 127.0.0.1:7004 127.0.0.1:7005

集群主节点状态：（任何集群管理操作都在管理节点执行）

redis-cli -p 7000 cluster nodes | grep master

集群从节点状态：

redis-cli -p 7000 cluster nodes | grep slave

增加新的节点：（非业务繁忙期操作，分配槽位中若有数据，会一并分配，影响性能）

mkdir -p /data/{7006,7007}/

cat > /data/7006/redis.conf <<EOF

port 7006

pidfile /data/7006/redis.pid

logfile "/data/7006/redis.log"

cluster-config-file nodes.conf

cluster-node-timeout 5000

appendonly yes

cat > /data/7007/redis.conf <<EOF

port 7007

pidfile /data/7007/redis.pid

logfile "/data/7007/redis.log"

cluster-config-file nodes.conf

cluster-node-timeout 5000

appendonly yes

redis-server /data/7006/redis.conf

启动节点：

redis-server /data/7007/redis.conf

添加主节点：

redis-trib.rb add-node 127.0.0.1:7006 127.0.0.1:7000

转移slot（重新分片）：

redis-trib.rb reshard 127.0.0.1:7000

查看主节点状态：（是否添加成功）

redis-cli -p 7000 cluster nodes | grep master

添加一个从节点：（指定要绑定到的主节点id：7006的id号）

redis-trib.rb add-node --slave --master-id 879b06d39c1384e7260b8bddcb5e91fac406a759 127.0.0.1:7007 127.0.0.1:7000

查看从节点状态：（是否添加成功）

redis-cli -p 7000 cluster nodes | grep slave

删除节点：

转移slot（重新分片，将要删除节点slot移动走；移动走后确认下该节点没有slot）

redis-trib.rb reshard 127.0.0.1:7000

删除节点：（指定节点IP+端口+ID号）

redis-trib.rb del-node 127.0.0.1:7006 2348689602d3e1d8d386880195a2f34skdfghos6

redis-trib.rb del-node 127.0.0.1:7007 1444c684003f9689602d3e1d8d386880195a2f34

设置redis最大内存

config set maxmemory 102400000

P187-MySQL+ redis-多API支持for Python

Python安装：

++++++++++++YUM方式+++++++++++++++

yum install -y python36

python3 -V

yum install -y python36-pip

pip3 install redis

pip3 install redis-py-cluster

++++++++++++源码方式+++++++++++++++

https://redis.io/clients

下载redis-py-master.zip

安装驱动：

unzip redis-py-master.zip

cd redis-py-master

python3 setup.py install

redis cluster的连接并操作（python2.7.2以上版本才支持 redis cluster，我们选择的是3.6）

https://github.com/Grokzen/redis-py-cluster

安装redis-cluser的客户端程序

cd redis-py-cluster-unstable

python3 setup.py install

+++++++++++++++++++++++++++++++++

通过python连接单实例redis：

开启redis单实例：

>>>r = redis.StrictRedis(host='127.0.0.1', port=6379, db=0,password='123456')

登录python并进行redis连接、读写测试：

测试读写：

>>>r.set('oldboy', 'oldguo')

>>>r.get('oldboy')

通过python连接redi ssentinel集群：

>>>from redis.sentinel import Sentinel

指定sentinel的地址和端口号：

>>> sentinel = Sentinel([('localhost', 26380)], socket_timeout=0.1)

测试，获取以下主库和从库的信息：

>>> sentinel.discover_master('mymaster')

>>> sentinel.discover_slaves('mymaster')

配置读写分离

写节点

>>> master = sentinel.master_for('mymaster', socket_timeout=0.1,password="123")

读节点

>>> slave = sentinel.slave_for('mymaster', socket_timeout=0.1,password="123")

读写分离测试 key

>>> master.set('oldboy', '123')

>>> slave.get('oldboy')

通过python连接redis cluster集群：（更换驱动退出重进）

python3

加载Cluster驱动：pip3 install redis-py-cluster

>>> from rediscluster import StrictRedisCluster

配置连接池：

>>>startup_nodes=[{"host":"127.0.0.1","port":"7000"},{"host":"127.0.0.1","port":"7001"},{"host":"127.0.0.1","port":"7002"}]

### Note: decode_responses must be set to True when used with python3

>>> rc = StrictRedisCluster(startup_nodes=startup_nodes,decode_responses=True)

>>> rc.set("foo", "bar")

True

>>> print(rc.get("foo"))

'bar'

缓存穿透：

访问一个不存在的key，缓存不起作用，请求会穿透到DB，流量大时DB会挂掉。

解决方案：

采用布隆过滤器，使用一个足够大的bitmap，用于存储可能访问的key，不存在的key 直接被过滤；

访问key未在DB查询到值，也将空值写进缓存，但可以设置较短过期时间。

缓存雪崩：

大量的key设置了相同的过期时间，导致在缓存在同一时刻全部失效，造成瞬时DB请求量大、压力骤增，引起雪崩。

解决方案：

可以给缓存设置过期时间时加上一个随机值时间，使得每个key的过期时间分布开来，不会集中在同一时刻失效。

缓存击穿：

一个存在的key，在缓存过期的一刻，同时有大量的请求，这些请求都会击穿到DB，造成瞬时DB请求量大、压力骤增。

解决方案：

在访问key之前，采用SETNX（set if not exists）来设置另一个短期key来锁住当前key 的访问，访问结束再删除该短期key。

P188-MongDB核心技术（运维篇）

远程工具设置：选项 –> 会话选项 –> 终端 –> 仿真 –> 终端中选择linux 即可

逻辑结构：

安装部署：

系统准备：

（1）redhat或centos6.2以上系统

（2）系统开发包完整

（3）ip地址和hosts文件解析正常

（4）iptables防火墙&SElinux关闭（root）

firewall-cmd --state

firewall-cmd --stop

（5）关闭大页内存机制：（THP）

vi /etc/rc.local（最后添加如下代码）

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then

echo never > /sys/kernel/mm/transparent_hugepage/enabled

if test -f /sys/kernel/mm/transparent_hugepage/defrag; then

echo never > /sys/kernel/mm/transparent_hugepage/defrag

刷新文件并查看结果：

sh /etc/rc.local

cat /sys/kernel/mm/transparent_hugepage/enabled

always madvise [never]

cat /sys/kernel/mm/transparent_hugepage/defrag

always madvise [never]

注：除centos外其他系统关闭参照官方文档：

https://docs.mongodb.com/manual/tutorial/transparent-huge-pages/

---------------

为什么要关闭？

提高best performance性能

You should disable THP on Linux machines to ensure best performance with MongoDB.

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

mongodb安装：

创建所需用户和组

useradd mongod

passwd mongod（输入两遍123）

创建mongodb所需目录结构

mkdir -p /mongodb/{conf,log,data}/

官网下载：

https://www.mongodb.com/download-center/community

解压软件并拷贝bin目录到指定位置：

cd /data

tar xf mongodb-linux-x86_64-rhel70-3.6.17.tgz

cp -r /data/mongodb-linux-x86_64-rhel70-3.6.17/bin/ /mongodb：

设置目录结构权限：

chown -R mongod:mongod /mongodb

设置私有用户环境变量：

su – mongod

vi .bash_profile

export PATH=/mongodb/bin:$PATH

source .bash_profile

启动mongodb：（指定参数启动）

mongod --dbpath=/mongodb/data --logpath=/mongodb/log/mongodb.log --port=27017 --logappend --fork

登录mongodb：

mongo

配置文件（YAML模式：不支持type，严格缩进）：

--系统日志有关

path: "/mongodb/log/mongodb.log" --日志位置

logAppend: true --日志以追加模式记录

dbPath: "/mongodb/data" --数据路径的位置

-- 进程控制

pidFilePath: <string> --pid文件的位置，一般不用配置，可以去掉这行，自动生成到data中

fork: true --后台守护进程

--网络配置有关

port: <port> -- 端口号,默认不配置端口号，是27017

bindIp: <ip> -- 监听地址

-- 安全验证有关配置

security:

authorization: enabled --是否打开用户名密码验证

------------------以下是复制集与分片集群有关----------------------

replication:

oplogSizeMB: <NUM>

replSetName: "<REPSETNAME>"

secondaryIndexPrefetch: "all"

archiveMovedChunks: <boolean>

clusterRole: <string>

---for mongos only

replication:

localPingThresholdMs: <int>

cat > /mongodb/conf/mongo.conf <<EOF

configDB: <string>

---

++++++++++++++++++++++

YAML例子：

path: "/mongodb/log/mongodb.log"

dbPath: "/mongodb/data/"

mongod -f /mongodb/conf/mongo.conf --shutdown

重启mongodb：（使用配置文件启动）

mongod -f /mongodb/conf/mongo.conf

mongodb 使用systemd管理：

cat > /etc/systemd/system/mongod.service <<EOF

After=network.target remote-fs.target nss-lookup.target

Description=mongodb

ExecStart=/mongodb/bin/mongod --config /mongodb/conf/mongo.conf

User=mongod

Type=forking

ExecReload=/bin/kill -s HUP $MAINPID

ExecStop=/mongodb/bin/mongod --config /mongodb/conf/mongo.conf --shutdown

PrivateTmp=true

重启mongodb：（使用systemctl启动）

systemctl restart mongod

systemctl stop mongod

systemctl start mongod

P189-MongDB-常用命令基本操作

mongodb 默认存在的库

test:登录时默认进入的库

管理MongoDB有关的系统库

admin库:系统预留库,MongoDB系统管理库

local库:本地预留库,存储关键日志

config库:MongoDB配置信息库

show databases/show dbs

show tables/show collections

use admin（自动创建，做修改后创建成功）

db/select database()

命令种类：（mongodb库表不需要提前创建）

rs 复制集有关(replication set)：

rs.[TAB][TAB]

rs.help()

sh 分片集群(sharding cluster)：

sh.[TAB][TAB]

sh.help()

P191-MongDB-对象操作

{ "dropped" : "test", "ok" : 1 }

集合的操作：

app> db.createCollection('a')

{ "ok" : 1 }

app> db.createCollection('b')

方法2：当插入一个文档的时候，一个集合就会自动创建。

db.test.insert({name:"zhangsan"})

db.stu.insert({id:101,name:"zhangsan",age:20,gender:"m"})

show tables;

db.stu.insert({id:102,name:"lisi"})

db.stu.insert({a:"b",c:"d"})

db.stu.insert({a:1,c:2})

db.stu.find().pretty()

文档操作：

数据录入：

for(i=0;i<10000;i++){db.log.insert({"uid":i,"name":"mongodb","age":6,"date":new

> DBQuery.shellBatchSize=50;

按照条件查询

> db.log.find({uid:999})

以标准的json格式显示数据

> db.log.find({uid:999}).pretty()

{

"_id" : ObjectId("5cc516e60d13144c89dead33"),

"uid" : 999,

"name" : "mongodb",

"age" : 6,

"date" : ISODate("2019-04-28T02:58:46.109Z")

app> db.log.totalSize() //集合中索引+数据压缩存储之后的大小

删除集合中所有记录

app> db.log.remove({})

查看集合存储信息

P193-MongDB-用户及权限管理

pwd: "<cleartext password>",

roles: [

{ role: "<role>",

db: "<database>" } | "<role>",

mongo -u oldboy -p 123 10.0.0.53/oldboy

用户管理例子

创建超级管理员：管理所有数据库（必须use admin再去创建）

roles: [ { role: "root", db: "admin" } ]

db.auth('root','root123')

)

验证用户（返回1成功）

开启密码验证：

vim /mongodb/conf/mongo.conf

security:

authorization: enabled

重启mongodb：

sysemctl restart mongod

登录验证：

mongo -uroot -proot123 admin

mongo -uroot -proot123 10.0.0.53:27017/admin

或者

mongo

db.auth('root','root123')

查看用户:

db.system.users.find().pretty()

roles: [ { role: "readWrite" , db: "oldboy" } ]

mongo -uroot -proot123 10.0.0.53:27017/admin

)

查询mongodb中的用户信息

db.system.users.find().pretty()

删除用户（root身份登录，use到验证库）

db.createUser({user: "app02",pwd: "app02",roles: [ { role: "readWrite" , db: "oldboy1" } ]})

mongo -uroot -proot123 10.0.0.53:27017/admin

use oldboy1

db.dropUser("app02")

用户管理注意事项：

P194-MongDB-复制集RS（ReplicationSet）

基本原理：

Replication Set配置过程详解：

一主两从：

一主一从一投票：（更建议使用，投票机没数据只投票）

环境准备：（4个实例）

28017、28018、28019、28020

多套目录：

su - mongod

mkdir -p /mongodb/28017/{conf,data,log}/

mkdir -p /mongodb/28018/{conf,data,log}/

mkdir -p /mongodb/28019/{conf,data,log}/

mkdir -p /mongodb/28020/{conf,data,log}/

多套配置文件：

配置文件内容

cat > /mongodb/28017/conf/mongod.conf <<EOF

path: /mongodb/28017/log/mongodb.log

dbPath: /mongodb/28017/data

\cp /mongodb/28017/conf/mongod.conf /mongodb/28018/conf/

\cp /mongodb/28017/conf/mongod.conf /mongodb/28019/conf/

\cp /mongodb/28017/conf/mongod.conf /mongodb/28020/conf/

sed 's#28017#28018#g' /mongodb/28018/conf/mongod.conf -i

sed 's#28017#28019#g' /mongodb/28019/conf/mongod.conf -i

sed 's#28017#28020#g' /mongodb/28020/conf/mongod.conf -i

启动多个实例备用

mongod -f /mongodb/28017/conf/mongod.conf

mongod -f /mongodb/28018/conf/mongod.conf

mongod -f /mongodb/28019/conf/mongod.conf

mongod -f /mongodb/28020/conf/mongod.conf

netstat -lnp|grep 280

配置普通复制集：

1主2从，从库普通从库

mongo --port 28017 admin

config = {_id: 'my_repl', members: [

{_id: 0, host: '192.168.3.122:28017'},

{_id: 1, host: '192.168.3.122:28018'},

{_id: 2, host: '192.168.3.122:28019'}]

mongo -port 28017 admin

config = {_id: 'my_repl', members: [

{_id: 0, host: '192.168.3.122:28017'},

{_id: 1, host: '192.168.3.122:28018'},

{_id: 2, host: '192.168.3.122:28019',"arbiterOnly":true}]

rs.isMaster(); // 查看当前是否是主节点

rs.initiate(config)

查看复制集状态：

rs.status(); //查看整体复制集状态

rs.conf()； //查看复制集配置信息

添加删除节点：

rs.remove("ip:port"); // 删除一个节点

rs.add("ip:port"); // 新增从节点

rs.addArb("ip:port"); // 新增仲裁节点

特殊从节点：

arbiter节点：主要负责选主过程中的投票，但是不存储任何数据，也不提供任何服务

hidden节点：隐藏节点，不参与选主，也不对外提供服务。

delay节点：延时节点，数据落后于主库一段时间，因为数据是延时的，也不应该提供服务或参与选主，所以通常会配合hidden（隐藏）

一般情况下会将delay+hidden一起配置使用

配置延时节点（一般延时节点也配置成hidden）：

[2]是conf中的id顺序排序数，有修改后需要数一下，会丢失id的

cfg=rs.conf()

cfg.members[2].priority=0

cfg.members[2].hidden=true

cfg.members[2].slaveDelay=120

rs.reconfig(cfg)

取消以上配置：

cfg=rs.conf()

cfg.members[2].priority=1

cfg.members[2].hidden=false

cfg.members[2].slaveDelay=0

admin> rs.freeze(300) //锁定从，使其不会转变成主库

freeze()和stepDown单位都是秒。

app> db.createCollection('a')

{ "ok" : 0, "errmsg" : "not master", "code" : 10107 }

监控主从延时：

admin> rs.printSlaveReplicationInfo()

source: 192.168.1.22:27017

syncedTo: Thu May 26 2016 10:28:56 GMT+0800 (CST)

0 secs (0 hrs) behind the primary

OPlog日志（备份恢复章节）

P198-MongDB-Sharding Cluster 分片集群

规划：

10个实例：38017-38026

（1）configserver:38018-38020

3台构成的复制集（1主两从，不支持arbiter）38018-38020（复制集名字configsvr）

（2）shard节点：

sh1：38021-23 （1主两从，其中一个节点为arbiter，复制集名字sh1）

sh2：38024-26 （1主两从，其中一个节点为arbiter，复制集名字sh2）

mkdir -p /mongodb/38021/{conf,log,data}/

mkdir -p /mongodb/38022/{conf,log,data}/

mkdir -p /mongodb/38023/{conf,log,data}/

mkdir -p /mongodb/38024/{conf,log,data}/

mkdir -p /mongodb/38025/{conf,log,data}/

mkdir -p /mongodb/38026/{conf,log,data}/

修改配置文件：

第一组复制集搭建：21-23（1主 1从 1Arb）

cat > /mongodb/38021/conf/mongodb.conf <<EOF

path: /mongodb/38021/log/mongodb.log

dbPath: /mongodb/38021/data

clusterRole: shardsvr

\cp /mongodb/38021/conf/mongodb.conf /mongodb/38022/conf/

\cp /mongodb/38021/conf/mongodb.conf /mongodb/38023/conf/

sed 's#38021#38022#g' /mongodb/38022/conf/mongodb.conf -i

sed 's#38021#38023#g' /mongodb/38023/conf/mongodb.conf -i

第二组节点：24-26(1主1从1Arb)

cat > /mongodb/38024/conf/mongodb.conf <<EOF

path: /mongodb/38024/log/mongodb.log

dbPath: /mongodb/38024/data

clusterRole: shardsvr

\cp /mongodb/38024/conf/mongodb.conf /mongodb/38025/conf/

\cp /mongodb/38024/conf/mongodb.conf /mongodb/38026/conf/

sed 's#38024#38025#g' /mongodb/38025/conf/mongodb.conf -i

sed 's#38024#38026#g' /mongodb/38026/conf/mongodb.conf -i

启动所有节点，并搭建复制集

mongod -f /mongodb/38021/conf/mongodb.conf

mongod -f /mongodb/38022/conf/mongodb.conf

mongod -f /mongodb/38023/conf/mongodb.conf

mongod -f /mongodb/38024/conf/mongodb.conf

mongod -f /mongodb/38025/conf/mongodb.conf

mongod -f /mongodb/38026/conf/mongodb.conf

ps -ef |grep mongod / netstat -lnt|grep 3802

mongo --port 38021

config = {_id: 'sh1', members: [

{_id: 0, host: '192.168.3.122:38021'},

{_id: 1, host: '192.168.3.122:38022'},

{_id: 2, host: '192.168.3.122:38023',"arbiterOnly":true}]

config = {_id: 'sh2', members: [

{_id: 0, host: '192.168.3.122:38024'},

{_id: 1, host: '192.168.3.122:38025'},

{_id: 2, host: '192.168.3.122:38026',"arbiterOnly":true}]

mkdir -p /mongodb/38018/{conf,log,data}/

mkdir -p /mongodb/38019/{conf,log,data}/

mkdir -p /mongodb/38020/{conf,log,data}/

修改配置文件：

cat > /mongodb/38018/conf/mongodb.conf <<EOF

path: /mongodb/38018/log/mongodb.conf

dbPath: /mongodb/38018/data

replSetName: configReplSet

port: 38018

replication:

oplogSizeMB: 2048

clusterRole: configsvr

\cp /mongodb/38018/conf/mongodb.conf /mongodb/38019/conf/

\cp /mongodb/38018/conf/mongodb.conf /mongodb/38020/conf/

sed 's#38018#38019#g' /mongodb/38019/conf/mongodb.conf -i

sed 's#38018#38020#g' /mongodb/38020/conf/mongodb.conf -i

启动节点，并配置复制集

mongod -f /mongodb/38018/conf/mongodb.conf

mongod -f /mongodb/38019/conf/mongodb.conf

mongod -f /mongodb/38020/conf/mongodb.conf

mongo --port 38018

config = {_id: 'configReplSet', members: [

{_id: 0, host: '192.168.3.122:38018'},

{_id: 1, host: '192.168.3.122:38019'},

{_id: 2, host: '192.168.3.122:38020'}]

注：configserver 可以是一个节点，官方建议复制集。configserver不能有arbiter。

rs.initiate(config)

新版本中，要求必须是复制集。

注：mongodb 3.4之后，虽然要求config server为replica set，但是不支持arbiter

mongos节点配置：

创建目录：

mkdir -p /mongodb/38017/conf /mongodb/38017/log

配置文件：

cat > /mongodb/38017/conf/mongos.conf <<EOF

path: /mongodb/38017/log/mongos.log

logAppend: true

port: 38017

configDB: configReplSet/192.168.3.122:38018,192.168.3.122:38019,192.168.3.122:38020

mongos -f /mongodb/38017/conf/mongos.conf

分片集群添加节点：

连接到其中一个mongos（192.168.3.122），做以下配置

（1）连接到mongs的admin数据库

# su - mongod

$ mongo 192.168.3.122:38017/admin

（2）添加分片

db.runCommand( { addshard : "sh1/192.168.3.122:38021,192.168.3.122:38022,192.168.3.122:38023",name:"shard1"} )

db.runCommand( { addshard : "sh2/192.168.3.122:38024,192.168.3.122:38025,192.168.3.122:38026",name:"shard2"} )

（3）列出分片

mongos> db.runCommand( { listshards : 1 } )

（4）整体状态查看

mongos> sh.status();

P201-MongDB-Sharding Cluster 分片使用

RANGE分片：

1、激活数据库分片功能

mongo --port 38017 admin

admin> ( { enablesharding : "数据库名称" } )

eg：

admin> db.runCommand( { enablesharding : "test" } )

2、指定分片键对集合分片

### 创建索引

use test

> db.vast.ensureIndex( { id: 1 } )

### 开启分片

> db.runCommand( { shardcollection : "test.vast",key : {id: 1} } )

3、集合分片验证

admin> use test

test> for(i=1;i<1000000;i++){ db.vast.insert({"id":i,"name":"shenzheng","age":70,"date":new Date()}); }

test> db.vast.stats()

Hash分片：（常用，好处：数据离散，分布均匀）

对oldboy库下的vast大表进行hash

创建哈希索引

（1）对于oldboy开启分片功能

mongo --port 38017 admin

admin> db.runCommand( { enablesharding : "oldboy" } )

（2）对于oldboy库下的vast表建立hash索引

oldboy> db.vast.ensureIndex( { id: "hashed" } )

（3）开启分片

admin > sh.shardCollection( "oldboy.vast", { id: "hashed" } )

（4）录入10w行数据测试

for(i=1;i<100000;i++){ db.vast.insert({"id":i,"name":"shenzheng","age":70,"date":new Date()}); }

P203-MongDB-Sharding Cluster-balancer 时间窗口

sh.getBalancerState()

（2）删除shard2节点(谨慎)

db.runCommand( { removeShard: "shard2" } )

注意：删除操作一定会立即触发blancer。

balancer操作

介绍：

设定balancer时间窗口：（避开业务繁忙期，及备份时间段）

https://docs.mongodb.com/manual/tutorial/manage-sharded-cluster-balancer/#schedule-the-balancing-window

// connect to mongos

use config

sh.setBalancerState( true )

db.settings.update({ _id : "balancer" }, { $set : { activeWindow : { start : "3:00", stop : "5:00" } } }, true )

sh.getBalancerWindow()

sh.status()

关于集合的balancer（了解下，一般不使用）

关闭某个集合的balance

sh.disableBalancing("students.grades")

打开某个集合的balancer

sh.enableBalancing("students.grades")

确定某个集合的balance是开启或者关闭

db.getSiblingDB("config").collections.findOne({_id : "students.grades"}).noBalance;

P204-MySQL-MongDB-异构平台迁移

备份恢复工具介绍：

（1）** mongoexport/mongoimport（逻辑迁移，格式：json，csv(excl表格)）

（2）***** mongodump/mongorestore（物理备份）

导出/导入工具mongoexport/mongoimport

--authenticationDatabase admin

单表备份至json格式：（默认就是json格式）

mongoexport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log -o /mongodb/log.json

单表备份至csv格式：（添加参数 --type=csv）

mongoexport -uroot -proot123 --port 27017 --authenticationDatabase admin -d test -c log --type=csv -f uid,name,age,date -o /mongodb/log.csv

恢复json格式表数据到log1：

mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log1 /mongodb/log.json

恢复csv格式的文件到log2：

（1）csv格式的文件头行，有列名字

mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log2 --type=csv --headerline --file /mongodb/log.csv

（2）csv格式的文件头行，没有列名字，需-f指定列名导入

mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log3 --type=csv -f id,name,age,date --file /mongodb/log.csv

异构平台迁移案例：

mysql -----> mongodb

world数据库下city表进行导出，导入到mongodb

导出mysql的city表数据

select * from world.city into outfile '/tmp/city1.csv' fields terminated by ',';

在mongodb中导入备份并指定列名

mongoimport -uroot -proot123 --port 27017 --authenticationDatabase admin -d world -c city --type=csv -f ID,Name,CountryCode,District,Population --file /tmp/city1.csv

use world

db.city.find({CountryCode:"CHN"});

world共100张表，全部迁移到mongodb：

select table_name ,group_concat(column_name) from columns where table_schema='world' group by table_name;

select * from world.city into outfile '/tmp/world_city.csv' fields terminated by ',';

select concat("select * from ",table_schema,".",table_name ," into outfile '/tmp/",table_schema,"_",table_name,".csv' fields terminated by ',';")

from information_schema.tables where table_schema ='world';

导入：

提示，使用infomation_schema.columns + information_schema.tables

mysql导出csv：

select * from test_info

into outfile '/tmp/test.csv'

fields terminated by ','　　　 ------字段间以,号分隔

optionally enclosed by '"'　　 ------字段用"号括起

escaped by '"' 　　　　　　 ------字段中使用的转义符为"

lines terminated by '\r\n';　　------行以\r\n结束

mysql导入csv：

load data infile '/tmp/test.csv'

into table test_info

fields terminated by ','

optionally enclosed by '"'

escaped by '"'

lines terminated by '\r\n';

mongodump和mongorestore

介绍

mongodump能够在Mongodb运行时进行备份，它的工作原理是对运行的Mongodb做查询，然后将所有查到的文档写入磁盘。

但是存在的问题时使用mongodump产生的备份不一定是数据库的实时快照，如果我们在备份时对数据库进行了写入操作，

则备份出来的文件可能不完全和Mongodb实时数据相等。另外在备份时可能会对其它客户端性能产生不利的影响。

-j, --numParallelCollections= number of collections to dump in parallel (4 by default)

--oplog 备份的同时备份oplog

全库备份：（默认全备）

mkdir /mongodb/backup

mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -o /mongodb/backup

备份world库：

mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -d world -o /mongodb/backup/

备份oldboy库下的log集合（表）：

mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy -c log -o /mongodb/backup/

压缩备份：

mongodump -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldguo -o /mongodb/backup/ --gzip

恢复world库：

mongorestore -uroot -proot123 --port 27017 --authenticationDatabase admin -d world1 /mongodb/backup/world

恢复oldguo库下的t1集合（表）：

mongorestore -uroot -proot123 --port 27017 --authenticationDatabase admin -d world -c t1 --gzip /mongodb/backup.bak/oldboy/log1.bson.gz

drop表示恢复的时候把之前的集合drop掉(危险)

mongorestore -uroot -proot123 --port 27017 --authenticationDatabase admin -d oldboy --drop /mongodb/backup/oldboy

P205-mongodump和mongorestore高级企业应用（--oplog）

注意：这是replica set（复制集）或者master/slave模式专用

--oplog

use oplog for taking a point-in-time snapshot

oplog介绍：

在replica set中oplog是一个定容集合（capped collection），它的默认大小是磁盘空间的5%（可以通过--oplogSizeMB参数修改）.

位于local库的db.oplog.rs，有兴趣可以看看里面到底有些什么内容。

其中记录的是整个mongod实例一段时间内数据库的所有变更（插入/更新/删除）操作。

当空间用完时新记录自动覆盖最老的记录。

其覆盖范围被称作oplog时间窗口。需要注意的是，因为oplog是一个定容集合，

所以时间窗口能覆盖的范围会因为你单位时间内的更新次数不同而变化。

查看oplog操作：

use local

db.oplog.rs.find().pretty()

"ts" : Timestamp(1553597844, 1),时间戳

rs.printReplicationInfo()

configured oplog size: 1561.5615234375MB <--集合大小

log length start to end: 423849secs (117.74hrs) <--预计窗口覆盖时间

oplog first event time: Wed Sep 09 2015 17:39:50 GMT+0800 (CST)

oplog last event time: Mon Sep 14 2015 15:23:59 GMT+0800 (CST)

now: Mon Sep 14 2015 16:37:30 GMT+0800 (CST)

oplog企业级应用：

（1）实现热备，在备份时使用--oplog选项

注：为了演示效果我们在备份过程，模拟数据插入

（2）准备测试数据

mongo --port 28018

for(var i = 1 ;i < 100; i++) {

db.foo.insert({a:i});

db.oplog.rs.find({"op":"i"}).pretty()

oplog 配合mongodump实现热备

mongodump --port 28018 --oplog -o /mongodb/backup

作用介绍：--oplog 会记录备份过程中的数据变化。会以oplog.bson保存下来

恢复

mongorestore --port 28018 --oplogReplay /mongodb/backup

oplog高级应用：

背景：每天0点全备，oplog恢复窗口为48小时

某天，上午10点world.city 业务表被误删除。

4、截取全备之后到world.city误删除时间点的oplog，并恢复到测试库

for(var i = 1 ;i < 20; i++) {

db.ci.insert({a: i});