postgresql连接池 pgpool vs pgbouncer

长话短说: 如果高TPS要做连接池, pgbouncer.

pgpool可以做failover和连接池, 但是有内存泄露的问题.

pgbouncer: 公司单实例大概在2000TPS, 高峰期内核态:用户态:剩余CPU大概在 2:1:2, 大部分的操作在fork进程上消耗, 用了连接池之后, 内核态占用能直接减小到可以忽略.真是好东西.

教程很容易搜索到, 就不列出来了.

这篇这么短, 再介绍点儿别的吧.

如果想找一个慢日志分析工具, 可以尝试pgbadger

主从同步流复制模式, 如果从库hung, 会导致主库hung.

高并发, 如果CPU不够用了, 分库前可以尝试先加CPU颗数试试.

redis刷磁盘可能会导致瞬时无法连接

业务日志监控中报告, 每天会有大约250次连接redis失败.

通过strace追踪发现.故障的时间点时写磁盘时间超过了10s.一般在10-15s之间. redis第二次重试使用的是10s.

这个实例所有的操作都是INCR, fdatasync block.

strace -Ttt -f -p 11302 -T -e  trace=fdatasync

11309 10:21:31.153900 fdatasync(116)    = 0 <0.034295>

11309 10:21:32.078747 fdatasync(116)    = 0 <7.592478>

11309 10:21:39.774959 fdatasync(116)    = 0 <10.098802>

11309 10:21:49.990623 fdatasync(116)    = 0 <2.026147>

11309 10:21:52.129676 fdatasync(116)    = 0 <0.002802>

治标:

超时时间改为15s.

治本:

正在用watchdog抓一下超过5s的堆栈.

堆栈:

[11302 | signal handler] (1499754857)

— WATCHDOG TIMER EXPIRED —

/usr/local/bin/redis-server-2.8 10.160.86.216:6699(logStackTrace+0x3e)[0x445ace]

/lib64/libpthread.so.0(write+0x2d)[0x7f19ef3b06fd]

/lib64/libpthread.so.0(+0xf710)[0x7f19ef3b1710]

/lib64/libpthread.so.0(write+0x2d)[0x7f19ef3b06fd]

/usr/local/bin/redis-server-2.8 10.160.86.216:6699(flushAppendOnlyFile+0x4e)[0x44116e]

/usr/local/bin/redis-server-2.8 10.160.86.216:6699(serverCron+0x3b7)[0x41bb17]

/usr/local/bin/redis-server-2.8 10.160.86.216:6699(aeProcessEvents+0x1e9)[0x416b69]

/usr/local/bin/redis-server-2.8 10.160.86.216:6699(aeMain+0x2b)[0x416deb]

/usr/local/bin/redis-server-2.8 10.160.86.216:6699(main+0x31d)[0x41e49d]

/lib64/libc.so.6(__libc_start_main+0xfd)[0x7f19ef02cd5d]

/usr/local/bin/redis-server-2.8 10.160.86.216:6699[0x415bd9]

[11302 | signal handler] (1499754857) ——–

fdatasync会在某个时间点超过10s.

看来因为写磁盘堵塞了, 把机械硬盘换成了SSD, 解决了.

direct reclaim内存回收导致postgresql CPU占用激增

收到报警, Postgresql数据库服务器出现瞬时CPU占用高峰, load/cpu能从0.5瞬间飙升到5以上,每次持续15s-2mins波峰比较严重时候,会导致PHP进程数占满,进而影响API接口的新入请求.

由于每次发生的时间间隔大概2个小时, 但是没有规律. zabbix监控对这种突发最好只做到在1.5分钟后通知, 失去了事故现场.

由于报警延迟, 只能一边开着terminal实时观察, 一边在下次故障发生前排查其他的可能性.

怀疑是最近变更. 近期postgresql刚安装了jieba分词插件. 有可能会导致这个问题.

怀疑是由5%慢请求拖累整个数据库,造成服务器性能不够.这个根据经验是最可能的.

怀疑定时任务导致瞬时大量请求.

怀疑是因为数据量增大后内存热数据不够导致.

怀疑是vacuum或其他后台进程导致.

排查的过程中, 突然抓到事故现场. 立即查看内存和CPU相关指标.

1. _spin_unlock_irqrestore 占用89%CPU时间.

2. 单个sql请求消耗内存大概是25M.

grep Pss /proc/`ps auxf | grep hbforce_v4| grep -ir select | tail -n 1  | awk ‘{print $2}’`/smaps |awk ‘{total+=$2}; END {print total}’

3. 内存现状,显示还有大量的cached内存, 系统故障时候伴随着内存也扫描和回收.(低于/proc/zoneinfolow的值时触发回收,回收到high停止)

用到的知识点:

在系统空闲内存低于 watermark[low]时,开始启动内核线程kswapd进行内存回收.直到该zone的空闲内存数量达到watermark[high]后停止回收。如果上层申请内存的速度太快,导致空闲内存降至watermark[min]后,内核就会进行direct reclaim(直接回收),即直接在应用程序的进程上下文中进行回收,再用回收上来的空闲页满足内存申请,因此实际会阻塞应用程序.当前情况下.min-low-high之间只有15320KB的内存差.

而一个postgresql请求中需要分配的内存一般会在25MB左右.会导致直接触发强制回收.

下面是定位出来的触发逻辑:

1.内存不足时,单次申请内存过大,导致直接强制回收.触发compat_zone, 导致自旋锁,这个锁的特点是等待锁的过程中不休眠,而是占着CPU空转.spin_unlock_irqrestore — 禁止内核抢占并屏蔽中断,事先保存中断屏蔽位并事后恢复原状.

2.CPU空转导致新进入的任务堆积,load升高,并占用连接.

3.PHP不能处理导致进程占用,PHP-FPM进程达到最大值时候阻塞新请求进入.

4.API接口超时,导致前端调用失败,显示接口等待或失败.

5.内存回收完毕后释放锁,然后恢复正常.

总结原因是单次内存申请超过了min-low-high间隔,导致强制回收,触发了自旋锁.

调整内核参数解决:

vm.min_free_kbytes = 1081344

vm.vfs_cache_pressure = 200

当时的现场的数据:

[root@DATABASE ~]# cat /proc/zoneinfo

Node 0, zone   Normal

  pages free     20198

        min      15319

        low      19148

        high     22978

[root@DATABASE ~]# free -m

             total       used       free     shared    buffers     cached

Mem:         32109      31778        330          0        256      30215

-/+ buffers/cache:       1307      30802

Swap:        0        0      0

[root@DATABASE ~]# cat /proc/meminfo

MemTotal:       32879936 kB

MemFree:         2506208 kB

Buffers:          892660 kB

Cached:         28122840 kB

SwapCached:            0 kB

Active:         16954896 kB

Inactive:       12560144 kB

Active(anon):    3459900 kB

Inactive(anon):   310148 kB

Active(file):   13494996 kB

Inactive(file): 12249996 kB

Unevictable:           0 kB

Mlocked:               0 kB

SwapTotal:             0 kB

SwapFree:              0 kB

Dirty:              7004 kB

Writeback:             0 kB

AnonPages:        258360 kB

Mapped:          3255272 kB

Shmem:           3271380 kB

Slab:             471896 kB

SReclaimable:     419744 kB

SUnreclaim:        52152 kB

KernelStack:        3680 kB

PageTables:        38140 kB

NFS_Unstable:          0 kB

Bounce:                0 kB

WritebackTmp:          0 kB

CommitLimit:    16439968 kB

Committed_AS:    8292496 kB

VmallocTotal:   34359738367 kB

VmallocUsed:       65256 kB

VmallocChunk:   34359667396 kB

HardwareCorrupted:     0 kB

AnonHugePages:         0 kB

HugePages_Total:       0

HugePages_Free:        0

HugePages_Rsvd:        0

HugePages_Surp:        0

Hugepagesize:       2048 kB

DirectMap4k:        6016 kB

DirectMap2M:     2091008 kB

DirectMap1G:    31457280 kB

[root@DATABASE ~]# numactl –hardware

available: 1 nodes (0)

node 0 cpus: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

node 0 size: 32767 MB

node 0 free: 1141 MB

node distances:

node   0

  0:  10

[root@DATABASE ~]# cat /proc/zoneinfo

Node 0, zone   Normal

  pages free     20198

        min      15319

        low      19148

        high     22978

        scanned  0

        spanned  7602176

        present  7498240

[root@DATABASE ~]# sar -B 1

Linux 2.6.32-279.el6.x86_64 (DATABASE) 06/21/2017 _x86_64_ (16 CPU)

12:51:57 AM  pgpgin/s pgpgout/s   fault/s  majflt/s  pgfree/s pgscank/s pgscand/s pgsteal/s    %vmeff

12:51:58 AM    129.17    612.50  13683.33      0.00  23310.42      0.00   7663.54   7704.17    100.53

12:51:59 AM   1872.34     12.77  17207.45      0.00  29784.04      0.00   2325.53   2325.53    100.00

12:52:00 AM    148.84      0.00  19734.30     11.05  24716.86      0.00   4424.42   4424.42    100.00

12:52:01 AM     84.21      0.00   2047.37     21.05  18484.21      0.00   4347.37   4178.95     96.13

12:52:02 AM   1025.00      0.00  18497.92      2.08  22652.08      0.00   5256.25   5289.58    100.63

12:52:03 AM   2235.79    450.53  52714.74      4.21  87222.11      0.00  24298.95  24297.89    100.00

12:52:05 AM     13.45      6.72  13763.87      0.00  22565.55      0.00   1813.45   1813.45    100.00

12:52:06 AM      8.00      0.00  14222.00      0.00  16852.00      0.00   5255.00   5199.00     98.93

12:52:07 AM  11734.69    114.29 190776.53      1.02 223285.71      0.00  43768.37  43814.29    100.10

12:52:08 AM  58848.98   1559.18 226263.27      0.00 233448.98      0.00   1057.14   1057.14    100.00

12:52:09 AM 108673.47     24.49 140714.29      0.00 142710.20      0.00      0.00      0.00      0.00

12:52:10 AM  65834.34     16.16  41429.29      0.00  42891.92      0.00      0.00      0.00      0.00

12:52:11 AM  59104.17      0.00  59959.38      0.00  60518.75      0.00      0.00      0.00      0.00

12:52:12 AM  56040.40    137.37  46878.79      0.00  40731.31      0.00      0.00      0.00      0.00

对应的是

procs ———–memory———- —swap– —–io—- –system– —–cpu—–

82  1      0 581128 587036 30372132    0    0 17092   616 63036 2132  1 99  0  0  0

73  1      0 687572 586204 30274540    0    0  1476    24 18942  581  0 100  0  0  0

83  0      0 700872 585844 30250296    0    0   432   588 16374  427  0 100  0  0  0

51  1      0 715412 585816 30238628    0    0  1752    12 16660  508  1 99  0  0  0

87  1      0 726340 585784 30229336    0    0   212     0 17575  480  1 99  0  0  0

88  1      0 742876 585760 30196484    0    0   116     0 25373  712  1 99  0  0  0

80  1      0 753324 585620 30180436    0    0  1328   428 17074  504  1 99  0  0  0

90  0      0 828596 585088 30092832    0    0  1736     8 19115 1378  1 99  0  0  0

86  0      0 835460 585000 30088000    0    0    16     0 17247  461  0 100  0  0  0

82  0      0 888980 584364 29995256    0    0    24     8 18090  574  1 99  0  0  0

 1  1      0 1012756 583688 29915708    0    0 20796   112 19206 4382  6 73 20  1  0

 1  1      0 1018156 583952 29982312    0    0 68868  1520 8720 5912  4  7 85  3  0

 0  1      0 962080 584016 30083852    0    0 101396    24 6240 4274  3  4 89  4  0

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

 1  1      0 906804 584032 30144308    0    0 61180    16 4026 3513  1  2 93  4  0

这些信息没有在默认的监控中体现出来, 后续又细化zabbix内存监控.

细化的模板在这里

参考文章:

http://blog.2baxb.me/archives/1065

https://engineering.linkedin.com/performance/optimizing-linux-memory-management-low-latency-high-throughput-databases

https://www.kernel.org/doc/gorman/html/understand/understand013.html

http://os.51cto.com/art/201310/412638.htm

http://www.cs.fsu.edu/~baker/devices/lxr/source/2.6.11.8/linux/mm/mempool.c

http://blog.csdn.net/longxibendi/article/details/44625703

https://www.google.com/#newwindow=1&hl=en&q=kswapd+spin_unlock_irqrestore+cpu

https://www.google.com/#q=%22compact_zone%22+spin_unlock_irqrestore

利用keepalived做redis的双机热备

应用场景:
服务压力不大,但是对可靠性要求极其高
特性:
至少需要两台服务器,其中一台为master始终提供服务,另外一台只有在主服务器挂掉的才提供服务。
优点:数据同步非常简单,不像负载均衡对数据一致性要求非常高
缺点:服务器有点浪费,始终有一台处于空闲状态

说明:
操作系统:CentOS 6.3 64位
redis服务器:172.27.4.38、172.27.4.39

架构规划:
需要在Master与Slave上均安装Keepalived和Redis,并且redis开启本地化策略。
master:172.27.4.38
slave:172.27.4.39
Virtural IP Address (VIP): 172.27.4.250

当 Master 与 Slave 均运作正常时, Master负责服务,Slave负责Standby;
当 Master 挂掉,Slave 正常时, Slave接管服务,同时关闭主从复制功能;
当 Master 恢复正常,则从Slave同步数据,同步数据之后关闭主从复制功能,恢复Master身份。与此同时Slave等待Master同步数据完成之后,恢复Slave身份。

redis安装步骤:
wget http://download.redis.io/releases/redis-2.4.15.tar.gz
tar zxvf redis-2.4.15.tar.gz
mkdir -p /usr/local/redis2.4.15/etc
mkdir -p /usr/local/redis2.4.15/data
mkdir -p /usr/local/redis2.4.15/logs

mv redis.conf /usr/local/redis2.4.15/etc
cd redis-2.4.15
make PREFIX=/usr/local/redis2.4.15 install
cd src/
make install

安装完毕
tree /usr/local/redis2.4.15/
/usr/local/redis2.4.15/
├── bin
│   ├── redis-benchmark
│   ├── redis-check-aof
│   ├── redis-check-dump
│   ├── redis-cli
│   └── redis-server
├── etc
└── redis.conf
2 directories, 6 files

修改redis配置文件

vim /usr/local/redis2.4.15/redis.conf
daemonize yes #当为yes时redis回后台运行,关闭命令为redis-cli shutdown
logfile /usr/local/redis2.4.15/logs/redis.log #指定日志文件目录
dbfilename dump.rdb #指定数据文件名称
dir /usr/local/redis2.4.15/data/ #指定数据文件文件夹未知

开启redis的命令为:
/usr/local/redis2.4.15/bin/redis-server /usr/local/redis2.4.15/etc/redis.conf
至此redis安装完毕

然后用客户端测试一下是否启动成功。

redis-cli
redis> set foo bar
OK
redis> get foo
“bar”

Keepalived安装
官方地址:http://www.keepalived.org/download.html 大家可以到这里下载最新版本。
环境配置:安装make 和 gcc openssl openssl-devel popt ipvsadm 等
yum -y install gcc make openssl openssl-devel wget kernel-devel
cd /usr/local/src/
wget http://www.keepalived.org/software/keepalived-1.2.13.tar.gz
tar zxvf keepalived-1.2.13.tar.gz
cd keepalived-1.2.13
预编译如果指定目录的话
./configure –prefix=/usr/local/keepalived
也可以直接使用默认
./configure
预编译后出现:
Keepalived configuration
————————
Keepalived version : 1.2.13
Compiler : gcc
Compiler flags : -g -O2
Extra Lib : -lssl -lcrypto -lcrypt
Use IPVS Framework : Yes
IPVS sync daemon support : Yes
IPVS use libnl : No
fwmark socket support : Yes
Use VRRP Framework : Yes
Use VRRP VMAC : Yes
SNMP support : No
SHA1 support : No
Use Debug flags : No

make && make install

整理管理文件:
ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/
ln -s /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
至此keepalived安装完毕
注册为服务并设置开机启动
chkconfig –add keepalived
chkconfig keepalived on
service keepalived start

ps -ef | grep keepalived
root 1332 1908 0 09:32 pts/0 00:00:00 grep keepalived
root 1977 1 0 Nov20 ? 00:00:02 keepalived -D
root 1978 1977 0 Nov20 ? 00:00:02 keepalived -D
root 1979 1977 0 Nov20 ? 00:00:17 keepalived -D
redis服务启动后会有3个进程。

首先,在Master上创建如下配置文件:
mkdir -p /etc/keepalived/scripts/
vim /etc/keepalived/keepalived.conf

vrrp_script chk_redis {
script “/etc/keepalived/scripts/redis_check.sh” ###监控脚本
interval 2 ###监控时间
}
vrrp_instance VI_1 {
state MASTER ###设置为MASTER
interface eth0 ###监控网卡
virtual_router_id 51
priority 101 ###权重值
authentication {
auth_type PASS ###加密
auth_pass redis ###密码
}
track_script {
chk_redis ###执行上面定义的chk_redis
}
virtual_ipaddress {
172.27.4.250 ###VIP
}
notify_master /etc/keepalived/scripts/redis_master.sh
notify_backup /etc/keepalived/scripts/redis_backup.sh
notify_fault /etc/keepalived/scripts/redis_fault.sh
notify_stop /etc/keepalived/scripts/redis_stop.sh

然后,在Slave上创建如下配置文件:

mkdir -p /etc/keepalived/scripts/
vim /etc/keepalived/keepalived.conf

vrrp_script chk_redis {
script “/etc/keepalived/scripts/redis_check.sh” ###监控脚本
interval 2 ###监控时间
}
vrrp_instance VI_1 {
state BACKUP ###设置为BACKUP
interface eth0 ###监控网卡
virtual_router_id 51
priority 100 ###比MASTRE权重值低
authentication {
auth_type PASS
auth_pass redis ###密码与MASTRE相同
}
track_script {
chk_redis ###执行上面定义的chk_redis
}
virtual_ipaddress {
172.27.4.250 ###VIP
}
notify_master /etc/keepalived/scripts/redis_master.sh
notify_backup /etc/keepalived/scripts/redis_backup.sh
notify_fault /etc/keepalived/scripts/redis_fault.sh
notify_stop /etc/keepalived/scripts/redis_stop.sh
}

在Master和Slave上创建监控Redis的脚本:
vim /etc/keepalived/scripts/redis_check.sh
#!/bin/bash

ALIVE=`/opt/redis/bin/redis-cli PING`
if [ “$ALIVE” == “PONG” ]; then
echo $ALIVE
exit 0
else
echo $ALIVE
exit 1
fi

编写以下负责运作的关键脚本:
notify_master /etc/keepalived/scripts/redis_master.sh
notify_backup /etc/keepalived/scripts/redis_backup.sh
notify_fault /etc/keepalived/scripts/redis_fault.sh
notify_stop /etc/keepalived/scripts/redis_stop.sh

因为Keepalived在转换状态时会依照状态来呼叫:
当进入Master状态时会呼叫notify_master
当进入Backup状态时会呼叫notify_backup
当发现异常情况时进入Fault状态呼叫notify_fault
当Keepalived程序终止时则呼叫notify_stop

首先,在Redis Master上创建notity_master与notify_backup脚本:

vim /etc/keepalived/scripts/redis_master.sh
#!/bin/bash

REDISCLI=”/usr/local/redis2.8.9/bin/redis-cli”
LOGFILE=”/var/log/keepalived-redis-state.log”

echo “[master]” >> $LOGFILE
date >> $LOGFILE
echo “Being master….” >> $LOGFILE 2>&1

echo “Run SLAVEOF cmd …” >> $LOGFILE
$REDISCLI SLAVEOF 172.27.4.39 6379 >> $LOGFILE 2>&1
sleep 10
#延迟10秒以后待数据同步完成后再取消同步状态

echo “Run SLAVEOF NO ONE cmd …” >> $LOGFILE
$REDISCLI SLAVEOF NO ONE >> $LOGFILE 2>&1

vim /etc/keepalived/scripts/redis_backup.sh

#!/bin/bash

REDISCLI=”/usr/local/redis2.8.9/bin/redis-cli”
LOGFILE=”/var/log/keepalived-redis-state.log”

echo “[backup]” >> $LOGFILE
date >> $LOGFILE
echo “Being slave….” >> $LOGFILE 2>&1

sleep 15
#延迟15秒待数据被对方同步完成之后再切换主从角色
echo “Run SLAVEOF cmd …” >> $LOGFILE
$REDISCLI SLAVEOF 172.27.4.39 6379 >> $LOGFILE 2>&1

接着,在Redis Slave上创建notity_master与notify_backup脚本:

vim /etc/keepalived/scripts/redis_master.sh

#!/bin/bash

REDISCLI=”/usr/local/redis2.8.9/bin/redis-cli”
LOGFILE=”/var/log/keepalived-redis-state.log”

echo “[master]” >> $LOGFILE
date >> $LOGFILE
echo “Being master….” >> $LOGFILE 2>&1

echo “Run SLAVEOF cmd …” >> $LOGFILE
$REDISCLI SLAVEOF 172.27.4.38 6379 >> $LOGFILE 2>&1
sleep 10
#延迟10秒以后待数据同步完成后再取消同步状态

echo “Run SLAVEOF NO ONE cmd …” >> $LOGFILE
$REDISCLI SLAVEOF NO ONE >> $LOGFILE 2>&1

vim /etc/keepalived/scripts/redis_backup.sh

#!/bin/bash

REDISCLI=”/usr/local/redis2.8.9/bin/redis-cli”
LOGFILE=”/var/log/keepalived-redis-state.log”

echo “[backup]” >> $LOGFILE
date >> $LOGFILE
echo “Being slave….” >> $LOGFILE 2>&1

sleep 15
#延迟15秒待数据被对方同步完成之后再切换主从角色
echo “Run SLAVEOF cmd …” >> $LOGFILE
$REDISCLI SLAVEOF 172.27.4.38 6379 >> $LOGFILE 2>&1

然后在Master与Slave创建如下相同的脚本:

vim /etc/keepalived/scripts/redis_fault.sh
#!/bin/bash

LOGFILE=/var/log/keepalived-redis-state.log

echo “[fault]” >> $LOGFILE
date >> $LOGFILE

vim /etc/keepalived/scripts/redis_stop.sh
#!/bin/bash

LOGFILE=/var/log/keepalived-redis-state.log

echo “[stop]” >> $LOGFILE
date >> $LOGFILE

给脚本都加上可执行权限:
chmod +x /etc/keepalived/scripts/*.sh

脚本创建完成以后,我们开始按照如下流程进行测试:
1.启动Master上的Redis
/etc/init.d/redis restart

2.启动Slave上的Redis
/etc/init.d/redis restart

3.启动Master上的Keepalived
/etc/init.d/keepalived restart

4.启动Slave上的Keepalived
/etc/init.d/keepalived restart

5.尝试通过VIP连接Redis:
redis-cli -h 172.27.4.250 INFO

连接成功,Slave也连接上来了。
role:master
slave0:172.27.4.39,6379,online

6.尝试插入一些数据:
redis-cli -h 172.27.4.250 SET Hello Redis
OK

从VIP读取数据
redis-cli -h 172.27.4.250GET Hello
“Redis”

从Master读取数据
redis-cli -h 172.27.4.38 GET Hello
“Redis”

从Slave读取数据
redis-cli -h 172.27.4.39 GET Hello
“Redis”

下面,模拟故障产生:
将Master上的Redis进程杀死:
killall -9 redis-server
或者redis-cli shutdown

查看Master上的Keepalived日志
tail -f /var/log/keepalived-redis-state.log
[fault]
Thu Sep 27 08:29:01 CST 2012

同时Slave上的日志显示:
tailf /var/log/keepalived-redis-state.log
[master]
Fri Sep 28 14:14:09 CST 2012
Being master….
Run SLAVEOF cmd …
OK
Run SLAVEOF NO ONE cmd …
OK

然后我们可以发现,Slave已经接管服务,并且担任Master的角色了。
redis-cli -h 172.27.4.250 INFO
redis-cli -h 172.27.4.39 INFO
role:master

然后我们恢复Master的Redis进程
/etc/init.d/redis start

查看Master上的Keepalived日志
tailf /var/log/keepalived-redis-state.log
[master]
Thu Sep 27 08:31:33 CST 2012
Being master….
Run SLAVEOF cmd …
OK
Run SLAVEOF NO ONE cmd …
OK

同时Slave上的日志显示:
tailf /var/log/keepalived-redis-state.log
[backup]
Fri Sep 28 14:16:37 CST 2012
Being slave….
Run SLAVEOF cmd …
OK

可以发现目前的Master已经再次恢复了Master的角色,故障切换以及自动恢复都成功了

Mysql删除某字段中有重复数据的行

有一次业务需求变更,需要将web_user_mobile_time_limit中的mobile字段值设置为唯一。
原有表结构如下:
mysql> desc web_user_mobile_time_limit;
+———-+————-+——+—–+——————-+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+——————-+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| mobile | varchar(20) | YES | | NULL | |
| dateTime | timestamp | YES | | CURRENT_TIMESTAMP | |
+———-+————-+——+—–+——————-+—————-+

如果mobile字段没有重复数据,可以执行下一行语句修改表结构:
Alter table web_user_mobile_time_limit add unique(mobile);

由于现有表的mobile字段的数据已经有一些是重复的。
mysql> select * from web_user_mobile_time_limit;
+—-+————-+———————+
| id | mobile | dateTime |
+—-+————-+———————+
| 1 | 13800138000 | 2014-09-23 20:18:17 |
| 2 | 13800138000 | 2014-09-23 20:18:57 |
| 3 | 13800138001 | 2014-09-23 20:19:04 |
| 4 | 13800138000 | 2014-09-23 20:19:18 |
| 5 | 13800138001 | 2014-09-23 20:19:22 |
| 6 | 13800138002 | 2014-09-23 20:19:25 |
| 7 | 13800138001 | 2014-09-23 20:19:29 |
| 8 | 13800138002 | 2014-09-23 20:19:34 |
| 9 | 13800138003 | 2014-09-23 20:19:39 |
| 10 | 13800138004 | 2014-09-23 20:19:43 |
| 11 | 13800138004 | 2014-09-23 20:19:46 |
+—-+————-+———————+
11 rows in set

在修改表结构的过会提示如下错误:
mysql> Alter table web_user_mobile_time_limit add unique(mobile);
ERROR 1062 : Duplicate entry ‘13800138000’ for key ‘mobile’

业务部门的需求是直接删除掉重复数据中更新时间较晚的数据,执行方法是这样:
delete from a using web_user_mobile_time_limit as a,web_user_mobile_time_limit as b where a.id>b.id and a.mobile = b.mobile;
如果想保留较旧的数据则执行:
delete from a using web_user_mobile_time_limit as a,web_user_mobile_time_limit as b where a.id<b.id and a.mobile = b.mobile;

再次查看结果如下:
mysql> select * from web_user_mobile_time_limit;
+—-+————-+———————+
| id | mobile | dateTime |
+—-+————-+———————+
| 1 | 13800138000 | 2014-09-23 20:18:17 |
| 3 | 13800138001 | 2014-09-23 20:19:04 |
| 6 | 13800138002 | 2014-09-23 20:19:25 |
| 9 | 13800138003 | 2014-09-23 20:19:39 |
| 10 | 13800138004 | 2014-09-23 20:19:43 |
+—-+————-+———————+
5 rows in set
至此,重复数据已经删除,现在修改表结构就不会报错了;

mysql> Alter table web_user_mobile_time_limit add unique(mobile);
mysql> desc web_user_mobile_time_limit;
+———-+————-+——+—–+——————-+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+——————-+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| mobile | varchar(20) | YES | UNI | NULL | |
| dateTime | timestamp | YES | | CURRENT_TIMESTAMP | |
+———-+————-+——+—–+——————-+—————-+
3 rows in set

MySQL服务器合并

不中断web服务的前提下合并多个MySQL服务器到单独的一台服务器上

 

目标描述:
公司内部生产环境中有多个MySQL服务器系统配置过剩、管理复杂,计划将三台MySQL数据库服务器A1(A2从)、B1(B2从)、C1(C2从)合并到配置稍高Mysql服务器D1(D2从)上。前提要求是不中断web服务,不能丢失数据。
由于A1、B1、C1的数据库database_name并不重复,数据引擎为INNODB,合并的难度就小了很多。
A1中有DBA1、DBA2、DBA3,IP 172.16.1.101
B1中有DBB1,IP 172.16.1.102
C1中有DBC1,IP 172.16.1.103
D1 IP 172.16.1.104

合并思路:
1、在D1上创建账号并赋予权限;A1、B1、C1创建同步账号(曾创建)、开启二进制文件(曾开启)
2、设置D1为A1的从库:A1数据,导入D1、设置同步,切换web代码中的数据库连接文件。
3、导出B1数据,导入到D1中,重设D1为B1的从库,同步完成后切换web代码中数据库连接文件。
4、同理导出C1数据,导入到D1中,重设D1为C1的从库,同步完成后切换web代码中数据库连接文件。
5、重设D1为主服务器,创建同步账号,导出D1数据库并导入到D2,设置主从和备份。

mysqldump关键参数:
–master-data=2:记录数据导出时的数据库使用的日志文件名和position
–single-transaction:导出时不锁表
–databases/–B:同时导出多个数据库
具体释义请man mysqldump

实施过程(本例数据库名、IP、权限等已替换为非无意义单词,仅展示原理和步骤):

1、在D1服务器安装同版本的MySQL数据库,并修改MySQL的配置文件/etc/my.cnf,本例中直接在A1复制/etc/my.cnf,并修改server-id=value中value不和A1、B1、C1冲突
2、确认A1、B1、C1中存在同步用的账号,并已赋予同步权限,以A1为例:
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| DBA1 |
| DBA2 |
| DBA13 |
| mysql |
| performance_schema |
+——————–+
6 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+———–+————–+
| user | host |
+———–+————–+
| root | 127.0.0.1 |
| sync_user | 172.16.1.% |
| DBAU1 | 172.16.1.1 |
| root | localhost |
+———–+————–+
4 rows in set (0.00 sec)
mysql> show grants for ‘sync_user’@’172.16.1.%’;
+——————————————————————————————————————————-+
| Grants for sync_user@172.16.1.% |
+——————————————————————————————————————————-+
| GRANT REPLICATION SLAVE ON *.* TO ‘sync_user’@’172.16.1.%’ IDENTIFIED BY PASSWORD ‘*****************************************’ |
+——————————————————————————————————————————-+
1 row in set (0.00 sec)

GRANT REPLICATION SLAVE [ … ] 说明已经赋予sync_user账号在172.16.1.%的同步权限。

3、在D1服务器上创建DBAU1账号和权限与A1服务器的权限相同。
在A1服务器执行下列语句查看DBAU1的账号权限:
mysql> show grants for ‘DBAU1’@’172.16.1.1’;
+—————————————————————————————————————–+
| Grants for DBAU1@172.16.1.19 |
+—————————————————————————————————————–+
| GRANT USAGE ON *.* TO ‘DBAU1’@’172.16.1.19’ IDENTIFIED BY PASSWORD ‘*****************************************’ |
| GRANT ALL PRIVILEGES ON `DBA1`.`hl_city_exchange_record` TO ‘DBAU1’@’172.16.1.1’ WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `DBA2`.`hl_community_detail` TO ‘DBAU1’@’172.16.1.1’ WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `DBA3`.`hl_city_price_trend` TO ‘DBAU1’@’172.16.1.1’ WITH GRANT OPTION |
+—————————————————————————————————————–+
4 rows in set (0.00 sec)
在D1中执行如下命令赋权限:
mysql>GRANT ALL PRIVILEGES ON `DBA1`.`hl_city_exchange_record` TO ‘DBAU1’@’172.16.1.1’ IDENTIFIED BY ‘YOUR PASSWORD’ WITH GRANT OPTION;
mysql>GRANT ALL PRIVILEGES ON `DBA2`.`hl_community_detail` TO ‘DBAU1’@’172.16.1.1’ WITH GRANT OPTION;
mysql>GRANT ALL PRIVILEGES ON `DBA3`.`hl_city_price_trend` TO ‘DBAU1’@’172.16.1.1′ WITH GRANT OPTION;

4、导出A1中的DBA1、DBA2、DBA3数据库,并修改导出时使用的二进制文件和日志位置;
shell>mysqldump -uroot -p –master-data=2 –single-transaction –database DBA1 DBA2 DBA3 >A1_DBA123.sql

将数据文件传送到D1上:
scp A1_DBA123.sql 172.17.1.104L/root/

–master-data=2:参数值为2时日志文件和日志位置时被注释的状态,需要根据实际情况修改
查找记录位置的行并增加主库信息:
cat -n A1_DBA123.sql | grep “CHANGE MASTER TO”,找到如下
22 — MASTER_LOG_FILE=’mysql-bin.000044′, MASTER_LOG_POS=627859448; 修改为下行(n为行数,file和pos值每个文件不同)
shell> sed -i “N,22a\CHANGE MASTER TO MASTER_HOST=’172.16.1.101′, MASTER_USER=’sync_user’, MASTER_PASSWORD=’SYNC USER PASSWORD’, MASTER_LOG_FILE=’mysql-bin.000044′, MASTER_LOG_POS=627859448;”
导入数据:在D1上执行下行中的命令将修改后的SQL导入到D1中。
shell> mysql -uroot -p <A1_DBA123.sql

然后开启D1的slave.
mysql>start slave;
之后做一些必要的检查:
mysql>show slave status\G;

[ … ]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[ … ]
Seconds_Behind_Master: 0
[ … ]

显示中如果包含以上三列代码值为YES、YES、0则说明同步成功,如果不为0需要等待从库同步完毕。

5、确认数据库同步成功后修改web代码中数据库连接模块,将数据库IP指向D1服务器,并观察数据运行情况;
查看mysql运行状态
mysql> show processlist;
至此A1数据库已无缝迁移完毕。
测试部门确认页面访问正常后停掉D1的slave(后续导入B1数据库时会提示停掉slave)
mysql> stop slave;

6、B1数据库导入的情况大体一致,根据步骤2、3、4、5做一些相应修改后导入到D1中,做必要检查数据库前已完毕后切换web代码中数据库连接模块。
7、C1数据库导入的情况大体一致,根据步骤2、3、4、5做一些相应修改后导入到D1中,做必要检查数据库前已完毕后切换web代码中数据库连接模块。

8、数据库导入完毕后等测试部门确认前台无误。进行下一步。

9、重设D1为主库;
重设D1为主库;
mysql> reset master;
(服务器端执行reset master会重设master,并清理掉作为master的二进制日志)
mysql> quit;
重启数据库
shell> service mysqld restart
测试查看D1的slave状态如下:
mysql> show slave status\G;
Empty set (0.00 sec)

ERROR:
No query specified
10、导出D1的所有数据库,并设置D2为D1的从库;
shell>mysqldump -uroot -p –master-data=2 –single-transaction –all-databases >D1_all-databases.sql
查找记录位置的行并增加主库信息:
cat -n D1_all-databases.sql | grep “CHANGE MASTER TO”,找到如下
22 — MASTER_LOG_FILE=’mysql-bin.00001′, MASTER_LOG_POS=627859448; 修改为下行(n为行数,file和pos值每个文件不同)
shell> sed -i “N,22a\CHANGE MASTER TO MASTER_HOST=’172.16.1.101′, MASTER_USER=’sync_user’, MASTER_PASSWORD=’SYNC USER PASSWORD’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=627859448;”
(“N,22a…” 此中22为行数,和cat中的行数对应)
导入数据:在D2上执行下行中的命令将修改后的SQL导入到D2中。
然后开启D2的作为从库:
shell> start slave;
并做一些必要的检查。

至此,三台MySQL数据库服务器A1(A2从)、B1(B2从)、C1(C2从)成功合并到D1(D2从)。

在D1上设置备份(略写)
shell>echo “password of backup user” > backup.pass
shell> chown 600 backup.pass
这样设置目的是只有当前用户能够读取到backup_user的密码。
备份的关键部分是:
mysqldump -uroot -p`cat backup.pass` –master-data –single-transaction –all-databases > BackupDate`date +%Y%m%d-%H%M%S`.sql
也可以扩展比如清理mtime大于30天的文件等。不再详述。

记一次Mysql数据库某个表误truncate恢复

状态描述:
公司的某个系统的数据库疏于维护,今天(2014/8/25)有个项目组PHPer不小心把线上的Mysql主库的某个表truncate掉了,询问是否有办法恢复数据.
恰好不巧这个唯独这个数据库从未做过备份,由于truncate(不同于delete)是不记录日志的,看来这次要悲剧了.后又与PHPer沟通了解到,原来这个项目新上不到一个月,好像又有了一线希望,马上设置好定时备份该数据表的计划任务后开始着手恢复.

恢复思路:尝试在测试库中通过mysql的二进制日志重放日志来恢复数据,然后稍加处理导入到生产数据库中;

step 1:查看数据库备份(本例没有备份文件)和二进制文件能否拼凑出完整的数据;
查看是否开启二进制日志:
mysql> show variables like ‘log_bin’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| log_bin | ON |
+—————+——-+
1 row in set (0.00 sec)
查看mysql binarylog的保存周期;
mysql> show variables like ‘expire_logs_days’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| expire_logs_days | 30 |
+——————+——-+
1 row in set (0.00 sec)
本地磁盘空间足够,并未清理日志文件;

数据库已开启二进制日志,并且设置的过期日期是30天且未被清理,由此推断被truncate的数据仍完整的存在于二进制文件中,这是本次通过mysqlbinlog进行恢复的前提.

step 2:查找需要使用的binary log文件,以及恢复的start-position(或者start-datetime)和end-position(或者stop-position)
mysqlbinlog有5个常用的参数:
-d:指定数据库名
–start-position:指定开始位置,
–stop-position:指定结束为止
–start-datetime:指定开始时间,格式如”2010-01-07 11:25:56″
–stop-position:指定结束时间
本例实际情况涉及到的文件列表如下:
-rw-rw—- 1 mysql mysql 1073760033 Aug 20 18:04 mysql-bin.002333
-rw-rw—- 1 mysql mysql 1073741925 Aug 21 11:30 mysql-bin.002334
-rw-rw—- 1 mysql mysql 1073741919 Aug 22 06:00 mysql-bin.002335
-rw-rw—- 1 mysql mysql 1073741936 Aug 22 22:30 mysql-bin.002336
-rw-rw—- 1 mysql mysql 1073741995 Aug 23 16:00 mysql-bin.002337
-rw-rw—- 1 mysql mysql 1073742017 Aug 24 09:00 mysql-bin.002338
-rw-rw—- 1 mysql mysql 1073742030 Aug 25 00:00 mysql-bin.002339
-rw-rw—- 1 mysql mysql 281269618 Aug 25 18:30 mysql-bin.002340

开始位置的确定:本例中情况比较特殊,开始日期设置为项目创建日期之前的任意时间点即可.
结束为止的确定:结束位置一定要特别注意,结束position一定要设置为truncate语句的上一条的结束位置,保证数据完整的同时又不能执行truncate语句.具体查找方法如下:

shell>mysqlbinlog -d db_mobi mysql-bin.002340 | grep -A5 -B 5 -E “truncate|agent_select_resblock_detail”
输出如下:

BEGIN
/*!*/;
# at 618699837
#140825 11:12:05 server id 1 end_log_pos 618699946 Query thread_id=258628603 exec_time=0 error_code=0
SET TIMESTAMP=1408936325/*!*/;
TRUNCATE agent_select_resblock_detail
/*!*/;
# at 618699946
#140825 11:12:05 server id 1 end_log_pos 618699973 Xid = 9541176706
COMMIT/*!*/;
# at 618699973

由此可以确定truncate操作日期大约在11:12:05
进一步确定:
shell>mysqlbinlog -d homelink_mobi mysql-bin.002340 –start-datetime “2014-08-25 11:12:00″ –stop-datetime=”2014-08-25 11:12:10”
输出:

…(省略)…
# at 618699733
#140825 11:12:03 server id 1 end_log_pos 618699760 Xid = 9541176633
COMMIT/*!*/;
# at 618699760
#140825 11:12:05 server id 1 end_log_pos 618699837 Query thread_id=258628603 exec_time=0 error_code=0
SET TIMESTAMP=1408936325/*!*/;
BEGIN
/*!*/;
# at 618699837
#140825 11:12:05 server id 1 end_log_pos 618699946 Query thread_id=258628603 exec_time=0 error_code=0
SET TIMESTAMP=1408936325/*!*/;
TRUNCATE agent_select_resblock_detail
/*!*/;
# at 618699946
#140825 11:12:05 server id 1 end_log_pos 618699973 Xid = 9541176706
COMMIT/*!*/;
# at 618699973
…(省略)…

由此可以得出结束position为618699760;

step 3:导出生产环境中的表结构,处理自增长字段(如果需要),并将表结构导入到测试数据库中;

首先查看被误truncate的表结构是否有自增长字段:
mysql> desc agent_select_resblock_detail;
+—————+————–+——+—–+——————-+—————————–+
| Field | Type | Null | Key | Default | Extra |
+—————+————–+——+—–+——————-+—————————–+
| id | int(11) | NO | PRI | NULL | auto_increment |
| agentId | varchar(20) | NO | | NULL | |
| communityCode | varchar(20) | YES | | NULL | |
| communityName | varchar(256) | NO | | NULL | |
| dateTime | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| agentType | int(2) | NO | | NULL | |
+—————+————–+——+—–+——————-+—————————–+
6 rows in set (0.00 sec)
其中id字段为自增长字段.

注意:本次恢复的过程只需要被误truncate的表所属数据库的表结构,并设定取消自增长;
shell>mysqldump -uroot -p -d db_mobi –no-data | sed ‘s/ AUTO_INCREMENT=[0-9]*\b//’ > biaojiegou.sql
并传递到测试数据库机器:
scp biaojiegou.sql Test_db_IP:/root/

step 4:在测试数据库中创建db-mobi数据库并导入表结构;

mysql>drop database db_mobi;
mysql>create database db_mobi;
mysql> use db_mobi;
mysql> source /root/biaojiegou.sql;
至此,数据表结构导入完毕.

step4:使用mysqlbinlog将日志导入到测试数据库中;

shell>mysqlbinlog -d db_mobi mysql-bin.002333 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002334 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002335 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002336 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002337 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002338 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002339 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002340 –stop-position 618699760 |mysql -uroot -ppassword

mysql-bin.00233[3-9]的开始时间和结束时间在本例中可以省略,其中最后一条命令中position由第二步得到.

导入完毕后查看被误truncate的表数据自增长字段最小值和最大值:
mysql> select min(id) from db_mobi.agent_select_resblock_detail;
+———+
| min(id) |
+———+
| 1 |
+———+
1 row in set (0.00 sec)

mysql> select max(id) from db_mobi.agent_select_resblock_detail;
+———+
| max(id) |
+———+
| 2593 |
+———+
1 row in set (0.01 sec)
子增长字段由1增长到2593;

step5:step4结束后,将被误truncate表的数据导出(注意:只导数据,不导出表结构,参数-t)

mysqldump -uroot -p -t db_mobi agent_select_resblock_detail >/root/db_mobil.agent_select_resblock_detail.sql

将导出的数据传送到生产服务器上(如/root/目录)(略)

step6:导入生产环境时候处理生产环境中的子增长字段,并导入step5的表数据;

因为db_mobil.agent_select_resblock_detail表被truncate过,所以子增长字段最小值应该为1,这时候需要把生产环境的自增长字段增加2593(step 4得出)
mysql> update db_mobi.agent_select_resblock_detail set id=id+2593;
Query OK, 2593 rows affected (0.10 sec)
Rows matched: 2593 Changed: 2593 Warnings: 0
将测试数据库中导出的表数据导入生产环境;
mysql> use db_mobi;
mysql> source /root/db_mobil.agent_select_resblock_detail.sql;

此时,数据表恢复完毕.最后再做一些必要的检查.

可能遇见的错误:
1、mysqlbinlog导入数据时提示:/usr/bin/mysqld: unknown variable ‘default-character-set=utf8’ ;
这个错误可能是mysqlbinlog的一个错误,临时注释掉/etc/my.cnf中default-character-set = utf8行(不需要重启数据库),待导入完毕后取消注释即可.
2、mysqlbinlog导入数据时提示类似:table_name does not exists ;
需要注意linux中对mysql表名的大小写是敏感的
3、mysqlbinlog导入到测试数据被truncate的表数据仍为空;
需要注意,mysqlbinlog导入数据的时候的结束的position一定要在truncate的上一条语句的结束点(一定不能包括truncate这条语句).
4、提示auto_increment相关错误;
需要注意在生产数据库导出表结构后需要删除auto_increment或者设置auto_increment=0
5、测试数据库导出的数据导入到生产环境后,生产环境中原有表数据丢失;
需要注意测试数据库中导出数据时不要带表结构,带带表结构导出会在导入到生产库的时候先drop table;

参考资料:
http://melikedev.com/2011/06/01/mysql-remove-auto_increment-from-schema-dumps-mysqldump/
linux:man mysqlbinlog

设置mysql主从复制

主机:172.16.0.108
从机:172.27.4.31
步骤如下:
1、主从服务器分别作以下操作:
1.1、版本一致
1.2、初始化表,并在后台启动mysql
1.3、修改root的密码
2、修改主服务器master:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=222 //[必须]服务器唯一ID,默认是1,一般取IP最后一段
3、修改从服务器slave:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=226 //[必须]服务器唯一ID,默认是1,一般取IP最后一段
4、重启两台服务器的mysql
/etc/init.d/mysql restart
5、在主服务器上建立帐户并授权slave:
#/usr/local/mysql/bin/mysql -uroot -p
mysql>GRANT REPLICATION SLAVE ON *.* to ‘sync_user’@’172.27.%.%’ identified by ‘homelink’;
//一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.145.226,加强安全。
6、主数据库锁表操作,不让数据再进行写入动作。
mysql>use dbname;
mysql>flush tables with read lock;
7、登录主服务器的mysql,查询master的状态
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 21420 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
记下File以及Position的值,以备从服务器使用。
8、以导入所有数据库为例。
shell>mysqldump -uroot -p –all-databases >all.sql
(只导出某个数据库:mysqldump -uroot -p dbname >dbnamebackup.sql)
9、将all.sql导入到从库中。
shell>mysql -uroot -p < all.sql
(只导如某个database:mysql -uroot -p dbname <dbnamebackup.sql)
10、配置从服务器Slave:
mysql>change master to master_host=’172.16.0.108′, master_user=’sync_user’, master_password=’homelink’,master_log_file=’mysql-bin.000001′,master_log_pos=21420; //注意不要断开,master_user=’sync_user’后面不需要@’172.27.%.%’,21420”无单引号。
返回类似:Query OK, 0 rows affected (0.05 sec)表示正常。

Mysql>start slave; //启动从服务器复制功
11、检查从服务器复制功能状态:
mysql> show slave status\G;//查看从库的状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.108 //主服务器地址
Master_User: sync_user //授权帐户名,尽量避免使用root
Master_Port: 3306 //数据库端口,部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 数值 //#同步读取二进制日志的位置,大于等于>=Exec_Master_Log_Pos
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 84996
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
…………
Exec_Master_Log_Pos: 3614785
…………
Seconds_Behind_Master: 0
…………
1 row in set (0.00 sec)
注:
Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
当从库出现性能问题或主从网络延迟时候Seconds_Behind_Master将不为0。
以上操作过程,主从服务器配置完成。
12、取消主数据库锁定
mysql>use dbname;
mysql>unlock tables;
13、验证可以对比show master status;查看Position是否与从库show slave status\G;中的Exec_Master_Log_Pos数值相同。
14、从库如果同步失败,可以通过查看查看从库状态和show slave status\G;或者当前进程show processlist;根据错误提示处理错误后stop slave,然后start slave;