MySQL

From SlackWiki
Jump to: navigation, search


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.