Help answer threads with 0 replies.
Go Back > Forums > Linux Forums > Linux - Software
User Name
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.


  Search this Thread
Old 12-14-2006, 06:36 PM   #1
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, 11:19 AM   #2
Mega Man X
LQ Guru
Registered: Apr 2003
Location: ~
Distribution: Ubuntu, FreeBSD, Solaris, DSL
Posts: 5,339

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

Old 12-15-2006, 10:01 PM   #3
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


- 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

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


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

(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'
/usr/bin/mysqladmin -u root -h password 'new-password'

# cd /usr/bin/
# mysqladmin -u root password 'bouv'
# /usr/bin/mysqladmin -u root -h 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 |
| | root | |
| | | |
|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:


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 -p -u tdec mydog

You can check that you are using the database by issuing :

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. (<)


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 ( )
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


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.


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

Original Poster
Rep: Reputation: 16
thanks guys, this was a really great help!
Old 12-16-2006, 05:21 PM   #5
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, 07:24 PM   #6
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.


GRANT ALL ON erat1db.* TO 'erat'@'' 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



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 12:25 PM
how to setup mysql for mythtv?? jadukor Slackware 9 04-13-2006 02:01 AM
MySQL setup. sikofitt Programming 1 09-29-2005 05:43 AM
MySQL setup stops at command "./configure --prefix=/usr/local/mysql" k41184 Linux - Software 1 05-20-2004 03:44 PM
How do you setup a mysql database brandog Linux - Newbie 15 03-24-2003 07:13 PM > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 01:43 AM.

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