MySQL Tutorial

Setting up MySQL

Versions of the MySQL Server

The MySQL Server Instance Configuration Wizard is used to set up the initial configuration of the MySQL server and set up the MySQL server as an automatically running Windows service. The service accesses the mysqld-nt or similar server script to actually run a MySQL server.

MySQL supports a Standard edition, a Max edition and a Debug edition (rarely used). As you probably noticed when you downloaded the Windows distribution file, you were not given the option to select an edition. Windows distribution files, unlike other operating systems, include all editions in one file, so you do not have to make a decision about editions until after you install MySQL. Initially, that choice is made for you by running the MySQL Server Instance Configuration Wizard. There might come a time, though, when you want to choose a different edition of the server.

The Windows distribution of MySQL includes five editions of the MySQL server. These editions can be divided into the broader categories that you saw earlier - Standard and Max. The following table describes the five server editions available in a MySQL Windows installation.

On variants of Windows (NT, Server 2000, XP, or Server 2003), we will start with the mysqld-nt server. If additional features of the Max version become a need, we can move to Max version later.

The mysqld_safe Wrapper

The mysqld program is the MySQL server daemon, though we will rarely interact with it directly. We will use the daemon through a wrapper script called mysqld_safe. The mysqld_safe wrapper adds a few extra safety-related logging features and system- integrity features when the daemon is started and is the preferred way to start the server.

MySQL Directory Structure

MySQL installs more than a thousand files and directories on your system. The location of these files differs depending on the installation mechanism you choose. The tar ball root directory structure contains the following:

Note: Please see more in the MySQL documentation for information about file layouts for different operating systems.

Securing the MySQL Server: Set root Password

After a new installation, anyone can log in as root without a password to MySQL and have unrestricted privileges to read, change, and delete all databases. For reasons of security it is absolutely necessary that you give a password for root.

Here are some methods to set or change the root password for a MySQL installation. That password is normally an empty string at initial installation.

• Use SET PASSWORD command:

  Code Sample: MySQLSetup/Demos/Set-Password.sql

  SET PASSWORD FOR 'root'@'localhost' = PASSWORD('abcd1234');

• Use mysqladmin

  root# mysqladmin -u root password "<password-string>"
  if there was already a mysql root password set, change this to:
  OS> mysqladmin -u root password <old-password> "<new-password>"
  

• root# mysqladmin -u root password 'secret'
  root# mysqladmin -u root -h your-host-domain password 'secret'
  

• You may see an error Host 'your-host-domain' is not allowed to connect to this MySQL server'.
  • This error indicates that MySQL Access-Control database mysql has no domain name set.
  • To solve this, we will restart the mysql and,
  • Update the table mysql.user with an UPDATE command.
  • Then we change the second root password with mysqladmin. The following lines summarize the commands, once with and once without the domain name:

    root# mysql -u root -p
    Enter password: *******
    mysql> USE mysql;
    mysql> UPDATE user SET host="<your-host-domain>" WHERE host="<computer-name>";
    mysql> FLUSH PRIVILEGES;
    mysql> exit
    root# mysqladmin -u root -h computer-name password 'secret'
    

• As seen above, we can also use UPDATE to directly edit the users table

  OS> mysql -u root
  mysql> use mysql;
  mysql> UPDATE user SET Password = PASSWORD('"<new-password>"') WHERE User = 'root';
  mysql> FLUSH PRIVILEGES;
  

  This requires knowing the current password for root user. Alternately, you can restart mysql as shown:

  OS> mysqld --skip-grant-tables user=root

  and use method shown above.

Delete Anonymous Accounts

As an important security measure, one should delete all anonymous users. These are users for whom the user column is empty. In other words, allow only explicitly registered users in the mysql.user table to log in.

DELETE FROM user WHERE user = ''

Creating an Account for Basic Use

One core initial task is setting up a new database and making it available to a user, who can create tables and fill them with data.

GRANT ALL ON sakila.* TO sakilaadmin IDENTIFIED BY 'sakila';
GRANT SELECT,UPDATE,DELETE,INSERT ON sakila.* TO sakilauser IDENTIFIED BY 'sakila';

Here, we grant unrestricted access to sakilaadmin user and limited access to sakila user.

General Information using MySQL Monitor

The MySQL client that ships with the MySQL distribution is named mysql. Next, we will connect to the server using the MySQL Client mysql, and execute some simple SQL commands. Run the client from your command prompt by switching to the bin directory of your MySQL installation:

OS> mysql -u root -p
Password: *****

SHOW DATABASES;
SHOW TABLES;
SELECT COUNT(*) FROM user;

Configuring MySQL

MySQL reads configuration file(s) when it starts up. If you use the defaults or an installer, you probably don't need to add anything to the configuration file. However, if you install MySQL in a nonstandard location or want the databases to be stored somewhere other than the default, you might need to edit the configuration file. The configuration file is named my.ini or my.cnf. It's located in your system directory (such as Windows or Winnt) if you are using Windows or in /etc on Linux, Unix, and Mac.

The configurations file contains several sections and commands for various components and processes in MySQL. As an example, the following commands in the mysqld section sometimes need to be reconfigured:

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are
#     usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

#Path to the database root
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

The # at the beginning of the line makes the line into a comment. The basedir line tells the MySQL server where MySQL is installed. The datadir line tells the server where the databases are located. You can change the port number to tell the server to listen for database queries on a different port.

We will see more configuration options in later lessons.

All material in this Setting up MySQL is copyright 2010 Webucator. The purpose of this website is to help you learn MySQL on your own and use of the website implies your agreement to our MySQL Tutorial Terms of Service.