I needed to set up MySQL
on a server, since it was a prerequisite for other software I needed to
install on the system. The mysql software was already installed on the system
as mysql --version and rpm -qi mysql showed.
If it is not installed, on a Fedora or CentOS system or other Linux
systems that use yum to manage packages, you can use
yum to install it.
# yum install mysql mysql-server
When I checked on whether the mysql server software was running on the
system by issuing the command mysql, I found it was not running.
The chkconfig command also showed it was not configured to
start when the system boots.
# mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) # chkconfig --list mysqld mysqld 0:off 1:off 2:off 3:off 4:off 5:off 6:off
I configured it to start when the system boots and started the mysqld service.
# chkconfig --level 2345 mysqld on; service mysqld start
Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h example.com password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[ OK ]
Starting MySQL: [ OK ]
Once the MySQL software is started, you should set a password for
the root account, since it won't have one initially. You can do so by
logging into MySQL as root with mysql -u root. You can
check to see what accounts already exist and whether they have a password
with SELECT Host, User, Password FROM mysql.user;. You can
set a password for root@localhost with
SET PASSWORD FOR 'root'@'localhost' =
PASSWORD('SomeGoodPassword');, where SomeGoodPassword
is a
strong password that you select.
# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT Host, User, Password FROM mysql.user;
+-----------------+------+----------+
| Host | User | Password |
+-----------------+------+----------+
| localhost | root | |
| example.com | root | |
| 127.0.0.1 | root | |
+-----------------+------+----------+
3 rows in set (0.00 sec)
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('SomeGoodPassword');
Query OK, 0 rows affected (0.01 sec)
Even though you see the message "0 rows affected", the password has been
set as you can see with SELECT Host, User, Password FROM mysql.user;
(Note: you can bring back previously entered commands with the
upward pointing arrow key on your keyboard).
mysql> SELECT Host, User, Password FROM mysql.user; +-----------------+------+------------------+ | Host | User | Password | +-----------------+------+------------------+ | localhost | root | 67e9e21c4866ed49 | | example.com | root | | | 127.0.0.1 | root | | +-----------------+------+------------------+ 3 rows in set (0.00 sec)
You should also set passwords for the other root entries. Use the
upward pointing arrow on the keyboard to recall the previous SET
PASSWORD command and modify it to be root@example.com,
where example.com is the name listed for your server, and
then do the same for the loopback address, 127.0.0.1. If you then
check the account information with SELECT Host, User, Password FROM
mysql.user;, you should see that passwords have been set for
all root entries. You can exit from mysql by using the exit
command.
mysql> SET PASSWORD FOR 'root'@'example.com' = PASSWORD('SomeGoodPassword');
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('SomeGoodPassword');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host, User, Password FROM mysql.user;
+-----------------+------+------------------+
| Host | User | Password |
+-----------------+------+------------------+
| localhost | root | 67e9e21c4866ed49 |
| example.com | root | 67e9e21c4866ed49 |
| 127.0.0.1 | root | 67e9e21c4866ed49 |
+-----------------+------+------------------+
3 rows in set (0.00 sec)
mysql> exit
Once you've set the root password, you will need to login with
mysql -p -u root. You will be prompted for the password you
set.
The commands that you enter during a mysql session are stored in
the file .mysql_history in the account's login directory. Reviewing
that history file can be useful for troubleshooting, but keep in mind that
the password you used when you entered the SET PASSWORD command
will be stored there as well.
# cat ~/.mysql_history
SELECT Host, User, Password FROM mysql.user;
SET PASSWORD FOR root = PASSWORD('SomeGoodPassword');
SET PASSWORD FOR 'root' = PASSWORD('SomeGoodPassword');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('SomeGoodPassword');
SELECT Host, User, Password FROM mysql.user;
SET PASSWORD FOR 'root'@'example.com' = PASSWORD('SomeGoodPassword');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('SomeGoodPassword');
SELECT Host, User, Password FROM mysql.user;
So, if you've used the SET PASSWORD
command during a session, you may want to wipe out the contents of that file
with cat /dev/null > ~/.mysql_history.
# cat /dev/null > ~/.mysql_history
References: