Main page  |  Install  |  Command-line tools  |  Data Types  |  SQL  |  Operators  |  Functions  |  Table Types

MySQL quick reference

Note that instructions below are for Linux / MacOS systems

Source installation

Pre-configuration setup

shell> sudo groupadd mysql
shell> sudo useradd -r -g mysql mysql

Compilation

Compiling MySQL requires the following steps:

- Run configure or cmake (our case).

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DWITH_DEBUG=0 -DENABLED_LOCAL_INFILE=1

- Run make to compile the code.

make

- Run make install as user root to install all components of MySQL in their proper locations.

sudo make install

Under Unix, make sure directory owners are all in order.

Post-installation setup:

shell> cd /usr/local/mysql
shell> sudo chown -R mysql .
shell> sudo chgrp -R mysql .
shell> sudo scripts/mysql_install_db --user=mysql
shell> sudo chown -R root .
shell> sudo chown -R mysql data
shell> sudo bin/mysqld_safe --user=mysql &
# Next command is optional
shell> sudo cp support-files/mysql.server /etc/init.d/mysql.server

and check that it works:

./bin/mysql

You'll later need to setup the password for the user root.

Now you must setup your PATH to be sure all the programs are accessible:

export PATH=/usr/local/mysql/bin:$PATH (bash)
or
setenv PATH /usr/local/mysql/bin:$PATH (tcsh)

Put this command in the appropriate file on your PC, e.g .profile / .tchsrc in your home directory, or at system level, typically in /etc. If you have another MySQL installed, make sure you are working in a terminal with the correct PATH set.

In the subdirectory support-files sample files, for small, medium, large, huge, and heavy configurations are provided.

Configuration

MySQL has three different kinds of configuration, both for the server process at server startup and for the client processes when a user executes them. In order of preference, these configuration options include:

1. Command-line options

2. Configuration file options

3. Environment variable options

In other words, if you have the password option specified on the command line, in your configuration file, and in an environment variable, the command-line option wins.

A MySQL configuration file (def. /etc/my.cnf) has the following format (see reference page):

# Example MySQL configuration file
#
# These options go to all clients
[client]
port         = 3306
socket       = /tmp/mysql.sock

# These options are specifically targeted at the mysqld server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
key_buffer_size = 16M
max_allowed_packet=8M
# set-variable = max_allowed_packet=1M
skip-locking

[mysqldump]
quick

The list of parameters is very long and some are version specific (find the list HERE). For example adding in the [mysqld] section the option lower_case_table_names=1 will force the server to store database and table names internally in lowercase. This is perfect for cross-platform portability.
MySQL supports multiple configuration files. As a general rule, it checks files in the following order of preference:

1. User configuration file (Unix only).

2. Configuration file specified through the --defaults-extra-file=filename option.

3. A configuration file in the MySQL data directory.

4. The system configuration file.

In all cases except the command-line and user configuration options, the name of the configuration file on Unix is my.cnf. A Unix user can override system configuration information by building their own configuration file in ~/.my.cnf. The system configuration file on a Unix system is /etc/my.cnf. For example one can create a file like this:

[client]
# The following password will be sent to all standard MySQL clients
password="my_password"

[mysql]
no-auto-rehash
connect_timeout=2

[mysqlhotcopy]
interactive-timeout

If you want to create option groups that should be read by mysqld servers from a specific MySQL release series only, you can do this by using groups with names of [mysqld-5.6], [mysqld-5.7], and so forth. The following group indicates that the --new option should be used only by MySQL servers with 5.7.x version numbers:

[mysqld-5.7]
new

Manual startup and stop

sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &
sudo /usr/local/mysql/bin/mysqladmin -u root -p shutdown

Once installed, you should run the mysql_install_db tool to set up your databases.

Automatic startup and stop

Mac OS X:

Mac OS X automatically executes all scripts under the /Library/StartupItems directory when the system boots up. If that directory does not yet exist, you will need to create it. For MySQL, you should create the directory /Library/StartupItems/MySQL and place the startup shell script MySQL and the configuration file StartupParameters.plist in that directory.

Once those files are set up, you need to edit the host configuration file /etc/hostconfig and add the line:

MYSQLSERVER=-YES-

The shell script to start, stop, and restart MySQL looks like this:

#!/bin/sh
   
. /etc/rc.common
   
StartService(  )
{
    if [ "${MYSQLSERVER:=-NO-}" = "-YES-" ]; then
        ConsoleMessage "Starting MySQL"
        cd /usr/local/mysql
        bin/mysqld_safe --user=mysql &
    fi
}
   
StopService(  )
{
    ConsoleMessage "Stopping MySQL"
    /usr/local/mysql/bin/mysqladmin shutdown
}
   
RestartService(  )
{
    if [ "${MYSQLSERVER:=-NO-}" = "-YES-" ]; then
        ConsoleMessage "Restarting MySQL"
        StopService
        StartService
    else
        StopService
    fi
}
   
RunService "$1"

StartupParameters.plist

The configuration file looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist SYSTEM "file://localhost/System/Library/
DTDs/PropertyList.dtd">
   
<plist version="0.9">
  <dict>
    <key>Description</key>
    <string>MySQL Database Server</string>
    <key>Provides</key>
    <array>
      <string>MySQL</string>
    </array>
    <key>Requires</key>
    <array>
      <string>Network</string>
    </array>
    <key>OrderPreference</key>
    <string>Late</string>
  </dict
</plist>

Linux/Unix:

Setting up other variants of Unix is as simple as copying the script mysql.server from the source's support-files directory to your version of Unix's startup directory and making sure it is executable by root. Typically the directory is /etc/init.d. Under FreeBSD, for example, place this script in /usr/local/etc/rc.d.

Set the Root Password

After starting the server, and before doing anything else, set a password for the root user:

mysqladmin -u root password "your_password"