Power Network Services with MySQL
Last week we learned how to create and populate a user authentication database. Today we'll dig into making changes, making backups, connecting to remote MySQL servers and plugging MySQL in to servers like Postfix and Samba.
In Part 1 we learned some commands for making changes to tables and data. Making changes to data, such as deleting or modifying users, means needing to know how to find the entries to change. MySQL provides the select, where and update commands for this. For example, suppose user Alice Smith wins the lottery and in a fit of sanity walks (or more likely, runs) off the job never to return. So you have to delete her from your user authentication database. Remember, you have to select the database and table (which we created in Part 1), and the MySQL commands are performed at the MySQL command line. In these examples the administrative user created in Part 1, sqllackey01, will do the work:
$ mysql -u sqllackey01 -p
mysql> use samba_auth;
mysql> select * from users where last_name='smith' and first_name='alice';
| uid | login | password | first_name | last_name |
| 611 | asmith | jVqfGYRRSm | alice | smith |
Easy peasey. Then delete Alice using the field containing the primary key, which in the example table is the uid:
mysql> delete from users where uid=611;
If you don't know exactly what to search for, use the like keyword:
mysql> select * from users where last_name like %sm%th%;
The % are wildcards; this example with find all last names containing the strings sm and th. So you'll get Smythe, Smithers, Smoothoperator, Smithee, and so forth. If you need to find literal percent characters, escape them like this: %%.
A common chore is resetting passwords, which you might do to disable Alice's account, instead of erasing her:
mysql> update users set password=(encrypt('newpass')) where uid=611;
Any data can be updated the same way, for example:
mysql> update users set first_name='RichAlice' last_name='SmytheSupreme' where uid=611;
Backing Up & Restoring
The easy way to make a backup of a database is to use MySQL Backup. MySQL Backup is a Perl script that uses mysqldump, tar, and gzip. The documentation is in the script, and it's simple to use. Starting at around line 104, comment out the three lines referencing CGI commands. These are for running backups from a Web browser, which is not a secure way to run the backups. The easiest thing to do is set everything up in the script, then run it automatically from a cron job. Anywhere a program or file is named, be sure to use the full absolute path name.
You'll have the option to backup all tables, or to select certain ones. The backups are stored locally by default, and can be uploaded via FTP to another location. There is even an option to email the backups to whatever lucky soul is elected to receive them.
This cron job runs the script every midnight:
# crontab -e
0 0 * * * /usr/sbin/scripts/mysql_backup
Restoring a database from backup is done by redirecting the contents of the backup file to the original location:
# mysql -u sqllackey01 -p [password] samba_auth < /backups/samba_auth_backup.sql
Logging in to a remote MySQL server is the same as logging into a local server, except you must specify the hostname or IP:
$ mysql -h windbag -u sqllackey01 -p
Administrative user sqllackey01 must have already been granted remote login privileges by the MySQL root user. Using the % wildcard allows sqllackey01 to log in from anywhere, and of course you don't want to use the word "password" for the password:
mysql> grant all privileges on samba_auth.* to sqllackey01@'%' identified by 'password';
You might want to restrict sqllackey01 to the local network by replacing the wildcard with either the domain name or subnet: '%.domain.com', '192.168.1.%'. Opening a MySQL database to the Internet is a bad idea. For remote administration over untrusted networks use SSH (see Resources.)
Debian users will probably get the dreaded "ERROR 2003 (HY000): Can't connect to the MySQL server" error, because by default MySQL accepts TCP connections only from localhost. Fix this by commenting out the "bind_address = 127.0.0.1" line in /etc/mysql/my.cnf. (Remember to restart MySQL after making changes to configuration files.)
Another way to prevent MySQL from accepting remote connections is to put this entry in /etc/mysql/my.cnf:
This tells it to not accept TCP connections, but only local UNIX sockets. You can see what MySQL is listening for with
$ netstat -an --inet
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
That's the output from commenting out "bind_address = 127.0.0.1", showing that MySQL is accepting connections from all network interfaces. Using skip-networking should show no listening TCP ports at all.
What MySQL Version?
mysql> select version();
Now that you're a wizard at the MySQL command line, check out the MySQL Administrator. It is a very nice graphical interface for MySQL that lets you perform all the common administrative functions: user management, backups and restores, and connection and server health monitoring.
Using MySQL with Samba
The samba-doc package comes with a script for creating the table that holds your user accounts, examples/pdb/mysql/mysql.dump. Of course you can create your own table from scratch; this is how to use the script to create the table:
mysql> samba_auth < /usr/share/doc/samba-doc/examples/pdb/mysql/mysql.dump
Then Samba needs some configuration tweaks; see the Samba/MySQL howto.
Using MySQL With Postfix
There are no official Postfix scripts for creating tables, but you can find a good third-party script here. (See the Postfix howto page for a nice assortment of excellent howtos.) One way or another, create your database and populate it with your desired tables, like transport, virtual, and users. Then edit a bunch of Postfix configuration files so Postfix knows where to find everything, and you'll have a flexible backend that can be used by all the components of a mail server POP, IMAP and SMTP. And all the users and domains are completely virtual, so you can easily add, remove and change accounts, just like an ISP.
Another fast, fully-featured, Free/Open Source database worth checking out is PostgreSQL. SQLite is also worth a look. It's small and embeddable, which means you don't have to hassle with setting up a server.