moodle mysql config change

[client] default-character-set = utf8mb4 [mysqld] innodb_file_format = Barracuda innodb_file_per_table = 1 innodb_large_prefix character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci skip-character-set-client-handshake [mysql] default-character-set = utf8mb4

we need to change the default storage engine to innodb and change the default file format to Barracuda, this is a new setting compared to previous versions. You also need to set innodb_file_per_table in order for Barracuda to work properly. Ref: https://dev.mysql.com/doc/refman/5.6/en/innodb-compression-usage.html

• You should not need to make innodb the default storage engine anymore, the latest version of Moodle will select it automatically during install. It is always a good idea to make it default anyway. You do however need to set the default file format!

for more details follow https://docs.moodle.org/36/en/Step-by-step_Installation_Guide_for_Ubuntu

Upgrading mysql version 5.7

Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error. JSON documents are normalized on creation, and can be compared using most comparison operators such as =, <, <=, >, >=, <>, !=, and <=>; for information about supported operators as well as precedence and other rules that MySQL follows when comparing JSON values

Here are steps to upgrade mysql to mysql5.7 version.

1. Step1 :-

Check the myql packages previously install:-

rpm -qa | grep -i mysql

2.Step2:-

Remove the previous install packages.in our case we have MySql5.5

sudo yum remove mysql-config-5.5.58.1.19.amz1*

it will remove all the packages of mysql5.5.

Once its over we move for next step

3.Step3:-

Install EL6

sudo yum localinstall http://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm

4.Step4:-

sudo yum clean all

5. Step5:-

sudo yum update

6.Step6:-

Sudo yum install mysql-community-server

7.Step7:-

checck the myql version

mysql --version

Mysql Benchmarking tool

I did a study on the benchmarking tools available for mysql. I found mysqlslap to be the ideal tool for this. You can install mysqlslap by using the following command

$home> sudo apt-get install mysqlslap.

Once install you can use mysqlslap --help to get the argument list and synopsis for how to use it.
you can use number of iteration and concurrency limit for benchmarking performance of mysql.

for example:

mysqlslap -uroot -p -q Desktop/emp.sql -i15000

in this case the sql query written in the file emp.sql will run for 15000 times.

One important note is that mysqlslap only work if all of your tables,views,proc used in the query are under mysqlslap database.

mysql dump

mysqldump is the client program for the back up of mysql database/tables.

to generate just schema of a table we can use option -d.

to limit the data in mysqldump we can use option --where="true limit 1000" which will give 1000 records.

ex. command is
mysqldump -u -p [] --where="true limit " > filenameforbackup.sql

MySQL tools

The most popular and best open source database is MySQL. Simple as that. It is consistently fast , reliabile and very, very easy to use. One of MySQLs main features is its cross-platform support, it will run on almost all operating systems, including Windows, OSX, Linux and many more. So, MySQL is the best database, and we all use it. The question now is which is the best MySQL Admin and Development Tool?

MySQL Workbench
This application is a fully featured visual database design package that includes database design, modeling, creation and maintenance bundled into a single environment for the MySQL database system.

Heidi SQL
Heidi SQL is lightweight and fast with a flexible interface, it allows you to browse and edit your databases databases, manage tables, view and edit records, manage user-privileges, import data from text files, run SQL queries, synchronize tables between two databases and export selected tables to other databases or SQL scripts, and all this from an intuitive Windows interface.

Aqua Data Studio
This complete IDE, has four major areas of functionality: Database query and administration tools; a suite of compare tools for databases, source control and file systems; integrated source control client for Subversion (SVN) and CVS; and a powerful database modeler.

phpMyAdmin
This very popular, feature rich and easy to use free tool, written in PHP (obviusly), has been built to handle the full administration of MySQL. It can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage users and permissions, and manage keys on fields.

SQLyog
SQLyog is another popular SQL management tool that has a couple of useful features such as visual schema designer, visual query builder, intelligent code completion and a data / structure synchronization. It support HTTP / HTTPs / SSH tunneling and SSL connections. You can also specify a job manager, define a scheduled database backup and use a notification service to be reminded when some jobs are finished.

MYSQL Front
This graphical GUI for the MySQL database, is a “real” application, it can offer a more refined user-interface than is possible with systems built on PHP and HTML. Response is immediate, as there is no delay of reloading HTML-pages.

SQL Buddy
This cool lightweight ajax powered database management tool is amazing. It is very, very easy to install, unzip the folder to your server and thats it. As for upgrading, delete the older version and replace with the new version, simple.

Navicat Lite MySQL Admin Tool
Navicat is a fast, reliable and popular (millions of downloads) Database Administration tool purpose-built for simplifying database management and reducing administrative costs.

Toad MySQL
Toad for MySQL is a database development and administration tool that provides productivity features for rapidly creating and executing queries and managing complex database systems. It provides utilities to compare, extract and search for objects, manage projects, import/export data and administer the database.

MyCon – MySQL GUI
MyCon is an extremely powerful windows based graphical user interface, MySQL GUI, for administering and working with MySQL server databases. This easy to use MySQL GUI front-end client includes all the functionality and tools you will need to be productive in MySQL as a DBA, developer, manager, report writer or a general end-user.

MySQL Sidu
MySQL Sidu is a FREE MySQL client that works via your web browser, it is simple, intuitive and an easy database tool to use. Sidu is not as powerful as other similiar tools but packs enough punch to merit its place in this list, its features may be limited but it does do exactly what the name says (Sidu stands for Select Insert Delete Update).

How to get XML/HTML result set with mysql

you can get directly HTML,XML format of your resultset by just executing the query by following syntax.

uttam@uttam:~$ mysql -u[userName] -p -H -e "[query]" [database]

where -H is to get HTML resultset.you can replace this with -X to get XML output.
-e is to execute the query specified.

go to the shell promt. and thry this example.
mysql -uroot -p -H -e "select * from user" mysql