Knowledge is Power
A wise man has great power, and a
man of knowledge increases strength
(Proverbs 24:5)
Database Server Setup using MySQL
on CentOS 6.3
Jeong Chul
tland12.wordpress.com
www.youtube.com/user/tland12
Computer Science
ITC and RUPP in Cambodia
Database Server Setup using MySQL
on CentOS 6.3
Part 1 Developing Database
Step 1
Step 2
Step 3
Step 4
Step 5
Basic database terminology
MySQL packages
MySQL Configuration
Working with MySQL databases
Displaying MySQL databases
Part 2 Database Administration
Step 6 Changes of tables and records
Step 7 User Management
Step 8 Backup MySQL database
Step 9 Checking and fixing database
Part 3 MySQL GUI tools
Step10 GUI tools - phpMyAdmin, MySQL Workbench
Step 1 Basic database terminology
MySQL creates, configures, and communicates with databases.
A database is an organized collection of data.
A database will consist of tables.
Tables contain records (sometimes called rows), and records contain
fields (sometimes called columns) :
Field 1
Field 2
Field 3
Field name
Name
Age
Favorite Color
Record 1
Jeong Chul
30
Green
Record 2
Kim Chan
40
Blue
Record 3
Sophon Pho
22
Black
Step 2 MySQL packages
1.MySQL packages (www.mysql.com)
# rpm qa | grep mysql
mysql-devel-5.1.67-1.el6_3.i686
mysql-libs-5.1.67-1.el6_3.i686
mysql-5.1.67-1.el6_3.i686
mysql-connector-odbc-5.1.5r1144-7.el6.i686
mysql-server-5.1.67-1.el6_3.i686
2.Install packages
# yum install mysql-server mysql mysql-devel mysql-libs mysql-connectorodbc
3. MySQL files and directory
/etc/my.cnf
/var/lib/mysql
/var/log/mysqld.log
/etc/rc.d/init.d/mysqld
/usr/bin/mysql*
//configuration file
//data dir
//log file
//starting script file
//mysql binary files
Step 3 MySQL Configuration
1. MySQL user/group account (/etc/passwd)
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
2. Choosing options
# vi /etc/my.cnf
# /usr/libexec/mysqld verbose help | less
3. Service startup
# service mysqld start
# chkconfig mysqld on
4. Adding administrative users
# mysqladmin u root password mypassword
5. Process and port checking
# ps ef | grep mysqld
# netstat nat | grep 3306
6. Checking that MySQL is working
#
mysqladmin u root p version proc
Step 4 Working with MySQL Databases (1)
1. Starting mysql command
# mysql u root p
mysql> status
mysql> select version(), curdate();
2. Creating a database
a. mysql> create database data;
b. # mysqladmin u root p create data
mysql> show databases;
mysql> use data;
mysql> drop database data;
3. Creating table
mysql> create table name (
-> id
int
->firstname
varchar(20)
->lastname
varchar(20)
->city
varchar(20)
->cur_date
timestamp(8)
->zipcode
varchar(10)
-> primary key (id)
);
not null auto_increment,
not null,
not null,
not null,
not null,
not null,
Step 4 Working with MySQL Databases (2)
4. Adding data to mysql database table
a. Manually entering data
mysql> use data;
mysql> show tables;
mysql> describe name; or show create table name;
mysql> insert into name values (,Tom,Win,Phnom Penh,
->now(),11111);
mysql> select * from name;
b. Loading data from a file
Create the file using Linux text editor
Each record must be on its own line
Separate each column by a Tab character
Any blank lines result in blank lines in the database table
mysql> load data local infile /root/name.txt into table name;
mysql> select * from name;
mysql> source /root/sakila/sakila-schema.sql;
mysql> source /root/sakila/sakila-data.sql;
Step 5 Displaying MySQL databases
1. Displaying all or selected records
mysql> select * from name;
mysql>select * from name where lastname = Jeong;
mysql>select * from name where firstname = Chul or firstname = Tom;
mysql> select * from name where firstname = Chul and lastname = Tom;
2. Displaying selected columns
mysql> select firstname,lastname,zipcode from name;
mysql> select firstname,lastname,city from name where firstname = Chul;
3. Sorting data
mysql> select * from name order by lastname;
mysql> select * from name order by city;
Step 6 Changes of tables and records
1.Altering the structure of mysql tables
a. Adding column
mysql> alter table name add curdate timestamp;
b. Dropping column
mysql> alter table name drop column curdate;
c. Changing column
mysql> alter table name change city town varchar(20);
d. Changing data type
mysql> alter table name change zipcode zipcode interger;
2. Updating and deleting mysql records
a. Updating records
mysql> update name set city = New York where firstname = Chul;
b. Deleting records
mysql> delete from name where firstname = Chul;
3. Deleting table
mysql> drop table name;
Step 7 User Management (1)
1. Creating user
mysql> create user tland'@'localhost' identified by mypassword';
2. Granting access
a. Full privileges
mysql> grant all privileges on data.* to tland@localhost;
mysql>select host,user,password from mysql.user where user = tland;
b. Limited privileges
mysql> grant delete,insert,select,update on data.* to tland@localhost;
mysql> flush privileges;
mysql> show grants for tland@localhost;
3. Changing user passwords
mysql> set password for tland@localhost = password(Newpassword);
mysql> grant usage on data.* to tland@localhost identified by newpass;
4. How to access Database
# mysql u tland p data
// data = database name to access
Step 7 User Management (2)
5. Revoking access
a. Revoking specific privileges
mysql> revoke update,delete on data.* from tland@localhost; .
b.Revoking full privileges
mysql> revoke all privileges on data.* from tland@localhost;
c. Checking the result
mysql> show grants for tland@localhost;
6. Rename user
mysql>rename user tland@localhost to chul@localhost;
mysql>select user from mysql.user where user = chul;
7. Deleting user
mysql> drop user chul@localhost;
Step 8 Backup MySQL database (1)
1.Binary Versus Text backup
Binary a copy of the files in which database contents are stored
Text a dump of database contents into text files
2. Binary backup using mysqlhotcopy
# mysqlhotcopy u root p password data /usr/local/src/
# ls l /usr/local/src/data
3.Text backup
1) Back up from the Command Line using mysqldump
a. Backup one database
# mysqldump -u root -p data > data.sql
b. Backup certain tables
# mysqldump -u root -p data name address > data_backup.sql
Step 8 Backup MySQL database (2)
c. Backup more than one database at once
# mysqldump -u root -p --databases data data2 > 2database_backup.sql
d.Back up all the databases in the server at one time
# mysqldump -u root -p --all-databases > allbackup.sql
2) Back up MySQL Database with Compress
# mysqldump -u root -p data | gzip -9 > databackup.sql.gz
3) Remote server backup
# mysqldump u root p h 192.168.80.2 data > data.sql
4. Restoring MySQL Database
# mysql -u root -p data < data.sql
Step 9 Checking and fixing databases
To use to check and repair corrupted databases
1.Table files in /var/lib/mysql/data
.frm : contains the definition or the table
.MYI: contains the tables index
.MYD: contains the tables data
2. Stop mysql daemon
# service mysqld stop
3. Check all database tables at once
# myisamchk /var/lib/mysql/data/name.MYI
# myisamchk /var/lib/mysql/*/*.MYI
4. Fix a corrupted database
# myisamchk r /var/lib/mysql/data/name.MYI
# myisamchk o /var/lib/mysql/data/name.MYI
//slower, older
Step 10 GUI tools for MySQL (1)
1.phpMyAdmin on Linux
a. web-based tool written in PHP to manage the MySQL database
b. phpMyAdmin Pre requisites
Php 5 or above, MySQL 5 or above, Apache installed & running
c. Installation of phpMyAdmin
# wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.31.el6.rf.i686.rpm
(32bit)
# wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.31.el6.rf.x86_64.rpm
(64bit)
# yum install phpMyAdmin
d. phpMyAdmin configuration
Blowfish secret
www.question-defense.com/tools/phpmyadmin-blowfish-secret-generator
# vi /usr/share/phpMyAdmin/config.inc.php
$cfg['blowfish_secret'] = 't}T*_xZaN8z}5S*UTd}7UPByGu]}=1UT8|gv##zj=vNy|6';
Access testing
http://localhost/phpMyAdmin/
Step 10 GUI tools for MySQL (2)
2.MySQL Workbench on Windows
GUI tool supported by Oracle
SQL development, Data modeling, Server administration
www.mysql.com/downloads
mysql-workbench-gpl-5.2.47-win32
Need to permit remote connection on MySQL
mysql>grant all privileges on *.* to 'root'@'%' identified by pass;
Next Videos Topics Short course
VPN service using OpenVPN on CentOS 6.3
Linux User Management
CMS - WordPress setup on CentOS 6.3
CMS - Joomla setup on CentOS 6.3
Linux Log system
Linux Firewall using IPTABLES
Oracle installation on CentOS 6.3
How to hack Wireless WEP on Backtrack5
How to control Linux Partitions
How to install Ubuntu on VMware Workstation 9
DNS security using Bind
VNC server setup
Google hacking
Next Videos Topics Full course
CCNA course on GNS3
CCNP course on GNS3
CCNA Security course on GNS3
Redhat Enterprise Security course
Backtrack 5 course
MySQL course
More security courses
Database Server Setup using MySQL
on CentOS 6.3
Thank you & God bless you
tland12.wordpress.com
www.youtube.com/user/tland12
