Erik: Copy from old

Erik — Thu, 04 Jun 2009 05:24:41 GMT

Copy from old

New page

[[Category:Tips]]

==I installed the MySQL package, now what?==
See [[Configuring MySQL]] for how to get [[mysqld]] running.

==How do I start mysqld?==
As root type: <code>mysqld_safe &</code>

==How do I check that mysqld is running?==
<code>ps aux</code>

If it's running, you should see several lines of something like this: 
:<code>mysql 14429 0.0 6.4 58056 15544 ? S 18:44 0:00 /usr/libexec/mysqld --basedi... blah blah</code> 
:<code>mysql 14430 0.0 6.4 58056 15544 ? S 18:44 0:00 /usr/libexec/mysqld --bas... blah blah</code> 

==How do I login to the MySQL console client?==
:<code>mysql -u root -p</code> 
:you will be prompted for root's password after which you will get the <code>mysql></code> prompt.

==How do I exit the MySQL console client?==
:<code>exit;</code>

==How do I see what databases already exist?==
:FROM SHELL: <code>echo "show databases;" | mysql -u root -p</code> 
:FROM A MYSQL CLIENT: <code>show databases;</code>

==How do I make a database?==
:FROM SHELL: <code>mysqladmin -u root -p create database databasename</code> 
:FROM A MYSQL CLIENT: <code>CREATE DATABASE databasename;</code>

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

==How do I delete a database?==
:FROM SHELL: <code>mysqladmin -u root -p drop database databasename</code> 
:FROM A MYSQL CLIENT: <code>DROP DATABASE databasename;</code>

==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: <code>mysql -u root -p</code> 
:: It will ask you for root's password, enter it. You will then get the <code>mysql></code> prompt 

:2) Add the user at localhost: <code>GRANT privileges (usually ALL) PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password';</code> 
::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 <code>*.*</code> 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: <code>GRANT privileges (usually ALL) PRIVILEGES ON database.* TO 'username'@'IP address or machine name' IDENTIFIED BY 'password';</code> 

:EXAMPLE:
:::<code>mysql -u root -p</code> 
:::enter password 
:::<code>GRANT ALL PRIVILEGES ON mydatabase.* TO 'joebleau'@'localhost' IDENTIFIED BY 'mypassword';</code> 
:::<code>GRANT ALL PRIVILEGES ON mydatabase.* TO 'joebleau'@'192.168.1.2' IDENTIFIED BY 'mypassword';</code>

:-->GOTCHA<-- Sometimes domain name resolution can screw up your connections, if your machine has a name in the <code>/etc/hosts</code> file or the DNS system, you may encounter a problem. Watch for connection errors and note if they say something like <code>Connection for 'root'@'www.hawtsecks.com' is not allowed</code>. 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 <code>/etc/rc.d/rc.mysqld</code> script to start MySQL daemon with external connections ignored. To fix this, find the <code>/etc/rc.d/rc.mysqld</code> line that says <code>SKIP="--skip-networking"</code> and comment it out (put a "#" in front of it).

==How do I ________?==
For more helpful MySQL tips, see [[More MySQL]].

MySQL - Revision history

Erik: Copy from old