MySQL is a specific type of SQL database.

It gained huge popularity thanks to being powerful, fully featured and also completely free open source software.

Hosting companies almost certainly offered MySQL databases and so it became the defacto database for LAMP software.

Most open source PHP packages are built around a MySQL database.

Learning MySQL

There are loads of online resources for learning MySQL, though this one is probably the best to start with

Working with MySQL

For a PHP developer, it is well worth getting phpMyAdmin or Adminer installed.

PHPStorm also offers a lot of MySQL functionality

Command Line MySQL

For working with MySQL on the command line, the first thing we should do is create a ~/.my.cnf file

This can have the correct MySQL username and password in there.

Warning

Make sure you set permissions so only you can read the file

1
2
3
4
5
6
7
echo "

[client]
user=root
password=rootpass

" > ~/.my.cnf

Once this is in place then you can run the mysql* command line tool without having to continually put in your user and pass. This also improves security because it stops sensitive details leaking into the currently running process list and command history.

Dumping and Migrating Databases

As a developer it is quite common for us to need to copy and move databases around between servers and environments.

The command to dump a database to a text file is mysqldump

1
mysqldump --single-transaction -h [dbHost] -u [dbUser] -p [dbName] > {dump_name}.sql

Skipping Data for Some Tables

Often there will be certain tables for which you don't really need the data and can save a lot of time and bandwidth by not including the data.

We can do handle this as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
#!/bin/bash

dbName="database_name"

dumpName="${dbName}.sql"

#list of tables for which we need the data
dataTables="
orders
etc
etc
"

echo "Dumping full DB structure:"
mysqldump --verbose --no-data=true $dbName > $dumpName

echo "Now Dumping Data:"
mysqldump $dbName $dataTables --verbose >> $dumpName

Piping Dumps over SSH

If we are moving database dumps between servers then it can save a bit of time and effort and also disk space to pipe the dump directly over SSH

We'll use pv to show us progress, so make sure you yum -y install pv

Here are a few variations for this idea:

Dump Local DB, Gzip, Pipe through SSH (with progress) to File on Remote Server

1
mysqldump dbName | gzip -c | ssh user@host 'cat > /path/to/dbdump.sql.gz'

Dump Local DB, Gzip, Pipe through SSH (with progress), Extract straight into MySQL

1
mysqldump dbName | gzip -c | pv | ssh user@host 'zcat -d | mysql dbName'

Dump Remote DB, Gzip and Pipe to Local File

1
ssh user@host -- "mysqldump dbName | gzip -c" > /path/to/dbdump.sql.gz

See other related pages