How to setup remote email server on a Centos 7 VPS using postfix with spf, dkim and dmark

 I used to pay a hefty sum to Amazon SES to deliver my site.com emails. However I frequently got suspended because of bounce rates which exceeded above 10% at times. So I said enough is enough and came up with this configs. It's a bit elaborate and may seem intimidating at the beginning but definitely worth the effort to free your emails from Amazon grips. It is tested and worked fine using postfix on Centos 7. No email is being flagged as spam by gmail. 

Here I will set up mail.mysite.com on a dedicated SMTP server on VPS which receives emails from mysite.com and delivers it to the recipients. I suppose the VPS IP is 1.2.3.4. Don't forget to change mail.mysite.com in the config

.

First install Postfix and its auth plugin

yum install postfix cyrus-sasl cyrus-sasl-lib cyrus-sasl-plain

 

Modify /etc/postfix/main.cf to look like this :

 

#========================================================

# Global Postfix configuration file. This file lists only a subset

# of all parameters. For the syntax, and for a complete parameter

# list, see the postconf(5) manual page (command: "man 5 postconf").

#

# For common configuration examples, see BASIC_CONFIGURATION_README

# and STANDARD_CONFIGURATION_README. To find these documents, use

# the command "postconf html_directory readme_directory", or go to

# http://www.postfix.org/.

#

# For best results, change no more than 2-3 parameters at a time,

# and test if Postfix still works after every change.

# SOFT BOUNCE

#

# The soft_bounce parameter provides a limited safety net for

#testing. When soft_bounce is enabled, mail will remain queued that

# would otherwise bounce. This parameter disables locally-generated

# bounces, and prevents the SMTP server from rejecting mail permanently

# (by changing 5xx replies into 4xx replies). However, soft_bounce

# is no cure for address rewriting mistakes or mail routing mistakes.

#

#soft_bounce = no

# LOCAL PATHNAME INFORMATION

#

# The queue_directory specifies the location of the Postfix queue.

# This is also the root directory of Postfix daemons that run chrooted.

# See the files in examples/chroot-setup for setting up Postfix chroot

# environments on different UNIX systems.

#

queue_directory = /var/spool/postfix

# The command_directory parameter specifies the location of all

# postXXX commands.

#

command_directory = /usr/sbin

# The daemon_directory parameter specifies the location of all Postfix

# daemon programs (ie programs listed in the master.cf file). This

# directory must be owned by root.

#

daemon_directory = /usr/libexec/postfix

# The data_directory parameter specifies the location of Postfix-writable

# data files (caches, random numbers). This directory must be owned

# by the mail_owner account (see below).

#

data_directory = /var/lib/postfix

# QUEUE AND PROCESS OWNERSHIP

#

# The mail_owner parameter specifies the owner of the Postfix queue

# and of most Postfix daemon processes. Specify the name of a user

# account THAT DOES NOT SHARE ITS

         # USER OR GROUP ID WITH OTHER ACCOUNTS

# AND THAT OWNS NO OTHER FILES OR PROCESSES ON THE SYSTEM. In

# particular, don't specify nobody or daemon. PLEASE USE DEDICATED

# USER.

#

mail_owner = postfix

# The default_privs parameter specifies the default rights used by

# the local delivery agent for delivery to external file or command.

# These rights are used in the absence of a recipient user context.

# DO NOT SPECIFY A PRIVILEGED USER OR THE POSTFIX OWNER.

#

#default_privs = nobody

# INTERNET HOST AND DOMAIN NAMES

# 

# The myhostname parameter specifies the internet hostname of this

# mail system. The default is to use the fully-qualified domain name

# from gethostname(). $myhostname is used as a default value for many

# other configuration parameters.

#

#myhostname = host.domain.tld

#myhostname = virtual.domain.tld

myhostname = mail.mysite.com

# The mydomain parameter specifies the local internet domain name.

# The default is to use $myhostname minus the first component.

# $mydomain is used as a default value for many other configuration

# parameters.

#

#mydomain = domain.tld

mydomain = mysite.com

# SENDING MAIL

# 

# The myorigin parameter specifies the domain that locally-posted

# mail appears to come from. The default is to append $myhostname,

# which is fine for small sites. If you run a domain with multiple

# machines, you should (1) change this to $mydomain and (2) set up

# a domain-wide alias database that aliases each user to

# user@that.users.mailhost.

#

# For the sake of consistency between sender and recipient addresses,

# myorigin also specifies the default domain name that is appended

# to recipient addresses that have no @domain part.

#

#myorigin = $myhostname

#myorigin = $mydomain

# RECEIVING MAIL

# The inet_interfaces parameter specifies the network interface

# addresses that this mail system receives mail on. By default,

# the software claims all active interfaces on the machine. The

# parameter also controls delivery of mail to user@[ip.address].

#

# See also the proxy_interfaces parameter, for network addresses that

# are forwarded to us via a proxy or network address translator.

#

# Note: you need to stop/start Postfix when this parameter changes.

#

inet_interfaces = all

#inet_interfaces = $myhostname

#inet_interfaces = $myhostname, localhost

#inet_interfaces = localhost

# Enable IPv4, and IPv6 if supported

inet_protocols = all

# The proxy_interfaces parameter specifies the network interface

# addresses that this mail system receives mail on by way of a

# proxy or network address translation unit. This setting extends

# the address list specified with the inet_interfaces parameter.

#

# You must specify your proxy/NAT addresses when your system is a

# backup MX host for other domains, otherwise mail delivery loops

# will happen when the primary MX host is down.

#

#proxy_interfaces =

#proxy_interfaces = 1.2.3.4

# The mydestination parameter specifies the list of domains that this

# machine considers itself the final destination for.

#

# These domains are routed to the delivery agent specified with the

# local_transport parameter setting. By default, that is the UNIX

# compatible delivery agent that lookups all recipients in /etc/passwd

# and /etc/aliases or their equivalent.

#

# The default is $myhostname + localhost.$mydomain.  On a mail domain

# gateway, you should also include $mydomain.

#

# Do not specify the names of virtual domains - those domains are

# specified elsewhere (see VIRTUAL_README).

#

# Do not specify the names of domains that this machine is backup MX

# host for. Specify those names via the relay_domains settings for

# the SMTP server, or use permit_mx_backup if you are lazy (see

# STANDARD_CONFIGURATION_README).

#

# The local machine is always the final destination for mail addressed

# to user@[the.net.work.address] of an interface that the mail system

# receives mail on (see the inet_interfaces parameter).

#

# Specify a list of host or domain names, /file/name or type:table

# patterns, separated by commas and/or whitespace. A /file/name

# pattern is replaced by its contents; a type:table is matched when

# a name matches a lookup key (the right-hand side is ignored).

# Continue long lines by starting the next line with whitespace.

#

# See also below, section "REJECTING MAIL FOR UNKNOWN LOCAL USERS".

#

mydestination = $myhostname, localhost.$mydomain, localhost

#mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain

#mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain,

# mail.$mydomain, www.$mydomain, ftp.$mydomain

# REJECTING MAIL FOR UNKNOWN LOCAL USERS

#

# The local_recipient_maps parameter specifies optional lookup tables

# with all names or addresses of users that are local with respect

# to $mydestination, $inet_interfaces or $proxy_interfaces.

#

# If this parameter is defined, then the SMTP server will reject

# mail for unknown local users. This parameter is defined by default.

#

# To turn off local recipient checking in the SMTP server, specify

# local_recipient_maps = (i.e. empty).

#

# The default setting assumes that you use the default Postfix local

# delivery agent for local delivery. You need to update the

# local_recipient_maps setting if:

#

# - You define $mydestination domain recipients in files other than

#   /etc/passwd, /etc/aliases, or the $virtual_alias_maps files.

#   For example, you define $mydestination domain recipients in    

#   the $virtual_mailbox_maps files.

#

# - You redefine the local delivery agent in master.cf.

#

# - You redefine the "local_transport" setting in main.cf.

#

# - You use the "luser_relay", "mailbox_transport", or "fallback_transport"

#   feature of the Postfix local delivery agent (see local(8)).

#

# Details are described in the LOCAL_RECIPIENT_README file.

#

# Beware: if the Postfix SMTP server runs chrooted, you probably have

# to access the passwd file via the proxymap service, in order to

# overcome chroot restrictions. The alternative, having a copy of

# the system passwd file in the chroot jail is just not practical.

#

# The right-hand side of the lookup tables is conveniently ignored.

# In the left-hand side, specify a bare username, an @domain.tld

# wild-card, or specify a user@domain.tld address.

# 

#local_recipient_maps = unix:passwd.byname $alias_maps

#local_recipient_maps = proxy:unix:passwd.byname $alias_maps

#local_recipient_maps =

# The unknown_local_recipient_reject_code specifies the SMTP server

# response code when a recipient domain matches $mydestination or

# ${proxy,inet}_interfaces, while $local_recipient_maps is non-empty

# and the recipient address or address local-part is not found.

#

# The default setting is 550 (reject mail) but it is safer to start

# with 450 (try again later) until you are certain that your

# local_recipient_maps settings are OK.

#

unknown_local_recipient_reject_code = 550

# TRUST AND RELAY CONTROL

# The mynetworks parameter specifies the list of "trusted" SMTP

# clients that have more privileges than "strangers".

#

# In particular, "trusted" SMTP clients are allowed to relay mail

# through Postfix.  See the smtpd_recipient_restrictions parameter

# in postconf(5).

#

# You can specify the list of "trusted" network addresses by hand

# or you can let Postfix do it for you (which is the default).

#

# By default (mynetworks_style = subnet), Postfix "trusts" SMTP

# clients in the same IP subnetworks as the local machine.

# On Linux, this does works correctly only with interfaces specified

# with the "ifconfig" command.

# 

# Specify "mynetworks_style = class" when Postfix should "trust" SMTP

# clients in the same IP class A/B/C networks as the local machine.

# Don't do this with a dialup site - it would cause Postfix to "trust"

# your entire provider's network.  Instead, specify an explicit

# mynetworks list by hand, as described below.

#  

# Specify "mynetworks_style = host" when Postfix should "trust"

# only the local machine.

# 

#mynetworks_style = class

#mynetworks_style = subnet

#mynetworks_style = host

# Alternatively, you can specify the mynetworks list by hand, in

# which case Postfix ignores the mynetworks_style setting.

#

# Specify an explicit list of network/netmask patterns, where the

# mask specifies the number of bits in the network part of a host

# address.

#

# You can also specify the absolute pathname of a pattern file instead

# of listing the patterns here. Specify type:table for table-based lookups

# (the value on the table right-hand side is not used).

#

mynetworks = 0.0.0.0/0, 127.0.0.0/8

#mynetworks = 168.100.189.0/28, 127.0.0.0/8

#mynetworks = $config_directory/mynetworks

#mynetworks = hash:/etc/postfix/network_table

# The relay_domains parameter restricts what destinations this system will

# relay mail to.  See the smtpd_recipient_restrictions description in

# postconf(5) for detailed information.

#

# By default, Postfix relays mail

# - from "trusted" clients (IP address matches $mynetworks) to any destination,

# - from "untrusted" clients to destinations that match $relay_domains or

#   subdomains thereof, except addresses with sender-specified routing.

# The default relay_domains value is $mydestination.

# 

# In addition to the above, the Postfix SMTP server by default accepts mail

# that Postfix is final destination for:

# - destinations that match $inet_interfaces or $proxy_interfaces,

# - destinations that match $mydestination

# - destinations that match $virtual_alias_domains,

# - destinations that match $virtual_mailbox_domains.

# These destinations do not need to be listed in $relay_domains.

# 

# Specify a list of hosts or domains, /file/name patterns or type:name

# lookup tables, separated by commas and/or whitespace.  Continue

# long lines by starting the next line with whitespace. A file name

# is replaced by its contents; a type:name table is matched when a

# (parent) domain appears as lookup key.

#

# NOTE: Postfix will not automatically forward mail for domains that

# list this system as their primary or backup MX host. See the

# permit_mx_backup restriction description in postconf(5).

#

#relay_domains = $mydestination

# INTERNET OR INTRANET

# The relayhost parameter specifies the default host to send mail to

# when no entry is matched in the optional transport(5) table. When

# no relayhost is given, mail is routed directly to the destination.

#

# On an intranet, specify the organizational domain name. If your

# internal DNS uses no MX records, specify the name of the intranet

# gateway host instead.

#

# In the case of SMTP, specify a domain, host, host:port, [host]:port,

# [address] or [address]:port; the form [host] turns off MX lookups.

#

# If you're connected via UUCP, see also the default_transport parameter.

#

#relayhost = $mydomain

#relayhost = [gateway.my.domain]

#relayhost = [mailserver.isp.tld]

#relayhost = uucphost

#relayhost = [an.ip.add.ress]

# REJECTING UNKNOWN RELAY USERS

#

# The relay_recipient_maps parameter specifies optional lookup tables

# with all addresses in the domains that match $relay_domains.

#

# If this parameter is defined, then the SMTP server will reject

# mail for unknown relay users. This feature is off by default.

#

# The right-hand side of the lookup tables is conveniently ignored.

# In the left-hand side, specify an @domain.tld wild-card, or specify

# a user@domain.tld address.

# 

#relay_recipient_maps = hash:/etc/postfix/relay_recipients

# INPUT RATE CONTROL

#

# The in_flow_delay configuration parameter implements mail input

# flow control. This feature is turned on by default, although it

# still needs further development (it's disabled on SCO UNIX due

# to an SCO bug).

# 

# A Postfix process will pause for $in_flow_delay seconds before

# accepting a new message, when the message arrival rate exceeds the

# message delivery rate. With the default 100 SMTP server process

# limit, this limits the mail inflow to 100 messages a second more

# than the number of messages delivered per second.

# 

# Specify 0 to disable the feature. Valid delays are 0..10.

# 

#in_flow_delay = 1s

# ADDRESS REWRITING

#

# The ADDRESS_REWRITING_README document gives information about

# address masquerading or other forms of address rewriting including

# username->Firstname.Lastname mapping.

# ADDRESS REDIRECTION (VIRTUAL DOMAIN)

#

# The VIRTUAL_README document gives information about the many forms

# of domain hosting that Postfix supports.

# "USER HAS MOVED" BOUNCE MESSAGES

#

# See the discussion in the ADDRESS_REWRITING_README document.

# TRANSPORT MAP

#

# See the discussion in the ADDRESS_REWRITING_README document.

# ALIAS DATABASE

#

# The alias_maps parameter specifies the list of alias databases used

# by the local delivery agent. The default list is system dependent.

#

# On systems with NIS, the default is to search the local alias

# database, then the NIS alias database. See aliases(5) for syntax

# details.

# 

# If you change the alias database, run "postalias /etc/aliases" (or

# wherever your system stores the mail alias file), or simply run

# "newaliases" to build the necessary DBM or DB file.

#

# It will take a minute or so before changes become visible.  Use

# "postfix reload" to eliminate the delay.

#

#alias_maps = dbm:/etc/aliases

alias_maps = hash:/etc/aliases

#alias_maps = hash:/etc/aliases, nis:mail.aliases

#alias_maps = netinfo:/aliases

# The alias_database parameter specifies the alias database(s) that

# are built with "newaliases" or "sendmail -bi".  This is a separate

# configuration parameter, because alias_maps (see above) may specify

# tables that are not necessarily all under control by Postfix.

#

#alias_database = dbm:/etc/aliases

#alias_database = dbm:/etc/mail/aliases

alias_database = hash:/etc/aliases

#alias_database = hash:/etc/aliases, hash:/opt/majordomo/aliases

# ADDRESS EXTENSIONS (e.g., user+foo)

#

# The recipient_delimiter parameter specifies the separator between

# user names and address extensions (user+foo). See canonical(5),

# local(8), relocated(5) and virtual(5) for the effects this has on

# aliases, canonical, virtual, relocated and .forward file lookups.

# Basically, the software tries user+foo and .forward+foo before

# trying user and .forward.

#

#recipient_delimiter = +

# DELIVERY TO MAILBOX

#

# The home_mailbox parameter specifies the optional pathname of a

# mailbox file relative to a user's home directory. The default

# mailbox file is /var/spool/mail/user or /var/mail/user.  Specify

# "Maildir/" for qmail-style delivery (the / is required).

#

#home_mailbox = Mailbox

#home_mailbox = Maildir/

 

# The mail_spool_directory parameter specifies the directory where

# UNIX-style mailboxes are kept. The default setting depends on the

# system type.

#

#mail_spool_directory = /var/mail

#mail_spool_directory = /var/spool/mail

# The mailbox_command parameter specifies the optional external

# command to use instead of mailbox delivery. The command is run as

# the recipient with proper HOME, SHELL and LOGNAME environment settings.

# Exception:  delivery for root is done as $default_user.

#

# Other environment variables of interest: USER (recipient username),

# EXTENSION (address extension), DOMAIN (domain part of address),

# and LOCAL (the address localpart).

#

# Unlike other Postfix configuration parameters, the mailbox_command

# parameter is not subjected to $parameter substitutions. This is to

# make it easier to specify shell syntax (see example below).

#

# Avoid shell meta characters because they will force Postfix to run

# an expensive shell process. Procmail alone is expensive enough.

#

# IF YOU USE THIS TO DELIVER MAIL SYSTEM-WIDE, YOU MUST SET UP AN

# ALIAS THAT FORWARDS MAIL FOR ROOT TO A REAL USER.

#

#mailbox_command = /some/where/procmail

#mailbox_command = /some/where/procmail -a "$EXTENSION"

# The mailbox_transport specifies the optional transport in master.cf

# to use after processing aliases and .forward files. This parameter

# has precedence over the mailbox_command, fallback_transport and

# luser_relay parameters.

#

# Specify a string of the form transport:nexthop, where transport is

# the name of a mail delivery transport defined in master.cf.  The

# :nexthop part is optional. For more details see the sample transport

# configuration file.

#

# NOTE: if you use this feature for accounts not in the UNIX password

# file, then you must update the "local_recipient_maps" setting in

# the main.cf file, otherwise the SMTP server will reject mail for    

# non-UNIX accounts with "User unknown in local recipient table".

#

# Cyrus IMAP over LMTP. Specify ``lmtpunix      cmd="lmtpd"

# listen="/var/imap/socket/lmtp" prefork=0'' in cyrus.conf.

#mailbox_transport = lmtp:unix:/var/lib/imap/socket/lmtp

# If using the cyrus-imapd IMAP server deliver local mail to the IMAP

# server using LMTP (Local Mail Transport Protocol), this is prefered

# over the older cyrus deliver program by setting the

# mailbox_transport as below:

#

# mailbox_transport = lmtp:unix:/var/lib/imap/socket/lmtp

#

# The efficiency of LMTP delivery for cyrus-imapd can be enhanced via

# these settings.

#

# local_destination_recipient_limit = 300

# local_destination_concurrency_limit = 5

#

# Of course you should adjust these settings as appropriate for the

# capacity of the hardware you are using. The recipient limit setting

# can be used to take advantage of the single instance message store

# capability of Cyrus. The concurrency limit can be used to control

# how many simultaneous LMTP sessions will be permitted to the Cyrus

# message store. 

#

# Cyrus IMAP via command line. Uncomment the "cyrus...pipe" and

# subsequent line in master.cf.

#mailbox_transport = cyrus

# The fallback_transport specifies the optional transport in master.cf

# to use for recipients that are not found in the UNIX passwd database.

# This parameter has precedence over the luser_relay parameter.

#

# Specify a string of the form transport:nexthop, where transport is

# the name of a mail delivery transport defined in master.cf.  The

# :nexthop part is optional. For more details see the sample transport

# configuration file.

#

# NOTE: if you use this feature for accounts not in the UNIX password

# file, then you must update the "local_recipient_maps" setting in

# the main.cf file, otherwise the SMTP server will reject mail for    

# non-UNIX accounts with "User unknown in local recipient table".

#

#fallback_transport = lmtp:unix:/var/lib/imap/socket/lmtp

#fallback_transport =

# The luser_relay parameter specifies an optional destination address

# for unknown recipients.  By default, mail for unknown@$mydestination,

# unknown@[$inet_interfaces] or unknown@[$proxy_interfaces] is returned

# as undeliverable.

#

# The following expansions are done on luser_relay: $user (recipient

# username), $shell (recipient shell), $home (recipient home directory),

# $recipient (full recipient address), $extension (recipient address

# extension), $domain (recipient domain), $local (entire recipient

# localpart), $recipient_delimiter. Specify ${name?value} or

# ${name:value} to expand value only when $name does (does not) exist.

#

# luser_relay works only for the default Postfix local delivery agent.

#

# NOTE: if you use this feature for accounts not in the UNIX password

# file, then you must specify "local_recipient_maps =" (i.e. empty) in

# the main.cf file, otherwise the SMTP server will reject mail for    

# non-UNIX accounts with "User unknown in local recipient table".

#

#luser_relay = $user@other.host

#luser_relay = $local@other.host

#luser_relay = admin+$local

  

# JUNK MAIL CONTROLS

# 

# The controls listed here are only a very small subset. The file

# SMTPD_ACCESS_README provides an overview.

# The header_checks parameter specifies an optional table with patterns

# that each logical message header is matched against, including

# headers that span multiple physical lines.

#

# By default, these patterns also apply to MIME headers and to the

# headers of attached messages. With older Postfix versions, MIME and

# attached message headers were treated as body text.

#

# For details, see "man header_checks".

#

#header_checks = regexp:/etc/postfix/header_checks

# FAST ETRN SERVICE

#

# Postfix maintains per-destination logfiles with information about

# deferred mail, so that mail can be flushed quickly with the SMTP

# "ETRN domain.tld" command, or by executing "sendmail -qRdomain.tld".

# See the ETRN_README document for a detailed description.

# 

# The fast_flush_domains parameter controls what destinations are

# eligible for this service. By default, they are all domains that

# this server is willing to relay mail to.

# 

#fast_flush_domains = $relay_domains

# SHOW SOFTWARE VERSION OR NOT

#

# The smtpd_banner parameter specifies the text that follows the 220

# code in the SMTP server's greeting banner. Some people like to see

# the mail version advertised. By default, Postfix shows no version.

#

# You MUST specify $myhostname at the start of the text. That is an

# RFC requirement. Postfix itself does not care.

#

#smtpd_banner = $myhostname ESMTP $mail_name

#smtpd_banner = $myhostname ESMTP $mail_name ($mail_version)

# PARALLEL DELIVERY TO THE SAME DESTINATION

#

# How many parallel deliveries to the same user or domain? With local

# delivery, it does not make sense to do massively parallel delivery

# to the same user, because mailbox updates must happen sequentially,

# and expensive pipelines in .forward files can cause disasters when

# too many are run at the same time. With SMTP deliveries, 10

# simultaneous connections to the same domain could be sufficient to

# raise eyebrows.

# 

# Each message delivery transport has its XXX_destination_concurrency_limit

# parameter.  The default is $default_destination_concurrency_limit for

# most delivery transports. For the local delivery agent the default is 2.

#local_destination_concurrency_limit = 2

#default_destination_concurrency_limit = 20

# DEBUGGING CONTROL

#

# The debug_peer_level parameter specifies the increment in verbose

# logging level when an SMTP client or server host name or address

# matches a pattern in the debug_peer_list parameter.

#

debug_peer_level = 2

# The debug_peer_list parameter specifies an optional list of domain

# or network patterns, /file/name patterns or type:name tables. When

# an SMTP client or server host name or address matches a pattern,

# increase the verbose logging level by the amount specified in the

# debug_peer_level parameter.

#

#debug_peer_list = 127.0.0.1

#debug_peer_list = some.domain

# The debugger_command specifies the external command that is executed

# when a Postfix daemon program is run with the -D option.

#

# Use "command .. & sleep 5" so that the debugger can attach before

# the process marches on. If you use an X-based debugger, be sure to

# set up your XAUTHORITY environment variable before starting Postfix.

#

debugger_command =

PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin

ddd $daemon_directory/$process_name $process_id & sleep 5

# If you can't use X, use this to capture the call stack when a

# daemon crashes. The result is in a file in the configuration

# directory, and is named after the process name and the process ID.

#

# debugger_command =

# PATH=/bin:/usr/bin:/usr/local/bin; export PATH; (echo cont;

# echo where) | gdb $daemon_directory/$process_name $process_id 2>&1

# >$config_directory/$process_name.$process_id.log & sleep 5

#

# Another possibility is to run gdb under a detached screen session.

# To attach to the screen sesssion, su root and run "screen -r

# <id_string>" where <id_string> uniquely matches one of the detached

# sessions (from "screen -list").

#

# debugger_command =

# PATH=/bin:/usr/bin:/sbin:/usr/sbin; export PATH; screen

# -dmS $process_name gdb $daemon_directory/$process_name

# $process_id & sleep 1

# INSTALL-TIME CONFIGURATION INFORMATION

#

# The following parameters are used when installing a new Postfix version.

# 

# sendmail_path: The full pathname of the Postfix sendmail command.

# This is the Sendmail-compatible mail posting interface.

# 

sendmail_path = /usr/sbin/sendmail.postfix

# newaliases_path: The full pathname of the Postfix newaliases command.

# This is the Sendmail-compatible command to build alias databases.

#

newaliases_path = /usr/bin/newaliases.postfix

# mailq_path: The full pathname of the Postfix mailq command.  This

# is the Sendmail-compatible mail queue listing command.

# 

mailq_path = /usr/bin/mailq.postfix

# setgid_group: The group for mail submission and queue management

# commands.  This must be a group name with a numerical group ID that

# is not shared with other accounts, not even with the Postfix account.

#

setgid_group = postdrop

# html_directory: The location of the Postfix HTML documentation.

#

html_directory = no

# manpage_directory: The location of the Postfix on-line manual pages.

#

manpage_directory = /usr/share/man

# sample_directory: The location of the Postfix sample configuration files.

# This parameter is obsolete as of Postfix 2.1.

#

sample_directory = /usr/share/doc/postfix-2.10.1/samples

# readme_directory: The location of the Postfix README files.

#

readme_directory = /usr/share/doc/postfix-2.10.1/README_FILES

## Below line added by Vivek ##

# With this, the Postfix SMTP server announces STARTTLS support to remote SMTP 

# clients, but does not require that clients use TLS encryption. 

smtpd_use_tls = yes

smtpd_tls_security_level = encrypt

smtp_tls_security_level = encrypt

# Configures the server certificate file and key file as well as the CA's 

# intermediate certificate file

smtpd_tls_cert_file = /etc/letsencrypt/live/mail.mysite.com/fullchain.pem

smtpd_tls_key_file = /etc/letsencrypt/live/mail.mysite.com/privkey.pem

# Enable logging of summary message for TLS handshake and to include 

# information about the protocol and cipher used as well as the client and 

# issuer CommonName

smtpd_tls_loglevel = 0

smtpd_tls_received_header = yes

# Postfix SMTP server and the remote SMTP client negotiate a session, which 

# takes some computer time and network bandwidth. SSL protocol versions other 

# than SSLv2 support resumption of cached sessions.

smtpd_tls_session_cache_database = btree:/var/lib/postfix/smtpd_scache

# Cached Postfix SMTP server session information expires after a certain 

# amount of time.RFC2246 recommends a maximum of 24 hours. 

smtpd_tls_session_cache_timeout = 10800s

#

# SMTP-AUTH configuration

#

# The name of the Postfix SMTP server's local SASL authentication realm. (default: empty)

smtpd_sasl_local_domain =

# Enable SASL authentication in the Postfix SMTP server. By default, the 

# Postfix SMTP server does not use authentication. 

smtpd_sasl_auth_enable = yes

# The SASL plug-in type that the Postfix SMTP server should use for authentication.

smtpd_sasl_type = cyrus

# Postfix SMTP server SASL security options. noanonymous disallow methods 

# that allow anonymous authentication. 

smtpd_sasl_security_options = noanonymous

# Enable inter-operability with remote SMTP clients that implement an obsolete 

# version of the AUTH command

broken_sasl_auth_clients = yes

# Do not report the SASL authenticated user name in the smtpd Received message header.

smtpd_sasl_authenticated_header = no

# Optional restrictions that the Postfix SMTP server applies in the context of 

# a client RCPT TO command, after smtpd_relay_restrictions. 

smtpd_recipient_restrictions = permit_sasl_authenticated,permit_mynetworks,reject_unauth_destination

smtpd_milters = inet:127.0.0.1:8891

non_smtpd_milters = $smtpd_milters

milter_default_action = accept

#=================master.cf========================

Also master.cf should look like this:

#

# Postfix master process configuration file.  For details on the format

# of the file, see the master(5) manual page (command: "man 5 master").

#

# Do not forget to execute "postfix reload" after editing this file.

#

# ==========================================================================

# service type  private unpriv  chroot  wakeup  maxproc command + args

#               (yes)   (yes)   (yes)   (never) (100)

# ==========================================================================

smtp      inet  n       -       n       -       -       smtpd

#smtp      inet  n       -       n       -       1       postscreen

#smtpd     pass  -       -       n       -       -       smtpd

#dnsblog   unix  -       -       n       -       0       dnsblog

#tlsproxy  unix  -       -       n       -       0       tlsproxy

submission inet n       -       n       -       -       smtpd

  -o syslog_name=postfix/submission

  -o smtpd_tls_security_level=encrypt

  -o smtpd_sasl_auth_enable=yes

  -o smtpd_reject_unlisted_recipient=no

#  -o smtpd_client_restrictions=$mua_client_restrictions

#  -o smtpd_helo_restrictions=$mua_helo_restrictions

#  -o smtpd_sender_restrictions=$mua_sender_restrictions

  -o smtpd_recipient_restrictions=permit_sasl_authenticated,reject

  -o milter_macro_daemon_name=ORIGINATING

  -o smtpd_enforce_tls=yes  

smtps     inet  n       -       n       -       -       smtpd

  -o syslog_name=postfix/smtps

  -o smtpd_tls_wrappermode=yes

  -o smtpd_sasl_auth_enable=yes

  -o smtpd_reject_unlisted_recipient=no

#  -o smtpd_client_restrictions=$mua_client_restrictions

 # -o smtpd_helo_restrictions=$mua_helo_restrictions

 # -o smtpd_sender_restrictions=$mua_sender_restrictions

 # -o smtpd_recipient_restrictions=permit_sasl_authenticated,reject

  -o milter_macro_daemon_name=ORIGINATING

   -o smtpd_enforce_tls=yes

#628       inet  n       -       n       -       -       qmqpd

pickup    unix  n       -       n       60      1       pickup

cleanup   unix  n       -       n       -       0       cleanup

qmgr      unix  n       -       n       300     1       qmgr

#qmgr     unix  n       -       n       300     1       oqmgr

tlsmgr    unix  -       -       n       1000?   1       tlsmgr

rewrite   unix  -       -       n       -       -       trivial-rewrite

bounce    unix  -       -       n       -       0       bounce

defer     unix  -       -       n       -       0       bounce

trace     unix  -       -       n       -       0       bounce

verify    unix  -       -       n       -       1       verify

flush     unix  n       -       n       1000?   0       flush

proxymap  unix  -       -       n       -       -       proxymap

proxywrite unix -       -       n       -       1       proxymap

smtp      unix  -       -       n       -       -       smtp

relay     unix  -       -       n       -       -       smtp

#       -o smtp_helo_timeout=5 -o smtp_connect_timeout=5

showq     unix  n       -       n       -       -       showq

error     unix  -       -       n       -       -       error

retry     unix  -       -       n       -       -       error

discard   unix  -       -       n       -       -       discard

local     unix  -       n       n       -       -       local

virtual   unix  -       n       n       -       -       virtual

lmtp      unix  -       -       n       -       -       lmtp

anvil     unix  -       -       n       -       1       anvil

scache    unix  -       -       n       -       1       scache

#

# =================================================

# Interfaces to non-Postfix software. Be sure to examine the manual

# pages of the non-Postfix software to find out what options it wants.

#

# Many of the following services use the Postfix pipe(8) delivery

# agent.  See the pipe(8) man page for information about ${recipient}

# and other message envelope options.

# =================================================

#

# maildrop. See the Postfix MAILDROP_README file for details.

# Also specify in main.cf: maildrop_destination_recipient_limit=1

#

#maildrop  unix  -       n       n       -       -       pipe

#  flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient}

#

# ==================================================

 

#

# Recent Cyrus versions can use the existing "lmtp" master.cf entry.

#

# Specify in cyrus.conf:

#   lmtp    cmd="lmtpd -a" listen="localhost:lmtp" proto=tcp4

#

# Specify in main.cf one or more of the following:

#  mailbox_transport = lmtp:inet:localhost

#  virtual_transport = lmtp:inet:localhost

#

# ===================================================

#

# Cyrus 2.1.5 (Amos Gouaux)

# Also specify in main.cf: cyrus_destination_recipient_limit=1

#

#cyrus     unix  -       n       n       -       -       pipe

#  user=cyrus argv=/usr/lib/cyrus-imapd/deliver -e -r ${sender} -m ${extension} ${user}

#

# ===================================================

#

# Old example of delivery via Cyrus.

#

#old-cyrus unix  -       n       n       -       -       pipe

#  flags=R user=cyrus argv=/usr/lib/cyrus-imapd/deliver -e -m ${extension} ${user}

#

# ================================================

#

# See the Postfix UUCP_README file for configuration details.

#

#uucp      unix  -       n       n       -       -       pipe

#  flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient)

#

# ================================================

#

# Other external delivery methods.

#

#ifmail    unix  -       n       n       -       -       pipe

#  flags=F user=ftn argv=/usr/lib/ifmail/ifmail -r $nexthop ($recipient)

#

#bsmtp     unix  -       n       n       -       -       pipe

#  flags=Fq. user=bsmtp argv=/usr/local/sbin/bsmtp -f $sender $nexthop $recipient

#

#scalemail-backend unix -       n       n       -       2       pipe

#  flags=R user=scalemail argv=/usr/lib/scalemail/bin/scalemail-store

#  ${nexthop} ${user} ${extension}

#

#mailman   unix  -       n       n       -       -       pipe

#  flags=FR user=list argv=/usr/lib/mailman/bin/postfix-to-mailman.py

#  ${nexthop} ${user}

''''''''''''********************************* 

Add hostname and domain name as per url I gave above in main.cf

hostnamectl set-hostname mail.mysite.com

In order to apply the new hostname, a system reboot is required.

 

In your DNS provider, make sure that mail.mysite.com points to 1.2.3.4. I set this by adding a A record 

to cloudflare

Also make sure that rDNS is set to mail.mysite.com

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

To obtain a certificate first install nginx 

Step 1 — Installing the Certbot Let’s Encrypt Client

The first step to using Let’s Encrypt to obtain an SSL certificate is to install the certbot software on your server. Currently, the best way to install this is through the EPEL repository.

Enable access to the EPEL repository on your server by typing:

sudo yum install epel-release

 

Once the repository has been enabled, you can obtain the certbot-nginx package by typing:

sudo yum install certbot-nginx

 

The certbot Let’s Encrypt client is now installed and ready to use.

Step 2 — Setting up Nginx

If you haven’t installed Nginx yet, you can do so now. The EPEL repository should already be enabled from the previous section, so you can install Nginx by typing:

sudo yum install nginx

 

Then, start Nginx using systemctl:

sudo systemctl start nginx

 

Certbot can automatically configure SSL for Nginx, but it needs to be able to find the correct server block in your config. It does this by looking for a server_name directive that matches the domain you’re requesting a certificate for. If you’re starting out with a fresh Nginx install, you can update the default config file:

sudo vi /etc/nginx/nginx.conf

 

Find the existing server_name line:

/etc/nginx/sites-available/default

server_name _;

 

Replace the _ underscore with your domain name:

/etc/nginx/nginx.conf

server_name mail.mysite.com;

 

Save the file and quit your editor. Verify the syntax of your configuration edits with:

sudo nginx -t

 

If that runs with no errors, reload Nginx to load the new configuration:

sudo systemctl reload nginx

 

Certbot will now be able to find the correct server block and update it. Now we’ll update our firewall to allow HTTPS traffic.

Step 3 — Updating the Firewall

If you have a firewall enabled, make sure port 80 and 443 are open to incoming traffic. If you are not running a firewall, you can skip ahead.

If you have a firewalld firewall running, you can open these ports by typing:

sudo firewall-cmd --add-service=http
sudo firewall-cmd --add-service=https
sudo firewall-cmd --runtime-to-permanent

 

If there is an  iptables  firewall running, the commands you need to run are highly dependent on your current rule set. For a basic rule set, you can add HTTP and HTTPS access by typing:

sudo iptables -I INPUT -p tcp -m tcp --dport 80 -j ACCEPT
sudo iptables -I INPUT -p tcp -m tcp --dport 443 -j ACCEPT

 

We're now ready to run Certbot and fetch our certificates.

Step 4 — Obtaining a Certificate

Certbot provides a variety of ways to obtain SSL certificates, through various plugins. The Nginx plugin will take care of reconfiguring Nginx and reloading the config whenever necessary:

sudo certbot --nginx -d mail.mysite.com 

 

This runs  certbot with the  --nginx plugin, using  -d to specify 

If it's successfull, you should see:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Congratulations! You have successfully enabled https://mail.mysite.com

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

IMPORTANT NOTES:

 - Congratulations! Your certificate and chain have been saved at:

   /etc/letsencrypt/live/mail.mysite.com/fullchain.pem

   Your key file has been saved at:

   /etc/letsencrypt/live/mail.mysite.com/privkey.pem

   Your cert will expire on 2021-04-16. To obtain a new or tweaked

   version of this certificate in the future, simply run certbot again

   with the "certonly" option. To non-interactively renew *all* of

   your certificates, run "certbot renew"

 - If you like Certbot, please consider supporting our work by: 

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

Now add a Linux user to be authenticated and send emails:

useradd mailman
passwd supersecret1234

 

Install dkim as using this guide:   https://tecadmin.net/setup-domainkeys-dkim-on-postfix-centos-rhel/

Step 1 – Install DKIM-milter

First make sure you have  enabled EPEL  repository in your system. After that install dkim-milter package using following command.

yum install opendkim

Step 2 – Generate Key Pair

Now create DKIM key pair using  dkim-genkey  command line utility provided by dkim-milter package. For this tutorial we are using domain name “example.com”, Change this name with your current names.

MYDOMAIN=mysite.com
 mkdir -p /etc/opendkim/keys/$MYDOMAIN
 cd /etc/opendkim/keys/$MYDOMAIN
 opendkim-genkey -r -d $MYDOMAIN

Above command will generate two files  default.private  and  default.txt . You can create multiple DKIM keys for different-2 domains and configure with your postfix server.

Now set the proper permissions on Keys directory.

chown -R opendkim:opendkim /etc/opendkim
 chmod go-rw /etc/opendkim/keys

Step 3 – Configure OpenDKIM

Edit the Opendkim configuration file and Add/Update following entries in file.

vim /etc/opendkim.conf

sv mode
Socket inet:8891@localhost
Domain example.com
#KeyFile /etc/opendkim/keys/default.private   ### comment this line
KeyTable /etc/opendkim/KeyTable
SigningTable refile:/etc/opendkim/SigningTable
ExternalIgnoreList refile:/etc/opendkim/TrustedHosts
InternalHosts refile:/etc/opendkim/TrustedHosts

Then edit the domain keys lists setting file  /etc/opendkim/KeyTable  and add following entry.

default._domainkey.example.com example.com:default:/etc/opendkim/keys/example.com/default.private

After that edit  /etc/opendkim/SigningTable  file and update following entry.

*@example.com default._domainkey.example.com

And edit  /etc/opendkim/TrustedHosts  file and update following entry.

mail.example.com
example.com

Step 4 – Configure Postfix

Now edit POSTFIX configuration file  /etc/postfix/main.cf  and add following values ​​at the end of file

smtpd_milters = inet:127.0.0.1:8891
non_smtpd_milters = $smtpd_milters
milter_default_action = accept

finally start DKIM service using following command

service opendkim start

Step 5 – Configure DNS Entry

After configuring private key in postfix server. there will be another file  /etc/opendkim/keys/example.com/default.txt  generated by opendkim-genkey. Edit your DNS zone file and add this as TXT record found in default.txt. In my case this is like below.

default._domainkey IN TXT ( "v=DKIM1; k=rsa; s=email; "
"p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCdTtEqM8FqndiFYOderzljMMMqBdEp+wJKP+VUbhc9GigmK34ZjrSqqdKjIEWr2q9DvSV p1H1bZs4t050m0HZxJqknDz2yoDJ6W4mCaSCHesRde5V44V/L65Gqm/rvBz1d6CCp8A2515eveWrIAocOD6pKJ4tnXHz3uwV2ZtgQiQIDAQAB" ) ; ----- DKIM key default for example.com

Place this in Cloudflare DNS as a TXT key-value this:

Key:   default._domainkey

Value: 

v=DKIM1; k=rsa; s=email;
p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCdTtEqM8FqndiFYOderzljMMMqBdEp+wJKP+VUbhc9GigmK34ZjrSqqdKjIEWr2q9DvSV p1H1bZs4t050m0HZxJqknDz2yoDJ6W4mCaSCHesRde5V44V/L65Gqm/rvBz1d6CCp8A2515eveWrIAocOD6pKJ4tnXHz3uwV2ZtgQiQIDAQAB

Step 6 – Verify DKIM

To verify that DKIM is working properly. Let's send a test email through command line

mail -vs "Test DKIM" my_test_email@gmail.com < /dev/null

In the received email in our mailbox, open the source of the email and search for "DKIM-Signature". You will find something like below

DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=example.com;
	s=default.private; t=1402388963;
	bh=fdkeB/A0FkbVP2k4J4pNPoe23AvqBm9+b0C3OY87Cw8=;
	h=Date:From:Message-Id:To:Subject;
	b=M6g0eHe3LNqURha9d73bFWlPfOERXsXxrYtN2qrSQ6/0WXtOxwkEjfoNTHPzoEOlD
	 i6uLLwV+3/JTs7mFmrkvlA5ZR693sM5gkVgVJmuOsylXSwd3XNfEcGSqFRRIrLhHtbC
	 mAXMNxJtih9OuVNi96TrFNyUJeHMRvvbo34BzqWY=

Started below services

systemctl restart saslauthd

systemctl restart postfix

systemctl restart httpd

systemctl restart opendkim

How remove duplicate rows in a large table

-- Create temporary table

CREATE TABLE temp_table LIKE table1;

-- Add constraint

ALTER TABLE temp_table ADD UNIQUE(category);

-- Copy data

INSERT IGNORE INTO temp_table SELECT * FROM table1;

-- Rename and drop

RENAME TABLE table1 TO old_table1, temp_table TO table1;

DROP TABLE old_table1; 

How to format disk larger than 2TB in Linux

Traditionally I used to use fdisk to do so (here), but it is limited to 2TB disks.

These tips are from techmint with some modification.

apt-get install parted 

1. Check Parted Version

Run the following command, you see message similar to the one shown on the image below. Don’t worry if your parted version is different. Unless specified otherwise, parted will use your primary drive, which in most cases will be /dev/sda.

$ parted

Check Parted Command Version

If you want to exit parted, simply type:

$ quit

2. List Linux Disk Partitions

Now that parted is started, let’s list the partitions of the selected hard disk. As mentioned earlier, parted chooses your first drive by default. To see the disk partitions run print.

(parted) print

Check Linux Partitions

When running print, it will also display the hard disk information and model. Here is example from a real hard disk (not virtual as shown on the image above) :

(parted) print

Model: ATA TOSHIBA MQ01ACF0 (scsi)
Disk /dev/sda: 320GB
Sector size (logical/physical): 512B/4096B
Partition Table: msdos

Number  Start   End    Size   Type      File system  Flags

 1      1049kB  256MB  255MB  primary   ext2         boot
 2      257MB   320GB  320GB  extended
 5      257MB   320GB  320GB  logical                lvm

In the example above, you can see the disk model, capacity sector size and partition table.

3. List or Switch to Different Disk

If you have more than one hard disk, you can easily switch between disks, by using the “select” command. In the example below, I will switch from /dev/sda to /dev/sdb which is a secondary drive on my system.

To easily switch between disks you can use:

(parted) select /dev/sdX

Select Different Disk

Change "X" with the letter of the disk to which you wish to switch.

4. Create Primary or Logical Partition in Linux

Parted can be used to create primary and logical disk partitions. In this example, I will show you how to create primary partition, but the steps are the same for logical partitions.

To create new partition, parted uses “mkpart“. You can give it additional parameters like "primary" or "logical" depending on the partition type that you wish to create.

Before you start creating partitions, it’s important to make sure that you are using (you have selected) the right disk.

Start by using print:

(parted) print

Show Current Linux Disk

As shown on the above image, we are using a virtual drive of 34 GB. First we will give the new disk a label and then create a partition and set a file system on it.

Now the first step is to give the new disk a label name with:

(parted) mklabel gpt  #Note: msdos is not suitble for > 2TBs

Now create the new partition with  mkpart. The listed units are in megabytes (MB). We will create a 10 GB partition starting from 1 to 10000:

(parted) mkpart 0% 100% primary 

Create Primary or Logical Linux Partitions

Next,  exit parted with "quit" command. We will format our new partition in ext4 file system using mkfs. To make this happen run the following command:

# mkfs.ext4 /dev/sdb1

Note: It’s important to select the right disk and partition when executing the above command!

Now let’s verify our results, by printing the partition table on our secondary disk. Under file system column, you should see ext4 or the file system type that you have decided to use for your partition:

Verify Disk Partition Filesystem

5. Resize Linux Disk Partition

Parted includes multiple useful functions and one of them is "resizepart". As you have probably figured this out by now, "resizepart" helps you resize a partition.

In the example below, you will see how to resize an existing partition. For the purpose of this example, we will be using the earlier created partition.

First you will need to know the number of the partition that you will be resizing. This can be easily found by using "print":

(parted) print

Find Linux Partition Number

In our example, the partition number is "1". Now run the resizepart command:

(parted) resizepart

You will be asked for the number of the partition that you will resize. Enter it’s number. After that, you will be asked to set the new ending point for this partition. Remember that by default the units are in MB. In our example, we have set the new partition size to 15 GB:

(parted) resizepart 
Partition number? 1
End?  [10.0GB]? 15000

Now verify the results with "print":

(parted) print

Verify Linux Resize Partition

6. Delete Linux Partition

The next thing you will learn is how to delete a partition from your hard drive. To do this, you will need to use the "rm" command within parted. To delete a disk partition you will need to know it’s number.

As mentioned earlier, you can easily obtain this number by using "print". In our example, we will delete the partition with number 1 from our secondary drive /dev/sdb1:

(parted) rm 1

Verify the results by printing the partitions table:

Delete a Linux Partition

7. Rescue Linux Disk Partition

Parted supports a “rescue" utility that helps you recover a lost partition between a starting and ending point. If a partition is found within that range, it will attempt to restore it.

Here is an example:

(parted) rescue
Start? 1
End? 15000
(parted) print
Model: Unknown (unknown)
Disk /dev/sdb1: 15.0GB
Sector size (logical/physical): 512B/512B
Partition Table: loop
Disk Flags:

Number Start End Size File system Flags
1 0.00B 15.0GB 15.0GB ext4

8 Change Linux Partition Flag

Using parted, you can change the state of a flag for disk partitions. The supported flags are:

1. boot
2. root
3. swap
4. hidden
5. raid
6. lvm
7. lba
8. legacy_boot
9. irst
10. esp
11. palo

The states can be either "on" or "off". To change a flag simply run "set" command within parted:

(parted) set 2 lba on

The above command sets lba flag to on for second partition. Verify the results with print:

Change Partition Flag

Conclusion

Parted is a useful and powerful utility that can help you manage your disk partitions in Linux systems. As always, when working with disk partitions you need to be extra careful. It is strongly recommend to go through parted man pages to learn how you can customize it’s output and find more information about its capabilities.

If you have any questions or comments, please do not hesitate to use the comment section below.

How to analyse MySQL slow_query_log like a pro?

How to Properly log slow queries:


Tested on Mariadb 10.3 on Debian 10:

Check if slow query log is running:

MariaDB [(none)]> show global variables like  "%slow%";

+------------------------------+--------------------------------------+
| Variable_name                | Value                                                                                                                                |
+------------------------------+--------------------------------------+
| log_slow_admin_statements    | ON                                                                                                                                   |
| log_slow_disabled_statements | sp                                                                                                                                   |
| log_slow_filter              |
| log_slow_rate_limit          | 1                                                                                                                                    |
| log_slow_slave_statements    | ON                                                                                                                                   |
| log_slow_verbosity           |                                                                                                                                      |
| slow_launch_time             | 2                                                                                                                                    |
| slow_query_log               | OFF                                                                                                                                  |
| slow_query_log_file          | up-slow.log                                                                                                                          |
+------------------------------+---------------------------------



Rotate the log to avoid old data to polute your current analysis:

mv /var/lib/mysql/slow-query.log /var/lib/mysql/slow-query.log.rm1

flush logs:

MariaDB [(none)]> flush logs;

Query OK, 0 rows affected (0.000 sec)

set the log to query ALL queries:

MariaDB [(none)]> set global slow_query_log=1;

Check the long_query_time (this is the threshhold number ins seconds to save a query in slow_query_log)

 MariaDB [(none)]&gt; select @@global.long_query_time;

+--------------------------+
| @@global.long_query_time |
+--------------------------+
|                10.000000 |
+--------------------------+
 1 row in set (0.000 sec)

Set long_query_time to zero to catch all queries. Notice that this affects performance, especially at high qps rates.

MariaDB [(none)]> set global long_query_time=0;
Query OK, 0 rows affected (0.000 sec)

Now watch slow query log size:

 ls  -lh /var/lib/mysql/up-slow.log

When the log file reached a couple fo GB, stop it:

MariaDB [(none)]> set slow_query_log=0;

Download perona query digest:

wget percona.com/get/pt-query-digest

chmod 755 pt-query-digest

Save the slow query log into a separate file:

cp  /var/lib/mysql/up-slow.log /var/lib/mysql/up-slow.log.out

And analyze the file with percona query digest:

perl  pt-query-digest /var/lib/mysql/up-slow.log

How to Properly analyze the log:

The first list gives an overview of the database activity:

# Overall: 8.31M total, 419 unique, 2.52k QPS, 9.38x concurrency _________
# Time range: 2020-04-08 09:06:28 to 10:01:24
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time         30923s       0      3s     4ms    16ms    21ms   167us
# Lock time           461s       0   385ms    55us    89us   142us    54us
# Rows sent         21.60M       0  58.01k    2.72    0.99  261.70    0.99
# Rows examine      26.66G       0   1.52M   3.36k  313.99  27.22k    0.99
# Rows affecte      38.95k       0      40    0.00       0    0.08       0
# Bytes sent        12.90G       0  23.10M   1.63k   2.27k  99.98k  874.75
# Query size         3.20G       6 464.07k  412.69  833.10 1012.18  313.99

The more interesting though is Profile of individual queries:

# Profile
# Rank Query ID                    Response time   Calls   R/Call V/M   It
# ==== =========================== =============== ======= ====== ===== ==
#    1 0xC549CF6B5AA5A6C68867CA... 6078.5728 19.7%   50213 0.1211  0.01 SELECT post_commentlike

Profile lists the queries which, altogether consumed have  %95 of the processing time.

Rank: Queries sorted by overal time taken to execute.
Response time : total time spent on this query (in milisecons and % of total time)
Calls: How many times the query is called
R/Call: Response tiem per call. Avergage response time per call.
V/M: Variance to mean ration. AKA index of dispersion.  0 no variance, 1 most variant.
Item: Abreviate version of a query.


Check queries and Index the tables when needed:

CREATE INDEX col ON tbl(col);

Source: This great video:

More info:

http://www.speedemy.com/

Using ‘dd’ to benchmark disk performance

 To get started you should change to a directory to which you have read and write permissions. Or create and enter a new directory:

mkdir /home/bench/
cd /home/bench/

Make sure you have a few gigabytes of free disk space. Then use the following command to test the write speed of your storage:

dd if=/dev/zero of=diskbench bs=1M count=1024 conv=fdatasync

The results looks something like this from Stacklinux’s 3GB VPS:

1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.553284 s, 1.9 GB/s

You’ll want this to be above 400 MB/s.

Using ‘dd’ to benchmark storage read performance

Now, lets delete the server’s buffer cache in order to measure ‘read’ speeds direct from the hard drive:

echo 3 | sudo tee /proc/sys/vm/drop_caches

Now that cache is deleted, we can test disk read performance of that ‘diskbench’ file using:

dd if=diskbench of=/dev/null bs=1M count=1024

This outputs the following:

1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.425812 s, 2.5 GB/s

Using ‘dd’ to benchmark memory-buffered read performance

After running the above command, the data will be pushed to memory-buffered cache. So lets test read speeds using memory buffer by repeating the previous command:

dd if=diskbench of=/dev/null bs=1M count=1024

Which outputs the following:

1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.135034 s, 8.0 GB/s

You should run this test a couple of times to a find the average. Lastly, remember to delete the 1.1GB test file using this command:

rm diskbench

If your results point to poor read/write performance you may want to look into upgrading hardware or switching your web hosts. In addition, more extensive tests can be performed using fio, bonnie++ or IOzone.

Using ‘dd’ to benchmark CPU performance

If you would like to verify CPU speed and # of cores. Use the following commands:

lscpu

‘lscpu’ gathers CPU architecture information from sysfs and /proc/cpuinfo. The information includes the number of CPUs, threads, cores etc. There is also information about the CPU caches and cache sharing, family, model, bogoMIPS, byte order, and stepping.

nproc

‘nproc’ prints the number of processing units (cpu cores) available.

Now ‘dd’ can also be used for a simple CPU benchmark using:

dd if=/dev/zero bs=1M count=1024 | md5sum

The results:

1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 2.5454 s, 422 MB/s
cd573cfaace07e7949bc0c46028904ff -

For most modern CPUs, you’ll want to see a minimum of 300 MB/s. Lower than that should prompt you to perform more accurate tests using BYTEmark or even unixbench. To sum things up, no matter your experience level, ‘dd’ can be used as a quick check into the disk, memory and CPU performance of your web server.

Using speedtest-cli for testing internet bandwidth

speedtest-cli is a command line interface for testing internet bandwidth using speedtest.net. Issue the following commands to download and run the network speed test script:

wget -O speedtest-cli https://raw.githubusercontent.com/sivel/speedtest-cli/master/speedtest.py
chmod +x speedtest-cli

or

curl -Lo speedtest-cli https://raw.githubusercontent.com/sivel/speedtest-cli/master/speedtest.py
chmod +x speedtest-cli

./speedtest-cli

or for usage/help use:

./speedtest-cli -h

For reference, here’s a cut-an-paste of results as tested on an 8GB DigitalOcean droplet:

[root@droplet ~]# dd if=/dev/zero of=diskbench bs=1M count=1024 conv=fdatasync
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 2.51205 s, 427 MB/s
[root@droplet ~]# dd if=/dev/zero of=diskbench bs=1M count=1024 conv=fdatasync
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 2.84208 s, 378 MB/s
[root@droplet ~]# dd if=/dev/zero of=diskbench bs=1M count=1024 conv=fdatasync
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 2.09716 s, 512 MB/s
[root@droplet ~]# echo 3 | sudo tee /proc/sys/vm/drop_caches
3
[root@droplet ~]# dd if=diskbench of=/dev/null bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 1.55411 s, 691 MB/s
[root@droplet ~]# dd if=diskbench of=/dev/null bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.348052 s, 3.1 GB/s
[root@droplet ~]# dd if=diskbench of=/dev/null bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.202393 s, 5.3 GB/s
[root@droplet ~]# rm diskbench
rm: remove regular file `diskbench'? y
[root@droplet ~]# nproc
4
[root@droplet ~]# dd if=/dev/zero bs=1M count=1024 | md5sum
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 3.34115 s, 321 MB/s
cd573cfaace07e7949bc0c46028904ff -
[root@droplet ~]# dd if=/dev/zero bs=1M count=1024 | md5sum
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 3.17004 s, 339 MB/s
cd573cfaace07e7949bc0c46028904ff -
[root@droplet ~]# dd if=/dev/zero bs=1M count=1024 | md5sum
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 3.09961 s, 346 MB/s
cd573cfaace07e7949bc0c46028904ff -
[root@droplet ~]#

Source