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.