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 = bluethu...@exmaple.com 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
