Kit Is It

sudo mysqld_safe
sudo mysqladmin shutdown

sudo mysql
GRANT ALL ON menagerie.* TO 'user'@'localhost';
\q

mysql
SELECT USER();
CREATE DATABASE menagerie;
USE menagerie;
SELECT DATABASE();
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
SHOW TABLES;
DESCRIBE pet;

• Secure the MySQL accounts. See 5.3.1. General Security Guidelines
• See Chapter 10: Data Types
• Delimiter can be specified in the LOAD DATA statement
• When writing tab-delimited files with Vim, set noexpandtab
• Pick up from 3.3.4.6. Working with NULL values
• Databases are stored in /usr/local/mysql/data
• User configuration file is ~/.my.cnf

After installing from the Mac OS X PKG Installer, you can use a different data directory for your databases:

1. Add the following lines to your .bash_profile

   export PATH=${PATH}:/usr/local/mysql/bin           # mysqld_safe and mysqladmin
   export PATH=${PATH}:/usr/local/mysql/scripts       # mysql_install_db
   export PATH=${PATH}:/usr/local/mysql/support-files # mysql.server
   

2. Then install the new data directory

   $ sudo mysql_install_db --basedir=/usr/local/mysql --datadir=/path/to/my/foobardata --user=Kit
   

   sudo is needed to invoke the --user option. Replace Kit with your own username. This will be the username that will run the mysqld_safe server later on.

   --basedir is the MySQL installation directory. By default it is /usr/local/mysql. I needed to specify this explicitly because the script looked for the default installation directory from the current directory I was working on (~).

   --datadir is where you want to store your database files

3. After executing the script, edit your .my.cnf file and add the following lines under [mysqld]:

   [mysqld]
   datadir=/path/to/my/foobardata
   

4. Take a look at the directory where you installed your database files:

   $ cd /path/to/my/foobardata
   $ ls -l
   

   Note that there is no *.err file yet. The _mysql user needs this to log errors. If you run mysqld_safe, you’ll get a permission denied error:

   $ mysqld_safe
   chown: /path/to/my/foobardata/host.name.err: Operation not permitted
   

   To work around this, the *.err file must be created and assigned to _mysql. Running sudo mysql_safe does the job. The & makes sure you retain control of the shell prompt.

   $ sudo mysqld_safe &
   

   Then shutdown the server:

   $ sudo mysqladmin shutdown
   

5. After following the above steps, you can now use your own datadir, without having to sudo:

   $ mysqld_safe &
   $ sudo mysqladmin shutdown
   

To use MySQL with SQLAlchemy and MySQLdb in Python:

1. Install mysqldb and sqlalchemy

   $ pip install MySQL-python
   $ pip install SQLAlchemy
   

2. Add the following line to your .bash_profile (from this Stack Overflow answer)

   export DYLD_LIBRARY_PATH=/usr/local/mysql/lib
   

3. Then try it out with Python:

   $ python
   >>> from sqlalchemy import create_engine
   >>> engine = create_engine("mysql://localhost")
   >>> engine.execute("SELECT 1").scalar()
   >>> engine.execute("SELECT USER()").scalar()
   >>> engine.execute("SELECT SIN(PI())").scalar()
   >>> engine.execute("SHOW DATABASES").fetchall()