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

下载最新的gr-doa源代码

#编译安装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

具体代码如下:

# -*- 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"

编译代码

$ 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()

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

gr-doaarmadillo-codeSoapySDR源代码下载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;"

从服务器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:

注意,偶尔在系统升级的时候,从库可能会丢失同步状态配置。这时候,我们需要重新同步,此时我们从从设备`/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`就是同步位置,在恢复的时候,就是这两个关键数据。

参考链接


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-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

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

参考链接


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

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

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

参考链接


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

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

在执行

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

的时候报告错误:

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;

这种报错多是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;“

注意上面的

`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> 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

[mysqld]下面添加如下列:

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

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

$ sudo bash denyhosts_unban.sh 42.120.74.106

参考链接


ubuntu 16.04配置MySQL主从同步

准备工作

1.主从数据库版本最好一致

2.主从数据库内数据保持一致

主数据库:121.199.27.227 /ubuntu 16.04 MySQL 5.7.21 (阿里云

从数据库:182.254.149.39 /ubuntu 16.04 MySQL 5.7.21 (腾讯云

防火墙配置

配置主服务器只允许特定IP访问数据库的端口,避免不必要的攻击。

主库防火墙配置
# iptables -A INPUT -p tcp -s slave_ip --dport 3306 -j ACCEPT

#删除可能已经存在的配置,避免出现多条重复记录
$ sudo iptables -D INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT 
$ sudo iptables -D INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT 
$ sudo iptables -D INPUT -p tcp --dport 3306 -j DROP 
$ sudo iptables -D INPUT -p udp --dport 3306 -j DROP 
$ sudo iptables -D INPUT -p sctp --dport 3306 -j DROP

#增加配置,只允许特定地址访问数据库端口
$ sudo iptables -A INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT
$ sudo iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
$ sudo iptables -A INPUT -p tcp --dport 3306 -j DROP
$ sudo iptables -A INPUT -p udp --dport 3306 -j DROP
$ sudo iptables -A INPUT -p sctp --dport 3306 -j DROP

$ sudo iptables -L -n -v

#保存配置
$ sudo apt-get install iptables-persistent

#注意,iptables-persistent 与 ufw 冲突, 
#现象就是系统重启后执行 sudo ufw status 显示 inactive,
#但是sudo systemctrl ufw status 或sudo service ufw status 显示服务正常,
#实际上ufw并没有正常工作。
#如果两者同时安装,需要参考 https://www.mobibrw.com?p=29330 进行配置

$ sudo netfilter-persistent save

#配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面,
#最好确认一下实际保存的内容,尤其是安装了denyhosts等其他安全软件的情况下,
#可能会记录了多余的规则,需要手工删除
从库防火墙配置
# iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT

#删除可能已经存在的配置,避免出现多条重复记录
$ sudo iptables -D OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT

#增加配置
$ sudo iptables -A OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT

$ sudo iptables -L -n

#保存配置
$ sudo apt-get install iptables-persistent

#注意,iptables-persistent 与 ufw 冲突, 
#现象就是系统重启后执行 sudo ufw status 显示 inactive,
#但是sudo systemctrl ufw status 或sudo service ufw status 显示服务正常,
#实际上ufw并没有正常工作。
#如果两者同时安装,需要参考 https://www.mobibrw.com?p=29330 进行配置

$ sudo netfilter-persistent save

#配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面,
#最好确认一下实际保存的内容,尤其是安装了denyhosts等其他安全软件的情况下,
#可能会记录了多余的规则,需要手工删除

主数据库master配置

1.修改mysql配置

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

在[mysqld]部分进行如下修改:

[mysqld]
#开启二进制日志,默认是注释掉的,我们去掉注释
log-bin = /var/log/mysql/mysql-bin.log 

#设置server-id
server-id = 1 

#默认是127.0.0.1,此处我们设置为任意地址,放开远程访问,这么操作之前一定要确保防火墙配置正确,否则会产生安全风险
bind-address = 0.0.0.0 

#如果数据库是从5.7版本之前升级的,并且是wordpress那么会遇到无法更改数据库的情况,
#NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制的,我们需要去掉这个限制,原因在于
#wordpress创建的表中存在
#`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
#这样的定义是没办法进行后续的操作的,因此我们需要重新定义sql_mode来解除这个限制
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.修改需要同步的表的引擎为INNODB,只有INNODB支持主从,MyISAM不支持

# 此处以wordpress为例,默认情况下wordpress中的wp_options表为MyISAM引擎,
# 这会导致数据同步失败,可能出现的错误信息如下:
# Last_Errno: 1032
# Last_Error: Could not execute Delete_rows event on table wordpress.wp_options; Can't find record in 'wp_options', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos 3678486
# 使用“show create table wp_options\G;”查看表的引擎信息
# 单个表修改的命令如下
#mysql -u root -p -e "use wordpress; ALTER TABLE wp_options ENGINE=INNODB;"

#整个数据库修改的命令如下,下面的语句只是生成执行语句,具体执行,还需要把结果拷贝出来执行
$ mysql -u root -p -e "USE wordpress; SET @DATABASE_NAME = 'wordpress'; SELECT GROUP_CONCAT(CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=InnoDB; ') SEPARATOR '' ) FROM information_schema.TABLES AS t WHERE TABLE_SCHEMA = @DATABASE_NAME AND TABLE_TYPE = 'BASE TABLE' AND ENGINE = 'MyISAM';"

#我这边的例子如下
$ mysql -u root -p -e "USE wordpress; ALTER TABLE wp_IPBLC_blacklist ENGINE=InnoDB; ALTER TABLE wp_IPBLC_login_failed ENGINE=InnoDB; ALTER TABLE wp_IPBLC_usernames ENGINE=InnoDB; ALTER TABLE wp_commentmeta ENGINE=InnoDB; ALTER TABLE wp_comments ENGINE=InnoDB; ALTER TABLE wp_links ENGINE=InnoDB; ALTER TABLE wp_options ENGINE=InnoDB; ALTER TABLE wp_postmeta ENGINE=InnoDB; ALTER TABLE wp_posts ENGINE=InnoDB; ALTER TABLE wp_term_relationships ENGINE=InnoDB; ALTER TABLE wp_term_taxonomy ENGINE=InnoDB; ALTER TABLE wp_terms ENGINE=InnoDB; ALTER TABLE wp_usermeta ENGINE=InnoDB; ALTER TABLE wp_users ENGINE=InnoDB;"

3.重启mysql,创建用于同步的用户账号

创建用户并授权:用户:repl 密码:slavepass

$ sudo service mysql restart

$ mysql -u root -p -e "CREATE USER 'repl'@'182.254.149.39' IDENTIFIED BY 'slavepass';" #创建用户

$ mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'182.254.149.39';" #分配权限

$ mysql -u root -p -e "flush privileges;"  #刷新权限

$ mysql -u root -p -e "SELECT User, Host FROM mysql.user;"  #查看用户

4.查看master状态,记录二进制文件名(mysql-bin.000001)和位置(333802):

# 阻止数据库记录写入,避免后期我们备份数据库的时候数据发生变动
# 该命令对于普通账号的只读模式,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.000001 |   333802 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

5.主库备份,为从库的第一次数据同步准备数据

使用如下脚本产生数据库备份文件

#此处以备份wordpress数据库为例子

datadump=`which mysqldump`

mysqluser="root"

userpass="password" 

wordpressdb="wordpress"

backupwordpress_sql=$wordpressdb.`date +%Y%m%d`.sql

# 注意,如果是MySQL 8.x那么 --master-data 需要修改成 --source-data 否则在备份的第一句会出现如下警告 
#“WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.”

ver=`mysqldump -V | sed -nre 's/^[^0-9]*(([0-9]+\.)*[0-9]+).*/\1/p'`

args='--master-data'

if [ "${ver}"\>="8.0.0" ]; then
  args='--source-data'
fi

if $datadump $args --single-transaction -u $mysqluser --password=$userpass -h localhost --opt $wordpressdb > $backupwordpress_sql 2>&1
then
  echo " backup $wordpressdb success"
else
  echo " backup $wordpressdb error"
  exit 1
fi

#检验文件尾部是否存在 “-- Dump completed on”,如果存在不存在,则说明备份出错了。
if [ 0 -eq "$(sed '/^$/!h;$!d;g' $backupwordpress_sql | grep -c "Dump completed on")" ]; 
then
  echo " backup $wordpressdb error"
  exit 1	
else
  echo " backup $wordpressdb success"
fi

执行脚本,确保最后输出备份成功

$ cd ~

$ sudo bash backup_wordpress.sh

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

从服务器slave配置

1.修改mysql配置

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

修改server-id,每个数据库的server-id要求是唯一的,不能相互冲突

[mysqld]
#设置server-id,必须唯一
server-id = 2

# 根据业务需要配置数据库是否只读
#read_only = on
#super_read_only = on
#tx_read_only = on

#日志也最好打开
log_bin                 = /var/log/mysql/mysql-bin.log

#如果日志开启了,最好把日志格式设置为row格式,这样如果主从数据不一致,可以尝试mysql flashback功能
binlog-format    = row 

#如果数据库是从5.7版本之前升级的,并且是wordpress那么会遇到无法更改数据库的情况,
#NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制的,我们需要去掉这个限制,原因在于
#wordpress创建的表中存在
#`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
#这样的定义是没办法进行后续的操作的,因此我们需要重新定义sql_mode来解除这个限制
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.首次还原数据库:

#停止可能访问数据库的应用
$ sudo service apache2 stop

$ sudo service php7.0-fpm stop

$ sudo service mysql restart

# 确保无应用访问数据库的情况下,记录主库日志位置,并执行备份脚本
 mysql -u root -p -e "SHOW MASTER STATUS;"
Enter password: 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |   333802 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

$ sudo bash backup_wordpress.sh

# 此处已经可以启动刚刚关闭的服务了
$ sudo service php7.0-fpm start

$ sudo service apache2 start

$ scp -P 22 -r root@121.199.27.227:~/wordpress.*.sql ./

#删除可能存在的一行警告信息,这行警告信息可能导致我们无法恢复数据
$ sed -i "/^mysqldump: \[Warning\] Using a password on the command line interface can be insecure\./d" wordpress.*.sql

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

$ mysql -u root -p -e "drop database wordpress;"

$ mysql -u root -p -e "create database wordpress;"

$ mysql -u root -p wordpress < wordpress.*.sql

还原完成后,把数据库设置成只读模式,如果从库可写会出现冲突导致同步失败

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

添加如下语句:

[mysqld]
#数据库只读 
#read_only = on 

super_read_only = on 

#tx_read_only = on

3.重启mysql,执行同步SQL语句(需要主服务器主机名,登陆凭据,二进制文件的名称和位置):

$ sudo service mysql restart

#只读模式
$ mysql -u root -p -e "set global read_only=1;"

#最好使用如下命令获得主库的起始位置
$ grep 'CHANGE MASTER TO MASTER_LOG_FILE'  wordpress.*.sql | more

$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='121.199.27.227', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=333802;"

4.启动slave同步进程:

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

5.查看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: 121.199.27.227
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 9448236
               Relay_Log_File: VM-114-251-ubuntu-relay-bin.000002
                Relay_Log_Pos: 17780
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
        ...

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了,比如在主master数据库的test数据库的一张表中插入一条数据,在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效,还可以关闭slave(mysql>stop slave;),然后再修改master,看slave是否也相应修改(停止slave后,master的修改不会同步到slave),就可以完成主从复制功能的验证了。

还可以用到的其他相关参数:

master开启二进制日志后默认记录所有库所有表的操作,可以通过配置来指定只记录指定的数据库甚至指定的表的操作,具体在mysql配置文件的[mysqld]可添加修改如下选项:

# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
  
# 只同步哪些数据库,除此之外,其他不同步  
binlog-do-db = game

如之前查看master状态时就可以看到只记录了test库,忽略了manual和mysql库。

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

里面的内容一般如下:

25
mysql-bin.000582
4765083
121.199.27.227
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`就是同步位置,在恢复的时候,就是这两个关键数据。

参考链接