Security

MySQL 5.7 auto-generated root password

Hi There.

You may have noticed something different when you install a fresh copy of MySQL 5.7 or 8.0 either via yum or using binary source. The root password is auto-generated by default, unlikely on older versions where it was empty.
Ok, and where can I find it?

Centos
cat /var/log/mysqld.log | grep "A temporary password is generated for" | awk '{print $NF}'
Example:

[root@master ~]# cat /var/log/mysqld.log | grep "A temporary password is generated for" | awk '{print $NF}'
a3BGf#TY.pBj

Binary distribution
Once you initialization the datadir mysqld --initialize you will see the bellow log printed on screen:

. . .
2016-01-13T21:05:03.070322Z 1 [Note] A temporary password is generated for root@localhost: vL8n>Hs%kr>s
. . .

You will be required to change the password on the first login. You can do it following step 2 of this article.

That’s it.
Now you guys know how to find the auto generated root password for MySQL 5.7.

See you next time.

Auditing MySQL With Mcafee Audit Plugin

mysql-audit

Audit MySQL isn’t an easy task by default, you can use some technics like tcpdump, write a parser for general log, use MySQL proxy, or you can use some of audit plugins available out there(Mcafee MySQL Audit Plugin or MySQL Enterprise Audit Log Plugin for example).

On this post I’ll cover the Mcafee MySQL Audit Plugin (https://github.com/mcafee/mysql-audit), on a follow-up post I’ll talk about MySQL Enterprise Audit Log Plugin.

The installation is easy and require just a few steps, I’m using MySQL 5.5 32 bits, so I’ll download the files for my MySQL version and architecture from https://github.com/mcafee/mysql-audit/downloads

[root@mysql-audit marcelo]# wget https://github.com/downloads/mcafee/mysql-audit/audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
[root@mysql-audit marcelo]# unzip audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
Archive:  audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
   creating: audit-plugin-mysql-5.5/
   creating: audit-plugin-mysql-5.5/lib/
  inflating: audit-plugin-mysql-5.5/lib/libaudit_plugin.so  
  inflating: audit-plugin-mysql-5.5/COPYING  
  inflating: audit-plugin-mysql-5.5/THIRDPARTY.txt  
  inflating: audit-plugin-mysql-5.5/README.txt

Next step is to copy the libaudit_plugin.so to MySQL plugin dir:

mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'plugin_dir';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| plugin_dir    | /var/marcelo/5.5.33/lib/plugin/ |
+---------------+---------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > quit
Bye
[root@mysql-audit audit-plugin-mysql-5.5]# cp lib/libaudit_plugin.so /var/marcelo/5.5.33/lib/plugin/

In order to work, Mcafee MySQL Audit Plugin needs to extract some offsets from MySQL server, some of them are build-in the the Audit code, but some aren’t (see https://github.com/mcafee/mysql-audit/wiki/Troubleshooting for more information).
GDB will be necessary to extract the offsets, use yum install gdb or apt-get install gdb

[root@mysql-audit marcelo]# wget https://raw.github.com/mcafee/mysql-audit/v1.0.3/offset-extract/offset-extract.sh
[root@mysql-audit marcelo]# ./offset-extract.sh 5.5.33/bin/mysqld
//offsets for: 5.5.33/bin/mysqld (5.5.33)
{"5.5.33","3172729c5bf6e81c8d87fe26fe248204", 3816, 3844, 2368, 2700, 44, 1656},

Now we are going to change our .cnf file to load and enable the plugin by default:

# add under the [mysqld] section of your .cnf
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=3816, 3844, 2368, 2700, 44, 1656
audit_json_file=1

Restart the server, by default, a file named mysql-audit.json is created on MySQL datadir, from now one, you can parse the file to extract the information that you think is relevant to you, see bellow some examples:

Get failed attempts to connect to MySQL server:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"cmd\":\"Failed Login\"'
{"msg-type":"activity","date":"1381764013653","thread-id":"65","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764016225","thread-id":"66","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764026005","thread-id":"67","query-id":"0","user":"root","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}

Get sucessful attempts to connect to MySQL server:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"cmd\":\"Connect\"'
{"msg-type":"activity","date":"1381763915626","thread-id":"60","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763915626","thread-id":"61","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763915626","thread-id":"62","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763916006","thread-id":"64","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}

Get all activities from a specific host

[root@mysql-audit data]# cat mysql-audit.json | grep '\"ip\":\"10.10.2.68\"'
{"msg-type":"activity","date":"1381763919836","thread-id":"60","query-id":"4798","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"SELECT SUM(K) FROM sbtest8 WHERE id BETWEEN 151257 AND 151257+99"}
{"msg-type":"activity","date":"1381763919844","thread-id":"62","query-id":"4799","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest6","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest6 WHERE id BETWEEN 141568 AND 141568+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919847","thread-id":"47","query-id":"4800","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"update","objects":[{"db":"test","name":"sbtest3","obj_type":"TABLE"}],"query":"UPDATE sbtest3 SET k=k+1 WHERE id=150189"}
{"msg-type":"activity","date":"1381763919848","thread-id":"60","query-id":"4801","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest8 WHERE id BETWEEN 175916 AND 175916+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919869","thread-id":"64","query-id":"4803","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest10","obj_type":"TABLE"}],"query":"SELECT DISTINCT c FROM sbtest10 WHERE id BETWEEN 132850 AND 132850+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919881","thread-id":"57","query-id":"4784","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"commit","query":"COMMIT"}
{"msg-type":"activity","date":"1381763919930","thread-id":"37","query-id":"4802","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"commit","query":"COMMIT"}
{"msg-type":"activity","date":"1381764013653","thread-id":"65","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764016225","thread-id":"66","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764026005","thread-id":"67","query-id":"0","user":"root","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}

Get all DELETES on a specific table(sbtest8) from a specific host

[root@mysql-audit data]# cat mysql-audit.json | grep -i '\"cmd\":\"delete\"' | grep -i '\"name\":\"sbtest8\"' | grep '\"ip\":\"10.10.2.68\"'
{"msg-type":"activity","date":"1381763914163","thread-id":"53","query-id":"3366","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=150652"}
{"msg-type":"activity","date":"1381763914947","thread-id":"44","query-id":"3513","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=194099"}
{"msg-type":"activity","date":"1381763915718","thread-id":"36","query-id":"3630","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=151405"}
{"msg-type":"activity","date":"1381763916273","thread-id":"48","query-id":"3799","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=148546"}
{"msg-type":"activity","date":"1381763918698","thread-id":"40","query-id":"4437","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=149492"}

As you can see, you can extract and adjust the log to fit your needs, there is also some configurations that you can enable to change the way the plugin works (https://github.com/mcafee/mysql-audit/wiki/Configuration for more info) :

audit_record_cmds – list of commands that you would like to record, for example, if you want to record only failed connections and connections, change it to : audit_record_cmds=”Failed Login,Connect”
audit_record_objs – list of objects/tables that you would like to record activity, for example, if you want to record only activity on test database, change it to audit_record_objs=”test.*”, if you want to record activity only for table sbtest1 and sbtest2 change it to audit_record_objs=”test.sbtest1,test.sbtest2″

That is it, use you imagination and start to audit your MySQL server.

MySQL Replication with SSL

Hi folks, let’s continue talk about replication, you can see the basics in this other post

First of all, lets create the ssl certificates, go to the master server:
Create CA certificate:

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

Create server certificate:

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 client certificate:

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

copy ca-cert.pem, client-req.pem, client-cert.pem to slave server

Adjust the cnf file:

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

Make sure you restart the MySQL service, and check if the ssl is enabled

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/sslcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/sslcerts/server-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/sslcerts/server-KEY.pem  |
+---------------+-------------------------------------+
7 rows IN SET (0.00 sec)

Then, we will need a replication user, and we will specify that this user require ssl:

GRANT REPLICATION SLAVE ON *.* TO 'user'@'178.77.90.38' IDENTIFIED BY 'PASSWORD' REQUIRE SSL;
FLUSH PRIVILEGES;

Ok, we have finish your work on master, let’s work on slave server:

Adjust the cnf file:

ssl
ssl-ca=/etc/mysql/sslcerts/ca-cert.pem
ssl-cert=/etc/mysql/sslcerts/client-cert.pem
ssl-key=/etc/mysql/sslcerts/client-key.pem

Let’s check if is everythink ok with ssl

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/sslcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/sslcerts/client-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/sslcerts/client-KEY.pem  |
+---------------+-------------------------------------+
7 rows IN SET (0.00 sec)

Finally, Start the replica:

CHANGE MASTER TO MASTER_HOST='xxxxxx', MASTER_USER='user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/sslcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/sslcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/sslcerts/client-key.pem';
START SLAVE;