How to Install Percona on CentOS7

Hello dear readers,

I’ve been checking the MySQL server options and new features which show that far from dying as many predicted when it fell under Oracle, it has evolved to something awesome.

In this post, I will use Percona for reasons I’d like to keep undisclosed. So let’s get started.

The easiest way to get up and running with Percona is to rely on the YUM repositories.

# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

This will install the Percona repository on your system so that you can then install the actual server and additional tools.

# yum install Percona-Server-server-57

This installs the server software, but doesn’t start the service. You need to manually start it for the first time.

# systemctl start mysql

After you get Percona MySQL running, you need to update your root password.

# cat /var/log/mysqld.log | grep generated
2018-03-24T15:54:37.006018Z 1 [Note] A temporary password is generated for root@localhost: Mr9nwsjduw=j

Use that password to login on your database and set your own:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('Your#Pass');

You’re all set! Grab your SQL Database Client of choice and start managing your Percona server!

Advertisements

SSH Connection Manager in Linux – Perl Auto Connector

Hello everyone,

Recently I found an awesome piece of software that can save you tons of time if you connect to several hosts through SSH from Linux.

In Windows, there are several choices like Putty CM (dead) or SuperPutty, which let you store connection data and connect to a host simply by double-clicking on the connection name later.

In Linux, the Perl Auto Connector (PAC) does this and does it very well.

It’s developed for Debian based distros, but you can find it the RPM package as well in their project page: https://sourceforge.net/projects/pacmanager/

Just go over to the “Files” tab and suit yourself.

You may run into a couple issues after installing, since it relies in some included libraries that must be removed  prior to being able to use it

After you install the RPM package, run the following

sudo find /opt/pac/ -name "Vte.so*" -exec rm {} +
sudo yum -y install perl-Gnome2-Vte

That will force the program to rely on global libraries instead of embedded libraries.

Enjoy!

 

Oracle Learning Library and Massive Open Online Courses – Free as in beer!

Hello everyone,

This is a very short post regarding Oracle Online Training for Free… Yes, as in beer!

There’s a new training mode for Oracle enthusiasts. It covers from Java to the Oracle Cloud, including everything in between, including Database, SQL, PL/SQL and other topics. There are permanent courses and MOOCs.

Massive Open Online Course (a.k.a. MOOC) are time limited events that  include videos, materials and everything you need to practice the lesson objectives.

Simply go to: http://www.oracle.com/goto/oll

This will take you to the Oracle Learning Library landing page.

From there you will find Learning Paths, Permanent Courses and Limited-Time Events. Each section has a wide range of options, topics and modules that can help grow your professional skills. The best of all? For Free!

In my case, my main interest circles around Oracle Databases, SQL, PL/SQL, Apex and DevOps.

Choose your destiny and grow!

MySQL 5.7 on Fedora 25/26 from scratch

Hello fellow DBAs, tech enthusiasts and anybody who wants to know how to install MySQL 5.7 on Fedora 25/26 or any equivalent RPM based Linux distro.

 

Installing the Repo

Let’s start by installing the dummy package that gives us access to the MySQL Community repo using the following command:

# dnf -y install https://dev.mysql.com/get/mysql57-community-release-fc25-9.noarch.rpm

Installing the server

After that’s done, just install the server as follows

# dnf -y install mysql-community-server

Once the server is installed and you have the service running, you may have noticed that no root password was given to the installation process, meaning that you’ll have to reset it manually. This is a good exercise on how to recover (or reset) ROOT passwords that have been forgotten.

Changing the ROOT password

Follow the steps described bellow:

  1. Stop the server
  2. Set the –skip-grant-tables option
  3. Start the server
  4. Update the password directly on the mysql.user table
  5. Flush the privileges.
  6. Stop the server
  7. Unset the option
  8. Start the server
  9. (Optional) Set the password if you are asked for it.
# systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

# systemctl start mysqld

$ mysql -u root

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword') WHERE user = 'root' and host = 'localhost';

mysql> FLUSH PRIVILEGES;

# systemctl stop mysqld

# systemctl unset-environment MYSQLD_OPTS

# systemctl start mysqld

Now you can login with your new ROOT password!

If you get this error

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Execute this command

set password = PASSWORD('MyNewPassword');

Enjoy!

JS.OracleDatabase v0.2.2.3 is out of the oven!

Hello fellow technocrats, automation lovers and PowerShell enthusiasts!

Only a few days go I uploaded to the PowerShell Gallery Site the new alpha version of my Oracle Database Client-based Module.

Here’s a summary of the functions you can find in it.

PS > Get-Command -Module JS.OracleDatabase

CommandType Name                         Version Source 
----------- ----                         ------- ------ 
Function    Add-OracleDBLink             0.2.2.3 JS.OracleDatabase
Function    Get-OracleADDMInstanceReport 0.2.2.3 JS.OracleDatabase
Function    Get-OracleAWRInstanceReport  0.2.2.3 JS.OracleDatabase
Function    Get-OracleAWRReport          0.2.2.3 JS.OracleDatabase
Function    Get-OracleDBID               0.2.2.3 JS.OracleDatabase
Function    Get-OracleDBInfo             0.2.2.3 JS.OracleDatabase
Function    Get-OracleDBVersion          0.2.2.3 JS.OracleDatabase
Function    Get-OracleHosts              0.2.2.3 JS.OracleDatabase
Function    Get-OracleInstances          0.2.2.3 JS.OracleDatabase
Function    Get-OracleLongOperations     0.2.2.3 JS.OracleDatabase
Function    Get-OracleLongRunQueries     0.2.2.3 JS.OracleDatabase
Function    Get-OracleNames              0.2.2.3 JS.OracleDatabase
Function    Get-OracleOptions            0.2.2.3 JS.OracleDatabase
Function    Get-OraclePerfReports        0.2.2.3 JS.OracleDatabase
Function    Get-OracleServices           0.2.2.3 JS.OracleDatabase
Function    Get-OracleSessions           0.2.2.3 JS.OracleDatabase
Function    Get-OracleSize               0.2.2.3 JS.OracleDatabase
Function    Get-OracleSnapshot           0.2.2.3 JS.OracleDatabase
Function    Get-OracleSnapshotTime       0.2.2.3 JS.OracleDatabase
Function    Get-OracleSQLText            0.2.2.3 JS.OracleDatabase
Function    Get-OracleUsers              0.2.2.3 JS.OracleDatabase
Function    Ping-OracleDB                0.2.2.3 JS.OracleDatabase
Function    Remove-OracleDBLink          0.2.2.3 JS.OracleDatabase
Function    Remove-OracleSchema          0.2.2.3 JS.OracleDatabase
Function    Test-OracleDBLink            0.2.2.3 JS.OracleDatabase
Function    Test-OracleEnv               0.2.2.3 JS.OracleDatabase
Function    Test-OracleHealth            0.2.2.3 JS.OracleDatabase
Function    Use-OracleDB                 0.2.2.3 JS.OracleDatabase

Most of the functions rely on Use-OracleDB function which allows you to run any query you want and outputs the result set as objects.

Let’s say you want to know the version of the database.

You could easily run the following command:

PS-Dev > Use-OracleDB -TargetDB myorcldb -SQLQuery "SELECT * FROM v`$version;"

DBName   BANNER                                                                        CON_ID ErrorMsg
------   ------                                                                       ------ --------
myorcldb Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production      0 
myorcldb PL/SQL Release 12.1.0.2.0 - Production                                            0 
myorcldb CORE 12.1.0.2.0 Production                                                        0 
myorcldb TNS for Linux: Version 12.1.0.2.0 - Production                                    0 
myorcldb NLSRTL Version 12.1.0.2.0 - Production                                            0

You could then think about giving your output a more interesting format

PS-Dev > Use-OracleDB -TargetDB myorcldb -SQLQuery "SELECT banner AS `"ComponentName`" FROM v`$version;"

DBName   ComponentName                                                                ErrorMsg
------   -------------                                                                --------
myorcldb Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 
myorcldb PL/SQL Release 12.1.0.2.0 - Production 
myorcldb CORE 12.1.0.2.0 Production 
myorcldb TNS for Linux: Version 12.1.0.2.0 - Production 
myorcldb NLSRTL Version 12.1.0.2.0 - Production

You can see that the ErrorMsg attribute is present everytime, providing additional information in case there’s an error during running time.

PS-Dev > Use-OracleDB -TargetDB myorcldb -SQLQuery "SELECT banner AS `"ComponentName`" FROM v`$version_bad;"

DBName   ErrorMsg 
------   -------- 
myorcldb ORA-00942: table or view does not exist

Feel free to use it and ask me here or reach me at my LinkedIn Profile if you have any questions.

Happy scripting!