Mysql 5.6 on cloud

Mysql 5.6 on cloud

April 24, 2017

1-click AWS Deployment 1-click Azure Deployment

1-click Google Deployment

Overview

MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various web-based software applications. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. This tutorial will give you a quick start to MySQL and make you comfortable with MySQL programming.

  Audience

  • This tutorial is prepared for the beginners to help them understand the basics-to-advanced concepts related to MySQL languages.
  • Prerequisites
  • Before you start doing practice with various types of examples given in this tutorial, it is being assumed that you are already aware about what a database is, especially an RDBMS and what is a computer programming language.

Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database.

  • Database− A database is a collection of tables, with related data.
  • Table− A table is a matrix with data. A table in a database looks like a simple spreadsheet.
  • Column− One column (data element) contains data of one and the same kind, for example the column postcode.
  • Row− A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.
  • Redundancy− Storing data twice, redundantly to make the system faster.
  • Primary Key− A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row.
  • Foreign Key− A foreign key is the linking pin between two tables.
  • Compound Key− A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.
  • Index− An index in a database resembles an index at the back of a book.
  • Referential Integrity− Referential Integrity makes sure that a foreign key value always points to an existing row.

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −

  • MySQL is released under an open-source license. So you have nothing to pay to use it.
  • MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
  • MySQL uses a standard form of the well-known SQL data language.
  • MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
  • MySQL works very quickly and works well even with large data sets.
  • MySQL is very friendly to PHP, the most appreciated language for web development.
  • MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
  • MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

Administrative MySQL Command

Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database −

  • USE Databasename− This will be used to select a database in the MySQL workarea.
  • SHOW DATABASES− Lists out the databases that are accessible by the MySQL DBMS.
  • SHOW TABLES− Shows the tables in the database once a database has been selected with the use command.
  • SHOW COLUMNS FROM tablename:Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.
  • SHOW INDEX FROM tablename− Presents the details of all indexes on the table, including the PRIMARY KEY.
  • SHOW TABLE STATUS LIKE tablename\G− Reports details of the MySQL DBMS performance and statistics.
  • MySQL works very well in combination of various programming languages like PERL, C, C++, JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities.
  • PHP provides various functions to access the MySQL database and to manipulate the data records inside the MySQL database. You would require to call the PHP functions in the same way you call any other PHP function.

Structured Query Language (SQL)

SQL is the core of a relational database which is used for accessing and managing the database. By using SQL, you can add, update or delete rows of data, retrieve subsets of information, modify databases and perform many actions. The different subsets of SQL are as follows:

  • DDL (Data Definition Language) – It allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects.
  • DML (Data Manipulation Language) – It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
  • DCL (Data Control Language) – It allows you to control access to the database. Example – Grant or Revoke access permissions.
  • TCL (Transaction Control Language) – It allows you to deal with the transaction of the database. Example – Commit, Rollback, Savepoint, Set Transaction.

MySQL is the most prevalent Open Source SQL database management system, it is developed, circulated, and braced by Oracle Corporation.

  • MySQL is a database management system.

A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications.

  • MySQL databases are relational.

A relational database stores data in separate tables rather than putting all the data in one big storeroom. The database structures are organized into physical files optimized for speed. The logical model, with objects such as databases, tables, views, rows, and columns, offers a flexible programming environment. You set up rules governing the relationships between different data fields, such as one-to-one, one-to-many, unique, required or optional, and “pointers” between different tables. The database enforces these rules, so that with a well-designed database, your application never sees inconsistent, duplicate, orphan, out-of-date, or missing data.

The SQL part of “MySQL” stands for “Structured Query Language”. SQL is the most common standardized language used to access databases.

MySQL software is Open Source.

Open Source means that it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything. If you wish, you may study the source code and change it to suit your needs. The MySQL software uses the to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL code into a commercial application, you can buy a commercially licensed version from us.

  • The MySQL Database Server is very fast, reliable, scalable, and easy to use.

If that is what you are looking for, you should give it a try. MySQL Server can run comfortably on a desktop or laptop, alongside your other applications, web servers, and so on, requiring little or no attention. If you dedicate an entire machine to MySQL, you can adjust the settings to take advantage of all the memory, CPU power, and I/O capacity available. MySQL can also scale up to clusters of machines, networked together.

  • MySQL Server works in client/server or embedded systems.

The MySQL Database Software is a client/server system that consists of a multithreaded SQL server that supports different back ends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs).

We also provide MySQL Server as an embedded multithreaded library that you can link into your application to get a smaller, faster, easier-to-manage standalone product.

  • A large amount of contributed MySQL software is available.

MySQL Server has a practical set of features developed in close cooperation with our users.

 Install mysql server 5.6 on Ubuntu 14.04 LTS

Installing mysql-server 5.6 is very easy. Follow the given below steps

Step 1: Install MySQL Server 5.6

Install mysql-server 5.6 with apt-get command.

sudo apt-get update
sudo apt-get install mysql-server-5.6

During installation of MySQL Server packages,on your terminal you will screen of setting mysql root password. Hence, give the mysql user root password.

Given below is screenshot of screen.

mysql Ubuntu 14.04

Now it will again ask mysql root password for confirmation. Rewrite the same mysql root password which you have given on first screen.

MySQL Server 5.6 Ubuntu 14.04

Step 2 : Login into MySQL Server

After mysql server installation get finishes , it start the mysql service automatically. Hence, you can login now in MySQL Server with user root.

To login into MySQL Server, use the below given command.It will ask the mysql root password which you have set during mysql server installation.

mysql -u root -p

Given below is the reference from my server

sharad@ubuntu:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 36
Server version: 5.6.17-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.04 sec)

mysql> exit
Bye

Step 3 :MySQL Service to Start/Stop/Restart/Status

(a) To restart the mysql service

sudo service mysql restart

(b) To start mysql Service

sudo service mysql start

(c) To stop mysql service

sudo service mysql stop

(c) To get status of mysql service

sudo service mysql status

NOTE: For start/stop/restart/status of mysql , use service command.

In case, if you are using init.d script (/etc/init.d/mysql), it will show some error as given below.

sharad@ubuntu:~$ 
sharad@ubuntu:~$ sudo /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld                                                                                                  [ OK ] 
 * Starting MySQL database server mysqld                                                                                                         No directory, logging in with HOME=/
                                                                                                                                          [ OK ]
 * Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly.
sharad@ubuntu:~$

Hence, it is good to use service command for start/stop of mysql service

Replication in MySQL 5.6: GTIDs benefits and limitations:

Global Transactions Identifiers are one of the new features regarding replication in MySQL 5.6. They open up a lot of opportunities to make the life of DBAs much easier when having to maintain servers under a specific replication topology. However you should keep in mind some limitations of the current implementation. This post is the first one of a series of articles focused on the implications of enabling GTIDs on a production setup.

Basically the steps are:

  • Make the master read-only so that the slaves can execute all events and be in sync with the master
  • Change configuration for all servers and restart them
  • Use CHANGE MASTER TO to instruct all servers to use GTIDs
  • Disable read-only mode

This procedure will switch all your servers from regular replication to GTID replication. But if you are running a production system, you will probably want to gradually enable GTID replication for an easier rollback in the event of a problem. And some items in the documentation are not so clear.

For instance:

  • Do we really need to restart all the servers at the same time? Downtime is something we like to avoid!
  • Is it necessary to make the master read-only?
  • Can we use regular replication for some slaves and GTID replication for other slaves at the same time?

To find an answer to these questions, let’s create a simple replication configuration with one master and two slaves, all running MySQL 5.6 with GTIDs disabled.

First try: configure only one of the servers with GTIDs

Let’s stop slave #2, change configuration and restart it:

The error log tells us why the IO thread has not started:

So unfortunately if you want replication to work correctly, gtid_mode must be ON on all servers or OFF on all servers, but not something in the middle.

This time, replication on slave #1 will stop:

These simple tests answer the first two questions: replication works only if all servers have the same value for gtid_mode, so you should restart them at the same time, which is best done by making the master read-only. However, “at the same time” means “at the same binlog position”, so you can perfectly restart the servers one by one.

Second try: GTIDs enabled, mixing regular replication and GTID replication

This time, we will enable GTID replication on slave #1, but not on slave #2

and let’s create a new table on the master table

Executing SHOW TABLES FROM test on both slaves shows that the table has been created everywhere. So once GTIDs are enabled on all servers, you can have some slaves using file-based positioning and some other slaves using GTID-based positioning.

This answers the second question: we can have different replication modes on different servers, but only if all servers have gtid_mode set to ON. Could it be interesting to run file-based replication when gtid_mode is ON? I can’t think of any use case, so in practice, you’ll probably use either file-based replication only (gtid_mode=off for all servers) or GTID-based replication only (gtid_mode=on for all servers).

Additional question: how can you know if a slave is using GTID-based replication by inspecting the output of SHOW SLAVE STATUS? Look at the last field, Auto_Position:

 

Enabling GTID-based replication can be tricky if your application does not easily tolerate downtime or read-only mode, especially if you have a lot of servers to reconfigure. It would be really nice to be able to mix servers where gtid_mode is ON with servers where gtid_mode is OFF. This would greatly simplify the transition to GTID-based replication and allow easier rollbacks if something goes wrong.

The main benefit of using GTIDs is to have much easier failover than with file-based replication. We will see how to change the replication topology when using GTID-based replication. That will show where GTIDs shine and where improvements are expected.

This is the second post of a series of articles focused on MySQL 5.6 GTIDs. You can find part one here.

Our goal will be to go from setup #1 to setup #2 on the picture below, following various scenarios:

repli_setup

For these tests, all servers are running on 127.0.0.1 with ports ranging from 10000 for s0 to 10004 for s4.

Scenario #1: All slaves have processed all the writes

This is the easiest case, we will make s2 a master and redirect replication on the other servers to s2. This scenario can happen when you want to perform a planned failover.

With GTIDs, all the operations are straightforward:

Those of you who have already done these operations with file-based replication know that it is usually very tedious and that proper recording of binlog file/binlog position needs to be done with care if you don’t want to break replication or corrupt your data.

Scenario #2: One of the slaves is behind

Now let’s imagine that s0 has crashed, and that s1 has not received all writes (and therefore s3 and s4 are also lagging behind).

Can we still use master_auto_position = 1? Let’s hope so, as it is one of the ideas of GTIDs: having for each event across the cluster a monotonically incremental identifier for each event.

Notice that this is the same problem for s0 (which will be late when it comes back) and s1, s3 and s4.

Let’s give it a try!

Great! So again, using GTIDs avoids the tedious work of looking for the binlog position of a specific event. The only part were we should pay attention is the server we choose for promotion: if it is not up-to-date, data may be lost or replication may be broken.

Scenario #3: The master has crashed before sending all writes

If the binary logs of the master are no longer readable, you will probably lose the events that have not been sent to the slaves (your last chance is to be able to recover data from the crashed master, but that’s another story). In this case, you will have to promote the most up-to-date slave and reconfigure the other slaves as we did above.

So we will suppose that we can read the binary logs of the crashed master. The first thing to do after choosing which slave will be the new master is to recover the missing events with mysqlbinlog.

Let’s say that we want to promote s1 as the new master. We need to know the coordinates of the last event executed:

We can see that it’s not obvious to know which was the last executed event: is it 219be3a9-c3ae-11e2-b985-0800272864ba:1 or 3d3871d1-c3ae-11e2-b986-0800272864ba:4 ? A ‘Last_Executed_GTID’ column would have been useful.

In our case we can check that 3ec18c45-c3ae-11e2-b986-0800272864ba is the server UUID of s2, and that the other one is from s0 (for s0 which is crashed, the server UUID can be read in the auto.cnf file in the datadir).

So the last executed event is 219be3a9-c3ae-11e2-b985-0800272864ba:1. How can I instruct mysqlbinlog to start reading from there? Unfortunately, there is no --start-gtid-position option or equivalent. See bug #68566.

Does it mean that we cannot easily recover the data with mysqlbinlog? There is a solution of course, but very poor in my opinion: look for the binlog file/position of the last executed event and use mysqlbinlog with the good old --start-position option! Even with GTIDs, you cannot totally forget old-style replication positioning.

Reconfiguring replication when using GTIDs is usually straightforward: just connect the slave to the correct master with master_auto_position = 1. This can even be made easier with mysqlfailover from the MySQL Utilities (this will be the topic of a future post).

Unfortunately, this will not work for every use case, and until this is fixed, it is good to be aware of the current limitations.

–MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications.

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons:

  • MySQL is released under an open-source license. So you have nothing to pay to use it.
  • MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
  • MySQL uses a standard form of the well-known SQL data language.
  • MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
  • MySQL works very quickly and works well even with large data sets.
  • MySQL is very friendly to PHP, the most appreciated language for web development.
  • MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
  • MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

Cognosys provides hardened and ready to run images of MYSQL 5.6 on all public cloud (MYSQL 5.6 on AWS marketplace and MYSQL 5.6 on Azure).
Deploy your MYSQL 5.6 securely on cloud i.e. AWS marketplace and Azure and Google Cloud Platform (GCP)

Click on the respective cloud provider tab for technical information.

Secured MySQL 5.6 on Centos 7.3

Secured MySQL 5.6 on Ubuntu 14.04 LTS

Features

AWS

Azure

Google

Videos

Mysql 5.6 on cloud

Related Posts