Moving MySQL's Data Directory on a New Install on Linux

It seems pretty simple on a new install. You can copy the files from /var/lib/mysql to the new location. If you want to initialize it from scratch:

1. set up the datadir variable in /etc/my.cnf under [mysqld] like so:

[mysqld]
datadir           = /my/path/to/data/directory



2. You'll need to run the mysql_install_db perl script as the mysql user like so:

su - mysql -c "mysql_install_db --datadir=/my/path/to/data/directory"

or, if you have sudo configured:

sudo -u mysql mysql_install_db --datadir=/my/path/to/data/directory


2b. If you're using selinux, copy the security contexts from /var/lib/mysql recursively like so:

chcon -R --reference=/var/lib/mysql /my/path/to/data/directory



3. Now, you should be able to start MySQL from init or using the service command (on Redhat based distributions)

sudo /etc/init.d/mysql start

or 

sudo /sbin/service mysql start

Left Join Example - SQL Query

Today, I had to write a MySQL query that found entries in one table that had no corresponding entries in a second table. The schema was written in such a a way that table A's id column corresponded to an id column in  table B (call the column tableA_id.) It's possible that some entries in table A might not have corresponding entries in table B (and vice versa, but I didn't actual care about the unique table B entries.) Table A was constructed with an "id" column and a "name" column. Table B was created like so: "id" "attribute" and "tableA_id"

The query (a rather simple one, at that...)

select A.id, A.name from A left join B  on A.id = B.tableA_id where B.tableA_id is NULL