Setting up MYSQL in CENTOS 6.9 Command Line

Prerequisite:

Whilst using MYSQL SERVER 5.7 or above kindly ensure to comment the line

#thread_concurrency = 8

 in the file (“/etc/my.cnf “  or “/etc/mysqld/my.cnf “ ) as its deprecated from version 5.7.

Installation Commands:

yum remove mysql57-community-release.noarch
yum clean all
yum install https://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm
yum install mysql-community-server

Now Start the Server using the following commands:

/etc/init.d/mysql start ## use restart after update

## OR ##

service mysql start ## use restart after update

chkconfig –levels 235 mysqld on

service mysql status

Note: IF service doesn’t start check logs for error using command ‘cat /var/logs/mysqld.log’, if table is damaged kindly run commands to fix

            /etc/init.d/mysqld start –skip-grant-tables && mysql_upgrade

            ## OR ##

            service mysqld start –skip-grant-tables && mysql_upgrade

        /etc/init.d/mysqld status

          ## OR ##

         service mysql status

If the UserName & Password for the server already exists then use command,

            mysql -u root -p

and enter the password to login.

If it’s a first time installation then use the following command to generate “temporary password” to login,

            sudo grep “temporary password” /var/log/mysqld.log

Example Output:

[Note] A temporary password is generated for root@localhost: -et)QoL4MLid

 

Next Follow the steps below to set up the server database (Optional):

 MySQL Secure Installation

  • Change root password
  • Remove anonymous users
  • Disallow root login remotely
  • Remove test database and access to it
  • Reload privilege tables

Start MySQL Secure Installation with following command

/usr/bin/mysql_secure_installation

Output:

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

VALIDATE PASSWORD PLUGIN can be used to test passwords

and improve security. It checks the strength of password

and allows the users to set only those passwords which are

secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8

MEDIUM Length >= 8, numeric, mixed case, and special characters

STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

Using existing password for root.

Estimated strength of the password: 100

Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password:

Re-enter new password:

Estimated strength of the password: 50

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

By default, a MySQL installation has an anonymous user,

allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y

Success.

By default, MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y

Success.

All done!

Note: If you don’t want some reason, do a “MySQL Secure Installation” then at least it’s very important to change the root user’s password

mysqladmin -u root password [your_password_here]

## Example ##

mysqladmin -u root password myownsecrectpass

  1. Connect to MySQL database (localhost) with password

mysql -u root -p

## OR ##

mysql -h localhost -u root -p

  1. Create Database, Create MySQL User and Enable Remote Connections to MySQL Database

This example uses following parameters:

  • DB_NAME = webdb
  • USER_NAME = webdb_user
  • REMOTE_IP = 10.0.15.25
  • PASSWORD = password123
  • PERMISSIONS = ALL

## CREATE DATABASE ##

mysql> CREATE DATABASE webdb;

## CREATE USER ##

mysql> CREATE USER ‘webdb_user’@’10.0.15.25’ IDENTIFIED BY ‘password123’;

## GRANT PERMISSIONS ##

mysql> GRANT ALL ON webdb.* TO ‘webdb_user’@’10.0.15.25’;

##  FLUSH PRIVILEGES, Tell the server to reload the grant tables  ##

mysql> FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘Root@123’ WITH GRANT OPTION;

 

Cant increase MAX_CONNECTIONS mysql on Ubuntu, stuck at 214

On Ubuntu 16.04, increasing the max_connections variable on mysql, does not go beyong 214 connections. Initially the max_connections variable was 100, modified max_connections, under /etc/mysql/my.cnf. The change does not go beyond 214 connections, and whatever specified in max_connections it remains at 214 verified using the command show variables like ‘max_connections’.

To bring the about the change followed up with article https://codepoets.co.uk/2015/mysql-max_connections-stuck-on-214/

It turns out,ubuntu doesn’t have pam_limits.so enabled by default in /etc/pam.d/* files. (But it is commented out, so easy enough to put into place).

/etc/pam.d/common-session :

# without comments.
session	[default=1] pam_permit.so
session	requisite   pam_deny.so
session	required    pam_permit.so
session	required    pam_unix.so 
session required    pam_limits.so
session	optional    pam_ck_connector.so nox11
# end of pam-auth-update config

Once that’s enabled, you can edit /etc/security/limits.conf and adjust the maximum number of file handles (nofile) levels appropriately – which fixes the problem of the system not allowing MySQL to use more file handles for more connections.

e.g.

/etc/security/limits.conf :

.... other stuff ...

*   hard    nofile  8192
*   soft    nofile  4096

(If you then login to the system again, and run ‘ulimit -a’ you’ll see the new numbers appear)

Next – reconfigure MySQL :

/etc/my.cnf:

[mysqld]
....
max_connections = 450
....

and now, once MySQL is restarted :

mysql> show variables like '%max_connecti%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 450   |
+-----------------+-------+
1 row in set (0.00 sec)

and

mysql> show variables like '%open_files_limit%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| open_files_limit           | 5000     |
+----------------------------+----------+

This did not yield expected results.

Going further to investigate, realised Ubuntu has moved from Upstart to Systemd in version 15.04 and no longer respects the limits in /etc/security/limits.conf for system services. These limits now apply only to user sessions.

The limits for the MySQL service are defined in the Systemd configuration file, which you should copy from its default location into /etc/systemd and then edit the copy.

sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service # or your editor of choice

Add the following lines to the bottom of the file:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

You could also set a numeric limit, eg LimitNOFILE=4510.

Now reload the Systemd configuration with:

sudo systemctl daemon-reload

Restart MySQL and it should now obey the max_connections directive.

The same problem may occur on any application hosted on a linux machine. To increase the limits for custom applications, follow the next tutorial. How to Increase File Open Limits on Linux

MySQL Remote access denied – Host is not allowed to connect to this MySQL server

At times when the MySQL server is residing on a different machine, and you want to access MySQL remotely you would get the error as access denied.

To resolve the issue,

First ensure that MySQL server is configured to accept remote connections from the user by verifying the My.cnf

Locate the my.cnf at

vim /etc/mysql/my.cnf, at times the configuration file could also be 
located at etc/mysql/mysql.conf.d/mysqld.cnf

comment out the lines 
#bind-address           = 127.0.0.1
#skip-networking

Restart the MySQL Server


Give permissions to the user who is trying to connect the MySQL Server
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Verify that the privileges have been assigned
SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";

Finally flush the privileges to take effect.
FLUSH PRIVILEGES;

Just in case if mistakingly privileges have been given to revoke the privileges to the uer

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'%';
Following will revoke all options for USERNAME from particular IP:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'1.2.3.4';
Its better to check information_schema.user_privileges table after running REVOKE command.
 

 


 

MySQL Configuration file in Workbench

A fresh installation of MySQL – Workbench in ubuntu at times does not reflect the mysql configuration file which is being used by the server. To set it up we first need to know the configuration files used at start up.

Following are the steps to determine which configuration file is used.

Determine mysql process from where it is initiated

user@Sys-201:/etc/mysql/conf.d$ ps aux | grep mysql
mysql    22272  0.1  1.2 1509844 198076 ?      Ssl  14:51   0:01 /usr/sbin/mysqld
user     22312  0.0  0.0  12548  3024 ?        S    14:51   0:00 /bin/bash /usr/bin/mysql-workbench
user     22315  0.0  0.0   4508   860 ?        S    14:51   0:00 /bin/sh /usr/bin/catchsegv /usr/lib/mysql-workbench/mysql-workbench-bin
user     22317  1.5  1.0 1506344 164356 ?      SLl  14:51   0:11 /usr/lib/mysql-workbench/mysql-workbench-bin
user     22514  0.0  0.0  14224   932 pts/1    S+   15:04   0:00 grep –color=auto mysql

besides the mysql row, it should indicate the configuration file which mysql is using when it starts off

If no file found, double check which instance of mysqld is running by issuing
user@Sys-201:/etc/mysql/conf.d$ which mysqld
/usr/sbin/mysqld

The one with the above matches the one listed, issue the following command to detect the order of loading the configuration files.
user@Sys-201:/etc/mysql/conf.d$ mysqld –verbose –help  | grep -A 1 “Default options”
mysqld: Can’t change dir to ‘/var/lib/mysql/’ (Errcode: 13 – Permission denied)
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

 

Once you know for certain that the configuration file used, the same can be associated with MySQL – Workbench by

Database-ManageConnections-> Select Connection in the Left hand pane-> The right hand side pane is enabled, select System Profile Tab-> Configuration File, specify the config file

 

MySQL Metrics

If you can’t measure it, you can’t improve it.

So the biggest thing when it comes to measuring is from where do you start. Here we lay down a few metrics which would help us in setting up things to measure the MySQL performance. This would be a building block and not a comprehensive list  of metrics to measure MySQL performance.

Performance is captured in terms of response times, the quicker the response time the better is the performance, though improving performance could orient towards the resources but we would not be delving into resources to get a better performance, but to profile as to where the could be the areas where performance could be improved.

Ensure to have the mysql slow query log enabled, check the variable by issuing the command

SHOW VARIABLES LIKE ‘%slow_query_log%’;
SET GLOBAL slow_query_log = ‘ON’;

Validate whether slow query log is enabled by issuing the same command, it should be ON now.

Also look up as to where the slow queries are logged by issuing the command

SHOW VARIABLES LIKE ‘%slow_query_log_file%’;

Just in case if you want to alter as to where the query log needs to be saved, issue the command

SET GLOBAL slow_query_log_file = ‘/var/logs/mysql-slow.log’;

With this we are set to start off with query-digest which would profile information for slow queries. This would help us dig deep into areas where the time is being spent in terms of execution and waiting while the query is getting executed.

user@Sys-201:~$ pt-query-digest –limit=100% /var/lib/mysql/Sys-201-slow.log > ptqd1.out

Just in case if you dont get to see any information in the file which is specified, probably you need to use the root login.

https://poormansprofiler.org/

MySQL does not need SQL

https://www.olindata.com/en/blog/2014/08/analysing-slow-mysql-queries-pt-query-digest

Profiling your slow queries using pt-query-digest and some love from Percona Server

https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

http://www.php-trivandrum.org/open-php-myprofiler/

Calculate Mysql Memory Usage – Quick Stored Procedure

https://www.percona.com/blog/2006/05/17/mysql-server-memory-usage/

http://mysql.rjweb.org/doc.php/memory

http://20bits.com/article/10-tips-for-optimizing-mysql-queries-that-dont-suck

MySQL Optimizations

Partitioning

MySQL partitioning is a concept with 2 contexts primarily the horizontal partitioning and the other being vertical partitioning.

Partitioning of relational data, it usually refers to decomposing your tables or breaking your tables either row-wise (horizontally) or column-wise (vertically).

Vertical partitioning, aka row splitting, uses the same splitting techniques as database normalization, but usually the term (vertical / horizontal) data partitioning refers to a physical optimization whereas normalization is an optimization on the conceptual level.

This is usually preferred when there is rare data used in one table, and you move the rare data into another table, thus reducing the overall size when running queries.

Horizontal partitioning“, or sharding, is replicating [copying] the schema, and then dividing the data based on a shard key.

Vertical partitioning” involves dividing up the schema (and the data goes along for the ride).

Horizontal Partitioning in data base

Keeping all the fields Ex:Table Employees has id,name,Geographical location ,email,designation,phone

Ex:1.Keeping all the fields and distributing records in multiple machines.say id= 1-100000 or 100000-200000 records in one machine each and distributing over multiple machines.

Ex:2.Keeping separate databases for Regions EG: Asia Pacific,North America

Key:Picking set of rows based on a criteria

Vertical Partitioning in data base

It is similar to Normalization where the same table is divided in to multiple tables and used with joins if required.

Ex: id, name,designation is put in one table and phone , email which may not be frequently accessed are put in another.

Key:Picking set of columns based on a criteria.

  • Horizontal/Vertical Scaling is different from partitioning

Horizontal Scaling:

is about adding more machines to enable improved responsiveness and availability of any system including database.The idea is to distribute the work load to multiple machines.

Vertical Scaling:

is about adding more capability in the form of CPU,Memory to existing machine or machines to enable improved responsiveness and availability of any system including database.In a virtual machine set up it can be configured virtually instead of adding real physical machines.

https://www.nylas.com/blog/growing-up-with-mysql/

MySQL does not need SQL

https://medium.com/@jeeyoungk/how-sharding-works-b4dec46b3f6

http://highscalability.com/blog/2013/4/15/scaling-pinterest-from-0-to-10s-of-billions-of-page-views-a.html

 http://softwareas.com/horizontally-scaling-databases-mysqlpostgres-sharding/

 https://github.com/evanelias/jetpants

Elias_percona_live_sc_2013

Massively Sharded MySQL at Tumblr Presentation

RDS_WhitePaper

https://noc.wikimedia.org/

http://blog.maxindelicato.com/2008/12/scalability-strategies-primer-database-sharding.html

https://www.percona.com/blog/2016/08/30/mysql-sharding-with-proxysql/

https://www.percona.com/blog/2009/08/06/why-you-dont-want-to-shard/

https://blog.asana.com/2015/04/sharding-is-bitter-medicine/

http://project-voldemort.com

vitess.io

http://www.craigkerstiens.com/2012/11/30/sharding-your-database/

https://www.percona.com/blog/2017/01/30/mysql-sharding-models-for-saas-applications/

MySQL Reset root password

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

mysqld_safe –skip-grant-tables

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

mysql –user=root mysql

update user set Password=PASSWORD(‘new-password’) where user=’root’;
flush privileges;
exit;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.

Note: Incase you get the error after performing the above steps then set the password policy low with the following command:

SET GLOBAL validate_password_policy=LOW;