MySQL

A relational database engine with a dolphin. MySQL Workbench is the associated management GUI.
web | feed (newsletter) | project | documentation | issues | GPLv2only (or proprietary)

Ubuntu packages | mysql-server mysql-client mysql-workbench
Other distributions | choco debian (wiki)
Other listings | fsd fresh? openhub openhatch ubuntuwiki (server guide) wikidata wikipedia
Alternatives | :postgresql
See also | :database

Notes

Terminology

In MySQL a database is referred to as a schema. (ref)

Command line access

mysql --user=<USERNAME> --password <SCHEMA_NAME>
# enter password when prompted

mysql> SELECT * FROM `example`;
mysql> exit;

Access MySQL from Python

To access MySQL from :python you need the MySQLdb library. Install it as follows:

apt install libmysqlclient-dev # non-python prerequisite
pip install python-mysql # in virtualenv

Notes

Enforce lowercase table names

lower_case_table_names = 1

Old databases will need to be corrected. A simple solution is to export them as a data dump (schema and data), correct names using regex, and reimport.
https://stackoverflow.com/questions/1612338/a-mysql-script-to-convert-the-column-names-to-lowercase

Unsecure MySQL for general access

You might do this, for example, on a test database:

Create a named superuser

  1. Log into the MySQL schema as root user (using password in this example)

mysql --user=root mysql --password
# prompted for password

  1. Then, at MySQL prompt:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'chosen_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'chosen_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

This creates a user username with the password chosen_password who can access the database from the same machine (@'localhost') and from all other machines on the network (@'%').

Note that the passwords for local and remote access could be different, if you'd prefer.

References
https://dev.mysql.com/doc/refman/5.1/en/adding-users.html

Command line notes for Debian 7

Configuration location:

/etc/mysql/my.cnf

To restart:

sudo /etc/init.d/mysql restart

Import SQL dump

Import a database as follows:

mysql --user=USERNAME --password < dump.sql
# assumes database/schema created in SQL script

Import into a specific schema as follows:

mysql --user=USERNAME --password <SCHEMA_NAME> < dump.sql
# assumes schema *not* created in SQL script (but it must exist)
# if it doesn't exist create a .sql file with the following directive:
#     CREATE SCHEMA `SCHEMA_NAME`;

If working with a compressed file, use the following:

gunzip -c FILENAME.sql.gz | mysql --user=USERNAME --password
# different decompress command required for other formats

Export data dump

TODO: instructions



Backlinks: postgresql microsoft:sql-server

CC0 / Public domain dedication To the extent possible under law, d3vid seaward has waived all copyright and related or neighboring rights to "MySQL in Grasmere notebook, including code snippets" (why? how?)