MySQL Cheatsheet

From Mavaball

This page briefly describes the subset of MySQL that you need to know to do simple backups and restores of websites that use MySQL as a database backend (e.g., WordPress, Joomla). This is primarily targeted towards Linux.

Contents 1 Installing MySQL 2 Creating a new user and database 3 Backup the database 3.1 Example Backup script 3.2 Add backup script to cron 4 Restore

Installing MySQL

You're on your own. Shouldn't be too hard. Most distributions and website hosts already have it installed for you.

Creating a new user and database

To do anything interesting with MySQL, you'll need to create a new database, and you typically also want to create a user with access only to that database. Essentially, you'll need to think of creative names for these things:

• database_name
• user_name
• user_password

You'll also need the database host, which could be localhost if your hosted locally.

• database_host

Log into mysql as root (or similar exalted user):

mysql -h database_host -u root -p

Enter your password and type these commands at the mysql prompt:

mysql> CREATE DATABASE database_name;
mysql> CREATE USER 'user_name'@'database_host' IDENTIFIED BY 'user_password';
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'database_host';
mysql> exit

At this point, you should be able to install your webapp.

Backup the database

Example Backup script

#!/bin/bash

# Local Variables
FNAME="my_prefix_`date +%Y-%m-%d`"
SITEDIR="/path/to/website/dir"
BACKUP="/path/to/backup/dir"
# use 'localhost' for locally hosted databases
DBHOST="mysql.example.com"

# Get mysqldump
mysqldump -h $DBHOST --database database_name -u user_name -puser_password --add-drop-table -B > ${SITEDIR}/${FNAME}.sql
# Zip up the data base and custom directories
tar -cjf ${BACKUP}/${FNAME}.tar.bz2 -C ${SITEDIR} .
# tar -cjf ${BACKUP}/${FNAME}.tar.bz2 -C ${SITEDIR} images/ ${FNAME}.sql LocalSettings.php extensions/
# Make this file unreadable, except to myself
chmod 600 ${BACKUP}/${FNAME}.tar.bz2
# remove the unneeded sql dump file (it's in the zip now)
rm ${SITEDIR}/${FNAME}.sql

Add backup script to cron

The next thing is to add this backup script to cron so that you periodically backup the database. (details TBD)

Restore

To restore the database (possibly on a different machine), first make sure that you create (or have already created) the same user with the same password with access to the same database.

Execute this command:

mysql -h database_host -u user_name -puser_password database_name < backup.sql

where database_host is where the database is hosted (possibly localhost), user_name is the database username, user_password is the database user password, database_name is the name of the database, and backup.sql is the sql file previously generated by mysqldump.

That should be it!