setup mysql on a raspberryPI

gistfile1.txt

## shell commands I used to setup mysql on my raspberryPI and enable SQLyog access to it from my laptop

# install mysql using these instructions
# http://databaseblog.myname.nl/2013/01/how-to-install-mysql-succesfully-on.html

# Verify the MySQL Client was installed

mysql --version

# alter the settings to enable it to be a server on your lan

# on a raspberryPI, my configuration file was here. 

sudo vi /etc/mysql/my.cnf

# bind-address = 192.xx.xx.xx
# use your lan address for the raspberryPI

# then restart the server
sudo /etc/init.d/mysql restart

# I had some connectivity issues - so ensure the firewall allows the connections
sudo /sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT
# this allows my raspberry to accept port 3306 connections from ip addresses inside my lan from 192.168.0.0 to 192.168.0.24
# so adjust as necessary

# http://stackoverflow.com/questions/6239131/how-to-grant-remote-access-permissions-to-mysql-server-for-user
# be sure to set up a user that you can connect with. Users in mysql are pegged to machines, so you need to do
# the following:
sudo mysql -u root -p
# to do this, first chose a password for your remote user. Lets choose 'foobar' for the moment.
select password('foobar');

+-------------------------------------------+
| password('foobar')                        |
+-------------------------------------------+
| *9B500343BC52E2911172EB52AE5CF4847604C6E5 |
+-------------------------------------------+
1 row in set (0.00 sec)

# select that long hex string, and use it when granting privileges to your user:

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' WITH GRANT OPTION;


# then restart your server
sudo /etc/init.d/mysql restart

# and in SQLyog community edition, you can create a connection for raspberryDB, and put in user=testuser, ps=foobar along 
# with the IP address, and you should be able to connect!

##### note that you may need to install this too on your PI which is the mysql dev libraries for clients.
##### which I needed to get RMySql running in the R programming language (which i also run on raspberryPI too)

sudo apt-get install libmysqlclient-dev

# note: in R use install.packages('RMySQL')
# and this will now build based on the successful libraries installed via libmysqlclient-dev
# and loaded via library(RMySQL)