Activities

April 2017
M T W T F S S
« Feb    
 12
3456789
10111213141516
17181920212223
24252627282930

MySQL Enterprise Backup (MEB) : How to Backup and Restore a DB server

We are maintaining a huge MySQL database server which having more than 3TB data. It’s very had for us to backup, move and restore those databases from one host to another or once network to another due to size constrains. Moreover any of these activities would be involved lots of time and work precision during any of db restoration and backup.

After a Googling it is identify that MySQL Enterprise version have all those features to fast backup and restore with better performance. But it is not freely available to download :-p . It is identified that their backup involve the following facts

Note: It is not possible to take single database backup/restore using MEB tool(did not tried :-))

MEB Backup Logic

a. It’s possible to backup Online without having any table lock or performance change.But recommending it to execute on off business hours.
b. MySQL Enterprise Backup (MEB) compress the backup in .bz format (This is optional and we’ve chosen this since we have TB storage) and store it on –backup-dir location. Hence the backup size will be noticeable less than the actual data storage and portable.

MEB Restore Logic

a. Apply-log and uncompress option will decompress the data files in backup location and make it ready for further action. This will decompress the files in backup location without over writing it. But I found that huge compressed files will be deleted once it is decompressed.

b. Copy backup option will be copy the actual data files from backup location those are decompressed and move it to “–datadir” path which mention in the my.cnf file.

MEB backup involves three steps if we compress the backup.

1. How do I backup the MySQL server using MEB

  /opt/meb-3.8/bin/mysqlbackup -u root --backup-dir="/storage/dbbackup/backup-`date +%F`/" --compress --compress-level=1   --show-progress backup --socket=/var/lib/mysql/mysql.sock --show-progress=file:/tmp/mebProgress`date +%Y-%m-%d-%H-%M`.log     2>&1 | tee /tmp/mysqlbackupFull.`date +%Y-%m-%d-%H-%M`.log

–backup-dir : Location of backup files to be stored
–socket : Will identify the running sever and collect the mysql data location to be backed up.
/tmp/mysqlbackupFull and –show-progress=file : These options will capture all the MEB output for identifying the progress. We uses a screen session from Linux box to trigger the backup as it will take 6-8 hours to complete.

170421 00:31:53 mysqlbackup: INFO: Copying the database directory 'Docs'
170421 00:31:53 mysqlbackup: INFO: Copying the database directory 'mysql'
170421 00:31:53 mysqlbackup: INFO: Copying the database directory 'performance_schema'
170421 00:31:53 mysqlbackup: INFO: Copying the database directory 'test'
170421 00:31:53 mysqlbackup: INFO: Copying the database directory 'test1'
170421 00:31:53 mysqlbackup: INFO: Copying the database directory 'tests'
170421 00:31:53 mysqlbackup: INFO: Completing the copy of all non-innodb files.
170421 00:31:54 mysqlbackup: INFO: Progress: 20 of 20 MB; state: Copying metadata to image; compression: -
170421 00:31:54 mysqlbackup: INFO: A copied database page was modified at 5312129472.
          (This is the highest lsn found on page)
          Scanned log up to lsn 5312132652.
          Was able to parse the log up to lsn 5312132652.
          Maximum page number for a log record 428
170421 00:31:54 mysqlbackup: INFO: All tables unlocked
170421 00:31:54 mysqlbackup: INFO: All MySQL tables were locked for 0.914 seconds.
170421 00:31:54 mysqlbackup: INFO: Progress: 20 of 20 MB; state: Completed; compression: -
170421 00:31:54 mysqlbackup: INFO: Compressed 18 MB of data files to 1092 kbytes (compression 94.08%).
170421 00:31:54 mysqlbackup: INFO: Compress Backup operation completed successfully.
170421 00:31:54 mysqlbackup: INFO: Backup created in directory '/var/dbbackup/backup-2017-04-21/'

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 5312129024
   End LSN                    : 5312132652
-------------------------------------------------------------

mysqlbackup completed OK!

2. How to Decompress the backup

/opt/meb-3.8/bin/mysqlbackup --backup-dir="/var/dbbackup/backup-2017-04-20" --uncompress apply-log  2>&1 | tee /tmp/Extracing_backup.`date +%Y-%m-%d-%H-%M`.log

–backup-dir : backup location which MEB push the backup earlier.

 mysqlbackup: INFO: Creating 1 buffers each of size 65678.
170421 00:48:02 mysqlbackup: INFO: Apply-log operation starts with following threads
                1 read-threads    1 process-threads
170421 00:48:02 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 5312129024, end lsn 5312132652,
          start checkpoint 5312129472.
InnoDB: Doing recovery: scanned up to log sequence number 5312132652
 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 536870912
InnoDB: Progress in MB: 100 200 300 400 500
Setting log file size to 536870912
InnoDB: Progress in MB: 100 200 300 400 500
170421 00:48:05 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 5312132652.
170421 00:48:05 mysqlbackup: INFO: The first data file is '/var/dbbackup/backup-2017-04-21/datadir/ibdata1'
          and the new created log files are at '/var/dbbackup/backup-2017-04-21/datadir'
170421 00:48:05 mysqlbackup: INFO: Apply-log operation completed successfully.
170421 00:48:05 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK! with 1 warnings

3. How do I restore the MEB backup

Restoration involves lots of steps. First we need to stop the MySQL service and remove the data directory. Pls take a copy of mysql database and other tables if you required. Mysql db backup can help to retain the existing permission if you are using same db names on target location.

a. Stop the Mysql service after taking the necessary backup.
b. Delete/move the data folder path shown at ‘–datadir’ variable in my.cnf file.
c. Apply the below command and change the path –datadir with your location.

/opt/meb-3.8/bin/mysqlbackup --backup-dir="/var/dbbackup/backup-2017-04-20"  copy-back  --datadir=/var/lib/mysql --show-progress=file:/tmp/mebProgress`date +%Y-%m-%d-%H-%M`​.log     2>&1 | tee /tmp/mysqlrestore.`date +%Y-%m-%d-%H-%M`.log
[root@ ~]# /opt/meb-3.8/bin/mysqlbackup --backup-dir="/var/dbbackup/backup-2017-04-21"  copy-back  --datadir=/var/lib/mysql --show-progress=file:/tmp/mebProgress`date +%Y-%m-%d-%H-%M`​.log     2>&1 | tee /tmp/mysqlrestore.`date +%Y-%m-%d-%H-%M`.log
MySQL Enterprise Backup version 3.8.2 [2013/06/18]
Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 /opt/meb-3.8/bin/mysqlbackup
        --backup-dir=/var/dbbackup/backup-2017-04-21 copy-back
        --datadir=/var/lib/mysql
        --show-progress=file:/tmp/mebProgress2017-04-21-00-49​.log

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'copy-back' run mysqlbackup
           prints "mysqlbackup completed OK!".

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /var/lib/mysql
  innodb_data_home_dir = /var/lib/mysql
  innodb_data_file_path = ibdata1:10M:autoextend
  innodb_log_group_home_dir = /var/lib/mysql
  innodb_log_files_in_group = 2
  innodb_log_file_size = 512M
  innodb_page_size = Null
  innodb_checksum_algorithm = innodb

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /var/dbbackup/backup-2017-04-21/datadir
  innodb_data_home_dir = /var/dbbackup/backup-2017-04-21/datadir
  innodb_data_file_path = ibdata1:10M:autoextend
  innodb_log_group_home_dir = /var/dbbackup/backup-2017-04-21/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 536870912
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb

 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
170421 00:49:50 mysqlbackup: INFO: Copy-back operation starts with following threads
                1 read-threads    1 write-threads
170421 00:49:50 mysqlbackup: INFO: Copying the log file 'ib_logfile0'
170421 00:49:50 mysqlbackup: INFO: Copying the log file 'ib_logfile1'
170421 00:49:50 mysqlbackup: INFO: Copying /var/dbbackup/backup-2017-04-21/datadir/ibdata1.
170421 00:49:50 mysqlbackup: INFO: Copying /var/dbbackup/backup-2017-04-21/datadir/fmi/Document.ibd.
170421 00:49:50 mysqlbackup: INFO: Copying /var/dbbackup/backup-2017-04-21/datadir/fmiDocument/DocumentContentTypes.ibd.
170421 00:49:50 mysqlbackup: INFO: Copying /var/dbbackup/backup-2017-04-21/datadir/fmi/adsdsy.ibd.
170421 00:49:50 mysqlbackup: INFO: Copying /var/dbbackup/backup-2017-04-21/datadir/testasd/test.ibd.
170421 00:49:50 mysqlbackup: INFO: Copying the database directory 'fmiDocument'
170421 00:49:50 mysqlbackup: INFO: Copying the database directory 'mysql'
170421 00:49:50 mysqlbackup: INFO: Copying the database directory 'performance_schema'
170421 00:49:50 mysqlbackup: INFO: Copying the database directory 'test'
170421 00:49:50 mysqlbackup: INFO: Copying the database directory 'test1'
170421 00:49:50 mysqlbackup: INFO: Copying the database directory 'testDss'
170421 00:49:50 mysqlbackup: INFO: Completing the copy of all non-innodb files.
170421 00:49:50 mysqlbackup: INFO: Copying the log file 'ib_logfile0'
170421 00:49:51 mysqlbackup: INFO: Copying the log file 'ib_logfile1'
170421 00:49:53 mysqlbackup: INFO: Copy-back operation completed successfully.
170421 00:49:53 mysqlbackup: INFO: Finished copying backup files to '/var/lib/mysql'
mysqlbackup completed OK!
[root@ ~]#

Now you would need to grant the permission to MySQL data folder and then start it.

170421 00:49:53 mysqlbackup: INFO: Finished copying backup files to '/var/lib/mysql'
mysqlbackup completed OK!
[root@fina-peak-qa01 ~]# chown -R mysql.mysql /var/lib/mysql
[root@fina-peak-qa01 ~]# chmod -R 770 /var/lib/mysql
[root@fina-peak-qa01 ~]# service mysql start
Starting MySQL.. SUCCESS!
[root@fina-peak-qa01 ~]#

############################# Pls note MEB IS NOT FREELY DOWNLOADABLE ###############################

How do I enable TLS on postfix mail server

One of the clients complaints me that they are getting warning on the email those sent to Gmail.com domain. They are using 25 smtp port to send email using sql based mail server. So anybody in between the network can tap the email content since it been send through non-encrypted emails.

So my plan is to enable tls handshake on my Postfix smtp server.

No-tls_smtp

The above screenshot says me that our mails are non-encrypted while communicating with outside. So we need to tweak some postfix setting in order to enable tls encryption. Edit the file “/etc/postfix/main.cf” file and add the lines at the bottom. Pls note that I’m using a valid ssl certificate for tls handshaking. You can also create self signed certificate for this purpose. No need to buy SSL for this.

openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/httpd/ssl/private/postfix.key -out /etc/httpd/ssl/private/postfix.crt
############### Enabling tls on postfix ###############
# logging
smtpd_tls_loglevel = 1
# Allow use of TLS but make it optional
smtp_use_tls=yes
# Disable SSLv2/3 as they are vulnerable
smtpd_tls_protocols = !SSLv2, !SSLv3
smtp_tls_protocols = !SSLv2, !SSLv3
# Insist on stronger ciphers
smtpd_tls_ciphers = high
smtp_tls_ciphers = high
# keys
smtp_tls_cert_file = /etc/httpd/ssl/private/postfix.crt
smtp_tls_key_file = /etc/httpd/ssl/private/postfix.key

Next restart postfix server and test it.

 [root@Web01 liju]#/etc/init.d/postfix restart

Now you should be able to send email over tls encryption.

TLS_working

How Do enable the MySQL audit using MariaDB Audit Plugin

We are using Oracle MySQL community 5.7 version which does not have capability of auditing user activities. There will be some option to audit the MySQL statements by enabling General log ( for whole sql activity) and Slow log ( to identify the culprit sql which slow down the system.

Neither of above does not help top give you a complete solution if somebody change the table values with or without a proper approval like accidental data deletion or query execution against a wrong window.

My Best advise is, ever ever use a unique database name or user name on other environments to avoid accidental disaster or data loss. Our team has already found McAfee MySQL Audit Plugin become useful to track down the users activity. See the how to link,

https://github.com/mcafee/mysql-audit/wiki/Installation. But this plugin does not have certain feature what we expect like readability of output file and identifying fail statements. Our prime importance is to identify the broken sql statements prior to the up-gradation from MySQL 5.5 to 5.7. Once we able to record all the ‘failed statements‘ then we could operate and work with equivalent sql statements on MySQL 5.7. FAILED denotes the connection error and 1045 is error code.

Download the MariaDB plugins from https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/ link which is not been published.

Install MariaDB-Audit-Plugin

root@Db01#wget https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/server_audit-1.4.0.tar.gz
 root@Db01#tar -zxvf server_audit-1.4.0.tar.gz
 root@Db01#cd server_audit-1.4.0
 root@Db01# cd linux-x86-64

Next you need to find the MySQL plugin location using the below command.

1. Install Plugins

mysql> SHOW GLOBAL VARIABLES LIKE 'plugi%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)

Then copy the plugin file to plugin_install location.

 root@Db01#cp server_audit.so /usr/local/mysql/lib/plugin/

2. Active the plugins at runtime

install plugin server_audit SONAME "server_audit.so";

3. How to test the plugin installation

-4.1# mysql -e "show plugins;" | grep "SERVER"
SERVER_AUDIT    ACTIVE  AUDIT   server_audit.so GPL
-bash-4.1#
mysql> SHOW  VARIABLES LIKE 'SERVER%';
+-------------------------------+--------------------------------------+
| Variable_name                 | Value                                |
+-------------------------------+--------------------------------------+
| server_audit_events           |                                      |
| server_audit_excl_users       |                                      |
| server_audit_file_path        | server_audit.log                     |
| server_audit_file_rotate_now  | OFF                                  |
| server_audit_file_rotate_size | 1000000                              |
| server_audit_file_rotations   | 9                                    |
| server_audit_incl_users       |                                      |
| server_audit_loc_info         |                                      |
| server_audit_logging          | OFF                                  |
| server_audit_mode             | 1                                    |
| server_audit_output_type      | file                                 |
| server_audit_query_log_limit  | 1024                                 |
| server_audit_syslog_facility  | LOG_USER                             |
| server_audit_syslog_ident     | mysql-server_auditing                |
| server_audit_syslog_info      |                                      |
| server_audit_syslog_priority  | LOG_INFO                             |
| server_id                     | 0                                    |
| server_id_bits                | 32                                   |
| server_uuid                   | 045804e0-eec9-11e6-9146-90e2ba073ca0 |
+-------------------------------+--------------------------------------+
19 rows in set (0.00 sec)
mysql>

4. How to activate the audit plugin

mysql> set GLOBAL server_audit_logging=On;
Query OK, 0 rows affected (0.00 sec)
mysql>

Happy

How to change default JMS port on Glassfish server

Yesterday I was doing a task to fecilitate another Glassfish instance on the same IP but on using different port. I have modified all the ports those are mentioned specifically in domain.xml file. But yo u remember, I have another Glassfish server installed on the same hosting using default port values. While I’m verifying the ports which Glassfish initiated, found that JMS default port was not been changed and using the default port 7676 till now. So I can not accommodate another Glassfish instance. But I could not see these port number defined no where in that domain.xml file. So I’m confused and become helpless

Today I was turned to look at the asadmin commands to listout the variable of ports and found it’s still using 7676 and knowing that it can be changed only using asadmin command. So I’ m looking in to that option.

Solution

Before

[root@~]# /var/glassfish-mysqltest/bin/asadmin get \* | grep 7676
configs.config.default-config.system-property.JMS_PROVIDER_PORT.value=37676
configs.config.default-config.admin-service.jmx-connector.system.port=27676
configs.config.default-config.jms-service.jms-host.default_JMS_host.port=37676
configs.config.server-config.jms-service.jms-host.default_JMS_host.port=7676

Execute this command

/var/glassfish-mysqltest/bin/asadmin set configs.config.server-config.jms-service.jms-host.default_JMS_host.port=37676

After

[root@ ~]# /var/glassfish-mysqltest/bin/asadmin get \* | grep 7676
configs.config.default-config.system-property.JMS_PROVIDER_PORT.value=37676
configs.config.default-config.admin-service.jmx-connector.system.port=27676
configs.config.default-config.jms-service.jms-host.default_JMS_host.port=37676
configs.config.server-config.jms-service.jms-host.default_JMS_host.port=37676

That’s it I can able to change the JMS default port to 37676

How to pull single table data from full database backup file.

At some time, you would ask to restore a particular table from a full backup. So you have to follow the below steps.

1. Request backup team to provide the backup
2. Decompress the backup.
3. Move the file to target server.
4. Restore entire backup which take long time based of the volume.
5. Pull particular table backup and provide it to the requestee.

The below single line of bash command will help you to get single table backup which stored into a particular file. Here I’m going to pull the table backup of help_category from a database.

#sed -n -e '/DROP TABLE.*`help_category`/,/UNLOCK TABLES/p' /tmp/fullbackup.sql  > tabledump.sql

Note : Note : You may need to remember this activity will open a single file, sometime GB file which would harm the entire server resources and not advisable to execute this script on any Production server. Also you may need more CPU/RAM for this activity.

Collecting the JVM data for identifying the bottleneck

WE all are developing jave application for various area as it is one of the secure and non-hackle platform. So it’s consider is most secured programming platform to choose. Here One of our servers went heavily loaded and we did not find any specific reasons to happen this. At the same time observed the CPU consumed heavily and RAM is barely used . So its seems like some memory leakage or code brake happening at web server end as we did not see any load conjunction on web server end.

So we need to dig the issue by collecting the Garbage collection data to a file. Based on the information which I’d given, There are 3 basic types data that we need to identify the a root cause of a Java application.

1. Collecting the Heap dump
This is basically pull what all the data are present in the system Memory. So this would help us to identify which function/code is currently loaded in memory.

How do I take thread dump : Execute the below command

 /var/jdk/bin/jmap  -histo $(pgrep java)   > ~/heapdump_$(date +"%Y-%m-%d_%H%M").log

The above command will pull the headp dump data to a dated separated file for future reference.

2. Collecting Thread dump
This is basically used for identify the programming side libraries/functions/forms those are being used in that particular time. This will be very useful to identify the functionality fix or brakes. So we had instructed to keep this data strictly up on any application outage.

  • How to pull the Thread dump
  •  /var/jdk/bin/jstack  -l $(pgrep java)  > ~/thread_dump_$(date +"%Y-%m-%d_%H%M").log

    Note : This command will pull the backup of all the java thread currently being executed in the memory and written it as a dated file. You also need to take these logs for a period of time to understand the history of the function loaded in memory. So I used to execute this commands 3 times in one minute interval.

    3. Verifying java application is heavily used using jstat.

    There is a utility (jstat) which collect the statistics of Garbage collection data which including the gcutils (used to check the usage of heap areas, the number of GC performed, and the total accumulated time for GC operations).

    [root@web232 ~]# /var/jdk/bin/jstat -gcutil $(pgrep java) 250 700
      S0     S1     E      O      P     YGC     YGCT    FGC    FGCT     GCT
      0.00  76.33 100.00 100.00  38.71    133   15.991   229 1465.177 1481.168
      0.00  76.33 100.00 100.00  38.71    133   15.991   229 1465.177 1481.168
      0.00  94.96 100.00 100.00  38.71    133   15.991   229 1466.212 1482.203
      0.00 100.00 100.00 100.00  38.71    133   15.991   230 1466.212 1482.203
      0.00 100.00 100.00 100.00  38.71    133   15.991   230 1466.212 1482.203
      0.00 100.00 100.00 100.00  38.71    133   15.991   230 1466.212 1482.203
      0.00 100.00 100.00 100.00  38.71    133   15.991   230 1466.212 1482.203
      0.00 100.00 100.00 100.00  38.71    133   15.991   230 1466.212 1482.203

    The above logs showing that Old space and Eden space was fully occupied by GC cycle. So any pending GC request will be keep in queue and web server remains running in hanging states appears even though it is working correctly. The only culprit is GC collection killing the web server and keep it forzhen. So restarting webserver will help to release the GC and hence platform bring it back quickly.

    If you see Eden space (E) and Old area (O) are showing the value 100. you application was driven in poor performance and may not be working correctly. So the options are restart the web server or kill the jave process.

    How do I kill the java process.

    /bin/kill -9 $(pgrep java)​