Pages this page links to:
Pages linking to this page:
Common Database Tasks
Short rundowns of how to perform common database tasks with the different databases we are running (mostly MySQL and PostgreSQL).
Fixing Permissions Issues
MySQL
access denied for debian-sys-maint
Debian (and Ubuntu) packages of MySQL are maintained by a debian-sys-maint account, running cleanup and verification tasks on tables on startup, etc. This account can infuriatingly lose its permissions, which prevent these tasks from occurring and screw up the mysql init scripts. The short python script below, run as root, will fix it.
#!/usr/bin/env python
"""A short script that reads the /etc/mysql/debian.cnf and re-grants the
debian-sys-maint account."""
CFG="/etc/mysql/debian.cnf"
GRANT="GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'%s' IDENTIFIED BY '%s' WITH GRANT OPTION;"
import os, sys
if os.getuid() != 0:
sys.stderr.write("This program must be run as root.\n")
sys.exit(-1)
import ConfigParser
scp = ConfigParser.SafeConfigParser()
scp.read(CFG)
password = scp.get("client", "password")
local_cmd = GRANT % ('localhost', password)
pct_cmd = GRANT % ('%%', password)
os.system('echo "%s" | mysql' % local_cmd)
os.system('echo "%s" | mysql' % pct_cmd)
PostgreSQL
Ident auth failures
By default on Debian (and Ubuntu), PostgreSQL uses an ident auth mechanism on local connections that is rarely set up properly. To fix this, edit the line in /etc/postgresql/<version>/main/pg_hba.conf that looks like this:
local all all ident
to read like this:
local all all md5
If you want to disable password checking for local connections completely, you can use trust instead of md5.
Creating Users
MySQL
Creating & managing users in MySQL is done through the database itself via CREATE USER and GRANT. You can also add users directly to the user table, if you were so inclined. For the most part, we create superusers, since our users are trusted. Under the configurations we use, you do not need to use CREATE USER; mysql will create users referenced in GRANT statements that do not exist. To see more about creating users, check out MySQL's adding users documentation. To create a superuser that can connect from anywhere:
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
PostgreSQL
PostgreSQL has a suspiciously generic sounding command line tool called createuser and a corresponding tool dropuser. However, you can still create users in SQL. Here's a superuser who can login and has a password:
postgres=# CREATE ROLE user WITH SUPERUSER LOGIN PASSWORD 'password';
Creating/Deleting Database
MySQL, PostgreSQL
CREATE DATABASE dbname;
DROP DATABASE dbname;
In PostgreSQL, the default database encoding for the debian configuration is LATIN1 (outdated?). To create a UTF-8 db, add WITH encoding='UTF8' to the CREATE DATABASE command.
Backup/Restore
Both of these procedures are similar, but the devil is in the details. Be sure to read the documentation for each of the tools mentioned, and to be safe, test a backup/restore cycle before you put your faith in your backups.
MySQL
MySQL ships with a mysqldump binary that is essentially a specialized client that can dump entire or partial databases in a variety of formats. As of MySQL 5.1, the --opt command is on by default. Read through the options to see what will be the best for your situation, but for a simple starting point, this command should do:
mysqldump --opt -p -u username dbname > dumpfile.sql
Note that this command will dump your entire database dbname as sql statements directly executable by the mysql server. The --opt option will take care of things like adding DROP statements before tables and locking tables before dumping them. You can chose to filter tables, and there are other output formats available, but they are easy to find in the documentation. -p will have mysql prompt for a password; skip this if you do not check for passwords (say, on local socket for your user). -u is the database username; you can skip this if it is the same as your unix username.
To load this, pipe it into mysql:
mysql -u %username% -p < dumpfile.sql
PostgreSQL
Use pg_dump or pg_dumpall for this. Safe options are '-d' for using INSERTs instead of COPYs, and (for pg_dump only) -C for adding CREATEs, and -E for forcing an encoding:
pg_dump -U username -dCE utf8 dbname -f dumpfile.sql
pg_dumpall -U username -d -f dumpfile.sql
To load, source the file from the pgsql prompt:
TODO: example