MySQL数据库之etcd安装部署及数据同步MySQL-职坐标

MySQL数据库之etcd安装部署及数据同步MySQL

小标 2018-08-20 来源：阅读 3945 评论 0

摘要：本文主要向大家介绍了MySQL数据库之etcd安装部署及数据同步MySQL ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

本文主要向大家介绍了MySQL数据库之etcd安装部署及数据同步MySQL ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

一、etcd说明及原理二、etcd安装部署说明三、etcd操作说明四、python安装etcd五、python-etcd使用说明六、通过脚本获取本地的信息上传到etcd七、通过脚本将etc的数据同步到mysql一、etcd 简介etcd是用于共享配置和服务发现的分布式，一致的键值存储，重点是：简单：定义明确，面向用户的API（gRPC）安全：使用可选的客户端证书认证的自动TLS快速：基准测试10,000写/秒可靠：使用Raft协议来进行合理的分布式etcd是在Go中编写的，并使用Raft一致性算法来管理高可用性的复制日志。etcd在许多公司的生产中使用，开发团队在关键部署场景中支持它，其中etcd经常与诸如Kubernetes，fleet, locksmith, vulcand, Doorman,等许多应用程序联合。通过严格的测试进一步确保可靠性。1.1.etcd原理及说明etc的原理参考：参考这个超好的动画：//thesecretlivesofdata.com/raft/二、etcd安装部署说明2.etcd安装部署最新版本3.1.1https://github.com/coreos/etcd/ 2.1.1.安装etcd下载:https://github.com/coreos/etcd/releasestar -xf etcd-v3.1.1-linux-amd64.tar.gz
cd etcd-v3.1.1-linux-amd64/
mv /tmp/soft/etcd-v3.1.1-linux-amd64 /data/etcdir连接程序路径：ln -s /data/etcdir/etcd /usr/bin/etcd
ln -s /data/etcdir/etcdctl /usr/bin/etcdctl2.1.2.部署服务器说明同时部署172.16.10.47172.16.10.48172.16.10.492.2.启动进程172.16.110.47 执行：nohup etcd --name etcd0 --initial-advertise-peer-urls //172.16.110.47:2380 --listen-peer-urls //172.16.110.47:2380 --listen-client-urls //172.16.110.47:2379 --advertise-client-urls //172.16.110.47:2379 --initial-cluster-token my-etcd-cluster --initial-cluster etcd0=//172.16.110.47:2380,etcd1=//172.16.110.48:2380,etcd2=//172.16.110.49:2380 --data-dir=/data/etcdir/ --initial-cluster-state new > /var/log/etcd.log 2>&1 &172.16.110.48 执行：nohup etcd --name etcd1 --initial-advertise-peer-urls //172.16.110.48:2380 --listen-peer-urls //172.16.110.48:2380 --listen-client-urls //172.16.110.48:2379 --advertise-client-urls //172.16.110.48:2379 --initial-cluster-token my-etcd-cluster --initial-cluster etcd0=//172.16.110.47:2380,etcd1=//172.16.110.48:2380,etcd2=//172.16.110.49:2380 --data-dir=/data/etcdir/ --initial-cluster-state new > /var/log/etcd.log 2>&1 &172.16.110.49 执行：nohup etcd --name etcd2 --initial-advertise-peer-urls //172.16.110.49:2380 --listen-peer-urls //172.16.110.49:2380 --listen-client-urls //172.16.110.49:2379 --advertise-client-urls //172.16.110.49:2379 --initial-cluster-token my-etcd-cluster --initial-cluster etcd0=//172.16.110.47:2380,etcd1=//172.16.110.48:2380,etcd2=//172.16.110.49:2380 --data-dir=/data/etcdir/ --initial-cluster-state new > /var/log/etcd.log 2>&1 &2.3.启动选项说明：--name: 节点名称，默认为UUID
--initial-advertise-peer-urls：该成员的URL地址，可以通告到集群的其他节点
--listen-peer-urls：
--listen-client-urls：客户端监听的URL地址
--initial-cluster-token：引导期间etcd集群的初始集群标记。指定此选项可以防止运行多个集群时无意中发生的跨集群交互。
--initial-cluster：参数描述了这个新集群中总共有哪些节点，其中每个节点用 name=ip的形式描述，节点之间用,分隔。
--data-dir：保存日志和快照的目录，默认为当前工作目录
--initial-cluster-state new：表示从无到有搭建etcd集群三、etcd操作说明3.1.查看版本：# curl //172.16.110.47:2379/version
{"etcdserver":"3.1.1","etcdcluster":"3.1.0"}3.2.查看键：curl //172.16.110.47:2379/v2/keys
{"action":"get","node":{"dir":true}}3.3.1.创建键值put方法如果key之前存在，则默认会先删除，再新建一个key。如果想要直接update，则追加 -d prevExist=true，但是加了这个参数，如果key之前不存在会报错。# curl //172.16.110.47:2379/v2/keys/ckl -XPUT -d value="love"
{"action":"set","node":{"key":"/ckl","value":"love","modifiedIndex":9,"createdIndex":9}}3.3.2.查看键(分别在47.48.49)：# curl //172.16.110.47:2379/v2/keys
{
    "action": "get",
    "node": {
        "dir": true,
        "nodes": [
            {
                "key": "/ckl",
                "value": "love",
                "modifiedIndex": 9,
                "createdIndex": 9
            }
        ]
    }# curl //172.16.110.48:2379/v2/keys
{
    "action": "get",
    "node": {
        "dir": true,
        "nodes": [
            {
                "key": "/ckl",
                "value": "love",
                "modifiedIndex": 9,
                "createdIndex": 9
            }
        ]
    }
}# curl //172.16.110.49:2379/v2/keys
{
    "action": "get",
    "node": {
        "dir": true,
        "nodes": [
            {
                "key": "/ckl",
                "value": "love",
                "modifiedIndex": 9,
                "createdIndex": 9
            }
        ]
    }
}3.4.创建目录# curl //172.16.110.47:2379/v2/keys/isdir -XPUT -d dir=true
{
    "action": "set",
    "node": {
        "key": "/isdir",
        "dir": true,
        "modifiedIndex": 13,
        "createdIndex": 13
    }
}查看结果：# curl //172.16.110.47:2379/v2/keys
{
    "action": "get",
    "node": {
        "dir": true,
        "nodes": [
            {
                "key": "/isdir",
                "dir": true,
                "modifiedIndex": 13,
                "createdIndex": 13
            },
            {
                "key": "/ckl",
                "value": "love",
                "modifiedIndex": 9,
                "createdIndex": 9
            }
        ]
    }
}3.5.创建带ttl的键值（单位为秒）# curl //172.16.110.47:2379/v2/keys/ttzhi -XPUT -d value=‘kafuka‘ -d ttl=8
{
    "action": "set",
    "node": {
        "key": "/ttzhi",
        "value": "kafuka",
        "expiration": "2017-02-27T02:12:28.306519372Z",
        "ttl": 8,
        "modifiedIndex": 14,
        "createdIndex": 14
    }
}查看值：# curl //172.16.110.47:2379/v2/keys
{
    "action": "get",
    "node": {
        "dir": true,
        "nodes": [
            {
                "key": "/ckl",
                "value": "love",
                "modifiedIndex": 9,
                "createdIndex": 9
            },
            {
                "key": "/isdir",
                "dir": true,
                "modifiedIndex": 13,
                "createdIndex": 13
            },
            {
                "key": "/ttzhi",
                "value": "kafuka",
                "expiration": "2017-02-27T02:12:28.306519372Z",
                "ttl": 6,
                "modifiedIndex": 14,
                "createdIndex": 14
            }
        ]
    }
}过十秒查看：# curl //172.16.110.47:2379/v2/keys
{
    "action": "get",
    "node": {
        "dir": true,
        "nodes": [
            {
                "key": "/ckl",
                "value": "love",
                "modifiedIndex": 9,
                "createdIndex": 9
            },
            {
                "key": "/isdir",
                "dir": true,
                "modifiedIndex": 13,
                "createdIndex": 13
            }
        ]
    }
}3.6.创建有序键值# curl //172.16.110.47:2379/v2/keys/xulie -XPOST -d value="ckl1"
# curl //172.16.110.47:2379/v2/keys/xulie -XPOST -d value="ckl2"
# curl //172.16.110.47:2379/v2/keys/xulie -XPOST -d value="ckl3"查看键值：# curl //172.16.110.47:2379/v2/keys/xulie
{
    "action": "get",
    "node": {
        "key": "/xulie",
        "dir": true,
        "nodes": [
            {
                "key": "/xulie/00000000000000000016",
                "value": "ckl1",
                "modifiedIndex": 16,
                "createdIndex": 16
            },
            {
                "key": "/xulie/00000000000000000017",
                "value": "ckl2",
                "modifiedIndex": 17,
                "createdIndex": 17
            },
            {
                "key": "/xulie/00000000000000000018",
                "value": "ckl3",
                "modifiedIndex": 18,
                "createdIndex": 18
            }
        ],
        "modifiedIndex": 16,
        "createdIndex": 16
    }
}3.7.删除指定的键3.7.1.查看键# curl //172.16.110.47:2379/v2/keys
{
    "action": "get",
    "node": {
        "dir": true,
        "nodes": [
            {
                "key": "/ckl",
                "value": "love",
                "modifiedIndex": 9,
                "createdIndex": 9
            },
            {
                "key": "/isdir",
                "dir": true,
                "modifiedIndex": 13,
                "createdIndex": 13
            },
            {
                "key": "/xulie",
                "dir": true,
                "modifiedIndex": 16,
                "createdIndex": 16
            }
        ]
    }
}3.7.2.删除键：# curl //172.16.110.47:2379/v2/keys/ckl -XDELETE
{
    "action": "delete",
    "node": {
        "key": "/ckl",
        "modifiedIndex": 19,
        "createdIndex": 9
    },
    "prevNode": {
        "key": "/ckl",
        "value": "love",
        "modifiedIndex": 9,
        "createdIndex": 9
    }
}3.7.3.查看删除后的键:# curl //172.16.110.47:2379/v2/keys
{
    "action": "get",
    "node": {
        "dir": true,
        "nodes": [
            {
                "key": "/isdir",
                "dir": true,
                "modifiedIndex": 13,
                "createdIndex": 13
            },
            {
                "key": "/xulie",
                "dir": true,
                "modifiedIndex": 16,
                "createdIndex": 16
            }
        ]
    }
}3.8.列出所有的集群成员：# curl //172.16.110.47:2379/v2/members
{
    "members": [
        {
            "id": "19114e391e0461bf",
            "name": "etcd1",
            "peerURLs": [
                "//172.16.110.48:2380"
            ],
            "clientURLs": [
                "//172.16.110.48:2379"
            ]
        },
        {
            "id": "1f82c5220d58283c",
            "name": "etcd2",
            "peerURLs": [
                "//172.16.110.49:2380"
            ],
            "clientURLs": [
                "//172.16.110.49:2379"
            ]
        },
        {
            "id": "c75626929de37ef1",
            "name": "etcd0",
            "peerURLs": [
                "//172.16.110.47:2380"
            ],
            "clientURLs": [
                "//172.16.110.47:2379"
            ]
        }
    ]
}3.9.查看leader：# curl //172.16.110.48:2379/v2/stats/leader
{
    "leader": "19114e391e0461bf",
    "followers": {
        "1f82c5220d58283c": {
            "latency": {
                "current": 0.013289,
                "average": 0.0035088124999999993,
                "standardDeviation": 0.004889767488321275,
                "minimum": 0.001167,
                "maximum": 0.032709
            },
            "counts": {
                "fail": 1,
                "success": 64
            }
        },
        "c75626929de37ef1": {
            "latency": {
                "current": 0.001966,
                "average": 0.0035810634920634917,
                "standardDeviation": 0.004692562520823965,
                "minimum": 0.000439,
                "maximum": 0.027367
            },
            "counts": {
                "fail": 0,
                "success": 63
            }
        }
    }
}3.10.查看节点自身信息：# curl //172.16.110.47:2379/v2/stats/self
{
    "name": "etcd0",
    "id": "c75626929de37ef1",
    "state": "StateFollower",
    "startTime": "2017-02-27T10:02:05.27853915+08:00",
    "leaderInfo": {
        "leader": "19114e391e0461bf",
        "uptime": "28m47.949767952s",
        "startTime": "2017-02-27T10:02:20.141696895+08:00"
    },
    "recvAppendRequestCnt": 63,
    "sendAppendRequestCnt": 0
}3.11.查看集群运行状态：# curl //172.16.110.47:2379/v2/stats/store
{
    "getsSuccess": 11,
    "getsFail": 21,
    "setsSuccess": 11,
    "setsFail": 0,
    "deleteSuccess": 1,
    "deleteFail": 1,
    "updateSuccess": 0,
    "updateFail": 0,
    "createSuccess": 6,
    "createFail": 0,
    "compareAndSwapSuccess": 0,
    "compareAndSwapFail": 0,
    "compareAndDeleteSuccess": 0,
    "compareAndDeleteFail": 0,
    "expireCount": 1,
    "watchers": 0
}四、python安装etcd4.1.python安装etcd安装etcd依赖于setuptools、packaging、pyparsing4.2.安装pyparsing模块tar -xf pyparsing-2.1.9.tar.gz
cd pyparsing-2.1.9
python setup.py build
python setup.py install4.3.安装packaging模块https://pypi.python.org/pypi/packaging/16.5
tar -xf packaging-16.5.tar.gz
cd packaging-16.5
python setup.py build
python setup.py install4.4.安装setuptools模块https://pypi.python.org/pypi/setuptools/20.0
tar -xf setuptools-20.0.tar.gz
cd setuptools-20.0
python setup.py build
python setup.py install4.5.安装etcd模块https://pypi.python.org/pypi/python-etcd
tar -xf python-etcd-0.4.4.tar.gz
cd python-etcd-0.4.4
python setup.py build
python setup.py install五、python-etcd使用说明官网说明：https://pypi.python.org/pypi/python-etcd5.1.测试脚本：#!/usr/bin/python
import etcd
import time

client = etcd.Client() # this will create a client against etcd server running on localhost on port 4001
client = etcd.Client(host=‘172.16.110.47‘, port=2379)
client.write(‘/nodes/n1‘, 1)
client.write(‘/nodes/n2‘, 2)
client.write(‘/nodes/n1‘, 1)
n1value = client.read(‘/nodes/n1‘).value
n2value = client.read(‘/nodes/n2‘).value
print "n1 value is:  %s" % n1value
print "n2 value is:  %s" % n2value
client.delete(‘/nodes/n1‘)
n2value1 = client.read(‘/nodes/n2‘).value
print "n2 value is:  %s" % n2value1
n1value1 = client.read(‘/nodes/n1‘).value
print "n1 value is:  %s" % n1value15.2.运行测试脚本查看结果n1 value is:  1
n2 value is:  2
n2 value is:  2
Traceback (most recent call last):
  File "daoru.py", line 17, in
    n1value1 = client.read(‘/nodes/n1‘).value
  File "/usr/lib/python2.7/site-packages/python_etcd-0.4.4-py2.7.egg/etcd/client.py", line 562, in read
    timeout=timeout)
  File "/usr/lib/python2.7/site-packages/python_etcd-0.4.4-py2.7.egg/etcd/client.py", line 874, in wrapper
    return self._handle_server_response(response)
  File "/usr/lib/python2.7/site-packages/python_etcd-0.4.4-py2.7.egg/etcd/client.py", line 954, in _handle_server_response
    etcd.EtcdError.handle(r)
  File "/usr/lib/python2.7/site-packages/python_etcd-0.4.4-py2.7.egg/etcd/__init__.py", line 304, in handle
    raise exc(msg, payload)
etcd.EtcdKeyNotFound: Key not found : /nodes/n1测试发现：   重复的键值会覆盖原有的   删除nodes/n1提示Key not found六、通过脚本获取本地的信息上传到etcd6.1.脚本内容#!/usr/bin/env python
#coding:utf-8

import etcd
import time
import json
import socket
import  fcntl
import struct
import uuid
import commands
from optparse import OptionParser
import sys
import os
from collections import OrderedDict
from multiprocessing import cpu_count

ttl= 300
processname="cmdb_agent"

def uniquecheck(processname,pid):
    print commands.getstatusoutput("ps -ef|grep %s|grep -v grep|grep -v %s|awk ‘{print $2}‘|xargs kill -9" %(processname,pid))

def setetcddata(hostname,pushjson,ttl):
    client = etcd.Client(host=‘172.16.110.47‘, port=2379,read_timeout=20)
    client.write(‘/cmdb/{0}‘.format(hostname),pushjson, ttl=ttl)

def getoptparse():
    parser = OptionParser()
    parser.add_option("-p",dest="projectname",help=u"项目名称")
    parser.add_option("-i",dest="ifname",
                      help=u"默认网卡",default = "ens33")
    parser.add_option("-P", dest="pre",
                      help=u"hostname前缀", default="auto")
    (options, args) = parser.parse_args()
    if not options.projectname:
        print u"项目名称为空"
        sys.exit()
    projectname = options.projectname
    ifname = options.ifname
    hostnamepre=options.pre
    print projectname,ifname,hostnamepre
    return  projectname,ifname,hostnamepre

def create_hostname(projectname,ip,hostnamepre):
    ipsplit = ip.split(".")
    if hostnamepre == "auto":
        if int(ipsplit[1]) > 100:
            hostnamepre= "qm-hd2a"
        else:
            hostnamepre = "qm-hd2b"
    hostname = "{0}-{1}-{2}_{3}".format(hostnamepre,projectname,ipsplit[2],ipsplit[3])
    print hostname
    return  hostname

def get_ip_address(ifname):
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    return socket.inet_ntoa(fcntl.ioctl(
        s.fileno(),
        0x8915,  # SIOCGIFADDR
        struct.pack(‘256s‘, ifname[:15])
    )[20:24])

def get_mac_address():
    mac=uuid.UUID(int = uuid.getnode()).hex[-12:]
    return ":".join([mac[e:e+2] for e in range(0,11,2)])

def get_serverconfig():
    meminfo = OrderedDict()
    try:
        with open(‘/proc/meminfo‘) as f:
            for line in f:
                meminfo[line.split(‘:‘)[0]] = line.split(‘:‘)[1].strip()
        MemTotal = meminfo.get("MemTotal")
        MemTotal = float(MemTotal.split()[0]) / 1024 / 1024
    except:
        MemTotal = 0
    return  str(cpu_count()),‘%.2fG‘ %MemTotal

def set_hostname(hostname):
    locahostname = socket.getfqdn(socket.gethostname())
    if locahostname == hostname:
        return 1
    print commands.getstatusoutput(‘hostname {0}‘.format(hostname))
    print commands.getstatusoutput("sed -i ‘s/^HOSTNAME=.*$/HOSTNAME={0}/‘ /etc/sysconfig/network".format(hostname))

if __name__==‘__main__‘:
    selfpid = str(os.getpid())
    uniquecheck(processname,selfpid)
    projectname, ifname, hostnamepre = getoptparse()
    ip = get_ip_address(ifname)
    hostname=create_hostname(projectname,ip,hostnamepre)
    macaddr = get_mac_address()
    cputotal,memtotal = get_serverconfig()
    print set_hostname(hostname)
    pushjson = json.dumps({"macaddr":macaddr,"ip":ip,"hostname":hostname,"projectname":projectname,"cputotal":cputotal,"memtotal":memtotal})
    setetcddata(hostname, pushjson, ttl)6.2.运行脚本# python cmdb_agent.py -p etcnode1
(31488, ‘\nUsage:\n kill [options]  [...]\n\nOptions:\n -a, --all              do not restrict the name-to-pid conversion to processes\n                        with the same uid as the present process\n -s, --signal      send specified signal\n -q, --queue       use sigqueue(2) rather than kill(2)\n -p, --pid              print pids without signaling them\n -l, --list [=] list signal names, or convert one to a name\n -L, --table            list signal names and numbers\n\n -h, --help     display this help and exit\n -V, --version  output version information and exit\n\nFor more details see kill(1).‘)
etcnode1 ens33 auto
qm-hd2b-etcnode1-110_47
(256, ‘hostname: the specified hostname is invalid‘)
(0, ‘‘)
None6.3.查看etcd的内容# curl //172.16.110.47:2379/v2/keys/cmdb
{
    "action": "get",
    "node": {
        "key": "/cmdb",
        "dir": true,
        "nodes": [
            {
                "key": "/cmdb/qm-hd2b-etcnode1-110_47",
                "value": "{\"memtotal\": \"0.74G\", \"ip\": \"172.16.110.47\", \"hostname\": \"qm-hd2b-etcnode1-110_47\", \"macaddr\": \"00:0c:29:91:cf:86\", \"cputotal\": \"1\", \"projectname\": \"etcnode1\"}",
                "expiration": "2017-03-02T03:47:49.288421145Z",
                "ttl": 268,
                "modifiedIndex": 63,
                "createdIndex": 63
            }
        ],
        "modifiedIndex": 63,
        "createdIndex": 63
    }
}七.通过脚本将etc的数据同步到mysql7.1.安装MySQL-pythonyum install MySQL-python7.2.脚本同步etcd的数据到mysql#!/usr/bin/env python
#coding:utf-8
# Author: Someone
# Datetime: 2017/3/2

import etcd
import json
import MySQLdb
import sys

serverlist = []
EtcdHostIpdict = {}
MyIpList = []
MyInsertList = []

client = etcd.Client(host=‘10.8.1.1‘, port=2379,read_timeout=20)
result=client.read(‘/cmdb/‘)
for i in result.children:
    server = json.loads(i.value)
    serverlist.append(server)

for hostinfo in serverlist:
    ip = hostinfo.get("ip", "")
    hostname = hostinfo.get("hostname", "")
    EtcdHostIpdict[ip] = hostname

try:
    Conn = MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘‘,db=‘jumpserver‘,port=3306)
    Cur = Conn.cursor()
    Cur.execute(‘select * from jasset_asset;‘)
    MyIpAll = Cur.fetchall()
    Num = 0
    for RealIp in MyIpAll:
        MyIpList.append(MyIpAll[Num][1])
        Num += 1
    Cur.close()
    Conn.close()
except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])

for RealIp,RealHost in EtcdHostIpdict.items():
    if RealIp not in MyIpList:
        print "%s %s" %(RealIp,RealHost)
        MyInsertList.append((RealIp,RealHost,22,1,1,1))

#print MyInsertList
if len(MyInsertList) == 0:
    print("No data need to add!")
    sys.exit()
else:
    try:
        Conn = MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘‘,db=‘cklserver‘,port=3306)
        Cur = Conn.cursor()
        Cur.executemany("insert into jasset_asset (ip,hostname,port,use_default_auth,idc_id,is_active) values(%s,%s,%s,%s,%s,%s);",MyInsertList)
        Conn.commit()
        Cur.close()
        Conn.close()
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])附加说明安装报错：python 安装etcdTraceback (most recent call last):
  File "setup.py", line 11, in
    import setuptools
  File "/tmp/soft/setuptools-34.2.0/setuptools/__init__.py", line 12, in
    import setuptools.version
  File "/tmp/soft/setuptools-34.2.0/setuptools/version.py", line 1, in
    import pkg_resources
  File "/tmp/soft/setuptools-34.2.0/pkg_resources/__init__.py", line 72, in
    import packaging.requirements
  File "/usr/lib/python2.7/site-packages/packaging/requirements.py", line 59, in
    MARKER_EXPR = originalTextFor(MARKER_EXPR())("marker")
TypeError: __call__() takes exactly 2 arguments (1 given)版本不对：https://bbs.archlinux.org/viewtopic.php?id=209485
$ pacman -Qo /usr/lib/python2.7/site-packages/packaging/requirements.py /usr/lib/python2.7/site-packages/pkg_resources/__init__.py
/usr/lib/python2.7/site-packages/packaging/requirements.py is owned by python2-packaging 16.5-1