Installation of MySQL generally follows the steps outlined here:
- Determine whether MySQL runs and is supported on your platform.
Please note that not all platforms are equally suitable for running MySQL, and that not all platforms on which MySQL is known to run are officially supported by Oracle Corporation. For information about those platforms that are officially supported, see http://www.mysql.com/support/supportedplatforms/database.html on the MySQL Web site. - Choose which distribution to install.
Several versions of MySQL are available, and most are available in several distribution formats. You can choose from pre-packaged distributions containing binary (precompiled) programs or source code. When in doubt, use a binary distribution. Oracle also provides access to the MySQL source code for those who want to see recent developments and test new code. To determine which version and type of distribution you should use, see Section 2.1.1, “Which MySQL Version and Distribution to Install”. - Download the distribution that you want to install.
For instructions, see Section 2.1.2, “How to Get MySQL”. To verify the integrity of the distribution, use the instructions in Section 2.1.3, “Verifying Package Integrity Using MD5 Checksums or GnuPG”. - Install the distribution.
To install MySQL from a binary distribution, use the instructions in Section 2.2, “Installing MySQL on Unix/Linux Using Generic Binaries”.
To install MySQL from a source distribution or from the current development source tree, use the instructions in Section 2.9, “Installing MySQL from Source”. - Perform any necessary postinstallation setup.
After installing MySQL, see Section 2.10, “Postinstallation Setup and Testing” for information about making sure the MySQL server is working properly. Also refer to the information provided in Section 2.10.4, “Securing the Initial MySQL Accounts”. This section describes how to secure the initial MySQL user accounts, which have no passwords until you assign passwords. The section applies whether you install MySQL using a binary or source distribution. - If you want to run the MySQL benchmark scripts, Perl support for MySQL must be available. See Section 2.13, “Perl Installation Notes”.
MySQL compressed tar file binary distributions have names of the form mysql-VERSION-OS.tar.gz, where VERSION is a number (for example, 5.6.33), and OS indicates the type of operating system for which the distribution is intended (for example, pc-linux-i686 or winx64).
Warning
If you have previously installed MySQL using your operating system native package management system, such as yum or apt-get, you may experience problems installing using a native binary. Make sure your previous MySQL installation has been removed entirely (using your package management system), and that any additional files, such as old versions of your data files, have also been removed. You should also check for configuration files such as /etc/my.cnf or the /etc/mysql directory and delete them.
For information about replacing third-party packages with official MySQL packages, see the related Apt guide or Yum guide.
Warning
MySQL has a dependency on the libaio library. Data directory initialization and subsequent server startup steps will fail if this library is not installed locally. If necessary, install it using the appropriate package manager. For example, on Yum-based systems:
shell> yum search libaio # search for info
shell> yum install libaio # install library
Or, on APT-based systems:
shell> apt-cache search libaio # search for info
shell> apt-get install libaio1 # install li
To install and use a MySQL binary distribution, the command sequence looks like this:
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
As of MySQL 5.6.8, mysql_install_db creates a default option file named my.cnf in the base installation directory. This file is created from a template included in the distribution package named my-default.cnf.
2.10 Postinstallation Setup and Testing
It might be necessary to specify other options such as --basedir or --datadir if mysql_install_db does not identify the correct locations for the installation directory or data directory. For example:
shell> scripts/mysql_install_db --user=mysql \
--basedir=/opt/mysql/mysql \
--datadir=/opt/mysql/mysql/data
For a more secure installation, invoke mysql_install_db with the --random-passwords option. This causes it to assign a random password to the MySQL root accounts, set the “password expired” flag for those accounts, and remove the anonymous-user MySQL accounts.
Start the MySQL server like this:
shell> bin/mysqld_safe --user=mysql &
It is important that the MySQL server be run using an unprivileged (non-root) login account. To ensure this if you run mysqld_safe as root, include the --user option as shown. Otherwise, execute the program while logged in as mysql, in which case you can omit the --user option from the command.
2.10.3 Testing the Server
After the data directory is initialized and you have started the server, perform some simple tests to make sure that it works satisfactorily. This section assumes that your current location is the MySQL installation directory and that it has a bin subdirectory containing the MySQL programs used here. If that is not true, adjust the command path names accordingly.
Alternatively, add the bin directory to your PATH environment variable setting. That enables your shell (command interpreter) to find MySQL programs properly, so that you can run a program by typing only its name, not its path name. See Section 4.2.10, “Setting Environment Variables”.
Use mysqladmin to verify that the server is running. The following commands provide simple tests to check whether the server is up and responding to connections:
shell> bin/mysqladmin version
shell> bin/mysqladmin variables
If you cannot connect to the server, specify a -u root option to connect as root. If you have assigned a password for the root account already, you'll also need to specify -p on the command line and enter the password when prompted. For example:
shell> bin/mysqladmin -u root -p version
Enter password: (enter root password here)
The output from mysqladmin version varies slightly depending on your platform and version of MySQL, but should be similar to that shown here:
shell> bin/mysqladmin version
mysqladmin Ver 14.12 Distrib 5.6.33, for pc-linux-gnu on i686
...
Server version 5.6.33
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 14 days 5 hours 5 min 21 sec
Threads: 1 Questions: 366 Slow queries: 0
Opens: 0 Flush tables: 1 Open tables: 19
Queries per second avg: 0.000
To see what else you can do with mysqladmin, invoke it with the --help option.
Verify that you can shut down the server (include a -p option if the root account has a password already):
shell> bin/mysqladmin -u root shutdown
Verify that you can start the server again. Do this by using mysqld_safe or by invoking mysqld directly. For example:
shell> bin/mysqld_safe --user=mysql &
Run some simple tests to verify that you can retrieve information from the server. The output should be similar to that shown here.
Use mysqlshow to see what databases exist:
shell> bin/mysqlshow
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
The list of installed databases may vary, but will always include the minimum of mysql and information_schema.
If you specify a database name, mysqlshow displays a list of the tables within the database:
shell> bin/mysqlshow mysql
Database: mysql
+---------------------------+
| Tables |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
shell> bin/mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql
+------+-----------+-----------------------+
| User | Host | plugin |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+
2.10.4 Securing the Initial MySQL Accounts
The MySQL installation process involves initializing the data directory, including the mysql database containing the grant tables that define MySQL accounts. For details, see Section 2.10, “Postinstallation Setup and Testing”.
This section describes how to assign passwords to the initial accounts created during the MySQL installation procedure, if you have not already done so.
The mysql.user grant table defines the initial MySQL user accounts and their access privileges:
- Some accounts have the user name root. These are superuser accounts that have all privileges and can do anything. If these root accounts have empty passwords, anyone can connect to the MySQL server as root without a password and be granted all privileges.
- On Windows, root accounts are created that permit connections from the local host only. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, or the IPv6 address ::1. If the user selects the Enable root access from remote machines option during installation, the Windows installer creates another root account that permits connections from any host.
- On Unix, each root account permits connections from the local host. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, the IPv6 address ::1, or the actual host name or IP address.
An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run with the --skip-name-resolve option, so the 127.0.0.1 account is useful in that case. The ::1 account is used for IPv6 connections.- If accounts for anonymous users were created, these have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.
- On Windows, there is one anonymous account that permits connections from the local host. Connections can be made by specifying a host name of localhost.
- On Unix, each anonymous account permits connections from the local host. Connections can be made by specifying a host name of localhost for one of the accounts, or the actual host name or IP address for the other.
- The 'root'@'localhost' account also has a row in the mysql.proxies_priv table that enables granting the PROXY privilege for ''@'', that is, for all users and all hosts. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. See Section 6.3.8, “Proxy Users”.
To display which accounts exist in the mysql.user table and check whether their passwords are empty, use the following statement:
mysql> SELECT User, Host, Password FROM mysql.user;
+------+--------------------+----------+
| User | Host | Password |
+------+--------------------+----------+
| root | localhost | |
| root | myhost.example.com | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | myhost.example.com | |
+------+--------------------+----------+
This output indicates that there are several root and anonymous-user accounts, none of which have passwords. The output might differ on your system, but the presence of accounts with empty passwords means that your MySQL installation is unprotected until you do something about it:
- Assign a password to each MySQL root account that does not have one.
- To prevent clients from connecting as anonymous users without a password, either assign a password to each anonymous account or remove the accounts.
In addition, the mysql.db table contains rows that permit all accounts to access the test database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for testing but inadvisable on production servers. Administrators who want database access restricted only to accounts that have permissions granted explicitly for that purpose should remove these mysql.db table rows.
The following instructions describe how to set up passwords for the initial MySQL accounts, first for the root accounts, then for the anonymous accounts. The instructions also cover how to remove anonymous accounts, should you prefer not to permit anonymous access at all, and describe how to remove permissive access to test databases. Replace new_password in the examples with the password that you want to use. Replace host_name with the name of the server host. You can determine this name from the output of the preceding SELECT statement. For the output shown, host_name is myhost.example.com.
Note
For additional information about setting passwords, see Section 6.3.5, “Assigning Account Passwords”. If you forget your root password after setting it, see Section B.5.3.2, “How to Reset the Root Password”.
To set up additional accounts, see Section 6.3.2, “Adding User Accounts”.
You might want to defer setting the passwords until later, to avoid the need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.
Note
On Windows, you can also perform the process described in this section during installation with MySQL Installer (see Section 2.3.3, “Installing MySQL on Microsoft Windows Using MySQL Installer”). On all platforms, the MySQL distribution includes mysql_secure_installation, a command-line utility that automates much of the process of securing a MySQL installation. MySQL Workbench is available on all platforms, and also offers the ability to manage user accounts (see Chapter 26, MySQL Workbench ).
Assigning root Account Passwords
A root account password can be set several ways. The following discussion demonstrates three methods:
- Use the SET PASSWORD statement
- Use the UPDATE statement
- Use the mysqladmin command-line client program
To assign passwords using SET PASSWORD, connect to the server as root and issue a SET PASSWORD statement for each root account listed in the mysql.user table.
For Windows, do this:
shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('new_password');
The last statement is unnecessary if the mysql.user table has no root account with a host value of %.
For Unix, do this:
shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('new_password');
You can also use a single statement that assigns a password to all root accounts by using UPDATE to modify the mysql.user table directly. This method works on any platform:
shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('new_password')
-> WHERE User = 'root';
mysql> FLUSH PRIVILEGES;
The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.
shell> mysqladmin -u root password "new_password"
shell> mysqladmin -u root -h host_name password "new_password"
Those commands apply both to Windows and to Unix. The double quotation marks around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.
The mysqladmin method of setting the root account passwords does not work for the 'root'@'127.0.0.1' or 'root'@'::1' account. Use the SET PASSWORD method shown earlier.
After the root passwords have been set, you must supply the appropriate password whenever you connect as root to the server. For example, to shut down the server with mysqladmin, use this command:
shell> mysqladmin -u root -p shutdown
Enter password: (enter root password here)
The mysql commands in the following instructions include a -p option based on the assumption that you have assigned the root account passwords using the preceding instructions and must specify that password when connecting to the server.
Assigning Anonymous Account Passwords
To assign passwords to the anonymous accounts, connect to the server as root, then use either SET PASSWORD or UPDATE.
To use SET PASSWORD on Windows, do this:
shell> mysql -u root -p
Enter password: (enter root password here)
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password');
To use SET PASSWORD on Unix, do this:
shell> mysql -u root -p
Enter password: (enter root password here)
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('new_password');
To set the anonymous-user account passwords with a single UPDATE statement, do this (on any platform):
shell> mysql -u root -p
Enter password: (enter root password here)
mysql> UPDATE mysql.user SET Password = PASSWORD('new_password')
-> WHERE User = '';
mysql> FLUSH PRIVILEGES;
The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.
Removing Anonymous Accounts
If you prefer to remove any anonymous accounts rather than assigning them passwords, do so as follows on Windows:
shell> mysql -u root -p
Enter password: (enter root password here)
mysql> DROP USER ''@'localhost';
On Unix, remove the anonymous accounts like this:
shell> mysql -u root -p
Enter password: (enter root password here)
mysql> DROP USER ''@'localhost';
mysql> DROP USER ''@'host_name';
Securing Test Databases
By default, the mysql.db table contains rows that permit access by any user to the test database and other databases with names that start with test_. (These rows have an empty User column value, which for access-checking purposes matches any user name.) This means that such databases can be used even by accounts that otherwise possess no privileges. If you want to remove any-user access to test databases, do so as follows:
shell> mysql -u root -p
Enter password: (enter root password here)
mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
mysql> FLUSH PRIVILEGES;
The FLUSH statement causes the server to reread the grant tables. Without it, the privilege change remains unnoticed by the server until you restart it.
With the preceding change, only users who have global database privileges or privileges granted explicitly for the test database can use it. However, if you prefer that the database not exist at all, drop it:
mysql> DROP DATABASE test;
댓글 없음:
댓글 쓰기