MySQL Global Variables,Session Variables and default MySQL databases

Here we will first go through the different types of variables, i.e. , Global and Session variables, and then look at the default databases of the mysql server.


Global Variables

  • Global variables are parameters that define how the server behaves.
  • Each global variable has a default value which is initialised when the server starts.
  • Global variable is identified by @@variable_name (@@ sign)

To see the default values of these global variables you can either execute:

    show global variables like 'variable_name';



     



           or

    select @@varible_name;











To get the names of all the global variables:

    show global variables;

You can also change the default value of any global variable according to your needs.

For example:

    set global max_connections = 300;













More examples of global variables are:

wait_timeout

server_id

max_connections etc.


Session Variables

Session variables are also used to configure server settings, but they apply only to the current session.

Changes to the session variable only last for the duration of the current session.

They are also identified by @@varible_name (@@ sign).

For viewing them you can either execute:

    show session variables like 'variable_name';

        or

    select @@variable_name;


You can change the default value of any session variable according to your needs.

For example:

    set session sql_mode = 'STRICT_TRANS_TABLES';











More examples of session variables are:

auto_commit,

time_zone(for this current session) etc.


MySQL system databases

MySQL comes with some default system databases.

  • information_schema
  • mysql
  • performance_schema
  • sys
  • Test - gets deleted by running mysql_secure_installation


1. information_schema

  • It is like a system catalog or data dictionary.
  • It provides access to the properties of main data, or we can say data about data
  • Tables in this database are read-only, therefore CRUD operations are not allowed.












We can look at the description of one of the tables, such as  PLUGINS:









2. mysql

Contains system tables that are used for managing mysql itself, such as user authentication, permission, and other internal configurations.

One of the primary uses of mysql database is to manage user accounts and permissions such as:

  • who can access the MySQL server
  • what operations users are allowed to perform
  • global permissions etc
We can have a look at all the users that has been created in this server (Table name = user)















3. performance_schema

  • As the name suggest, it stores and collects information about the performance of the server's operations.
  • Data is related to server' performance, including queries, memory usage, thread activity etc.
  • Helps in tracking performance issues and optimising queries.

We can look at session status of the server using table session_status.








4. sys

  • Collection of views, stored procedures and functions that provide performance and operational data about the MySQL server.
  • It is similar to performance_schema but has more user friendly UI

We can have a look at the host summary using table host_summary. 








Great! We now have the basic understanding of the Global and Session variables, as well as a good overview of the default databases in the mysql server ðŸŽ‰. 





Comments

Post a Comment

Popular posts from this blog

MySQL architecture, locations of MySQL installed files and MySQL Shell commands

Image
In this post we will first take a look at the mysql architecture and then explore the locations of the installed mysql files. First layer is the client-layer: It is an interface between the mysql user and the database server. It handles client connections and requests. authentication and authorisation to ensure secure access. Second layer is the Daemon process: mysqld(daemon process) runs core server process that runs in the background to manage the database operations. It oversees query execution, caching, session management. Third layer is the MySQL brain: This is the core execution engine responsible for managing and processing data. It includes the storage engine layer, with innoDB being the most commonly used storage engine Fourth layer is the Physical storage layer: This layer deals with the actual data and database files on the underlying file system. Handles how MySQL stores and receives data physically on disk. This is the basic overview of the MySQL architecture. Now lets exp...
Read more