How To Install MySQL5.7 on ubuntu 14.04 Linux Using APT

Before installing new packages always take the backup of current datadir and remove the old packages using below commands if present:

To remove mysql completely from your system Just type in terminal

shell> sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
OR
shell> sudo apt-get remove --purge mysql*

shell> sudo rm -rf /etc/mysql /var/lib/mysql
shell> sudo apt-get autoremove
shell> sudo apt-get autoclean

Steps for a Fresh Installation of MySQL:

Adding the MySQL APT Repository

First, add the MySQL APT repository to your system's software repository list. Follow below steps:

1. Go to the download page for the MySQL APT repository at http://dev.mysql.com/downloads/repo/apt/.
2. Install the downloaded release package with the following command, replacing version-specific-package-name with the name of the downloaded package (preceded by its path, if you are not running the command inside the folder where the package is):

   shell> sudo dpkg -i /PATH/version-specific-package-name.deb
   

   For example, we are using mysql-apt-config_0.8.1-1_all.deb package, the command is:

   shell> sudo dpkg -i mysql-apt-config_0.8.1-1_all.deb
   

   Note that the same package works on all supported Debian and Ubuntu platforms.
3. During the installation of the package, you will be asked to choose the versions of the MySQL server and other components that you want to install. If you are not sure which version to choose, do not change the default options selected for you. You can also choose none if you do not want a particular component to be installed. After making the choices for all components, choose OK to finish the configuration and installation of the release package.

• Choose MySQL Server 5.7 option and press enter:

• Choose mysql-5.7 release and press enter:
• Choose OK and press enter: 
• You will again come on command prompt.

d. Update package information from the MySQL APT repository with the below command:

1. shell> sudo apt-get update

Installing MySQL with APT

Install MySQL by the using below command:

shell> sudo apt-get install mysql-server

:::::::::::

:::::::::::

Do you want to continue? [Y/n] y

This installs the package for the MySQL server, as well as the packages for the client and for the database common files.

During the installation, there are two requests by the dialogue boxes:

• Supply a password for the root user for your MySQL installation.

• Press OK after taking DataDir backup or if it is a fresh installation:

• Provide root password:

• Provide root password again:

Starting and Stopping the MySQL Server

The MySQL server is started automatically after installation. You can check the status of the MySQL server with the following command:

1. shell> sudo service mysql status

   Stop the MySQL server with the following command:

   shell> sudo service mysql stop

   To restart the MySQL server, use the following command:

   shell> sudo service mysql start

MySQL Secure installation for initial security:

• mysql_secure_installation — Improve MySQL Installation Security

This program enables you to improve the security of your MySQL installation in the following ways:

• You can set a password for root accounts.
• You can remove root accounts that are accessible from outside the local host.
• You can remove anonymous-user accounts.
• You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

As of MySQL 5.7.2, mysql_secure_installation is an executable binary available on all platforms. Before 5.7.2, it was a script available for Unix and Unix-like systems.

Normal usage is to connect to the local MySQL server; invoke mysql_secure_installation without arguments:

shell> mysql_secure_installation

When executed, mysql_secure_installation prompts you to determine which actions to perform.

As of MySQL 5.7.2, mysql_secure_installation supports these additional features:

• The validate_password plugin can be used for password strength checking. If the plugin is not installed, mysql_secure_installation prompts the user whether to install it. Any passwords entered later are checked using the plugin if it is enabled.
• Most of the usual MySQL client options such as --host and --port can be used on the command line and in option files. For example, to connect to the local server over IPv6 using port 3307, use this command:

  shell> mysql_secure_installation --host=::1 --port=3307

For example below we are using 3310 port for mysql:

• root@root@LSG-MySQLCITY:~# mysql_secure_installation --port=3310
  
  Securing the MySQL server deployment.
  
  Connecting to MySQL using a blank password.
  The 'validate_password' plugin is installed on the server.
  The subsequent steps will run with the existing configuration
  of the plugin.
  Please set the password for root here.
  
  New password:
  
  Re-enter new password:
  
  Estimated strength of the password: 100
  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) : n
  
   ... skipping.
  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!
  root@LSG-MySQLCITY:~#

How to install MemSQL Manually on ubuntu 14.04 using

Steps for a Fresh Installation of MEMSQL

• Run below command to check current running MEMSQL processes. The following command should now show nothing:

ps aux | grep memsql

a = show processes for all users | u = display the process's user/owner | x = also show processes not attached to a terminal

• Configure the Linux virtual machine settings.

sysctl -w vm.max_map_count=1000000000

sysctl -w vm.min_free_kbytes=164330

Or, optionally, at root, edit the /etc/sysctl.conf file at root as the superuser. Add or edit the virtual machine settings.

sudo su

sudo vi /etc/sysctl.conf

vm.max_map_count=1000000000

vm.min_free_kbytes=500000

i = Insert Mode | Esc = Normal Mode | :w! = force save changes | :q = quits and closes | :wq! = force save and quits

• Configure the Linux ulimit settings.

sudo su

ulimit -n 1000000

ulimit -u 128000

Or, optionally, at root, edit the /etc/security/limits.conf file.

sudo su

sudo vi /etc/security/limits.conf

ulimit -n 1000000

ulimit -u 128000

i = Insert Mode | Esc = Normal Mode | :w! = force saves changes | :q = quits and closes | :wq! = force saves and closes

• Install the MySQL client

You will need a client to connect to our MemSQL database. You will install the MySQL client.

From root, get the MySQL client.

sudo su

whoami

sudo apt-get update --fix-missing

sudo apt-get install mysql-client

At the prompt, type Y to install the client.

Y

• Add the memsql user

Add a memsql user to the machine.

If there is no memsql user, let’s create one.

sudo useradd memsql

Create a password for the memsql user. For the password, enter memsql.

sudo passwd memsql

memsql

memsql

Add the memsql user to the admin group.

sudo usermod -a -G admin memsql

Switch to the memsql user in a new shell.

sudo -u memsql -s /bin/bash

Verify that you are memsql.

whoami

• Download and untar MemSQL

Download to the tmp directory.

cd /tmp

wget http://download.memsql.com/734707c866e44856989fef2942e9141b/memsqlbin_amd64.tar.gz

Create a local directory and change the ownership.

cd ..

sudo mkdir /var/lib/memsql/

cd /var/lib/

sudo chown -R memsql:memsql memsql

Verify the directory ownership.

ls -l

The memsql user should own the memsql directory.

drwxr-xr-x 2 memsql memsql 4096 Sep 20 22:08 memsql

Untar MemSQL as the memsql user and copy to /var/lib/memsql/.

whoami

cd /tmp

tar -xvzf memsqlbin_amd64.tar.gz -C /var/lib/memsql/

Confirm the successful installation.

cd /var/lib/memsql/

ls

You should see the memsqlbin folder.

• Create leaf-3307, leaf-3308, and master-3306

Next, you will copy and rename the memsqlbin directory.

Copy the memsqlbin directory as leaf-3307

cp -r memsqlbin leaf-3307

ls

Copy the memsqlbin directory as leaf-3308

cp -r memsqlbin leaf-3308

ls

Rename the memsqlbin directory to master-3306

mv memsqlbin/ master-3306

ls

You should now see three directories in /var/lib/memsql:

leaf-3307 leaf-3308 master-3306

• On port 3307, start leaf-3307

You will start the leaf after making some configuration file changes.

For leaf-3307, edit the memsql.cnf file.

sudo -u memsql -s /bin/bash

cd /var/lib/memsql/leaf-3307/

sudo vi memsql.cnf

Bind the leaf to port 3307.

; ---------------------------------

; MySQL-style configuration options

; ---------------------------------

basedir = .

tmpdir = .

lc-messages-dir = ./share

socket = memsql.sock

port = 3307

lock_wait_timeout = 60

bind-address = 0.0.0.0

max-connections = 100000

core-file

i = Insert Mode | Esc = Normal Mode | :w! = force save changes | :q = quits and closes | :wq! = force save and quits

Start MemSQL with the ./service scripts (not generic service).

./service start

In the console, you should now see:

* Starting memsqld [ OK ]

* Waiting up to 60 seconds for a clean start [ OK ]

The following command should now show various user processes:

ps aux | grep memsql

Connect to MemSQL.

Connect to the leaf on port 3307.

mysql -h <your-host-ip> –u root –P 3307

At the mysql prompt, enter:

show databases;

Verify the two databases, information_schema and memsql, and exit.

exit;

• On port 3308, start leaf-3308

You will start the leaf after making some configuration file changes. These are the same steps for leaf-3307 except that you are binding the leaf to port 3308.

For leaf-3308, edit the memsql.cnf file.

sudo -u memsql -s /bin/bash

cd /var/lib/memsql/leaf-3308/

sudo vi memsql.cnf

Bind the leaf to port 3308.

; ---------------------------------

; MySQL-style configuration options

; ---------------------------------

basedir = .

tmpdir = .

lc-messages-dir = ./share

socket = memsql.sock

port = 3308

lock_wait_timeout = 60

bind-address = 0.0.0.0

max-connections = 100000

core-file

i = Insert Mode | Esc = Normal Mode | :w! = force save changes | :q = quits and closes | :wq! = force save and quits

Start MemSQL with the ./service scripts (not generic service).

./service start

In the console, you should now see:

* Starting memsqld [ OK ]

* Waiting up to 60 seconds for a clean start [ OK ]

The following command should now show various user processes:

ps aux | grep memsql

Connect to MemSQL.

Connect to the leaf on port 3308.

mysql -h 127.0.0.1 –u root –P 3308

At the mysql prompt, enter:

show databases;

Verify the two databases and exit.

exit;

• On port 3306, start master-3306

You will start the master aggregator after making some configuration file changes. The steps are similar to configuring and starting a leaf.

For master-3306, edit the memsql.cnf file to declare the master aggregator.

sudo -u memsql -s /bin/bash

cd /var/lib/memsql/master-3306/

sudo vi memsql.cnf

Declare the master-aggregator.

; ---------------------------------

; MySQL-style configuration options

; ---------------------------------

basedir = .

tmpdir = .

lc-messages-dir = ./share

socket = memsql.sock

port = 3306

lock_wait_timeout = 60

bind-address = 0.0.0.0

max-connections = 100000

core-file

master-aggregator

i = Insert Mode | Esc = Normal Mode | :w! = force save changes | :q = quits and closes | :wq! = force save and quits

Start MemSQL with the ./service scripts (not generic service).

./service start

In the console, you should now see:

* Starting memsqld [ OK ]

* Waiting up to 60 seconds for a clean start [ OK ]

View the tracelogs.

cd tracelogs

ls

Verify the command.log, memsql.log, and query.log files.

The following command should now show various user processes:

ps aux | grep memsql

Verify that you see processes for leaf-3307, leaf-3308, and master-3306.

• Add a leaf for 3307

Connect to the master aggregator with the MySQL client.

mysql -h <your-host-ip> –u root -P 3306

The host IP can be a public IP or public host name. In most cases, it is the internal IP or host name. It is also possible to use the machine IP or host name.

At the mysql prompt, enter:

show databases;

Verify the three database:

information_schema

memsql

sharding

Set Redundancy Level to 2.

set global redundancy_level=2;

show variables like '%redun%';

At the mysql prompt, enter:

show leaves;

Verify that the command returns an empty set.

Add a Leaf for port 3307 into Group 1.

ADD LEAF root@'<your-host-ip>':3307 into group 1;

The host IP can be a public IP or public host name. In most cases, it is the internal IP or host name. It is also possible to use the machine IP or host name.

Verify the Leaf.

show leaves;

The console output should look like:

+-----------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+

| Host | Port | Availability_Group | Pair_Host | Pair_Port | State | Opened_Connections | Average_Roundtrip_Latency_ms |

+-----------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+

| 127.0.0.1 | 3307 | 1 | NULL | NULL | online | 1 | 0.487 |

+-----------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+

• Add a leaf for 3308

Add a Leaf for port 3308 into Group 2.

ADD LEAF root@'<your-host-name':3308 INTO group 2;

Verify the leaves.

show leaves;

The console output should look like:

+-----------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+

| Host | Port | Availability_Group | Pair_Host | Pair_Port | State | Opened_Connections | Average_Roundtrip_Latency_ms |

+-----------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+

| 127.0.0.1 | 3307 | 1 | 127.0.0.1 | 3308 | online | 1 | 0.345 |

| 127.0.0.1 | 3308 | 2 | 127.0.0.1 | 3307 | online | 1 | 0.281 |

+-----------+------+--------------------+-----------+-----------+--------+--------------------+------------------------------+

FINAL: Create a database and table on the master aggregator and insert some data. 

             Verify the databases. using show databases extended ; on leaf nodes.

Understanding MemSQL in 5 Points

MemSQL is a relational database management system with a SQL interface and, it stores data in memory and runs in a cluster.

1. What is MemSQL?

MemSQL is a relational database management system with a SQL interface. It has the following additional properties:

·         Distributed architecture: MemSQL typically runs on a “cluster” of servers. However, users address a single interface to get data in or out of MemSQL. MemSQL handles details like which servers store certain volumes of data, and distributed query execution logic. Users benefit from the resources of many servers without needing distributed systems expertise.

·         Memory-optimized: MemSQL gives users the option to read and write data directly to and from main memory. Accessing data in DRAM is orders of magnitude faster than disk or even flash/SSD, and this provides extreme performance benefits for high-throughput transaction processing and real-time analytics. However, MemSQL does not require that all data fit in memory, and includes the option to store data, in a compressed format, on flash/SSD/disk.

·         Software only: MemSQL is distributed as software, and can run in variety of Linux environments including on physical hardware (“bare metal”), in virtual machines, or in software containers. It can be run on premises, in your data center, or in a public cloud. Unlike legacy database vendors that require the use of proprietary hardware, MemSQL can run on commodity servers.

2. Where does MemSQL store its data?

When creating a table in MemSQL, you specify whether the data for that table will reside in memory or on disk.

MemSQL has two table types: rowstore tables and columnstore tables.

Rowstore tables are primarily used for operational or transactional workloads, particularly workloads that require rapid updates. MemSQL rowstore tables store data entirely in memory, with logs and full database snapshots written to disk for durability.

Columnstore tables, on the other hand, are typically used for analytical workloads and data warehousing, as the format naturally lends itself to compression, efficient scanning, and rapidly appending data. MemSQL columnstore tables store data primarily on disk, but cache data in memory when possible.

3. How does MemSQL ensure durability for data stored in memory?

MemSQL writes logs and full database snapshots to disk, which can be used to recover state in the event that a machine turns off. MemSQL writes logs as data changes, and periodically triggers a full backup of the data in memory (snapshot). Users can configure the frequency of full database snapshots.

MemSQL supports transaction processing, and exposes parameters for the user to tune performance. The most common question we get is how do we ensure durability when processing transactions in memory. In MemSQL, a transaction is “committed” when it has been written to the in-memory transaction buffer. MemSQL keeps a thread running in the background that is constantly writing blocks of data from the in-memory transaction buffer to disk as logs. The size of the transaction buffer is configurable. For instance, setting the size of the transaction buffer to 0 MB means that a transaction will not be committed until it has been logged to disk. In practice, it is not necessary to set the transaction buffer to 0 MB since using a buffer allows MemSQL to write large chunks of data to disk all at once, and because doing so requires only sequential I/O.

4. How is MemSQL architected?

A MemSQL cluster consists of two types of nodes: aggregator and leaf nodes. Client applications connect to an aggregator, which serves as the query router. Aggregators are aware of the entire cluster and know where specific data reside. Leaf nodes handle data storage and most of the computation during query execution. When the client sends a query, the aggregator splits it into several queries which are sent to each leaf node. The leaf computes its query and sends the result back to the aggregator. The aggregator consolidates the results from each leaf and sends the final result back to the client.

Every cluster has a special aggregator called the Master Aggregator (non-Master aggregators are called child aggregators). Any aggregator, master or child, can process data manipulation language (DML) queries including SELECT, INSERT, UPDATE, and DELETE. Data definition language (DDL) commands, such as CREATE, DROP, or ALTER TABLE, must run on the Master Aggregator. In the event that the Master Aggregator machine fails, an administrator can “promote” a child aggregator to Master.

Database administrators can add (and remove) nodes to the cluster at any time while keeping the cluster online, even while running a workload. Simply provision additional nodes, then add them to the MemSQL cluster. The easiest way to do this is through MemSQL Ops, but it can also be accomplished by sending commands to the Master Aggregator.

5. How does MemSQL licensing work? How do I know how many machines I will need in my cluster?

MemSQL is licensed based on the total RAM capacity of a cluster (the sum of the RAM in each server/VM/container in the cluster). Unlike some vendors in the database space, we don’t license based on number of CPU cores. This allows our customers to maximize CPU resources without licensing overhead.

When planning, note that the cluster must have enough RAM for the following:

·         Rowstore data storage in memory

·         Rowstore and columnstore query execution

·         The operating system (kernel)

MemSQL enables several types of workloads ranging from stream processing and real-time analytics, to transaction processing, to data warehousing. Each of these workloads has different performance characteristics and latency requirements.