Common Database Tasks
Fixing Permissions Issues
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)
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
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 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';
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.
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 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
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: