Difference between revisions of "MySQL/MariaDB Setup"
m (→mySQL Setup) |
m (→mySQL Setup) |
||
Line 38: | Line 38: | ||
mysql> delete from user where Host='127.0.0.1'; | mysql> delete from user where Host='127.0.0.1'; | ||
− | 6. move database directory | + | 6. move database directory<br> |
6a. stop mysql first | 6a. stop mysql first | ||
ENT 7 | ENT 7 |
Revision as of 12: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)*