Category Archives: Databases

← Older posts
Newer posts →
21
06
19
 Leave a comment

How to change MySQL 5.7 root password

Posted by | MySQL |

First, check MySQL version:

SHOW VARIABLES LIKE "%version%";

+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.25                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.25-0ubuntu0.16.04.2 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+
(more…)
27
05
19
 Leave a comment

Accessing Oracle Database in C++ with MS VS2017.

Posted by | Oracle |

Download binaries (redistributables) and C++ SDK from Oracle website. For 64bit platform they are respectively:

instantclient-basic-windows.x64-18.5.0.0.0dbru.zip
instantclient-sdk-windows.x64-18.5.0.0.0dbru.zip

Extract them, create a C++ project in VS2017 and link oraocci18.lib in Release configuration and oraocci18d.lib in Debug configuration.

(more…)
14
05
19
 Leave a comment

How to get all the privileges of an Oracle database user.

Posted by | Oracle |

While investigating of how to get all the privileges of an Oracle database user I played with the following queries:

select COUNT(*) from DICTIONARY;
select USERNAME from SYS.ALL_USERS;

select * from DBA_USERS;
select * from DBA_ROLES;
select * from DBA_SYS_PRIVS where grantee = 'CONNECT';
select * from DBA_SYS_PRIVS where grantee = 'RESOURCE';
select * from DBA_SYS_PRIVS;
select * from SESSION_PRIVS;
select * from DBA_TAB_PRIVS where GRANTEE <> 'PUBLIC';
select count(*) from DBA_TAB_PRIVS;
select * from DBA_COL_PRIVS;
select * from ROLE_TAB_PRIVS;
(more…)
07
05
19
 Leave a comment

Installing sample Oracle Database schemas.

Posted by | Oracle |

I installed sample Oracle database schemas with the following steps:

Cloned the repository, changed the directories in the installation script, and started my docker container with the mapped folder:

sudo su - oracle
mkdir repos
cd repos
git clone https://github.com/oracle/db-sample-schemas.git
cd db-sample-schemas
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
exit
sudo docker run -d -it --rm --name oracle18se -v /home/oracle/oradata18:/opt/oracle/oradata -v /home/oracle/repos:/home/oracle/repos -p 1521:1521 -p 5500:5500 oracle/database-se:18.3.0
sudo docker logs oracle18se
(more…)
25
04
19
 Leave a comment

Oracle 19c Audit changes.

Posted by | Oracle |

In Oracle 19c the following fields of AUDIT_UNIFIED_ENABLED_POLICIES table were renamed:

  • ENABLED_OPT renamed with ENABLED_OPTION
  • USER_NAME renamed with ENTITY_NAME

So the audit options query in Oracle 19c looks like this:

SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up 
WHERE uep.ENTITY_NAME = 'ALL USERS' and uep.ENABLED_OPTION='BY USER' and uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE = 'STANDARD ACTION';
(more…)
14
03
19
 3 Comments

Configuring Oracle Database for Auditing.

Posted by | Oracle |

To enable Unified Auditing in Oracle Database 12 and 18 on Oracle Linux I did this:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
./sqlplus sys as sysdba
SHUTDOWN IMMEDIATE
EXIT
./lsnrctl stop
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
cd $ORACLE_HOME/bin
./lsnrctl start
./sqlplus sys as sysdba
STARTUP
(more…)
06
03
19
 3 Comments

Running Oracle database (v12,18,19) in a docker container on Ubuntu 18.04.

Posted by | Oracle | Tagged |

Clone the repository with the official Oracle docker images:

git clone https://github.com/oracle/docker-images

Install docker, download Oracle Database, put it to the directory docker-images/OracleDatabase/SingleInstance/dockerfiles/18.3.0 containing the Dockerfile and run the following commands to build and run Enterprise Edition:

cd docker-images/OracleDatabase/SingleInstance/dockerfiles/18.3.0
mv ~/Downloads/LINUX.X64_180000_db_home.zip .
# Fix a small bug in Dockerfile, see https://github.com/oracle/docker-images/issues/1468
sed -i 's/V981623-01/LINUX.X64_180000_db_home/g' Dockerfile
sudo docker build -t oracle/database:18.3.0 --build-arg DB_EDITION=EE .
sudo docker run -d -it --rm --name oracle18 oracle/database:18.3.0
sudo docker logs oracle18 --tail 100
sudo docker logs oracle18 | grep -i password

and the following commands to build and run Standard Edition 2 (it does not require V981623-01.zip):

sudo docker build -t oracle/database-se:18.3.0 --build-arg DB_EDITION=SE2 .
sudo docker run -d -it --rm --name oracle18se oracle/database-se:18.3.0
sudo docker logs oracle18se --tail 100

the first string of the docker output contains the generated password for SYS, SYSTEM and PDBADMIN.

(more…)
23
05
17
 Leave a comment

Backing up and restoring all the PHP MySQL websites on a Linux server

Posted by | MySQL, Ubuntu |

All the following commands assumes we saved MySQL root password into MROOTPASS variable:

export MROOTPASS=<mysql root password>

The most straight forward method to backup all the MySQL databases and all the website files (PHP scripts, images, etc..) stored in the /home directory is the following:

mysqldump --all-databases -u root -p$MROOTPASS | gzip > all-databases-$(date '+%Y-%m-%d_%H-%M-%S').sql.gz
tar -cvzf home.tar.gz /home

If we backup some individual database (probably not as root) and change its user while restoring it, it might make a sense to remove DEFINER from the output script:

sed -e 's/*]*\*/\*/'

The following commands restore all the websites from the archives:

gunzip -c all-databases-2017-05-23_15-31-00.sql.gz | mysql -u root -p$MROOTPASS
cd /
sudo tar -xvzf home.tar.gz

After migration from MySQL version  14.14 Distrib 5.5.54 to 14.14 Distrib 5.7.18 (I do not know what is the difference between them) I got the following error: “ERROR 1805 (HY000) at line 1: Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted” while trying to drop some user, and fixed it by running:

mysql_upgrade -u root -p$MROOTPASS
service mysql restart
06
10
16
 Leave a comment

Compiling GDAL on Ubuntu Linux with SQLite and MySQL support

Posted by | MySQL, SQLite, Ubuntu | Tagged |

Fist install MySQL client libraries and check their location:

apt-get install libmysqlclient-dev
find / -name '*libmysqlclient*'

Then install SQLite:

sudo apt-get install sqlite3 libsqlite3-dev

Extract GDAL sources and configure supported modules (MySQL and SQLite are not compiled by default, so we need to specify them explicitly):

unzip gdal211.zip
cd gdal-2.1.1/
./configure --with-sqlite3 --with-mysql

this will output “MySQL support: yes” and “SQLite support: yes” along with other information.

(more…)

02
09
13
 3 Comments

How to encrypt MySQL database in Ubuntu 12.04 LTS

Posted by | MySQL, Ubuntu |

encrypt MySQL databaseProbably, the easiest way to encrypt MySQL database in Ubuntu is by using ecryptfs-utils. Install ecryptfs-utils:

apt-get install ecryptfs-utils

Mount /usr/local/encrypted directory and create mdf directory for MySQL data files (you will be prompted for passphrase and other options):

mkdir /usr/local/encrypted
mount -t ecryptfs /usr/local/encrypted /usr/local/encrypted
cd /usr/local/encrypted
mkdir mdf
chug.sh mysql mdf
chmod og-rwx mdf

(more…)

← Older posts
Newer posts →