does anyone have a very descriptive, down to earth tutorial on setting up mysql... preferably on an ubuntu system?
I think this is pretty much what you are looking for:
This is a little guide I wrote to myself when I started to use mysql with mythtv. Hope it helps.
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
To initialize the grant tables
1. Start the server:
1.1 Two ways to start the server:
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'
/usr/bin/mysqladmin -u root -h amd.site password 'new-password'
# cd /usr/bin/
# mysqladmin -u root password 'bouv'
# /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:
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
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.
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.
ERROR 1049: Unknown database 'mydog'
Solution: create the database
You should see a list of a lot of tables used by mydog
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.
thanks guys, this was a really great help!
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?
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.
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'@'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
|All times are GMT -5. The time now is 03:30 AM.|