Difference between revisions of "MySQL/MariaDB Setup"

From The Linux Source
Jump to: navigation, search
(Created page with "=== 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...")
 
 
(7 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
===  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
 
1. install packages
 +
ENT 7
 +
# yum install mariadb-server mariadb
 +
BEFORE Ent 7
 
  # yum install mysql-server mysql
 
  # yum install mysql-server mysql
  
 
2. set to start at bootup
 
2. set to start at bootup
 +
ENT 7
 +
# systemctl enable mariadb
 +
BEFORE Ent 7
 
  # chkconfig mysqld on
 
  # chkconfig mysqld on
  
 
3. start mysql server
 
3. start mysql server
 +
ENT 7
 +
# systemctl start mariadb
 +
BEFORE Ent 7
 
  # service mysqld start
 
  # service mysqld start
  
−
4. secure mysql; setup root password (usually same as OS root pw), disallow root remote access, remove test database
+
4. secure mysql; setup root password, disallow root remote access, remove test database
 
  # mysql_secure_installation
 
  # mysql_secure_installation
−
  OR (see Change Passwords below for issues before running)
+
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'
 
  # mysqladmin -u root password 'new-password'
 
  # mysql -p mysql
 
  # mysql -p mysql
Line 28: Line 33:
 
  mysql> delete from user where Host='127.0.0.1';
 
  mysql> delete from user where Host='127.0.0.1';
  
−
6. move database directory to the partition that has space (/home)
+
6. move database directory<br>
 +
6a. stop mysql first
 +
ENT 7
 +
# systemctl stop mariadb
 +
BEFORE Ent 7
 
  # service mysqld stop
 
  # 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
 
  # cd /var/lib ; mv mysql /home/ ; ln -s /home/mysql
 +
6c. start mysql
 +
ENT 7
 +
# systemctl start mariadb
 +
BEFORE Ent 7
 
  # service mysqld start
 
  # 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)*
 

Latest revision as of 13:16, 19 May 2017

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
Retrieved from "http://thelinuxsource.org/index.php?title=MySQL/MariaDB_Setup&oldid=154"