Web lists-archives.com

can't start mariadb with client certs specified




Hey guys,

I'm trying to setup multi master replication using SSL under MariaDB 10.20.
I've been able to specify my client certs on the second node (db2) with no
issue and start up the mysql service.

But for some reason when I do the same on the first node (db1) the mysql
service takes a really long time and then times out with the following
message.

[root@db1:~] #systemctl start mysql
Job for mysql.service failed. See 'systemctl status mysql.service' and
'journalctl -xn' for details.

[root@db1:~] #systemctl status mysql.service -l
mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql)
   Active: failed (Result: timeout) since Wed 2015-07-22 02:58:53 UTC; 5min
ago
  Process: 22906 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited,
status=0/SUCCESS)
  Process: 23247 ExecStart=/etc/rc.d/init.d/mysql start (code=killed,
signal=TERM)

Jul 22 02:53:53 db1 systemd[1]: Starting LSB: start and stop MySQL...
Jul 22 02:58:53 db1 systemd[1]: mysql.service operation timed out.
Terminating.
Jul 22 02:58:53 db1 systemd[1]: *Failed to start LSB: start and stop MySQL.*
Jul 22 02:58:53 db1 systemd[1]: *Unit mysql.service entered failed state.*
Jul 22 02:58:53 db1 mysql[23247]: Starting
MySQL...........................................................................................................................................

But at the end, mysql (mariadb) is actually running, but its not running
correctly.

[root@db1:~] #ps -auxwww | grep mysql | grep -v grep
root      1867  0.0  0.1 115344  1696 ?        S    04:18   0:00 /bin/sh
/usr/bin/mysqld_safe --datadir=/var/lib/mysql
--pid-file=/var/lib/mysql/db1.pid
mysql     1976  0.1  9.5 722928 97256 ?        Sl   04:18   0:00
/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql
--plugin-dir=/usr/lib64/mysql/plugin --user=mysql
--log-error=/var/log/mariadb/mariadb.log --pid-file=/var/lib/mysql/db1.pid
--socket=/var/lib/mysql/mysql.sock

And mysql is listening on the right port:

[root@db1:~] #lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1976 mysql   16u  IPv6 100319      0t0  TCP *:mysql (LISTEN)

If I try to go into the mysql command prompt I get this following error:

[root@db1:~] #mysql
ERROR 2026 (HY000): SSL connection error: error:14090086:SSL
routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

And I'm seeing the following errors in the logs:

[root@db1:~] #grep -i error /var/log/mariadb/mariadb.log
150722  4:18:47 [ERROR] Missing system table mysql.roles_mapping; please
run mysql_upgrade to create it
150722  4:18:47 [ERROR] Column count of mysql.events_waits_current is
wrong. Expected 19, found 16. Created with MariaDB 50541, now running
100020. Please use mysql_upgrade to fix this error.
150722  4:18:47 [ERROR] Column count of mysql.events_waits_history is
wrong. Expected 19, found 16. Created with MariaDB 50541, now running
100020. Please use mysql_upgrade to fix this error.
150722  4:18:47 [ERROR] Column count of mysql.events_waits_history_long is
wrong. Expected 19, found 16. Created with MariaDB 50541, now running
100020. Please use mysql_upgrade to fix this error.
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_waits_summary_by_host_by_event_name' has the
wrong structure
150722  4:18:47 [ERROR] Incorrect definition of table
performance_schema.events_waits_summary_by_thread_by_event_name: expected
column 'THREAD_ID' at position 0 to have type bigint(20), found type
int(11).
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_waits_summary_by_user_by_event_name' has the
wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_waits_summary_by_account_by_event_name' has
the wrong structure
150722  4:18:47 [ERROR] Column count of mysql.file_summary_by_event_name is
wrong. Expected 23, found 5. Created with MariaDB 50541, now running
100020. Please use mysql_upgrade to fix this error.
150722  4:18:47 [ERROR] Column count of mysql.file_summary_by_instance is
wrong. Expected 25, found 6. Created with MariaDB 50541, now running
100020. Please use mysql_upgrade to fix this error.
150722  4:18:47 [ERROR] Native table 'performance_schema'.'host_cache' has
the wrong structure
150722  4:18:47 [ERROR] Incorrect definition of table
performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID'
at position 2 to have type bigint(20), found type int(11).
150722  4:18:47 [ERROR] Native table
'performance_schema'.'objects_summary_global_by_type' has the wrong
structure
150722  4:18:47 [ERROR] Incorrect definition of table
performance_schema.rwlock_instances: expected column
'WRITE_LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found
type int(11).
150722  4:18:47 [ERROR] Native table 'performance_schema'.'setup_actors'
has the wrong structure
150722  4:18:47 [ERROR] Native table 'performance_schema'.'setup_objects'
has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'table_io_waits_summary_by_index_usage' has the wrong
structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'table_io_waits_summary_by_table' has the wrong
structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'table_lock_waits_summary_by_table' has the wrong
structure
150722  4:18:47 [ERROR] Column count of mysql.threads is wrong. Expected
14, found 3. Created with MariaDB 50541, now running 100020. Please use
mysql_upgrade to fix this error.
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_stages_current' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_stages_history' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_stages_history_long' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_stages_summary_by_thread_by_event_name' has
the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_stages_summary_by_account_by_event_name' has
the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_stages_summary_by_user_by_event_name' has the
wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_stages_summary_by_host_by_event_name' has the
wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_stages_summary_global_by_event_name' has the
wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_current' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_history' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_history_long' has the wrong
structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_summary_by_thread_by_event_name'
has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_summary_by_account_by_event_name'
has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_summary_by_user_by_event_name' has
the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_summary_by_host_by_event_name' has
the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_summary_global_by_event_name' has
the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'events_statements_summary_by_digest' has the wrong
structure
150722  4:18:47 [ERROR] Native table 'performance_schema'.'users' has the
wrong structure
150722  4:18:47 [ERROR] Native table 'performance_schema'.'accounts' has
the wrong structure
150722  4:18:47 [ERROR] Native table 'performance_schema'.'hosts' has the
wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'socket_instances' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'socket_summary_by_instance' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'socket_summary_by_event_name' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'session_connect_attrs' has the wrong structure
150722  4:18:47 [ERROR] Native table
'performance_schema'.'session_account_connect_attrs' has the wrong structure

I noticed this message standing out from the errors in the logs from above:

150722  4:18:47 [ERROR] Column count of mysql.threads is wrong. Expected
14, found 3. Created with MariaDB 50541, now running 100020. Please use
mysql_upgrade to fix this error.

So if I try to take the advice of that message and try and upgrade mysql I
get another error based on that cert:

[root@db1:~] #mysql_upgrade
Version check failed. Got the following error when calling the 'mysql'
command line client
ERROR 2026 (HY000): SSL connection error: error:14090086:SSL
routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed
FATAL ERROR: Upgrade failed

If I try and verify that cert I do get an error:

[root@db1:/opt/mysql] #openssl verify client-cert.pem
client-cert.pem: C = US, ST = NJ, L = Newark, O = Jokefire LLC, OU = Ops,
CN = db1.example.com, emailAddress = bluethundr@xxxxxxxxxxx
error 18 at 0 depth lookup:self signed certificate
OK

This is the my.cnf file I have with the trouble spot bolded:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
old_passwords=1
ssl

ssl-ca=/opt/mysql/ca-cert.pem
ssl-cert=/opt/mysql/server-cert.pem
ssl-key=/opt/mysql/server-key.pem

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
master-connect-retry=60

!includedir /etc/my.cnf.d

*[client]*
*ssl-ca=/opt/mysql/ca-cert.pem*
*ssl-cert=/opt/mysql/client-cert.pem*
*ssl-key=/opt/mysql/client-key.pem*

And if I remove the client certificate options in bold above, the mysql
service will start up without any problem

[client]
#ssl-ca=/opt/mysql/ca-cert.pem
#ssl-cert=/opt/mysql/client-cert.pem
#ssl-key=/opt/mysql/client-key.pem
"/etc/my.cnf" 32L, 864C written

[root@db1:~] #systemctl start mysql

[root@db1:~] #lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4558 mysql   16u  IPv6 106308      0t0  TCP *:mysql (LISTEN)

I used this method to create the cert and keys that are causing the error:

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

I'm not really sure why these errors are occurring. Can someone please
offer some advice on how to get past this problem?

Thanks,
Tim
-- 
GPG me!!

gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B