[+/-]
After installing MySQL on Unix, you need to initialize the grant tables, start the server, and make sure that the server works satisfactorily. 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:
If you install MySQL on Linux using RPM distributions, the server RPM runs mysql_install_db.
If you install MySQL on Mac OS X using a PKG distribution, the installer runs mysql_install_db.
Otherwise, you'll need to run mysql_install_db yourself.
The following procedure describes how to initialize the grant tables (if that has not previously 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.11.2.2, “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.11.3, “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.
Change location into the top-level directory of your MySQL
installation, represented here by
BASEDIR
:
shell> cd BASEDIR
BASEDIR
is likely to be something
like /usr/local/mysql
or
/usr/local
. The following steps assume
that you are located in this directory.
If necessary, run the mysql_install_db program to set up the initial MySQL grant tables containing the privileges that determine how users are allowed to connect to the server. You'll need to do this if you used a distribution type for which the installation procedure doesn't run the program for you.
Typically, mysql_install_db needs to be run only the first time you install MySQL, so you can skip this step if you are upgrading an existing installation, However, mysql_install_db does not overwrite any existing privilege tables, so it should be safe to run in any circumstances.
To initialize the grant tables, use one of the following
commands, depending on whether
mysql_install_db is located in the
bin
or scripts
directory:
shell>bin/mysql_install_db --user=mysql
shell>scripts/mysql_install_db --user=mysql
It might be necessary to specify other options such as
--basedir
or
--datadir
if
mysql_install_db does not use the correct
locations for the installation directory or data directory.
For example:
shell>bin/mysql_install_db --user=mysql \
--basedir=/opt/mysql/mysql \
--datadir=/opt/mysql/mysql/data
The mysql_install_db script creates the
server's data directory. Under the data directory, it
creates directories for the mysql
database that holds all database privileges and the
test
database that you can use to test
MySQL. The script also creates privilege table entries for
root
and anonymous-user accounts. The
accounts have no passwords initially. A description of their
initial privileges is given in
Section 2.11.3, “Securing the Initial MySQL Accounts”. Briefly, these
privileges allow the MySQL root
user to
do anything, and allow anybody to create or use databases
with a name of test
or starting with
test_
.
It is important to make sure that the database directories
and files are owned by the mysql
login
account so that the server has read and write access to them
when you run it later. To ensure this, the
--user
option should be used as shown if
you run mysql_install_db as
root
. Otherwise, you should execute the
script while logged in as mysql
, in which
case you can omit the --user
option from
the command.
mysql_install_db creates several tables
in the mysql
database, including
user
, db
,
host
, tables_priv
,
columns_priv
, func
,
and others. See Section 5.4, “The MySQL Access Privilege System”, for a
complete listing and description of these tables.
If you don't want to have the test
database, you can remove it with mysqladmin -u root
drop test after starting the server.
If you have trouble with mysql_install_db at this point, see Section 2.11.2.1, “Problems Running mysql_install_db”.
Start the MySQL server:
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, the --user
option should be used as shown if you run
mysqld_safe as system
root
. Otherwise, you should execute the
script while logged in to the system as
mysql
, in which case you can omit the
--user
option from the
command.
Further instructions for running MySQL as an unprivileged user are given in Section 5.3.6, “How to Run MySQL as a Normal User”.
If you neglected to create the grant tables before proceeding to this step, the following message appears in the error log file when you start the server:
mysqld: Can't find file: 'host.frm'
If you have other problems starting the server, see Section 2.11.2.3, “Starting and Troubleshooting the MySQL Server”.
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
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.4.3, for pc-linux-gnu on i686
...
Server version 5.4.3
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:
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 --log &
If mysqld_safe fails, see Section 2.11.2.3, “Starting and Troubleshooting the MySQL Server”.
Run some simple tests to verify that you can retrieve information from the server. The output should be similar to what is shown here:
shell>bin/mysqlshow
+-----------+ | Databases | +-----------+ | mysql | | test | +-----------+ shell>bin/mysqlshow mysql
Database: mysql +---------------------------+ | Tables | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ shell>bin/mysql -e "SELECT Host,Db,User FROM db" mysql
+------+--------+------+ | host | db | user | +------+--------+------+ | % | test | | | % | test_% | | +------+--------+------+
There is a benchmark suite in the
sql-bench
directory (under the MySQL
installation directory) that you can use to compare how
MySQL performs on different platforms. The benchmark suite
is written in Perl. It requires the Perl DBI module that
provides a database-independent interface to the various
databases, and some other additional Perl modules:
DBI DBD::mysql Data::Dumper Data::ShowTable
These modules can be obtained from CPAN (http://www.cpan.org/). See also Section 2.15.1, “Installing Perl on Unix”.
The sql-bench/Results
directory
contains the results from many runs against different
databases and platforms. To run all tests, execute these
commands:
shell>cd sql-bench
shell>perl run-all-tests
If you don't have the sql-bench
directory, you probably installed MySQL using RPM files
other than the source RPM. (The source RPM includes the
sql-bench
benchmark directory.) In this
case, you must first install the benchmark suite before you
can use it. There are separate benchmark RPM files named
mysql-bench-
that contain benchmark code and data.
VERSION
.i386.rpm
If you have a source distribution, there are also tests in
its tests
subdirectory that you can
run. For example, to run
auto_increment.tst
, execute this
command from the top-level directory of your source
distribution:
shell> mysql -vvf test < ./tests/auto_increment.tst
The expected result of the test can be found in the
./tests/auto_increment.res
file.
At this point, you should have the server running. However, none of the initial MySQL accounts have a password, so you should assign passwords using the instructions found in Section 2.11.3, “Securing the Initial MySQL Accounts”.
The MySQL 5.4 installation procedure creates time
zone tables in the mysql
database. However,
you must populate the tables manually using the instructions in
Section 9.6, “MySQL Server Time Zone Support”.
User Comments
You must run mysqld_install_db as the user that mysqld will run under, for example:
shell> su mysql -c mysql_install_db
Otherwise, the daemon will not be able to look in the mysql directory, and will not be able to find the file 'host.frm' inside it.
if you didn't run the mysql_install_db as the mysql user and you are unable to start the mysql daemon with an error such as "Can't find file ... host.frm", all you need to check is see if the dir and files belong to the user mysql.
/var/lib/mysql/mysql and files in it should belong to the user mysql.
just do chown and you should be ok.
cheers.
aj
I am confused Step 6 says ...
6. Verify that you can restart the server. Do this by using mysqld_safe or by invoking mysqld directly. For example:
shell> bin/mysqld_safe --user=mysql --log &
If mysqld_safe fails, see section 2.4.2.3 Starting and Troubleshooting the MySQL Server.
... is it mysqld_safe or safe_mysqld ???
In opposition at what is said in the docs the minimun version of the Perl module MySQL::DBD for MySQL 4.1 is the 2.9004.
The compilation of MySQL::DBD version 2.9003 gives the following error:
mysql.xs: In function `XS_DBD__mysql__dr__admin_internal':
mysql.xs:100: too few arguments to function `mysql_shutdown'
This is because from MySQL version 4.1.3 the function mysql_shutdown has an extra parameter.
As I told before the problem is solved in the version 2.9004.
Thanks to [name withheld] for the advice on running the mysql_install_db script as the mysql user! This appears to be <strong>crucial</strong>, but I didn't find that tip anywhere but in her/his comment.
Have your server up and running but can't get a connection to it over your network? Try commenting out the poorly documented "bind-address = 127.0.0.1" in my.cnf.
While you're there, make sure that "skip-networking" is commented out.
It's worth noting that the package install for OpenBSD creates the "_mysql" user account.
One little underscore that can really confuse readers...
I just spent what would have been a hair-pulling two hours (if I had hair) trying to install mySQL on a mac running osx 10.3.8
I downloaded the nice "easy" installer package from this site, and ran both that installer and the startup item installer. The install seemed to go fine, however, the mysql_install_db would not set up the access tables no matter what I did.
My solution was to activate the root account via netinfo manager, and do a complete reinstall as root, and then manually run mysql_install_db via su as root. Everything worked flawlessly after that. I then deactivated the root account (for the system, not mySQL). I try not to leave a root account active on these systems.
I'm only mentioning this simply because I couldn't find any tips that worked for me, and this seemed to solve the problem.
I had a problem with step 1: 'cd BASEDIR'. My MySQL was installed with the Linux system, so where is BASEDIR? I studied the script "mysql_install_db" and figured this out: Use 'locate' or 'find' for the file "my_print_defaults". That should be BASEDIR/bin/print_my_defaults or BASEDIR/extra/print_my_defaults. In my case BASEDIR was "/usr".
Under Unix/Linux, if you have already run mysql_install_db as root, you may find that running it as the mysql user still doesn't fix the problem - mainly because, as mentioned above, the mysql_install_db utility won't write over databases that are already there (and probably wouldn't be able to even if it tried)
The solution then is to go to wherever your databases are stored (mine are in /var/lib/mysql) and, as root, type:
chown -R mysqluser mysql
where "mysqluser" is the name of whatever user uses the database (most systems have a "mysql" user specifically set up for this purpose) and "mysql" refers to the directory (in my case, /var/lib/mysql/mysql)
Note: I'm no security expert, I'm only using mysql on a small test system... this may be unsafe to do in a deployment system. If someone could advise, please do...)
On OpenBSD, after running
bin/mysqld_safe --user=_mysql &
mysql used to stop immediately. Logfiles showed the message
050622 13:29:53 mysqld started
050622 13:29:53 Can't start server : Bind on unix socket: Permission denied
050622 13:29:53 Do you already have another mysqld server running on socket: /var/run/mysql/mysql.sock ?
Turns out root was owner of /var/run/mysql, so i turned ownership over to _mysql:
chown _mysql /var/run/mysql/
Restart MySQL
I installed MySQL-server-4.1.14-0.glibc23.i386.rpm on Linux. After running
rpm -i MySQL-server-4.1.....rpm
the server started straight away. Then i realised that mysql is installed in /usr and /usr was my BASEDIR.
Then under /usr, i set up the password for the root:
/bin/mysqladmin -u root password 'somepass'
Then i tried to view all databases using
bin/mysqlshow
i got the access denied message, then i tried:
bin/mysqlshow -u root -p
the system then asked me for the password, then 2 databases mysql and test showed up :)
After that, i decided to stop and restart MySQL
bin/mysqladmin -u root shutdown -p
(and typed in the password)
bin/safe_mysqld -user root -p somepass
but i could not restart it, i dont know if the command was incorrect.
then i did a series of chown:
cd /var/lib/mysql
chown -R root mysql
chown -R myusername mysql
chown -R mysql mysql
I then restarted MySQL using:
cd /usr
bin/safe)mysqld --user=root &
and the server started.
Note: so far i run everything as 'root' user of the OS.
After installing the mysql-debug-5.0.15-osx10.4-powerpc.pkg package
and running the mysql server through installed preference pane I got the following error when I tried to set the anonymous passwords:
shell> mysql -u root
Access denied for user 'root'@'localhost' (using password: NO)
So I first followed the directions in A.4.1. How to Reset the Root Password
to reset the root password and then I modified the anonymous and root passwords as in this section.
Watch out when you run perl run-all-tests.
I got a 270 MB log file. You might want to turn query logging off.
OS X: 10.4.6
Installed via Darwin Ports ('port install mysql5')
$sudo /opt/local/lib/mysql5/bin/mysql_install_db --user=mysql
$cd /opt/local/var/db
$ ls -l
drwx------ 9 mysql admin 306 May 8 21:19 mysql5
$cd /opt/local/share/mysql5/mysql
$sudo ./mysql.server start
$mysqladmin ping
mysqld is alive
Using RedHat Enterprise Linux 4:
You need to disable selinux to start the service. So do the following:
1. /usr/sbin/setenforce 0
2. /etc/init.d/mysql start
3. /usr/sbin/setenforce 1
Autostart in Slackware
I been looking all over for this and I found it on another forum, but I believe it is important to be here as I know some people uses Slackware also, and made perfect sense to have it here for that sole purpose.
Source: http://sol4.net/linux/mysql1.shtml
Date Accessed it: April 3, 2007
Date Posted: N/A
Starting MySQL on bootup under Slackware is very simple, it even uses the same script as Redhat. Now unlike the previous example, this one works regardless of how you boot. As with the Redhat example you first need to copy the script to the startup script directory, which is done like this.
cp support-files/mysql.server /etc/rc.d
Just as with the Redhat example you need to make this script executable, which you do with the following command.
chmod +x /etc/rc.d/mysql.server
Now you need to edit your /etc/rc.d/rc.local file and add the following line to the end.
/etc/rc.d/mysql.server start
When this error happens and mysql is being run as a normal user:
mysqld: Can't find file: 'host.frm'
make sure that the *.frm files are owned by the user that runs mysql.
bin/mysql_install_db --user=mysql
assumes that you want to use the configuration in /etc/my.cnf
If you want to use another one (for instance, if you're not root and cannot change that file) then
bin/mysql_install_db --defaults-file=your-conf-file
(there must be a dozen places where this advice belongs)
While installing 5.0.67 on Linux running mysql_install_db failed with the error text
FATAL ERROR: Could not find fill_help_tables.sql inside --basedir
When using --basedir you must point either into a MySQL binary
distribution directory or a compiled tree previously populated
by 'make install'
It seems `make install` put this file and a few others under the datadir, but the script looks for them under basedir.
A workaround is to create the place the files are expected and copy them there:
mkdir $PREFIX/share
cp $DATADIR/mysql/*.sql $PREFIX/share
Reported as Bug #38828
Add your own comment.