Install MySQL plugin for Newrelic in a Minute

To use this plugin, you must have:

  • a Java Runtime Environment (JRE) of 1.6 or higher
  • at least one database to monitor (MySQL 5.0 or higher)
  • a New Relic account

New Relic Platform Installer (NPI) is a simple, lightweight command line tool that helps you easily download, configure and manage New Relic Platform Plugins

Plugin for Generic Linux OS (OS = Opensuse)

LICENSE_KEY=4eeeeeeeeeeeeeeeeeeeeeeee2e bash -c "$(curl -sSL https://download.newrelic.com/npi/release/install-npi-linux-x64.sh)"
npi install com.newrelic.plugins.mysql.instance

Configuration File

#vim ~/newrelic-npi/plugins/com.newrelic.plugins.mysql.instance/newrelic_mysql_plugin-2.0.0/config/plugin.json

 

{
"agents": [
{
"name" : "Host Name on Newrelic UI",
"host" : "localhost/RDS ENpoint",
"metrics" : "status,newrelic",
"user" : "DB_USER_NAME",
"passwd" : "DB_PASSWORD"
}
]
}

Start Plugin:

#cd /root/newrelic-npi/plugins/com.newrelic.plugins.mysql.instance/newrelic_mysql_plugin-2.0.0
#java -Xmx128m -jar plugin.jar

 

 

GitHub = https://github.com/newrelic-platform/newrelic_mysql_java_plugin
Plugin Home Page = https://rpm.newrelic.com/accounts/748441/plugins/directory/52

Database Sharding

 

 

“share nonthing” : Key Law on database sharding Architecture.
Small Databases are Fast, Big Databases are Slow !!!
DB Sharding – Breaking a Bigger DB into a Smaller DB.

Key Points on DB Sharding —

  • Partition Data across master
  • Writes and read are distributed
  • Application needs modification
  • Needs choice partitioning strategy for uniform data distribution.

Issues –

  • Joins cannot be performed across shards
  • Application modification can be expensive.
  • Example : Evernote uses database sharding – localized failures , no need for joins. Each shards handles all  data &  traffic for about 100,000 users.

Sharding is another way to resolve MySQL scalability issues. It usually means splitting up the data by some logic derived from the application. This can be done by selecting a key in the data and splitting the data by hashing that key and having some distribution logic. It can also be done by identifying the application needs and setting different tables or different data sets in different databases (splitting the North-America sales data from the EMEA sales data, etc.)

This approach is simple from the database standpoint, but is very complex from the application standpoint since the application needs to be modified to deal with the data being scattered into the different shards. Moreover, combining data from different shards can be very complex and involves development in the application (you can’t just run a simple JOIN.)

Advantages of scaling out and in using sharding:

* Scales beyond the limitations of a single machine
* Scales both read and write operations (but makes some operations impossible to achieve in the database)
* Scales both throughput and capacity

Disadvantages of scaling out and in using sharding:

* Complex and requires application changes
* Scaling is usually offline and requires a re-partitioning event – and may require application changes.

Today, there are some solutions that introduce auto-sharding (Scalebase, Dbshards). This approach makes sharding more similar to shared-nothing partitioning, thus taking the sting out of some sharding complexities. However, it still requires application awareness and could prove to be a limiting factor if you needed to update your app or migrate to a different database solution.

Quick MySQL Commands

To Login into the MySQL

#mysql -u username -ppaswword

and if its RDS :

#mysql -h rds.indexpoint/dnsname/ip -u username -ppassword

To List Databases

mysql>show databases;

To View tables

mysql>use databasename;
mysql>show tables;

To View Contents Inside the Table

mysql>select * from tablename;

To Delete Database

mysql>drop database dbname;

To create Database user (we also need to grant permission to a db to gain access for the new user.)

mysql>create user 'UNNI'@'ipaddress/%/localhost' identified by 'passwd3@1';

To Grant Permission to a Database for a User and Create the User at the same Time

mysql>grant all on databasename.* to 'UNNI'@'ipaddress/%/localhost' identified by 'password';

[NOTE– To provide access from all IP Address use ‘%’ instead of ipaddress. By default ‘localhost’ is the value that accepts on a normal mysql installation on a standalone EC2 machine]

To List out all Database Users

mysql>SELECT user,host FROM mysql.user;

To Create a Database

mysql>create database UNNI;

To restore a specific Database in Mysql (database UNNI has to be created already)

#mysql -u username -ppassword UNNI < UNNI.sql

[NOTE: Database UNNI had to be created before restore]

To dump/backup a specific Database in Mysql

#mysqldump -u username -ppassword UNNI > UNNI.sql

[NOTE: Database UNNI is backed up into UNNI.sql]

To know the permission of Mysql User

#show grants for 'unni'@'ipaddress/%/localhost';

Get MySQL Server Info

mysql> \s

To list all mysql admin tables

mysql> show tables from mysql;

To list mysql variables related to Errors

mysql> use dbname;
mysql> show variables like '%err%';

Deleting Users

To delete users from the MySQL database use the DROP command.

 mysql>DROP USER user@host;

The command in turn removes the user record from the mysql.user table.

As the CREATE USER command, even the DROP USER command has been added since MySQL 5.0.2. In previous versions of MySQL you must revoke the user’s privileges first, delete the records from user manually and then issue the FLUSH PRIVILEGES command.

mysql>DELETE FROM user WHERE User= 'technofriends' AND Host= 'localhost';
FLUSH PRIVILEGES;

General Notes

  • There is no concept in MySQL of “Owner” of database or its objects, as there is in MS Access and MS SQL Server. I surmise this from the lack of “owner” field anywhere in mysql system tables.

Create a Read-Only User in MySQL

create user 'new_user'@'%' identified by 'my_tough_passwd';
GRANT SELECT, SHOW VIEW ON database_name.* TO new_user@'%' IDENTIFIED BY 'my_tough_passwd';
FLUSH PRIVILEGES;

To show current user

mysql>select current_user();

Find if a query is doing a full table scan

mysql> show full processlist;
| 7 | root | ip-10-142-159-56:50960 | mydb | Sleep | 0 | | NULL |
| 8 | root | ip-10-142-191-57:60270 | mydb | Query | 0 | Sending data | SELECT `mycustomtable`.* FROM `mycustomtable` WHERE `mycustomtable`.`column_id` = 8373 LIMIT 1 |
| 9 | root | ip-10-138-58-103:35042 | mydb | Sleep | 0 | | NULL
mysql>  explain SELECT  `mycustomtable`.* FROM `mycustomtable`  WHERE `mycustomtable`.`some_columnvalue` = 8373 LIMIT 1
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | mycustomtable | ALL | NULL | NULL | NULL | NULL | 11906 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

The ALL means – This query is doing a full table scan.

 

Restore Dump and You get error : max_allowed_size error (got a packet bigger than ‘max_allowed_packet’ bytes)

 

mysql>show global variables where variable_name='max_allowed_packet';
mysql>SET GLOBAL max_allowed_packet=1073741824;

Quick Fixes

 

1.Kill MySQL sessions which are in sleep state

a. Login to the DB server

b. Execute the below command

echo "select ID FROM INFORMATION_SCHEMA.PROCESSLIST where Command='Sleep' ;" | mysql -u username -p'password' | awk '{ print "kill "$1";"}' > kill.sql

c. Execute the kill.sql in the DB

#mysql -u username -p'passowrd'
mysql> source kill.sql

2.Error While Taking dump from mysql 5.6

mysqldump: Couldn’t execute ‘SET OPTION SQL_QUOTE_SHOW_CREATE=1’: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘OPTION SQL_QUOTE_SHOW_CREATE=1’ at line 1 (1064)

The reason for this is that MySQL 5.6 has removed support for “SET OPTION” and your mysql client tools are probably on older version. Most likely 5.5 or 5.1. There is more info about this issue on MySQL bugs website. The quickest solution is to update your mysql client tools to 5.6 and your problem will be solved. Unfortunately, there is now official binary of MySQL 5.6 tools for Ubuntu at the moment. However, I did find a solution on the good old GitHub where you can add this custom MySQL 5.6 client tools to your ubuntu repository. It works like a charm. To install mysql client tools 5.6 on ubuntu run the following commands:

sudo add-apt-repository ppa:ondrej/mysql-experimental
sudo apt-get update
sudo apt-get remove mysql-client-5.5
sudo apt-get install mysql-client-5.6

Now you should be able to run mysqldump backups with MySQL 5.6.