MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

Had a some conversation with friend he is facing this issue for long back and unable to solve it . But i had an thought why we cant replicate in our test environment and fix this quickly . After a long time had a nice troubleshooting hours 

Lets deep dive into the troubleshooting , before enter into the troubleshooting requested few sample output for the same 

Outputs for the issues : 

First things requested to login without grant tables and asked to repair the tables  

root@ip-100-23-45-122:sudo mysqld_safe --skip-grant-tables --skip-networking --skip-plugin-load &

When trying to repair it mysql database most of the tables got corrupted , So corruption happened on the master tables itself ,

root@ip-100-23-45-122:/var/lib/mysql/mysql# mysqlcheck -u root -p --repair --databases mysql

Enter password: 

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.engine_cost

Error    : Table 'mysql.engine_cost' doesn't exist

status   : Operation failed

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.gtid_executed

Error    : Table 'mysql.gtid_executed' doesn't exist

status   : Operation failed

mysql.help_category

Error    : Table 'mysql.help_category' doesn't exist

status   : Operation failed

mysql.help_keyword

Error    : Table 'mysql.help_keyword' doesn't exist

status   : Operation failed

mysql.help_relation

Error    : Table 'mysql.help_relation' doesn't exist

status   : Operation failed

mysql.help_topic

Error    : Table 'mysql.help_topic' doesn't exist

status   : Operation failed

mysql.innodb_index_stats

Error    : Table 'mysql.innodb_index_stats' doesn't exist

status   : Operation failed

mysql.innodb_table_stats

Error    : Table 'mysql.innodb_table_stats' doesn't exist

status   : Operation failed

mysql.ndb_binlog_index                             OK

mysql.plugin

So further we need to try something to resolve it . I have replicated same issues with my local environment and followed below steps in the local 

Troubleshooting Step 1 : 

Installed separate mysql instance with different port and copied all user database directories to new server then tried to start mysql its started also 

But when trying to access the same user table got an same error 

MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

 So issue not with master table corruption , something different 

Troubleshooting Step 2 : 

I have gone through so many links and references tried below things as well ,

• Stop the MySQL then Remove ib_logfile0 , ib_logfile1 . Again start the MySQL Server - Its not worked 
• Move corrupted ibdata1 file in the newly installed MySQL server and then restart MySQL Server - Its not worked 
• More links requested to restart multiple times its very funny thing but tried it will or not - Its also not worked 

Finally complete MySQL troubleshooting reference  resolved this issue with replacing tablespace method 

Troubleshooting Step 3 :

As per reference link mentioned below Restoring Orphan File-Per-Table ibd Files then its resolved and able to retrive the records from the table 

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html

Again installed one more MySQL Server and Created dummy database as datablogs and created only structure from the sample 

Note : Only if you have structure of the table its possible to recover 

mysql> create database datablogs;

Query OK, 1 row affected (0.01 sec)

mysql> use datablogs;

Database changed

mysql> CREATE TABLE tbl_followup (

       id int(11) NOT NULL AUTO_INCREMENT,

       table_header text,

       action varchar(100) DEFAULT NULL,

       action_button_text varchar(100) DEFAULT NULL,

       parent_template text,

       created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

       modified_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

       PRIMARY KEY (id)

     ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE datablogs.tbl_followup DISCARD TABLESPACE;

Query OK, 0 rows affected (0.01 sec)

Copied only tbl_followup.ibd file from the corrupted server to new server then imported the tablespace 

mysql> ALTER TABLE datablogs.tbl_followup IMPORT TABLESPACE;

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select * from datablogs.tbl_followup;

Empty set (0.01 sec)

So overall while reading it very simple but its very hard troubleshooting did in lifetime !!!

Anyway instead of doing multiple solution follow my steps to resolve the issue sortly !!!

Cross region RDS replication with customized parameter group

Replicate with customized parameter group ..

https://medium.com/@datablogs/cross-region-rds-replication-with-customized-parameter-group-fc29bf991957

Feeling Proud !!!

 Most Viewed Blog of the Year . Its on Second Page 

Capture Basic MySQLRemoteServerDetails

Basic Requirement to get the below details from MySQL Remote Host Using Python

1. OS Version
2. Find Physical host / VMWare
3. MySQL Port
4. Total RAM / Total CPU and Cores
5. How many Database and Size ?
6. Disk space

https://github.com/selvackp/MySQLRemoteServerDetails.git

# Required libraries for program
import paramiko
from mysql.connector import connect, Error

# Required libraries to connect
client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())

# Remote server details to enter
print('Enter the RemoteHost Login Details')
print('----------------------------------')
print('\t')
re_host_IP = input("RemoteHost IP: ")  # If any IP works , pass that to RemoteHost IP
re_host_user = input("RemoteHost Username: ")
re_host_password = input("RemoteHost Password: ")  # If no password for AWS machine just enter to fill the next details
re_host_key_file = input(
    "RemoteHost keyfile name: ")  # If no keys just enter to move next step . We have tested AWS machine , so its required to pass pem key file to connect .
print('\t')

try:
    # Remote Host Server Connectivity
    client.connect(re_host_IP, username=re_host_user, password=re_host_password, key_filename=re_host_key_file)
    stdin, stdout, stderr = client.exec_command('cat /proc/version')
    print('Remote Server OS and Version')
    print('----------------------------')
    print(stdout.readlines()[0])
    print('\t')
    print('Remote Server Total CPU and Cores')
    print('---------------------------------')
    stdin, stdout, stderr = client.exec_command('lscpu')
    print('Total CPU/Core count for Remote server: ', stdout.readlines()[3])
    print('\t')
    print('Connected Server Total RAM in GB')
    print('--------------------------------')
    stdin, stdout, stderr = client.exec_command('vmstat -s')
    print('Total RAM for Remote server: ', stdout.readlines()[0])
    print('\t')
    print('Connected Server Total Disk Spaces')
    print('----------------------------------')
    stdin, stdout, stderr = client.exec_command('df -h --total')
    print('Total Disk for Remote server: ', stdout.read().decode())
    print('\t')

    # MySQL Basic Details code below
    with connect(
            host=re_host_IP,
            user=input("Enter Remote MySQL Username: "),
            password=input("Enter Remote MySQL Password: "),
    ) as connection:
        print('\t')
        get_database_host = "select @@hostname"
        with connection.cursor() as cursor:
            cursor.execute(get_database_host)
            database_host = cursor.fetchone()
            print("Successfully connected Remote MySQL Server :", database_host[0])
            print('\t')
        print(database_host[0], 'Database Name and Size in MB')
        print('----------------------------------------------')
        get_database_size = "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;"
        with connection.cursor() as cursor:
            cursor.execute(get_database_size)
            database_size = cursor.fetchall()
            for row in database_size:
                dbname, dbsize = row
                print('{} - {}'.format(dbname, dbsize))
        select_port = 'SELECT @@port'
        with connection.cursor() as cursor:
            cursor.execute(select_port)
            database_port = cursor.fetchone()
            print('\t')
            print('Database Port :')
            print('---------------')
            print(database_host[0], 'Server MySQL Port is :', database_port[0])
        client.close()

except Error as e:
    print(e)
    exit()

Multi Source Replication from GCP Cloud MySQL to Percona XtraDB Cluster

It is easy to migrate the MySQL databases from one cloud provider to another cloud provider , but without downtime is little difficult to migrate the 1 TB of data . Using GTID based replication is easy to achieve in few hours of effort

Before go into detail , we will go through explanations of each things,

What is Cloud MySQL ?

Google Cloud Managed service for MySQL Database servers . It is supporting MySQL 5.6 and 5.7 with first and second generation instances . First and Second generation differs with allocating RAM and Storage

What is Percona xtradb cluster ?

Synchronous multi-master replication using percona MySQL server and percona xtrabackup using galera Library . As recommended it should contains odd number of nodes and same data spanned with all nodes

What is multi-source replication ?

MySQL multi-source replication enables a replication slave to receive transactions from multiple immediate masters in parallel

In this scenario , we have to migrate more than 3 GCP Cloud MySQL Managed instances into

Percona Galera Cluster . Before proceed to migrate the data , it is requires to compare MySQL 5.7 and Percona MySQL 5.7

https://www.percona.com/doc/percona-server/5.7/feature_comparison.html

So that we can avoid impact after migrating the data into new server

Instructions follow to setup multi source replication ,

• In GCP MySQL Instance , take a user database backup from each instances separately through mysqldump or using export in GCP GUI Console and move to backup files to destination server using gsutil or scp utility

mysqldump --user=root --password --host=gcpinstance1 sourcedb1 > mysqldump1.sql

mysqldump --user=root --password --host=gcpinstance2 sourcedb2 > mysqldump2.sql

• Before goes to next steps , we have to ready with three node percona XtraDB cluster environment before proceeds with next steps
• In Percona XtraDB Cluster end , need to modify below server variables in my.cnf file for GTID based replication and restart mysql service

[mysqld]
server-id=[SERVER_ID]
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
replicate-ignore-db=mysql
binlog-format=ROW
log_bin=mysql-bin
expire_logs_days=1
read_only=ON

Same Changes needs to edit for additional node of percona cluster

• Restore multiple source cloud mysql backup into one percona cluster instance

mysql --user=root --password --host=perconainstanceslave < mysqldump1.sql

mysql --user=root --password --host=perconainstanceslave < mysqldump2.sql

• Once restore is completed , we need to add multiple source of GCP Cloud MySQL instances into single percona cluster instance using below command

CHANGE MASTER TO MASTER_HOST="gcpinstance1", MASTER_USER="slaveuser1",MASTER_PASSWORD="slaveuser1", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

CHANGE MASTER TO MASTER_HOST="gcpinstance2", MASTER_USER="slaveuser2",MASTER_PASSWORD="slaveuser2", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

One we execute this , we will get below error due to applied transactions exists already in slave server or we will get duplicate transactions issues commonly

Slave_IO_Running: No

Slave_SQL_Running: Yes

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

• So,We need to get gtid_purged values from each separate source backups to ignore the deleted transactions in the binary log and troubleshoot above issues

Get gtid values

cat mysqldump1.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'''

cat mysqldump2.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'''

• To set global GTID_PURGED values , needs to do reset master in percona galera cluster . If server is in cluster not possible to execute reset master command . Needs to disable wsrep_provider variable in wsrep.conf to execute reset master and restart mysql service in slave server

#wsrep_provider = /usr/lib/galera/libgalera_smm.so

• Once mysql service is restarted , we can able to do reset master in percona galera cluster server and set GTID_PURGED values

Set GTID_PURGED Value :

Configure gtid values

Check Show Slave Status G :

Slave Status

We able to see slave server is get synced in few minutes , and seconds_behind_master is 0

Check Show Processlist :

Highlighted the multi master servers ( two servers ) is syncing to slave server

Processlist

Once completed all the steps , enable cluster wsrep_provider variable in wsrep.conf and restart mysql service in slave server

Verify once again the slave status and processlist :)

References :

External Replica Setup : https://cloud.google.com/sql/docs/mysql/replication/configure-external-replica

Get gtid_purged : https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source-provision-slave.html

Set gtid_purged and troubleshoot : https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source-provision-slave.html

MySQL GTID Replication and lower_case_table_names

Error 'Table 'EMPLOYEES.POSITION' doesn't exist' on query. Default database: 'employees'. Query: 'ALTER TABLE EMPLOYEES.POSITION ADD COLUMN phone VARCHAR(15)'

Interesting , table exist on slave server . But we are getting above error frequently and unable to broken the replication because database size is too big

We are having environment as GTID replication setup with windows server (Master) to Ubuntu Linux machine (Slave) . When we dig into all the findings , concluded its may be with case sensitivity issue . Lower_case_table_names variable value is same on both servers . But as per MySQL documents 

Database and table names are not case-sensitive in Windows , but are case-sensitive in most varieties of Unix.Column,index,stored routine, and event names are not case-sensitive on any platform,nor are column aliases

So , decided to tackle the current situation without data loss in slave server . Changing the lower_case_table_names is not best practice and it will corrupt case_sensitive names data files . 

we have stopped the slave , executed all the DML and DDL statements in slave server to match the schema deployments with master server .

And followed GTID replication error skipping process to skip the transactions manually handled in slave servers 

Capture the error GTID with master binlog files

root@master:/var/log/mysql# mysqlbinlog --start-position=904 mysql-bin.000004

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#200102 11:35:24 server id 1 end_log_pos 123 CRC32 0x14c9c2f7 Start: binlog v 4, server v 5.7.28-0ubuntu0.16.04.2-log created 200102 11:35:24

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

fNUNXg8BAAAAdwAAAHsAAAABAAQANS43LjI4LTB1YnVudHUwLjE2LjA0LjItbG9nAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AffCyRQ=

'/*!*/;

# at 904

#200102 12:06:51 server id 1 end_log_pos 969 CRC32 0x3564eba7 GTID last_committed=4 sequence_number=5 rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= '69b98063-2d31-11ea-9586-12b321a8670d:78'/*!*/;

# at 969

#200102 12:06:51 server id 1 end_log_pos 1042 CRC32 0xb527b5d3 Query thread_id=328 exec_time=0 error_code=0

Skipped the binlog position 904 transactions in slave server

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SESSION.GTID_NEXT= '69b98063-2d31-11ea-9586-12b321a8670d:78';

Query OK, 0 rows affected (0.00 sec)

mysql> begin ; commit;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET gtid_next=automatic;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Finally , GTID Replication begins to sync pending SQL threads and upto date the data with Master server !!!

Sensitive Data Cleaning with MasKING

Wow !!! Its easy too restore sensitive data without any fear !!!

Its really tough sometimes , we restored the sensitive data without knowing and test mails triggered to customer as $100 deducted from your account for purchase . Its strange scenario when we missed to cleansing the customer data in DEV Sandbox !!!

Yes MasKING sensitive / Credential is easy now !!! Reference : https://github.com/kibitan/masking

Just tried simple practice for masking the paymentdb table data with masking , Its working as expected

Step 1 : Installed latest Ruby version and masking using below commands . Before doing install , update the server with latest packages

rvm install ruby-2.6.3

gem install masking

Step 2 : Create masking.yml file anywhere in your linux machine . I have created the file inside etc directory with below values

In this address is table in paymentdb and name , email are few columns in address table

Step 3 : Now ready to take backup with masking utility of paymentdb

mysqldump --complete-insert -u root -p paymentdb | masking -c /var/lib/mysql/masking.yml > /tmp/paymentdb.sql

                                

Step 4 : Restoring the paymentdb backup database into paymentdb_masked

mysql -u root -p paymentdb_masked < /tmp/paymentdb.sql

Step 5 : Yay !!! Data has been successfully masked with masking utility . Results are below

Yup !!! Saved one more escalation !!! Thanks to MasKING :)

Repair GTID Based Slave on Percona Cluster

Problem : 

We are running 5 node percona cluster on Ubuntu 16.04, and its configured with master-slave replication. Suddenly we got an alert for replica broken from slave server, which was earlier configured with normal replication 

We have tried to sync the data and configure the replication, unable to fix that immediately due to huge transactions and GTID enabled servers. So we have decided to follow with innobackupex tool, and problem fixed in 2 hours 

Followed all the steps from percona doc and shared the experience in my environment 

Steps involving to repair the broken Replication :

1.Backup master server 

2.Prepare the backup 

3.Restore and Configure the Replication

4Check Replication Status

1.Backup master server 

We need to configure the complete master server database into a slave. So we are taking a full backup from the master server, before proceeding the backup we should check disk space available for the backup because of its system-level backup 

We have created a specific user for taking a backup from master server, once the backup is completed will get OK like below,

2. Prepare the backup for Restore

We need to prepare the backups to apply the transaction logs into data files, once it is OK, data files are ready to restore 

Before moving the prepared files into slave server, verify the GTID information from xtrabackup_binlog_info 

3.Restore and Configure the Replication

We can restore the backup else to create a new data directory and move the files into the directory. Stop mysql service before start restoring

innobackupex --copy-dir /mnt/2019-11-26_09-35-31

Default it will restore the data related files in server datadir , using the server my.cnf configuration file .Once changed the data directory, we need to change the owner and permission of the MySQL data directory 

chown mysql:mysql /mnt/mysqldatanew

And restart the service with a new data directory, once its started login with master MySQL root user password. Because we have taken file backup from the master so metadata will be the same of master

Execute below commands to configure the replication 

4. Check Replication Status

Once slave is configured, verify the replication status as below 

Also, Slave has retrieved a new transaction 

Thanks for Reading !!!

MySQL for Entity Framework

The tutorial guides you through MySQL Database connectivity with Entity Framework

We are facing entity framework database provider compatible with version could not be found for your data connection issue while connecting with MySQL 5.7 and 8.0. Problem with MySql.Data.Entity.EF6.dll missing and not able to connect with Entity framework

As per my requirements, the customer wants to migrate MSSQL to MySQL server. So I want to use any version of MySQL, installed with below configurations and achieve the connectivity 

1.MySQL Server : 5.6.41
2.MySQL Connector/Net : 6.9.12
3.Entity Framework : 6.2.0
4.Visual Studio : Professional 2017

Configuration steps are below,

1. Download required versions,

MySQL - https://dev.mysql.com/downloads/windows/installer/5.7.html

Visual Studio - https://visualstudio.microsoft.com/downloads/

2. Once downloaded, Launch web API project and Install entity framework 6.2.0 through NuGet Packages 

3.Install the MySQL 5.6 with Connector/Net 

Once you installed, you will see below dll files local server 

C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.12\Assemblies\v4.5

4. Add the dll files in Project reference and rebuild the project 

Once we rebuild, open the web.config file add the below lines if already provider tag part exist remove and add it 

5. Again rebuild the project and add the connection. You will successfully be connected

Thanks for reading ...

Deploy a MySQL Cluster step by step in Linux

Before proceeding installation things , go through cluster basics from the MySQL website and forums. It will be helpful to easily understand installation and configuration. First time is difficult to get things to done in one shot is not possible , no problem will learn it .

MySQL Cluster Installation will be mostly done by two ways.,

1. Using binaries
2. rpm(Rethat,CentOS..,)

 Different Linux flavors has different methods to install format , here I have explaining with CentOS7 .

1.Have prepared two different machines to install MySQL Cluster . First you have to login two different machines

Two VM Machines

2.Be ready with mysql rpm's

3.Now get ready to start installation of MySQL Cluster

Create mysql user ...

4.Install downloaded Server and Client rpm's in both VM

5.MySQL Server needs to be install fully

6.Please verify cluster/mysql folder,once installation is completed

All files needs to be there , for MySQL Cluster you will get new database called ndbinfo .

7.This is time to create configuration file for mysql cluster environment

Only two configuration files needs to create for mysql cluster

• my.cnf for mysql (SQL node)
• config.ini for cluster 

Config.ini file for my cluster environment below

[ndbd default]

# Options affecting ndbd processes on all data nodes:

NoOfReplicas=2 # Number of replicas

#DataMemory=80M # How much memory to allocate for data storage

#IndexMemory=18M # How much memory to allocate for index storage

# For DataMemory and IndexMemory, we have used the

# default values. Since the "world" database takes up

# only about 500KB, this should be more than enough for

# this example Cluster setup.

#[tcp default]

# TCP/IP options:

# portnumber=2202 # This the default; however, you can use any

# port that is free for all the hosts in the cluster

# Note: It is recommended that you do not specify the port

# number at all and simply allow the default value to be used

# instead

[ndb_mgmd]

# Management process options:

NodeID=1

hostname=192.168.159.131 # Hostname or IP address of MGM node

[ndb_mgmd]

# Management process options:

NodeID=2

hostname=192.168.159.134 # Hostname or IP address of MGM node

[ndbd]

# Options for data node "A":

# (one [ndbd] section per data node)

NodeID=3

hostname=192.168.159.131 # Hostname or IP address

datadir=/var/lib/mysql-cluster/ # Directory for this data node's data files

[ndbd]

# Options for data node "B":

NodeID=4

hostname=192.168.159.134 # Hostname or IP address

datadir=/var/lib/mysql-cluster/ # Directory for this data node's data files

[mysqld]

# SQL node options:

NodeID=5

Same file you have to prepare and place in /var/lib/mysql-cluster directory . If any single word is missed cluster will not be start properly.

As per standalone mysql setup you have to prepare my.cnf file for SQL Node 

8.This is very important step you follow before you get into cluster environment

You have to start cluster as below.,

1. Start management node in both mysqlone and mysqltwo 

     2.  Start data node in both mysqlone and mysqltwo

     3.  Start SQL node in both mysqlone and mysqltwo , after data node is up . If you start before data node might be crash.

 9.Now you have to check cluster status in any one of VM machine , using ndb_mgm utility

Once you start the data node , it will be in starting mode . Meanwhile you have to check howmuch data and index memory cluster is using as below.,

It will be start slowly never expect as much as you started , once data and management node start you will see like below.,

if data and index usage is above 90% you have to increase RAM memory and increase data memory , index memory parameter changes in config.ini file .

10.After you have to started Data node you will needs to start the SQL Node as said before

Once you started management,data and sql node in order wise . You ready with access high availability feature in MySQL .

Please contact me if you need further assistance on MySQL support , Thanks in advance .

MySQL Server Instance Configuration Wizard (Windows is Not Responding)

On windows me had this problem two times , first in my own laptop , second time in my friend laptop . I took long time to resolve this issue but before done so many methods to resolve it .

I will share the experience to resolve the instance configuration error step by step,

Error : MySQL Server Instance Configuration Wizard (Windows is Not Responding)

Steps to resolve it ,

First we clear when you get this error,in my experience first time had installed the MySQL server and deleted MySQL folder in my machine or you have tried to uninstall and install MySQL Server.

Step 1 : In your windows installation driver , you have Program files(x64),Program files and Program Data.You need to delete MySQL Folder in these three folders.But few of you installed InnoDB Table Settings in some other drive , so you need delete in that installed driver.I have installed into D drive.

Step 2 : Uninstall MySQL Server in Control Panel\All Control Panel Items\Programs and Features\MySQL

Step 3 : In your keyboard press HOME+R in this cmd prompt type regedit you will get registry editor HKEY_LOCAL_MACHINE-->System-->CurrentControlSet-->services-->Delete MySQL folder with subkeys.

Step 4 : Now you have completely removed mysql server folder in your machine . Then why waiting for next step lets start MySQL Server installation.

Step 5 : Will we meet on next issues.,

Experience with McAfee Mysql Audit Plugin

This blog provides step by step tutorial for setting up McAfee MySQL Audit Plugin in MySQL-Server-5.5.37 and platform Linux 2.6.39-400.17.1.e16uek.x86_64 #1

It is difficult task comparing with others auditing tasks.Auditing much important for all DBMS.Comparing with other DBMS MySQL auditing need to be third party plugins otherwise use MySQL Enterprise Audit Log Plugin.Using general log its performance degradation in production environment.

Third party plugin as McAfee MySQL Audit Plugin.McAfee and this plugin provide audit capabilities for MySQL Database and MySQL started supporting for plugins API since MySQL 5.1.

Below processes successfully tested in my local environment.

Prerequisites:

• Installing McAfee Audit Plugin needs MySQL 5.1 to latest version of MySQL Server.
• Only Linux Environment is feasible for McAfee Audit Plugin.

Step 1 : Download Audit plugin for your version

[root@selva]# wget https://github.com/downloads/McAfee/mysql-audit/audit-plugin-MySQL-5.5-1.0.5-473-Linux-x86_64

Step 2 : Extract the audit-plugin-mysql-5.5-1.0.5-473-linux-x86_64.zip

[root@selva]# unzip audit-plugin-mysql-5.5-1.0.5-473-linux-x86_64.zip

Step 3 : Check the location of plugin directory in Linux using MySQL server 

[root@selva]# mysql -u root -p
mysql>show global variables like 'plugin_dir';

The plugin_dir values in my environment /usr/lib64/mysql/plugin/

Step 4 : Copy the unziped plugin into plugin directory

[root@selva]# cp ./audit-plugin-mysql-5.5/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/

Step 5 : When we done copy the plugin into directory use install plugin following the command

mysql>INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

When you installing this command you lucky gey Query OK result.Otherwise you will get any of below different errors in installation,

• SQL Error (1126):can't open shared library '/usr/lib64/mysql/plugin/libaudit_plugin.so'(errno:2 /usr/lib64/mysql/plugin/libaudit_plugin.so:undefined symbol:_cxa_pure_virtual)
• SQL Error (1123):can't initialize function 'AUDIT';Plugin initialization function failed.

Using this command you will get error log path for mysql server 

mysql>show global variables like 'log_error';

You will get these error in log_error file , 

• 140417 12:06:07 [Note] Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1
• 140417 12:06:07 [Note] Audit Plugin: mysqld: /usr/sbin/mysqld (b77de3909af62707ca068be61a64f406)
• 140417 12:06:07 [Note] Audit Plugin: Couldn't find proper THD offsets for: 5.5.37
• 140417 12:06:07 [ERROR] Plugin 'AUDIT' init function returned error.
• 140417 12:06:07 [Note] Audit Plugin: deinit

Never get worried failure leads to success , Use correct version of audit plugin you will get install soon.Once you struggling in this do one more things,try this all plugin for your required environment surely you will get result.Follow below link to get all versions audit plugins,

http://dl.bintray.com/mcafee/mysql-audit-plugin/#audit-plugin-mysql-5.5-1.0.5-464-linux-i386.zip

Step 6 : Once you successfully installed , check 

mysql>show plugins;

You will get list of plugins installed in the mysql server.You can find audit plugin version you have installed in the mysql server,

mysql>SHOW GLOBAL STATUS LIKE 'AUDIT_version';

Step 7 : Once audit plugin installed and loaded into mysql server , we can set the audit plugin confguration system variables,check available audit plugin variables using below command,

mysql>show global variables like 'audit%';

Check available audit plugin configuration variables below link,

https://github.com/mcafee/mysql-audit/wiki/Configuration 

Step 8 : We must need to set only three or four configuration variables , changes made in my.cnf file or via command.we can set this using command,no need to add via my.cnf file.

All audit activities store in the JSON format.It supports directly auditing activities to a file or a Unix socket .

we need to set , audit_json_file disable/enable , audit_record_cmds like DDL or DCL and Connect,Failed Login,Quit , audit_record_objs like databases (mysql,*).

mysql>set global audit_json_file=1;
mysql>set global audit_record_cmds='Connect,Quit,Failed Login,insert,drop,create';
mysql>set global audit_record_objs='mysql,sakila';

After completing this check again , for all configuration variables is updated .

mysql>show global variables like 'audit%';

Step 9 : Now check the mysql-audit.json file in mysql datadir it will update the result.

Note:Will Truncate the mysql-audit.json file will be replace the data once you have did any changes in databases.If you deleted the mysql-audit.json need to install 'libaudit_plugin.so' again.

Step 10 : Steps will not complete mysql-audit.json file will growth like general log , so we need to backup or update into another file , keep log in separate json files . forthcoming posts will update export json file data into mysql table and the processes.

Thank You,

Be Better Than You Were Yesterday