Installing MySQL

If you want MySQL also do the following:

sudo apt-get install mysql-server
sudo apt-get install php5-mysql

The php5-mysql install adds the mysql libraries to allow PHP to access the mysql database.

Accessing MySQL from the command line

First connect to the database and specify a user:

mysql -p -u root

Then enter the users password when prompted. You should now have a mysql> prompt.  

Exiting MySQL connection / command line login

quit

Restarting The MySQL service

sudo service mysql restart

Creating A Database

Fist connect to the database using "mysql -p -u root".  To create a database:

CREATE DATABASE MY_DATABASE_NAME;

Adding A User

Login to mysql using "mysql -p -u root" and then create a new user to avoid using root:

CREATE USER 'MY_USERNAME'@'localhost' IDENTIFIED BY 'MY_PASSWORD';

Accessing a database

Local Access

For security reasons, by default access to the MySQL server via the main IP address is disabled in the MySQL config. You can connect locally using:

localhost
127.0.0.1
or the internal socket connection on "/var/run/mysqld/mysqld.sock"

Remote Access

First we need to edit the MySQL config:

sudo nano /etc/mysql/my.cnf

Find the configuration line called bind-address. By default this is set to 127.0.0.1. 

This is the local address(es) / network adaptors that MySQL will listen for connections on. The RPi default is 127.0.0.1 for localhost only. To allow connection on all interfaces set it to "0.0.0.0"

Set it to "0.0.0.0" and save the file

sudo service mysql restart

To grant access for a remote connection, login to mysql using "mysql -p -u root" and then create a new user to avoid using root:

CREATE USER 'MY_USERNAME'@'localhost' IDENTIFIED BY 'MY_PASSWORD';

To allow access from any IP address

GRANT ALL PRIVILEGES ON MY_DATABASE_NAME.* TO 'MY_USERNAME'@'%' IDENTIFIED BY 'MY_PASSWORD';
flush privileges;

To allow access from a fixed IP address

GRANT ALL PRIVILEGES ON MY_DATABASE_NAME.* TO 'MY_USERNAME'@'12.34.56.78' IDENTIFIED BY 'MY_PASSWORD';
flush privileges;

Changing MySQL root User Password

See here.

Copy Database To/ From A Remote Server

Insecure Method

If you have direct access to the remote server and aren't worried about security

mysqldump -h [server] -u [user] -p[password] [databasename] | mysql -h [server] -u [user] -p[password] [databasename]

Note: There is NO space between -p and [password]
The left side is the from, the right side is the to.  [server] can be localhost on either side.

Secure Method

If you can SSH into the remote server you can use this

mysqldump -h [server] -u [user] -p[password] [databasename] | ssh [ssh_username]@remote_domain.com mysql -u [user] -p[password] [databasename]

You will then be promoted for the ssh password of the remote server

Copy A Single Database Table To/From A Remote Server

Insecure Method

If you have direct access to the remote server and aren't worried about security

Its the same method as for copying an entire database except that you specify the tablename after the from database name:

mysqldump -h [server] -u [user] -p[password] [databasename] [tablename] | mysql -h [server] -u [user] -p[password] [databasename]

If the table already exists it is overwritten.

Using SSL For Database & Table Copy

See this guide here.

In theory using the –ssl option with mysqldump enables the connection over ssl but we've not verified this and it doesn't look like you get errors for domains with no SSL certificate setup so maybe this option is only good if you've alread ensure SSL is available?:

mysqldump --ssl