Edit
/CommonDatabaseTasks
Sorry, this wiki is closed to anonymous edits.
Short rundowns of how to perform common database tasks with the different databases we are running (mostly [MySQL](http://mysql.com) and [PostgreSQL](http://postgresql.org)). ## 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. ```python #!/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](http://dev.mysql.com/doc/refman/5.0/en/adding-users.html). To create a superuser that can connect from anywhere: ```sql 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: ```sql postgres=# CREATE ROLE user WITH SUPERUSER LOGIN PASSWORD 'password'; ``` ## Creating/Deleting Database ### MySQL, PostgreSQL ```sql 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](http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html) 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 ``` ## Password Files * MySQL: http://dev.mysql.com/doc/refman/5.1/en/password-security-user.html * PostgreSQL: http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html