Mysql Database Size

Mysql Database Size

Unlike other enterprise database management systems, Mysql do not have a command to check database size. In this article i am going to show you how to learn mysql database size.

There are two sql statements to learn mysql database size. At that point i should give credits to who deserve it. Two years ago, i needed this information and i found scripts on MkYoung’s Blog. Since that day, they are in my toolkit. I use them whenever i need.

How to Learn Mysql Database Size

Even MkYoung provided us two scripts, second one requires a little customization to run.

Size of Mysql Databases

Let’s start with first script:

I save this scripts into a folder and i execute them like this whenever i need:

As you see, this script creates a report about mysql database size. Output shows size of every database in your mysql instance.

Size of Mysql Tables

Second script will show us sizes of mysql tables. Here is the second script:

You need to change <dbname> with name of your database to run this script.

This script will give you an output like this:

This script will show you size of each table. That is very useful when you need a deeper information about your tables.

Find Physical Size of Your Database

Databases contain more information then you store. It keeps some storage before you fill it. You can easily find Mysql database directory and measure size of it.

You can execute this statement to find data directory of your mysql instance:

Now, navigate to your data directory.

Here is our test folder, that contains test database inside. Now here is the last step. We are going to ask Linux for it’s size.

As you can see, our test database is 10 megabytes.

CONCLUSION

In this article we covered to measure size of mysql databases. Also we mentioned data directories a little bit.

If you have any questions, or any comment, don’t hesitate to write them below. I’ll be happy to help you. ­čÖé

How to Check Mysql Version

Check Mysql Version

Many times we get a mysql image or a mysql pre installed cloud vps server. In this cases, we want to check mysql version running our server. In this article i am going to show you, how to check mysql version.

If you are new to mysql, you may want to read our previous article How to Learn Mysql? You can learn more about Mysql using this article.

How to Check Mysql Version?

Like other things in mysql, checking mysql version is pretty easy.

There are two common ways to learn mysql version. First is, getting information via connection information. Second way is using system variables.

Learn Mysql Version Using Connection Information

Login mysql command line using mysql command, as we always mentioned in our classes.

Mysql will tell you about it’s version information and operating system information as welcome message.

Learn Mysql Version Using Mysql System Variables

The other way to check mysql version is using Mysql system variables. You can list variables using this simple command:

But that command will list every variable in system. We just need version information, you can use LIKE clause to filter:

As you can see, this command will list all version information you need.

Info About System Variables

I just want to tell you about system variables. Just a little bit.

Mysql stores system variables that indicate how your server is configured. System variables contain information about “how you server will serve?”, authentication information, performance settings, debug and trace information etc.

You can learn more about system variables from Mysql Reference Manual.

CONCLUSION

In this article we covered how to check mysql version.

If you have any comment, or any questions to ask, don’t hesitate to write below. I’ll be happy to help you.

Mysql Stored Procedure Parameters

Mysql Stored Procedure Parameters

There are three kind of parameters you can use to define a stored procedure. Each one of them designed for different purposes. In this article we are going to cover mysql stored procedure parameters.

In programming we use parameters to transfer data between functions and object. You can transfer and/or process that data using mysql stored procedure parameters.

If you are new to mysql, you can read our previous article How to Learn Mysql. In this article you can find more information about mysql.

Also you can read our tutorial about stored procedures.

Mysql Stored Procedure Parameters

As i mentioned above, there are three kinds of parameters you can use. They are:

  1. In
  2. Out
  3. Inout

These three kind of parameters have different purposes and usages.

Mysql Stored Procedure IN Parameter

This is default type of parameter. IN type parameters should be passed from caller programs. Also In parameters are protected.

What does being protected means?

Even you change value of an IN type parameter, mysql will restore it’s original value in the end of execution. In other words, you should copy value of in parameter to work on it.

Mysql Stored Procedure OUT Parameter

Out parameter passed back to caller program. Stored procedures can not access initial value of an OUT parameter when it starts.

Mysql Stored Procedure INOUT Parameter

INOUT type parameters are a combination of IN and OUT parameters. Caller program has to pass argument to stored procedure. Stored procedure passes back argument to caller program when it finished execution.

Mysql Stored Procedure Parameters Examples

We are going to make some examples together. Our examples will be short but effective to help you understand about mysql stores procedure parameters.

IN Example

This is a very basic example about in parameter. If you call getSales procedure you will get an output like this:

OUT Example

We are going to improve IN parameter example a little bit as our OUT parameter example.

As i mentioned above this procedure will pass back, total value to caller.

INOUT Example

After definin increase procedure, now we are going to define @number parameter as follows:

Now i am going to call increase procedure two times and select result:

CONCLUSION

In this article we covered Mysql Stored Procedure Parameters topic.

If you have something to add, or ask, please leave a comment below. I’ll be happy to answer ­čÖé

Which Database Should i Use for My Startup?

Which Database Should i Use for My Startup?

You have a business idea, you are gathering a great team for your next million dollar business. But you have a question. Which database you should use for your startup?

You should trust you technology. You strategy about technology may help you to create a better business, also may help you to fail better.

It is a hard question. I need to learn more about your project to answer properly. But in this article, i am going to try to help you to find a better direction.

Which Database Should i Use for My Startup?

First of all, i should tell you about relational and non relational databases.

Let’s start with relational databases. Relational databases store data in tables. We use keys to follow relation between these tables. Tables must be structured before inserting data in them.

Non Relational Databases are a little bit different than relational databases. Non relational databases store data in “documents”. They don’t have a structure, you can add one more attribute whenever you want.

In some cases, i suggest startups and businesses to use a non relational database like mongo db or couch db.

There is no need for racism. We use technologies to help our businesses. There are no reasons to fall in love with one of them.

Using a Non Relational Database

Using relational databases is better idea in most of the cases. But sometimes, we need something more flexible.

Non relational databases help us in this times.

Let me give an example.

Non relational databases can give you a strong advantage if you use them for a content management system. Most types of content is not structured and may contain different kinds of information. Non relational databases fits this situation.

Using a Relational Database

If you can model your data into parts, relational databases migh be good idea for you.

Think about your blog, there are posts and comments. In comments table, there is a key, that shows the comment belongs to which article. That is the relation we need to create in relational databases.

Unlike non relational databases, relational databases are flexible. You need to model your data before creating a database because you need to alter your tables when you need to add a column.

Relational Data vs Non Relational Database

Don’t be a fanatic.

We need both of them in our projects.

I don’t like this kind of comparisons because they are not fighting to show that they are better. They both have different purpose and they are here to do our job.

Here is  a simple trick for you.

If you can create a relation model of your business data, then use a relational database. If it is hard to model your business data, or if you think your data will change one than twice in a month, go with a non relational database.

Don’t Waste Your Capital

If you are starting a new business you should have limited capital.

DON’T WASTE your capital to buy expensive software.

There are free tools you can use to create your startup, even databases.

They will say you need to buy an expensive software license if you ask to an Oracle sales guy.

But that is a lie. You don’t need to.

You just need to focus producing best products, as best as you can.

Let me make a list of your requirements.

  1. Security
  2. Reliability
  3. Experts

You just need that three things above. You don’t need an expensive software license to build you business.

That is waste of money to spend your capital for imple database requiremens.

Free alternatives

As i mentioned above, there are free (and better) alternatives out there. You can save your money for your super good product. (or advertisement)

For non relational databases, i have two alternative suggestions:

  1. mongo db
  2. couch db

If you want to use a relational database, you might choose:

  1. MySql
  2. Postgresql

Both of these four database management systems will be completely enough for you.

CONCLUSION

I think i am going to continue this article as a serie. Because i want to help startups, to set up a robust architecture for their businesses.

Picking a database management system, picking a programming language, a framework is really hard for most of the business owners.

In this article we covered database management systems for startups in general. Wish to see you in next article.

If you have a question or something to add, please leave a comment below.

Mysql Alter Table Add Column Example

Mysql Alter Table Add Column Example

Sometimes we need to add a column to a mysql table. In this article we are going to make a mysql alter table add column example together.

If you are new to mysql, you want to take a look to our previous article: How to Learn Mysql. You can learn more about mysql via reading our article.

Mysql Alter Table Add Column Example

First of all we are going to create a mysql table.

We are going to use SALES_PEOPLE table for our mysql alter table add column example.

Now we need example data, one or two rows are completely enough for our mysql alter table add column example.

You can use these statement to insert example rows.

Now, we are starting to alter our table.

Alter Mysql Table with Nullable Columns

If you want to add a column, that’s nullable you can execute your alter statement like this:

If you make a select on this table you are going to see your new null column.

Now drop AGE column to move our next example:

Alter Mysql Table with Not Null Columns

We may need to create new column with some default values. Mysql can alter table using filled columns. We need to add NOT NULL flag to our statement, with default value:

You can control your altered table using a select statement:

CONCLUSION

Thank you for reading.

Today we made a mysql alter table add column example. If you want to read more about altering a table, you may read mysql manual from here.

If you have any questions or something to add, please leave a comment below. I will be happy to help you.

Change Mysql Database Collation

Change Mysql Database Collation [How to]

Do you wan to change Mysql database collation? In this article we are going to cover topic, how to change mysql database collation?

Character set, is set of available characters and symbols. A collation is set of rules to compare members of character set. There are both case sensitive collations and case insensitive collations. You can select the best fit for you.

Change Mysql Database Collation

You can change mysql database collation via altering it.

Before altering database, i want to give you a trick about collations.

Listing Available Collations:

You can list available collations using a simple query. You make a select using: SELECT * FROM INFORMATION_SCHEMA.COLLATIONS;

Best Collation for Mysql

Most popular Mysql Collations is utf8_unicode_ci. You can display all characters in your client if you use utf8_unicode_ci collation.

Also utf8 char set contains most of the characters. That makes utf8 is a good choice for projects. Even this blog’s database uses utf8_unicode_ci collation to store data for you.

How to Change Mysql Database Collation

You need to execute a simple alter query to change database collation in mysql.

It’s that easy. It only takes less than a second. Just execute that command and update your database structure.

CONCLUSION

In this article we covered topic how to change mysql database collation. Displaying wrong characters, or making a wrong comparison may ruin your project. Select your collation and char set carefully.

You can learn more about character set and collations from here.

If you have a question, or something to add, please leave a comment. I will be happy to help you ­čÖé

New Mysql User

Creating New Mysql User

Securing you server is not enough to secure whole mysql environment. You need to create users with limited permissions on you mysql servers. In this article we are going to cover to create a new mysql user.

If you are new to mysql, you might want to read our previous article: How to Learn Mysql. You can learn more about mysql with this tutorial.

How to Create New Mysql User

First of all i need to tell a few things about mysql users before diving into creating a new mysql user:

Mysql users are virtual. Unlike IBM DB2, there are no connection between operating system users and mysql users

Limit connection source: Mysql can limit source of the connection. You can create an user for an application, that user will only be able to create a connection from it’s application server. No one can connect from any where else.

Authorization: You may want to limit capabilities (permission of users) on a database. For example you can limit a user with select operation only.

Create New Mysql User Syntax

First of all, you need to create authentication for your new user, as i mentioned above. Authentication is user name and password rule.

Authentication

Let me explain what did i do above:

I called create user statement first, and given testuser as username. Parameter after @ character shows connection source limit. testuser will only be able to connect from localhost.

Now we need to set authorization for user. For example i am going to grant everything on TEST database to testuser.

Authorization

Mysql’s privileges are: Alter, create view, create, delete, drop, grant option, index, insert, references, select, show view, trigger and update. If you want to learn more about mysql’s privileges, you can visit this page.

The example above, shows to grant a user on one database only. testuser is now granted to do everything on the tables of TEST database.

Now i am going to grant testuser for everything:

After granting users, we need to reload privileges.

Now new users and their permissions are active.

Listing Users

You can list mysql users with a single query only:

Or you can select them more readable:

CONCLUSION

In this article, we covered creating new mysql user topic.

Creating a new user is very easy but it is vital part of database security.

If you have any question, or something to add, you can leave a comment below.

Mysql Stored Procedure Variables

Mysql Stored Procedure Variables

You can define, set and reuse variables in mysql stored procedures. Variables will help to use operations with query results. In this article i am going to cover mysql stored procedure variables.

Mysql Stored Procedure Variables

A variable in mysql stored procedures, is used to store immediate data and make some operations on them. Mysql stored procedure variables are local and only useful in their scope.

You can not assign value to variable while declaring it. You have to declare a variable to assign value to it.

Declaring Mysql Stored Procedure Variables

You have to use DECLARE statement to declare a variable as follow:

You can use that easy statement to declare a variable.

  • Variables have to be named
  • You should give a type to your variable
  • Value of your variable will be null when you declare it unless you set a default value using DEFAULT keyword.

For example, i am going to define a variable with default value 0 in this example:

Like most of programming languages, you can declare two or more variables in same data type in same line.

Assigning Mysql Stored Procedure Variables

You can assign mysql stored procedure variables after declaring them. Assignment is done using SET keyword.

We assigned 110 to products variable.

In most of the cases, we want to query results to variables. You can assign a query result into a variable like that:

To assign a query result to a variable:

  1. Declare a variable
  2. Make a query using select … into syntax.

Mysql Stored Procedure Variables & Scope

If you declare a variable in the body of stored procedure, between begin and end blocks, that will be destroyed when END; reached.

If you define a variable with @ sign, that is a session variable, your variable will be available until you close session.

Conclusion

Today we made an introduction to mysql stored procedure variables. We are going to continue to write about mysql stored procedures.

You can find detailed information about variables on mysql reference manual.

If you have any comments or anything to ask, please leave a comment. I will be happy to help you.

Mysql Stored Procedure Tutorial

Mysql Stored Procedure Tutorial

Stored Procedures are a way to encapsulate repeating jobs in mysql. This article is an introduction to Mysql stored procedure tutorial.

In some cases, we need to do repeating tasks on mysql, we use stored procedures as a shortcut to call our existing code on the server. The most important thing about stored procedures, they work and store on the server, they are not about client side.

If you are new to mysql, you may want to read our previous article How to Learn Mysql to learn further details.

I believe this article will be great mysql stored procedure tutorial for everyone.

Before starting to mysql stored procedure tutorial i want to make a definition and some explanations about stored procedures.

Stored procedure is a server-stored no value returning function.

Stored procedures are also known as stored routines.

Mysql Stored Procedure Tutorial

In the end of this tutorial, you will be able to do:

  • Create a stored procedure.
  • Call a stored procedure.

Stored procedure is not a really complex topic but it helps us to save time from repeating jobs.

Step 1. Define Your Delimiter

As you may know, standard Mysql delimiter is semicolon. But using semicolon in stored procedures may cause problems therefore you need to pick a delimiter for yourself. There might be multiple statements in a stored procedure, due to that, you should pick a custom delimiter. My choice is using $$ as delimiter.

Step 2. Create Stored Procedure

This is a mysql stored procedure tutorial, so let’s create a stored procedure together.

How to call a stored procedure?

It is very easy to call a stored procedure. call a2()$$ is the command to call our stored procedure.

If your stored procedure requires parameters, you should call it using parameters like, call name_of_procedure(param1, param2…)

Step 3. How to Modify a Stored Procedure

There is no way to modify the body of a stored procedure. You need to drop and recreate stored procedure:

Step 4. Understanding Parameters

In this mysql stored procedure tutorial, we are going to dive in parameters too. There are 3 different kind of parameters.

In:

That is an input parameter. In type parameters should be passed from caller program to stored procedure. Even you change value of an in parameter in program body, it’s initial value stored in the end of program. In other words, you should work with copy of IN parameters.

Out:

Out parameter is output parameter that can be changed by program and it’s new value is passed back to caller program.

Inout:

Inout is a combination of in and out parameters. That’s both read as input and passed back to caller program as output.

Parameter Examples

We are going to continue with parameter examples to our mysql stored procedure tutorial.

In Example

In this example we created a stored procedure named GetSalesBySalesPerson, takes one parameter IN. It makes a select * from sales table using giving input as salesman.

Out Example

You can call this procedure as:

Inout Example

Conclusion

In this article we made a great introduction to our mysql stored procedure tutorial series.

If you want to add anything, or ask a question, you can leave a comment.

Install Mysql on Windows

Do you want to install Mysql on Windows? In this article i am going to show you how to install mysql on Windows, step by step.

Mysql is a relational database management system. If you are new to Mysql, you may want to read my previous article How to Learn Mysql. You can find further information about mysql in this article.

How to Install Mysql on Windows

Installing Mysql on windows machine is pretty easy. Unlike Linux based operating system, Mysql provides a step by step installation wizard. But there are prerequisites to install mysql on Windows.

Step 1. Prerequisites to Install Mysql on Windows

Even Mysql installation wizard will install some of that dependencies for you, you still need to install some dependencies by yourself.

There are two dependencies you need to install by yourself before install mysql on Windows.

  1. Visual C++ Redistributable Packages for Visual Studio 2013
  2. .NET Framework 4.5.2

You can download Visual C++ Redistributable Packages for Visual Studio 2013 from here. Note that, Mysql Installation wizard will install x64 package for you. You need to download and install x86 package on your own.

You can download .NET Framework 4.5.2 from here. It’s an offline installation package.

Installation of these two package is fairly easy. Just run installation wizards and wait until they complete installation. You don’t need to make any further actions to complete installation.

Step 2. Downloading Mysql Installer

Mysql has a Windows installation wizard. In most cases i don’t prefer web installer. I download and use, offline installer. It contains almost everything about mysql.

Here is the link for Mysql installer for windows.

After downloading Mysql installation wizard, run it. First of all, it installs Mysql installer.

Step 3. Install MySql

I made a screen shot for every step of installer.

In first screen you need to make a product selection. If you are developing with mysql, development set will be enough for you:

Accept License Agreement

 

Choose a setup type

 

In next step, mysql installer will detect prerequisites and install them for you:

Mysql Installation - check requirements

 

Now you come to installation, click on execute and let installer to install Mysql packages for Windows.

List installation packages

Installing Mysql Programs on Windows

After installation completes installer launches configuration wizards. Standalone mysql server option is completely enough for development. InnoDb cluster is a Mysql cluster method for production servers that need high availability and performance.

Select Mysql Server Type

You can use default port if there are no errors:

Set up networking for Mysql

Set root password and create new users if you need some:

Pick a Root password

Now configure windows service. If you want to run multiple mysql instances on the same machine pick your service name carefully, that need to be unique.

Configure Windows Service Name

Configuration wizard completes configuration and starts mysql server for you:

Mysql Windows Installation Wizard Completes Configuration

CONCLUSION

Today, we covered how to install mysql on windows topic. As you can see, mysql on Windows has a very straightforward installation. You can easily install by yourself easily, even you are just starting to learn mysql.

Do you have a question about mysql installation or anything else? Leave a comment below. I will be happy to help you ­čÖé