docs/guides/basic-database.rst

   1 Basic setup: configuring database connectivity
   2 ==============================================
   3
   4 This shows you how to configure the Generic MySQL backend. This backend
   5 is called 'gmysql', and needs to be configured in ``pdns.conf``. Add the
   6 following lines, adjusted for your local setup (specifically, you may
   7 not want to use the 'root' user):
   8
   9 ::
  10
  11     launch=gmysql
  12     gmysql-host=127.0.0.1
  13     gmysql-user=root
  14     gmysql-dbname=pdns
  15     gmysql-password=mysecretpassword
  16
  17 Remove any earlier :ref:`setting-launch` statements and
  18 other configuration statements for backends.
  19
  20 .. warning::
  21   Make sure that you can actually resolve the hostname of
  22   your database without accessing the database! It is advised to supply an
  23   IP address here to prevent chicken/egg problems!
  24
  25 Now start PowerDNS in the foreground:
  26
  27 ::
  28
  29     # /usr/sbin/pdns_server --daemon=no --guardian=no --loglevel=9
  30     (...)
  31     Dec 30 13:40:09 About to create 3 backend threads for UDP
  32     Dec 30 13:40:09 gmysql Connection failed: Unable to connect to database: Access denied for user 'hubert'@'localhost' to database 'pdns-non-existant'
  33     Dec 30 13:40:09 Caught an exception instantiating a backend: Unable to launch gmysql connection: Unable to connect to database: Access denied for user 'hubert'@'localhost' to database 'pdns-non-existant'
  34     Dec 30 13:40:09 Cleaning up
  35     Dec 30 13:40:10 Done launching threads, ready to distribute questions
  36
  37 This is as to be expected - we did not yet add anything to MySQL for
  38 PowerDNS to read from. At this point you may also see other errors which
  39 indicate that PowerDNS either could not find your MySQL server or was
  40 unable to connect to it. Fix these before proceeding.
  41
  42 General MySQL knowledge is assumed in this chapter, please do not
  43 interpret these commands as DBA advice!
  44
  45 Example: configuring MySQL
  46 --------------------------
  47
  48 Connect to MySQL as a user with sufficient privileges and issue the
  49 following commands:
  50
  51 .. literalinclude:: ../../modules/gmysqlbackend/schema.mysql.sql
  52
  53 Now we have a database and an empty table. PowerDNS should now be able
  54 to launch in monitor mode and display no errors:
  55
  56 ::
  57
  58     # /usr/sbin/pdns_server --daemon=no --guardian=no --loglevel=9
  59     (...)
  60     15:31:30 PowerDNS 1.99.0 (Mar 12 2002, 15:00:28) starting up
  61     15:31:30 About to create 3 backend threads
  62     15:39:55 [gMySQLbackend] MySQL connection succeeded
  63     15:39:55 [gMySQLbackend] MySQL connection succeeded
  64     15:39:55 [gMySQLbackend] MySQL connection succeeded
  65
  66 In a different shell, a sample query sent to the server should now
  67 return quickly without data:
  68
  69 ::
  70
  71     $ dig +short www.example.com @127.0.0.1
  72     $
  73
  74 .. warning::
  75   When debugging DNS problems, don't use ``host``. Please use
  76   ``dig`` or ``drill``.
  77
  78 And indeed, the output in the first terminal now shows:
  79
  80 ::
  81
  82     Mar 01 16:04:42 Remote 127.0.0.1 wants 'www.example.com|A', do = 0, bufsize = 1680: packetcache MISS
  83
  84 Now we need to add some records to our database (in a separate shell):
  85
  86 ::
  87
  88     # mysql pdnstest
  89     mysql> INSERT INTO domains (name, type) values ('example.com', 'NATIVE');
  90     INSERT INTO records (domain_id, name, content, type,ttl,prio)
  91     VALUES (1,'example.com','localhost admin.example.com 1 10380 3600 604800 3600','SOA',86400,NULL);
  92     INSERT INTO records (domain_id, name, content, type,ttl,prio)
  93     VALUES (1,'example.com','dns-us1.powerdns.net','NS',86400,NULL);
  94     INSERT INTO records (domain_id, name, content, type,ttl,prio)
  95     VALUES (1,'example.com','dns-eu1.powerdns.net','NS',86400,NULL);
  96     INSERT INTO records (domain_id, name, content, type,ttl,prio)
  97     VALUES (1,'www.example.com','192.0.2.10','A',120,NULL);
  98     INSERT INTO records (domain_id, name, content, type,ttl,prio)
  99     VALUES (1,'mail.example.com','192.0.2.12','A',120,NULL);
 100     INSERT INTO records (domain_id, name, content, type,ttl,prio)
 101     VALUES (1,'localhost.example.com','127.0.0.1','A',120,NULL);
 102     INSERT INTO records (domain_id, name, content, type,ttl,prio)
 103     VALUES (1,'example.com','mail.example.com','MX',120,25);
 104
 105 .. warning::
 106   Host names and the MNAME of a :ref:`types-soa`
 107   records are NEVER terminated with a '.' in PowerDNS storage! If a
 108   trailing '.' is present it will inevitably cause problems, problems that
 109   may be hard to debug.
 110
 111 If we now requery our database, ``www.example.com`` should be present:
 112
 113 ::
 114
 115     $ dig +short www.example.com @127.0.0.1
 116     192.0.2.10
 117
 118     $ dig +short example.com MX @127.0.0.1
 119     25 mail.example.com
 120
 121 To confirm what happened, check the statistics:
 122
 123 ::
 124
 125     $ /usr/sbin/pdns_control SHOW \*
 126     corrupt-packets=0,latency=0,packetcache-hit=2,packetcache-miss=5,packetcache-size=0,
 127     qsize-a=0,qsize-q=0,servfail-packets=0,tcp-answers=0,tcp-queries=0,
 128     timedout-packets=0,udp-answers=7,udp-queries=7,
 129     %
 130
 131 The actual numbers will vary somewhat. Now hit CTRL+C in the shell where
 132 PowerDNS runs, start PowerDNS as a regular daemon, and check launch
 133 status:
 134
 135 On SysV systems:
 136
 137 ::
 138
 139     # /etc/init.d/pdns start
 140     pdns: started
 141     # /etc/init.d/pdns status
 142     pdns: 8239: Child running
 143     # /etc/init.d/pdns dump
 144     pdns: corrupt-packets=0,latency=0,packetcache-hit=0,packetcache-miss=0,
 145     packetcache-size=0,qsize-a=0,qsize-q=0,servfail-packets=0,tcp-answers=0,
 146     tcp-queries=0,timedout-packets=0,udp-answers=0,udp-queries=0,
 147
 148 On systemd systems:
 149
 150 ::
 151
 152     # systemctl start pdns.service
 153     # systemctl status pdns.service
 154     * pdns.service - PowerDNS Authoritative Server
 155        Loaded: loaded (/lib/systemd/system/pdns.service; enabled)
 156        Active: active (running) since Tue 2017-01-17 15:59:28 UTC; 1 months 12 days ago
 157          Docs: man:pdns_server(1)
 158                man:pdns_control(1)
 159                https://doc.powerdns.com
 160      Main PID: 24636 (pdns_server)
 161        CGroup: /system.slice/pdns.service
 162                `-24636 /usr/sbin/pdns_server --guardian=no --daemon=no --disable-syslog --write-pid=no
 163
 164     (...)
 165     # /usr/sbin/pdns_control SHOW \*
 166     corrupt-packets=0,latency=0,packetcache-hit=2,packetcache-miss=5,packetcache-size=0,
 167     qsize-a=0,qsize-q=0,servfail-packets=0,tcp-answers=0,tcp-queries=0,
 168     timedout-packets=0,udp-answers=7,udp-queries=7,
 169
 170 You now have a working database driven nameserver! To convert other
 171 zones already present, see the :doc:`migration guide <../migration>`.
 172
 173 Common problems
 174 ---------------
 175
 176 Most problems involve PowerDNS not being able to connect to the
 177 database.
 178
 179 Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
 180 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 181
 182 Your MySQL installation is probably defaulting to another location for
 183 its socket. Can be resolved by figuring out this location (often
 184 ``/var/run/mysqld.sock``), and specifying it in the configuration file
 185 with the :ref:`setting-gmysql-socket` parameter.
 186
 187 Another solution is to not connect to the socket, but to 127.0.0.1,
 188 which can be achieved by specifying ``gmysql-host=127.0.0.1``.
 189
 190 Host 'x.y.z.w' is not allowed to connect to this MySQL server
 191 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 192
 193 These errors are generic MySQL errors. Solve them by trying to connect
 194 to your MySQL database with the MySQL console utility ``mysql`` with the
 195 parameters specified to PowerDNS. Consult the MySQL documentation.
 196
 197 Typical Errors after Installing
 198 -------------------------------
 199
 200 At this point some things may have gone wrong. Typical errors include:
 201
 202 binding to UDP socket: Address already in use
 203 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 204
 205 This means that another nameserver is listening on port 53 already. You
 206 can resolve this problem by determining if it is safe to shutdown the
 207 nameserver already present, and doing so. If uncertain, it is also
 208 possible to run PowerDNS on another port. To do so, add
 209 :ref:`setting-local-port`\ =5300 to ``pdns.conf``, and
 210 try again. This however implies that you can only test your nameserver
 211 as clients expect the nameserver to live on port 53.
 212
 213 binding to UDP socket: Permission denied
 214 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 215
 216 You must be superuser in order to be able to bind to port 53. If this is
 217 not a possibility, it is also possible to run PowerDNS on another port.
 218 To do so, add :ref:`setting-local-port`\ =5300 to
 219 ``pdns.conf``, and try again. This however implies that you can only
 220 test your nameserver as clients expect the nameserver to live on port
 221 53.
 222
 223 Unable to launch, no backends configured for querying
 224 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 225
 226 PowerDNS did not find the ``launch=gmysql`` instruction in pdns.conf.
 227
 228 Multiple IP addresses on your server, PowerDNS sending out answers on the wrong one, Massive amounts of 'recvfrom gave error, ignoring: Connection refused'
 229 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 230
 231 If you have multiple IP addresses on the internet on one machine, UNIX
 232 often sends out answers over another interface than which the packet
 233 came in on. In such cases, use :ref:`setting-local-address` to bind to specific IP
 234 addresses, which can be comma separated. The second error comes from
 235 remotes disregarding answers to questions it didn't ask to that IP
 236 address and sending back ICMP errors.
 237