Difference between revisions of "MySQL/MariaDB Setup"

Revision as of 11:47, 19 May 2017

Overview

mySQL is depreciated in favor of the mariaDB replacement in newer OS version (i.e. >=7). mariaDB is created by the previous mySQL team, but the mySQL name is now owned by Oracle Corporation (so they had to change it). The new server package (yum install) is now mariadb, but the client/command line names are still mysql.

mySQL 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)*