Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Sunday, 22 April 2018

How to Install MySQL on Windows Environment

How to Install MySQL on Windows


The easiest way to install MySQl in Windows Machine is to download MySQL installer  from  https://dev.mysql.com/downloads/installer/

MySQL Installer provides an easy to use, wizard-based installation which included the latest versions of all components:

  • MySQL Server
  • MySQL Connectors
  • MySQL Workbench and sample models
  • Sample Databases
  • MySQL for Excel
  • MySQL Notifier
  • MySQL for Visual Studio
  • Documentation
First of all you have to choose right file which you have to download from the website\

  • If you have an online connection while running the MySQL Installer, choose the mysql-installer-web-community file.
  • If you do NOT have an online connection while running the MySQL Installer, choose the mysql-installer-community file.
Note: MySQL Installer is 32 bit, but will install both 32 bit and 64 bit binaries. 


Install MySQL via MySQL Installer

To install MySQL using the MySQL installer, double-click on the MySQL installer file and follow the steps below:

Install MySQL Step 1
Install MySQL Step 1: Windows configures MySQL Installer

Install MySQL Step 2 - Welcome Screen
Install MySQL Step 2 – Welcome Screen: A welcome screen provides several options. Choose the first option: Install MySQL Products

Install MySQL Step 3 - Download the latest MySQL products
Install MySQL Step 3 – Download the latest MySQL products: MySQL installer checks and downloads the latest MySQL products including MySQL server, MySQL Workbench, etc.

Install MySQL Step 4
Install MySQL Step 4: Click Next button to continue

Install MySQL Step 5 - Choosing a Setup Type
Install MySQL Step 5 – Choosing a Setup Type: there are several setup types available. Choose the Full option to install all MySQL products and features.

Install MYSQL Step 6 - Checking Requirements
Install MYSQL Step 6 – Checking Requirements

Install MySQL Step 7 - Installation Progress
Install MySQL Step 7 – Installation Progress: MySQL Installer downloads all selected products. It will take a while, depending on which products that you selected and the speed of your internet connection.

Install MySQL Step 7 - Installation Progress - Downloading Products in Progress
Install MySQL Step 7 – Installation Progress: downloading Products in progress.

Install MySQL Step 7 - Installation Progress - Complete Downloading
Install MySQL Step 7 – Installation Progress: Complete Downloading. Click Next button to continue…

Install MySQL Step 8 - Configuration Overview
Install MySQL Step 8 – Configuration Overview. Click Next button to configure MySQL Database Server

Install MySQL Step 8.1 - MySQL Server Configuration
Install MySQL Step 8.1 – MySQL Server Configuration: choose Config Type and MySQL port (3006 by default) and click Next button to continue.

Install MySQL Step 8.1 - MySQL Server Configuration
Install MySQL Step 8.1 – MySQL Server Configuration: choose a password for the root account. Please note the password download and keep it securely if you are installing MySQL database server on a production server. If you want to add a more MySQL user, you can do it in this step.

Install MySQL Step 8.1 - MySQL Server Configuration
Install MySQL Step 8.1 – MySQL Server Configuration: choose Windows service details including Windows Service Name and account type, then click Next button to continue.

Install MySQL Step 8.1 - MySQL Server Configuration - In Progress
Install MySQL Step 8.1 – MySQL Server Configuration – In Progress: MySQL Installer is configuring MySQL database server. Wait until it is done and click Next button to continue.

Install MySQL Step 8.1 - MySQL Server Configuration - Done
Install MySQL Step 8.1 – MySQL Server Configuration – Done. Click the Next button to continue.

Install MySQL Step 8.2 - Configuration Overview
Install MySQL Step 8.2 – Configuration Overview: MySQL Installer installs sample databases and sample models.

Install MySQL Step 9 - Installation Completes

Now Installation has been completed . Just Click on finish to close installation wizard and login to MySQL client and enjoy .

How to Install MySQL on CentOS 7 ?

Before start in installation you need to ensure that you have logged in with root credential and have proper packages to support MySQL Server.

  1. To check your hostname run:
    hostname  hostname -f  
    The first command should show your short hostname, and the second should show your fully qualified domain name (FQDN).
  2. Update your system:
    sudo yum update  
  3. You will need wget to complete this guide. It can be installed as follows:
    yum install wget

Now Install MySQLPermalink

MySQL must be installed from the community repository.
  1. Download and add the repository, then update.
    wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm  sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm  yum update  
  2. Install MySQL as usual and start the service. During installation, you will be asked if you want to accept the results from the .rpm file's GPG verification. If no error or mismatch occurs, enter y.
    sudo yum install mysql-server  sudo systemctl start mysqld  
MySQL will bind to localhost (127.0.0.1) by default. 
Note
Allowing unrestricted access to MySQL on a public IP not advised but you may change the address it listens on by modifying the bind-address parameter in /etc/my.cnf. If you decide to bind MySQL to your public IP, you should implement firewall rules that only allow connections from specific IP addresses.

Harden MySQL ServerPermalink

  1. Run the mysql_secure_installation script to address several security concerns in a default MySQL installation.
    sudo mysql_secure_installation  
You will be given the choice to change the MySQL root password, remove anonymous user accounts, disable root logins outside of localhost, and remove test databases. It is recommended that you answer yes to these options. 
Now you can Login & Use MySQLPermalink
The standard tool for interacting with MySQL is the mysql client which installs with the mysql-serverpackage. The MySQL client is used through a terminal.
Root LoginPermalink
  1. To log in to MySQL as the root user:
    mysql -u root -p  
  2. When prompted, enter the root password you assigned when the mysql_secure_installation script was run.
    You'll then be presented with a welcome header and the MySQL prompt as shown below:
    mysql>  
  3. To generate a list of commands for the MySQL prompt, enter \h. You'll then see:
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>

MySQL 8.0:Release has been declared Generally Available (GA) with New Features in Replication

Below Update Reference and Copyright to https://mysqlhighavailability.com
 
In this post, I will update you all the new MySQL Replication features introduced in MySQL 8.0 GA Release. Recently MySQL Group Replicate was introduced in MySQL 5.7 and later it was also included in MySQL 8. It is continuously improved either through bug fixing or new features in both MySQL 5.7 and MySQL 8. But this is not the only new thing in MySQL 8. There is more.

Relevant replication areas that have been improved in MySQL 8:

  • Observability: more replication applier queuing details; improved tracing for Group Replication; replication filters statistics; additional cluster statistics on group replication; enhanced visibility into row-based replication.
  • Performance and efficiency: WRITESET based parallel replication applier; efficient synchronization between receiver and applier threads; flow control fine tuning; partial replication of JSON data types.
  • Operations: improved GTIDs handling; better delayed replication;  RESET MASTER command allows specifying the number of the next binary log file; option to expire binary logs with sub-day precision; extraction of log consistent positions for backup purposes; primary election with member weights; whitelisting with support for hostnames; per-channel replication filters in multi-source replication.
  • Resiliency: crash-recoverable DDLs in replication.
  • Usability: support for transaction savepoints for group replication; less locking between receiver, applier and user threads when disk is full.
  • Integration: More table related metadata was added to the binary log; More transaction related metadata also added to the binary log.
  • Code maintenance: removed legacy code (v1-v3 binary log support); removed duplicated information and code; improved structure of the group communication system component in the MySQL sources; removed old LOAD DATA events; removed old log events; added a service to notify group membership events to other internal components.
  • Defaults: several changes in defaults. This shall be covered on a separate blog post.
  • Deprecations: several features were deprecated. This shall be covered on a separate blog post.

New Features

Let's detail the features in each individual section. This blog post will not address deprecations, default changes and feature removals. This will be done on a separate post.

Observability

A lot of work was done to make replication more observable. The focus was set on instrumenting the code and exposing data collected through performance schema. This was done across the board, from instrumenting the group communication system in group replication to the row based replication applier. Let's have a look short at what was done:

  • Monitor row event apply progress. The row-based replication applier reports progress while applying changes. Does this through performance_schema event stages. Therefore, if the applier is seemingly taking a long time to apply a large set of rows, you can check its progress through the performance schema event stages infrastructure.
  • Fetch statistics on the entire replication pipeline. The applier pipeline is now instrumented and data collected is made available to the user through new columns on the existing receiver and applier tables. The user has access to data such as last transaction handled identifier, or commit timestamp with respect to the immediate and original server. Therefore they can use this data as input to diagnose and troubleshoot replication lag. This is also a better framework to infer how much time a replica is behind with respect to the original execution or the immediate relay server in the replication chain.
  • Observe and understand replication filters. A new set of performance schema tables tracks replication filters activities and displays information such as the number of times the filter was triggered or when was it activated.
  • Read cluster-wide statistics through any single member. Performance schema tables for group replication member stats and membership were extended with information stating, for instance, what is the member role or its version. Moreover, every member shows stats of all other members in the group, not just its own stats.
  • Analyze all Group Replication mutexes and threads. Group communication threads, mutexes and conditional variables are instrumented and are now visible in performance schema tables. More details here.
  • Trace low level events in Group Replication. A new tracing infrastructure was introduced in Group Replication so that the user can collect additional information that helps diagnosing and troubleshooting message passing issues in the group communication infrastructure. Read more about this here.

Performance and Efficiency

Making replication efficient and fast is also something that is continuously worked on. In MySQL 8, there are a set of features that enhance the replication applier pipeline, so that it is able to ingest more load coming in, faster and in a more efficient way. Here are the highlights:

  • Transactions spend less time in the replication applier pipeline. There is an improved synchronization mechanism between receiver and applier threads, which translates into less contention between receiver and applier threads. This enhancement allows both receiver and applier threads to do more work in parallel instead of serializing themselves in a lock that protects the relay log.
  • More transactions are applied in parallel.  Much improved parallel applier by relying on transaction WRITESETs (roughly the set of rows changed). This also allows the applier to install changes in parallel even for single threaded workloads coming in from replication. There is much to say about this feature, but you can learn more about the details here.
  • Predictable and sustained replication throughput fine tuning flow control. Flow control was also revised and improved. The user can now fine tune a group replication cluster so that the impact of imbalanced members is limited and the entire system converges to a sustained throughput.
  • Replicate partial JSON objects. The server is able to capture, replicate and apply partial JSON document changes. This makes replication of JSON objects quite efficient, since in row-based replication only a small fraction of the JSON document is captured – the actual changes. This means that the footprint for using JSON objects is small. Read about all the details here.

Operations

A lot of effort was put into operations. As MySQL provides more and more functionality it becomes more of a challenge to make the entire setup easier to use, manage and sustain. Add high availability and replication and all the sudden the system's complexity can easily grow an order of magnitude. Therefore, the ability to observe and then react/manage a system like this becomes rather important. Here are the relevant improvements on this area in MySQL 8:

  • Update @@GTID_PURGED on an active server. The user is able to manipulate GTID_PURGED dynamically on a server that already has a GTID history in it. This facilitates use case scenarios where a logical backup, for instance, needs to be installed on a running server. You can find more details in the manual about this.
  • Delay your replication… in a better way! This is a feature that some users really appreciate because it allows them to keep a server running with artificially delayed replication. This gives the user a constantly updated "backup" shifted in time by a predefined time window. If catastrophic errors happen, for example an unintended DROP TABLE, within the window they can quickly resort to the delayed slave and use it for a fast recovery scheme. The enhancements include clarified semantics (delays are introduced per transaction and not per event as it used to happen) and a reimplementation that builds on new metadata – commit timestamps – introduced also in MySQL 8. Learn more about this here.
  • Reset and name your binary logs with one command. An extension to the RESET MASTER command was contributed and then merged – thank you DaniĆ«l van Eeden. Now one can specify the index number of the binary log file created after removing the existing ones by reseting the logs. This removes some operations burden by collapsing into a single command a set of commands, thus making this easier to sustain and operate. Read more about it here.
  • Expire your half day long binary logs. There is now an option to automatically purge the binary logs by setting a time window that is expressed in seconds. Thus the granularity is not days as it used to be, but rather seconds. This is also explained here.
  • Lock and read log positions almost instantly while taking a backup. A more intuitive and simple way to fetch consistent log positions was introduced in MySQL 8. By querying the performance schema table log_status, the server will "freeze" for a short while. However, despite being quite short, it will collect a consistent view over the log positions, which includes: GTID_EXECUTED; current binary log file name and offset; each replication channel relay log file name and offset; and storage engine log position (in case of InnoDB, Log Sequence Number), provided that the storage engine implements the interface to provide this data. This allows the backup tools to get a consistent position snapshot across binary logs, relay logs and storage engines. More details about this here.
  • Influence the primary election. In Group Replication, when deployed in single primary mode and the primary is removed from the group, a new server is automatically selected from the remaining servers and elected as the new primary. This is all fine and good. In MySQL 8 the user was given a mean to influence which server is going to be picked as the primary when the next primary failure happens. The user can do it by assigning different election weights to different members. The member with the highest weight wins the election and becomes the next primary. As such, this is a big operational enhancement. Many users expressed their needs to be able to create a failover plan that would consider different hardware profiles, different timezones, or different generic properties altogether. More technical details can be found here. This feature was backported also to MySQL 5.7.
  • Whitelist servers based using hostnames instead of IPs. In Group Replication, the user can restrict which servers are allowed to connect. Before this feature got in, the user could do it by specifying an IP address. However, in some setups the IP can float and only the hostnames are reliable for this purpose. As such having the whitelist understand names in addition to IPs would make this possible. Therefore this functionality was introduced in MySQL 8 (and backported to MySQL 5.7 as well). You can read more on this here.
  • Notice Group Replication protecting your data. This feature makes sure that if a server drops out of the group it will automatically set itself to read only. This prevents accidental writes to the members that leave the group both intentionally or unintentionally. More on this here. This feature was first introduced in MySQL 8 and later backported to MySQL 5.7.
  • Filter different data from different sources. Multi-source replication is quite a big feature in MySQL 5.7. In addition to being able to replicate from multiple sources, users want to also have different filter setups per source. MySQL 8 introduced a feature that the user can specify for which channel a given filter is set up. You can read more about it here.

Resiliency

Metaphorically speaking, MySQL has had a brain transplant. It has now a new data dictionary, which among other improvements also brings in crash-recoverable dictionary changes. Roughly, this makes DDLs atomic and recoverable. This functionality is also extended to include binary log recoverability for DDLs.

  • On recovery, recover my atomic DDLs too! When the server is restarting after a crash, the binary log and the InnoDB synchronize on to figure out which transactions to recover to bring both to a consistent state. Now atomic DDLs recovery also takes into account the contents of the binary log, so that metadata and the binary log is consistent.
  • On recovery, recover my atomic DDLs too… Oh, and my applier positions as well! If a crash happens while a replication applier is applying an atomic DDL, then recovery will make sure that the replication positions are consistent with the metadata changes.

Usability

With the monitoring and operations enhancements, usability comes naturally. But the following functionality deserves to be highlighted:

  • Roll back to a given savepoint in Group Replication. In MySQL 8, another Group Replication limitation that was lifted was the (lack of) support for transaction savepoints. This limitation was first lifted in MySQL 8 and then backported to MySQL 5.7 (already a long time ago).
  • Don't be afraid to ask if disk space ran out! A lock was held while a replication thread was waiting for the disk space to be freed. This prevented users from easily querying performance schema tables to figure out what was wrong. MySQL 8 improves the interaction between these threads and allows the user to quickly diagnose problematic scenarios like this one by not blocking the monitoring commands. Details are here.

Integration

The information available in the binary log was extended to better integrate MySQL replication with other bits and pieces of the infrastructure that MySQL users have deployed side-by-side with MySQL. A big part of these enhancements consist in extending the table metadata in row based replication, but also a bit of additional data was added. Here is what was added:

  • Extract more details about your table from the binary log. Several users, that for on reason or another mine the binary log, have requested that more information related to table metadata was added into the binary log. Then they can more easily mine the log and extract the information they need without having to resort to follow up queries to information_schema or other sources of data, on the server that the binary log was created, as this is rather brittle, cumbersome and does not provide any guarantee of consistency by itself. Thus, this feature adds to the binary log additional data, for instance, character sets information, which columns of the table are signed and unsigned, the column names, and more. Further details here.
  • Extract more details about your transaction from the binary log. The binary log was enriched with additional transaction metadata. This includes the transaction size as well as a few commit timestamps that help implement other features, such as better observability of replication lag, and improve others, such as delayed slave. The commit timestamps added represent the commit timestamp of the original execution and the commit timestamp of the immediate master on the replication chain. You can read more about this here.

Code Maintenance

The replication code base is quite extensive. In MySQL 8 we continued to improve the code base. Granted, there are areas that could still use a bit of work, but there are others that we had a chance to remove, refactor or restructure. This is a continuous work though and one that is never in the spotlight as other features that provide additional functionality and thus are more user visible. Regardless, but I would like to spend a few sentences on this:

  • Removed very old binary log format cross-version conversion code. This removes code that was only used for a brief period of (non-GA) time in MySQL 5.1. This relates to a family of row events that is not used anymore. If you are familiar with the code, this was what was mostly on log_event_old.cc.
  • Removed cross-version compatibility code that was obsolete. There was code that was obsolete and only used for allowing conversion between obsolete binary log versions. This code was removed.
  • Removed deprecated LOAD DATA events code. Old and unused LOAD DATA events were removed.
  • MySQL group communication infrastructure fully tracked in mysql-trunk. This was a big step that allowed moving a part of the Group Replication plugin from its own infrastructure into the mysql-trunk code base – i.e., these were remains of a point in time that Group Replication was released as a separate plugin.
  • Removed redundant information in the group communication infrastructure. This work removed duplicate code and information for each member in internal memory structures and also introduced a better and more reliable way to track a member rejoin.
  • Publishing membership events to other components in the server. Group Replication membership events are propagated, through the new service registry, to other components in the server. These components only have to implement the listener interface and then will start getting a notification of events such as: a view has changed, quorum was lost, and so forth. This is extremely powerful, if you want to extend MySQL with a new component that reacts on membership changes.

This post is published on GetMySQL just only for Update on MySQL Release 8.0  to our readers and Content  is Copyright to https://mysqlhighavailability.com

Thursday, 19 April 2018

Select Query to Print Rank on belalf of obtained Marks

​We have a test table as below..


Run below query to print rank...


SELECT id,  marks,
find_in_set(marks,(SELECT GROUP_CONCAT( distinct(marks) ORDER BY marks DESC ) FROM test )) AS rank
FROM test.test;

How to generate auto increment serial number column in MySQL Select Query / How to declear & pass variable in Select Query

In MySQL , We can generate auto increment serial number column during select query .

For e.g,

We have following table...


Using below queries , we can achieve the subjected discussion.


​SELECT @a:=@a+1 sr_num ,id,name,marks,insert_time from (select @a:=0) initvars, test.test;




Select all column :

SELECT @a:=@a+1 Sr_Num , A.* FROM (SELECT @A:= 0) initvars, test.test A ;

How to Select Nth highest marks/Value in Column ?

MySQL Select Query for Nth highest Value

​SELECT * from test.test where marks= (SELECT distinct (marks) FROM test.test order by marks desc limit (n-1),1);​

​We have following table..

Now we have to select 3rd highest Marks in table "test"

Query and it's result is showing below..

SELECT  * from test.test where marks= (SELECT distinct (marks) FROM test.test order by marks desc limit 2,1);


 
Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...