raspberry pi - mariadb install and change default data dir

* mariadb

sudo apt-get install mariadb-server

mysql -u root -p
 "mypassword"

CREATE DATABASE IF NOT EXISTS new_database;
SELECT database();
DROP DATABASE new_database;
DROP DATABASE IF EXISTS new_database;


** check
mysql -u root -p

: Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
SELECT database();

: +------------+
: | database() |
: +------------+
: | NULL       |
: +------------+
: 1 row in set (0.01 sec)

select @@datadir;
: +-----------------+
: | @@datadir       |
: +-----------------+
: | /var/lib/mysql/ |
: +-----------------+
: 1 row in set (0.00 sec)

exit
: Bye


** change default data directory


*** stop service
- stop service
sudo service mysql stop

> check
sudo service mysql status

- directory move

> permission check
 --> /var/lib/mysql
   drwxr-xr-x  4 mysql   mysql   4.0K  4월  3 15:44 mysql

ls -alh /var/lib/mysql/


*** move default directory with tar-mode
mkdir $new_volume/mysql

cd $new_volume/mysql
mkidr -p $new_volume/log
sudo cp -apR /var/log/mysql /$new_volume/log/


*** copy with rsync

ref.
>> https://www.digitalocean.com/community/tutorials/how-to-change-a-mariadb-data-directory-to-a-new-location-on-centos-7


*** modify my.cnf
sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf-dist
sudo vi /etc/mysql/my.cnf


-- my.cnf
#+title:
#+begin_src conf
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# modified for utf8 client
default-character-set=utf8  # <-- added with modified


[mysqld]
...
socket          = /var/run/mysqld/mysqld.sock # leave it
# datadir               = /var/lib/mysql # modified
datadir         = /NEW_VOLUME_ROOT/mysql # modified
tmpdir          = /tmp # leave it
...
# -- modify
bind-address          = 127.0.0.1
# bind-address            = 0.0.0.0

# -- modify
log_error = /NEW_VOLUME_ROOT/log/mysql/error.log
#log_bin                        = /var/log/mysql/mysql-bin.log
..
# expire_logs_days      = 10
expire_logs_days        = 2 # modified
# max_binlog_size         = 100M
max_binlog_size         = 10M # modified
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
# added following 2 line for utf8 -- modified
init_connect=SET collation=utf8_general_ci
init_connect=SET NAMES utf8
# -- default-character-set=utf8 # error
# character-set-server=utf8 # new DB create with option character-set
# collation-server=utf8_general_ci


[client]
port=3306
# unmodified:w

# socket=/mnt/volume-nyc1-01/mysql/mysql.sock


!includedir /etc/my.cnf.d

#+end_src


*** test connect

tail -f /NEW_VOLUME_ROOT/log/mysql/error.log

sudo service mysql start

sudo service mysql status
: ● mysql.service - LSB: Start and stop the mysql database server daemon
:    Loaded: loaded (/etc/init.d/mysql)
:    Active: active (running) since 월 2017-04-03 16:39:16 KST; 33s ago
:   Process: 28500 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
:   Process: 29028 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
:    CGroup: /system.slice/mysql.service
:            ├─29053 /bin/bash /usr/bin/mysqld_safe
:            ├─29206 /usr/sbin/mysqld --basedir=/usr --datadir=/NEW_VOLUME_ROOT/mysql --plugin-dir=/usr/li...
:            └─29207 logger -t mysqld -p daemon.error


- check local connect
mysql -u root -p

\quit


- check host connect
mysql -h MY_MARIADB_HOST -u root -p
: Enter password:
: ERROR 1130 (HY000): Host 'pii.local' is not allowed to connect to this MariaDB server


*** create table & user

sudo netstat -tap | grep mysql

mariadb-client-core-10.0

- connect
mysql -u root -p
or
mysql -h server_ip -P 3306 -u root -p

- root: create user
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
flush privileges;

: CREATE USER 'myuser'@'192.168.0.3';
: CREATE USER 'myuser'@'%';
: CREATE USER myuser@test IDENTIFIED BY 'mypassword';
: mysql -h my_server_ip -u myuser -p

-- other options
CREATE USER foo
  WITH MAX_QUERIES_PER_HOUR 10
  MAX_UPDATES_PER_HOUR 20
  MAX_CONNECTIONS_PER_HOUR 30
  MAX_USER_CONNECTIONS 40;

-- check grant or privileges

 > commit;
flush privileges;
status;

 > if super priviledge need - with grant option
#+begin_src sql
GRANT ALL PRIVILEGES ON journaldev.* TO 'USER_ME'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON journaldev.* TO 'USER_ME'@localhost WITH GRANT OPTION;
#+end_src


*** client install

$ mysql-workbench
The following packages will be REMOVED:
  mariadb-client-core-10.0

The following NEW packages will be installed:

  libaec0 libarmadillo6 libarpack2 libcrypto++6 libctemplate3
  libdap17v5 libdapclient6v5 libepsilon1 libfreexl1 libgdal20
  libgeos-3.5.0 libgeos-c1v5 libgeotiff2 libhdf4-0-alt libhdf5-10
  libkmlbase1 libkmldom1 libkmlengine1 libminizip1 libmysqlclient20
  libmysqlcppconn7v5 libnetcdf11 libodbc1 libogdi3.2 libpq5 libproj9
  libqhull7 libspatialite7 libsuperlu4 libsz2 liburiparser1
  libvsqlitepp3v5 libxerces-c3.1 libzip4 mysql-client mysql-client-5.7
  mysql-client-core-5.7 mysql-utilities mysql-workbench
  mysql-workbench-data odbcinst odbcinst1debian2 proj-bin proj-data
  python-cffi-backend python-cryptography python-enum34 python-idna
  python-ipaddress python-mysql.connector python-paramiko
  python-pyasn1 python-pyodbc python-pysqlite2 ttf-bitstream-vera


- ubuntu client (only)
sudo apt-get install mariadb-client-core-10.0

※ mysql-workbench will automatically install
mysql-client, mysql-client-core then
remove mariadb-clinet

so, reinstall mariadb-clinet-core

sudo apt-get install mariadb-client-core-10.0

then clinet prompt will change from "mysql" to "MariaDB"
MariaDB [(none)]>


** remove mysql start service
https://mariadb.com/kb/en/mariadb/installing-system-tables-mysql_install_db/ https://www.digitalocean.com/community/tutorials/how-to-change-a-mariadb-data-directory-to-a-new-location-on-centos-7


** Prevent MySQL from starting on boot up
sudo update-rc.d -f mysql remove

then
  You would then start mysql manually using

sudo service mysql start


** referance
How To Change a MariaDB Data Directory to a New Location on CentOS 7
https://www.digitalocean.com/community/tutorials/how-to-change-a-mariadb-data-directory-to-a-new-location-on-centos-7


sudo service mysql status

: ● mysql.service - LSB: Start and stop the mysql database server daemon
:    Loaded: loaded (/etc/init.d/mysql)
:    Active: inactive (dead) since 월 2017-04-03 15:54:30 KST; 58s ago
:   Process: 28500 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)