深海游弋的鱼 – 第 3 页

ubuntu 16.04使用LimeSDR 1.4s估算信号到达角DOA

安装依赖的软件包

#安装预编译版本，为后续编译减少依赖,预编译版本太老了
$ sudo apt-get install libarmadillo-dev

$ cd ~

#编译安装最新版本的armadillo，gr-doa需要这个库的支持
$ git clone https://github.com/conradsnicta/armadillo-code.git

$ cd armadillo-code

$ mkdir build 

$ cd build 

$ cmake .. 

$ make

$ sudo make install


#编译安装SoapySDR，为我们后续操作LimeSDR准备操作接口
$ cd ~

$ git clone https://github.com/pothosware/SoapySDR.git

$ cd SoapySDR

$ git pull origin master

$ mkdir build && cd build

$ cmake ..

$ make -j4

$ sudo make install

$ sudo ldconfig


#编译安装GrOsmoSDR,后续为了支持GnuRadio,我们需要GrOsmoSDR帮我们完成一个中转
$ cd ~

$ git clone git://git.osmocom.org/gr-osmosdr

$ cd gr-osmosdr

#修正几处问题
$ sed -i '$a\from _osmosdr_swig import source_IQBalanceOff' ./python/__init__.py

$ sed -i '$a\from _osmosdr_swig import source_IQBalanceManual' ./python/__init__.py

$ sed -i '$a\from _osmosdr_swig import source_IQBalanceAutomatic' ./python/__init__.py

$ mkdir build

$ cd build

$ cmake ..

$ make

$ sudo make install

$ sudo ldconfig

#编译安装LimeSDR的驱动
$ git clone https://github.com/myriadrf/LimeSuite.git

$ cd LimeSuite

#建议2018年4月以后的的主分支,早期版本存在相位随时间漂移的问题
$ git checkout master

# 不可删除build目录，清理build目录后要还原被误删除的文件，
# 原因在于build目录下存在mcu程序，默认应用启动后从这个目录提取mcu程序刷新到设备

$ mkdir build ; cd build

# cmake -DCMAKE_BUILD_TYPE=Debug ..
$ cmake ..

$ make -j4

$ sudo make install

$ sudo ldconfig

$ cd ../udev-rules/

$ sudo ./install.sh

# Download board firmware
$ sudo LimeUtil --update

#安装预编译版本，为后续编译减少依赖,预编译版本太老了

$ sudo apt-get install libarmadillo-dev

$ cd ~

#编译安装最新版本的armadillo，gr-doa需要这个库的支持

$ git clone https://github.com/conradsnicta/armadillo-code.git

$ cd armadillo-code

$ mkdir build

$ cd build

$ cmake ..

$ make

$ sudo make install

#编译安装SoapySDR，为我们后续操作LimeSDR准备操作接口

$ cd ~

$ git clone https://github.com/pothosware/SoapySDR.git

$ cd SoapySDR

$ git pull origin master

$ mkdir build && cd build

$ cmake ..

$ make -j4

$ sudo make install

$ sudo ldconfig

#编译安装GrOsmoSDR,后续为了支持GnuRadio,我们需要GrOsmoSDR帮我们完成一个中转

$ cd ~

$ git clone git://git.osmocom.org/gr-osmosdr

$ cd gr-osmosdr

#修正几处问题

$ sed -i '$a\from _osmosdr_swig import source_IQBalanceOff' ./python/__init__.py

$ sed -i '$a\from _osmosdr_swig import source_IQBalanceManual' ./python/__init__.py

$ sed -i '$a\from _osmosdr_swig import source_IQBalanceAutomatic' ./python/__init__.py

$ mkdir build

$ cd build

$ cmake ..

$ make

$ sudo make install

$ sudo ldconfig

#编译安装LimeSDR的驱动

$ git clone https://github.com/myriadrf/LimeSuite.git

$ cd LimeSuite

#建议2018年4月以后的的主分支,早期版本存在相位随时间漂移的问题

$ git checkout master

# 不可删除build目录，清理build目录后要还原被误删除的文件，

# 原因在于build目录下存在mcu程序，默认应用启动后从这个目录提取mcu程序刷新到设备

$ mkdir build ; cd build

# cmake -DCMAKE_BUILD_TYPE=Debug ..

$ cmake ..

$ make -j4

$ sudo make install

$ sudo ldconfig

$ cd ../udev-rules/

$ sudo ./install.sh

# Download board firmware

$ sudo LimeUtil --update

下载最新的`gr-doa`源代码

#编译安装gr-doa 
$ cd ~ 

$ git clone https://github.com/EttusResearch/gr-doa.git

#编译安装gr-doa

$ cd ~

$ git clone https://github.com/EttusResearch/gr-doa.git

增加limesdr的调用代码:

$ sed -i "s/__init__.py/__init__.py\nlimesdr_soapysdr_source.py/g" ~/gr-doa/python/CMakeLists.txt

$ sed -i "s/from twinrx_usrp_source import twinrx_usrp_source/from twinrx_usrp_source import twinrx_usrp_source\nfrom limesdr_soapysdr_source import limesdr_soapysdr_source/g" ~/gr-doa/python/__init__.py

$ vim ~/gr-doa/python/limesdr_soapysdr_source.py

$ sed -i "s/__init__.py/__init__.py\nlimesdr_soapysdr_source.py/g" ~/gr-doa/python/CMakeLists.txt

$ sed -i "s/from twinrx_usrp_source import twinrx_usrp_source/from twinrx_usrp_source import twinrx_usrp_source\nfrom limesdr_soapysdr_source import limesdr_soapysdr_source/g" ~/gr-doa/python/__init__.py

$ vim ~/gr-doa/python/limesdr_soapysdr_source.py

具体代码如下：

# -*- coding: utf-8 -*-
#
# Copyright 2018
# longsky <wangqiang1588@sina.com>
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#

from gnuradio import gr
from gnuradio import blocks
import SoapySDR
from SoapySDR import * #SOAPY_SDR_ constants

import osmosdr
from osmosdr import *

from gnuradio.filter import firdes

def gen_sig_io(num_elements):
    # Dynamically create types for signature
    io = []
    for i in range(num_elements):
        io.append(gr.sizeof_gr_complex*1)
    io.append(gr.sizeof_float*num_elements)
    return io

class limesdr_soapysdr_source(gr.hier_block2):

    def __init__(self, samp_rate=1000000, center_freq=2400000000, gain=40, sources=2):
        gr.hier_block2.__init__(
            self, "LimeSDR SoapySDR",
            gr.io_signature(0, 0, 0),
            gr.io_signaturev(sources, sources, gen_sig_io(sources)),
        )

        ##################################################
        # Parameters
        ##################################################
        self.samp_rate = samp_rate
        self.center_freq = center_freq
        self.gain = gain
        self.sources = sources

        args = dict(driver="lime")
        limesdr = SoapySDR.Device(args)
        # We need to detect the number of channels as the LimeSDR has two
        # and the LimeSDR-Mini has one
        chans = range(limesdr.getNumChannels(0))
        if len(chans) < sources :
            raise Exception("limesdr device doesn't provide enough rx channels ")

        while len(chans) > sources :
            chans.pop()

        limesdr = None
      
        #warnning: args must be written like below 
        args = 'soapy=0,driver=lime,nchan=' + str(sources)
        self.osmosdr_source_0 = osmosdr.source(args)
        self.osmosdr_source_0.set_clock_source('internal', 0)
        self.osmosdr_source_0.set_sample_rate(self.samp_rate)
        #self.osmosdr_source_0.set_time_now(osmosdr.time_spec_t())
        
        for ch in chans:
            self.osmosdr_source_0.set_gain(self.gain, ch)
            # print(self.limesdr.listAntennas(SOAPY_SDR_RX, 0))
            # ('NONE', 'LNAH', 'LNAL', 'LNAW', 'LB1', 'LB2')
            #RX_L : is lower then 2.5Ghz,RX_H : higher than 2.5GhZ
            self.osmosdr_source_0.set_antenna("LNAH", ch)
            #self.osmosdr_source_0.set_bandwidth(ch,20*1000*1000)
            self.osmosdr_source_0.set_dc_offset_mode(osmosdr.source_IQBalanceAutomatic,ch)

        # Use timed commands to set frequencies
     	self.set_center_freq(center_freq,sources)
     	
        ##################################################
        # Connections
        ##################################################
        for source in range(sources):
            self.connect((self.osmosdr_source_0, source), (self, source))


    def __del__(self):
        self.disconnect_all()
        if hasattr(gr.hier_block2,"__del__") :
            gr.hier_block2.__del__(self)

    def get_samp_rate(self):
        return self.samp_rate

    def set_samp_rate(self, samp_rate):
        self.samp_rate = samp_rate
        self.osmosdr_source_0.set_sample_rate(self.samp_rate)

    def set_center_freq(self, center_freq, sources):
        self.center_freq = center_freq
        for ch in range(self.sources):
            self.osmosdr_source_0.set_center_freq(self.center_freq, ch)

    def get_center_freq(self):
        return self.center_freq


    def get_gain(self):
        return self.gain

    def set_gain(self, gain):
        print "DO NOT TUNE GAINS DURING RUNTIME"

    def get_sources(self):
        return self.sources

    def set_sources(self, sources):
        print "DO NOT CHANGE SOURCES DURING RUNTIME"

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

# -*- coding: utf-8 -*-

# longsky <wangqiang1588@sina.com>

# This program is free software: you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation, either version 3 of the License, or

# (at your option) any later version.

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License

# along with this program. If not, see <http://www.gnu.org/licenses/>.

from gnuradio import gr

from gnuradio import blocks

import SoapySDR

from SoapySDR import * #SOAPY_SDR_ constants

import osmosdr

from osmosdr import *

from gnuradio.filter import firdes

def gen_sig_io(num_elements):

# Dynamically create types for signature

io = []

for i in range(num_elements):

io.append(gr.sizeof_gr_complex*1)

io.append(gr.sizeof_float*num_elements)

return io

class limesdr_soapysdr_source(gr.hier_block2):

def __init__(self, samp_rate=1000000, center_freq=2400000000, gain=40, sources=2):

gr.hier_block2.__init__(

self, "LimeSDR SoapySDR",

gr.io_signature(0, 0, 0),

gr.io_signaturev(sources, sources, gen_sig_io(sources)),

)

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

# Parameters

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

self.samp_rate = samp_rate

self.center_freq = center_freq

self.gain = gain

self.sources = sources

args = dict(driver="lime")

limesdr = SoapySDR.Device(args)

# We need to detect the number of channels as the LimeSDR has two

# and the LimeSDR-Mini has one

chans = range(limesdr.getNumChannels(0))

if len(chans) < sources :

raise Exception("limesdr device doesn't provide enough rx channels ")

while len(chans) > sources :

chans.pop()

limesdr = None

#warnning: args must be written like below

args = 'soapy=0,driver=lime,nchan=' + str(sources)

self.osmosdr_source_0 = osmosdr.source(args)

self.osmosdr_source_0.set_clock_source('internal', 0)

self.osmosdr_source_0.set_sample_rate(self.samp_rate)

#self.osmosdr_source_0.set_time_now(osmosdr.time_spec_t())

for ch in chans:

self.osmosdr_source_0.set_gain(self.gain, ch)

# print(self.limesdr.listAntennas(SOAPY_SDR_RX, 0))

# ('NONE', 'LNAH', 'LNAL', 'LNAW', 'LB1', 'LB2')

#RX_L : is lower then 2.5Ghz,RX_H : higher than 2.5GhZ

self.osmosdr_source_0.set_antenna("LNAH", ch)

#self.osmosdr_source_0.set_bandwidth(ch,20*1000*1000)

self.osmosdr_source_0.set_dc_offset_mode(osmosdr.source_IQBalanceAutomatic,ch)

# Use timed commands to set frequencies

self.set_center_freq(center_freq,sources)

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

# Connections

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

for source in range(sources):

self.connect((self.osmosdr_source_0, source), (self, source))

def __del__(self):

self.disconnect_all()

if hasattr(gr.hier_block2,"__del__") :

gr.hier_block2.__del__(self)

def get_samp_rate(self):

return self.samp_rate

def set_samp_rate(self, samp_rate):

self.samp_rate = samp_rate

self.osmosdr_source_0.set_sample_rate(self.samp_rate)

def set_center_freq(self, center_freq, sources):

self.center_freq = center_freq

for ch in range(self.sources):

self.osmosdr_source_0.set_center_freq(self.center_freq, ch)

def get_center_freq(self):

return self.center_freq

def get_gain(self):

return self.gain

def set_gain(self, gain):

print "DO NOT TUNE GAINS DURING RUNTIME"

def get_sources(self):

return self.sources

def set_sources(self, sources):

print "DO NOT CHANGE SOURCES DURING RUNTIME"

编译代码

$ cd ~

$ cd gr-doa 

$ mkdir build 

$ cd build 

$ cmake .. 

$ make 

$ sudo make install 

$ sudo ldconfig

$ cd ~

$ cd gr-doa

$ mkdir build

$ cd build

$ cmake ..

$ make

$ sudo make install

$ sudo ldconfig

测试代码

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
##################################################
# GNU Radio Python Flow Graph
# Title: Run Rootmusic Lin Array LimeSDR
# Generated: Tue Apr  3 20:14:45 2018
##################################################

if __name__ == '__main__':
    import ctypes
    import sys
    if sys.platform.startswith('linux'):
        try:
            x11 = ctypes.cdll.LoadLibrary('libX11.so')
            x11.XInitThreads()
        except:
            print "Warning: failed to XInitThreads()"

def struct(data): return type('Struct', (object,), data)()
from PyQt4 import Qt
from gnuradio import eng_notation
from gnuradio import gr
from gnuradio.eng_option import eng_option
from gnuradio.filter import firdes
from optparse import OptionParser
import doa
import sys


class run_RootMUSIC_lin_array_LimeSDR(gr.top_block, Qt.QWidget):

    def __init__(self):
        gr.top_block.__init__(self, "Run Rootmusic Lin Array LimeSDR")
        Qt.QWidget.__init__(self)
        self.setWindowTitle("Run Rootmusic Lin Array LimeSDR")
        try:
            self.setWindowIcon(Qt.QIcon.fromTheme('gnuradio-grc'))
        except:
            pass
        self.top_scroll_layout = Qt.QVBoxLayout()
        self.setLayout(self.top_scroll_layout)
        self.top_scroll = Qt.QScrollArea()
        self.top_scroll.setFrameStyle(Qt.QFrame.NoFrame)
        self.top_scroll_layout.addWidget(self.top_scroll)
        self.top_scroll.setWidgetResizable(True)
        self.top_widget = Qt.QWidget()
        self.top_scroll.setWidget(self.top_widget)
        self.top_layout = Qt.QVBoxLayout(self.top_widget)
        self.top_grid_layout = Qt.QGridLayout()
        self.top_layout.addLayout(self.top_grid_layout)

        self.settings = Qt.QSettings("GNU Radio", "run_RootMUSIC_lin_array_LimeSDR")
        self.restoreGeometry(self.settings.value("geometry").toByteArray())

        ##################################################
        # Variables
        # NormSpacing 天线之间的实际距离，单位为米，类型为浮点类型
        # NumTargets 发出信号的设备数量
        ##################################################
        self.input_variables = input_variables = struct({"SampleRate": 1000000, "CenterFreq": 2550000000, "Gain": 60, "NumArrayElements": 2, "NormSpacing": 0.5, "SnapshotSize": 2**11, "OverlapSize": 2**9, "NumTargets": 1, })

        ##################################################
        # Blocks
        ##################################################
        self.limesdr = doa.limesdr_soapysdr_source(
            samp_rate=input_variables.SampleRate,
            center_freq=input_variables.CenterFreq,
            gain=input_variables.Gain,
            sources=input_variables.NumArrayElements
        )
        self.tab = Qt.QTabWidget()
        self.tab_widget_0 = Qt.QWidget()
        self.tab_layout_0 = Qt.QBoxLayout(Qt.QBoxLayout.TopToBottom, self.tab_widget_0)
        self.tab_grid_layout_0 = Qt.QGridLayout()
        self.tab_layout_0.addLayout(self.tab_grid_layout_0)
        self.tab.addTab(self.tab_widget_0, "Angle of Arrival")
        self.top_layout.addWidget(self.tab)
        self.doa_rootMUSIC_linear_array_0 = doa.rootMUSIC_linear_array(input_variables.NormSpacing, input_variables.NumTargets, input_variables.NumArrayElements)
        self.doa_qt_compass_0 = doa.compass("", 0, 180, 10, 0)
        self.top_layout.addLayout(self.doa_qt_compass_0.this_layout)
        self.doa_autocorrelate_0 = doa.autocorrelate(input_variables.NumArrayElements, input_variables.SnapshotSize, input_variables.OverlapSize, 1)

        ##################################################
        # Connections
        ##################################################
        self.connect((self.doa_autocorrelate_0, 0), (self.doa_rootMUSIC_linear_array_0, 0))    
        self.connect((self.doa_rootMUSIC_linear_array_0, 0), (self.doa_qt_compass_0, 0))
        for ch in range(input_variables.NumArrayElements) :
            self.connect((self.limesdr, ch), (self.doa_autocorrelate_0, ch))    

    def closeEvent(self, event):
        self.settings = Qt.QSettings("GNU Radio", "run_RootMUSIC_lin_array_LimeSDR")
        self.settings.setValue("geometry", self.saveGeometry())
        event.accept()

def main(top_block_cls=run_RootMUSIC_lin_array_LimeSDR, options=None):

    from distutils.version import StrictVersion
    if StrictVersion(Qt.qVersion()) >= StrictVersion("4.5.0"):
        style = gr.prefs().get_string('qtgui', 'style', 'raster')
        Qt.QApplication.setGraphicsSystem(style)
    qapp = Qt.QApplication(sys.argv)

    tb = top_block_cls()
    tb.start()
    tb.show()

    def quitting():
        tb.stop()
        tb.wait()
    qapp.connect(qapp, Qt.SIGNAL("aboutToQuit()"), quitting)
    qapp.exec_()


if __name__ == '__main__':
    main()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

#!/usr/bin/env python2

# -*- coding: utf-8 -*-

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

# GNU Radio Python Flow Graph

# Title: Run Rootmusic Lin Array LimeSDR

# Generated: Tue Apr 3 20:14:45 2018

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

if __name__ == '__main__':

import ctypes

import sys

if sys.platform.startswith('linux'):

try:

x11 = ctypes.cdll.LoadLibrary('libX11.so')

x11.XInitThreads()

except:

print "Warning: failed to XInitThreads()"

def struct(data): return type('Struct', (object,), data)()

from PyQt4 import Qt

from gnuradio import eng_notation

from gnuradio import gr

from gnuradio.eng_option import eng_option

from gnuradio.filter import firdes

from optparse import OptionParser

import doa

import sys

class run_RootMUSIC_lin_array_LimeSDR(gr.top_block, Qt.QWidget):

def __init__(self):

gr.top_block.__init__(self, "Run Rootmusic Lin Array LimeSDR")

Qt.QWidget.__init__(self)

self.setWindowTitle("Run Rootmusic Lin Array LimeSDR")

try:

self.setWindowIcon(Qt.QIcon.fromTheme('gnuradio-grc'))

except:

pass

self.top_scroll_layout = Qt.QVBoxLayout()

self.setLayout(self.top_scroll_layout)

self.top_scroll = Qt.QScrollArea()

self.top_scroll.setFrameStyle(Qt.QFrame.NoFrame)

self.top_scroll_layout.addWidget(self.top_scroll)

self.top_scroll.setWidgetResizable(True)

self.top_widget = Qt.QWidget()

self.top_scroll.setWidget(self.top_widget)

self.top_layout = Qt.QVBoxLayout(self.top_widget)

self.top_grid_layout = Qt.QGridLayout()

self.top_layout.addLayout(self.top_grid_layout)

self.settings = Qt.QSettings("GNU Radio", "run_RootMUSIC_lin_array_LimeSDR")

self.restoreGeometry(self.settings.value("geometry").toByteArray())

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

# Variables

# NormSpacing 天线之间的实际距离，单位为米，类型为浮点类型

# NumTargets 发出信号的设备数量

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

self.input_variables = input_variables = struct({"SampleRate": 1000000, "CenterFreq": 2550000000, "Gain": 60, "NumArrayElements": 2, "NormSpacing": 0.5, "SnapshotSize": 2**11, "OverlapSize": 2**9, "NumTargets": 1, })

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

# Blocks

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

self.limesdr = doa.limesdr_soapysdr_source(

samp_rate=input_variables.SampleRate,

center_freq=input_variables.CenterFreq,

gain=input_variables.Gain,

sources=input_variables.NumArrayElements

)

self.tab = Qt.QTabWidget()

self.tab_widget_0 = Qt.QWidget()

self.tab_layout_0 = Qt.QBoxLayout(Qt.QBoxLayout.TopToBottom, self.tab_widget_0)

self.tab_grid_layout_0 = Qt.QGridLayout()

self.tab_layout_0.addLayout(self.tab_grid_layout_0)

self.tab.addTab(self.tab_widget_0, "Angle of Arrival")

self.top_layout.addWidget(self.tab)

self.doa_rootMUSIC_linear_array_0 = doa.rootMUSIC_linear_array(input_variables.NormSpacing, input_variables.NumTargets, input_variables.NumArrayElements)

self.doa_qt_compass_0 = doa.compass("", 0, 180, 10, 0)

self.top_layout.addLayout(self.doa_qt_compass_0.this_layout)

self.doa_autocorrelate_0 = doa.autocorrelate(input_variables.NumArrayElements, input_variables.SnapshotSize, input_variables.OverlapSize, 1)

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

# Connections

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

self.connect((self.doa_autocorrelate_0, 0), (self.doa_rootMUSIC_linear_array_0, 0))

self.connect((self.doa_rootMUSIC_linear_array_0, 0), (self.doa_qt_compass_0, 0))

for ch in range(input_variables.NumArrayElements) :

self.connect((self.limesdr, ch), (self.doa_autocorrelate_0, ch))

def closeEvent(self, event):

self.settings = Qt.QSettings("GNU Radio", "run_RootMUSIC_lin_array_LimeSDR")

self.settings.setValue("geometry", self.saveGeometry())

event.accept()

def main(top_block_cls=run_RootMUSIC_lin_array_LimeSDR, options=None):

from distutils.version import StrictVersion

if StrictVersion(Qt.qVersion()) >= StrictVersion("4.5.0"):

style = gr.prefs().get_string('qtgui', 'style', 'raster')

Qt.QApplication.setGraphicsSystem(style)

qapp = Qt.QApplication(sys.argv)

tb = top_block_cls()

tb.start()

tb.show()

def quitting():

tb.stop()

tb.wait()

qapp.connect(qapp, Qt.SIGNAL("aboutToQuit()"), quitting)

qapp.exec_()

if __name__ == '__main__':

main()

如果上面的代码下载困难，可从本站下载一份代码拷贝

gr-doa，armadillo-code，SoapySDR源代码下载，gr-osmosdr

至于测试的信号发送设备，任何能产生自相关信号的设备都可以，一般建议对讲机即可，注意频率要调整到一致才可以。

参考链接

ubuntu 16.04配置基于SSL的MySQL主从同步

首先参考 ubuntu 16.04配置MySQL主从同步实现同步,接下来执行如下操作

主数据库master配置

#生成加密证书，默认在/var/lib/mysql/下生成ca-key.pem，server-key.pem，client-key.pem

$ sudo mysql_ssl_rsa_setup --uid=mysql

#有时候需要手工修改一下证书所有者
$ sudo chown mysql:mysql /var/lib/mysql/*.pem

$ sudo ls -l /var/lib/mysql/ | grep .pem
-rw------- 1 mysql mysql 1679 Apr  2 22:53 ca-key.pem         #CA私钥
-rw-r--r-- 1 mysql mysql 1107 Apr  2 22:53 ca.pem             #自签的CA证书，客户端连接也需要提供
-rw-r--r-- 1 mysql mysql 1107 Apr  2 22:53 client-cert.pem    #客户端连接服务器端需要提供的证书文件
-rw------- 1 mysql mysql 1679 Apr  2 22:53 client-key.pem     #客户端连接服务器端需要提供的私钥文件
-rw------- 1 mysql mysql 1675 Apr  2 22:53 private_key.pem    #私钥/公钥对的私有成员
-rw-r--r-- 1 mysql mysql  451 Apr  2 22:53 public_key.pem     #私钥/公钥对的共有成员
-rw-r--r-- 1 mysql mysql 1107 Apr  2 22:53 server-cert.pem    #服务器端证书文件
-rw------- 1 mysql mysql 1679 Apr  2 22:53 server-key.pem     #服务器端私钥文件

#配置服务器
$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

$ sudo sed -i "s/# ssl-ca=\/etc\/mysql\/cacert.pem/ssl-ca=\/var\/lib\/mysql\/ca.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf

$ sudo sed -i "s/# ssl-cert=\/etc\/mysql\/server-cert.pem/ssl-cert=\/var\/lib\/mysql\/server-cert.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf

$ sudo sed -i "s/# ssl-key=\/etc\/mysql\/server-key.pem/ssl-key=\/var\/lib\/mysql\/server-key.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf

#重启MySQL服务
$ sudo service mysql restart

#查看服务器状态，是否已经启用SSL
$ mysql -u root -p -e "show global variables like '%ssl%'"
Enter password: 
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /var/lib/mysql/ca.pem          |
| ssl_capath    |                                |
| ssl_cert      | /var/lib/mysql/server-cert.pem |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /var/lib/mysql/server-key.pem  |
+---------------+--------------------------------+

#修改已存在用户,要求必须通过SSL才能同步,完成主从同步之后，从库可能会无法正常同步这个修改，需要手工跳过一个错误 "stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;start slave;"
$ mysql -u root -p -e "ALTER USER 'repl'@'182.254.149.39' REQUIRE SSL;"

# 阻止数据库记录写入，避免后期我们备份数据库的时候数据发生变动
# 该命令对于普通账号的只读模式，root 账号无效，因此访问数据库的账号
# 尽量不要使用root账号，如果是root 账号，只能暂时停止所有访问数据库的服务了
$ mysql -u root -p -e "set global read_only=1;"  

#查询并记录主库的同步位置
$ mysql -u root -p -e "SHOW MASTER STATUS;"
Enter password: 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000021 |   12144639 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 执行 ubuntu 16.04配置MySQL主从同步 http://www.mobibrw.com/?p=10541 里的备份脚本 
$ sudo bash backup_wordpress.sh

# 取消普通账号的只读模式
$ mysql -u root -p -e "set global read_only=0;"

#生成加密证书，默认在/var/lib/mysql/下生成ca-key.pem，server-key.pem，client-key.pem

$ sudo mysql_ssl_rsa_setup --uid=mysql

#有时候需要手工修改一下证书所有者

$ sudo chown mysql:mysql /var/lib/mysql/*.pem

$ sudo ls -l /var/lib/mysql/ | grep .pem

-rw------- 1 mysql mysql 1679 Apr 2 22:53 ca-key.pem #CA私钥

-rw-r--r-- 1 mysql mysql 1107 Apr 2 22:53 ca.pem #自签的CA证书，客户端连接也需要提供

-rw-r--r-- 1 mysql mysql 1107 Apr 2 22:53 client-cert.pem #客户端连接服务器端需要提供的证书文件

-rw------- 1 mysql mysql 1679 Apr 2 22:53 client-key.pem #客户端连接服务器端需要提供的私钥文件

-rw------- 1 mysql mysql 1675 Apr 2 22:53 private_key.pem #私钥/公钥对的私有成员

-rw-r--r-- 1 mysql mysql 451 Apr 2 22:53 public_key.pem #私钥/公钥对的共有成员

-rw-r--r-- 1 mysql mysql 1107 Apr 2 22:53 server-cert.pem #服务器端证书文件

-rw------- 1 mysql mysql 1679 Apr 2 22:53 server-key.pem #服务器端私钥文件

#配置服务器

$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

$ sudo sed -i "s/# ssl-ca=\/etc\/mysql\/cacert.pem/ssl-ca=\/var\/lib\/mysql\/ca.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf

$ sudo sed -i "s/# ssl-cert=\/etc\/mysql\/server-cert.pem/ssl-cert=\/var\/lib\/mysql\/server-cert.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf

$ sudo sed -i "s/# ssl-key=\/etc\/mysql\/server-key.pem/ssl-key=\/var\/lib\/mysql\/server-key.pem/g" /etc/mysql/mysql.conf.d/mysqld.cnf

#重启MySQL服务

$ sudo service mysql restart

#查看服务器状态，是否已经启用SSL

$ mysql -u root -p -e "show global variables like '%ssl%'"

Enter password:

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

| Variable_name | Value |

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

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /var/lib/mysql/ca.pem |

| ssl_capath | |

| ssl_cert | /var/lib/mysql/server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | /var/lib/mysql/server-key.pem |

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

#修改已存在用户,要求必须通过SSL才能同步,完成主从同步之后，从库可能会无法正常同步这个修改，需要手工跳过一个错误 "stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;start slave;"

$ mysql -u root -p -e "ALTER USER 'repl'@'182.254.149.39' REQUIRE SSL;"

# 阻止数据库记录写入，避免后期我们备份数据库的时候数据发生变动

# 该命令对于普通账号的只读模式，root 账号无效，因此访问数据库的账号

# 尽量不要使用root账号，如果是root 账号，只能暂时停止所有访问数据库的服务了

$ mysql -u root -p -e "set global read_only=1;"

#查询并记录主库的同步位置

$ mysql -u root -p -e "SHOW MASTER STATUS;"

Enter password:

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

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

| mysql-bin.000021 | 12144639 | | | |

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

# 执行 ubuntu 16.04配置MySQL主从同步 http://www.mobibrw.com/?p=10541 里的备份脚本

$ sudo bash backup_wordpress.sh

# 取消普通账号的只读模式

$ mysql -u root -p -e "set global read_only=0;"

从服务器slave配置

$ sudo mkdir /etc/mysql/ssl
#从服务端获取证书

$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/ca.pem /etc/mysql/ssl/

$ sudo chown mysql:mysql /etc/mysql/ssl/ca.pem

$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/client-cert.pem /etc/mysql/ssl/

$ sudo chown mysql:mysql /etc/mysql/ssl/client-cert.pem

$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/client-key.pem /etc/mysql/ssl/

$ sudo chown mysql:mysql /etc/mysql/ssl/client-key.pem

$ mysql -u root -p -e "stop slave;"

#获取最后同步的位置，为后续恢复进行准备
$ mysql -u root -p -e "show slave status\G;" | grep Exec_Master_Log_Pos:
Enter password:
          Exec_Master_Log_Pos: 12178842

$ mysql -u root -p -e "show slave status\G;" | grep Master_Log_File
Enter password:
              Master_Log_File: mysql-bin.000021
        Relay_Master_Log_File: mysql-bin.000021

#修改同步信息
$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='www.mobibrw.com', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000021',MASTER_LOG_POS=12178842 ,master_ssl=1,master_ssl_ca='/etc/mysql/ssl/ca.pem', master_ssl_capath='/etc/mysql/ssl', master_ssl_cert='/etc/mysql/ssl/client-cert.pem', master_ssl_key='/etc/mysql/ssl/client-key.pem';"

$ mysql -u root -p -e "start slave;"

#查看从库状态
$ mysql -u root -p -e "show slave status\G;"
Enter password:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: www.mobibrw.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000021
          Read_Master_Log_Pos: 12701244
               Relay_Log_File: VM-xxx-xxx-xxxxx-relay-bin.000003
                Relay_Log_Pos: 195033
        Relay_Master_Log_File: mysql-bin.000021
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 12701244
              Relay_Log_Space: 523107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ssl/ca.pem
           Master_SSL_CA_Path: /etc/mysql/ssl
              Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/ssl/client-key.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

$ sudo mkdir /etc/mysql/ssl

#从服务端获取证书

$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/ca.pem /etc/mysql/ssl/

$ sudo chown mysql:mysql /etc/mysql/ssl/ca.pem

$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/client-cert.pem /etc/mysql/ssl/

$ sudo chown mysql:mysql /etc/mysql/ssl/client-cert.pem

$ sudo scp -P 22 -r root@www.mobibrw.com:/var/lib/mysql/client-key.pem /etc/mysql/ssl/

$ sudo chown mysql:mysql /etc/mysql/ssl/client-key.pem

$ mysql -u root -p -e "stop slave;"

#获取最后同步的位置，为后续恢复进行准备

$ mysql -u root -p -e "show slave status\G;" | grep Exec_Master_Log_Pos:

Enter password:

Exec_Master_Log_Pos: 12178842

$ mysql -u root -p -e "show slave status\G;" | grep Master_Log_File

Enter password:

Master_Log_File: mysql-bin.000021

Relay_Master_Log_File: mysql-bin.000021

#修改同步信息

$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='www.mobibrw.com', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000021',MASTER_LOG_POS=12178842 ,master_ssl=1,master_ssl_ca='/etc/mysql/ssl/ca.pem', master_ssl_capath='/etc/mysql/ssl', master_ssl_cert='/etc/mysql/ssl/client-cert.pem', master_ssl_key='/etc/mysql/ssl/client-key.pem';"

$ mysql -u root -p -e "start slave;"

#查看从库状态

$ mysql -u root -p -e "show slave status\G;"

Enter password:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: www.mobibrw.com

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000021

Read_Master_Log_Pos: 12701244

Relay_Log_File: VM-xxx-xxx-xxxxx-relay-bin.000003

Relay_Log_Pos: 195033

Relay_Master_Log_File: mysql-bin.000021

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 12701244

Relay_Log_Space: 523107

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: Yes

Master_SSL_CA_File: /etc/mysql/ssl/ca.pem

Master_SSL_CA_Path: /etc/mysql/ssl

Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem

Master_SSL_Cipher:

Master_SSL_Key: /etc/mysql/ssl/client-key.pem

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

注意，偶尔在系统升级的时候，从库可能会丢失同步状态配置。这时候，我们需要重新同步，此时我们从从设备/var/lib/mysql/master.info中找到被中断的同步点。

里面的内容一般如下：

25
mysql-bin.000582
4765083
www.mobibrw.com
xxxx
xxxx
3306
60
1
/etc/mysql/ssl/ca.pem
/etc/mysql/ssl
/etc/mysql/ssl/client-cert.pem

/etc/mysql/ssl/client-key.pem
0
30.000

0
0b674082-f01d-11e9-8f8c-00163e0a4ffe
86400


0

mysql-bin.000582

4765083

www.mobibrw.com

xxxx

3306

/etc/mysql/ssl/ca.pem

/etc/mysql/ssl

/etc/mysql/ssl/client-cert.pem

/etc/mysql/ssl/client-key.pem

30.000

0b674082-f01d-11e9-8f8c-00163e0a4ffe

86400

注意mysql-bin.000582下面的4765083就是同步位置，在恢复的时候，就是这两个关键数据。

参考链接

ubuntu 16.04屏幕录像软件－Kazam

ubuntu 16.04系统上最好用的开源屏幕录像软件－Kazam，安装方式如下：

$ sudo apt-get install kazam

1	$ sudo apt-get install kazam

启动命令:

$ kazam

$ kazam

继续阅读ubuntu 16.04屏幕录像软件－Kazam

ubuntu 16.04获取当前安装的gnuradio的版本

使用如下命令获取

$ gnuradio-companion --version
GNU Radio Companion 3.7.9

This program is part of GNU Radio
GRC comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to redistribute it.

$ gnuradio-companion --version

GNU Radio Companion 3.7.9

This program is part of GNU Radio

GRC comes with ABSOLUTELY NO WARRANTY.

This is free software, and you are welcome to redistribute it.

或者如下命令也可

$ gnuradio-config-info --version
3.7.9

1 2	$ gnuradio-config-info --version 3.7.9

参考链接

GNU Radio version

ubuntu 16.04编译安装gqrx-v2.11.2

$ sudo apt-get install qt5-qmake

$ sudo apt-get install qtbase5-dev

$ sudo apt-get install pkg-config

$ sudo apt-get install libqt5svg5-dev

$ export QT_SELECT=qt5

$ cd ~

$ git clone https://github.com/csete/gqrx.git

$ cd gqrx

$ git checkout v2.11.2

$ mkdir build

$ cd build

$ qmake ../gqrx.pro

$ make

$ sudo apt-get install qt5-qmake

$ sudo apt-get install qtbase5-dev

$ sudo apt-get install pkg-config

$ sudo apt-get install libqt5svg5-dev

$ export QT_SELECT=qt5

$ cd ~

$ git clone https://github.com/csete/gqrx.git

$ cd gqrx

$ git checkout v2.11.2

$ mkdir build

$ cd build

$ qmake ../gqrx.pro

$ make

如果代码下载困难，可以从本站下载一份拷贝。 gqrx

参考链接

ubuntu 16.04编译安装HackRF One软件及固件hackrf-v2018.01.1

$ cd ~

$ git clone https://github.com/mossmann/hackrf.git

$ cd hackrf

$ git checkout v2018.01.1 

#编译工具
$ cd host

$ mkdir build

$ cd build

$ cmake .. -DINSTALL_UDEV_RULES=ON

$ make

$ sudo make install

#编译固件
$ sudo apt-get install gcc-arm-none-eabi

$ cd ~

$ cd hackrf

$ git submodule init

$ cd firmware

$ cd libopencm3

$ make 

$ cd ..

$ mkdir build 

$ cd build

$ cmake .. -DBOARD=HACKRF_ONE

$ make


#升级固件
$ hackrf_spiflash -w ./hackrf_usb/hackrf_usb.bin

$ cd ~

$ git clone https://github.com/mossmann/hackrf.git

$ cd hackrf

$ git checkout v2018.01.1

#编译工具

$ cd host

$ mkdir build

$ cd build

$ cmake .. -DINSTALL_UDEV_RULES=ON

$ make

$ sudo make install

#编译固件

$ sudo apt-get install gcc-arm-none-eabi

$ cd ~

$ cd hackrf

$ git submodule init

$ cd firmware

$ cd libopencm3

$ make

$ cd ..

$ mkdir build

$ cd build

$ cmake .. -DBOARD=HACKRF_ONE

$ make

#升级固件

$ hackrf_spiflash -w ./hackrf_usb/hackrf_usb.bin

如果代码下载困难，可以从这里下载一份代码拷贝 hackrf

参考链接

ubuntu 16.04系统wordpress-4.9.4修改表引擎报告错误“Invalid default value for 'comment_date'”

最近在捣鼓wordpress主从同步的时候（ubuntu 16.04配置MySQL主从同步），需要把wp_comments的数据库引擎从MyISAM切换到INNODB（MyISAM不支持主从同步）。

在执行

$ mysql -u root -p -e "use wordpress; ALTER TABLE wp_comments ENGINE=INNODB;"

1	$ mysql -u root -p -e "use wordpress; ALTER TABLE wp_comments ENGINE=INNODB;"

的时候报告错误：

Invalid default value for 'comment_date'

1	Invalid default value for 'comment_date'

原因出在类似这样的建表语句

DROP TABLE IF EXISTS `wp_comments`;
CREATE TABLE `wp_comments`  (
  `comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_author_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT 0,
  `comment_approved` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`comment_ID`) USING BTREE,
  INDEX `comment_post_ID`(`comment_post_ID`) USING BTREE,
  INDEX `comment_approved_date_gmt`(`comment_approved`, `comment_date_gmt`) USING BTREE,
  INDEX `comment_date_gmt`(`comment_date_gmt`) USING BTREE,
  INDEX `comment_parent`(`comment_parent`) USING BTREE,
  INDEX `comment_author_email`(`comment_author_email`(10)) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `wp_comments`;

CREATE TABLE `wp_comments` (

`comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT 0,

`comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,

`comment_author_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_author_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

`comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

`comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,

`comment_karma` int(11) NOT NULL DEFAULT 0,

`comment_approved` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '1',

`comment_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT 0,

`user_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,

PRIMARY KEY (`comment_ID`) USING BTREE,

INDEX `comment_post_ID`(`comment_post_ID`) USING BTREE,

INDEX `comment_approved_date_gmt`(`comment_approved`, `comment_date_gmt`) USING BTREE,

INDEX `comment_date_gmt`(`comment_date_gmt`) USING BTREE,

INDEX `comment_parent`(`comment_parent`) USING BTREE,

INDEX `comment_author_email`(`comment_author_email`(10)) USING BTREE

) ENGINE = MyISAM AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci ROW_FORMAT = Dynamic;

这种报错多是mysql升级到5.7而引起的默认值不兼容的问题。看看你的字段名是什么，我的是时间字段，类型是datetime。想到可能是类型的默认值被限制了，查看sql_mode。果然：NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制时间不能为0。

可以使用如下语句查看建表命令：

$ mysql -u root -p -e "use wordpress;show create table wp_comments\G;“

1	$ mysql -u root -p -e "use wordpress;show create table wp_comments\G;“

注意上面的

`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00', 
`comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

1 2	`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

这两句受到NO_ZERO_IN_DATE,NO_ZERO_DATE的影响。

查看 sql_mode

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> show variables like 'sql_mode';

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

| Variable_name | Value |

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

| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

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

1 row in set (0.00 sec)

mysql>

临时修改：

mysql> set session
 -> sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> set session

-> sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

永久修改：

修改配置文件

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

1	$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

在[mysqld]下面添加如下列：

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1	sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

参考链接

导入数据库时报错1067 – Invalid default value for ‘字段名’

ubuntu 16.04删除被denyhosts阻止的IP地址

参照 ubuntu 16.04防止SSH暴力登录攻击安装denyhosts之后，由于某些莫名的操作导致自己的一个登录地址被加入了阻止列表，尝试很多次之后，都没有办法恢复。于是找到如下脚本来进行删除操作：

#!/bin/bash

IP=$1
if [ -n "$IP" ];then
    if [[ $IP =~ ^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$ ]];then
        service denyhosts stop
        sed -i "/$IP/d" /etc/hosts.deny
        sed -i "/$IP/d" /var/lib/denyhosts/hosts-valid
        sed -i "/$IP/d" /var/lib/denyhosts/users-hosts
        sed -i "/$IP/d" /var/lib/denyhosts/hosts
        sed -i "/$IP/d" /var/lib/denyhosts/hosts-root
        sed -i "/$IP/d" /var/lib/denyhosts/hosts-restricted
        iptables -D INPUT -s $IP -j DROP 
        echo $IP remove from Denyhosts
        service denyhosts start
    else
        echo "This is not IP"
    fi
else
    echo "IP is empty"
fi

#!/bin/bash

IP=$1

if [ -n "$IP" ];then

if [[ $IP =~ ^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$ ]];then

service denyhosts stop

sed -i "/$IP/d" /etc/hosts.deny

sed -i "/$IP/d" /var/lib/denyhosts/hosts-valid

sed -i "/$IP/d" /var/lib/denyhosts/users-hosts

sed -i "/$IP/d" /var/lib/denyhosts/hosts

sed -i "/$IP/d" /var/lib/denyhosts/hosts-root

sed -i "/$IP/d" /var/lib/denyhosts/hosts-restricted

iptables -D INPUT -s $IP -j DROP

echo $IP remove from Denyhosts

service denyhosts start

else

echo "This is not IP"

else

echo "IP is empty"

生成脚本后，如下方式执行:

$ sudo bash denyhosts_unban.sh 42.120.74.106

1	$ sudo bash denyhosts_unban.sh 42.120.74.106

2018 年 4 月
一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

安装依赖的软件包

下载最新的gr-doa源代码

参考链接

主数据库master配置

从服务器slave配置

参考链接

参考链接

参考链接

参考链接

查看 sql_mode

临时修改：

永久修改：

参考链接

参考链接

下载最新的`gr-doa`源代码