LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Software (http://www.linuxquestions.org/questions/linux-software-2/)
-   -   mysql setup!? (http://www.linuxquestions.org/questions/linux-software-2/mysql-setup-510509/)

erat123 12-14-2006 05:36 PM

mysql setup!?
 
does anyone have a very descriptive, down to earth tutorial on setting up mysql... preferably on an ubuntu system?

Mega Man X 12-15-2006 10:19 AM

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

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

Regards!

terryxela 12-15-2006 09:01 PM

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

erat123 12-16-2006 02:43 AM

thanks guys, this was a really great help!

erat123 12-16-2006 04:21 PM

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?

terryxela 12-16-2006 06:24 PM

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


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