Comparison of graphical tools for MySQL

Home Setting up MySQL

Setting up MySQL

In this lesson of the MySQL tutorial, you will learn...

  1. Various versions of MySQL server.
  2. The MySQL directory structure.
  3. To configure MySQL.
  4. To setup the root password and accounts.

Once MySQL has been installed, you need to set it up for usage.

Versions of the MySQL Server

Print PDF
User Rating: / 3

The My SQL Server Instance Configuration Wizard is used to set up the initial configuration of the My SQL server and set up the My SQL server as an automatically running Windows service. The service accesses the mysqld-nt or similar server script to actually run a My SQL 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.

Server Edition Description
mysqld This is the basic compiled edition of the MySQL server. It supports all the basic features found in a production release of MySQL. This edition also contains full debugging support, which means that it might run more slowly and use more memory than other editions of the MySQL server.
mysqld-debug The binaries are compiled with additional debug data and are not intended for use in a production environment.
mysqld-nt This edition of the MySQL server is optimized to run on Windows NT, Windows 2000, Windows XP, and Windows Server 2003. In addition, the mysqld-nt server supports named pipes.
mysqld-max The mysqld-max server is the Max edition of the MySQL server. As a result, it includes all features found in the mysqld server, plus additional features.
mysqld-max-nt Like the mysqld-nt server, the mysqld-max-nt server is optimized to run on Windows NT, Windows 2000, Windows XP, and Windows Server 2003, and it supports named pipes. In addition, because the mysqld-max-nt server is the Max edition of the MySQL server, it includes all features found in the mysqld-nt server, plus additional features.

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:

Directory Contents
bin Binaries - mysqld server program, all client programs and tools you will run to use and administer MySQL.
data The data files where MySQL reads and writes its data, along with log files for the server.
docs Documentation - HTML and text files pertaining to installed version.
include A set of header files that may be used when writing or compiling other programs.
lib MySQL library files.
scripts mysql_install_db script, which is used to install initial data files and accounts.
share SQL scripts for fixing privileges, as well as a set of language files for using MySQL in a variety of languages.
sql-bench A set of benchmarking tools included with MySQL.
support-files Several configuration file examples and other support scripts.

Note: Please see more in the My SQL 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:

    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';

    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.

Code Sample:

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.

Code Sample:

GRANT ALL ON sakila.* TO sakilaadmin IDENTIFIED BY 'sakila';
Code Explanation

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 My SQL 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 My SQL installation:

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

Code Sample:


Configuring My SQL

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:


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

#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.

Setting up My SQL Conclusion

In this lesson of the MySQL tutorial, we reviewed MySQL installation, processes and directory structure.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.

mysql tools