Thread: I installed MySQL (32bits) on my Imac (OSX 10.6.8), but what should I do next?

So beginning to study database stuff.
I installed MySQL (32bits) on my Imac (OSX 10.6.8).
The status panel says "running"

So what should I be doing next?

I work from a Netbean java platform...
and I have a beginning/ advanced java knowledge....

I am following instructions:
-If you are using Netbeans IDE, than you have already the driver at hand. Inside the Projects tab, right click on the Libraries node and select Add Library option. From the list of options, select MySQL JDBC Driver.

1) I created a new project in Netbeans: learningdatabase
2) added the MySQL JDBC Driver to it
3) and compiled the next small app
4) and got an error code
So what is going on next?

package learningdatabase;
 
/**
 *
 * 
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
 
public class Version {
 
    public static void main(String[] args) {
 
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
 
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "testuser";
        String password = "test623";
 
        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            rs = st.executeQuery("SELECT VERSION()");
 
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
 
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Version.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
 
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }
 
            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(Version.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}

19-ago-2013 8:48:42 learningdatabase.Version main
GRAVE: Access denied for user 'testuser'@'localhost' (using password: YES)
java.sql.SQLException: Access denied for user 'testuser'@'localhost' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:3423)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:910)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java: 3923)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:12 73)
at com.mysql.jdbc.ConnectionImpl.createNewIO(Connecti onImpl.java:2031)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImp l.java:718)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connect ion.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Construc tor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:40 6)
at com.mysql.jdbc.ConnectionImpl.getInstance(Connecti onImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonReg isteringDriver.java:282)
at java.sql.DriverManager.getConnection(DriverManager .java:582)
at java.sql.DriverManager.getConnection(DriverManager .java:185)
at learningdatabase.Version.main(Version.java:33)
BUILD SUCCESSFUL (total time: 1 second)

--- Update ---

========================================
command line instructions:

-If you want to compile the examples from the command line, go to the site MySQL :: MySQL Connectors and download the MySQL connector for the Java language.

I downloaded that link but when I try this:
$ javac zetcode/Version.java
I get:
javac: file not found: zetcode/Version.java

Assuming, that you have put the connector jar file into the lib directory and using package zetcode, you compile and run the first example this way.

and when I do:
java -cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/Version
I get:
Exception in thread "main" java.lang.NoClassDefFoundError: zetcode/Version
Caused by: java.lang.ClassNotFoundException: zetcode.Version
at java.net.URLClassLoader$1.run(URLClassLoader.java: 202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.j ava:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:3 06)
at sun.misc.Launcher$AppClassLoader.loadClass(Launche r.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:2 47)

Hello.
Looks like the username you are using "testuser" doesn't have enough privileges.
Try to give privileges at the MySql command using GRANT command and run your program again.

Syed.

Syedhal, where at the command line?

these are my mysql helps....

mysql> help

For information about MySQL products and services, visit:
MySQL :: The world's most popular open source database
For developer information, including the MySQL Reference Manual, visit:
MySQL :: Developer Zone
To buy MySQL Network Support, training, or other products, visit:
https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

--- Update ---

=====//==//============//=============//=======


I think I have to read some mysql manual first!
any suggestions?

Hello.
I can give you the GRANT command. But I want you to try sincerely first.
Just google "execute GRANT command in MySql".
Find that command and just type at the MySql command prompt (the way you typed "help") earlier.
Grant full privileges to the "testuser".

Syed.

Don't worry I'll find it but it is like a whole new subject this mysql
It is overwhelming and try to understand where to start!
First there is the installing issue with many, many command line --I love--
stuff. I got a nice looking manual but very php based...
MySQL Tutorial
So I am learning php as well, besides studying unix basics which is very
use full for that command line installing stuff, right!

(I feel like the guy in a circus throwing balls and adding
another and another and another one etc.....)

"Get familiar with the database. Use the command line to connect to the database, create users, create tables, and learn about database normalization, keys, indexes, and referential integrity (dependent upon the storage engine for MySQL) "

That is through unix right?

so trying this one:
mysql -u root -p

and getting this:
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

But before even getting to:
mysql> GRANT ALL ON demo.* TO user1@localhost IDENTIFIED BY 'mypassword';

http://www.cyberciti.biz/faq/mysql-c...root-password/
So what is my root mysql password different from my root password?
I do not remember setting it hahahahahaha!

Should I install mysql again in order to get that passwd?

Mann this is not easy, I got so far now:

willem$ /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
130821 09:30:17 mysqld_safe Logging to '/usr/local/mysql/data/iMac.local.err'.
touch: /usr/local/mysql/data/iMac.local.err: Permission denied
chmod: /usr/local/mysql/data/iMac.local.err: Permission denied
touch: /usr/local/mysql/data/iMac.local.err: Permission denied
chown: /usr/local/mysql/data/iMac.local.err: Permission denied
130821 09:30:17 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
/usr/local/mysql/bin/mysqld_safe: line 121: /usr/local/mysql/data/iMac.local.err: Permission denied
rm: /usr/local/mysql/data/iMac.local.pid: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 158: /usr/local/mysql/data/iMac.local.err: Permission denied
touch: /usr/local/mysql/data/iMac.local.err: Permission denied
chown: /usr/local/mysql/data/iMac.local.err: Permission denied
chmod: /usr/local/mysql/data/iMac.local.err: Permission denied
130821 09:30:17 mysqld_safe mysqld from pid file /usr/local/mysql/data/iMac.local.pid ended
/usr/local/mysql/bin/mysqld_safe: line 121: /usr/local/mysql/data/iMac.local.err: Permission denied

willem$ /usr/local/mysql/bin/mysql mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

--- Update ---

Ok I checked in as ROOT and did this
bash-3.2# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
130821 09:46:22 mysqld_safe Logging to '/usr/local/mysql/data/iMac.local.err'.
130821 09:46:22 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
but now I can not get out anymore.....

--- Update ---

OK HERE IS THE CORRECT SCRIPT FOR ME:

Stopping MySQL
First stop the service. You can either do this using the preference pane if you have that installed if you don’t you should be well aware of doing in from the terminal.

Skipping Access Tables
Alright – so open up a Terminal window and execute:

For MySQL 5 Installations do – (thanks to RY for pointing it out):
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables

Running the Reset
Ok – so you have safe_mysqld running in one Terminal window, now open up another one and execute “/usr/local/mysql/bin/mysql mysql” (no quotes). If you aren’t familiar you are opening up the MySQL console and opening the mysql table.

Write the reset query into the console as follows:

UPDATE user SET Password=PASSWORD('YOUR_PASSWORD')
WHERE Host='localhost' AND User='root';
flush privileges;
Replacing “YOUR_PASSWORD” with your desired password of course. Once you’ve done that just exit the console “exit;” close the safe_mysqld execution and restart your MySQL server in normal mode.

--- Update ---

+++++++++++++++++++++++++++++
thanks copeg I got in with my new created password!

--- Update ---

here is a link skip-grant-tables Now the MySQL server should be running wide open (ie. every user that logs in has full access) - See more at: How Can I Recover My Lost MySQL root Password? | franzone.com

but after skip-grant-tables
Now the MySQL server should be running wide open (ie. every user that logs in has full access) - See more at: How Can I Recover My Lost MySQL root Password? | franzone.com

Should I be worried and do something about this?

But there is more trouble, what about this:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

For the moment I stopped the server like this:
sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
and got:
Password:
Stopping MySQL database server
iMac:mysql willem$

So I am safe for the moment!

But having issues with
user_name
password
host_name

I do not know where they are and how to set them
I am looking.......
(Have set my root passwd at least!)

--- Update ---

The host_name seems to be the localhost and when doing this:
iMac:mysql willem$ telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
4
5.1.70,9xsW<c85B+Q4Q{7=k'Connection closed by foreign host.
iMac:mysql willem$

so what means this now (have some more manuals and will start reading)

--- Update ---

=========================
when trying to create a user by:

INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');

I got
ERROR 1046 (3D000): No database selected

thanks, I'll go into some reading first

Things are starting to work out, I did create my DB "realestate"
and thinking about its design and found this for real dummies like me:
A Simple Guide to Database Design in MySQL - LINUX For You

--- Update ---

Things are starting to work out, I did create my DB "realestate"
and thinking about its design and found this for real dummies like me:
http://www.linuxforu.com/2011/05/a-s...sign-in-mysql/

Here is some basic info of getting started with mysql on comment line.
Next I will see how this stuff connects with java, right?

Reseting root password MySQL
(file location on my comp: ls /usr/local/mysql/bin)

1) Stopping MySQL:
sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop

2) login as root: sudo -s password (exit to go out)
(ask: whoami)

3) /usr/local/mysql/bin/mysqld_safe –skip-grant-tables #ind the correcgt path on comp

4) Running the Reset

Ok – so you have safe mysqld running in one Terminal window, now open up another one and execute “/usr/local/mysql/bin/mysql mysql” (no quotes). If you aren’t familiar you are opening up the MySQL console and opening the mysql table.

Write the reset query into the console as follows:

UPDATE user SET Password=PASSWORD('YOUR_PASSWORD')
WHERE Host='localhost' AND User='root';
flush privileges;
Replacing “YOUR_PASSWORD” with your desired password of course. Once you’ve done that just exit the console “exit;” close the safe_mysqld execution and restart your MySQL server in normal mode.

To Start and Stop Server
sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop /start #from root
here is the mysql stuff located:
/usr/local/mysql/bin # on my comp
go to the above address and
from root authority do: ./mysqld_safe & #to start
or (of course always from root: sudo -s)
/usr/local/mysql/bin/mysqld_safe & #to start
./mysqladmin -u root -p shutdown # to shut down always from root
ps -ef | grep mysqld # to see the thread



Log in, Creating a Database and Quiting Mysql
-mysql -u root -p #log in
(password mysql>)
create database xxxxx; #create a database
-mysql> use xxxxx; #going into exsisting database
or
mysql -h host -u user -p xxxxxx
(Database changed)
-mysql> show tables;
-mysql> \q (quit) #log out

Example Create Table:
(from: MySQL for Absolute Beginners )
-mysql> create database bookstore;
-mysql> use bookstore;
-mysql> show bookstore;

-mysql> DROP TABLE IF EXISTS books;
CREATE TABLE books (
id int unsigned NOT NULL auto_increment, # Unique ID for the record
title varchar(255) NOT NULL, # Full title of the book
author varchar(255) NOT NULL, # The author of the book
price decimal(10,2) NOT NULL, # The price of the book
PRIMARY KEY (id) );
-mysql> show tables;
-mysql> explain books;
Example Adding Records:

mysql> INSERT INTO books ( title, author, price )

VALUES ( "Nineteen Eighty-Four", "George Orwell", 8.99 ),
( "The Grapes of Wrath", "John Steinbeck", 12.99 ),
( "The Wind-Up Bird Chronicle", "Haruki Murakami", 7.99 );


Example Retrieving Records:

(SELECT fieldNames FROM tableName [WHERE criteria])

-mysql> SELECT * FROM books;

-mysql> SELECT * FROM books WHERE id = 2;

-mysql> SELECT * FROM books WHERE title = "Nineteen Eighty-Four";

-mysql> SELECT * FROM books WHERE price < 10 AND price > 5;

-mysql> SELECT title, author FROM books;

So through the command line I started mysql and I can get into mysql using my password (by owning to command line root)!
-I created a test database called: bookstore;
-created a table called: books;
-added some records to that table (id, title, author and price)!
-and finally filled up the database with some books.

Now turning to Java I should be able to connect. right?
The principle connector code line would look like this?

Connection con = DriverManager.getConnection(
"jdbc:myDriver:myDatabase",
username,
password);
I have tested my host through unix, which is:

ysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| iMac.local |

So my question is what would be in my case (connecting with bookstore/ books):
"jdbc:myDriver:myDatabase"
username
password

====================
the three arguments of getConnection are they:
1) path to the jdbc driver (have the driver but can not find it)
2) username = mysql or a to be created usergroup for mysql (found one in the file group called _mysql)
3) passwd = mysql general passwd or a new to be created passwd for the usergroup

when feeding for:
1) "MySQL:jdbc:mysql://localhost:3306/"
2) root
3) the root password (of mysql root of course) I get:

29-ago-2013 15:31:26 learningdatabase.connectToAndQueryDatabase main
GRAVE: null
java.sql.SQLException: No suitable driver found for MySQL:jdbc:mysql://localhost:3306/
at java.sql.DriverManager.getConnection(DriverManager .java:602)
at java.sql.DriverManager.getConnection(DriverManager .java:185)
at learningdatabase.connectToAndQueryDatabase.<init>( connectToAndQueryDatabase.java:33)
at learningdatabase.connectToAndQueryDatabase.main(co nnectToAndQueryDatabase.java:24)
BUILD SUCCESSFUL (total time: 1 second)

So where to get the driver at the NetBeans context?
(I can see the driver at the project folder of the above application at the directory: libraries,
called: MySQL JDBC Driver - mysql-connector-java-5.1.6-bin.jar)

I got the Java connection through the jdbc driver, so mysql is talking to Java finally
1) "jdbc:mysql://localhost:3306/mysql" on my comp
2) root
3) passwd of the mysql root user
I can deal with the "pure" Java programming but,
these environmental issues, of setting drivers etc., are really challenging!

thread closed!

--- Update ---

here is the java part:

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
 
/**
 * Bassic Java MySQL connector
 * bassed on jdbc driver/ mysql username and password
 * and does some sql stuff at the doSomeSQLstuff method
 * of a pre-installed small db called bookstore
 * table books with some entries done by mysql command line
 *
 */
public class connectToAndQueryDatabase {
    private String url = "jdbc:mysql://localhost:3306/mysql";
    private static String username, passwd;
    private  Statement stmt;
 
    public static void main(String[] args) {
        try {
            connectToAndQueryDatabase cd = new
                    connectToAndQueryDatabase("root", "xxxxxxx");
 
        } catch (SQLException ex) {
            System.out.println("ERROR in connection of mysql see errortrace:  ");
            Logger.getLogger(connectToAndQueryDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
 
    public connectToAndQueryDatabase(String username,
                        String passwd) throws SQLException {
 
        Connection con = (Connection) DriverManager.getConnection(
                url, // the jdbc driver that makes mysql talk to java
                this.username = username,
                this.passwd = passwd);
        stmt = (Statement) con.createStatement();
        doSomeSQLstuff();
 
    }
 
    public void doSomeSQLstuff() {
        try {
            stmt.executeQuery("USE bookstore");
            ResultSet rs = stmt.executeQuery("SELECT id, title, author, price FROM books");
            while (rs.next()) {
                String s1 = rs.getString("id");
                String s2 = rs.getString("title");
                String s3 = rs.getString("author");
                float x = rs.getFloat("price");
                System.out.println("books " + s1 + " / " + s2 + " / " + s3 + " / " + x);
            }
        } catch (SQLException ex) {
            System.out.println("ERROR in getting the mysql stuff see errortrace:  ");
            Logger.getLogger(connectToAndQueryDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
 
    }

output:

run:
books 1 / Nineteen Eighty-Four / George Orwell / 8.99
books 2 / The Grapes of Wrath / John Steinbeck / 12.99
books 3 / The Wind-Up Bird Chronicle / Haruki Murakami / 7.99
BUILD SUCCESSFUL (total time: 1 second)