MySQL/MariaDB Setup

1. install packages

ENT 7
# yum install mariadb-server mariadb
BEFORE Ent 7
# yum install mysql-server mysql

2. set to start at bootup

ENT 7
# systemctl enable mariadb
BEFORE Ent 7
# chkconfig mysqld on

3. start mysql server

ENT 7
# systemctl start mariadb
BEFORE Ent 7
# service mysqld start

4. secure mysql; setup root password, disallow root remote access, remove test database

# mysql_secure_installation
answer Y to all of the questions and set root password  (sometimes same as OS root pw - NOT A GOOD IDEA!)
OR (see Changing Passwords for issues before running)
# mysqladmin -u root password 'new-password'
# mysql -p mysql
mysql> delete from user where User!='root';
mysql> delete from user where Host!='localhost';
mysql> delete from db;
mysql> drop database test;

5. remove additional/unneeded root acct

# mysql -p
mysql> use mysql;
mysql> delete from user where Host='127.0.0.1';

6. move database directory
6a. stop mysql first

ENT 7
# systemctl stop mariadb
BEFORE Ent 7
# service mysqld stop

6b. move db directory to a partition that has space (/home or the partition which has space allocated for applications)

# cd /var/lib ; mv mysql /home/ ; ln -s /home/mysql

6c. start mysql

ENT 7
# systemctl start mariadb
BEFORE Ent 7
# service mysqld start

mySQL Quick Reference

• List Databases*

 mysql> show databases;

• List Tables*

 mysql> show tables;

• Change Passwords*

 # mysqladmin -u root password 'new-password'

Note: the mysqladmin command should be run from a shell script, so as to not have this critical password exposed in the command history - make sure to delete the shell script after running

• Delete Database*

 mysql> drop database test;

• Creating a new DB and assigning permissions*

Command line/scripted

 # mysqladmin -p create wikidb
 # echo "grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';" | mysql -p

Note: the echo command should be run from a shell script, so as to not have a critical password exposed in the command history - make sure to delete the shell script after running

mySQL command line - note: this is added to the mysql command history (not a good idea to leave the password exposed):

 # mysql -p
 mysql> create database wikidb;
 mysql> grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';

• Delete From Table*
• Table Structure*
• View Data (Some Query Examples)*