Re: can't start mariadb with client certs specified
> hopefully you did run mysql_upgrade first on the slave as all docs in that > context saying and after that on the master Cool!! Yeah, running mysql_upgrade did the trick. After I performed that on the slave, that error went away. I actually did the same on both nodes figuring it would be best to keep both nodes identical. Sorry, I'm a little new to database administration. Been working mostly on the SA side for years now however. however, i would just double-rsync (hot and than cold) the whole datadir to > the not running slave and just start replication with that binary identical > copies from scratch, doing that in case of any replication issues for many > years now and if it's just for safety What I'm doing is using a dump from last night's backup on both nodes. Once I get replication working the way I want I'm going to do another final dump from the live web node. And then stop replication and import that backup into both nodes. Then start it up again and point the web nodes to the new HA/Mariadb setup that I'm working on. We've been having some availability problems with the database lately on this wiki site. The site runs on 3 t2 micros and the database lives on one of the web nodes. And occasionally the database crashes because it's too squeezed for memory. I figure that moving mysql (or mariadb in this case) to another 2 nodes and going for high availability will solve that problem. So now that I have replication working fine going from db1 to db2 in master slave, I just reversed the replication direction of the replication so I can have master/master working. So now I have master / slave going from db1 -> db2 and just a minute ago I setup master / slave going from db2 -> db1. And if I'm not mistaken that's all that Master/Master is? Is there anything else I need to worry about really, in terms of achieving HA? Not sure if I'm oversimplifying this Thanks, Tim On Wed, Jul 22, 2015 at 10:38 AM, Reindl Harald wrote: > > > Am 22.07.2015 um 16:24 schrieb Tim Dunphy: > >> Hi Reindl, >> >> what about running mysql_upgrade *directly* after the major update >> and *before* touch anything else? >> >> That was precisely what happened. In setting up the new database >> machine, puppet had installed version 5 of mariadb. Before even starting >> up version of for the first time, I uninstalled the RPM's for that >> version and installed version 10.0.20 from the mariadb repo. >> >> However I was able to solve this problem my recreating the keys and >> certs. I had setup master/slave replication using this tutorial: >> >> >> https://www.howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-centos-5.4 >> >> I'm not sure what the problem is with the way the keys are generated in >> this article that could have caused that last, rather long email. :) >> >> So after I regenerated the keys and certs using this method: >> >>182 openssl genrsa -des3 -out db1.example.com.key 4096 >>183 openssl rsa -in db1.example.com.key -out db1.example.com.key >>184 openssl req -new -key db1.example.com.key -out >> db1.jokefire.com.csr >>185 openssl x509 -req -days 3650 -in db1.example.com.csr -CA ca.crt >> -CAkey ca.key -set_serial 01 -out db1.example.com.crt >> > > you need the same certs and same CA on both sides > > *Last_Error: Unable to load replication GTID slave state from >> mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* >> >> Last_IO_Errno: 0 >> >> Last_IO_Error: >> >> Last_SQL_Errno: 1146 >> >> * Last_SQL_Error: Unable to load replication GTID slave state from >> mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* >> >> Any idea what that error means and how I can get rid of it? >> > > hopefully you did run mysql_upgrade first on the slave as all docs in that > context saying and after that on the master > > however, i would just double-rsync (hot and than cold) the whole datadir > to the not running slave and just start replication with that binary > identical copies from scratch, doing that in case of any replication issues > for many years now and if it's just for safety > > > > -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: can't start mariadb with client certs specified
Am 22.07.2015 um 16:24 schrieb Tim Dunphy: Hi Reindl, what about running mysql_upgrade *directly* after the major update and *before* touch anything else? That was precisely what happened. In setting up the new database machine, puppet had installed version 5 of mariadb. Before even starting up version of for the first time, I uninstalled the RPM's for that version and installed version 10.0.20 from the mariadb repo. However I was able to solve this problem my recreating the keys and certs. I had setup master/slave replication using this tutorial: https://www.howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-centos-5.4 I'm not sure what the problem is with the way the keys are generated in this article that could have caused that last, rather long email. :) So after I regenerated the keys and certs using this method: 182 openssl genrsa -des3 -out db1.example.com.key 4096 183 openssl rsa -in db1.example.com.key -out db1.example.com.key 184 openssl req -new -key db1.example.com.key -out db1.jokefire.com.csr 185 openssl x509 -req -days 3650 -in db1.example.com.csr -CA ca.crt -CAkey ca.key -set_serial 01 -out db1.example.com.crt you need the same certs and same CA on both sides *Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 * Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* Any idea what that error means and how I can get rid of it? hopefully you did run mysql_upgrade first on the slave as all docs in that context saying and after that on the master however, i would just double-rsync (hot and than cold) the whole datadir to the not running slave and just start replication with that binary identical copies from scratch, doing that in case of any replication issues for many years now and if it's just for safety signature.asc Description: OpenPGP digital signature
Re: can't start mariadb with client certs specified
Hi Reindl, what about running mysql_upgrade *directly* after the major update and > *before* touch anything else? That was precisely what happened. In setting up the new database machine, puppet had installed version 5 of mariadb. Before even starting up version of for the first time, I uninstalled the RPM's for that version and installed version 10.0.20 from the mariadb repo. However I was able to solve this problem my recreating the keys and certs. I had setup master/slave replication using this tutorial: https://www.howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-centos-5.4 I'm not sure what the problem is with the way the keys are generated in this article that could have caused that last, rather long email. :) So after I regenerated the keys and certs using this method: 182 openssl genrsa -des3 -out db1.example.com.key 4096 183 openssl rsa -in db1.example.com.key -out db1.example.com.key 184 openssl req -new -key db1.example.com.key -out db1.jokefire.com.csr 185 openssl x509 -req -days 3650 -in db1.example.com.csr -CA ca.crt -CAkey ca.key -set_serial 01 -out db1.example.com.crt I was able to start up both db's 1 and 2 with the SSL keys and certs specified in both the client and server sections of the my.cnf. Like this example from the first node shows: [root@db1:~] #egrep -v '^#|^$' /etc/my.cnf [mysqld] ...redacted. sslssl-ca=/opt/mysql/ca.crt ssl-cert=/opt/mysql/db1.example.com.crt ssl-key=/opt/mysql/db1.example.com.key ssl-cipher=AES256-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:!aNULL server-id=1 log_bin=/var/log/mariadb/mariadb-bin.log gtid-domain-id=1 ... [mysqld_safe] [client] ssl-ca=/opt/mysql/ca.crt ssl-cert=/opt/mysql/db1.example.com.crt ssl-key=/opt/mysql/db1.example.com.key Anyplace you see a line of dots there was omitted to save space. I am facing a new issue however. It looks as if I've been able to get slave replication working as I try to demonstrate below: [root@db2:~] #mysql -e "show slave status \G" | egrep -i "Slave_IO_State|Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master" Slave_IO_State: Waiting for master to send event Master_Log_File: mariadb-bin.04 Relay_Master_Log_File: mariadb-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 But I'm seeing an error that I've never seen before relating to 'gtid': [root@db2:~] #mysql -e "show slave status \G" | egrep -i "error|errno" Last_Errno: 1146 *Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 * Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist* Any idea what that error means and how I can get rid of it? I've done some reading on the error and tried setting my gtid variables for both nodes. For example this is what I have in the first node: MariaDB [(none)]> show variables like '%gtid%'; ++---+ | Variable_name | Value | ++---+ | gtid_binlog_pos| | | gtid_binlog_state | | | gtid_current_pos | | | gtid_domain_id | 1 | | gtid_ignore_duplicates | OFF | | gtid_seq_no| 0 | | gtid_slave_pos | | | gtid_strict_mode | OFF | | last_gtid | | ++---+ 9 rows in set (0.00 sec) Can you please help me out with this one as well? Thanks, Tim On Wed, Jul 22, 2015 at 4:31 AM, Reindl Harald wrote: > > > Am 22.07.2015 um 06:39 schrieb Tim Dunphy: > >> 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 >> > > what about running mysql_upgrade *directly* after the major update and > *before* touch anything else? > > > 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 > > -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: can't start mariadb with client certs specified
Am 22.07.2015 um 06:39 schrieb Tim Dunphy: 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 what about running mysql_upgrade *directly* after the major update and *before* touch anything else? 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 signature.asc Description: OpenPGP digital signature
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