centos mysql bind_centos7搭建MySQL+BIND-dlz

环境说明

Linux：centos 7.4.1708

Mysql: Ver 15.1 Distrib 10.1.32-MariaDB

BIND: 9.12.1

安装mysql

创建/etc/yum.repos.d/MariaDB.repo文件

> cat /etc/yum.repos.d/mariadb.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.1/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

yum安装 mariadb

yum install -y MariaDB-server MariaDB-client MariaDB-devel

修改配置文件

> cat /etc/my.cnf.d/mysql-clients.cnf

[client]

port = 3306

socket = /data/db/mysql/mysql.sock

[mysql]

no-auto-rehash

[mysqldump]

quick

max_allowed_packet = 64M

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

> cat /etc/my.cnf.d/server.cnf

[mysqld]

port = 3306

datadir = /data/db/mysql

socket = /data/db/mysql/mysql.sock

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

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 64M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 8

log-bin=mysql-bin

binlog_format=mixed

server-id = 1

default_storage_engine = InnoDB

max_allowed_packet = 256M

max_connections = 2048

open_files_limit = 65535

skip-name-resolve

lower_case_table_names=1

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

init_connect='SET NAMES utf8mb4'

初始化

mkdir /data/db/mysql

chown mysql.mysql -R /data/db/mysql

mysql_install_db --defaults-file=/etc/my.cnf --datadir=/data/db/mysql/ --user=mysql

启动

systemctl start mysql

授权以及设置字符集

mysql

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '123456' WITH GRANT OPTION;

MariaDB [(none)]> create database binddns;

MariaDB [(none)]> GRANT ALL PRIVILEGES ON binddns.* TO 'bind'@'%' IDENTIFIED BY '123456';

安装bind

下载

wget https://www.isc.org/downloads/file/bind-9-12-1/?version=tar-gz

tar xvf bind-9.12.1.tar.gz

cd xvf bind-9.12.1

安装

useradd -s /sbin/nologin -M named

./configure --with-dlz-mysql=/usr --enable-largefile --enable-threads=yes --prefix=/usr/local/bind --with-openssl

make -j 4

make install

普通配置

环境变量

echo "export PATH=${PATH}:/usr/local/bind/sbin/:/usr/local/bind/bin/" >> /etc/profile

source /etc/profile

配置rndc 配置named.conf

cd /usr/local/bind/etc/

rndc-confgen -r /dev/urandom >rndc.conf

head -5 rndc.conf >named.conf

wget http://www.internic.net/domain/named.root

添加其他配置

controls {

inet 127.0.0.1 port 953

allow { 127.0.0.1; } keys { "rndc-key"; };

};

zone "." IN {

type hint;

file "/usr/local/bind/etc/named.root";

};

options {

tcp-clients 50000;

directory "/usr/local/bind/var";

pid-file "/usr/local/bind/var/bind.pid";

dump-file "/usr/local/bind/var/bind_dump.db";

statistics-file "/usr/local/bind/var/bind.stats";

rate-limit {

nxdomains-per-second 3;

window 1;

};

notify yes;

recursion yes;

version "ooxx-bind:1.0.24";

allow-notify { none; };

allow-recursion { any; };

allow-transfer { none; };

allow-query { any; };

};

logging {

channel bind_log {

file "/usr/local/bind/log/bind.log" versions 3 size 20m;

severity info;

print-time yes;

print-severity yes;

print-category yes;

};

category default {

bind_log;

};

};

include "/usr/local/bind/etc/default.zones";

新建default.zones

> cat default.zones

zone "ooxx.com" IN {

type master;

file "/usr/local/bind/zones/ooxx.com.zone";

};

zone "1.1.1.in-addr.arpa" IN {

type master;

file "/usr/local/bind/zones/1.1.1.zone";

};

配置解析域文件

> mkdir /usr/local/bind/zones && cd $!

> cat ooxx.com.zone

$TTL 1D

@ IN SOA ooxx.com. admin.ooxx.com. ( 0 1D 1H 1W 3H )

NS ns1.ooxx.com.

NS ns2.ooxx.com.

A 127.0.0.1

AAAA ::1

MX 10 mx.ooxx.com.

ttl IN A 1.1.1.22

www IN A 1.1.1.33

bbs IN CNAME www

mx IN A 1.1.1.66

ns1 IN A 1.1.1.11

ns2 IN A 1.1.1.11

> cat 1.1.1.zone

$TTL 1D

@ IN SOA ooxx.com. admin.ooxx.com. ( 0 2H 10M 7D 1D )

NS ttl.ooxx.com.

A 127.0.0.1

AAAA ::1

22 IN PTR ooxx.com

33 IN PTR www.ooxx.com.

11 IN PTR ns1.ooxx.com.

11 IN PTR ns2.ooxx.com.

66 IN PTR mx.ooxx.com.

测试

mkdir /usr/local/bind/log/

chown -R named:named /usr/local/bind

找个windows，改下dns,访问测试

dlz 配置

添加mysql数据

create database bind;

# 新建record表

CREATE TABLE IF NOT EXISTS records (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

zone varchar(255) NOT NULL,

host varchar(255) NOT NULL,

type enum('A','MX','CNAME','NS','SOA','PTR','TXT','AAAA','SVR','URL') NOT NULL,

data varchar(255) NOT NULL,

ttl int(11) NOT NULL,

mx_priority int(11) DEFAULT NULL,

refresh int(11) DEFAULT NULL,

retry int(11) DEFAULT NULL,

expire int(11) DEFAULT NULL,

minimum int(11) DEFAULT NULL,

serial bigint(20) DEFAULT NULL,

resp_person varchar(64) DEFAULT NULL,

primary_ns varchar(64) DEFAULT NULL,

PRIMARY KEY (id),

KEY type (type),

KEY host (host),

KEY zone (zone)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

# 新建acl表

CREATE TABLE IF NOT EXISTS acl (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

zone varchar(255) NOT NULL,

client varchar(255) NOT NULL,

PRIMARY KEY (id),

KEY client (client),

KEY zone (zone)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

修改配置文件

dlz "mysql-dlz" {

database "mysql

{host=127.0.0.1 dbname=binddns ssl=false port=3306 user=bind pass=123456}

{select name from bind_domain where name = '$zone$' limit 1}

{select ttl, type, mx, case when lower(type)='txt' then concat('\"', value, '\"') when lower(type) = 'soa' then concat_ws(' ', value, serial, refresh, retry, expire, minimum) else value end from bind_record where domain_id = (select id from bind_domain where name = '$zone$') and name = '$record$'}

{}

{select ttl, type, name, mx, data from bind_record where domain_id = (select id from bind_domain where name = '$zone$') and not (type = 'SOA' or type = 'NS')}";

};

# include "/usr/local/bind/etc/default.zones";

第一次写host=localhost,报错：mysql driver failed to create database connection after 4 attempts,后改成host=127.0.0.1就好了

插入数据

INSERT INTO bind.records ( zone, HOST, type, DATA, ttl ) VALUES ( 'ooxx.com', 'www', 'A', '1.1.1.22', '600' );

INSERT INTO bind.records ( zone, HOST, type, DATA, ttl ) VALUES ( 'ooxx.com', 'mail', 'CNAME', 'www', '600' );

INSERT INTO bind.records ( zone, HOST, type, DATA, ttl ) VALUES ( 'ooxx.com', '@', 'NS', 'ns', '60' );

INSERT INTO bind.records ( zone, HOST, type, DATA, ttl ) VALUES ( 'ooxx.com', 'ns', 'A', '1.1.1.11', '600' );

启动

named -u named -n1 -g -d1

没看到报错，说明配置没问题

测试

找个windows，改下dns,访问测试

测试结果dlz比文件要慢

测试日志, 可以看到查询过程

Query String: select zone from records where zone = '11.1.1.1.in-addr.arpa' limit 1

14-Apr-2018 02:20:40.595

Query String: select zone from records where zone = '1.1.1.in-addr.arpa' limit 1

14-Apr-2018 02:20:40.595

Query String: select zone from records where zone = '1.1.in-addr.arpa' limit 1

14-Apr-2018 02:20:40.595

Query String: select zone from records where zone = '1.in-addr.arpa' limit 1

14-Apr-2018 02:20:40.595

Query String: select zone from records where zone = 'in-addr.arpa' limit 1

14-Apr-2018 02:20:40.595

Query String: select zone from records where zone = 'arpa' limit 1

14-Apr-2018 02:20:40.596

Query String: select zone from records where zone = 'www.ooxx.com' limit 1

14-Apr-2018 02:20:40.597

Query String: select zone from records where zone = 'ooxx.com' limit 1

14-Apr-2018 02:20:40.597

Query String: select ttl, type, mx_priority, case when lower(type)='txt' then concat('"', data, '"') when lower(type) = 'soa' then concat_ws(' ', data, resp_person, serial, refresh, retry, expire, minimum) else data end from records where zone = 'ooxx.com' and host = '@'

14-Apr-2018 02:20:40.597

Query String: select ttl, type, mx_priority, case when lower(type)='txt' then concat('"', data, '"') when lower(type) = 'soa' then concat_ws(' ', data, resp_person, serial, refresh, retry, expire, minimum) else data end from records where zone = 'ooxx.com' and host = 'www'

bind systemd启动文件

> cat /usr/lib/systemd/system/named.service

[Unit]

Description=Internet domain name server

After=network.target

[Service]

ExecStart=/usr/local/bind/sbin/named -f -u named -4

ExecReload=/usr/local/bind/sbin/rndc reload

ExecStop=/usr/local/bind/sbin/rndc stop

[Install]

WantedBy=multi-user.target

Alias=bind.service

> ln -s /usr/lib/systemd/system/named.service /etc/systemd/system/bind.service

> systemctl daemon-reload

> systemctl start bind