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