LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 12-14-2006, 05:36 PM   #1
erat123
Member
 
Registered: Oct 2006
Distribution: Ubuntu
Posts: 69

Rep: Reputation: 16
mysql setup!?


does anyone have a very descriptive, down to earth tutorial on setting up mysql... preferably on an ubuntu system?
 
Old 12-15-2006, 10:19 AM   #2
Mega Man X
Guru
 
Registered: Apr 2003
Location: ~
Distribution: Ubuntu, FreeBSD, Solaris, DSL
Posts: 5,339

Rep: Reputation: 63
I think this is pretty much what you are looking for:

http://ubuntuguide.org/wiki/Ubuntu_E...atabase_Server

Regards!
 
Old 12-15-2006, 09:01 PM   #3
terryxela
Member
 
Registered: Dec 2006
Location: Denver, Colorado, USA
Distribution: SuSE 11.3
Posts: 125

Rep: Reputation: 17
mysql guide

This is a little guide I wrote to myself when I started to use mysql with mythtv. Hope it helps.

General Terminology:

MySQL is a RDBMS and it is composed of server program (mysqld) and one or more client programs (mysql etc). So MySQL is the whole package, mysql is just an interactive client.

An important point:
- database names must be different across the system
- table can have the same cames across the system

Requirements:

- MySQL installed (server/client). The client needs to be in the machine were you work; the server could be in any place.
- MySQL account in the server
- A database to work with

If it is the first time using MySQL after installation you may have to initialized data directory, the mysql database and the default grant tables. There is an installation script that you may have t run. With rpm installation this script is run automatically. Just in case:

Go to the /usr/bin

su
mysql_install_db --user=mysql

To initialize the grant tables


1. Start the server:

1.1 Two ways to start the server:

#/etc/init.d/mysql start

or

Yast sytem/Runlevel/mysql enable

This will start the service and at the begining will initialized the grant tables, creating a mysql and test databases. It runs the script mysql_install_db

1.2 How to Stop the Server

linux:/home/tdec # /etc/init.d/mysql stop

1.2 Hot to restart the Server

# /etc/init.d/mysql restart


2.REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
(note: amd is the name of my computer; bouv1 is a password I choose for this example)

To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
or
/usr/bin/mysqladmin -u root -h amd.site password 'new-password'

# cd /usr/bin/
# mysqladmin -u root password 'bouv'
or
# /usr/bin/mysqladmin -u root -h amd.site password bouv

3. Test the new password for root:


# mysql -u root -p mysql

mysql>SELECT host, user, password FROM user;
+------------+------+-------------------------------------------+
| host | user | password |
+------------+------+-------------------------------------------+
|localhost | root | *B38ADCFE256D2ECA9AED7A3183A0781EA5B9DAEB |
|amd.site | root | |
|amd.site | | |
|localhost | | |
+------------+------+-------------------------------------------+
4 rows in set (0.01 sec)
Do not delete ever these host/users!!

4. Create a database

mysql> CREATE DATABASE mydog;

This will create the database however to be able to use it it has to become the default otherwise mysql will not know which database from the server are you referring to.
To make it the default there are few ways to do it:

4.1

mysql> USE mydog;


4.2 the other way is to name it in the command line:

$mysql -p -u tdec mydog

This will allow the user tdec to access mydog database which will be the default. In this case we are working in the machine were the server is install (localhost). If the server is in another machine the command is similar

$mysql -h me.cis.net -p -u tdec mydog

You can check that you are using the database by issuing :
mysql> SELECT DATABASE();

it will show you the "mydog" database

5. Create the tables.

CREATE TABLE tbl_name ( column_specs)

Two ways to create it directly just typing or piping an already written table. (<)

CREATE TABLE mydog
(
name VARCHAR(10) NOT NULL
birth DATA NOT NULL
IQ VARCHAR(3) NULL
);

6. Important commands.

mysql>DESCRIBE user; describe the structure of the table
mysql>SELECT * FROM user; will show all data in the user table
mysql>SELECT host, user, password FROM user; will show the user and passord of each account

To modify existing record use UPDATE
UPDATE tbl_name
SET which columns to change
WHERE which records to update

With UPDATE as well as for DELETE is not a bad idea to test a WHERE with the SELECT statement.

mysql> DELETE user FROM user WHERE user='fufu';
will remove user='fufu'

mysql> UPDATE user
-> SET user='rex'
-> WHERE user='fufu'
-> ;

User name "fufu" will be change to "rex"
mysql> SELECT host, user, password FROM user;
will confirm the change.

7. Troubleshooting.

Is the mysql demon running?

Often show that it can not connect to the mysql socket.

ps ax | grep mysql

You should see several processes of mysqld running.
If not, the mysqld daemon isn't started, try:
sudo /etc/init.d/mysql start
and look for errors, also check /var/log/mysql.log for errors:
sudo tail /var/log/mysql.log

If mysqld is running, try:

nmap localhost (xnmap GUI)

you should see:
Starting nmap V. 2.54BETA31 ( www.insecure.org/nmap/ )
...
Port State Service
...
3306/tcp open mysql

If so, try this:
mysql -u -p mydog (of course provide user and password)

you should get a mysql> prompt.

If error:
ERROR 1049: Unknown database 'mydog'

Solution: create the database

Try:
\q

show tables;

You should see a list of a lot of tables used by mydog

if error:
Empty set (0.01 sec)

solution: table corrupt or none existant
Sometimes you can fix the table:

mysqlcheck -r -uxxxx -pxxxx mydog

replace the xxxx with user name and password.


-=terry(Denver)=-

Last edited by terryxela; 12-16-2006 at 06:28 PM.
 
Old 12-16-2006, 02:43 AM   #4
erat123
Member
 
Registered: Oct 2006
Distribution: Ubuntu
Posts: 69

Original Poster
Rep: Reputation: 16
thanks guys, this was a really great help!
 
Old 12-16-2006, 04:21 PM   #5
erat123
Member
 
Registered: Oct 2006
Distribution: Ubuntu
Posts: 69

Original Poster
Rep: Reputation: 16
just one more question. i got the database up and running. it resides on the sql server. i'm trying to use MySQL Administrator from my home computer to gain access to the server's database. but it's not allowing me. can anyone help me w/ that?
 
Old 12-16-2006, 06:24 PM   #6
terryxela
Member
 
Registered: Dec 2006
Location: Denver, Colorado, USA
Distribution: SuSE 11.3
Posts: 125

Rep: Reputation: 17
Add user different than localhost

You will have two basic steps

1. Create a new account (=add to the mysql the user name and host so you are permitted to access it) Two commands are important

GRANT that creates the account and determine the privileges
REVOKE which does the opposite

2. Be sure the firewall is not in the way.


The GRANT statement is a tricky one. It will specified the user as well as where he can connect from.

For example

GRANT ALL ON erat1db.* TO 'erat'@'%' IDENTIFIED by 'eratpassword';

this will create and account that have access to all tables in the erat1db to the user 'erat" that he can access from everywhere.

While

GRANT ALL ON erat1db.* TO 'erat'@'192.168.1.125' IDENTIFIED by 'eratpassword';

will only provide the same privilege but from the specified host only!.

After you do all this then check it using the paragraph #3 of the first message I sent you and you will see the new user and the new host and password

-=terry(Denver)=-
 
  


Reply

Tags
tutorial, mysql, connection, guide


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Setup lostnhell Linux - Server 30 08-09-2009 11:25 AM
how to setup mysql for mythtv?? jadukor Slackware 9 04-13-2006 01:01 AM
MySQL setup. sikofitt Programming 1 09-29-2005 04:43 AM
MySQL setup stops at command "./configure --prefix=/usr/local/mysql" k41184 Linux - Software 1 05-20-2004 02:44 PM
How do you setup a mysql database brandog Linux - Newbie 15 03-24-2003 06:13 PM


All times are GMT -5. The time now is 05:21 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration