Attachment 66558 Details for Bug 98704 – mysql.xml

mysql.xml

mysql.xml (text/plain), 31.09 KB, created by Chris White (RETIRED) on 2005-08-22 09:08:20 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Chris White (RETIRED)

Created: 2005-08-22 09:08:20 UTC

Size: 31.09 KB

patch

obsolete

><?xml version="1.0" encoding="UTF-8"?>
><!DOCTYPE guide SYSTEM "http://www.gentoo.org/dtd/guide.dtd">
>
>
><guide link="/home/chriswhite/mysql.xml" lang="en">
><title>MySQL Startup Guide</title>
><author title="Author">
><mail link="chriswhite@gentoo.org">Chris White</mail>
></author>
>
><abstract>
></abstract>
>
>
>
><license/>
>
><version>1.0</version>
><date>2005-07-10</date>
>
><chapter>
><title>Getting Started With MySQL</title>
><section>
><title>Background</title>
><body>
>
>
>MySQL is a popular database server that is used in various applications. SQL
>stands for (S)tandard (Q)uery (L)anguage, which is what MySQL uses to
>communicate with other programs. On top of that, MySQL has its own expanded
>SQL functions to provide additional functionality to users. In this document,
>we'll look at how to do the initial MySQL installation, setup databases and
>tables and create new users. Let's start out with the initial installation.
>
>
></body>
></section>
><section>
><title>MySQL Installation</title>
><body>
>
>
>First make sure you have MySQL emerged on your system. You can do this by
>simply running:
>
>
><pre caption="MySQL emerge">
># emerge mysql
></pre>
>
><note>
>If you require specific functionality from mysql, add <c>-v</c> after emerge to
>show what USE flags are avaliable. These USE flags will help you fine tune
>your installation.
></note>
>
>
>Upon completion of the installation, you will be given the following notice:
>
>
><pre caption="MySQL einfo message">
>You might want to run:
>"ebuild /var/db/pkg/dev-db/mysql-[version]/mysql-[version].ebuild config"
>if this is a new install.
></pre>
>
>
>Let's go ahead and run the given command since this is indeed a new installation:
>
>
><pre caption="MySQL configuration">
># ebuild /var/db/pkg/dev-db/mysql-[version]/mysql-[version].ebuild config
> * MySQL DATADIR is /var/lib/mysql
> * Press ENTER to create the mysql database and set proper
> * permissions on it, or Control-C to abort now...
>
> Preparing db table
> Preparing host table
> Preparing user table
> Preparing func table
> Preparing tables_priv table
> Preparing columns_priv table
> Installing all prepared tables
>
> To start mysqld at boot time you have to copy support-files/mysql.server
> to the right place for your system
>
> PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
> To do so, issue the following commands to start the server
> and change the applicable passwords:
> /etc/init.d/mysql start
> /usr/bin/mysqladmin -u root -h pegasos password 'new-password'
> /usr/bin/mysqladmin -u root password 'new-password'
> Depending on your configuration, a -p option may be needed
> in the last command. See the manual for more details.
>
><comment>I've deleted some MySQL non-ebuild specific information from here so
>as to keep this document as consistent as possible.</comment>
>
> * For security reasons you should set your MySQL root
> * password as soon as possible.	 
></pre>
>
><impo>
>As of mysql-4.0.24-r2, passwords are entered during the config phase making 
>root password entry more secure.
></impo>
>
>
>Pressing <c>ENTER</c> when prompted while configuring the MySQL database. What
>is basically being done here is that the main MySQL database is being setup.
>This database contains administrative information such as databases, tables,
>users, permissions and more. The configuration recommends that you change your
>root password as soon as possible. We will definitely do this, otherwise
>someone could come along by chance and hack our default setup MySQL server. The
>configuration has already presented us with a way to set our password up, so
>let's go ahead and follow the instructions:
>
>
><pre caption="Setting up your MySQL root password">
># /etc/init.d/mysql start
> * Re-caching dependency info (mtimes differ)...
> * Starting mysqld (/etc/mysql/my.cnf) ...
> * [ ok ] 
>$ /usr/bin/mysqladmin -u root -h localhost password 'new-password'
></pre>
>
>
>You can now test that your root password was successfully configured by trying
>to login to your MySQL server:
>
>
><pre caption="Logging into the MySQL server using mysql">
>$ mysql -u root -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 4 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>The <c>-u</c> switch sets the user that will be logging in. The <c>-h</c>
>switch sets the host. This will usually be <c>localhost</c> unless you are
>setting up a remote server. Finally, <c>-p</c> tells the mysql client that you
>will be entering a password to access your database. Notice the
><c>mysql&gt;</c> prompt. This is where you type in all your commands. Now that
>we're in the mysql prompt as the root user, we can begin to setup our database.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>Setting Up The Database</title>
><section>
><title>Creating A Database</title>
><body>
>
>
>We now have a mysql prompt displayed. This prompt is where we type our commands
>to the MySQL server. First let's take a look at the databases we currently
>have. To do so, we use the <c>SHOW DATABASES</c> command as shown here:
>
>
><pre caption="Displaying MySQL databases">
>mysql&gt; SHOW DATABASES;
>+----------+
>| Database |
>+----------+
>| mysql |
>| test |
>+----------+
>2 rows in set (0.09 sec)
></pre>
>
><impo>
>Please remember that MySQL commands should end with semicolons!
></impo>
>
>
>Despite the fact that a test database is already created, we're going to create
>our own for a more complete guide. Databases are created using the <c>CREATE
>DATABASE</c> command. We'll create one named "gentoo" as shown here:
>
>
><pre caption="Creating the gentoo database">
>mysql&gt; CREATE DATABASE gentoo;
>Query OK, 1 row affected (0.08 sec)
></pre>
>
>
>The response lets us know that the command was executed ok. In this case, 1
>row was modified. This is a reference to the main mysql database, which
>carries a list of all the databases. You won't need to worry too much about
>the background details. The last number is how fast the query was executed.
>We can verify the database was created by running the <c>SHOW DATABASES</c>
>command again:
>
>
><pre caption="Verifing the database is created">
>mysql&gt; SHOW DATABASES;
>+----------+
>| Database |
>+----------+
>| gentoo |
>| mysql |
>| test |
>+----------+
>3 rows in set (0.00 sec)
></pre>
>
>
>Indeed our database has been created. In order to work with creating tables
>for our new gentoo database, we need to select it as our current database.
>To do so, we use the<c>USE</c> command. The <c>USE</c> command takes the 
>name of the database you wish to use as your current database. Another 
>option is to set it on the command line after the <c>-p</c> switch. Let's go
>ahead and switch to the gentoo database:
>
>
><pre caption="Switching our database">
>mysql&gt; USE gentoo;
>Database changed
></pre>
>
>
>And the current database is now our previously created gentoo database. Now
>that we're using it, we can start to create some tables and put information in
>them.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>Working With Tables In MySQL</title>
><section>
><title>Creating the tables</title>
><body>
>
>
>In the structure of MySQL, there are databases, tables, records, and fields.
>Databases hold together tables, tables hold together records, records hold
>together fields, which contain the actual information. This structure lets
>users select how they want to access their information. So far we've dealt
>with databases, now let's work with tables. First off, tables can be listed
>similiarly to databases using the <c>SHOW TABLES</c> command. Right now there
>are no tables in our gentoo database, as running the command will show us:
>
>
><pre caption="Empty gentoo database">
>mysql&gt; SHOW TABLES;
>Empty set (0.00 sec)
></pre>
>
>
>This means we need to create some tables. In order to do so, we use the
><c>CREATE TABLE</c> command. However, this command is quite different from
>simple <c>CREATE DATABASE</c> command. This command takes a list of arguments
>you must give it. The form is as follows:
>
>
><pre caption="CREATE TABLE format">
>CREATE TABLE [table_name] ([field_name] [field_type]([size]));
></pre>
>
>
>table_name is the name of the table we wish to create. In this case, let's
>make a table named <c>developers</c>. This table will contain the 
>developer's name, email and job. field_name will contain the name of the 
>field. We have 3 required names in this case: name, email, and job. Now
>what about the field type? The field type is what type of information will
>be stored. The different formats avaliable can be found at the <uri
>link="http://dev.mysql.com/doc/mysql/en/column-types.html">this page</uri>. For
>our purposes, we'll use <c>VARCHAR</c> data type for all of our fields.
><c>VARCHAR</c> is one of the most simplistic data types when it comes 
>to working with strings. size is how many of the field_type we will be
>creating. In this case, we'll use 128 for our size. This means that the
>information can be stored in terms of 128 <c>VARCHAR</c>. You can safely think
>of this as 128 characters for the time being, though there is a somewhat more
>technical explanation that the above site will provide you with. Now that we
>know how we are going to create the table, we can do so now:
>
>
><pre caption="Creating our table">
>mysql&gt; CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));
>Query OK, 0 rows affected (0.11 sec)
></pre>
>
>
>Looks like our table was created ok. Let's check it with the <c>SHOW
>TABLES</c> command:
>
>
><pre caption="Verifying our table">
>mysql&gt; SHOW TABLES;
>+------------------+
>| Tables_in_gentoo |
>+------------------+
>| developers |
>+------------------+
>1 row in set (0.00 sec)
></pre>
>
>
>Yes, there's our table. However, it doesn't seem to have any information on
>the types of fields we setup. For that, we use the <c>DESCRIBE</c> command,
>which takes the name of the table as its argument. Let's see what that gives
>us for our developers table:
>
>
><pre caption="Describing our developers table">
>mysql&gt; DESCRIBE developers;
>+-------+--------------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+--------------+------+-----+---------+-------+
>| name | varchar(128) | YES | | NULL | |
>| email | varchar(128) | YES | | NULL | |
>| job | varchar(128) | YES | | NULL | |
>+-------+--------------+------+-----+---------+-------+
>3 rows in set (0.00 sec)
></pre>
>
><note>
>You can alternatively use <c>DESC</c> as well.
></note>
>
>
>This shows the different fields and their types. It also shows a few extra
>attributes that are beyond the scope of this doc and should be consulted with
>the MySQL documentation. We now have a table to work with. Now let's go ahead
>and put some information in it.
>
>
></body>
></section>
><section>
><title>Putting Information Into Our MySQL Database</title>
><body>
>
>
>Now that we have a table setup, let's go ahead and put some information in it.
>We do so using the <c>INSERT</c> command. Like the <c>CREATE TABLE</c>
>command, it also has a specific format:
>
>
><pre caption="INSERT format">
>INSERT INTO table VALUES('value1', 'value2', ...);
></pre>
>
>
>This command is used to insert a record into table. table contains the MySQL 
>table we wish to enter the information into. value# contain the values you 
>wish to insert into the table. In this case, we want to insert data into the
>developers table. Let's insert a sample record with 'Joe Smith', 
>'joesmith@gentoo.org', 'toolchain'. So we would do as follows:
>
>
><pre caption="Inserting information into our developers table">
>mysql&gt; INSERT INTO developers VALUES('Joe Smith', 'joesmith@gentoo.org', 'toolchain');
>Query OK, 1 row affected (0.06 sec)
></pre>
>
>
>According to our return result, it appears that the record was inserted ok.
>What if we want to input more information than just one record? Another handy
>command is the <c>LOAD DATA</c> command. This loads records from a tab
>separated file. Let's try that by editing a file in our home directory with
>the records. We'll call this file <path>records.txt</path>. Here's a sample:
>
>
><pre caption="~/records.txt">
>John Doe	johndoe@gentoo.org	portage
>Chris White	chriswhite@gentoo.org	documentation
>Sam Smith	samsmith@gentoo.org	amd64
></pre>
>
><impo>
>Be sure you know what data you're dealing with! It's very unsafe to use LOAD
>DATA when you are uncertain of the file's contents!
></impo>
>
>
>Now the <c>LOAD DATA</c> command has a somewhat enlongated definition, but
>we'll use the simplest form here:
>
>
><pre caption="LOAD DATA format">
>LOAD DATA LOCAL INFILE '/path/to/filename' INTO TABLE table;
></pre>
>
>
><path>/path/to/filename</path> is the directory and filename that will be used.
>table is the name of our table. In this case, our file is
><path>~/records.txt</path> and the table is developers:
>
>
><pre caption="Loading in our data">
>mysql&gt; LOAD DATA LOCAL INFILE '~/records.txt' INTO TABLE developers;
>Query OK, 3 rows affected (0.00 sec)
>Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
></pre>
>
><impo>
>If you come up with any strange behaviour, make sure your fields are separated
>by tabs! If you paste information into your infile from another source, it 
>may convert your tabs to spaces!
></impo>
>
>
>That worked as well. However, this simply inserts records, and does not give
>you any sort of control over MySQL. Many web applications use sql scripts in
>order to setup MySQL quickly and easily. If you want to use an sql script,
>you'll need to run mysql in batch mode, or source the file. Here's an example
>of running mysql in batch mode:
>
>
><pre caption="MySQL in batch mode">
>$ mysql -u root -h localhost -p &lt; sqlfile
></pre>
>
>
>Like <c>LOAD DATA</c>, be sure you can tell what sql file does. <e>Failure to 
>do so may cause your database to be compromised</e>! Another way you can
>accomplish this is to use the <c>source</c> command. This command will run
>mysql commands from an sql file while in the mysql interactive mode. Here is
>how to source an sql file:
>
>
><pre caption="Sourcing an sql file">
>mysql&gt; source sqlfile;
></pre>
>
>
>If you see a web application wanting you to run an sql file, the two above
>commands can be used to accomplish that taks. We have our table setup, so how
>do we check our fields? We do this by searching our table with queries.
>
>
></body>
></section>
><section>
><title>Browsing MySQL Tables With Queries</title>
><body>
>
>
>Queries are one of the main features of any SQL database. They are what help
>us interpret information in our tables to something useful. Most queries are
>done with the <c>SELECT</c> command. The <c>SELECT</c> command is fairly
>complex, and we're only going to be looking at three main forms of the command
>in this document:
>
>
><pre caption="SELECT forms">
><comment>selects all entries in a table</comment>
>SELECT * FROM table;
><comment>selects specific entries in a table</comment>
>SELECT * FROM table WHERE field=value;
><comment>selects specific fields</comment>
>SELECT field1,field2,field3 FROM table [WHERE field=value];
></pre>
>
>
>Let's take a quick look at the first form. It's relatively simple and gives
>you an overall look of your table. Let's go ahead and run it to see what data
>we have so far:
>
>
><pre caption="Overviewing our table">
>mysql&gt; SELECT * FROM developers;
>+-------------+-----------------------+---------------+
>| name | email | job |
>+-------------+-----------------------+---------------+
>| Joe Smith | joesmith@gentoo.org | toolchain |
>| John Doe | johndoe@gentoo.org | portage |
>| Chris White | chriswhite@gentoo.org | documentation |
>| Sam Smith | samsmith@gentoo.org | amd64 |
>+-------------+-----------------------+---------------+
>4 rows in set (0.00 sec)
></pre>
>
>
>We see both the data we inserted through <c>INSERT</c> and those inserted by
><c>LOAD DATA</c> present. Now, let's say that we just want to see the record
>for Chris White. We can do so with the second form of select as shown here:
>
>
><pre caption="Selecting a specific record using SELECT">
>mysql&gt; SELECT * FROM developers WHERE name = 'Chris White';
>+-------------+-----------------------+---------------+
>| name | email | job |
>+-------------+-----------------------+---------------+
>| Chris White | chriswhite@gentoo.org | documentation |
>+-------------+-----------------------+---------------+
>1 row in set (0.08 sec)
></pre>
>
>
>As expected, the specific entry that we were looking for has been selected. Now, let's say we only wanted to know the person's job and email address, not their name. We can do so with the third form of <c>SELECT</c> as shown here:
>
>
><pre caption="Selecting specific record and fields using SELECT">
>mysql> SELECT email,job FROM developers WHERE name = 'Chris White';
>+-----------------------+---------------+
>| email | job |
>+-----------------------+---------------+
>| chriswhite@gentoo.org | documentation |
>+-----------------------+---------------+
>1 row in set (0.04 sec)
></pre>
>
>
>This method of selection is a lot easier to manage, expecially with larger 
>ammounts of information, as we'll see later on. Right now, being the root
>mysql user, we have unlimited permissions to do what we wish with the MySQL
>database. In a server environment, a user with such privileges can be quite
>problematic. In order to control who does what with the databases, we setup
>privileges.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>MySQL Privileges</title>
><section>
><title>Granting Privileges with GRANT</title>
><body>
>
>
>Privileges are what kind of access users have to databases, tables, pretty much
>anything. Right now in the gentoo database, the MySQL root account is the only
>account that can access it, given its permissions. Now, let's create two 
>somewhat generic users, guest and admin, who will access the gentoo database 
>and work with the information in it. Now guest will be a restricted account.
>All it will be able to do is get information from the database, and that's it. 
>admin will have the same control as root, but only for the gentoo database 
>(not the main mysql databases). Before we start on that, let's have a closer 
>look at this somewhat simplified format of the <c>GRANT</c> command:
>
>
><pre caption="GRANT format">
>GRANT [privleges] ON database.* TO '[user]'@'[host]' IDENTIFIED BY '[password]';
></pre>
>
><note>
><c>GRANT</c> is considered to be the way to create a user. Latever versions of
>MySQL, however, do contain a <c>CREATE_USER</c> function, though GRANT is still
>prefered.
></note>
>
>
>First we have the privleges we wish to assign. With what we've learned so far,
>here are some of the privleges you can set:
>
>
><ul>
> <li>ALL - Gives the all privlege control for the database</li>
> <li>CREATE - Let's the user create tables</li>
> <li>SELECT - Allows users to query tables</li>
> <li>INSERT - Allows users to insert data into a table</li>
> <li>SHOW DATABASES - Allows users to see a list of databases</li>
> <li>USAGE - User has no privleges</li>
> <li>GRANT OPTION - Allows users to grant privleges</li>
></ul>
>
><note>
>If you're running MySQL to communicate data to a web application, CREATE,
>SELECT, INSERT (discussed here), DELETE and UPDATE <uri
>link="http://dev.mysql.com/doc/mysql/en/grant.html">(see MySQL
>documentation)</uri> are the only permissions you will most likely need. A lot
>of people make the mistake of granting all permissions when it's not really
>necessary. Check with the application developers to see if such permissions
>will cause issues with general operation.
></note>
>
>
>For our admin user, ALL will be sufficient. For the guest user, SELECT will be
>sufficient for read only access. database is the database we wish the user to
>have these permissions on. In this example, gentoo is the database. The .*
>means all tables. If you wanted to, you could apply per table access. user is
>the name of the user and host is the hostname the user will be accessing from.
>In most cases, this will be localhost. Finally, password is the user's
>password. Once we're done, we also will need to execute the <c>FLUSH
>PRIVILEGES</c> command. This updates user permissions without restarting the
>server. Given the information, let's go ahead and create our users:
>
>
><pre caption="Creating the admin and guest user">
><comment>admin</comment>
>mysql&gt; GRANT ALL ON gentoo.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
><comment>guest</comment>
>mysql&gt; GRANT SELECT ON gentoo.* TO 'guest'@'localhost' IDENTIFIED BY 'password';
></pre>
>
>
>Now that we have the users setup, let's test them out. First we quit mysql by
>typing <c>quit</c> at the command prompt:
>
>
><pre caption="Quitting MySQL">
>mysql&gt; quit
></pre>
>
>
>We're now at the console. Now that we have our users setup, let's go ahead and
>see what they can do.
>
>
></body>
></section>
><section>
><title>Testing User Permissions</title>
><body>
>
>
>Now that we're back in the console, we'll attempt to login as the guest user.
>Currently, the guest user has <c>SELECT</c> only privleges. This basically
>comes down to being able to search and nothing more. Let's go ahead and login
>with the guest account:
>
>
><pre caption="Logging in with the guest account">
>$ mysql -u guest -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 6 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>First off we should test user restriction. Let's switch to the gentoo database:
>
>
><pre caption="Switching to the gentoo database">
>mysql&gt; USE gentoo;
>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
></pre>
>
>
>Now let's test something we can do. We'll attempt to create a table for that
>purpose. Let's go ahead and try that:
>
>
><pre caption="Attempting to create a table with the guest user">
>mysql&gt; CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));
>ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'
></pre>
>
>
>As you can see, this function fails, as our user does not have the appropriate
>access. However, one access we did grant is the <c>SELECT</c> statement.
>Let's go ahead and try that:
>
>
><pre caption="Attempting the SELECT statement">
>mysql&gt; SELECT * FROM developers;
>+-------------+-----------------------+---------------+
>| name | email | job |
>+-------------+-----------------------+---------------+
>| Joe Smith | joesmith@gentoo.org | toolchain |
>| John Doe | johndoe@gentoo.org | portage |
>| Chris White | chriswhite@gentoo.org | documentation |
>| Sam Smith | samsmith@gentoo.org | amd64 |
>+-------------+-----------------------+---------------+
>4 rows in set (0.01 sec)
></pre>
>
>
>The command succeeds, and we're given a glimpse of what user permissions can
>do. We did, however, create an admin account as well. This was created to
>show that even all permissions granted users can still have limitations. Go
>ahead and quit MySQL and login as the admin:
>
>
><pre caption="Logging in as admin">
>mysql&gt; quit
>Bye
>$ mysql -u admin -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 7 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>To begin, we'll try creating a new database with our admin user. This admin 
>user will have access similiar to the root MySQL account, and will be able to
>do any kind of modification to the gentoo database it chooses. This will
>test the user's access to the main MySQL database. Remember ealier that we
>only set permissions to a specific database. Let's go ahead and attempt now:
>
>
><pre caption="Attempting to create a new database">
>mysql&gt; CREATE DATABASE gentoo2;
>ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'
></pre>
>
>
>Indeed, the admin user cannot create databases on the main MySQL database,
>despite all of its permissions on the gentoo database. However, we're still
>able to use the admin account to modify the gentoo database, as shown here by
>this example data insertion:
>
>
><pre caption="admin permissions in gentoo database">
>mysql&gt; USE gentoo;
>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&gt; INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python');
>Query OK, 1 row affected (0.08 sec)
></pre>
>
>
>Indeed our admin can access the database as they please. Now sometimes, we
>need to get rid of user permissions. This could be anything from a problematic
>user to a retired employee. Let's take a look at how to disable user
>permissions with the REVOKE command.
>
>
></body>
></section>
><section>
><title>Removing User Access With The REVOKE Command</title>
><body>
>
>
>The <c>REVOKE</c> command let's us deny user access. We can either deny full
>access, or specific access. In fact, the format is very similiar to
><c>GRANT</c>:
>
>
><pre caption="REVOKE format">
>REVOKE [privleges] ON database.* FROM '[user]'@'[host]';
></pre>
>
>
>Options here are explained in the <c>GRANT</c> command section. In this
>section however, we're going to deny full access to a user. Let's say we find
>out the guest account is causing some problems security wise. We decide to
>revoke all privleges. Let's go ahead and quit our current mysql session and 
>login as root:
>
>
><pre caption="Logging in as root">
>mysql&gt; quit
>Bye
>$ mysql -u root -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 8 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>Now then, let's go ahead and revoke the user's permissions as follows:
>
>
><pre caption="Revoking user permissions">
>mysql&gt; REVOKE ALL ON gentoo.* FROM 'guest'@'localhost';
>Query OK, 0 rows affected (0.00 sec)
></pre>
>
><note>
>In this case, user access is simple, so per database revoking is not a problem.
>However, in larger cases, you would most likely be using *.* instead of
>gentoo.* to remove user access to all other databases.
></note>
>
>
>Now let's quit and attempt to login as a guest user:
>
>
><pre caption="Attempting guest user login">
>$ mysql -u guest -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Youra MySQL connection id is 9 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
>mysql&gt;
></pre>
>
>
>We're able to login, but our access to gentoo is now gone:
>
>
><pre caption="Denied access to guest account">
>mysql&gt; USE gentoo;
>ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'
></pre>
>
>
>And our problematic user is no longer able to access the gentoo database.
>Please not that the user was still able to login. That is because they remain
>in the main MySQL database. Let's take a look at how to completely remove an
>account with DELETE and the MySQL user table.
>
>
></body>
></section>
><section>
><title>Completely Removing Accounts Using DELETE And The MySQL user
>Table</title>
><body>
>
>
>The MySQL user table is a listing of all users and information about them.
>Let's logout of our previous guest account and back as root:
>
>
><pre caption="Logging in as root">
>mysql&gt; quit
>Bye
>$ mysql -u root -h localhost -p
>Enter password:
>Welcome to the MySQL monitor. Commands end with ; or \g.
>Your MySQL connection id is 14 to server version: 4.0.24-debug
>
>Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>mysql&gt;
></pre>
>
>
>First off, let's go ahead and utilize the main MySQL database:
>
>
><pre caption="Using the main mysql database">
>mysql&gt; 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&gt;
></pre>
>
>
>Now let's see what tables the mysql database has:
>
>
><pre caption="mysql database table listing">
>mysql&gt; SHOW TABLES;
>+-----------------+
>| Tables_in_mysql |
>+-----------------+
>| columns_priv |
>| db |
>| func |
>| host |
>| tables_priv |
>| user |
>+-----------------+
>6 rows in set (0.00 sec)
></pre>
>
>
>The user table is the table we're after. However, the user table contains 30
>different fields, making it very hard to read. In order to make things easier
>to read, we'll go ahead and use the third version of the <c>SELECT</c>
>statement. The fields we're after are Host and User.
>
>
><pre caption="Finding our guest user in the user table">
>mysql&gt; SELECT Host,User FROM user WHERE User = 'guest';
>+-----------+-------+
>| Host | User |
>+-----------+-------+
>| localhost | guest |
>+-----------+-------+
>1 row in set (0.00 sec)
></pre>
>
>
>Now that we have our information, we can get rid of the guest user.
>This is done with the <c>DELETE command</c>. The format of the <c>
>DELETE</c> command for our usage is as follows:
>
>
><pre caption="DELETE format">
>DELETE FROM table WHERE field='value';
></pre>
>
>
>You may notice that <c>DELETE</c> is somewhat similiar to the <c>SELECT</c>
>statement in its format. In this case, the field will be User, and the value
>guest. This will delete the record in the user table where the user is guest,
>successfully deleting our guest user account. Let's go ahead and do that:
>
>
><pre caption="Deleting the guest account">
>mysql&gt; DELETE FROM user WHERE User='guest';
>Query OK, 1 row affected (0.07 sec)
><comment>FLUSH PRIVILEGES is needed to update user permissions</comment>
>mysql&gt; FLUSH PRIVILEGES;
>Query OK, 0 rows affected (0.00 sec)
></pre>
>
>
>It seems to have worked ok. Let's test by logging out and attempting to login
>as our guest user:
>
>
><pre caption="Attempting login as the guest user">
>mysql&gt; quit
>Bye
>$ mysql -u guest -h localhost -p
>Enter password:
>ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)
></pre>
>
>
>Our user is now successfully deleted!
>
>
></body>
></section>
><section>
><title>Conclusion</title>
><body>
>
>
>While this guide focuses mainly on setting up MySQL on the command line, a few
>alternatives are avaliable in GUI form: 
>
>
><ul>
> <li>phpMyAdmin - Popular php based MySQL administration tool</li>
> <li>mysqltool - Another web interface for MySQL administration</li>
> <li>mysqlnavigator - QT frontend to MySQL</li>
> <li>gmyclient - A GNOME based MySQL client</li>
> <li>knoda - A KDE MySQL client</li>
></ul>
>
>
>There are also some masked frontends as well:
>
>
><ul>
> <li>mysqlcc - MySQL GUI client by MySQL development team</li>
> <li>xmysqladmin - Xforms based frontend to MySQL</li>
></ul>
>
>
>This ends the MySQL introductory tutorial. I hope this gives you a better
>understanding of the basics behind MySQL and getting a database setup. Please
>email me at <mail>chriswhite@gentoo.org</mail> if you have any comments.
>
>
></body>
></section>
></chapter>
>
></guide>

Actions: View

Attachments on bug 98704: 63171 | 63240 | 63279 | 66558 | 66718