News and events

CodeIgniter RESTful Web Services

Representational state transfer (REST) or RESTful web services provide a way to exchange data between applications or systems on the Internet. RESTful web service also refers as RESTful API, uses HTTP request to GET, PUT, POST and DELETE data across platforms. In present days, RESTful API is an essential component of the web application.

When the CodeIgniter application requires communicating with another application, RESTful API is needed to integrate into CodeIgniter. Using RESTful API in CodeIgniter, you can exchange data between different applications or platform. This tutorial shows RESTful server implementation for CodeIgniter and you will learn how to create RESTful web services in CodeIgniter.

To demonstrate CodeIgniter REST API, we will implement CRUD operations in CodeIgniter web service. The following tasks will be performed to create a simple REST API in CodeIgniter.

  • Setup RESTful library for CodeIgniter.
  • Create API method to fetch the user information via GET request.
  • Create API methods to add, edit, and delete user information via POST, PUT, and DELETE request.
  • Interact with the CodeIgniter Rest API using PHP cURL.
  • Test HTTP calls with Google Chrome Postman extension.

Before you get started, take a look the files structure of CodeIgniter REST API.

Create Database Tables

To store user’s information, a table needs to be created in the database. The following SQL creates a users table with some basic fields in MySQL database.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The following SQL creates a keys table to store REST API key for authentication.
CREATE TABLE `keys` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `key` varchar(40) NOT NULL,
 `level` int(2) NOT NULL,
 `ignore_limits` tinyint(1) NOT NULL DEFAULT '0',
 `is_private_key` tinyint(1) NOT NULL DEFAULT '0',
 `ip_addresses` text,
 `date_created` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now insert an API key in keys table, which will be used on API call.

INSERT INTO `keys` (`id`, `user_id`, `key`, `level`, `ignore_limits`, `is_private_key`, `ip_addresses`, `date_created`) VALUES
(1, 0, 'CODEX@123', 0, 0, 0, NULL, '2017-10-12 13:34:33');

CodeIgniter REST Controller Setup

We will use CodeIgniter REST_Controller to build RESTful web services in CodeIgniter. Follow the below steps to setup REST Controller Library in your CodeIgniter application.

  • Download REST config file and place in the application/config/ directory.
    Open the application/config/rest.php file and setup the following configuration.

    • Set REST login username and password.
      $config['rest_valid_logins'] = ['admin' => '1234'];
    • Enable REST API key.
      $config['rest_enable_keys'] = TRUE;
  • Download REST_Controller file and place in the application/libraries/ directory.
  • Download Format class file and place in the application/libraries/ directory.
  • Download Language file and place in the application/language/english/ directory.

Note that: All the required library files are included in our example CodeIgniter application, so, you don’t need to download these files separately. Download our source code to get sample CodeIgniter RESTful Web service application.

Create Model

Open the application/models/ directory and create User.php file and add the following code to handle the database related works.
The User model has the following methods to fetch, insert, update, and delete user data in the database.

  • __construct() – Load the database library.
  • getRows() – Fetch the user data from the users table and returns single row or multiple rows.
  • insert() – Insert user data in the users table.
  • user_put() – Update user data in the users table based on the given ID.
  • user_delete() – Delete user from the users table based on the given ID.
load->database();
    }

    /*
     * Fetch user data
     */
    function getRows($id = ""){
        if(!empty($id)){
            $query = $this->db->get_where('users', array('id' => $id));
            return $query->row_array();
        }else{
            $query = $this->db->get('users');
            return $query->result_array();
        }
    }
    
    /*
     * Insert user data
     */
    public function insert($data = array()) {
        if(!array_key_exists('created', $data)){
            $data['created'] = date("Y-m-d H:i:s");
        }
        if(!array_key_exists('modified', $data)){
            $data['modified'] = date("Y-m-d H:i:s");
        }
        $insert = $this->db->insert('users', $data);
        if($insert){
            return $this->db->insert_id();
        }else{
            return false;
        }
    }
    
    /*
     * Update user data
     */
    public function update($data, $id) {
        if(!empty($data) && !empty($id)){
            if(!array_key_exists('modified', $data)){
                $data['modified'] = date("Y-m-d H:i:s");
            }
            $update = $this->db->update('users', $data, array('id'=>$id));
            return $update?true:false;
        }else{
            return false;
        }
    }
    
    /*
     * Delete user data
     */
    public function delete($id){
        $delete = $this->db->delete('users',array('id'=>$id));
        return $delete?true:false;
    }

}
?>

Create API Controller

It’s always a good idea to group all API controllers in a separate folder for better usability. So, create an api/ folder in application/controllers/ directory to place the controller which will be used for API call.
Open the application/controllers/api/ directory and create Example.php file. At first, include the REST Controller library and add the following code.
The Example API controller has the following methods to handle the GET, POST, PUT, and DELETE request.

  • __construct() – Load the User model.
  • user_get() – Return the user data from database. If the ID parameter doesn’t exist, it returns all the rows otherwise single row will be returned.
  • user_post() – Add user data to the database.
  • user_put() – Update the user data in the database based on the ID.
  • user_delete() – Delete the user from the database based on the ID.
load->model('user');
    }
    
    public function user_get($id = 0) {
        //returns all rows if the id parameter doesn't exist,
        //otherwise single row will be returned
        $users = $this->user->getRows($id);
        
        //check if the user data exists
        if(!empty($users)){
            //set the response and exit
            $this->response($users, REST_Controller::HTTP_OK);
        }else{
            //set the response and exit
            $this->response([
                'status' => FALSE,
                'message' => 'No user were found.'
            ], REST_Controller::HTTP_NOT_FOUND);
        }
    }
    
    public function user_post() {
        $userData = array();
        $userData['first_name'] = $this->post('first_name');
        $userData['last_name'] = $this->post('last_name');
        $userData['email'] = $this->post('email');
        $userData['phone'] = $this->post('phone');
        if(!empty($userData['first_name']) && !empty($userData['last_name']) && !empty($userData['email']) && !empty($userData['phone'])){
            //insert user data
            $insert = $this->user->insert($userData);
            
            //check if the user data inserted
            if($insert){
                //set the response and exit
                $this->response([
                    'status' => TRUE,
                    'message' => 'User has been added successfully.'
                ], REST_Controller::HTTP_OK);
            }else{
                //set the response and exit
                $this->response("Some problems occurred, please try again.", REST_Controller::HTTP_BAD_REQUEST);
            }
        }else{
            //set the response and exit
            $this->response("Provide complete user information to create.", REST_Controller::HTTP_BAD_REQUEST);
        }
    }
    
    public function user_put() {
        $userData = array();
        $id = $this->put('id');
        $userData['first_name'] = $this->put('first_name');
        $userData['last_name'] = $this->put('last_name');
        $userData['email'] = $this->put('email');
        $userData['phone'] = $this->put('phone');
        if(!empty($id) && !empty($userData['first_name']) && !empty($userData['last_name']) && !empty($userData['email']) && !empty($userData['phone'])){
            //update user data
            $update = $this->user->update($userData, $id);
            
            //check if the user data updated
            if($update){
                //set the response and exit
                $this->response([
                    'status' => TRUE,
                    'message' => 'User has been updated successfully.'
                ], REST_Controller::HTTP_OK);
            }else{
                //set the response and exit
                $this->response("Some problems occurred, please try again.", REST_Controller::HTTP_BAD_REQUEST);
            }
        }else{
            //set the response and exit
            $this->response("Provide complete user information to update.", REST_Controller::HTTP_BAD_REQUEST);
        }
    }
    
    public function user_delete($id){
        //check whether post id is not empty
        if($id){
            //delete post
            $delete = $this->user->delete($id);
            
            if($delete){
                //set the response and exit
                $this->response([
                    'status' => TRUE,
                    'message' => 'User has been removed successfully.'
                ], REST_Controller::HTTP_OK);
            }else{
                //set the response and exit
                $this->response("Some problems occurred, please try again.", REST_Controller::HTTP_BAD_REQUEST);
            }
        }else{
            //set the response and exit
            $this->response([
                'status' => FALSE,
                'message' => 'No user were found.'
            ], REST_Controller::HTTP_NOT_FOUND);
        }
    }  
}

?>

Interacting with CodeIgniter RESTful Web Services

Now it’s time to interact with the CodeIgniter RESTful Web Services. The cURL is the most flexible and easiest way to interact with a REST API. In the following example code, we will show you how to send GET, POST, PUT and DELETE request to CodeIgniter REST API using PHP cURL. Also, HTTP Basic authentication and API key will be used to connect with RESTful API.

Retrieve User Data via REST API
The following code performs a GET request to fetch the user data via RESTful Web Services (Example API).

//API URL
$url = 'http://localhost/codeigniter/api/example/user/';

//API key
$apiKey = 'CODEX@123';

//Auth credentials
$username = "admin";
$password = "1234";

//create a new cURL resource
$ch = curl_init($url);

curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("X-API-KEY: " . $apiKey));
curl_setopt($ch, CURLOPT_USERPWD, "$username:$password");

$result = curl_exec($ch);

//close cURL resource
curl_close($ch);

Insert User Data via REST API
The following code performs a POST request to insert user data via RESTful Web Services (Example API).

//API URL
$url = 'http://localhost/codeigniter/api/example/user/';

//API key
$apiKey = 'CODEX@123';

//Auth credentials
$username = "admin";
$password = "1234";

//user information
$userData = array(
    'first_name' => 'John',
    'last_name' => 'Doe',
    'email' => 'john@example.com',
    'phone' => '123-456-7890'
);

//create a new cURL resource
$ch = curl_init($url);

curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("X-API-KEY: " . $apiKey));
curl_setopt($ch, CURLOPT_USERPWD, "$username:$password");
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $userData);

$result = curl_exec($ch);

//close cURL resource
curl_close($ch);

Update User Data via REST API
The following code performs a PUT request to update user data via RESTful Web Services (Example API).

//API URL
$url = 'http://localhost/codeigniter/api/example/user/';

//API key
$apiKey = 'CODEX@123';

//Auth credentials
$username = "admin";
$password = "1234";

//user information
$userData = array(
    'id' => 2,
    'first_name' => 'John2',
    'last_name' => 'Doe2',
    'email' => 'john2@example.com',
    'phone' => '098-765-4321'
);

//create a new cURL resource
$ch = curl_init($url);

curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('X-API-KEY: '.$apiKey, 'Content-Type: application/x-www-form-urlencoded'));
curl_setopt($ch, CURLOPT_USERPWD, "$username:$password");
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "PUT");
curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($userData));

$result = curl_exec($ch);

//close cURL resource
curl_close($ch);

Delete User Data via REST API
The following code performs a DELETE request to delete user data via RESTful Web Services (Example API).

//API URL
$url = 'http://localhost/codeigniter/api/example/user/2';

//API key
$apiKey = 'CODEX@123';

//Auth credentials
$username = "admin";
$password = "1234";

//create a new cURL resource
$ch = curl_init($url);

curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("X-API-KEY: " . $apiKey));
curl_setopt($ch, CURLOPT_USERPWD, "$username:$password");
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'DELETE');

$result = curl_exec($ch);

//close cURL resource
curl_close($ch);

Test HTTP calls with Postman Extension

Postman Extension makes API development faster, easier, and better. Install Postman Extension for testing the API call.

  • Get all users data: GET http://localhost/codeigniter/api/example/user/
  • Get single user data: GET http://localhost/codeigniter/api/example/user/4
  • Add user data in the database: POST http://localhost/codeigniter/api/example/user/

Update user data in the database: PUT http://localhost/codeigniter/api/example/user/

Delete user from the database: DELETE http://localhost/codeigniter/api/example/user/4

Connecting Oracle database in CodeIgniter

Option 01:

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
    'dsn'   => '',
    'hostname' => '192.168.0.109:1521/orcl', //hostname:db_port/service_name
    //'hostname' => 'localhost:1521/orcl',
    'username' => 'db_username', 
    'password' => 'db_password', 
    'database' => 'db_name',
    'dbdriver' => 'oci8',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);


Option 02:

$active_group = 'default';
$active_record = TRUE;
$db['default']['hostname'] = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.246)(PORT=1521))(CONNECT_DATA=(SID=orcl)))';
$db['default']['username'] = 'db_username';
$db['default']['password'] = 'db_password';
$db['default']['database'] = 'db_name';
$db['default']['dbdriver'] = 'oci8';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Connecting to POSTGRESQL in CodeIgniter 3

First enable Postgresql extension in php.ini

extension=php_pgsql.dll

You also can enable Postgresql extension for PDO as well.

extension=php_pdo_pgsql.dll


$db['default'] = array(
    'port'   => 5432, # Add 
);

OR

$db['default'] = array(
    'dsn'   => 'pgsql:host=localhost;port=5432;dbname=database_name', 
    'dbdriver' => 'pdo',
);
 

$active_group = ‘default’;
$query_builder = TRUE;

$db[‘default’] = array(
‘dsn’ => ”,
‘hostname’ => ‘localhost’,
‘username’ => ‘postgres’,
‘password’ => ”,
‘database’ => ‘fmsdb’,
‘dbdriver’ => ‘postgre’,
‘dbprefix’ => ”,
‘pconnect’ => FALSE,
‘db_debug’ => (ENVIRONMENT !== ‘production’),
‘cache_on’ => FALSE,
‘cachedir’ => ”,
‘char_set’ => ‘utf8’,
‘dbcollat’ => ‘utf8_general_ci’,
‘swap_pre’ => ”,
‘encrypt’ => FALSE,
‘compress’ => FALSE,
‘stricton’ => FALSE,
‘failover’ => array(),
‘save_queries’ => TRUE
);

Protect your WordPress by hiding the REST API

The WordPress REST API was introduced in the WordPress core at the end of 2016 with the release of WordPress 4.6. Like all the big changes that appear in the platform, the REST API generated controversy in some and indifference in others.

It’s even possible that you have no idea what it is, but if you have an updated version of WordPress (and you should) you are exposing many aspects of your website publicly through the REST API. Just append the fragment /wp-json/ to your domain name and visit this URL to see it with your own eyes.

Moreover, do the exercise of visiting the following web URLs and you may be surprised with what you’ll find:

  • mydomain.com/wp-json/wp/v2/users
  • mydomain.com/wp-json/wp/v2/posts

As a result of the first URL you will have a JSON with the data of the users of your web. Notice that user identifiers are included there, and this is something that people traditionally hide due to security issues and to prevent possible attacks.

The second URL shows us a list with the last posts. However, if you have protected content that only certain premium users of your website (in a membership site, for example) should have access to, it’s possible that you’ve been exposing this premium content through the REST API.

Let’s see how we can avoid compromised situations by being more aware of what we publicly expose through the WordPress REST API.

Show WordPress REST API Only to Registered Users

A solution that we can implement to hide the WordPress REST API is to prevent those users who are not registered on our website from accessing it.

To hide the REST API to unregistered users, we must add the following code in our WordPress. Remember that you can put it in the functions.php file of your theme or just develop a plugin for it (a much better option).


401 ) );
}
if ( ! current_user_can( 'administrator' ) ) {
return new WP_Error( 'rest_not_admin', 'You are not an administrator.', array( 'status' => 401 ) );
}
return $result;
});


401 ) );
}
return $result;
});

Change Password in CodeIgniter with Callback Validation to Check Old Password

Change password in CodeIgniter with callback validation for check old password.

Controller

application/controllers/Users.php



defined('BASEPATH') OR exit('No direct script access allowed');

class Users extends CI_Controller{
    
    public function __construct()
    {
        parent::__construct();
        $this->load->helper('form');
        $this->load->library('form_validation');
        $this->load->model('users_model');
    }
    
    private function logged_in()
    {
        if( ! $this->session->userdata('authenticated')){
            redirect('users/login');
        }
    }
    
    public function changePassword()
    {
        $this->logged_in();

        $data['title'] = 'Change Password';

        $this->load->library('form_validation');

        $this->form_validation->set_rules('oldpass', 'old password', 'callback_password_check');
        $this->form_validation->set_rules('newpass', 'new password', 'required');
        $this->form_validation->set_rules('passconf', 'confirm password', 'required|matches[newpass]');

        $this->form_validation->set_error_delimiters('
', '
'
); if($this->form_validation->run() == false) { $this->load->view('header', $data); $this->load->view('users/change_password', $data); $this->load->view('footer', $data); } else { $id = $this->session->userdata('id'); $newpass = $this->input->post('newpass'); $this->users_model->update_user($id, array('password' => md5($newpass))); redirect('users/logout'); } } public function password_check($oldpass) { $id = $this->session->userdata('id'); $user = $this->users_model->get_user($id); if($user->password !== md5($oldpass)) { $this->form_validation->set_message('password_check', 'The {field} does not match'); return false; } return true; } } ?>

Model

application/models/Users_model.php



defined('BASEPATH') OR exit('No direct script access allowed');

class Users_model extends CI_Model{
    
    public function get_user($id)
    {
        $this->db->where('id', $id);
        $query = $this->db->get('users');
        return $query->row();
    }

    public function update_user($id, $userdata)
    {
        $this->db->where('id', $id);
        $this->db->update('users', $userdata);
    }
}
?>

View

application/views/users/change_password.php

div class="row justify-content-center">
    <div class="col-6">
        <h1> echo $title ?>h1>
         echo form_open('users/changePassword', array('id' => 'passwordForm'))?>
            <div class="form-group">
                <input type="password" name="oldpass" id="oldpass" class="form-control" placeholder="Old Password" />
                 echo form_error('oldpass', '
', '
'
)?>
div> <div class="form-group"> <input type="password" name="newpass" id="newpass" class="form-control" placeholder="New Password" /> echo form_error('newpass', '
', '
'
)?>
div> <div class="form-group"> <input type="password" name="passconf" id="passconf" class="form-control" placeholder="Confirm Password" /> echo form_error('passconf', '
', '
'
)?>
div> <div class="form-group"> <button type="submit" class="btn btn-success">Change Passwordbutton> div> echo form_close(); ?> div> div>

How to install MySQL server on CentOS 8 Linux

How do I install MySQL server 8.0 on CentOS 8 Linux server running on Linode and AWS cloud? How do I add and set up a new MySQL user and database account on the newly created CentOS server?

Oracle MySQL server version 8.0 is a free and open-source free database server. It is one of the most popular database system used in web apps and websites on the Internet.

Typically MySQL is part of the LAMP (Linux, Apache/Nginx, MySQL, Perl/Python/PHP) stack. Popular open-source software such as WordPress, MediaWiki, and others profoundly used by MySQL as a database storage engine. Let us see how to install MySQL server version 8.x on CentOS 8 Linux server.

How to install MySQL server on a CentOS 8

First, open the terminal app and then log in to your CentOS server using the ssh command:
$ ssh vivek@centos-8-ec2-box-ip
Now, update CentOS system to apply security updates and fixes on Linux system using the dnf command/yum command:
$ sudo yum update
## or ##
$ sudo dnf update

Sample outputs:

CentOS-8 - AppStream                            21 MB/s | 5.8 MB     00:00    
CentOS-8 - Base                                 14 MB/s | 2.2 MB     00:00    
CentOS-8 - Extras                               50 kB/s | 8.6 kB     00:00    
Dependencies resolved.
Nothing to do.
Complete!

Step 1 – Installing MySQL 8 server

Luckily our CentOS 8 box comes with MySQL 8 server package. Let us search for it:
$ sudo yum search mysql-server
$ sudo yum module list mysql

And we see:

Last metadata expiration check: 0:02:47 ago on Mon Nov 23 16:26:31 2020.
===================== Name Exactly Matched: mysql-server ======================
mysql-server.x86_64 : The MySQL server and related files

Next, find out version information, run:
$ sudo yum info mysql-server
Here is what we see:

Last metadata expiration check: 0:02:22 ago on Mon Nov 23 16:26:31 2020.
Available Packages
Name         : mysql-server
Version      : 8.0.21
Release      : 1.module_el8.2.0+493+63b41e36
Architecture : x86_64
Size         : 22 M
Source       : mysql-8.0.21-1.module_el8.2.0+493+63b41e36.src.rpm
Repository   : AppStream
Summary      : The MySQL server and related files
URL          : http://www.mysql.com
License      : GPLv2 with exceptions and LGPLv2 and BSD
Description  : MySQL is a multi-user, multi-threaded SQL database server. MySQL
             : is a client/server implementation consisting of a server daemon
             : (mysqld) and many different client programs and libraries. This
             : package contains the MySQL server and some accompanying files
             : and directories.

Install it:
$ sudo yum install mysql-server

How to install MySQL 8 on CentOS 8 Linux

Click to enlarge

Step 2 – Enabling MySQL 8 mysqld.service,server

The service name is mysqld.service, and we need to enable it using the following systemctl command:
$ sudo systemctl enable mysqld.service
Confirmation displayed:

reated symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

Start the service and then verify it:
$ sudo systemctl start mysqld.service
$ sudo systemctl status mysqld.service

 mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2020-11-23 16:50:14 UTC; 4s ago
  Process: 551 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
  Process: 681 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
  Process: 601 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
  Process: 577 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 637 (mysqld)
   Status: "Server is operational"
    Tasks: 39 (limit: 24960)
   Memory: 331.0M
   CGroup: /system.slice/mysqld.service
           └─637 /usr/libexec/mysqld --basedir=/usr

Nov 23 16:50:13 centos-aws-mysql systemd[1]: Stopped MySQL 8.0 database server.
Nov 23 16:50:13 centos-aws-mysql systemd[1]: Starting MySQL 8.0 database server...
Nov 23 16:50:14 centos-aws-mysql systemd[1]: Started MySQL 8.0 database server.

Step 3 – Securing MySQL 8 server

All you need to do is type the following command, and it will secure MySQL 8 server installation on CentOS Linux:
$ sudo mysql_secure_installation

Please set the password for root here.

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

Step 4 – Starting/Stopping/Restarting MySQL 8 server

The syntax is:
$ sudo systemctl start mysql.service
$ sudo systemctl stop mysql.service
$ sudo systemctl restart mysql.service

To view the MySQL 8 service log as follows using the journalctl command:
$ sudo journalctl -u mysqld.service -xe
$ sudo tail -f /var/log/mysql/mysqld.log

MySQL 8 log file sample entries:

2020-11-23T16:55:19.101316Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.21).
2020-11-23T16:55:21.728819Z 0 [Warning] [MY-010909] [Server] /usr/libexec/mysqld: Forcing close of thread 10  user: 'root'.
2020-11-23T16:55:23.083389Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.21)  Source distribution.
2020-11-23T16:56:19.225544Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.21) starting as process 524
2020-11-23T16:56:19.237500Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-23T16:56:19.562441Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-11-23T16:56:19.677202Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock
2020-11-23T16:56:19.754024Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-11-23T16:56:19.754207Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-11-23T16:56:19.780843Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.21'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution.

Step 5 – Testing MySQL 8 installation

So far, so good. You learned how to install, set up, secure, and start/stop the MySQL 8 on CentOS 8 Linux cloud server. It is time to log in as a
mysql root user. The syntax is:
$ mysql -u root -p
$ mysql -u USER -h host -p
$ mysql -u USER -h host -p mysql

Let us type a few SQL commands at the mysql> prompt:
STATUS;
SHOW VARIABLES LIKE "%version%";
quit

Testing MySQL on CentOS 8

Step 6 – Creating a new MySQL 8 database and user account with password

Let create a new database called ‘spacedb‘, type at the mysql> prompt:
CREATE DATABASE spacedb;
Next, we are going to create a new user named ‘mars‘ for our database called ‘spacedb’ as follows:
CREATE USER 'mars'@'%' IDENTIFIED BY 'User_Password_Here';
Finally, give permissions:
GRANT SELECT, INSERT, UPDATE, DELETE ON spacedb.* TO 'mars'@'%';
Of course, we can grant ALL PRIVILEGES too as follows:
GRANT ALL PRIVILEGES ON spacedb.* TO 'mars'@'%';
See MySQL 8 users and their grants/permissions as follows:
SELECT user,host FROM mysql.user;
SHOW GRANTS for mars;
quit

Test new user settings and DB as follows:
mysql -u mars -p spacedb
mysql -u mars -h localhost -p spacedb

Creating MySQL 8 database with user and password on CentOS 8

Where,

  • -u mars; : User name for login
  • -h localhost : Connect to server named localhost
  • -p : Prompt for password
  • spacedb : Connect to database named spacedb

Step 7 – Configuring MySQL 8 server on a CentOS 8

Let us see default config file using the cat command:
# cat /etc/my.cnf.d/mysql-server.cnf
Config:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

Want to allow remote connections to your MySQL server? Edit the /etc/my.cnf.d/mysql-server.cnf and append the following line under [mysqld]:
bind_address = 0.0.0.0

WARNING: See MySQL documentation for a detailed explanation for tuning options as to each server and set up is unique. Do not set up values blindly. I provide them as a starting point for optimizing MySQL 8 installation and values depending upon available RAM, CPU cores, server load and other circumstances.

Set InnoDB settings:

default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1
innodb_buffer_pool_size         = 512M
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 512M
innodb_stats_on_metadata        = 0
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64

MyISAM settings:

# UPD
key_buffer_size                 = 32M   
low_priority_updates            = 1
concurrent_insert               = 2
# UPD
max_connections                 = 100   
back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K
interactive_timeout             = 180
wait_timeout                    = 180

Buffer settings UPD:

join_buffer_size                = 4M    
read_buffer_size                = 3M    
read_rnd_buffer_size            = 4M    
sort_buffer_size                = 4M

Edit and config logging if needed (by default slow_query disabled):

log_queries_not_using_indexes   = 1
long_query_time                 = 5
#slow_query_log                  = 0     
#slow_query_log_file             = /var/log/mysql/mysql_slow.log

This is useful for mysqldump command to make backups:

[mysqldump]
quick
quote_names
max_allowed_packet              = 64M

Step 8 – Firewall configuration to open MySQL server TCP port 3306

Are you using MySQL 8 server remotely? Do you have Apache/Nginx/PHP/Python/Perl app on another server? Then open port for everyone:
$ sudo firewall-cmd --zone=public --add-service=mysql --permanent
Only allow access from 192.168.1.0/24 CIDR:
$ sudo firewall-cmd \
--add-rich-rule 'rule family="ipv4" \
source address="192.168.1.0/24" \
service name="mysql" accept' --permanent

The above is fine grained firewalld access rules to restrict access to MySQL 8 server to VLAN users only. See how to set up a firewall using FirewallD on CentOS 8 Linux for more info.

Conclusion

And there you have it, Oracle MySQL server version 8.x set up and running correctly on a CentOS Linux 8 server with Firewalld config. Further, you learned how to add a new database, user, and password for your project including MySQL 8 server tuning options.

Install Oracle Database 19c on CentOS 8 in VirtualBox

** Please use the root user to edit the files and execute the commands unless further notice. **

Prerequisite

  1. Install the latest VirtualBox Platform Package and the VirtualBox Extension Pack (Oracle_VM_VirtualBox_Extension_Pack-VERSION.vbox-extpack).
  2. Download the latest VirtualBox Guest Additions (VBoxGuestAdditions_VERSION.iso).
  3. Download the latest CentOS Stream 8.
  4. Create a new virtual machine and install the CentOS to the virtual machine. During the CentOS installation, select Workstation as Base Environment, select Container ManagementDevelopment Tools and Graphical Administration Tools as Additional software for Selected Environment. Use http://mirror.centos.org/centos/8/BaseOS/x86_64/os/ as the installation source.
  5. After installing the CentOS, execute the following commands to get the required libraries to create applications for handling compiled objects.
dnf update
dnf -y install elfutils-libelf-devel
  1. Insert the ISO of VirtualBox Guest Additions to the virtual machine, and then install it.

Download Packages and Software

Hostname and Host File

  1. Open the file /etc/hostname, change the content to update the hostname.
ol8-19.localdomain
  1. Open the file /etc/hosts, add your IP address and hostname.
192.168.122.1 ol8-19.localdomain

Install Required Packages

  1. Perform a dnf update to update every currently installed package.
dnf update
  1. Add execute permission to the downloaded rpm files.
chmod u+x *.rpm
  1. Install the libcapl library for getting and setting POSIX.1e (formerly POSIX 6) draft 15 capabilities.
dnf localinstall -y compat-libcap1-1.10-7.el7.x86_64.rpm
  1. Inatll the libstdc++ package which contains compatibility standard C++ library from GCC 3.3.4.
dnf localinstall -y compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
  1. Install the below required packages.
dnf install -y bc binutils elfutils-libelf elfutils-libelf-devel fontconfig-devel \
    gcc gcc-c++ glibc glibc-devel ksh ksh libaio libaio-devel libgcc libnsl libnsl.i686 \
    libnsl2 libnsl2.i686 librdmacm-devel libstdc++ libstdc++-devel libX11 libXau libxcb \
    libXi libXrender libXrender-devel libXtst make net-tools nfs-utils smartmontools \
    sysstat targetcli unixODBC;

Install Oracle Installation Prerequisites

  1. Install the Oracle Installation Prerequisites (OIP) package.
dnf localinstall -y oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
  1. Open the /etc/group file, update the GID of the below items.
oinstall:x:64890:oracle
dba:x:64891:oracle
oper:x:64892:oracle
backupdba:x:64893:oracle
dgdba:x:64894:oracle
kmdba:x:64895:oracle
racdba:x:64896:oracle
  1. Open the /etc/passwd file, update both the UID and GID of account oracle.
oracle:x:64890:64890::/home/oracle:/bin/bash
  1. Update the password of account oracle.
passwd oracle
  1. Set secure Linux to permissive by editing the /etc/selinux/config file.
SELINUX=permissive
  1. Set the secure Linux change right now.
setenforce Permissive
  1. Disable the firewall.
systemctl stop firewalld
systemctl disable firewalld

Setup Oracle User Profile

  1. Create Oracle directories.
mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1
mkdir -p /u02/oradata
chown -R oracle:oinstall /u01 /u02
chmod -R 775 /u01 /u02
  1. Create a new directory for Oracle user.
mkdir -p /home/oracle/scripts
chown -R oracle:oinstall /home/oracle
  1. Create an environment setting file.
cat > /home/oracle/scripts/setEnv.sh <<EOF
# Oracle Settings
export TMP=/tmp
export TMPDIR=\$TMP

export ORACLE_HOSTNAME=$HOSTNAME
export ORACLE_UNQNAME=cdb1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/19.3.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=cdb1
export PDB_NAME=pdb1
export DATA_DIR=/u02/oradata

export PATH=/usr/sbin:/usr/local/bin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$PATH

export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
EOF
  1. Create a startup shell script.
cat > /home/oracle/scripts/start_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

dbstart \$ORACLE_HOME
EOF
  1. Create a stop shell script.
cat > /home/oracle/scripts/stop_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

dbshut \$ORACLE_HOME
EOF
  1. Update the owner and permission of the shell scripts and its parent directory.
chown -R oracle:oinstall /home/oracle
chmod u+x /home/oracle/scripts/*.sh
  1. Set the environment when the Bash runs whenever it is started interactively.
cat > /home/oracle/.bashrc <<EOF
#.bashrc

# User specific aliases and functions

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'

# Source global definitions
if [ -f /etc/bashrc ]; then
  . /etc/bashrc
fi

. /home/oracle/scripts/setEnv.sh >> /home/oracle/.bashrc
EOF

chown oracle:oinstall /home/oracle/.bashrc

Create and Add New Swap File

  1. Run the following command, with oracle user, to create and apply new swap file.
dd if=/dev/zero of=/tmp/additional-swap bs=1048576 count=4096
chmod 600 /tmp/additional-swap
mkswap /tmp/additional-swap
  1. Apply the swap by executing the following command with root user.
swapon /tmp/additional-swap

Install Oracle Database

  1. Set the DISPLAY variable with oracle user.
DISPLAY=$HOSTNAME:0.0; export DISPLAY
  1. Unzip the archive with oracle user.
cd $ORACLE_HOME
unzip -oq /path/to/software/LINUX.X64_193000_db_home.zip
  1. “Cheat” the installer about the distribution with oracle user.
export CV_ASSUME_DISTID=RHEL7.6
  1. Run the installer, with oracle user, to install Oracle database.
cd $ORACLE_HOME
./runInstaller -ignorePrereq -waitforcompletion -silent                        \
    -responseFile ${ORACLE_HOME}/install/response/db_install.rsp               \
    oracle.install.option=INSTALL_DB_SWONLY                                    \
    ORACLE_HOSTNAME=${ORACLE_HOSTNAME}                                         \
    UNIX_GROUP_NAME=oinstall                                                   \
    INVENTORY_LOCATION=${ORA_INVENTORY}                                        \
    SELECTED_LANGUAGES=en,en_GB                                                \
    ORACLE_HOME=${ORACLE_HOME}                                                 \
    ORACLE_BASE=${ORACLE_BASE}                                                 \
    oracle.install.db.InstallEdition=EE                                        \
    oracle.install.db.OSDBA_GROUP=dba                                          \
    oracle.install.db.OSBACKUPDBA_GROUP=dba                                    \
    oracle.install.db.OSDGDBA_GROUP=dba                                        \
    oracle.install.db.OSKMDBA_GROUP=dba                                        \
    oracle.install.db.OSRACDBA_GROUP=dba                                       \
    SECURITY_UPDATES_VIA_MYORACLESUPPORT=false                                 \
    DECLINE_SECURITY_UPDATES=true
  1. If the setup is success, the following message should be printed on screen.
Successfully Setup Software.
  1. Execute the below scripts, with root user, to update the permission of Oracle directories and set the environment variables.
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/19.3.0/dbhome_1/root.sh

Database Creation

  1. Start the listener with oracle user.
lsnrctl start
  1. Create a database with oracle user.
dbca -silent -createDatabase                                                   \
     -templateName General_Purpose.dbc                                         \
     -gdbname ${ORACLE_SID} -sid  ${ORACLE_SID} -responseFile NO_VALUE         \
     -characterSet AL32UTF8                                                    \
     -sysPassword SysPassword1                                                 \
     -systemPassword SysPassword1                                              \
     -createAsContainerDatabase true                                           \
     -numberOfPDBs 1                                                           \
     -pdbName ${PDB_NAME}                                                      \
     -pdbAdminPassword PdbPassword1                                            \
     -databaseType MULTIPURPOSE                                                \
     -automaticMemoryManagement false                                          \
     -totalMemory 1000                                                         \
     -storageType FS                                                           \
     -datafileDestination "${DATA_DIR}"                                        \
     -redoLogFileSize 50                                                       \
     -emConfiguration NONE                                                     \
     -ignorePreReqs

Listener Update

  1. Replace or edit the listener.ora file, with oracle user, to set the correct hostname, port number and SID name.
cat > /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora <<EOF
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1539))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ${ORACLE_SID})
    )
  )

EOF
  1. Reload the Oracle Listener.
lsnrctl reload

Post Installation

  1. Edit the /etc/oratab file, with root user, to update the restart flag from ‘N‘ to ‘Y‘.
orcl:/u01/app/oracle/product/19.3.0/dbhome_1:Y
  1. Configure the Database instance “orcl” with auto startup.
cd $ORACLE_HOME/dbs
ln -s spfilecdb1.ora initorcl.ora
  1. Enable Oracle Managed Files (OMF) and make sure the PDB starts when the instance starts.
sqlplus / as sysdba <<EOF
alter system set db_create_file_dest='${DATA_DIR}';
alter pluggable database ${PDB_NAME} save state;
exit;
EOF
  1. Execute the following commands, with root user, to start the Oracle Listener automatically.
cat > /home/oracle/scripts/cron.sh <<EOF1
#!/bin/bash

. /home/oracle/scripts/setEnv.sh

echo "\`date\`" > /home/oracle/scripts/last.log

lsnrctl start

sleep 3

lsnrctl reload

sleep 3

sqlplus /nolog <
conn / as sysdba
startup
EOF

EOF1

chown oracle:oinstall /home/oracle/scripts/cron.sh
chmod 744 /home/oracle/scripts/cron.sh
  1. Use the following command, with oracle user, to edit the crontab file.
crontab -e
  1. Put the following cron job in the first line of crontab file, then press the keys :wq to save and exit.
@reboot /home/oracle/scripts/cron.sh

Healthcheck

  1. Login as oracle user and then execute the following commands one-by-one.
sqlplus /nolog
conn / as sysdba;
select * from v$version;
show pdbs;

Create New User and Tablespace

  1. Login as Sysdba with SqlPlus.
sqlplus / as sysdba
  1. Update the seesion setting _ORACLE_SCRIPT to true to allow common user comes without c## as prefix.
ALTER SESSION SET "_ORACLE_SCRIPT"=true;
  1. Create a new tablespace with an automatic extensible size 100MB, maximum 10G in size.
-- DROP TABLESPACE my_tablespace INCLUDING CONTENTS AND DATAFILES;
-- Location of the dat file: /u01/app/oracle/product/19.3.0/dbhome_1/dbs/my_tablespace.dat
-- SELECT tablespace_name, block_size, max_size, status FROM DBA_TABLESPACES;
CREATE TABLESPACE my_tablespace
  DATAFILE 'my_tablespace.dat'
    SIZE 100M
    AUTOEXTEND ON
    NEXT 32M MAXSIZE 10G
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO
;

SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY 
FROM DBA_DATA_FILES ORDER BY FILE_ID DESC;
  1. [Optional] Update the password life time from 180 days (default) to unlimited.
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
  1. Create a new user.
-- ALTER SESSION SET "_ORACLE_SCRIPT"=true;
-- DROP USER newuser CASCADE;
CREATE USER newuser IDENTIFIED BY "P@ssw0rd" DEFAULT TABLESPACE my_tablespace;
  1. Grant permissions to the new user.
-- REVOKE CREATE SESSION FROM newuser;
-- REVOKE CREATE TABLE FROM newuser;
-- REVOKE CREATE VIEW FROM newuser;
-- REVOKE CREATE ANY TRIGGER FROM newuser;
-- REVOKE CREATE ANY PROCEDURE FROM newuser;
-- REVOKE CREATE SEQUENCE FROM newuser;
-- REVOKE CREATE SYNONYM FROM newuser;
GRANT CREATE SESSION TO newuser;
GRANT CREATE TABLE TO newuser;
GRANT CREATE VIEW TO newuser;
GRANT CREATE ANY TRIGGER TO newuser;
GRANT CREATE ANY PROCEDURE TO newuser;
GRANT CREATE SEQUENCE TO newuser;
GRANT CREATE SYNONYM TO newuser;

ALTER USER newuser QUOTA UNLIMITED ON my_tablespace;
  1. [Optional] Grant DBA to the new user.
-- REVOKE DBA FROM newuser;
GRANT DBA TO newuser;

References

CodeIgniter htaccess File

RewriteEngine On
RewriteBase /ci301/

RewriteCond %{REQUEST_URI} ^system.*
RewriteRule ^(.*)$ /index.php?/$1 [L]

RewriteCond %{REQUEST_URI} ^application.*
RewriteRule ^(.*)$ /index.php?/$1 [L]

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?/$1 [L]

Free Web Hosting