HEP System Administration

I want to be able to play around with mysql and php webpages. So I want to create/use a database as a new user.

mysql -u root -p
mysql> create web_events;

This creates a new database called web_events. Now I want a particular user to be able to do anything to it.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| web_events         |
+--------------------+
4 rows in set (0.00 sec)

mysql> grant all on web_events.* to user1@localhost identified by "secret";

mysql> select Host,User,Password from user;
+-----------+-------+------------------+
| Host      | User  | Password         |
+-----------+-------+------------------+
| localhost | root  | 1e3392c069a69a58 |
| localhost | user1 | 66d28a553dc2d3a7 |
+-----------+-------+------------------+
2 rows in set (0.00 sec)

Now I can log in as user1 and see the databases available.

hep:~: mysql -u user1 -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 19 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| test               |

| web_events         |

+--------------------+

3 rows in set (0.00 sec)
				

Start with:

 mysql -u username -p 

Then enter password.

Shows all databases allowed to see.

mysql> show databases;

Pick database to work on

mysql> use database_name;

See tables in database

mysql> show tables;

Get description of table

mysql> describe table_name;

Simple query to see all data in table_name

mysql> select * from table_name;

To prepare for websites using php and databasese, mysql-server was installed. Upon starting with /etc/rc.d/init.d/mysqld start, got an error message about group mysql being invalid. Turns out that we didn’t have a mysql group. Normally, the group mysql would get GID 27. However, that GID was already in use for us. So, mysql was given GID 241 because that was the GID of a directory created from the installation.

/etc/rc.d/init.d/mysqld start

mysqladmin -u root password 'new_password'

mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select Host,User,Password from user where user='';
+-----------+------+----------+
| Host      | User | Password |
+-----------+------+----------+
| comp      |      |          |
| localhost |      |          |
+-----------+------+----------+
2 rows in set (0.00 sec)

mysql> select Host,User,Password from user where Password='';
+-----------+------+----------+
| Host      | User | Password |
+-----------+------+----------+
| comp      | root |          |
| comp      |      |          |
| localhost |      |          |
+-----------+------+----------+
3 rows in set (0.00 sec)

Now need to delete those empty users.

mysql> delete from user where User='';
Query OK, 2 rows affected (0.00 sec)

mysql> select Host,User,Password from user where Password='';
+------+------+----------+
| Host | User | Password |
+------+------+----------+
| comp | root |          |
+------+------+----------+
1 row in set (0.00 sec)

mysql> delete from user where Password='';
Query OK, 1 row affected (0.00 sec)

mysql> select Host,User,Password from user;
+-----------+------+------------------+
| Host      | User | Password         |
+-----------+------+------------------+
| localhost | root | 1e3392c069a69a58 |
+-----------+------+------------------+
1 row in set (0.00 sec)

So we are now left with one user (root) on the localhost with a password. New users can be created to take care of different databases.