< Day Day Up > |
After installing MySQL, there are some issues you should address. For example, on Unix, you should initialize the data directory and create the MySQL grant tables. On all platforms, an important security concern is that the initial accounts in the grant tables have no passwords. You should assign passwords to prevent unauthorized access to the MySQL server. The following sections include post-installation procedures that are specific to Windows systems and to Unix systems. Another section, Section 2.4.4, "Starting and Troubleshooting the MySQL Server," applies to all platforms; it describes what to do if you have trouble getting the server to start. Section 2.4.5, "Securing the Initial MySQL Accounts," also applies to all platforms. You should follow its instructions to make sure that you have properly protected your MySQL accounts by assigning passwords to them. When you are ready to create additional user accounts, you can find information on the MySQL access control system and account management in Section 4.4, "The MySQL Access Privilege System," and Section 4.5, "MySQL User Account Management." 2.4.1 Windows Post-Installation ProceduresOn Windows, the data directory and the grant tables do not have to be created. MySQL Windows distributions include the grant tables already set up with a set of preinitialized accounts in the mysql database under the data directory. However, you should assign passwords to the accounts. The procedure for this is given in Section 2.4.5, "Securing the Initial MySQL Accounts." Before setting up passwords, you might want to try running some client programs to make sure that you can connect to the server and that it is operating properly. Make sure the server is running (see Section 2.2.1.5, "Starting the Server for the First Time"), then issue the following commands to verify that you can retrieve information from the server. The output should be similar to what is shown here: C:\> C:\mysql\bin\mysqlshow +-----------+ Databases +-----------+ mysql test +-----------+ C:\> C:\mysql\bin\mysqlshow mysql Database: mysql +--------------+ Tables +--------------+ columns_priv db func host tables_priv user +--------------+ C:\> C:\mysql\bin\mysql -e "SELECT Host,Db,User FROM db" mysql +------+-------+------+ host db user +------+-------+------+ % test% +------+-------+------+ If you are running a version of Windows that supports services and you want the MySQL server to run automatically when Windows starts, see Section 2.2.1.7, "Starting MySQL as a Windows Service." 2.4.2 Unix Post-Installation ProceduresAfter installing MySQL on Unix, you need to initialize the grant tables, start the server, and make sure that the server works okay. You may also wish to arrange for the server to be started and stopped automatically when your system starts and stops. You should also assign passwords to the accounts in the grant tables. On Unix, the grant tables are set up by the mysql_install_db program. For some installation methods , this program is run for you automatically:
Otherwise, you'll need to run mysql_install_db yourself. The following procedure describes how to initialize the grant tables (if that has not already been done) and then start the server. It also suggests some commands that you can use to test whether the server is accessible and working properly. For information about starting and stopping the server automatically, see Section 2.4.3, "Starting and Stopping MySQL Automatically." After you complete the procedure and have the server running, you should assign passwords to the accounts created by mysql_install_db . Instructions for doing so are given in Section 2.4.5, "Securing the Initial MySQL Accounts." In the examples shown here, the server runs under the user ID of the mysql login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server.
2.4.2.1 Problems Running mysql_install_dbThe purpose of the mysql_install_db script is to generate new MySQL privilege tables. It will not overwrite existing MySQL privilege tables, and it will not affect any other data. If you want to re-create your privilege tables, first stop the mysqld server if it's running. Then rename the mysql directory under the data directory to save it, and then run mysql_install_db . For example: shell> mv mysql-data-directory /mysql mysql-data-directory /mysql-old shell> mysql_install_db --user=mysql This section lists problems you might encounter when you run mysql_install_db :
2.4.3 Starting and Stopping MySQL AutomaticallyGenerally, you start the mysqld server in one of these ways:
The mysql.server and mysqld_safe scripts and the Mac OS X Startup Item can be used to start the server manually, or automatically at system startup time. mysql.server and the Startup Item also can be used to stop the server. To start or stop the server manually using the mysql.server script, invoke it with start or stop arguments: shell> mysql.server start shell> mysql.server stop Before mysql.server starts the server, it changes location to the MySQL installation directory, and then invokes mysqld_safe . If you want the server to run as some specific user, add an appropriate user option to the [mysqld] group of the /etc/my.cnf option file, as shown later in this section. (It is possible that you'll need to edit mysql.server if you've installed a binary distribution of MySQL in a non-standard location. Modify it to cd into the proper directory before it runs mysqld_safe . If you do this, your modified version of mysql.server may be overwritten if you upgrade MySQL in the future, so you should make a copy of your edited version that you can reinstall.) mysql.server stop brings down the server by sending a signal to it. You can also stop the server manually by executing mysqladmin shutdown . To start and stop MySQL automatically on your server, you need to add start and stop commands to the appropriate places in your /etc/rc* files. If you use the Linux server RPM package ( MySQL-server- VERSION .rpm ), the mysql.server script will already have been installed in the /etc/init.d directory with the name mysql . You need not install it manually. See Section 2.2.2, "Installing MySQL on Linux," for more information on the Linux RPM packages. Some vendors provide RPM packages that install a startup script under a different name such as mysqld . If you install MySQL from a source distribution or using a binary distribution format that does not install mysql.server automatically, you can install it manually. The script can be found in the support-files directory under the MySQL installation directory or in a MySQL source tree. To install mysql.server manually, copy it to the /etc/init.d directory with the name mysql , and then make it executable. Do this by changing location into the appropriate directory where mysql.server is located and executing these commands: shell> cp mysql.server /etc/init.d/mysql shell> chmod +x /etc/init.d/mysql Older Red Hat systems use the /etc/rc.d/init.d directory rather than /etc/init.d . Adjust the preceding commands accordingly . Alternatively, first create /etc/init.d as a symbolic link that points to /etc/rc.d/init.d : shell> cd /etc shell> ln -s rc.d/init.d . After installing the script, the commands needed to activate it to run at system startup depend on your operating system. On Linux, you can use chkconfig : shell> chkconfig --add mysql On some Linux systems, the following command also seems to be necessary to fully enable the mysql script: shell> chkconfig --level 345 mysql on On FreeBSD, startup scripts generally should go in /usr/local/etc/rc.d/ . The rc(8) manual page states that scripts in this directory are executed only if their basename matches the *.sh shell filename pattern. Any other files or directories present within the directory are silently ignored. In other words, on FreeBSD, you should install the mysql.server script as /usr/local/etc/rc.d/mysql.server.sh to enable automatic startup. As an alternative to the preceding setup, some operating systems also use /etc/rc.local or /etc/init.d/boot.local to start additional services on startup. To start up MySQL using this method, you could append a command like the one following to the appropriate startup file: /bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &' For other systems, consult your operating system documentation to see how to install startup scripts. You can add options for mysql.server in a global /etc/my.cnf file. A typical /etc/my.cnf file might look like this: [mysqld] datadir=/usr/local/mysql/var socket=/var/tmp/mysql.sock port=3306 user=mysql [mysql.server] basedir=/usr/local/mysql The mysql.server script understands the following options: basedir , datadir , and pid-file . If specified, they must be placed in an option file, not on the command line. mysql.server understands only start and stop as command-line arguments. The following table shows which option groups the server and each startup script read from option files:
[mysqld- major-version ] means that groups with names like [mysqld-4.0] , [mysqld-4.1] , and [mysqld-5.0] will be read by servers having versions 4.0.x, 4.1.x, 5.0.x, and so forth. This feature was added in MySQL 4.0.14. It can be used to specify options that will be read only by servers within a given release series. For backward compatibility, mysql.server also reads the [mysql_server] group and mysqld_safe also reads the [safe_mysqld] group. However, you should update your option files to use the [mysql.server] and [mysqld_safe] groups instead when you begin using MySQL 4.0 or later. See Section 3.3.2, "Using Option Files." 2.4.4 Starting and Troubleshooting the MySQL ServerIf you have problems starting the server, here are some things you can try:
Some storage engines have options that control their behavior. You can create a my.cnf file and set startup options for the engines you plan to use. If you are going to use storage engines that support transactional tables ( InnoDB , BDB ), be sure that you have them configured the way you want before starting the server:
When the mysqld server starts, it changes location to the data directory. This is where it expects to find databases and where it expects to write log files. On Unix, the server also writes the pid (process ID) file in the data directory. The data directory location is hardwired in when the server is compiled. This is where the server looks for the data directory by default. If the data directory is located somewhere else on your system, the server will not work properly. You can find out what the default path settings are by invoking mysqld with the --verbose and --help options. (Prior to MySQL 4.1, omit the --verbose option.) If the defaults don't match the MySQL installation layout on your system, you can override them by specifying options on the command line to mysqld or mysqld_safe . You can also list the options in an option file. To specify the location of the data directory explicitly, use the --datadir option. However, normally you can tell mysqld the location of the base directory under which MySQL is installed and it will look for the data directory there. You can do this with the --basedir option. To check the effect of specifying path options, invoke mysqld with those options followed by the --verbose and --help options. For example, if you change location into the directory where mysqld is installed, and then run the following command, it will show the effect of starting the server with a base directory of /usr/local : shell> ./mysqld --basedir=/usr/local --verbose --help You can specify other options such as --datadir as well, but note that --verbose and --help must be the last options. (Prior to MySQL 4.1, omit the --verbose option.) Once you determine the path settings you want, start the server without --verbose and --help . If mysqld is currently running, you can find out what path settings it is using by executing this command: shell> mysqladmin variables Or: shell> mysqladmin -h host_name variables host_name is the name of the MySQL server host. If you get Errcode 13 (which means Permission denied ) when starting mysqld , this means that the access privileges of the data directory or its contents do not allow the server access. In this case, you change the permissions for the involved files and directories so that the server has the right to use them. You can also start the server as root , but this can raise security issues and should be avoided. On Unix, change location into the data directory and check the ownership of the data directory and its contents to make sure the server has access. For example, if the data directory is /usr/local/mysql/var , use this command: shell> ls -la /usr/local/mysql/var If the data directory or its files or subdirectories are not owned by the account that you use for running the server, change their ownership to that account: shell> chown -R mysql /usr/local/mysql/var shell> chgrp -R mysql /usr/local/mysql/var If the server fails to start up correctly, check the error log file to see if you can find out why. Log files are located in the data directory (typically C:\mysql\data on Windows, /usr/local/mysql/data for a Unix binary distribution, and /usr/local/var for a Unix source distribution). Look in the data directory for files with names of the form host_name .err and host_name .log , where host_name is the name of your server host. (Older servers on Windows use mysql.err as the error log name.) Then check the last few lines of these files. On Unix, you can use tail to display the last few lines: shell> tail host_name .err shell> tail host_name .log The error log contains information that indicates why the server couldn't start. For example, you might see something like this in the log: 000729 14:50:10 bdb: Recovery function for LSN 1 27595 failed 000729 14:50:10 bdb: warning: ./test/t1.db: No such file or directory 000729 14:50:10 Can't init databases This means that you didn't start mysqld with the --bdb-no-recover option and Berkeley DB found something wrong with its own log files when it tried to recover your databases. To be able to continue, you should move away the old Berkeley DB log files from the database directory to some other place, where you can later examine them. The BDB log files are named in sequence beginning with log.0000000001 , where the number increases over time. If you are running mysqld with BDB table support and mysqld dumps core at startup, this could be due to problems with the BDB recovery log. In this case, you can try starting mysqld with --bdb-no-recover . If that helps, then you should remove all BDB log files from the data directory and try starting mysqld again without the --bdb-no-recover option. If either of the following errors occur, it means that some other program (perhaps another mysqld server) is already using the TCP/IP port or Unix socket file that mysqld is trying to use: Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on unix socket... Use ps to determine whether you have another mysqld server running. If so, shut down the server before starting mysqld again. (If another server is running, and you really want to run multiple servers, you can find information about how to do so in Section 4.9, "Running Multiple MySQL Servers on the Same Machine.") If no other server is running, try to execute the command telnet your-host-name tcp-ip-port-number . (The default MySQL port number is 3306.) Then press Enter a couple of times. If you don't get an error message like telnet: Unable to connect to remote host: Connection refused , some other program is using the TCP/IP port that mysqld is trying to use. You'll need to track down what program this is and disable it, or else tell mysqld to listen to a different port with the --port option. In this case, you'll also need to specify the port number for client programs when connecting to the server via TCP/IP. Another reason the port might be inaccessible is that you have a firewall running that blocks connections to it. If so, modify the firewall settings to allow access to the port. If the server starts but you can't connect to it, you should make sure that you have an entry in /etc/ hosts that looks like this: 127.0.0.1 localhost This problem occurs only on systems that don't have a working thread library and for which MySQL must be configured to use MIT-pthreads. If you can't get mysqld to start, you can try to make a trace file to find the problem by using the --debug option. 2.4.5 Securing the Initial MySQL AccountsPart of the MySQL installation process is to set up the mysql database containing the grant tables:
The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows :
As noted, none of the initial accounts have passwords. This means that your MySQL installation is unprotected until you do something about it:
The following instructions describe how to set up passwords for the initial MySQL accounts, first for the anonymous accounts and then for the root accounts. Replace " newpwd " in the examples with the actual password that you want to use. The instructions also cover how to remove the anonymous accounts, should you prefer not to allow anonymous access at all. You might want to defer setting the passwords until later, so that you don't need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for any real production work. To assign passwords to the anonymous accounts, you can use either SET PASSWORD or UPDATE . In both cases, be sure to encrypt the password using the PASSWORD() function. To use SET PASSWORD on Windows, do this: shell> mysql -u root mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD(' newpwd '); mysql> SET PASSWORD FOR ''@'%' = PASSWORD(' newpwd '); To use SET PASSWORD on Unix, do this: shell> mysql -u root mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD(' newpwd '); mysql> SET PASSWORD FOR ''@' host_name ' = PASSWORD(' newpwd '); In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the name that is specified in the Host column of the non- localhost record for root in the user table. If you don't know what hostname this is, issue the following statement before using SET PASSWORD : mysql> SELECT Host, User FROM mysql.user; Look for the record that has root in the User column and something other than localhost in the Host column. Then use that Host value in the second SET PASSWORD statement. The other way to assign passwords to the anonymous accounts is by using UPDATE to modify the user table directly. Connect to the server as root and issue an UPDATE statement that assigns a value to the Password column of the appropriate user table records. The procedure is the same for Windows and Unix. The following UPDATE statement assigns a password to both anonymous accounts at once: shell> mysql -u root mysql> UPDATE mysql.user SET Password = PASSWORD(' newpwd ') -> WHERE User = ''; mysql> FLUSH PRIVILEGES; After you update the passwords in the user table directly using UPDATE , you must tell the server to re-read the grant tables with FLUSH PRIVILEGES . Otherwise, the change will go unnoticed until you restart the server. If you prefer to remove the anonymous accounts instead, do so as follows: shell> mysql -u root mysql> DELETE FROM mysql.user WHERE User = ''; mysql> FLUSH PRIVILEGES; The DELETE statement applies both to Windows and to Unix. On Windows, if you want to remove only the anonymous account that has the same privileges as root , do this instead: shell> mysql -u root mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User = ''; mysql> FLUSH PRIVILEGES; This account allows anonymous access but has full privileges, so removing it improves security. You can assign passwords to the root accounts in several ways. The following discussion demonstrates three methods:
To assign passwords using SET PASSWORD , connect to the server as root and issue two SET PASSWORD statements. Be sure to encrypt the password using the PASSWORD() function. For Windows, do this: shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD(' newpwd '); mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD(' newpwd '); For Unix, do this: shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD(' newpwd '); mysql> SET PASSWORD FOR 'root'@' host_name ' = PASSWORD(' newpwd '); In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the same hostname that you used when you assigned the anonymous account passwords. To assign passwords to the root accounts using mysqladmin , execute the following commands: shell> mysqladmin -u root password " newpwd " shell> mysqladmin -u root -h host_name password " newpwd " These commands apply both to Windows and to Unix. In the second command, replace host_name with the name of the server host. The double quotes 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. If you are using a server from a very old version of MySQL, the mysqladmin commands to set the password will fail with the message parse error near 'SET password' . The solution to this problem is to upgrade the server to a newer version of MySQL. You can also use UPDATE to modify the user table directly. The following UPDATE statement assigns a password to both root accounts at once: shell> mysql -u root mysql> UPDATE mysql.user SET Password = PASSWORD(' newpwd ') -> WHERE User = 'root'; mysql> FLUSH PRIVILEGES; The UPDATE statement applies both to Windows and to Unix. After the passwords have been set, you must supply the appropriate password whenever you connect to the server. For example, if you want to use mysqladmin to shut down the server, you can do so using this command: shell> mysqladmin -u root -p shutdown Enter password: (enter root password here) Note: If you forget your root password after setting it up, the procedure for resetting it is covered in Section A.4.1, "How to Reset the Root Password." To set up new accounts, you can use the GRANT statement. For instructions, see Section 4.5.2, "Adding New User Accounts to MySQL." |
< Day Day Up > |