First download mysql-python from http://sourceforge.net/projects/mysql-python.

Extract it and run:

   1  python setup.py build
   2  sudo python setup.py install

If you get this error you need to install python-dev package:

   1  In file included from _mysql.c:29:
   2  pymemcompat.h:10:20: error: Python.h: No such file or directory
   3  _mysql.c:30:26: error: structmember.h: No such file or directory
   4  In file included from /usr/include/mysql/mysql.h:44,
   5                   from _mysql.c:40:
   6  .
   7  .
   8  .
   9  _mysql.c:2808: warning: return type defaults to 'int'
  10  _mysql.c: In function 'DL_EXPORT':
  11  _mysql.c:2808: error: expected declaration specifiers before 'init_mysql'
  12  _mysql.c:2886: error: expected '{' at end of input
  13  error: command 'gcc' failed with exit status 1

Installing the python-dev package on Debian is done with apt-get or synaptic:

   1  apt-get install python-dev

Installing the library should now work:

   1  python setup.py build
   2  python setup.py install

Next test the library in the python console:

   1  import MySQLdb
   2  
   3  # Note that this example uses UTF-8 encoding
   4  conn = MySQLdb.connect(host='localhost', user='...', passwd='...', db='...', charset = "utf8", use_unicode = True)
   5  cursor = conn.cursor()
   6  
   7  
   8  cursor.execute ("SELECT * FROM cities")
   9  rows = cursor.fetchall ()
  10  
  11  for row in rows:
  12    print "%s, %s" % (row[0], row[1].encode('utf-8'))
  13  
  14  print "Number of rows returned: %d" % cursor.rowcount
  15  

Don’t forget to close the cursor and connection, and if you’re inserting data commit before closing, because autocommit is disabled by default:

   1  cursor.close ()
   2  conn.commit ()
   3  conn.close ()

For more information about MySQLdb see this article.

   1  GRANT DELETE,INSERT,SELECT,UPDATE ON charlie_production.* TO 'munger'@'localhost' IDENTIFIED BY 'xxx';

http://sourceforge.net/projects/automysqlbackup/

   1  cd /etc/mysql
   2  wget http://garr.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5
   3  
   4  ln -s automysqlbackup.sh.2.5 backup-script
   5  chmod 740 backup-script
   6  
   7  vim backup-script

Next tell cron to run it 4 in the morning:

   1  crontab -e

Add the following line:

   1  0 4 * * * /etc/mysql/backup-script

MySQL doesn’t have rownum, but user variables can be used as a replacement:

   1  SET @pos=0;
   2  SELECT @pos:=@pos+1,name FROM players ORDER BY score DESC;

Example from MySQL 5.0 Reference Manual

I just happened to lock myself out of MySQL, but luckily I have root access to the server so I can reset it easily by first shutting down MySQL:

   1  /etc/init.d/mysql stop

And then creating a MySQL init file with the desired password:

   1  $ echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('1234');" >> /tmp/mysql_init.txt

Starting MySQL with the —init-file parameter like this resets the password:

   1  $ mysqld_safe --init-file=/tmp/mysql_init.txt

Remember to delete the file:

   1  rm /tmp/mysql_init.txt