Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Sunday, 20 May 2018

How to configure MySQL Replication using Percona XtraBackup without any downtime?

Configure Replication in Consistent Mode
-No Downtime
With
Percona XtraBackup


Before starting the replication with Percona XtraBackup, You will need to check some points and key notes as mentioned below.

Summary: All the things you will need
ü Installing Percona XtraBackup from Percona yum repository (Skip if already installed)
ü Make a backup on Master and prepare it using XtraBackup
ü Move existing Data on Slave to other Folder
ü Copy backed up data to Slave.
ü Configure the Master MySQL server.
ü Configure the Slave MySQL server.
ü Start the replication

All the things you will need:

Setting up a slave for replication with Percona XtraBackup is really a very straightforward procedure. In order to keep it simple, here is a list of the things you need to follow the steps without hassles:
The Master db01 system with a MySQL-based server installed, configured and running. This system will be called The Master, as it is where your data is stored and the one to be replicated. We will assume the following about this system:
  • The MySQL server is able to communicate with others by the standard TCP/IP port;
  • The SSH server is installed and configured;
  • You have a user account in the system with the appropriate permissions;
  • You have a MySQL’s user account with appropriate privileges.
  • Server has binlogs enabled and server-id set up to 1.

The Slave db02 another system, with a MySQL-based server installed on it. We will refer to this machine as The Slave and we will assume the same things we did about The Master, except that the server-id on the Slave is 2.
Percona XtraBackup The backup tool we will use. It should be installed in both computers for convenience.

Note: It is not recommended to mix MySQL variants (Percona Server, MySQL, MariaDB) in your replication setup. This may produce incorrect xtrabackup_slave_info file when adding a new slave.

Install Percona XtraBackup

Testing the repository
Make sure packages are now available from the repository, by executing the following command:

yum list | grep percona

You should see output similar to the following:

percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-22.x86_64               2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64     2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test.x86_64             2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64

Note:

db01 - Master MySQL Server
db02 - Slave MySQL Server

On db01 only, install Percona XtraBackup using Percona’s repos:

[alok@db01 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
[alok@db01 ~]# yum install percona-xtrabackup-24
On db01 only, confirm it installed XtraBackup version 2.3 or newer:
[alok@db01 ~]# xtrabackup –version
Result like...
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
Take XtraBackup and setup MySQL Replication using Percona XtraBackup

On db02 only, rename the existing MySQL datadir, and create a fresh folder:
[alok@db02 ~]# service mysqld stop
[alok@db02 ~]# mv /var/lib/mysql /var/lib/mysql.old
[alok@db02 ~]# mkdir /var/lib/mysql
On db01 only, create the backup, make the snapshot consistent by applying the log, and copy it over to db02 using either scp or rsync:

[alok@db01 ~]# sudo mkdir /home/alok/perconabackup
[alok@db01 ~]# sudo innobackupex --user=root --password=mysql /home/alok/xtra_Bkup_18may/ 
Will get result like…
180518 10:56:37 [00]        ...done
180518 10:56:37 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2008941'
xtrabackup: Stopping log copying thread.
.180518 10:56:37 >> log scanned up to (2008941)

180518 10:56:37 Executing UNLOCK TABLES
180518 10:56:37 All tables unlocked
180518 10:56:37 Backup created in directory '/home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/'
MySQL binlog position: filename 'mysql-bin.000001', position '118445'
180518 10:56:37 [00] Writing /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/backup-my.cnf
180518 10:56:37 [00]        ...done
180518 10:56:37 [00] Writing /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/xtrabackup_info
180518 10:56:37 [00]        ...done
xtrabackup: Transaction log of lsn (2008941) to (2008941) was copied.
180518 10:56:37 completed OK!

[alok@db01 ~]# sudo innobackupex --user=root --password=mysql --apply-log /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/ 

This will get result like..

InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 118445, file name mysql-bin.000001
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.19 started; log sequence number 2010645
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2011550
180518 11:00:09 completed OK!

Now Copy Backup folder to slave server…
[alok@db01 ~]# sudo scp -r /home/alok/perconabackup/2018-05-17_14-58-31/ alok@192.168.0.201:/var/lib/mysql
OR
[alok@db01 ~]# sudo rsync -r /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/ alok@192.168.0.201:/var/lib/mysql/
On db02, fix the ownership of the datadir, startup MySQL, and grab the binlog name and position:
[alok@db02 ~]# sudo chown -R mysql:mysql /var/lib/mysql
[alok@db02 ~]# sudo service mysqld start
NOTE: Troubleshooting
Once you will start mysqld service this may happen to fail in start service. Kindly follow the below troubleshooting one-by-one
1. Check /var/lib/mysql folder having chown mysql:mysql if not kindly set to mysql as owner.
2. Check /var/lib/mysql/AAAA.err file having some error like ibdata access permission denied . So provide chmod –R 777 /var/lib/mysql . If still issue follows the next…
3. You will need to disable Selinux at once.
[alok@localhost ~]$ sudo vi /etc/selinux/config
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX= disabled
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
4. After that reboot server and check mysql service started. Once checked working fine then again you can revert SELINUX setting as back. 

When MySQL service start successfully then…

[alok@db02 ~]# cat /var/lib/mysql/xtrabackup_binlog_info
db01-bin-log.000001     120
Now on db02, startup slave replication:
[alok@db02 ~]# mysql –u root –p
mysql> CHANGE MASTER TO MASTER_HOST='10.x.x.x', MASTER_USER='repl', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='db01-bin-log.000001', MASTER_LOG_POS=120;
mysql> start slave;
mysql> show slave status\G
...
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
...










Saturday, 19 May 2018

MySQL Auditing with MariaDB Audit Plugin

MySQL Auditing for community edition with
MariaDB Audit Plug-in

MySQL Enterprise Edition has in-build feature for database Auditing. But if you needed to record user access to be in compliance with auditing regulations for MySQL Community Edition , You have to deploy some third party plug-in because of Auditing features is currently not available with MySQL Community Edition.

MariaDB Audit Plug-in is one of the solutions developed by MariaDB Corporation to achieve Auditing for MySQL Community Edition.


How to deploy MariaDB Audit Plugin

The MariaDB Audit Plugin is provided as a dynamic library: server_audit.so (server_audit.dll for Windows). The plugin must be located in the plugin directory, the directory containing all plugin libraries for MySQL. You can follow below instruction one by one...

ØDownload the latest Plugin from https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin 

ØExtract "server_audit.so" file and put it on Plugin directory

ØCopy “server_audit.so” in above Plugin directory

ØAdd below parameter  in my.cnf file 
    [mysqld]
    plugin_load=server_audit=server_audit.so 

ØRestart mysqld service ;

ØShow plugins;

Check for plugin server_audit   if plugin still not loaded then you need to install manual one time using below INSTALL PLUGIN command.

ØINSTALL PLUGIN server_audit SONAME 'server_audit.so';

ØAgain Show plugins;

ØCreate Audit Log Directory

[alok@localhost ~]$ sudo mkdir /var/lib/mysql/AuditLog

ØProvide full permission to log directory

[alok@localhost ~]$ sudo chmod -R 777 /var/lib/mysql/AuditLog

ØNow add others plugin parameter as per need in my.cnf

(Kindly refer and read all the parameter meaning and config from https://mariadb.com/kb/en/library/mariadb-audit-plugin/ )

[mysqld]
plugin_load=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
server_audit_events=CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL
server-audit-file-path=/var/lib/mysql/AuditLog/audit.csv     {if not set then it will create server_audit.log in Data Dir}
server_audit_excl_users=root
#server_audit_incl_users=aloktest
server_audit_file_rotate_size= 104857600    {in Byte ‘Herein 100MB defined}
server_audit_file_rotations= 500 {Max number of files after that it will overwrite}

server_audit_logging=ON

[Note: You must have full permission (777) in audit directory (/var/lib/mysql/AuditLog) otherwise Plugin failed to create audit file.]

ØRestart mysqld service;

mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='SERVER_AUDIT' \G;
*************************** 1. row ******************
PLUGIN_NAME: SERVER_AUDIT
PLUGIN_VERSION: 1.4
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 3.2
PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.4
PLUGIN_AUTHOR:  Alexey Botchkov (MariaDB Corporation)
PLUGIN_DESCRIPTION: Audit the server activity
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)

=====================================================

ØCheck all configured variables is working or not.


ØNow MariaDB Audit Plugin has been deployed. Let’s check log files and observe the same.












Status Monitoring
You may want to monitor routinely the status of the auditing plugin. The SHOW GLOBAL STATUS statement can assist you in this. On the server, execute the


Idea Configuration for Server

[mysqld]

#explicit_defaults_for_timestamp = 1
plugin_load=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
server_audit_events=CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL
server-audit-file-path=/var/lib/mysql/AuditLog/audit.csv
server_audit_excl_users=root
#server_audit_incl_users=aloksingh
server_audit_file_rotate_size= 104857600
server_audit_file_rotations= 500
server_audit_logging=ON
Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...