Jump To: Support > KB > NetManager > MySQL

Using MySQL

NetManager has support for MySQL built-in. MySQL is a very widely used free database server. Built-in MySQL support allows you to easily install hundreds of software titles (especially web-based resources such as Moodle).

Creating databases can be done from webadmin by choosing Services > Databases:

The username and password are separate from other usernames on your network, so do not reuse real usernames. It is common convention to use the name of the database as the username and then invent a secure password.

When configuring software you have installed in your intranet, you will need to specify the database name, username and password. Often you will also need to give the database server (or host). Use localhost for the local server.

Connecting remotely

MySQL doesn't actually grant access to just a username and password; it actually grants access on the basis of the tuple of username, password and client machine name. This means that even if you have given access to a certain username from the local server, you will not be able to access the database from another machine without explicitly granting access to a user on that machine.

If you are attempting to connect to the MySQL server from another server, you will see errors such as:

Invalid query: Host 'otherserver.school.internal' is not allowed to connect to this MySQL server
Error Number: 1130

The configuration tools in webadmin do not, at the time of writing, support specifying a client machine. Therefore you must use the backend mysqladd program when logged in as root at the command line.

The full syntax is:

mysqladd: $Revision: 1.20 $ $Date: 2010/11/18 10:28:43 $
Syntax: mysqladd [-acdplL] [-e encoding] [-o file/dir] [-r host] database [username password]
        mysqladd -D username
        -a = Add database (default) username/password required
        -c = Check whether database exists (see exit status)
        -d = Delete database
        -D = Delete user
        -e = change encoding from latin1 default (options: utf8)
        -l = List databases
        -L = List databases with permissions
        -o = Dump database(s) to given file or directory
             If a directory is given, databases will be dumped to separate files
        -p = Set access permissions on database
        -r = Grant access to given host, not localhost

The -p and -r options are the important ones. The -p flag allows you to change permissions on an existing database. The -r option allows you to specify a certain client machine. Use % to grant access to all client macines. N.B. the MySQL server will do a reverse DNS lookup on the connecting IP address and then use that name when checking access. This means you cannot just use otherserver or its IP address, you must use its full domain name and there must be a reverse DNS record for it. You can test this by using the host command:

netmanager 1# host 10.0.0.2
2.0.0.10.in-addr.arpa domain name pointer otherserver.school.internal.

In this example you must use otherserver.school.internal, not otherserver, i.e.

mysqladd -pr otherserver.school.internal mydb mydb mypass

This will give access to database mydb from client machine otherserver to user mydb and password mypass. You must have created the database itself first to use the -p flag.

mysqladd -pr % mydb mydb mypass

This will give access to database mydb from all client machines to user mydb and password mypass. You must have created the database itself first to use the -p flag.