The MySQL driver is obviously meant to be used in combination with a MySQL database. This driver has been tested on Windows 2K and Redhat Linux 7.2 using only MySQL 4.0. Older versions of MySQL may work but have not been tested. The driver should build properly on any UN*X system that BIND and MySQL both support. Be sure to specify --with-dlz-mysql when running configure so that the MySQL driver is built with BIND. By default DLZ and it's drivers are not built. When you specify a DLZ driver the DLZ core is automatically built too. The MySQL client libraries and header files are required to build the driver and are not included in this package. You can download the MySQL source from www.mysql.com. The configure script will search in the normal locations for the MySQL headers and libraries. You can specify the appropriate directory if it is not found by appending a path to the MySQL configure option. For example: --with-dlz-mysql=/path/to/files

To find out where your mysql headers and libraries are you can run the mysql_config command. "mysql_config --cflags" will return the path to the MySQL header files, and "mysql_config --libs" will return the path to the MySQL library files.

The MySQL driver was built to be as flexible as possible. Most "drivers" tend to require a specific database schema for the driver to be able to interact with the database. This then requires modification to the driver to support any variation of the schema, introducing new problems and limiting the usefulness and adoption of the driver. This driver has been built specifically to avoid that problem. Instead of imposing a database schema, the driver accepts SQL queries with a few special tokens as parameters. These queries are then parsed and the tokens removed. When a query is run, the token is replaced with the appropriate value. This allows a variety of database schemas to be used without modification to the driver's code. The only limitations are:

1. The query must return the appropriate data types in the correct order.
2. The query must use the correct pre-defined tokens.
3. The query must be written to that when the tokens are replaced with search values it is still valid.

IMPORTANT NOTICE!!! READ THIS!!! IMPORTANT INFORMATION BELOW!!!

The MySQL driver has one additional limitation. MySQL uses thread local storage in its C api. Thus MySQL requires that each thread of an application execute a MySQL "thread initialization" to setup the thread local storage. This is impossible to do safely while staying within the DLZ driver API. This is a limitation caused by MySQL, and not the DLZ API. Because of this BIND MUST only run with a single thread when using the MySQL driver. To ensure BIND runs with a single thread pass "-n 1" on the command line when starting BIND (named). This should not be a limitation on most UN*X systems as BIND is normally compiled single threaded (there are some exceptions). Even if BIND is compiled to support threads passing "-n 1" on the command line will cause it to use a single thread. Also, if the MySQL driver is compiled into BIND but NOT USED then "-n 1" is not required. The "-n 1" command line parameters are only required when the MySQL driver is USED.

The chart below shows which operating systems BIND uses threads on by default.

To build BIND without threads pass "--enable-threads=no" to configure. Again, this limitation is only necessary when USING the MySQL driver. I only recommend forcing a non-threaded build if you will ALWAYS be using the MySQL driver.

Below is a sample of a proper dlz_mysql_driver configuration. This configuration segment would be contained in BIND's config (named.conf) file. It is explained more below. When you are developing your own MySQL driver SQL queries, be sure to pass the following parameters to BIND "-g -d 1". The first "-g" tells BIND to write all log messages to stdout instead of a log file. The second parameter "-d 1" sets BIND's debug level to 1. The MySQL driver will print each SQL query it is about to run when the debug level is set to at least 1. This can be very helpful in debugging SQL queries while you are setting up the driver. The SQL queries will be printed only when BIND is trying to answer DNS queries, not when BIND loads. Run a few sample DNS queries to see the SQL queries that are used.

dlz "Mysql zone" {
   database "mysql
   {host=localhost dbname=dns_data ssl=tRue}
   {select zone from dns_records where zone = '%zone%'}
   {select ttl, type, mx_priority, case when lower(type)='txt' then concat('\"', data, '\"')
        else data end from dns_records where zone = '%zone%' and host = '%record%'
        and not (type = 'SOA' or type = 'NS')}
   {select ttl, type, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum
        from dns_records where zone = '%zone%' and (type = 'SOA' or type='NS')}
   {select ttl, type, host, mx_priority, data, resp_person, serial, refresh, retry, expire,
        minimum from dns_records where zone = '%zone%' and not (type = 'SOA' or type = 'NS')}
   {select zone from xfr_table where zone = '%zone%' and client = '%client%'}
   {update data_count set count = count + 1 where zone ='%zone%'}";
};

The first line: dlz "Mysql zone" {

This line tells BIND we want to use a DLZ driver. The word "dlz" is a new BIND keyword added by the DLZ patch. The next section "Mysql zone" is the label for this configuration segment. It is used in any error messages BIND displays while parsing its config file. The last piece "{" starts the DLZ configuration section in BIND's config file.

The second line: database "mysql

This line is indented just to make it easier to read the configuration file. The keyword "database" is the only parameter that can be specified in a DLZ configuration segment. It is required. The double quote (") begins the command line that is passed to the DLZ driver--in this case, the MySQL driver. Notice that the command line is actually broken over many lines. This is done to make the configuration segment easier to read. The next piece is the word "mysql". This is the official name of the DLZ MySQL driver. We are telling BIND that we want to use the MySQL driver. The word "mysql" is located at argv[0]. I.E. This is the command line array passed to the driver, and the driver name must always be at argv[0], it is not optional. Notice that the Postgres driver specifies a number as its next arguement, and the MySQL driver does not. This is because the Postgres driver supports threads and can use more than one database connection at a time. The MySQL driver DOES NOT support threads, and can only use 1 database connection per driver instance.

The third line: {host=localhost dbname=dns_data ssl=tRue}

Notice that the contents of this line are surrounded by braces "{" and "}". Braces can be used inside of DLZ command lines to group several items together into one parameter. The entire line above is one parameter located at argv[2]. Braces cannot be nested, so "{some commands {more commands} other stuff}" would be invalid and have unpredictable results. Spaces are not removed inside of braces, so be careful. For example, {some commands} would be "some commands", but { some commands } would be " some commands ". Notice the extra spaces in the second set! Usually, for SQL queries this does not have a negative effect, but it should still be avoided. For this reason, the queries are all on one line. The use of braces is similar to the use of double quotes on a standard command line. Double quotes could not be used, as the entire DLZ command line is already inside of double quotes and the escaping would be nasty. Additionally, single quotes were not used because they have special meaning in SQL queries. Using single quotes would have made escaping SQL queries nasty, too.

This is the database connection string; it is required. In the Postgres driver this string is passed directly to the Postgres connect function. The MySQL connect function doesn't accept a "connection string". The MySQL driver emulates the Postgres way of accepting connection parameters. The following parameters are accepted by the MySQL driver:

• dbname=
• port=
• host=
• user=
• pass=
• socket=
• compress=
• ssl=
• space=

Most of these parameters are self explanatory, and match up with the parameters MySQL accepts for it's mysql_real_connect() function. The last 3 parameters (compress=, ssl=, space=) are flags. If you specify "ssl=true" in the database connection string then the MySQL driver will attempt to use SSL encryption when communicating with the database. If you specify anything other than "true" for these parameters it will be considered "false". The case (capitalization) of the word "trUe" does not matter. Lastly, do not include any spaces between the parameter name, the equal sign, and the value you wish to pass. For example:

dbname = my_database    THIS IS WRONG!
dbname= my_database     THIS IS WRONG!
dbname=my_database      CORRECT will use "my_database" to answer queries
dbname=my database      this will use "my" as the database to answer queries
                        Database names should not have spaces in them.

For more information on what values should be passed to these parameters read the MySQL docs.

Fourth line: {select zone from dns_records where zone = '%zone%'}

This query is used by findzone() in the MySQL driver. This parameter is required and located at argv[3]. Notice the string %zone%. This is one of the tokens discussed previously. When BIND needs to ask if a zone is supported by the MySQL database, it will use the above query and replace %zone% with the zone name it's asking about. Also, notice that the %zone% is inside a set of single quotes. BIND makes no guesses about syntax. Zone is always a string, but DLZ doesn't know or care that SQL strings should be inside of single quotes. It's up to you to make sure the query will have the correct syntax when %zone% is replaced. The MySQL driver is smart enough, however, to properly escape the zone string. So "www.dom'ain.com" will be properly escaped so as not to cause a problem when used in the query. Only the token %zone% should be used in the findzone() query. Other tokens will be recognized and parsed out, but they will never be replaced by any value because only zone is used in the findzone() operation. Results returned by the findzone query are not really used so what you return and in what order doesn't really matter. What does matter is the number of rows that are returned. If zero rows are returned, the database does not support the zone (i.e. it is not authoritative for the zone). If at least 1 row is returned, then the database does support the zone, and thus is authoritative for it. Findzone MUST return at least 1 row for a zone query before the lookup query will ever be called. In the MySQL driver, the find zone query is also used as the first query during the allowzonexfr() query operation. This lets allowzonexfr() determine if the zone is supported by the database, before it attempts to determine if a zone transfer is allowed on the zone by the client.

Red SQL query: {select ttl, type, mx_priority, case when lower(type)='txt' then concat('\"', data, '\"') else data end from dns_records where zone = '%zone%' and host = '%record%' and not (type = 'SOA' or type = 'NS')}

This query is used by lookup() in the MySQL driver. This parameter is required and located at argv[4]. Notice the string %record%. This is the second of the tokens discussed previously. Once BIND has determined the zone is supported by calling findzone(), it will call lookup() during domain name resolution DNS queries. Only the %zone% and %record% tokens are useful in a lookup query. You don't have to use both tokens if your database query doesn't require it. Again, you are responsible for making sure the syntax of the query is correct once %zone% and %record% are replaced. The driver will properly escape the strings used to replace the tokens. The token %zone% is replaced with the zone name, and %record% is replaced with the host name we are searching for. If we are searching at the zone apex, then %record% is replaced with the string "@" (i.e. a string consisting of the single at (@) character). If we have searched for the host name and not found it lookup will search for a "wild card" hostname. When searching for a wild card hostname %record% is replaced with "*". If your lookup query will be returning TXT dns records be sure to wrap your TXT data with double quotes before returning the data.

Let's say your TXT data is: a long example

If your SQL does not wrap the data in double quotes the DNS query will return

"a" "long" "example"

If you do wrap the TXT data with quotes the DNS query will return

"a long example"

Notice that there is no token to tell the query what type of DNS record we are looking for. The query should return all records matching the record and zone parameters. Returning NS and SOA records by lookup is optional. If your lookup query will not return NS and SOA records, you must implement an authority query that will return those types of records. If your lookup query will return NS and SOA records, the authority query is not required. You should NOT implement an authority query if your lookup query will return NS and SOA records, as it will cause an error! In this sample query, we are artificially preventing NS and SOA records from being returned by the query so that we can show the use of the authority function later in this document. The order and type of data returned by a lookup query IS EXTREMELY IMPORTANT!! Also, the number of fields returned by the lookup query can very from 1 to an unlimited number of fields. How many fields are returned is also of great importance. All of this is discussed later in the documentation, after we have introduced all of the queries that can be passed to the MySQL driver.

Green SQL query: {select ttl, type, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum from dns_records where zone = '%zone%' and (type = 'SOA' or type='NS')}

This query is used by authority() in the MySQL driver. This parameter is optional and always located at argv[5]. Only the token %zone% is useful in this query. Other tokens will never be replaced with a value, although they are parsed out if you use them. Again, you are responsible for making sure the syntax of the query is correct once %zone% is replaced. The driver will properly escape the string used to replace the token. The token %zone% is replaced with the zone name. This query should be used to return NS and SOA records if the lookup query does not. If the lookup query will return NS and SOA records, DO NOT USE an authority query, as it will cause errors! Like the lookup query, the number, type and order of fields returned by the query is extremely important. All of this is discussed later in the documentation.

Blue SQL query: {select ttl, type, host, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum from dns_records where zone = '%zone%' and not (type = 'SOA' or type = 'NS')}

This query is used by allnodes() in the MySQL driver. This parameter is optional and always located at argv[6]. If your lookup function will return SOA and NS data, and you will not be using an authority query, you still need to specify an empty authority query so that the allnodes query will be located at argv[6]. To specify an empty authority query, use "{}" as your authority query. Notice there are no characters or spaces between the brackets. If you accidentally use "{ }" the driver will think you are using a single space as your authority query and error. BE CAREFUL! In order to support zone transfer, you must specify an allnodes AND an allowzonexfr query. If only one query is supplied, zone transfers will fail! This query should be used to return ALL records in the zone. Notice in this query we have NOT taken the efforts to wrap TXT records with double quotes. If you will have TXT records in your database be sure to wrap the TXT records with double quotes in both the lookup and allnode queries. Only the token %zone% is useful in this query. Other tokens will never be replaced with a value, although they are parsed out if you use them. Again, you are responsible for making sure the syntax of the query is correct once %zone% is replaced. The driver will properly escape the string used to replace the token. The token %zone% is replaced with the zone name. Like the lookup query, the number, type and order of fields returned by the query is extremely important. All of this is discussed later in the documentation.

Violet SQL query: {select zone from xfr_table where zone = '%zone%' and client = '%client%'}

This query is used by allowzonexfr() in the MySQL driver. This parameter is optional and always located at argv[7]. In order to support zone transfer, you must specify an allnodes AND an allowzonexfr query. If only one query is supplied, zone transfers will fail! Notice the string %client%. This is the third token discussed previously. The %client% token is only useful in the allowzonexfr query. Like all the other queries, %zone% will be replaced with the zone name. The token %client% will be replaced with the client's IP address. The IP address will be either an IPV4 or IPV6 address, depending upon how the client connected to the DNS server (using IPV4 or IPV6). The client address will be ASCII text like all the other token replacement strings. Also, both the %zone% and %client% replacement strings will be properly escaped. You are still responsible to make sure the query is correct once the tokens have been replaced. Like the findzone query, the results returned by the allowzonexfr query are not really used, so what you return and in what order doesn't really matter. What does matter is the number of rows that are returned. If zero rows are returned, the client is not allowed to perform zone transfers on this zone. If at least 1 row is returned, the client is allowed to perform zone transfers for this zone, and the allnodes query will be executed next. Before using this query, the Postgres driver will use the findzone query to determine if the zone is supported by the database. Only if the zone is supported will the allowzonexfr query be run.

Cyan SQL query: {update data_count set count = count + 1 where zone ='%zone%'}";

This query is used by the findzone() function AFTER the findzone query ONLY if the findzone query returned at least 1 row. This allows you to count how many queries have been perfomed for a zone. Since the findzone() function is run by the allowzonexfr() function this query is also run when allowzonexfr() is run. Thus, when someone attempts to perform a zone transfer, this query is run if the zone exists, whether or not the requestor is allowed to perform the zone transfer. Also notice the double quote and semi-colon at the end of the line. The double quote closes the command line string that was started on the second line. The semi-colon is required after a BIND parameter, and is part of BIND's standard config file syntax.

Last line: };

This closes the DLZ configuration section in BIND's config file. It is part of BIND's standard configuration file syntax.

Returned Fields:

Now that we have covered all the different types of queries that can be used by the MySQL driver, we can discuss the number, type and order of fields returned by the queries. This can be a bit difficult to understand, so be sure to read the next section of the documentation thoroughly and carefully!

The chart below shows the order in which fields should be returned in queries. Field 1 is the left most field in a SQL query. The chart below shows 12 fields but not all fields are required in all queries. You can divide your data up into even more fields if you like, as long as the concatenation of the data makes sense to BIND. An explanation of how fields are used and concatenated together is detailed below.

Notice the data type column. Some fields are labeled as "string (num)". MySQL only really returns strings from a database when using its C API functions. The values of the fields may be held as integers, floats, whatever by the database, but the return from the MySQL functions is only a string. Eventually, however, the fields marked with "string (num)" are turned into numbers by BIND or the MySQL driver. Your database can hold these fields as strings or numbers, but they MUST be able to be properly parsed into a number without any extra characters. If they cannot, BIND will error and fail to answer the DNS query. I recommend the fields labeled as "string (num)" be held as numbers in your database schema.

There are six queries that can be used by the MySQL driver. Of those, only the lookup, allnodes, and authority queries make use of the fields returned by the query. In order for the fields to be interpreted properly by the driver, they must be in the correct order.

Allnodes:

The allnodes query is actually the simplest to understand because it makes use of all the fields and has the fewest options. When the allnodes() function loops through the rows returned by the query, it passes that data back to BIND using the dns_sdlz_putnamedrr() function. This function takes five parameters and has the following signature:

dns_sdlz_putnamedrr(allnodes, name, type, ttl, data)

The first parameter "allnodes" is used internally by the driver and BIND so you can just ignore it. The next parameter "name" is the hostname of the record or field 3 from the chart above. Next is "type"--this is the DNS data type of the record, field 2 from the chart above. Next is "ttl" or time to live, field 1 in our chart. The last parameter is "data". Data accepts all the other information about this record as a string. This string must be in the proper format for BIND to use it properly.

The data string is "built" by concatenating fields 4 through 12 together, with a space added in between each. Fortunately, the number of spaces in between doesn't matter, as long as there is at least one. This makes it easy for one query to return a number of different DNS types. If a field returned by the allnodes query is NULL, nothing is appended by that field. The driver then appends a space to the end of the string and continues to the next field. This is done until fields 4 through 12 (or however many fields you have in the query) are processed.

For example, if we had an all nodes query that returned 12 fields, the row for a mx record might have the following data:

ttl = 3600
type = MX
host = @
mx_priority = 20
data = mail
all other fields in the row are NULL.

This would result in the following call to dns_sdlz_putnamedrr().

dns_sdlz_putnamedrr(allnodes, "@", "MX", 3600, "20 mail ")

Notice all the extra spaces at the end of the data string. That is caused by the other fields in the row being NULL. This is correct operation, and BIND will be able to understand and use this data.

In the same result set, say the next row is an NS record.

ttl = 3600
type = NS
host = @
data = NS1
all other fields in the row are NULL.

The call to dns_sdlz_putnamedrr() now would look like this:

dns_sdlz_putnamedrr(allnodes, "@", "NS", 3600, " NS1 ")

Notice the space at the beginning. This is caused by the field mx_priority being NULL.

Now obviously, only 4 fields are required for the dns_sdlz_putnamedrr() function. So if your query returns only four fields, it should work properly as long as the first three are ttl, type, host (in that order), and the fourth has all the "data" needed for the last parameter of dns_sdlz_putnamedrr(). The advantage of splitting the "data" field into many fields in your database is easier data management.

Relative return data:

Another important point is that the MySQL driver uses relative data. In the last example, the call to dns_sdlz_putnamedrr() looked like this:

dns_sdlz_putnamedrr(allnodes, "@", "NS", 3600, " NS1 ")

In the "data" field, we only have NS1. NS1 is not a fully qualified domain name. BIND only returns fully qualified answers though. Our driver knows that NS1 is relative to the zone we are searching in. So if we were searching in the zone "example.com" the above would actually result in BIND returning "NS1.example.com" (case doesn't matter in DNS queries). This makes it easy to manage our DNS data in the database using separate fields for zone and host names. Sometimes, though, we want to return data that is NOT relative to the zone. For example, if the authoritative name server for this zone isn't "ns1.example.com", but is "ns1.domain.com", we need to be able to properly return that information to BIND so the DNS response will be correct. To override the default behavior, use an extra "." on the end of the domain name.

for example:

ttl = 3600
type = NS
host = @
data = NS1
all other fields in the row are NULL, our zone is "example.com"

dns_sdlz_putnamedrr(allnodes, "@", "NS", 3600, " NS1 ")

tells BIND our name server is "NS1.example.com"

if data = NS1.domain.com

dns_sdlz_putnamedrr(allnodes, "@", "NS", 3600, " NS1.domain.com ")

Tells BIND our name server is "NS1.domain.com.example.com". This is correct behavior! It is not the right answer, but the behavior is correct.

if data = NS1.domain.com.

dns_sdlz_putnamedrr(allnodes, "@", "NS", 3600, " NS1.domain.com. ")

Tells BIND our name server is "NS1.domain.com". This is the answer we want to give. Notice the extra "." at the end of the host name. This tells BIND that the domain name is absolute, and not to be used relative to our zone of "example.com".

To summarize, the allnodes query MUST have at least 4 fields in the result set, and those fields must be ttl, type, host and data. The fields must be in that order, and the data field (or fields concatenated together) must hold the remainder of any data required for that DNS record type. To return absolute instead of relative hostname data, an extra "." should be used at the end of the domain name.

Lookup and Authority:

Lookup and authority operate in a manner similar to allnodes. Instead of dns_sdlz_putnamedrr(), lookup and authority use dns_sdlz_putrr(). This function is similar to dns_sdlz_putnamedrr(), except it only takes 4 parameters. The function dns_sdlz_putrr() has the following signature:

dns_sdlz_putrr(lookup, type, ttl, data)

The first parameter "lookup" is used internally by the driver and BIND so you can just ignore it. The next three parameters "type", "ttl", and "data" are all the same as in the dns_sdlz_putnamedrr() function.

Lookup and authority work a little differently in that they have default values. If a result set for a lookup or authority query only has one field, the driver will use the default value of "a" for type and 86400 for ttl. The call to dns_sdlz_putrr will look something like:

dns_sdlz_putrr(lookup, "a", 86400, "your_data_field_here")

The field returned by your result set should be just a hostname or IP address (for reverse DNS queries). This is fine for a lookup query, but the purpose of the authority query is to return NS and SOA records, so an authority query should never return just one field!

If the result set for a lookup or authority query only has two fields, the driver will use the default value of 86400 for ttl. The first field in the result set MUST be the DNS record type. The second field MUST be the DNS data in the format expected by BIND. The format is the same as discussed for the allnodes query.

dns_sdlz_putrr(lookup, "dns_type_here", 86400, "data_field_here")

If the result set for a lookup or authority query has three fields, the driver will not use any default values. The first field in the result set MUST be the ttl. The second field MUST be the DNS data type, and the last field MUST be the DNS data in the format expected by BIND. The format is the same as discussed for the allnodes query.

dns_sdlz_putrr(lookup, "dns_type_here", "ttl_field_here", "data_field_here")

If the result set for a lookup or authority query has more than three fields, the driver will not use any default values. The first field in the result set MUST be the ttl. The second field MUST be the DNS data type. All remaining fields will be concatenated together to create the last parameter passed to dns_sdlz_putrr(). This string must be formatted as BIND expects it. The format is the same as discussed for the allnodes query.

dns_sdlz_putrr(lookup, "dns_type", "ttl_here", "concatenated_fields_here")

As with the allnodes query, append a "." to the end of a hostname if you need to return an absolute hostname instead of a relative one.

It may be confusing at first to understand how to properly create an SQL query for the MySQL driver. But the flexibility afforded by this way of doing things is very powerful. It sure beats being limited to a set schema, or building an entirely new driver!