Team Members
Fedora 9 Re-Spin Released!
Installing & Configuring MySQL Server
—
filed under:
Server Solutions
This Howto will show you how to install MySQL 5.x, start the service, make sure the server starts on reboot, login via terminal, change the root database admin password, change the name of the root user, add a new user with specific privileges to a specific database, add a new DBA, add a new database, remove all anonymous logins, remove all non-root users, added file security steps, disable remote access (via port 3306), purge the scrollback history, and finally the installation of the gui tool mysql-administrator.
Applicable to Fedora Versions
- Fedora 6, 7, 8, 9
- CentOS 4, 4.5, 5, 5.1
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:
- Start MySQL server daemon (mysqld):
- Login as root database admin to MySQL server:
- Delete ALL users who are not root:
- Change root database admin password: (note: once this step is complete you'll need to login with: mysql -p -u root)
- Change root username to something less guessable for higher security.
- Remove anonymous access to the database(s):
- Add a new user with database admin privs for all databases:
- 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, see below.)
- Add a MySQL database:
- Installing mysql-administrator (note: This tool can be found in the gnome main menu under 'System Tools' once installed):
- Improving local file security (after saving and exiting remember to: service mysqld restart for changes to take effect):
- Disabling remote access to the MySQL server (after saving and exiting remember to: service mysqld restart for changes to take effect).
# 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]:
# chkconfig --level 2345 mysqld on; 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>
mysql> delete from mysql.user where not (host="localhost" and user="root");
Query OK, 5 rows affected (0.15 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
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>
mysql> update mysql.user set user="mydbadmin" where user="root";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
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>
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>
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>
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
# 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]:
The next change is to disable the use of LOAD DATA LOCAL INFILE command, which will help to
prevent against unauthorized reading from local files. This matters especially when new SQL
Injection vulnerabilities in PHP applications are found.
For that purpose, the following parameter should be added in the [mysqld] section in:
/etc/my.cnf
set-variable=local-infile=0
This change applies to the 3306/tcp port, on which MySQL listens by default. Because,
according to the initial assumptions, the database will be used only by locally installed PHP
applications, we can freely disable listening on that port. This will limit possibilities of
attacking the MySQL database by direct TCP/IP connections from other hosts. Local communication
will be still possible throw the mysql.sock socket. In order to disable listening on the
mentioned port, the following parameter should be added to the [mysqld] section of /etc/my.cnf:
skip-networking
If, for some reason, remote access to the database is still required (e.g. to perform remote
data backup), the SSH protocol can be used as follows:
(modify to your needs)
backuphost$ ssh mysqlserver /usr/local/mysql/bin/mysqldump -A > backup
Troubleshooting
How to test
- Make sure mysql and mysql server are indeed installed and that they are the correct versions:
- Starting mysqld on boot:
- Clear MySQL scrollback history (so sensitive data such as passwords cannot be seen by others with access):
- Show all users in the MySQL Server database:
- Delete a user from the MySQL Server database:
- Delete a null user (user without a username) from the MySQL Server database:
# 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
# chkconfig --level 2345 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
]# cat /dev/null > ~/.mysql_history
mysql> select * from mysql.user;
8 rows in set (0.00 sec)
mysql> delete from mysql.user where host = "dev.mydomain.com";
Query OK, 2 rows affected (0.00 sec)
mysql> delete from mysql.user where user = ' ';
Query OK, 1 rows affected (0.00 sec)
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
This has been tested on Fedora 6, 7, 8, & 9 & CentOS 5, 5.1
Forgot your password?
New user?
