MySQL/MariaDB Setup

Overview

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

mySQL Setup

1. install packages

# yum install mysql-server mysql

2. set to start at bootup

# chkconfig mysqld on

3. start mysql server

# service mysqld start

4. secure mysql; setup root password (usually same as OS root pw), disallow root remote access, remove test database

# mysql_secure_installation
OR (see Change Passwords below 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 to the partition that has space (/home)

# service mysqld stop
# cd /var/lib ; mv mysql /home/ ; ln -s /home/mysql
# 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)*