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.pemMake 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.pemRestart 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
1 Response
[…] Read –Â Install and Configure Mysql on Redhat Enterprise Linux […]