Running Linux, 5th Edition - Matthias Kalle Dalheimer [451]
An important thing you need to know about MySQL is that Linux user accounts are different from MySQL user accounts. In other words, MySQL has its own account management. Most people give their MySQL user accounts the same names as their Linux user accounts in order to avoid confusion, though.
By default, there is one MySQL account called root, which has no password (talk about "security by default"). This means that you can access the database server with the interactive command-line tool mysql as follows:
owl$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 4.1.13.
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
The -u option specifies the database user to use. If this does not work, maybe your MySQL installation has a password set for the root user. Try to find this password in the documentation and start the mysql program with:
owl$ mysql -u root -p
which will prompt you for the password.
Assuming that you have been able to log in to the database server, let's try to issue a command:[*]
mysql> show databases;
+-------------+
| Database |
+-------------+
| mysql |
| test |
+-------------+
2 rows in set (0.11 sec)
This tells you that two databases are managed by this database server. One is called mysql and contains MySQL's internal configuration information, including the usernames, and the other one is called test and can be used by you for your experiments. It's also no problem at all to create additional databases ; we'll show you how in a minute. As you can see, all SQL commands need to be terminated with a semicolon—probably in order to make the C programmers happy.
Now you should give the root account a password (in case it does not have one already). This is done with two SQL commands:
mysql> SET PASSWORD FOR root=PASSWORD('new_topsecret_passwd');
mysql>FLUSH PRIVILEGES
;
Notice again the semicolon at the end of these commands; if you forget to type them before pressing the Enter key, MySQL will just stare at you, waiting for you to enter more.
By the way, SQL commands are case-insensitive; we have written them in uppercase here because that makes it a bit easier to see where the command keywords and the variable parameters are in a SQL script.
Also note the use of the FLUSH PRIVILEGES command. This is important because only after this command has been executed will MySQL update its user database.
Now we want to create a new user called olof, which has the same access rights as root, except that it cannot create new users. Apart from that, olof may use and manipulate all MySQL databases on this database server:
mysql> GRANT ALL PRIVILEGES ON *.* TO olof@localhost IDENTIFIED BY 'olof_passwd';
mysql>FLUSH PRIVILEGES;
The user olof can log in to the database only from the local machine. This is a good idea since it leaves one less security issue to think about. We recommend that you only allow access from the local machine unless you have a very good reason not to do it this way. Even in the LAMP combo, local access is enough, because the web server process is running on the local machine, and this is the process that connects to the database, not the user's web browser process.
But if you really require access to the database over the network, you could use these commands instead:
mysql> GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY 'user_passwd';
mysql>FLUSH PRIVILEGES;
If you think that having all access rights except creating new users is a bit too much, let's create another user that may execute the