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 ?S04: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
Re: can't authenticate ssl user account
Hi Reindel, you client configuration shows no indication for SSL, i see it in my.cnf > only in the [mysqld] section and remember when you initrialize replication > you need to specify it there too > > i doubt there is anything to change the logging but since you *know* what > that user requires that should really not be the problem - said from > somebody using SSL for any mysql connection over TCP for years now > (replication, php-applications, cli-client...) > Ok!! Thanks. But when I try to setup my client configuration to use SSL, mariadb server refuses to start. It times out: [root@db2:~] #systemctl status mysql.service mysql.service - LSB: start and stop MySQL Loaded: loaded (/etc/rc.d/init.d/mysql) Active: failed (Result: exit-code) since Tue 2015-07-21 18:29:24 UTC; 13s ago Process: 19965 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS) Process: 21973 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=1/FAILURE) Jul 21 18:29:23 db2 systemd[1]: Starting LSB: start and stop MySQL... Jul 21 18:29:24 db2 mysql[21973]: Starting MySQL. ERROR! Jul 21 18:29:24 db2 systemd[1]: mysql.service: control process exited, code=exited status=1 Jul 21 18:29:24 db2 systemd[1]: Failed to start LSB: start and stop MySQL. Jul 21 18:29:24 db2 systemd[1]: Unit mysql.service entered failed state. Here's my my.cnf on the client side that is causing the time out error to occur: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 old_passwords=1 ssl server-id=2 replicate-do-db=jfwiki [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 Any idea why that's happening or how to correct it? Thanks, Tim On Tue, Jul 21, 2015 at 4:25 AM, Reindl Harald wrote: > > Am 21.07.2015 um 05:03 schrieb Tim Dunphy: > >> I see absolutely NO indication as to why the login for the 'slave2' user >> (that requires SSL) is failing >> >> So my questions are 1) how to I bump up the verbosity on the logs so I can >> get an indication as to why this is failing? 2) what is the best way to >> troubleshoot this? >> > > you client configuration shows no indication for SSL, i see it in my.cnf > only in the [mysqld] section and remember when you initrialize replication > you need to specify it there too > > i doubt there is anything to change the logging but since you *know* what > that user requires that should really not be the problem - said from > somebody using SSL for any mysql connection over TCP for years now > (replication, php-applications, cli-client...) > > CHANGE MASTER TO MASTER_HOST='masterip', MASTER_USER='user', > MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_CONNECT_RETRY=3600, > MASTER_SSL=1, MASTER_SSL_CA='/etc/mysqlssl/ca.crt', > MASTER_SSL_CERT='/etc/mysqlssl/client.pem', > MASTER_SSL_KEY='/etc/mysqlssl/client.pem'; START SLAVE; > > -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: Starting mysql 5.7.7
Have u executed mysql_install_db? On 15-May-2015 1:31 pm, "Jørn Dahl-Stamnes" wrote: > Hello, > > I have reinstalled a test server after a disk crash. I upgraded from an > older Fedora Core (don't > remember the version) to Fedora Core 21. > > Then I installed Mysql 5.7.7 from RPM. I'm able to start it but mysql does > not use my /etc/my.cnf > file and I do not find any other on my system. > > After doing a 'yum install ...' I started it without a my.cnf file. It > seem to start OK and all the > database files++ was added to /var/lib/mysql. > > Since I got a SSD disk where I want to locate the databases, I stopped > mysql and moved the file to > this disk and added links (just as I did on the old system). > I removed all ib* files and added my myc.fn file (it contain info about > ibdata files etc) and then > tried to start it - no luck. > > Base on the entries in /var/log/messages, it does not seem to use the > /etc/my.cnf file. There is > also a few warnings which I'm not sure how to fix. > > Any suggestions what's wrong? > > > My my.cnf file: > # The MySQL server > [mysqld] > port= 3306 > socket = /tmp/mysql.sock > > skip-external-locking > key_buffer_size = 384M > max_allowed_packet = 32M > table_open_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > read_rnd_buffer_size = 8M > myisam_sort_buffer_size = 64M > thread_cache_size = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 16 > max_connections = 50 > > log-bin=/var/mysql/mysql-bin > > server-id = 1 > > binlog_format=mixed > > # Innodb settings. > > # Open files. > innodb_open_files = 2048 > open_files_limit= 8096 > > innodb_data_home_dir= /data/mysql/data > innodb_data_file_path = > ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend > innodb_file_per_table = 0 > innodb_autoextend_increment = 256 > > innodb_log_group_home_dir = /data/mysql/data > > # You can set .._buffer_pool_size up to 50 - 80 % > # of RAM but beware of setting memory usage too high. > innodb_buffer_pool_size = 25G > > # Set .._log_file_size to 25 % of buffer pool size > innodb_log_file_size= 300M > innodb_log_files_in_group = 2 > innodb_log_buffer_size = 128M > > innodb_flush_log_at_trx_commit = 1 > innodb_support_xa = 0 > innodb_flush_method = O_DIRECT > innodb_lock_wait_timeout= 50 > innodb_fast_shutdown= 0 > > > > From /var/log/messages: > > > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.255284Z 0 [Warning] > TIMESTAMP with implicit > DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp > server option (see > documentation for more details). > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.256631Z 0 [Note] > /usr/sbin/mysqld (mysqld 5.7.7- > rc) starting as process 1528 ... > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.265686Z 0 [Note] > InnoDB: Mutexes and rw_locks use > GCC atomic builtins > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.266263Z 0 [Note] > InnoDB: Uses event mutexes > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.21Z 0 [Note] > InnoDB: GCC builtin > __atomic_thread_fence() is used for memory barrier > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.267045Z 0 [Note] > InnoDB: Compressed tables use > zlib 1.2.3 > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.267432Z 0 [Note] > InnoDB: Using Linux native AIO > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.268269Z 0 [Note] > InnoDB: Number of pools: 1 > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.268804Z 0 [Note] > InnoDB: Using CPU crc32 > instructions > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.270686Z 0 [Note] > InnoDB: Initializing buffer > pool, total size = 128M, instances = 1, chunk size = 128M > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.298852Z 0 [Note] > InnoDB: Completed initialization > of buffer pool > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.353601Z 0 [Note] > InnoDB: If the mysqld execution > user is authorized, page cleaner thread priority can be changed. See the > man page of setpriority(). > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.356077Z 0 [Note] > InnoDB: Highest supported file > format is Barracuda. > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.361383Z 0 [Note] > InnoDB: Log scan progressed past > the checkpoint lsn 1210467 > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.361927Z 0 [Note] > InnoDB: Doing recovery: scanned > up to log sequence number 1210476 > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.362672Z 0 [Note] > InnoDB: Doing recovery: scanned > up to log sequence number 1210476 > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.363083Z 0 [Note] > InnoDB: Database was not > shutdown normally! > May 15 09:41:27 anakin mysqld: 2015-05-15T07:41:27.363456Z 0 [Note] > InnoDB: Starting crash recovery. > May 15 09:
Re: can't authenticate ssl user account
Am 21.07.2015 um 05:03 schrieb Tim Dunphy: I see absolutely NO indication as to why the login for the 'slave2' user (that requires SSL) is failing So my questions are 1) how to I bump up the verbosity on the logs so I can get an indication as to why this is failing? 2) what is the best way to troubleshoot this? you client configuration shows no indication for SSL, i see it in my.cnf only in the [mysqld] section and remember when you initrialize replication you need to specify it there too i doubt there is anything to change the logging but since you *know* what that user requires that should really not be the problem - said from somebody using SSL for any mysql connection over TCP for years now (replication, php-applications, cli-client...) CHANGE MASTER TO MASTER_HOST='masterip', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_CONNECT_RETRY=3600, MASTER_SSL=1, MASTER_SSL_CA='/etc/mysqlssl/ca.crt', MASTER_SSL_CERT='/etc/mysqlssl/client.pem', MASTER_SSL_KEY='/etc/mysqlssl/client.pem'; START SLAVE; signature.asc Description: OpenPGP digital signature
