How To Set Up MySQL

http://ariejan.net/2007/12/12/how-to-install-mysql-on-ubuntudebian/

https://help.ubuntu.com/11.04/serverguide/C/mysql.html

MySQL is a fast, multi-threaded, multi-user, and robust SQL database server. It is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software.

Get ready

sudo apt-get update
sudo apt-get dist-upgrade

Installation

To install MySQL, run the following command from a terminal prompt:

sudo apt-get install mysql-server

During the installation process you will be prompted to enter a password for the MySQL root user.

Once the installation is complete, the MySQL server should be started automatically. You can run the following command from a terminal prompt to check whether the MySQL server is running:

sudo netstat -tap | grep mysql

When you run this command, you should see the following line or something similar:

tcp        0      0 localhost:mysql         *:*                     LISTEN      2556/mysqld

If the server is not running correctly, you can type the following command to start it:

sudo /etc/init.d/mysql restart

Configuration

You can edit the /etc/mysql/my.cnf file to configure the basic settings — log file, port number, etc. For example, to configure MySQL to listen for connections from network hosts, change the bind-address directive to the server's IP address:

# — daq — bind-address = 127.0.0.1

After making a change to /etc/mysql/my.cnf the mysql daemon will need to be restarted:

sudo /etc/init.d/mysql restart

The mysql prompt

In order to get to the mysql prompt you will want to issue the command:

mysql -u root -p mysql

Where root is the MySQL administrative user (most like it is root).

You will be prompted for the MySQL administrators password. After you have successfully authenticated you will have a new prompt that looks like:

mysql>

You are now at the MySQL prompt. You only have one command to enter for this to work. You will want to enter this command carefully:

GRANT ALL PRIVILEGES ON *.* TO username@address IDENTIFIED BY “password”;

GRANT ALL PRIVILEGES ON *.* TO root@192.168.1.198 IDENTIFIED BY “QBW password”;

FLUSH PRIVILEGES;

Where username is the username on the remote machine that will be connecting, address is the IP address of the remote machine, and password is the password that will be used by the remote user.

When that command is issued successfully you should see something like:

Query OK, 0 rows affected (0.00 sec)

As long as you get Query OK, you should be good to go.

Now when you need to connect from a remote machine you will use the IP address of the MySQL server, the username you entered in the MySQL command prompt, and the username will be the username you entered in the MySQL command prompt.

That’s it. Your MySQL server is ready to accept remote connections.

Tutorial for MySQL

http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html

Show Grants for user "root" and on ip 192.168.1.198

select * from information_schema.user_privileges where grantee like "'root'%";

select * from information_schema.user_privileges where grantee like "%'192.168.1.198'";

Delete Grants for user "root" on ip 192.168.1.198

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'root'@'192.168.1.198' ;

Read this again (or more like it) http://ubuntu-commands.blogspot.com/2008/11/how-do-i-enable-remote-access-to-mysql.html

Root Passsword change

If you would like to change the MySQL root password, in a terminal enter:

sudo dpkg-reconfigure mysql-server-5.1

The mysql daemon will be stopped, and you will be prompted to enter a new password.

You may need to open port if using firewalls

You need to open port 3306 using iptables or BSD pf firewall.
A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT

OR only allow remote connection from your web server located at 10.5.1.3:

/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp –destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp –destination-port 3306 -j ACCEPT

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.198 -p tcp –destination-port 3306 -j ACCEPT

Did not do these.

sudo mysqladmin -u root -h localhost password 'mypassword'
sudo mysqladmin -u root -h myhostname password 'mypassword'

update db set Host='192.168.1.198' where Db='webdb';
update user set Host='192.168.1.198' where user='webadmin';

Un-install/Remove

sudo apt-get –purge remove mysql-server mysql-common mysql-client

Resources

See the MySQL Home Page for more information.
The MySQL Handbook is also available in the mysql-doc-5.0 package. To install the package enter the following in a terminal:
```
											sudo apt-get install mysql-doc-5.0
	
```
The documentation is in HTML format, to view them enter file:///usr/share/doc/mysql-doc-5.0/refman-5.0-en.html-chapter/index.html in your browser's address bar.
For general SQL information see Using SQL Special Edition by Rafe Colburn.