Howto mariadb server

install mariadb on archlinux

install the package:

pacman -S mariadb

create the datadir:

mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

secure your mariadb installation:

mysql_secure_installation

start and enable mariadb service:

systemctl start mariadb
systemctl enable mariadb

connect to your database server:

mysql -u root -h localhost -p

mariadb administration

set this variables on your shell or script:

ROOT_PASS="mysqlrootpassword"
DBUSER=mydbuser
DBNAME=mydbname
DBPASS=mydbpassword

create the database:

mysql -uroot -p${ROOT_PASS} -e "CREATE DATABASE ${DBNAME};"

create the user and privileges to access from localhost and remote:

mysql -uroot -p${ROOT_PASS} -e "GRANT ALL PRIVILEGES ON ${DBNAME}.* TO '${DBUSER}'@'localhost' IDENTIFIED BY '${DBPASS}';"
mysql -uroot -p${ROOT_PASS} -e "GRANT ALL PRIVILEGES ON ${DBNAME}.* TO '${DBUSER}'@'%' IDENTIFIED BY '${DBPASS}';"

connect to mariadb with user:

mysql -u ${DBUSER} -p${DBPASS}

create user:

create user username@localhost IDENTIFIED BY 'yourpassword';

create database;

create database mydbname;

create user privileges for username on mydbname:

grant select,insert,update,delete mydbname.* TO 'username'@'localhost';

give all privileges for username on mydbname:

grant all on mydbname.* TO 'usuario'@'localhost';

remove or delete user:

drop user username@localhost;

list users:

select User,Host from mysql.user;

list databases:

show databases;

select specific database:

use mydbname;

list database tables:

show tables;

describe database table

describe tablename;

show content inside tables:

select * from tablename;

revoke user privileges:

revoke all on mydbname.* FROM 'username'@'localhost';

import data to mydbname from existing sql file:

mysql -u username -pmypassword mydbname < backup.sql

backup data from existing database:

mysqldump -u username -pmypassword mydbname > backup-$(date +%F).sql