# mysql –version
mysql Ver 15.1 Distrib 5.5.37-MariaDB, for Linux (x86_64) using readline 5.1

I ran the mysql command on the system – I received the error message shown below:

# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

I checked to see if the mysqld service was running and found it was not.

# systemctl status mysqld.service
mysqld.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)

When I tried to start the service, it wouldn’t start.

# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Failed to issue method call: Unit mysqld.service failed to load: No such file or directory.
# systemctl start mysqld.service
Failed to issue method call: Unit mysqld.service failed to load: No such file or directory.

I checked the contents of the /etc/my.cnf file and saw the following:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

I checked for the existence of files and directories

# ls -ld /var/lib/mysql
drwxr-xr-x. 19 mysql mysql 4096 Oct 14 23:46 /var/lib/mysql

[root@localhost install]# ls -l /var/lib/mysql/mysql.sock
srwxrwxrwx. 1 mysql mysql 0 Sep 29 15:05 /var/lib/mysql/mysql.sock

# ls -l /var/log/mariadb/mariadb.log
-rw-r-----. 1 mysql mysql 0 Oct  5 20:49 /var/log/mariadb/mariadb.log

# ls -l /var/run/mariadb/mariadb.pid
ls: cannot access /var/run/mariadb/mariadb.pid: No such file or directory

I didn’t see any mariadb.pid file

When I checked the files in the includedir directory, I saw the following:

# ls -l /etc/my.cnf.d
total 12
-rw-r--r--. 1 root root 295 Apr 15  2014 client.cnf
-rw-r--r--. 1 root root 232 Apr 15  2014 mysql-clients.cnf
-rw-r--r--. 1 root root 744 Apr 15  2014 server.cnf

So..how to start the service

# systemctl start mariadb.service
# systemctl status mysqld service
mysqld.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)
service.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)

# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

When I was using CentOS 6, I had MySQL rather than MariaDB – didn’t know I needed to start the MariaDB RDBMS service on the CentOS 7 system using a command referencing mariadb rather mysqld. I assumed I could reference mysqld to start the service.

To have the MariaDB service start automatically each time the system boots, issue the command

“systemctl enable mariadb.service”. After you have started the service, also issue the command

“mysql_secure_installation”.

# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
ERROR 1146 (42S02) at line 1: Table 'mysql.servers' doesn't exist
 ... Failed!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Note on copying files to the maria DB!

If you copy DB files to /var/lib/mysql directory from the hard drive of the prior CentOS 6 system to the new CentOS 7 system to have all of the databases from the old system available on the new system, so that appeared to be the reason that the mysql_secure_installation, which can be found in /usr/bin, did not accept my just hitting Enter for the password initially. When I entered the root password for MySQL on the old system, it was accepted. And I was able finally get a prompt where I could enter SQL commands using that password with mysql -u root -p .

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.37-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

So the 3 steps to enable and run MariaDB are as follows, assuming it was previously installed during the initial setup for the system or with yum install mariadb mariadb-server:

# systemctl start mariadb.service
# systemctl enable mariadb.service
# mysql_secure_installation 

From MySQL Large Configuration.

MySQL Large Original

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

After Optimization

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

Other ideas…

mysql optimize

innodb_buffer_pool_instances = 2
reduced the number of max connections to 500 (which is still high)
syn error

adjusting /proc/sys/net/ipv4/tcp_max_syn_backlog

nf_conntrack: table full, dropping packet.

network and ulimit settings

number of connections in time_wait – how to contrl:

set both tcp_tw_recycle and lowered tcp_fin_timeout to 30

# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Linux Shortcuts

Find Plesk Password on older plesk versions. Login via ssh as root and run the following command:

# /usr/local/psa/bin/admin --show-password

Show encrypted admin password:

# cat /etc/psa/.psa.shadow

Show Plesk MySQL password

# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Show processlist

# mysqladmin -u admin -p`cat /etc/psa/.psa.shadow` processlist

Export Plesk database

# mysqldump  -u admin -p`cat /etc/psa/.psa.shadow` database > /tmp/database.sql

Import Plesk database
# mysql -u admin -p`cat /etc/psa/.psa.shadow` database < /tmp/database.sql Email Commands [bash] /usr/local/psa/admin/bin/mailqueuemng -s tail -50 /usr/local/psa/var/log/maillog tail -50 /var/log/maillog tail -50 /var/log/exim_mainlog [/bash] RAM [bash] # curl -s http://dev.patlathem.com/mem.txt | python [/bash] Windows Shortcuts [bash] cd %plesk_bin% plesksrvclient -get [/bash] [bash] rdesktop -g 1024x768 -P IPaddress -u administrator rdesktop ipaddress -g 1024x768 [/bash] [bash] # mysql -uroot -p status Uptime: 68730 Threads: 378 Questions: 459474 Slow queries: 0 Opens: 17283 Flush tables: 15 Open tables: 201 Queries per second avg: 6.685 [/bash] [bash] # mysqladmin -uroot -p processlist Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 13 | root | localhost | | Query | 0 | init | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ [/bash]

# mysqladmin kill process id

http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://www.percona.com/blog/2014/03/14/tools-and-tips-for-analysis-of-mysqls-slow-query-log/

There is a nice tool like Percona’s ‘pt-query-digest’ tool at http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html, which will automatically parse the slow_query_log and analyze it for the slowest queries.

you run the command

pt-query-digest slowquerylog.txt

Wich should output a summary and a list of the longest-to-execute queries.