Skip to content

Instantly share code, notes, and snippets.

@gnat

gnat/mariadb_mysql_portable_no_install.md

Last active April 9, 2024 06:15
Show Gist options
  • Save gnat/208ea8036b401d73f0422ae649a5f165 to your computer and use it in GitHub Desktop.
Save gnat/208ea8036b401d73f0422ae649a5f165 to your computer and use it in GitHub Desktop.
Download ZIP
Portable Database - Standalone MariaDB and MySQL zero install.
Raw
mariadb_mysql_portable_no_install.md

Database Standalone Quickstart

🐬 = MySQL 8
🦭 = MariaDB

Why?

  • Localize your database into one single folder of your choosing.
  • No sudo / root requirement.
  • Run multiple database at the same time on the same machine- Full bare metal performance.
  • These are great starter databases moving to "slow single machine but horizontal scale": CockroachDB, ScyllaDB, etc.

Download and Setup

  • 🐬 Download + extract to database: https://dev.mysql.com/downloads/mysql/
  • 🦭 Download + extract to database: https://mariadb.org/download
  • Open terminal, cd database. Create data folder: mkdir -p data
  • 🐬 Create system tables: bin/mysqld --no-defaults --user=$USER --initialize Save the temporary password!
  • 🦭 Create system tables: scripts/mysql_install_db --datadir=./data --basedir=./ --user=$USER

Configure for Performance

🐬 Create ./database/my.cnf and add:

[mysqld]
max_connections = 8000
innodb_buffer_pool_size = 30G
innodb_log_file_size = 30G
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 2

🦭 Create ./database/my.cnf and add:

[mysqld]
data=./data
key_buffer_size=256M
max_connections = 8000
innodb_buffer_pool_size = 30G
innodb_log_file_size = 30G
innodb_log_buffer_size = 512M

Run Server

  • 🐬 Run: bin/mysqld --defaults-file="./my.cnf"
  • 🦭 Run: bin/mariadbd --defaults-file="./my.cnf" --basedir=./ --user=$USER --bind-address=127.0.0.1 --socket=/tmp/mariadb

Run Management Tool

  • 🐬 Run: bin/mysql --user=root -p

Maintenence and Health

  • 📦 Backup
    • 🐬 xtrabackup --backup --defaults-file="./my.cnf" --target-dir="./data_backup_$(date +%Y%m%d_%H%M%S)" --no-server-version-check --host=127.0.0.1 -u root -p
      • 🐬 Use xtrabackup for MySQL 8 and Percona. Does not block database.
    • 🦭 ./bin/mariabackup --defaults-file="./my.cnf" --backup --target-dir="./data_backup_$(date +%Y%m%d_%H%M%S)" --host=127.0.0.1 -u root -p
      • 🦭 Mariabackup is a fork of xtrabackup, only supported for MariaDB. Does not block database.
    • S3 or cluster: https://github.com/wal-g/wal-g
  • 🚚 Table Migrations: MoveTables in Vitess, or https://github.com/Shopify/ghostferry for vanilla MySQL.
  • ⭐ Restore: It's just your ./data directory.
    • 🦭 Prepare: ./bin/mariabackup --prepare --target-dir=... Afterwards, it's a fully functional data directory... stop server, replace ./data, start server.
  • ⏫ Upgrades: Just copy your config and ./data directory into a new MySQL.
  • 🚚 Export (too slow for backup, but useful to migrate to another database): ./bin/mysqldump test -h 127.0.0.1 > backup.sql
  • ⛓️ Replication: https://youtu.be/s4oYWBGy_FE
    • Logical (binary log) replication is ideal. Allows you to do upgrades, keeps bandwidth requirements low.

Upgrade Server

  • Download new version: https://dev.mysql.com/downloads/mysql/
  • killall mysqld Check to ensure server has shut down: pgrep mysqld
  • Keep ./data and ./my.cnf. Replace all other files.
  • Start up mysqld again, auto-upgrade sould start and report when completed.

Notes

  • Your tables will be stored in datadir= (./data in the above).
  • basedir= is the extraction directory.
  • mysqld will be ran as $USER

Migrations

User Accounts

  • 🐬 Change the password: ALTER USER 'root'@'localhost' identified by 'password';
  • 🦭 Set default character set: set character_set_server = utf8mb4; 🐬 Will already be utf8mb4
    • Make sure your database and tables are all set to the utf8mb4 character set and collation or you will have garbled data when working with unicode! Emoji's, etc. will break!
  • 🦭 Change the password: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('cleartext password');
  • Create a new user: CREATE USER 'guy'@'localhost' IDENTIFIED BY 'password';
  • Grant admin privileges: GRANT ALL PRIVILEGES ON * . * TO 'guy'@'localhost'; then FLUSH PRIVILEGES;
  • Check out other options, example: ./bin/mysqld --help --verbose | grep "bind"

Your first database and tables.

  • Create a database: CREATE DATABASE test;
  • Show databases: show databases;
  • Use database: use test;
  • Create table:
CREATE TABLE users (id BIGINT UNSIGNED PRIMARY KEY
, name VARCHAR(255)
, email VARCHAR(255)
, created_at DATETIME
, updated_at DATETIME);
  • Show table schema: SHOW CREATE TABLE test;

Remaining compatible with SQLite and CockroachDB:

  • Use BIGINT or UUID for primary keys and indexes (BIGINT for Snowflake ID UUID for uuid or ulid).
  • Use TEXT or VARCHAR(255) (indexes must be VARCHAR(255) in MySQL)
  • Use TINYINT instead of BOOL or tiny ENUM (Ex: on/off/disabled)
  • Use SMALLINT instead of ENUM
  • Use JSON
  • Use VARCHAR(255) for Dates/Times.
  • Note VARCHAR(255) is the same as VARCHAR(100) in MySQL because VARCHAR stores 1 byte length.
    • The only advantage to using a lower number is a length check.

Debugging

SHOW PROCESSLIST;

Product Notes

  • https://github.com/shlomi-noach/awesome-mysql
  • MariaDB Xpand (Clustrix) is basically MariaDB done in the CockroachDB way. Like Vitess or Citus.
    • MariaDB Xpand is rebranded Clustrix, brought into MariaDB core.
    • Backups are done the same as CockroachDB. Endpoint is given to all clients, and they push their pieces to it (SFTP / FTP)
    • Vitess doesn't have indexes or transactions outside the local shard.
  • Vitess may not support MariaDB in the future.. may support Postgres: https://github.com/shlomi-noach/awesome-mysql#sharding
    • Probably not a huge issue with MariaDB Xpand.
  • Galera Cluster (and Percona XtraDB, Maria Galera Cluster) doesn’t scale writes. Write all, read once. Shotgun architecture- Generally you dont want to scale past 5 nodes because diminishing returns. No raft or pax can cause deadlocks. Multi-master in the most basic sense. Should not be used: https://github.com/shlomi-noach/awesome-mysql#ha
    • MariaDB Xpand is designed to obsolete Galera Cluster.
  • MariaDB MaxScale Load balancing forward proxy, like ProxySQL or MySQL Router
    • Used on top of MariaDB Xpand
    • Forward proxy not always required. MariaDB / MySQL is "thread per connection" vs Postgres "process per connection".
  • XtraDB is N/A. Was an alternative engine vs InnoDB but was rolled into InnoDB

Why choose MariaDB over MySQL?

  • RETURNING clause.
  • uuid type.

Why choose MySQL over MariaDB?

  • Ops experience tends to be smoother.

Vitess

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment