Installing & Configuring MySQL Server

by opsec — last modified Jan 30, 2008 03:46 AM

Contributors: opsec

— filed under: Server Solutions

This howto will show you how to install MySQL 5.x, start the service, login via terminal, change the root database admin password, add a new user, add a new database, remove all anonymous logins, and finally the installation of the gui tool mysql-administrator.

Applicable to Fedora Versions

Fedora Core 6 all versions

Requirements

Login to a terminal as root using one of these options: su --login | su -l | or: su -
Yum must also be installed and functional

Doing the Work

Install mysql mysql-server:

# yum install mysql mysql-server
Loading "priorities" plugin
Loading "changelog" plugin
Loading "fastestmirror" plugin
Loading "allowdowngrade" plugin
Loading "kernel-module" plugin
Loading "fedorakmod" plugin
Loading "installonlyn" plugin
Loading "protectbase" plugin
Setting up Install Process
Setting up repositories
livna                     100% |=========================| 1.1 kB    00:00     
updates                   100% |=========================| 1.2 kB    00:00     
core                      100% |=========================| 1.1 kB    00:00     
extras                    100% |=========================| 1.1 kB    00:00     
Loading mirror speeds from cached hostfile
Reading repository metadata in from local files
primary.xml.gz            100% |=========================| 1.8 MB    00:06     
extras    : ################################################## 5594/5594
0 packages excluded due to repository priority protections
0 packages excluded due to repository protections
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for mysql to pack into transaction set.
mysql-5.0.27-1.fc6.i386.r 100% |=========================|  36 kB    00:00     
---> Package mysql.i386 0:5.0.27-1.fc6 set to be updated
---> Downloading header for mysql-server to pack into transaction set.
mysql-server-5.0.27-1.fc6 100% |=========================|  33 kB    00:00     
---> Package mysql-server.x86_64 0:5.0.27-1.fc6 set to be updated
---> Downloading header for mysql to pack into transaction set.
mysql-5.0.27-1.fc6.x86_64 100% |=========================|  36 kB    00:00     
---> Package mysql.x86_64 0:5.0.27-1.fc6 set to be updated
--> Running transaction check
--> Processing Dependency: perl-DBI for package: mysql-server
--> Processing Dependency: perl(DBI) for package: mysql
--> Processing Dependency: perl(DBI) for package: mysql-server
--> Processing Dependency: perl-DBD-MySQL for package: mysql-server
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for perl-DBI to pack into transaction set.
perl-DBI-1.52-1.fc6.x86_6 100% |=========================|  16 kB    00:00     
---> Package perl-DBI.x86_64 0:1.52-1.fc6 set to be updated
---> Downloading header for perl-DBD-MySQL to pack into transaction set.
perl-DBD-MySQL-3.0007-1.f 100% |=========================| 8.5 kB    00:00     
---> Package perl-DBD-MySQL.x86_64 0:3.0007-1.fc6 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
 Package                 Arch       Version          Repository        Size 
=============================================================================
Installing:
 mysql                   i386       5.0.27-1.fc6     updates           3.3 M
 mysql                   x86_64     5.0.27-1.fc6     updates           3.3 M
 mysql-server            x86_64     5.0.27-1.fc6     updates            10 M
Installing for dependencies:
 perl-DBD-MySQL          x86_64     3.0007-1.fc6     core              147 k
 perl-DBI                x86_64     1.52-1.fc6       core              605 k

Transaction Summary
=============================================================================
Install      5 Package(s)         
Update       0 Package(s)         
Remove       0 Package(s)         

Total download size: 18 M
Is this ok [y/N]:

Start MySQL server daemon (mysqld):

# service mysqld start
Initializing MySQL database:  Installing all prepared tables
Fill help tables

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 angstrom 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 the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

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  ]

# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Change root database admin password: (note: once this step is complete you'll need to login with: mysql -u root -p)

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

Remove anonymous access to the database(s):

mysql> DELETE FROM mysql.user WHERE User = '';
Query OK, 2 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

Add a new user with database admin privs for all databases:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'warren'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

Add a new user with database admin privs for a specific database, in this case the database is called "bugzilla": (note: The 'bugzilla' database must first be added)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON bugzilla.* TO 'warren'@'localhost' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

Add a MySQL database:

mysql> create database bugzilla;
Query OK, 1 row affected (0.15 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Installing mysql-administrator (note: This tool can be found in the gnome main menu under 'System Tools' once installed):

# yum install mysql-administrator
Loading "priorities" plugin
Loading "changelog" plugin
Loading "fastestmirror" plugin
Loading "allowdowngrade" plugin
Loading "kernel-module" plugin
Loading "fedorakmod" plugin
Loading "installonlyn" plugin
Loading "protectbase" plugin
Setting up Install Process
Setting up repositories
Loading mirror speeds from cached hostfile
Reading repository metadata in from local files
0 packages excluded due to repository priority protections
0 packages excluded due to repository protections
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for mysql-administrator to pack into transaction set.
mysql-administrator-1.1.1 100% |=========================|  25 kB    00:00     
---> Package mysql-administrator.x86_64 0:1.1.10-3.fc6 set to be updated
--> Running transaction check
--> Processing Dependency: libsigc-2.0.so.0()(64bit) for package: mysql-administrator
--> Processing Dependency: mysql-gui-common for package: mysql-administrator
--> Processing Dependency: libgdkmm-2.4.so.1()(64bit) for package: mysql-administrator
--> Processing Dependency: libpangomm-1.4.so.1()(64bit) for package: mysql-administrator
--> Processing Dependency: libglibmm-2.4.so.1()(64bit) for package: mysql-administrator
--> Processing Dependency: libcairomm-1.0.so.1()(64bit) for package: mysql-administrator
--> Processing Dependency: libatkmm-1.6.so.1()(64bit) for package: mysql-administrator
--> Processing Dependency: libgtkmm-2.4.so.1()(64bit) for package: mysql-administrator
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for gtkmm24 to pack into transaction set.
gtkmm24-2.10.5-1.fc6.x86_ 100% |=========================| 7.8 kB    00:00     
---> Package gtkmm24.x86_64 0:2.10.5-1.fc6 set to be updated
---> Downloading header for cairomm to pack into transaction set.
cairomm-1.2.4-1.fc6.x86_6 100% |=========================| 5.2 kB    00:00     
---> Package cairomm.x86_64 0:1.2.4-1.fc6 set to be updated
---> Downloading header for libsigc++20 to pack into transaction set.
libsigc++20-2.0.17-2.x86_ 100% |=========================| 6.1 kB    00:00     
---> Package libsigc++20.x86_64 0:2.0.17-2 set to be updated
---> Downloading header for glibmm24 to pack into transaction set.
glibmm24-2.12.3-1.x86_64. 100% |=========================| 6.1 kB    00:00     
---> Package glibmm24.x86_64 0:2.12.3-1 set to be updated
---> Downloading header for mysql-gui-common to pack into transaction set.
mysql-gui-common-1.1.10-3 100% |=========================|  17 kB    00:00     
---> Package mysql-gui-common.x86_64 0:1.1.10-3.fc6 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
 Package                 Arch       Version          Repository        Size 
=============================================================================
Installing:
 mysql-administrator     x86_64     1.1.10-3.fc6     extras            1.5 M
Installing for dependencies:
 cairomm                 x86_64     1.2.4-1.fc6      extras             40 k
 glibmm24                x86_64     2.12.3-1         extras            145 k
 gtkmm24                 x86_64     2.10.5-1.fc6     extras            1.1 M
 libsigc++20             x86_64     2.0.17-2         extras             49 k
 mysql-gui-common        x86_64     1.1.10-3.fc6     extras            208 k

Transaction Summary
=============================================================================
Install      6 Package(s)         
Update       0 Package(s)         
Remove       0 Package(s)         

Total download size: 3.1 M
Is this ok [y/N]:

Troubleshooting

How to test

Make sure mysql and mysql server are indeed installed and that they are the correct versions:

# rpm -qa | grep mysql && chkconfig --list | grep mysql
mysql-5.0.27-1.fc6
mysql-5.0.27-1.fc6
mysql-gui-common-1.1.10-3.fc6
mysql-server-5.0.27-1.fc6
mysql-administrator-1.1.10-3.fc6
mysqld          0:off   1:off   2:off   3:off   4:off   5:off   6:off

Starting mysqld on boot:

# chkconfig mysqld on && service mysqld restart && chkconfig --list | grep mysqld
Stopping MySQL:                                            [  OK  ]
Starting MySQL:                                            [  OK  ]
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

Common problems and fixes

Problem: User has not properly logged in with roots environment.

Fix: Login with: su --login | su -l | su -

More Information

Disclaimer

We test this stuff on our own machines, really we do. But you may run into problems, if you do, come to #fedora on irc.freenode.net

Added Reading

http://dev.mysql.com/doc/

Personal tools