MariaDB

installation

check out the possible configuration options

# cmake . -LH
and do it - unfortunately SSL support is disabled by default - enable it!
# export CFLAGS="-Os -pipe -fomit-frame-pointer -march=native -fno-stack-protector"
# cmake . -DCMAKE_INSTALL_PREFIX=/opt/mariadb -DWITH_SSL=yes
# make && make test
# make install
verify the existing configuration in /opt/mariadb/my.cnf

prior first startup

# useradd mysql
# groupadd mysql
# chown -R mysql:mysql /opt/mariadb
# scripts/mysql_install_db --user=mysql
and start it up
# /opt/mariadb/bin/mysqld_safe --user=mysql &

sql

most priviledged administration

$ mysql -u root -p
basic tasks
MariaDB [(none)]> show databases;
MariaDB [(none)]> use mysql;
MariaDB [mysql]> show table status;
MariaDB [mysql]> show tables;
MariaDB [mysql]> select * from user;
MariaDb [mysql]> select host,user, ssl_type,ssl_cipher from user;
MariaDB [mysql]> create user armin@localhost identified by '*************';
MariaDB [mysql]> grant all on mysql.* to armin@localhost;
SET PASSWORD FOR root@localhost = PASSWORD(MyNewPassword); 
by LinuxGuru

operating

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password ***********
./bin/mysqladmin -u root -h <hostname> password *******

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

You can start the MariaDB daemon with:
cd . ; ./bin/mysqld_safe --datadir=./data

You can test the MariaDB daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
use secure installation
# bin/mysql_secure_installation 
Set root password? [Y/n] Y 
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
the default CentOS7 root account after installation is empty

defrag

$ mysqlcheck -o --all-databases -u mysql -p
Enter password:
Allows to defrag/optimize all tables on a server - much easier than select every table and get the SQL OPTIMIZE running

Master-Slave Replication

Reference MariaDB KB Entry
this samples uses root for all operations

Master

SQL> create user 'root'@'%' identified by ***************;
SQL> grant all on *.* to 'root'@'%' with grant option;
SQL> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
SQL> flush privileges;
SQL> FLUSH TABLES WITH READ LOCK;
SQL> SHOW MASTER STATUS;
mysql$ mysqldump -p  --databases mysql  > backup-mysql.sql
SQL>UNLOCK TABLES;

SLAVE

mysql$ mysql -u root -p mysql < backup-mysql.sql
SQL> CHANGE MASTER TO MASTER_HOST=sql01.ahammer.ch, 
  MASTER_USER=root, MASTER_PASSWORD=******,
  MASTER_PORT=3306, MASTER_LOG_FILE=master1-bin.000001,
  MASTER_LOG_POS=1195, MASTER_CONNECT_RETRY=10; 
SQL> start slave;  

DEBUG

SQL> flush logs;
SQL> show binary logs;
SQL> PURGE BINARY LOGS TO master1-bin.000002;

links

mariadb.org - mysqldump