Day 15 Server Logs

There are 6 types of logs available for MySQL environment, Logs are important information for DBA to troubleshoot or understand the system. MySQL track the information in those logs.

Following are the list of logs:

Error Log (– start/stop/errors)

Query Log (trace)

Bin Log ( Transaction/Replication)

Slow Log (Slow queries)

Relay log (Replication)

DDL log (metadata log)

 

By default when MySQL Starts except Error Log all other logs are disabled, we have to enable those.

Error Log:

Error log are Basic log which provides information about Mysql Activity during start and during stop of MySQL Processes. It also capture errors while MySQL is running.

 

Query Log:

Query log provides detail statement level information about all the SQL Queries runs on the MySQL server, it will be as is information about queries which is executed on Mysql. As it tracks all the activity and captured the size of the file grows heavily and if this log enabled for long and heavily active system, it will occupy all your system space quickly. So be aware before enabling this log.

This log can be enabled in my.cnf file on [mysqld] group

[mysqld]

Log=1

 

Bin Log:

This log is very important and captures transaction activity on the system. Any DML operations performed on this system is stored in this log in Binary format. Binary format is safe and consumes less space in compare to query log also as it tracks only transactions.

This log is also mandatory to enable replication on the system, Replication works on Bin log transactions, Binlog transferred the transactions from master to slave and those transactions is tracked in Relay log.

This log is also enabled in my.cnf file on [mysqld] group

[mysqld]

log-bin=1

We can also set the format we would like:

SET GLOBAL binlog_format = {Statement/Row/Mixed}

 

Prior to MySQL 5.7.7, statement-based logging format was the default. In MySQL 5.7.7 and later, row-based logging format is the default.

 

Relay log:

This log is only be used for replication and as stated on Bin log discussion, Relay log is keeping track of transactions applied at slaves.

 

Slow Query Log:

This is very important log for performance point of view, the events will be captured in this log on either one of the situations:

When Query duration is more than 10 seconds( long_query_time seconds)

When query is not having indexes or no indexes being used on the table (log_queries_not_using_indexes)

Other conditions where Query can be tuned

 

DDL Log:

This log maintains the DDL operations of the system. This DDL is useful for Recovery purpose, during system crash or server down without proper shutdown mysql at the middle of incomplete DDL Operations this logs captured the information and helps the system to recovery it at the stable state. This log stored the data in Binary format only. Generally this log file is empty or does not exist if system starts /shutdown normally. As stated this is used only for recovery time.

The location of this log would be at data directory and name would be ddl_log.log

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/server-administration.html#server-logs

Advertisements
Posted in Error Log, MySQL, Others, Performance Tuning, Transaction Log, Troubleshooting | Tagged , , | Leave a comment

Day 14 MySQL sys Schema

MySQL 5.7.7 and higher includes the sys schema,

This has View, Stored Procedures and Functions to gather the performance related information, as it works on Performance Schema the Performance Schema must be enabled for the sys database.

Following some of the important tables:

sys_config: Contains information about configurations (variables).

We can enabled the performance related events as follows:

CALL sys.ps_setup_enable_instrument(‘wait’);

CALL sys.ps_setup_enable_instrument(‘stage’);

CALL sys.ps_setup_enable_instrument(‘statement’);

CALL sys.ps_setup_enable_consumer(‘current’);

CALL sys.ps_setup_enable_consumer(‘history_long’);

 

It is recommended to enable only default instruments and consumers so that if you like you can restore it quick instead to make customized and having problem for restore/resetup.

CALL sys.ps_setup_reset_to_default(TRUE);

Sys schema is introduced MySQL 5.7.7 so still DBA has to explore it more as most of the environments are still earlier then this

 

Reference:

 

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/sys-schema.html

Posted in MySQL, Others, Performance Tuning, What I learned today | Tagged , , , | Leave a comment

Day 13 MySQL Performance_Schema

performace_schema is a dynamic database created when you restart the mysql, all the data from performance_schema database will be clear. This database has its own storage engine –“performance_schema”

Even you may have this database but it could be possible that event gathering is not enabled for MySQL server then you will not get any data on this database. You can enable to retrieve the internal events of mysql. It gathers data using instruments and consumers

[Mysqld]

performance_schema=ON

It can be verify using

Mysql> SHOW VARIABLES LIKE ‘performance_schema’

To get information about which all instruments and consumers are enabled

Mysql>SELECT * FROM setup_instruments;

Mysql>SELECT * FROM setup_consumers;

We can update this tables and enabled those events as per our requirement, default not all are enabled.

This database is not maintain any log in binary log and do not get replicated. This is specific to given instance.

Performance schema database collect very useful data for performance point of view and provide internal information about the events and help DBA to understand system. It will provide detail information about queries and memory usage and all performance related information with minimum impact on the system.

Tables:

events_waits_current

 

SELECT EVENT_NAME, SUM_TIMER_WAIT

FROM events_waits_summary_global_by_event_name

ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Similarly there are several important events which will help to resolve any performance problems.

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/performance-schema.html

Posted in MySQL, Others, Performance Tuning, What I learned today | Tagged , , , , | Leave a comment

Day 12 INFORMATION_SCHEMA database

Yesterday we discuss about mysql system database, today we will see “INFORMATION_SCHEMA” database, this we cannot call as database because it is not stored any data directly, it provides information about metadata and other information which mysql has – data dictionary and system catalog.

In short you can view the configuration and other database metadata and information about objects.

Some of the tables from this tables are:

CHARACTER_SETS         –    complete Character sets information

COLLATIONS             – complete Collactions information

COLUMNS                               – all column information from all schemas

COLUMN_PRIVILEGES                    –

ENGINES

EVENTS

FILES

GLOBAL_STATUS

GLOBAL_VARIABLES

KEY_COLUMN_USAGE

OPTIMIZER_TRACE

PARAMETERS

PARTITIONS

PROCESSLIST

PROFILING

REFERENTIAL_CONSTRAINTS

SCHEMATA

SCHEMA_PRIVILEGES

SESSION_STATUS

SESSION_VARIABLES

STATISTICS

TABLES

TABLESPACES

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TRIGGERS

USER_PRIVILEGES

VIEWS

INNODB_*

 

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/information-schema.html

Posted in General, MySQL, Others, What I learned today | Tagged , | Leave a comment

Day 11 MySQL System Database

Previous days we discuss Variables and configurations which are quite important for DBA to know. As stated earlier there are some system database MySQL has which will also contains very important information. Now on we will spend some time on discussion on those system databases. Today we will discuss about “mysql” system database. On latest version Oracle is moving most of the variables into System databases, so It could be possible that in future this databases will be more used and contains more information(from MySQL Ver 8.0 on but in our blog we will discuss till 5.7.x version).

Mysql database contains table related to following:

  • User related Tables: when you create user and provide access the information goes into these tables:

User,db, tables_priv, columns_priv ,procs_priv, proxies_priv

  • Object related Tables
  • Event,func, plugin, proc
  • Log related tables
    • General_log, slow_log
  • Time Zone related Tables
    • Time_zone.*
  • Replication related Tables
  • Optimizer related Tables

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/server-administration.html#system-database

Posted in General, MySQL, Others, What I learned today | Tagged , , | Leave a comment

Day 10 Option file

So far we have discussed various mays to configure MySQL, Environmental Variables, Global Variable, Session Variables, Status Variable and Server Variables. All this variables are important and set the variable some of them dynamically affects the system and some required system restart.

Today we will be discussing on very important way to configuration mysql system using Option file also known as Configuration file (my.cnf). If you know MySQL you must be aware of this file. Most of the command-line Variables can be configurable using option file.

This is the standard way of setup the system MySQL read this file at startup and configure the system accordingly, so when you run the command those values will be set.

*again – Want to repeat what I stated earlier that configuration options is totally depends upon:

  • Storage engine : Specific to Storage engine like innodb, ISAM, and others
    • Operating system: Redhad, other Linux, Windows.
  • MySQL Version: Earlier version and new version, like newer versions and MySQL 8 is not moving variables into tables and decrypting Variables

 

Order of option file read: /etc/my.cnf, /etc/mysql/my.cnf, $MYSQL_HOME/my.cnf, ~/.my.cnf, ~/.mylogin.cnf

Option file has a configuration option in “Groups” and MySQL program read those groups.

Eg. Mysqld_safe read [mysqld] and [mysqld_safe] groups from option file. So all the configuration set in that group will be retrieved during mysqld_safe program.

MySQL makes no guarantee about the order in which option files in the directory will be read.

Some Groups for option file are as follows:

[client] – client – users access this group

[mysql] – mysql client access this group

[mysqld] – mysql startup access this group

[mysqld_safe] – similar to mysqld +mysqld_safe

[mysql.server]

[mysqlAdmin]

[mysqlDump]

option file is very important and main file where all MySQL is getting controlled.

Template:

http://www.fromdual.com/mysql-configuration-file-sample

 

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/programs.html#option-files

Posted in MySQL, Others, What I learned today | Tagged , | Leave a comment

Day 9 Variables

In earlier days we have discussed about providing options with environmental variable and command line, today we will be discussing on other variables, there are Global Variables and Session/Local Variables which reflects the state of the MySQL and we can set values to it. Status Variable is maintain the status of Global and Session Variable.

 

There is another very important variable is System variables these variables provides the system state.

MySQL is very flexible and has many exceptions and different compare other RDBMS. Here we have command called “SHOW” this command will shows the information about variables.

Most of these variables value can be change dynamically and will not require system/mysql restart and for some it may require.

Want to repeat what I stated earlier that configuration options is totally depends upon:

  • Storage engine : Specific to Storage engine like innodb, ISAM, and others
    • Operating system: Redhad, other Linux, Windows.
  • MySQL Version: Earlier version and new version, like newer versions and MySQL 8 is not moving variables into tables and decrypting Variables

 

System Variable: MySQL maintains the system status in system variable, most of the time this variables are set during MySQL Startup and mostly it is dynamic for some of the variable it requires MySQL to restart.

 

Global Variables is specified with @@GLOBAL.<variable> and Session/Local Variable is with @@SESSION.<Variable> ,  this is similar as what developers used in their programming.

Some of the Variables are BOTH Global and Session.

Local Variable are more specific to the current session:

Some of the important Variables:

@@Hostname- current host name

@@max_connections – max connection setting

 @@innodb_buffer_pool_size  – Memory size allocated to MysQL

@@innodb_log_file_size – Log file size

@@ log_bin– is bin log enabled

@@Threads_connected – current connection to server

@@Max_used_connections – max connection since server started.

@@ Uptime – server up time

To set global variables – SUPER privilege is required.

Mysql>SHOW Variables;

This command will show all the variables.

You can use “LIKE” Keyword with show command to filter it.

Mysql>SHOW Variables like ‘%bin%’

Status Variable: is shows the status of the variable and those variables are either Global/Session Variables generally they are not called same name in command-line, option file or system variable also they will not be set dynamically by system.

Status variable provide status of Global or Session Variables. (see reference for details)

*MySQL Reference provide all list of Variables (Show Variables).

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/server-administration.html#mysqld-option-tables

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/server-administration.html#server-status-variables

Posted in Basic, MySQL, Others, What I learned today | Tagged , , , | Leave a comment

Day 8 Configuration- Command-line

Yesterday we discuss about different ways of options and configuring with environment variable which will place MySQL locate for values for parameters used during related program execution if it is not specified elsewhere,

Today we will discuss about  Command-line options. we pass options directly when we execute the program when we provide options during command like all the other options will be ignored.

So during mysqld startup if you provide the options.

Eg. Shell> mysql –uroot –p -h myhost

It will prompt for password of root user

If you do not provide any options to the program

It will search for option file (my.cnf) as specified order like user profile, server option file if that is also not found then it may look for variables and environmental variable and if all fails it will throw an error.

This is application to almost all the programs.

Some of the variables, options are having standard default values for system to run effort free these options/values can be changes.

It is recommended that for big system we can customize the system values.

  • A MySQL program started with the –no-defaults option reads no option files other than .mylogin.cnf.
  • MySQL makes no guarantee about the order in which option files in the directory will be read.
  • If you like, underscores in a variable name can be specified as dashes

Eg.

max_allowed_packet and max-allowed-packet are same

  • Option is case sensitive -v and -V are valid but have different meanings (–verbose and –Version options).
  • When providing option on the command line you can give size with K, M and G for KB, MB and GB respectively but for the same purpose/variable set value cannot be in that format it has to be in Bytes only.

Eg:

Shell> mysql –max_allowed_packet=16M

mysql> SET GLOBAL max_allowed_packet=16*1024*1024;

Most of the command-line Variables can be configurable using option file,

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/server-administration.html#mysqld-server

 

Posted in MySQL, Others, What I learned today | Tagged , | Leave a comment

Day 7 Configuration – Environmental Variable

On day 6 we discussed about Programs. today  is very important day, we will discussing about Configuration options or parameters. All the program requires options/parameters accordingly it works differently. as MySQL is an open source and everyone want to use its own way of configuring the system.There are multiple ways to provide or set the parameters.

Every program works as per the parameters. Following are some of the important configuration options(orders may vary).

  1. Environment variables
  2. Option file
    1. Order of option file read: /etc/my.cnf, /etc/mysql/my.cnf, $MYSQL_HOME/my.cnf, ~/.my.cnf, ~/.mylogin.cnf
  3. Global Variable
  4. System tables
  5. Status Variable
  6. Session/ Local Variable
  7. Command-line options

These are the ways you can setup or configured the parameter of the MySQL server. It starts with command-line options if you find the parameter on command itself MySQL uses it, if that option is not found their it will check for other places like user profile option file. Session variable, status variable, system table, global variable, environment variable. Accordingly program/command will execute.

To get help for program use

Shell>mysql –help;

Mysql>mysql -?; (Ver. 5.7.5 on)

The configuration options is totally depends upon:

  • Storage engine : Specific to Storage engine like innodb, ISAM, and others
  • Operating system: Redhad, other Linux, Windows.
  • MySQL Version: Earlier version and new version, like newer versions and MySQL 8 is not moving variables into tables and decrypting Variables

Configuration using Environmental variable:

This is the environment OS specific variable and we can use those variable into our mysql program as well this will be helpful when we have multiple instances sharing similar information.

Eg.

shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock

shell> MYSQL_TCP_PORT=3307

shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT

shell> mysql –u root –p

It will retrieve the data for Port, sock from Environmental variable even it is not be included in option or other locations.

Like this we can have multiple Environmental variables set (see reference for all list).

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/programs.html#environment-variables

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/installing.html#data-directory-initialization

 

Posted in MySQL, Others, What I learned today | Tagged , | Leave a comment

Day 6: MySQL Programs

On day 5 we understand how to install MySQL or upgrade to latest version. Today we will discuss once installation is done we will be having multiple executable programs by which MySQL Runs, every RDBMS has its executable.

MySQL programs are categories in following types here we are specifying brief and important programs more detail can be found in reference document(MySQL Reference Guide):

  1. Startup Program:
    1. mysqld: is the daemon – base MySQL engine executable and it should be running.
    2. mysql_safe, mysql.server, mysqld_multi: these are programs using it we can start /stop MySQL Engine with several ways.
    3. Systemd is replaced by mysql.server, mysqld_safe and mysqld_multi on several Linux platforms using RPM Installation.
  2. Installation Program:
    1. mysql_install_db: this program initialize data directory and creates mysql Database. Executed only once when installation of MySQL system.
    2. mysql_upgrade: used after upgrade operations and check tables and repairs if necessary.
  3. Client Program:
    1. mysql: is the command- line tool to interface MySQL Engine.
    2. mysqladmin : used to perform administrative commands
  4. Administrative:
    1. mysqlcheck: used for Check, analyze and optimize for some engine it also repairs.
    2. mysqldump: For backup
    3. innochecksum : for integrity of database.
    4. mysqlbinlog:used to read the statement from bin log file
    5. mysqldumpslow :Read slow query log

 

Standard Location /path for MySQL programs or files(may vary):

mysqld:                                                /bin

Configuration file:                            /etc/my.cnf

Error Log:                                             /var/log/mysqld.log

Init.d location:                                   /etc/init.d/mysqld

Pid file:                                                /var/run/mysql/mysqld.pid

Socket:                                                 /var/lib/mysql/mysql.sock

Log files, databases:                       /basedir/data or /var/lib/MySQL

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/programs.html#option-files

 

Posted in General, Installation, MySQL, Others | Tagged , | Leave a comment