Move a table between MySQL databases

Having organized a lot of datasets in severeal MySQL databases on the same (local)-server the I needed to move a table to another database. The posted solution is copy-paste from Eric Bergen on the MySQL forum:

Alter table can be used to move tables from one db to another.

alter table rename

Sane PATH variable in Emacs on Mac OS X

On Mac OS X the system PATH variable is not recognized by emacs. This means that one can not simply type


in the emacs shell to get into the database. The emacs shell complains about “binary not found”.


echo $PATH

reveals that emacs just looks into /bin, /usr/bin, /sbin and /usr/sbin.

To set the $PATH variable inside emacs one can append the following lines to the .emacs file (found on github, hattip Alex Payne):

; sane path
(setq path "/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:/usr/local/mysql/bin")
(setenv "PATH" path)

Next time Emacs starts one can go to the shell and


presents the database prompt.

Compressed backup of MySQL database

Wrote several posts on this topic, but none was 100% right. The following is a blockquote from and looks much better researched then my previous tries:

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]
If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

Create an appropriately named database on the target machine
Load the file using the mysql command:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql
To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

MySQL tables must have indexes

A newbee to MySQL myself I had a rather odd experience: A simple inner join of three research tables took severeal hours. I posted the problem on the and the following is the result of the post.

To give the conclusion first:

  1. Always create indexes for each key-variable in each and every MySQL table, no matter how small or big.
    where MyTable is a Table in the database and MyID is one of the key variables.

  2. Never use brackets in consecutive joint statements! The right way is:
    select * from Table_A A inner join Table_B B on = inner join Table_C on =;
    Brackets force the creation of temporary tables and increase execution time
  3. The first one is the most important. It reduced the time for a join from more then 10 hours to a couple of minutes.

MySQL backup

Just for the record: How to combine mysqldump and zip to archive all MySQL databases on the host. I am using a simple MySQL database server on localhost, to organise research tables before analysis.

mysqldump --all-databases | zip -9 -

mysqldump --all-databases writes the content of all databases into the pipe and
zip -9 Filename - compresses the standard input (note the dash ‘-‘ at the end!) to ‘Filename’ (-9 gives maximum compression).

The reverse following the man page of ‘mysqldump’:

You can load the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

Emacs as MySQL frontend

After working quite a time with some minor MySQL databases in the (Emacs-) Terminal I just looked up the preinstalled SQL related emacs functions. Just entered M-x sql TAB and indeed the autocompletion showed a function sql-mysql, as expected…

I gave it a try with
M-x sql-mysql
and after prompting for database, servername, username and password Emacs connected to the database and presented the MySQL shell. So I bound sql-mysql to some keyboard shortcut, BUT entering the whole connection parameters each and every time was not acceptable. has a really nice post on Enhancing Emacs’ SQL Mode (you can have a look, but you cannot read the post before pasting the content to a text editor). There I found some excellent functions which would provide a solution:

(setq sql-connection-alist
(sql-product 'mysql)
(sql-server "")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3306))
(sql-product 'mysql)
(sql-server "")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3307))))

(defun sql-connect-preset (name)
"Connect to a predefined SQL connection listed in `sql-connection-alist'"
(eval `(let ,(cdr (assoc name sql-connection-alist))
(flet ((sql-get-login (&rest what)))
(sql-product-interactive sql-product)))))

(defun sql-pool-a ()
(sql-connect-preset 'pool-a))

Now, you can just run sql-pool-a and get connected right away. Because the buffers have good names, you can easily fire up many connections.

I included it in my .emacs file and appended
(DefGlobKey "s-a" 'sql-pool-a)
and with a keystroke the database promt appears.

Thx,, but what’s that webpage giving you an epileptic fit looking at it?!. Excellent page 🙂

Fixing MySQL autostart in Mac OS X

I switched my work laptop – MacBook Air 3.2 – not without headaches… No Ubuntu at the time of writing. This will change.

But for now I needed a MySQL server running, Emacs not colliding with the Mac desktop and R all talking nicely to each other.

After downloading the MySQL-5.5.8-osx10.6-x86_64.dmg and installing MySQL-5.5.8 on MacOSX-10.6.6 (Snow Leopard) everything went fine first.

1. Install the database,
2. install the Startup script,
3. install the extension for the System Preference dialogue.

I did everything worked – until restart. OSX complained about a failing startup script (MySQL) and that was that. The menu in the system preferences did not help and trying to start the MySQL server from the shell did also not work (without throwing any error code).

The solution of debenriver (scroll down a bit) worked for me. In a nutshell:

  1. Fix the user rights of the startup script (that was what OSX complained about):
    sudo chown -R root:wheel /Library/StartupItems/MySQLCOM
    That should fix the problem, but it is not enough.
  2. Restart the computer
  3. Open a terminal and navigate to MySQL
    cd /usr/local/mysql
  4. Change to superuser mode for a while
    sudo echo
  5. Set a root password for your database
    sudo bin/mysqld_safe &
    hit enter after the & and continue
    bin/mysqladmin -u root password 'xxxxxxxx'
    to enter a root password for the MySQL database (replace xxxxxx with your chosen password. This should probably be different from your computer password. Don’t forget the single quotes).

UPDATE: No, 5.5.8 does not work. After a reboot (or something) MySQL refused again to startup. I followed the recommendations on the same thread I quoted, removed MySQL 5.5 and installed MySQL 5.1.54 which was also the current version in the Ubuntu repositories on my old workhorse. Works just fine.

Modify MySQL Tables

After setting up my first small databases the next learning steps concern basic operation on existing tables in a MySQL database, such as deleting or creating additional variables/columns in a table. I found very helpful:

Delete a variable/column

To delete the column MyCol from the table MyTable, use the following SQL command:


Comment: used single-quotes around MyCol and MyTable which I found not necessary.

Add a column/variable

To add a column called email […] with a datatype of VARCHAR(60) to the table contacts, use the following SQL statement:

ALTER TABLE contacts ADD email VARCHAR(60);

This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:


Update/Change the values in a column

One would use the UPDATE statement so I looked it up in the reference

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

In order to update the column intensity in the table measures from “20%” to simply “20” one would
UPDATE measures SET intensity="20" WHERE intensity="20%";

Command line instructions to create a database in MySQL

Still working on a way to port a sloppy Access database to MySQL. In the end it would be lovely to have a shell script doing all steps in a row:

  1. Extract the tables of the database as .csv
  2. Create a new (empty) MySQL database
  3. Create all tables in the new MySQL db
  4. Import the .csvs into those tables

One of the necessary steps is to create a new MySQL database:

$ mysql -u ''adminusername'' -p
mysql> CREATE DATABASE ''databasename'';
mysql> GRANT ALL PRIVILEGES ON ''databasename''.* TO "''username''"@"''hostname''" IDENTIFIED BY "''password''";
mysql> EXIT

I just collected the code, did not try it out. Just not to forget….