MySQL

Setting Up MySQL

As per this guide:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-server

Starting MySQL

sudo service mysql start

Alternatively, if you want to hold on to the process:

sudo mysqld_safe

Connecting to MySQL

Local Host

mysql -u ${user}
#Enter password: ...
#Welcome to ...
mysql> USE ${db}

Alternatively,

mysql -u${user} -p${pw} ${db}

Remote Host

mysql -h ${host} -u${user} -p
#Enter password: ...
#Welcome to ...
mysql> USE ${db}

Alternatively,

mysql -h ${host} -u${user} -p${pw} ${db}

Replace ${host}, ${user}, and ${db} with your own credentials.

Remember that you shouldn’t put any spaces between -p and your password.

Shutting Down MySQL Server

Try:

sudo mysqladmin -u root -p shutdown

If that didn’t work, then your mysql-related executables are not under standard path. Run:

cd /
find -name 'mysql'

to find the installation path for your mysql.

Note: shutdown is not your password. It’s a command. You have to supply the password when prompted.

If mysqladmin is unable to shutdown the process, go to here and attempt the suggested methods.

Basic Queries

I’m not going to teach you the basics of MySQL, which can be found here.

For more advanced stuff, see the following tips:

Check if a query exists

mysql> SELECT EXISTS(SELECT * from TABLE WHERE COLUMN = VALUE ...) as `exists`

Encryption with AES

TIP : you may set BLOB as the datatype returned by AES_ENCRYPT.

To insert:

mysql> INSERT INTO TABLE (encrypted_field...) VALUE AES_ENCRYPT('SOME_PASSCODE'), ...

To Query:

SELECT * FROM TABLE WHERE encrypted_field = AES_ENCRYPT('SOME_PASSCODE') AND ...

Installing Node MySQL Driver

You should have Node.js and NPM.

Otherwise, go to the Node Tutorial.

npm install --save node-mysql