Linux Admin Reference – Mysql on Redhat Enterprise Linux

MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.This article describe the procedure to install mysql and perform initial configuration required as part of many third party application installations.

Installation of MySql and Confiruring SSL

Download and install the required mysql related packages

yum install mysql-server perl-DBD-MySQL perl-DBI

Start mysql.

service mysqld start

Optionally set mysqld to start at boot

chckconfig mysqld on

Change mysql root password

/usr/bin/mysqladmin -u root password ‘mysql’

Configure SSL for mysql server and the clients that will access the server

mkdir -p /etc/mysql/newcerts  
chown -R mysql:mysql /etc/mysql/newcerts

Create a certificate authority

cd /etc/mysql/newcerts  
openssl genrsa 2048 > ca-key.pem

NOTE This command will ask details of your certificate provider, provide a unique Common Name when asked

openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Create a certificate for the server using the CA certificate generated above

NOTE Do not provide a password if asked in the next step

The Common Name used here must differ from the one used for the Certificate Authority above.

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Create a certificate for the clients using the same CA certificate

NOTE You must provide the details for the client that will connect to the server.
The Common Name used here must differ from the one used for the Certificate Authority and the Server certificate above.

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Make sure following entries are present in /etc/my.cnf under the [mysqld] section

ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem

Restart mysqld

service mysqld restart

Ensure that mysql root is authenticated with SSL and has correct permissions

NOTE : use your mysql root password here.

mysql -u root -p  
mysql> GRANT ALL ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘mysql’ REQUIRE SSL;
mysql> quit

Test that SSL is working

login to the database

cd /etc/mysql/newcerts  
mysql –ssl-cert=ca-cert.pem –ssl-key=client-key.pem –ssl-cert=client-cert.pem -u root -p -v -v -v  
Enter password: <password>

Check for the ciphers

mysql> SHOW STATUS LIKE ‘Ssl_cipher’;  
————–  
SHOW STATUS LIKE ‘Ssl_cipher’  
————–  

+—————+——————–+  
| Variable_name | Value              |  
+—————+——————–+  
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+—————+——————–+  
1 row in set (0.00 sec)  

mysql> show variables like ‘%%ssl%%’;  
————–  
show variables like ‘%%ssl%%’  
————–  

+—————+————————————-+  
| Variable_name | Value                               |  
+—————+————————————-+  
| have_openssl  | YES                                 |  
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/newcerts/ca-cert.pem     |  
| ssl_capath    |                                     |  
| ssl_cert      | /etc/mysql/newcerts/server-cert.pem |  
| ssl_cipher    |                                     |  
| ssl_key       | /etc/mysql/newcerts/server-key.pem  |  
+—————+————————————-+  
7 rows in set (0.01 sec)  

mysql> quit

Configuring Remote access to MySQL Server

 

 

On  MySQL server:

1. Install mysql-server package on server

# yum install mysql-server

2. Edit /etc/my.cnf file

# vi /etc/my.cnf

Search for [mysqld] section, If skip-networking line exists then comment out this line and add following entry:

bind-address=<server IP address>

3. Restart the mysql server:

# service mysqld restart

4. Connect to mysql server and provide access to remote system:

mysql> CREATE DATABASE <db-name>;
mysql> GRANT ALL ON <db-name>.* TO <username>@'<IP Address>’ IDENTIFIED BY ‘PASSWORD’;

Where,     <username>@'<IP address>’ –  is the address of the  remote host machine

PASSWORD – is the password used to connect mysql  from remote system. replace “PASSWORD” with actual password.

4. Exit from mysql

mysql> exit

5. Open TCP port 3306 on server to provide access to remote  system:

# iptables -I INPUT -p tcp –dport 3306 -j ACCEPT
# service iptables save

On the Remote Client Server

1. Install mysql package

# yum install mysql

2. Execute following command to connect mysql:

# mysql -u <username> –h <Server IP address> –p

 

How to Reset MYSQL Database Root Password

 

The MySQL root password can be set using the following procedure:

Stop the MySQL service:

 

# service mysqld stop
Stopping MySQL:                                            [  OK  ]

 

Start MySQL with:

# /usr/bin/mysqld_safe –skip-grant-tables &

On Red Hat Enterprise Linux 3, mysqld_safe was called safe_mysqld:

# /usr/bin/safe_mysqld –skip-grant-tables &

 

Note: mysql_safe is a shell script which invokes mysqld, but additionally traps any forceful terminations of the MySQL server and avoids any database corruption.

 
Change the password of the root user:

 

# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD(‘new_password’) WHERE user=’root’;
mysql> FLUSH PRIVILEGES;
mysql> exit;

 

Restart mysqld using mysqladmin to ensure that the service shuts down successfully (as it was started manually in step 2).

# mysqladmin shutdown

 

Restart the MySQL service as per normal:

# service mysqld start