How to: Use seperate local MySQL DB

By Easton , 27 April, 2009

This was quite frustrating for me. I tried everything; why doesn't it connect to to the database? I finally figured it out: wrong IP. Anyway, here are some tricks that I used to get Server A to connect to Server B, with MySQL.

First things first. Change your bind-address. For Debian, edit your my.cnf in /etc/mysql
Change the bind-address to your local LAN IP. Restart MySQL with:

^{/etc/init.d/mysql restart}

Now to tackle MySQL...

^{mysql -u root -p

<password>}

Create the user and assign privileges:

Note, 192.168.0.100 should be the clients IP, not the servers.

^{CREATE USER 'user1'@'192.168.0.100' IDENTIFIED BY 'pass1';}

^{GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'192.168.0.100';}

^{GRANT ALL ON *.* TO 'user1'@'192.168.0.100';}

And finally create the database:

^{create database databasename;}

I'm no SQL guru, so please correct me if I messed up ;)

Hope that helps!