mercoledì 17 agosto 2016

Getting started with MariaDB on CentOS

Some notes about installing and using MariaDB on CentOS 7.

The notes are based on documentation found at https://mariadb.com/kb/en.

Installation

# yum install mariadb-server

Startup

[root@mariadbhost ~]# systemctl start mariadb
[root@mariadbhost ~]# systemctl|awk 'NR==1||/mariadb/'
UNIT             LOAD   ACTIVE SUB       DESCRIPTION
mariadb.service  loaded active running   MariaDB database server
[root@mariadbhost ~]# 

Shutdown

[root@mariadbhost ~]# systemctl stop mariadb
[root@mariadbhost ~]# systemctl|awk 'NR==1||/mariadb/'
UNIT             LOAD   ACTIVE SUB       DESCRIPTION
[root@mariadbhost ~]# ps -efa | grep mariadb
root      2936  2425  0 11:26 pts/0    00:00:00 grep --color=auto mariadb
[root@mariadbhost ~]# 

Automatic Startup

[root@mariadbhost ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@mariadbhost ~]# reboot
...
[root@mariadbhost ~]# systemctl|awk 'NR==1||/mariadb/'
UNIT             LOAD   ACTIVE SUB       DESCRIPTION
mariadb.service  loaded active running   MariaDB database server
[root@mariadbhost ~]# 

Default configuration and log files

[root@mariadbhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@mariadbhost ~]# find /etc/my.cnf.d/
/etc/my.cnf.d/
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/server.cnf
[root@mariadbhost ~]# ls -l /etc/my.cnf.d/
total 12
-rw-r--r--. 1 root root 295 Jun 16 21:08 client.cnf
-rw-r--r--. 1 root root 232 Jun 16 21:08 mysql-clients.cnf
-rw-r--r--. 1 root root 744 Jun 16 21:08 server.cnf
[root@mariadbhost ~]# cat /etc/my.cnf.d/mysql-clients.cnf
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]

[root@mariadbhost ~]# cat /etc/my.cnf.d/client.cnf
#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#


[client]

# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]

[root@mariadbhost ~]# cat /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

[root@mariadbhost ~]# cat /var/run/mariadb/mariadb.pid
2770
[root@mariadbhost ~]# ps -efa | grep -w 2770
mysql     2770  2613  0 11:03 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      2861  2425  0 11:11 pts/0    00:00:00 grep --color=auto -w 2770
[root@mariadbhost ~]# cat /var/log/mariadb/mariadb.log
160817 11:03:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160817 11:03:03 [Note] /usr/libexec/mysqld (mysqld 5.5.50-MariaDB) starting as process 2770 ...
160817 11:03:03 InnoDB: The InnoDB memory heap is disabled
160817 11:03:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160817 11:03:03 InnoDB: Compressed tables use zlib 1.2.7
160817 11:03:03 InnoDB: Using Linux native AIO
160817 11:03:03 InnoDB: Initializing buffer pool, size = 128.0M
160817 11:03:03 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
160817 11:03:03  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
160817 11:03:04  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
160817 11:03:04  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
160817 11:03:04  InnoDB: Waiting for the background threads to start
160817 11:03:05 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-37.9 started; log sequence number 0
160817 11:03:05 [Note] Plugin 'FEEDBACK' is disabled.
160817 11:03:05 [Note] Server socket created on IP: '0.0.0.0'.
160817 11:03:05 [Note] Event Scheduler: Loaded 0 events
160817 11:03:05 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.50-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
[root@mariadbhost ~]# 

Administration

[root@mariadbhost ~]# mysqladmin  status
Uptime: 797  Threads: 1  Questions: 4  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.005
[root@mariadbhost ~]# mysqladmin  processlist
+----+------+-----------+----+---------+------+-------+------------------+----------+
| Id | User | Host      | db | Command | Time | State | Info             | Progress |
+----+------+-----------+----+---------+------+-------+------------------+----------+
| 5  | root | localhost |    | Query   | 0    |       | show processlist | 0.000    |
+----+------+-----------+----+---------+------+-------+------------------+----------+
[root@mariadbhost ~]# mysqladmin ver
mysqladmin  Ver 9.0 Distrib 5.5.50-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Server version  5.5.50-MariaDB
Protocol version 10
Connection  Localhost via UNIX socket
UNIX socket  /var/lib/mysql/mysql.sock
Uptime:   16 min 47 sec

Threads: 1  Questions: 33  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.032
[root@mariadbhost ~]# mysqladmin variables|grep datadir
| datadir                                           | /var/lib/mysql/                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
[root@mariadbhost ~]# mysqladmin 
mysqladmin  Ver 9.0 Distrib 5.5.50-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
The following groups are read: mysqladmin client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

  -c, --count=#       Number of iterations to make. This works with -i
                      (--sleep) only.
  --debug-check       Check memory and open file usage at exit.
  --debug-info        Print some debug info at exit.
  -f, --force         Don't ask for confirmation on drop database; with
                      multiple commands, continue even if an error occurs.
  -C, --compress      Use compression in server/client protocol.
  --character-sets-dir=name 
                      Directory for character set files.
  --default-character-set=name 
                      Set the default character set.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -b, --no-beep       Turn off beep on error.
  -p, --password[=name] 
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -r, --relative      Show difference between current and previous values when
                      used with -i. Currently only works with extended-status.
  -s, --silent        Silently exit if one can't connect to server.
  -S, --socket=name   The socket file to use for connection.
  -i, --sleep=#       Execute commands repeatedly with a sleep between.
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags).
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-verify-server-cert 
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  -u, --user=name     User for login if not current user.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.
  -E, --vertical      Print output vertically. Is similar to --relative, but
                      prints output vertically.
  -w, --wait[=#]      Wait and retry if connection is down.
  --connect-timeout=# 
  --shutdown-timeout=# 
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name Default authentication client-side plugin to use.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
count                             0
debug-check                       FALSE
debug-info                        FALSE
force                             FALSE
compress                          FALSE
character-sets-dir                (No default value)
default-character-set             auto
host                              (No default value)
no-beep                           FALSE
port                              0
relative                          FALSE
socket                            (No default value)
sleep                             0
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
user                              (No default value)
verbose                           FALSE
vertical                          FALSE
connect-timeout                   43200
shutdown-timeout                  3600
plugin-dir                        (No default value)
default-auth                      (No default value)

Where command is a one or more of: (Commands may be shortened)
  create databasename   Create a new database
  debug     Instruct server to write debug information to log
  drop databasename   Delete a database and all its tables
  extended-status         Gives an extended status message from the server
  flush-all-statistics    Flush all statistics tables
  flush-all-status        Flush status and statistics
  flush-client-statistics Flush client statistics
  flush-hosts             Flush all cached hosts
  flush-index-statistics  Flush index statistics
  flush-logs              Flush all logs
  flush-privileges        Reload grant tables (same as reload)
  flush-slow-log          Flush slow query log
  flush-status    Clear status variables
  flush-table-statistics  Clear table statistics
  flush-tables            Flush all tables
  flush-threads           Flush the thread cache
  flush-user-statistics   Flush user statistics
  kill id,id,... Kill mysql threads
  password [new-password] Change old password to new-password in current format
  old-password [new-password] Change old password to new-password in old format
  ping   Check if mysqld is alive
  processlist  Show list of active threads in server
  reload  Reload grant tables
  refresh  Flush all tables and close and open logfiles
  shutdown  Take server down
  status  Gives a short status message from the server
  start-slave  Start slave
  stop-slave  Stop slave
  variables             Prints variables available
  version  Get version info from server
[root@mariadbhost ~]# 

Create an user

[root@mariadbhost ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.50-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test123test!';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT User,Host FROM mysql.user;
+----------+-------------------------+
| User     | Host                    |
+----------+-------------------------+
| root     | 127.0.0.1               |
| root     | ::1                     |
|          | localhost               |
| root     | localhost               |
| testuser | localhost               |
|          | mariadbhost.localdomain |
| root     | mariadbhost.localdomain |
+----------+-------------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> 

Connecting

[root@mariadbhost ~]# mysql -u testuser -p -h localhost test
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.50-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> 

Creating a database

MariaDB [test]> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1007 | Can't create database 'test'; database exists |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> USE test;
Database changed
MariaDB [test]>

Creating tables

MariaDB [test]> CREATE TABLE IF NOT EXISTS books (BookID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Title VARCHAR(100) NOT NULL, SeriesID INT, AuthorID INT); 
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> CREATE TABLE IF NOT EXISTS series (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.05 sec)

Adding records to a table

MariaDB [test]> INSERT INTO books (Title,SeriesID,AuthorID) VALUES ('testbook1',1,1), ('testbook2',1,1), ('testbook3',1,1), ('testbook4',2,2), ('testbook5',2,2), ('testbook6',2,2), ('testbook7',0,1);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

MariaDB [test]> 

Showing tables

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| books          |
| series         |
+----------------+
2 rows in set (0.00 sec)

MariaDB [test]> describe books;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| BookID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| Title    | varchar(100) | NO   |     | NULL    |                |
| SeriesID | int(11)      | YES  |     | NULL    |                |
| AuthorID | int(11)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> select * from books;
+--------+-----------+----------+----------+
| BookID | Title     | SeriesID | AuthorID |
+--------+-----------+----------+----------+
|      1 | testbook1 |        1 |        1 |
|      2 | testbook2 |        1 |        1 |
|      3 | testbook3 |        1 |        1 |
|      4 | testbook4 |        2 |        2 |
|      5 | testbook5 |        2 |        2 |
|      6 | testbook6 |        2 |        2 |
|      7 | testbook7 |        0 |        1 |
+--------+-----------+----------+----------+
7 rows in set (0.00 sec)

MariaDB [test]> 

Modifying records

MariaDB [test]> update books set Title = 'lastbook' where BookID = 7;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from books;
+--------+-----------+----------+----------+
| BookID | Title     | SeriesID | AuthorID |
+--------+-----------+----------+----------+
|      1 | testbook1 |        1 |        1 |
|      2 | testbook2 |        1 |        1 |
|      3 | testbook3 |        1 |        1 |
|      4 | testbook4 |        2 |        2 |
|      5 | testbook5 |        2 |        2 |
|      6 | testbook6 |        2 |        2 |
|      7 | lastbook  |        0 |        1 |
+--------+-----------+----------+----------+
7 rows in set (0.00 sec)

MariaDB [test]>