mtheDBA

In this blog post we will see how to monitor MySQL databases using the open source monitoring tool provided by Percona, referred as Percona Monitoring and Management (PMM). About PMM: Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance. It is developed by Percona in collaboration with experts in the field of managed database services, support and consulting. PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible. Basic Requirements: 1. Need at least one Linux server to install PMM Client and PMM Server. 2. Need Operating System - root user credentials 3. Default port's(80) have to be open if using to or more servers. 4. A MySQL user is required to capture the Queries in the Database. 5. A Docker se...

In this post, we will see how to Install and Run PostgreSQL on Linux machines. There are three ways to installation procedures to download the software. 1. Binary Installation 2. Yum Installation 3. RPM Installation In this post, will see how to install PostgreSQL using Binary installation. 1. Download the binaries from the official website. $ wget https://get.enterprisedb.com/postgresql/postgresql-9.6.2-4-linux-x64-binaries.tar.gz 2. Untar the downloaded binaries. $ tar -xvzf postgresql-9.6.2-4-linux-x64-binaries.tar.gz 3. Create appropriate data directories and permissions with root user to run PostgreSQL Server. # mkdir -p /var/lib/pgsql/data # chown -R psql /var/lib/pgsql/data # su psql 4. Now change the directory path to the downloaded software path $ cd /home/psql/softwares/pgsql 5. Initialize the data directories required for PostgreSQL to run, there are two ways to initialize the directories. $ initdb -D /usr/local/pgsql/data Alternatively, we ...

As we know that MySQL come up with JSON datatype equivalent(but not completely) to MongoDB's Document Store Database. In this post, we are going to see how to Install and Use MySQL's new JSON datatype. Installing MySQL - JSON Plugin: 1. In order to use JSON datatype in MySQL, we need to have MySQL 5.7.12 or higher versions. Download the latest version of MySQL 5.7.12 or higher from here. 2. Installing the X Plugin A non-root user will have access to install the X Plugin on MySQL database, since he have to perform INSERT operations on the database. There are 3 types of Plugin installations available.  Using MySQL installer for Windows: Launch MySQL Installer for Windows. Click the Reconfigure option. Use Next and Back to configure for following actions: In Accounts and Roles , confirm the current root account password In Plugin and Extensions , check mark the Enable X Protocol/MySQL as a Document Store check box. In Apply Server Configuration , click...

While starting the server you may find the below errors sometimes, it's because of unintentionally deleting the MySQL Binary Log files. When starting mysql if the server unable to find the mysql-bin-log files then below errors will arise. 170418 12:15:19 InnoDB: 5.5.30 started; log sequence number 32347593422 /usr/sbin/mysqld: File './mysql-bin.000023' not found (Errcode: 2) 170418 12:15:19 [ERROR] Failed to open log (file './mysql-bin.000023', errno 2) 170418 12:15:19 [ERROR] Could not open log file 170418 12:15:19 [ERROR] Can't init tc log 170418 12:15:19 [ERROR] Aborting 170418 12:15:19 InnoDB: Starting shutdown... 170418 12:15:20 InnoDB: Shutdown completed; log sequence number 32347593422 170418 12:15:20 [Note] /usr/sbin/mysqld: Shutdown complete Do we have a solution to get rid of this and start MySQL again? Well, we have two types of workarounds to start MySQL again. 1. Deleting the missed files in binlog .index  file 2. Disabling the binlog log...

We usually use MySQL login information on shell scripts to load some data into database or to fetch some monitoring information. To login to the database will write login information on Shell Scripts as below #!/bin/sh username = someusername password = somepassword $ mysql -u$username -p$password -A -v -S By doing that so, we will hit a warning in stdout saying: Warning: Using a password on the command line interface can be insecure To avoid the warning in shell script, we need to so some workaround like below: 1. Create a hidden file configuration file > touch /tmp/.mysqllogin.cnf 2. Enter your database credential information on the hidden file: > vi /tmp/.mysqllogin.cnf [client] user=someusername password=somepassword 3. Save the file and try to login to mysql using  --defaults-extra-file  keyword like $ mysql --defaults-extra-file=/tmp/.mysqllogin.cnf -A -v By using this workaround you can avoid the warning.

Recently while installing Percona MySQL 5.7.17 in an Amazon EC2 instance identified couple of Errors. The resolutions are quite simple and easy. Will have a keen look into this by installing MySQL step by step: 1. Download Percona from the official website $ wget https://www.percona.com/downloads/Percona-Server-LATEST/Percona-Server-5.7.17-12/binary/tarball/Percona-Server-5.7.17-12-Linux.x86_64.ssl101.tar.gz 2. Untar/Unzip the Binaries $ tar -zxvf Percona-Server-5.7.17-12-Linux.x86_64.ssl101.tar.gz 3. After successful unzip of software, tried to install the default mysql database using mysql_insall_db command,. Note: From MySQL 5.7 mysql_install_db path has changed from scripts/mysql_install_db to bin/mysql_install_db After running the below command: $ bin/mysql_install_db --defaults-file=/etc/my.cnf --datadir=/data/mysql/data/ Got the below error [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize [ERROR] Child process:...

What's new in MySQL 5.7:          Configuration Changes          System Table Changes          Server Changes          InnoDB Changes          SQL Changes Configuration Changes: 1.      The information_schema has table’s that contains system and status variables (Global Variables and Session Variables). As of MySQL 5.7.6 the performance_schema also contains same tables, those are actually intended to replace in information_schema which are deprecated as of MySQL 5.7.6, and will be removed in future releases. 2.      As of MySQL 5.7.6 mysqld_safe is no longer installed on Linux systems on which MySQL installed using RPM packages, the server startup and shutdown will be managed by using the system d service. 3.      As of MySQL 5.7.6 and later mysql_install_db is moved from s...

Import of a MySQL 5.5 dump followed by the  mysql_upgrade  command while GTIDs are enabled causes troubles with some MySQL 5.6 releases. Do the upgrade with  --gtid-mode=0  and enable it later on. Some  default values have changed : The most important one is  innodb_file_per_table = 1 . Some old variables and some deprecated commands have been removed. So test carefully! The  Query Cache  is disabled by default in 5.6. Change to the previous behavior by setting  query_cache_type=1  in  my.cnf 8 new reserved key words have been defined. The most important ones are  get  and  partition . Implicit  GROUP BY  sorting in  MySQL 5.6  is deprecated. When upgrading a master-slave setup from 5.5 to 5.6 without downtime, take care: MySQL 5.6 (by default) writes checksums into the binary log which MySQL 5.5 does not understand, so the 5.5 IO slave reports an error and replication stops. It’s beca...

Load data infile will read the content from the text file and insert into table. This process is very fast in MySQL. Below is the Syntax suggested by MySQL in their documentation: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ' file_name ' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION ( partition_name ,...)] [CHARACTER SET charset_name ] [{FIELDS | COLUMNS} [TERMINATED BY ' string '] [[OPTIONALLY] ENCLOSED BY ' char '] [ESCAPED BY ' char '] ] [LINES [STARTING BY ' string '] [TERMINATED BY ' string '] ] [IGNORE number {LINES | ROWS}] [( col_name_or_user_var ,...)] [SET col_name = expr ,...] Will look into some of the procedures to load data into table using an external text file: Procedure 1: Text file contains header: LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE ...

In the earlier post we discuss about how to take backup using mysqldump. Today will discuss about how to take backup using Percona XtraBackup. The Percona XtraBackup is a Physical Dump / Backup which consists of raw copies of data and data directories present in it. This type of backup is very useful and suitable for the databases which contains large and important data sets that need to recovered quickly when the disaster occurs. Creating a Backup: To create a backup, Xtrabackup script has to be called with backup, datadir and targetdir parameters. backup - is to create the backup datadir - which is where the MySQL data directory stored targetdir - backup directory, where we want to save the backup If the target directory does not exist,  xtrabackup  creates it. If the directory does exist and is empty,  xtrabackup  will succeed.  xtrabackup  will not overwrite existing files, it will fail with operating system error 17,  file   exists ...