MySQL Architecture -2(Disk space & Error logs – Article -05)

MySQL Architecture -2(Disk space & Error logs).

How MySQL will use disk space

Disk space used primarily for files in the server’s data directory

–> A sub directory  for each database in the data directory.

  • Data directory depends on distribution platform ,operating system.
  • For Windows , for example

<mysql-home\data or the AppData directory ,for  example  :

  • Linux, for example :

  • Current setting in @@datadir server variable
  • Table and view format files(.frm)
  • Server log files and status files.
  • Trigger storage
  • System database (mysql)

 

At the time of installation if you are not specifying any path MySQL will be installed in /var/lib/mysql location.

ibdata1 file is a table space file in MySQL, this file is specific to InnoDB tables this doesn’t contain any information about InnoDB.

ib_logfile0 and ib_logfile1 are transaction redo log files.

Whenever we create a database, the related folder same as database name which we crated will be created in your /var/lib/mysql location and if you go in that database directory you see db.opt (option file) file and if you open that file you have default character set and latin collation as shown  below :

Read the content in db.opt file.

If we create a table in a database 2 types of files will be created for each table 1) <table_name>.frm 2) <table_name>.ibd as shown below.

Let’s create table with specified engine called MyISM.

check how many files are created for testdiskspace2 object.

There is three files has created for MyISAM engine objects with the extension called “.MYD” , “MYI”  ,””.FRM”.

There is a file called ibdata1 which is called tablespace in mysql databases.

For InnoDB databases the default tablespace is ibdata1

 

The default location of MySQL is  /var/lib/mysql

To check in MySQL prompt

To change the location of MySQL databases  we need to change configuration file.

First need to stop MySQL server then copy the content into new directory location then only need to change datadir variable.

Types of log files

 

Error log file:

It contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running.

 

Slow log file:

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. It can be used to find queries that take a long time to execute and can be used for optimization. We can process a slow query log file using the mysqldumpslow command to summarize it.
mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order. Also, the slow query log should be protected because logged statements might contain passwords. We need to enable to use this option.

To set time for long running quires.

 

Binary log file:

contains events that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done.

 

General log file:

This log is a general record of what mysqld  is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. It can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.


Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

Add Comment