MySQL

I installed the MySQL package, now what?

See Configuring MySQL for how to get mysqld running.

How do I start mysqld?

As root type: mysqld_safe &

How do I check that mysqld is running?

ps aux

If it's running, you should see several lines of something like this:

mysql 14429 0.0 6.4 58056 15544 ? S 18:44 0:00 /usr/libexec/mysqld --basedi... blah blah

mysql 14430 0.0 6.4 58056 15544 ? S 18:44 0:00 /usr/libexec/mysqld --bas... blah blah

How do I login to the MySQL console client?

mysql -u root -p

you will be prompted for root's password after which you will get the mysql> prompt.

How do I exit the MySQL console client?

exit;

How do I see what databases already exist?

FROM SHELL: echo "show databases;" | mysql -u root -p

FROM A MYSQL CLIENT: show databases;

How do I make a database?

FROM SHELL: mysqladmin -u root -p create database databasename

FROM A MYSQL CLIENT: CREATE DATABASE databasename;

It will ask you for the root password and then it will create the database.

How do I delete a database?

FROM SHELL: mysqladmin -u root -p drop database databasename

FROM A MYSQL CLIENT: DROP DATABASE databasename;

How do I create a table in a database?

Now you are starting to get complex, see More MySQL.

How do I connect to it?

You must add users to the database.

1) Log into mysqld as root: mysql -u root -p

It will ask you for root's password, enter it. You will then get the mysql> prompt

2) Add the user at localhost: GRANT privileges (usually ALL) PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password';

NOTES:

a) Even if you only want pople to connect from remote machines, you must first give them privileges as localhost. This seems to be a MySQL quirk.

b) To grant permissions on ALL databases, use *.* as the database name.

At this point, users should be able to connect and manipulate databases from localhost.

FROM REMOTE MACHINES:

3) Add permissions from remote machines: GRANT privileges (usually ALL) PRIVILEGES ON database.* TO 'username'@'IP address or machine name' IDENTIFIED BY 'password';

EXAMPLE:

mysql -u root -p

enter password

GRANT ALL PRIVILEGES ON mydatabase.* TO 'joebleau'@'localhost' IDENTIFIED BY 'mypassword';

GRANT ALL PRIVILEGES ON mydatabase.* TO 'joebleau'@'192.168.1.2' IDENTIFIED BY 'mypassword';

-->GOTCHA<-- Sometimes domain name resolution can screw up your connections, if your machine has a name in the /etc/hosts file or the DNS system, you may encounter a problem. Watch for connection errors and note if they say something like Connection for 'root'@'www.hawtsecks.com' is not allowed. Even though the machine is really localhost, you may need to grant permissions to "root@www.hawtsecks.com" or even "root@192.168.2.42" depending on your situation.

CAN'T CONNECT FROM A REMOTE MACHINE

If you are using programs to connect to your databases (e.g. Navicat, MySQLadmin, or web scripts) from a remote machine and you cannot get them to connect correctly, you should check:

- Make sure that mysqld is running.

- Make sure that your firewall isn't blocking the port (3306).

- Make sure you have been GRANT'd on to the database and from the correct machine address(see above).

- Make sure mysqld has networking on -->SLACKWARE 10+ GOTCHA<--

As of about Slackware 10.0, Patrick has set the /etc/rc.d/rc.mysqld script to start MySQL daemon with external connections ignored. To fix this, find the /etc/rc.d/rc.mysqld line that says SKIP="--skip-networking" and comment it out (put a "#" in front of it).

How do I ________?

For more helpful MySQL tips, see More MySQL.