How to actually set up MariaDB replication/high availability

I’m setting up a high availability LAMP stack – so far, I have two servers with HAProxy running on both their frontends that own a floating IP. HAProxy redirects requests to backend Apache servers running on each of them (same machine, port 8000).

This all works, and I’m happy with this so far for high availability. I have yet to deal with the databases yet however.

Since I only have two servers, I don’t think I can do master-master like Galera cluster due to the requirement of quorum. So, I planned to do master-slave read only replication.

Most of the guides I see online don’t mention the MaxScale proxy that MariaDB suggests in its high availability guide (page 4 for example)

I’m just really confused overall on what to do and how to set it up?

From my perspective, I’d like to set up a MaxScale proxy on one of my servers and run MariaDB on both servers; MaxScale will handle things like redirects. How can I actually accomplish what I’m looking for?

Go to Source
Author: forkwasher

MariaDB subqueries to same table and column resulting in several columns

I have a table and want to pick monthly minute data to compare column wize in 10.3.13-MariaDB

Tested and tested for hours and hours different approaches without success, one example is below. Some tests does not complain syntactically but takes forever, and some tests complains about column names not recognized. All subqueries if tested separately return the same number of records, each in one column.

`SELECT RD, OT1, OT2, OT3 FROM

(SELECT rdate from OO where month(rdate) = 7 and year(rdate) = 2006) AS RD,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2006) AS OT1,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2007) AS OT2,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2008) AS OT3;`

The result should be something like:

`RD OT1 OT2 OT3

2006-07-01 00:00:00 1.2345 2.1234 1.543

… … … …

2006-07-31 23:59:00 3.456 3.234 2.234`

And, no I dont want to use UNION because then they will still follow one after the other…

Any thoughts?!

Go to Source
Author: OldBadger

SQLMap Only Returns information_schema

I run this command python sqlmap.py -u https://acme.com/post.php --data "id=1" --tamper="between,randomcase,space2comment" -v 3 --random-agent --dbs but SQLMap only returns information_schema database.

Is there something wrong (if so, is there anything I can do to circumvent it ?) or the database really only has 1 database ?

Go to Source
Author: maximillian1

Why are CASE-expressions in the list documenting operator precedence?

In the list here:

https://mariadb.com/kb/en/operator-precedence/

case-expressions are put at the same precedence level as BETWEEN, between the NOT operator and the comparison operators.

However, case-expressions always begin with CASE and end with END, and all subexpressions are also delimited by the CASE keywords. They’re like parenthetical expressions, so I don’t understand why case-expressions are on this list.

Is there an SQL expression that would be parsed differently if the case-expression precedence was set higher or lower?

Go to Source
Author: JoL

Have Ubuntu 20 VPS on ex. index.php, but it’s 404 site

I am trying to remove all of this sql and then start again on mariadb, but it still sais warning and errors:

sudo apt remove mysql-server mysql-client mariadb-server mariadb-client
sudo apt auroremove mysql-server mysql-client mariadb-server mariadb-client
sudo apt purge mysql-server mysql-client mariadb-server mariadb-client
sudo apt update
sudo apt upgrade

There are full of errors here. See the four pictures (remove, auroremove, purge and upgrade): https://ibb.co/r6zcTDf, https://ibb.co/Xk7pPLk, https://ibb.co/KhLxPpw and https://ibb.co/khdgmJr.

commando:
/var/www/html/test.com/index.php //<?php phpinfo();

website https://test.com/index.php:
404 Not Found
nginx/1.18.0 (Ubuntu)

/var/log/mysql/error.log

[System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-0ubuntu0.20.04.1)  (Ubuntu).
[System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20-0ubuntu0.20.04.1) starting as process 28629
[System] [MY-011012] [Server] Starting upgrade of data directory.
[System] [MY-013576] [InnoDB] InnoDB initialization has started.
[ERROR] [MY-012936] [InnoDB] Database upgrade cannot be accomplished with innodb_force_recovery > 0
[ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
[ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting
[System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-0ubuntu0.20.04.1)  (Ubuntu).

I have innodb_force_recovery = 1 on /etc/mysql/mysql.conf.d/mysqld.cnf, but that didn’t help me.

Can you help me?

Go to Source
Author: Oldie

How to turn on GTID on MariaDB if already using binary/position replication?

MariaDB 10.3

current master config

[mysqld]
log-bin
server_id=1
binlog-format=mixed
expire_logs_days=10

current slave config

[mysqld]
log-bin
server_id=2
binlog-format=mixed
expire_logs_days=10

I can stop both master and slave if needed for changing replication method.

So questions are:

  1. Do I need to stop replication and application and then execute “CHANGE MASTER TO MASTER_USE_GTID = slave_pos” Is it enough? Should I purge bin logs?
  2. If so how do slave knows about where master server is?
  3. Should I changed binlog-format to “row”?

Go to Source
Author: GarfieldCat

Can InnoDB data at rest encryption be overriden

I am exploring options for distributing a MySQL/MariaDB database with my app. I want to ensure users (even the root user) cannot view or manipulate data in my database/tables. Recent releases of MySQL and MariaDB have data-at-rest capabilities:

MariaDB
https://mariadb.com/kb/en/mariadb/why-encrypt-mariadb-data/

MySQL 5.7.11 comes with InnoDB tablespace encryption
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html

But what is not clear to me is: Can the MySQL root user reset the encryption password on my database/tables to view or manipulate the contents?

Go to Source
Author: TSG