6 posts tagged with "mariadb"

MariaDB container​

I started the mariadb container like this:

version: "3"
name: "mariadb"
services:
  mariadb:
    image: mariadb:10.10.2
    container_name: mariadb_prod_1
    ports:
      - "3306:3306"
    volumes:
      - ./fs/mariadb/volume:/var/lib/mysql:rw
      - ./fs/mariadb/mysqld:/var/run/mysqld:rw
      - ./fs/mariadb/log:/var/log/mysql:rw
      - ./fs/mariadb/conf.d:/etc/mysql/conf.d:ro
    environment:
      MARIADB_MYSQL_LOCALHOST_USER: 1
      MARIADB_MYSQL_LOCALHOST_GRANTS: "RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR"
      MARIADB_ROOT_PASSWORD: password
    restart: always

Those folders are empty at startup, I used them to make the server data persistent and also because some were required by mariabackup.

Afterwards I created a script which is ran by a cronjob, the script creates a full backup using mariabackup and uploads the backup to S3 or back blaze using restic(restic detects which files are already up and it won't upload them again).

Umami is a lightweight alternative to google analytics.

You will need a db and an user: umami db and umami-user.

create database umami;


CREATE USER 'umami-user'@'%' IDENTIFIED BY 'umami-pass';

GRANT ALL PRIVILEGES ON `umami`.* TO 'umami-user'@'%';

mysql -uroot -p umami < schema.sql

You can do this using these queries:

SET GLOBAL general_log_file='mariadb.log';
SET GLOBAL general_log_file='/var/log/mariadb/query.log';
SHOW VARIABLES LIKE '%general_log_file%';
SET GLOBAL general_log=1;

And then access your logs like this:

tail -f /var/log/mariadb/query.log

Resources:

• show-variables
• general-query-log

Check this

This is an example of how to connect an internal pod to an external db that is not inside the cluster. In the pod configuration you will need to connect to the brandName-database service from below.

apiVersion: v1
kind: Service
metadata:
  name: brandName-database
spec:
  ports:
    - name: brandName-database-external
      protocol: TCP
      port: 3306
      targetPort: 3306

---
kind: Endpoints
apiVersion: v1
metadata:
  name: brandName-database
subsets:
  - addresses:
      - ip: 192.168.100.52
    ports:
      - port: 3306
        name: brandName-database-external

First we need to install everything, here are the steps:

These steps are for centos First step is to install percona-release:

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Enable the package that contains the pt-query-digest tool:

percona-release enable tools

Next install percona-toolkit:

yum install percona-toolkit

How to use pt-query-digest:

pt-query-digest /../mysql_slow/slow-query.log

Set interval:

pt-query-digest --since '2019-02-07 08:09:00' --until '2019-02-07 08:02:03' /../mysql_slow/slow-query.log > output.out

Filter host, database or other things:

# Ignore queries from a specific database: ($event->{db} || "") && $event->{db} ne "ignored_schema"
# Ignore queries are not on a database:" ($event->{db} || "") , Ex PING
#Ignore queries that came from a specific IP address: ($event->{host} || "") && $event->{host} ne "192.168.2.1"
pt-query-digest --filter  '($event->{db} || "") && $event->{db} && $event->{db} ne "ignored_schema" && $event->{cmd} ne "Admin" && ($event->{host} || "") && $event->{host} ne "192.168.2.1" && $event->{host} ne "192.168.0.1"' /../mysql_slow/slow-query.log > output.out

To see all components of event run this command:

# This will print the $event variable with all it's componets:

pt-query-digest pdbx5-slow-query.log  --filter  '($event->{db} || "") && $event->{db} && $event->{db} ne "ignored_database" && print Dumper $event'  --no-report  --sample 1

Uninstall:

yum remove percona-toolkit

percona-release disable tools

yum remove percona-release

Show indexes for a specific table:

SHOW INDEXES FROM TABLE_NAME;

Note: INDEX_TYPE ='HASH' is slower than INDEX_TYPE ='BTREE'

Show indexes for tables from a specific database or all databases:

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
[WHERE TABLE_SCHEMA = 'database_name'];

Add new index for a specific table:

ALTER TABLE `table_name` ADD INDEX `index_name` USING BTREE(`column1`, `column2`);

Rename table:

ALTER TABLE current_table_name RENAME new_table_name;

Show global system variables:

SHOW GLOBAL variables;

Set global variable:

SET GLOBAL long_query_time = 20;

This will have effect only if the field is dynamic, check the documentaion for each variable.

Show global status:

SHOW GLOBAL status;

Get size total size of all MEMORY tables:

SELECT (data_length+index_length)/(1024*1024*1024) table_size,
       TABLE_NAME,
       TABLE_SCHEMA
FROM information_schema.tables
WHERE ENGINE LIKE 'MEMORY'
ORDER BY table_size DESC;