Category Archives: sql

All things sql related

Galera / WSREP – MySQL cluster on Ubuntu 12.04 LTS – basic initial setup

mysql-logoIt took me about 4 hours of actual research, and trial and error, however I finally got a working Galera based WSREP MySQL cluster up and running. Here’s what it took and how the pieces came together:

Getting the install right took a couple of hours by itself for some reason, here’s the order that worked best on Ubuntu 12.04 LTS:
Continue reading

MySQL basics

Don’t wait for a DBA folks, their probably off normalizing their hair. How about that web-site that runs your ticketing system, is it being backed up? Do you have any idea where you MySQL data is stored? Here’s enough MySQL information to help you sleep at night knowing your data is somewhat secure:

  1. Backup a database:
    mysqldump -C -u <user> -p --database <dbname> > <outfile>
  2. Restore a database from backup:
    mysql -u <user> -p <dbname> < <infile>
  3. Create a database:
    echo "create database <dbname>;" | mysql -u root -p
  4. Drop a database:
    echo "drop database <dbname>;" | mysql -u root -p
  5. Create a user (With password):
    echo "create user '<username>'@'localhost' identified by '<password>';" | mysql -u root -p
  6. Destroy a user:
    echo "drop user '<username>'@'localhost';" | mysql -u root -p
  7. Grant permission on a database to a user:
    echo "grant <permission> on <dbname>.*; to <user>;" | mysql -u root -p
  8. Revoke permission on a database to a user (Opposite of grant):
    echo "revoke <permission> on <dbname> to <user>;" | mysql -u root -p
  9. MySQL configuration information:
    /etc/my.cnf Contains data file locations – change the actual location or create a symbolic link in the path to your data (NOTE: This requires enabling symbolic links)
  10. Starting and stopping mysql:
    To start: /usr/bin/mysqld_safe &
    To stop: /usr/bin/mysqladmin shutdown

I’ll add more as necessary – if you’d like to see specific examples let me know.