Welcome To TechBrothersIT: October 2018

What is Slow Query Logging in MairaDB or MySQL and How to Enable Slow Query Log in MySQL or MairaDB

Slow Query Log

⦁ Slow query log stores the information for querys that took long time to run.

Enable Slow Query Logging :

For Stand Alone :

Go to /etc/my.cnf and add below entries


slow_query_log = 1

long_query_time = 1

slow_query_log_file = /var/log/mysql/slow-query.log

log_queries_not_using_indexes=1

For Galera Cluster :

Go to /etc/my.cnf.d/server.cnf file and add below



slow_query_log = 1

long_query_time = 1

slow_query_log_file = /var/log/mysql/slow-query.log

log_queries_not_using_indexes=1

Check Current values :

SHOW VARIABLES LIKE 'slow_query_log';

SHOW VARIABLES LIKE 'long_query_time';

SHOW VARIABLES LIKE 'slow_query_log_file';

Enable General Log for Table :

To write logs into tables, the log_output server system variable is used. Allowed values are FILE, TABLE and NONE. It is possible to specify multiple values, separated with commas, to write the logs into both tables and files. NONE disables logging and has precedence over the other values.
SET GLOBAL log_output = 'FILE,TABLE';
If General log is active for table, you can query the table to see the information.

SELECT * FROM mysql.slow_log;

Video Demo : How to capture long running queries in MySQL or MariaDB

How to schedule backup Job for MariaDB or MySQL by using crontab

Scenario:

you are working as MySQL or MariaDB admin and you need to schedule daily backups for a database.How will you schedule backup for MariaDB or MySQL database?

Solution:

Crontab can be used to schedule the backups for MySQL or MariaDB , Crontab entry consist of six below fields.

minute(s) hour(s) day(s) month(s) weekday(s) command(s)

Field	Value	Description
minute	0-59	The exact minute that the command sequence executes
hour	0-23	The hour of the day that the command sequence executes
day	31-Jan	The day of the month that the command sequence executes
month	12-Jan	The month of the year that the command sequence executes
weekday	0-6	The day of the week that the command sequence executes (Sunday = 0, Monday = 1, Tuesday = 2, and so forth)
Command	special	Provide the Command you like to execute

Let's make changes to our crontab file to run backup every minutes, if you like to run backup every minute, you will be providing * * * * * for all the time fields

open crontab file by sudo vim /etc/crontab

* * * * * root mysqldump -u [User]-p[Password] DBName> /BackupDirectory/DatabaseName_`date +\%m_\%d_\%YT\%H_\%M_\%S`.sql

Let's say if I am taking backup of abc database to mysqlbackup directory every minute, I will be using below command.

* * * * * root mysqldump -u root -pDBA321 abc > /mysqlbackup/abc_`date +\%m_\%d_\%YT\%H_\%M_\%S`.sql

Video Demo : How to schedule backup for MySQL database by using Crontab job

What is General Query Log in MariaDB and How to Enable Query Logging in MariaDB

What is General Query Log in MariaDB

The general query log is a log of every SQL query received from a client, as well as each client connect and disconnect.
As it keep the records of every query it grows very fast
If you only are intereted to keep the records for query for data change, you might want to use binary log.
By defaul the general query log is disabled
if you want to Enable it , you have to add two entries in config file.

How to Enable General Log in MariaDB

For Stand Alone MariaDB Installation go to /etc/my.cnf and add below entries


general_log =1

general_log_file=/directory/filename.log

For Galera Cluster MariaDB Installation, go to /etc/my.cnf.d/server.cnf file and add below


general_log =1

general_log_file=/directory/filename.log

How to Check Current values for General Query Log

MariaDB [(none)]>  SHOW VARIABLES LIKE 'general_log';

MariaDB [(none)]>  SHOW VARIABLES LIKE 'general_log_file';

Enable General Log for Table :

MariaDB [(none)]>  SET GLOBAL log_output = 'FILE,TABLE';

If General log is active for table, you can query the table to see the information.

MariaDB [(none)]>  SELECT * FROM mysql.general_log;

Check Demo video: How to enable General Query log in MariaDB

What is Error Log and How to setup in MariaDB

Error Log

The error log contains a record of critical errors that occurred during the server's operation, table corruption, start and stop information.

If the log-warnings server system variable is 1 (default), critical SQL warnings will be logged in the error log. If its value is 0, the logging of SQL warnings is disabled. If its value is greater than 1, Statements that were unsafe to log as statement-based, aborted connections and access-denied errors for new connection attempts are also written to the error log. For a complete list, see the description of the log-warnings variable.

On Unix systems which support the logger program, errors can also be written into syslog.

On Windows, the errors are also written in the Windows Event Log, in the Application Log. The source of the log enties is MySQL.

What is Statement based: The default format until MariaDB 10.2.3, statement-based logging logs all SQL statements that make changes to the data or structure of a table. Enable with --binlog-format=STATEMENT.

The above definitions are taken from mariadb website. find the link here

Location of Error Log

Default: /var/lib/mysql/hostname.err

Current Error Log Location:
If you need to check the current location for your error log, you can use below statement.

MariaDB [(none)]>  SHOW VARIABLES LIKE 'log_error';

Check log_warraning value :
You can run below statement on mairadb to get current value for log_warrinings variable

MariaDB [(none)]>  SHOW VARIABLES LIKE 'log_warnings';

How to Enable:

In case of Stand alone mairadb , go to /etc/my.cnf and make changes.
e.g
log-warnings=1
log-error=/DirectoryPath/ErrorLogFileName.log

In case of Galera cluster installation : Go to /etc/my.cnf.d/server.cnf
log-warnings=1
log-error=/DirectoryPath/ErrorLogFileName.log

Video Demo : What is Error Log in MariaDB and How to enable Error Logging in MariaDB

Restoring a single database from a complete MySQL database dump

Scenario:

You are working as MySQL / MariaDB database administrator. You are provided a complete MySQL or MariaDB database dump but you need to restore only one database out of entire database dump. What steps you will take to restore single database from complete MySQL database Dump.

Solution:

To create the single database dump file you can use below command.

# mysqldump -u UserName--password=PasswordforUser--all-databases >/DestinationDirectory/BackupFileName.sql

To Restore single database from complete backup dump, you can use below command.

# mysql -u [UserName] -p[Password] --one-database destdbname < alldatabases.sql

You need to create the empty database first before you run the restore database command. Let's say we need to restore abc database, then you can use below command.

# mysql -u root -p --one-database abc < /mysqlbackup/alldbs.sql

Video Demo: How to Restore One Database From All Database mysqldump

How to monitor the Restore progress in MySQL or MariaDB

Scenario:

You are working as MySQL/ MariaDB database administrator. You need to restore / import large backup file to database and would like to monitor the progress of restore. Which tools you can use to monitor the restore progress in MySQL / MariaDB?

Solution:

In our backup monitor progress posts, we mentioned that you need to install PV (Pipe Viewer). Check the link here.

Once the PV is installed you can use below command to import / Restore and monitor progress.

# pv /SourceDirectory/sqlfile.sql | mysql -uUserName -pPassword dbname

If I need to restore abc database from exiting abc.sql file. I can use below command.

# pv /mysqlbackup/abc.sql | mysql -u root -pDBA123 abc

For demo, check the video : How to monitor MySQL or MariaDB restore progress.

Restore single MySQL table from a full mysqldump backup file

Scenario:

You are working as MySQL/ MariaDB Admin, you have schedule daily full backup of a database. Once of the Junior DBA dropped the table from a database. You are asked to restore only single Table from full backup that were taking last night, What steps you will follow to restore Single Table from MySQL or MariaDB Backup?

Solution:

First of all you will be separating the data for deleted table from full backup. You can set program to extract the data for the table you want to restore. The below command will extract the data for table and create new file. Once we have the data for single table, then we will restore.

shell > sed -n -e '/DROP TABLE.*`YourTableName`/,/UNLOCK TABLES/p' /SourceDirection/FullbackupName.sql > Extracted_YourTableData.sql

Let's say our table name is employee, then we will modify above as shown below.

shell > sed -n -e '/DROP TABLE.*`employee`/,/UNLOCK TABLES/p' /mysqlbackup/testdb.sql > Employee.sql

Now you are ready to restore employee table to testdb.

shell > sudo mysql --user=root --password=DBA123 testdb< /mysqlbackup/Employee.sql

For Demo : Check the video step by step - How to restore single table from full backup in MySQL or MariaDB.

How to create zipped Backup and Restored zipped Backup in MySQL or MariaDB

Scenario:

You are working as MySQL or MariaDB Admin, You have some big databases those you would like to take backup. It is good idea to take compressed backups for large databases. How you would perform compress backup and restore them on another server?

Solution:

To take the compressed backup, you will use below command.

shell > mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

For example if you have testdb and you would like to run the compress backup, your command will look like below.

shell >  mysqldump -u root  -pDBA123 testdb | gzip -9 > /mysqlbackup/testdb.sql.gz

To RESTORE the compressed backup to a database, you will use below syntax, Make sure to create the empty database first.

shell > gunzip < backupfile.sql.gz | mysql -u [uname] -p[pass] [dbname]

Let's say you are restoring the testdb that we took above. Your command will look like below.

shell > gunzip < /mysqlbackup/testdb.sql.gz | mysql -u root  -pDBA123 testdb

To see the demo, please watch video : How to take compress backup and restore in MySQL or MariaDB

How to Restore a database in MySQL or MariaDB

Scenario:

You are working as MySQL or MariaDB Admin and you have taken the full backup of a database by using mysqldump utility, Now you need to restore that backup to database, how would you do that?

Solution:

To take full backup of a database in MySQL or MairaDB , below mysqldump command can be used.

shell > mysqldump --user=YourUserName --password=PasswordfortheUser DatabaseName >/DestinationDirectory/BackupFileName.sql

Let's say if you have a database name testdb and you would like to take full backup, you can use below command

shell > mysqldump --user=root --password=DBA123 testdb >/mysqlbackup/testdb.sql

To Restore, you will be using below command. Make sure you have created empty database first , otherwise you will get an error.

shell > mysql --user=UserName --password=PasswordforUser DatabaseName < /SourceDirectoryforBackup/BackupFile.sql

Let's say if we would like to restore testdb, I can use below statement.

shell > mysql --user=root --password=DBA123 testdb < /mysqlbackup/testdb.sql

Video Demo : How to restore Database in MariaDB

How to drop multiple databases in MySQL or MariaDB

Scenario:

You are working as MySQL or MariaDB DBA, you have created many databases for testing purpose. After testing you are asked to drop all of them, How would you do that?

Solution:

The below script can be used to loop through list of database those you wan to delete. Change the below query to choose the databases you want to drop.

Select Table_schema from information_Schema.tables where Table_schema like "%your pattern%"

# Provide the user name that has permission on mysql to drop the database
MYSQL_USER="yourUserName"
#Provide the Password 
MYSQL_PASS="YourPasswordforAboveUser"
MYSQL="mysql"
# Get the database list
for db in $(mysql -B -s -u $MYSQL_USER --password=$MYSQL_PASS  -e 'Select Table_schema from information_Schema.tables where Table_schema like "%your Pattern to filter the DBs%";')
do
  # backup each database in a separate file
echo $db
 $MYSQL -u$MYSQL_USER -p$MYSQL_PASS -Bse "drop database $db"
done

How to monitor the progress of backup in MySQL or MariaDB

Scenario:

I am taking backup of many databases in MySQL or MairaDB and I would like to monitor the progress of backups.

Solution:

Either MySQL or MariaDB does not provide direct way to monitor the progress of backup. You can use PV("Pipe Viewer") to monitor the progress of backups. PV("Pipe Viewer") is a tool for monitoring the progress of data through the pipeline. It can be used to visualize how quickly data is passing through and progress.

First of all we need to install the PV. I am using Centos. If I try to install PV on Centos, I will get below error.

# sudo yum install pv

Including mirror: mirror.es.its.nyu.edu

* base: mirror.es.its.nyu.edu

* extras: repo.ialab.dsu.edu

* updates: centos-distro.cavecreek.net

No package pv available.

Error: Nothing to do

If you are running Centos on your Server, first of all you will be installing EPEL (Extra Packages for Enterprise Linux) Repository. Below below commands to intall EPEL Repository.

# wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

# rpm -ivh epel-release-latest-7.noarch.rpm

Once the EPEL repository installed, you can check by using below command

# yum repolist

Now you are ready to install the pv, use below command to run the pv

# sudo yum install pv

Backup with Progress Bar:

Once the pv is installed, you will be using mysqldump with pv. In PV you have to provide estimated size of file. You can get estimated size of backups by checking the size of database. The backup file is most like smaller than actual database size.

SELECT table_schema AS "DBName", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "DB Size in(MB)" FROM information_schema.TABLES GROUP BY table_schema;

Use below command to take backup with progress

# mysqldump  | pv --progress --size 50m > backupfile.sql

50M is 50MB that is I am expecting the size of my file.

Let's say If I am taking the backup of all the databases in single file, I can use below mysqldump command with pv to monitor the progress of my backup. I am expecting the size of backup file around 325 MB.

# sudo mysqldump -u root -p --all-databases | pv --progress --size 325M >/mysqlbackup/alldbs.sql

How to get the file size in Megabytes ( MB ) in linux

Scenario:

I need to get the size of files in a directory in MegaBytes (MB). Which command I can use to see the size of files in MB?

Solution:

When you run ls -la command in linux, it shows you the file size in KB ( Kilobytes). To see the file size in MegaBytes ( MB) , use below command.

ls -la --block-size=M

How to backup all Databases to single file in MySQL or MairaDB by using mysqldump

Scenario:

You are working as MySQL/ MariaDB DBA, you have a MySQL or MariaDB service on which you have many databases, you need to take backup of all the databases in single file. Later this file can be used on same of different Server for restore purpose.

Solution:

mysqldump program can be used to take backup of all the databases in single file. Below statement should do the trick.

mysqldump -u root -p --all-databases >/destinationdirectory/AllDatabases.sql

Video Demo : How to backup all databases to single file in MySQL or MariaDB by using mysqldump

Label

Slow Query Log

For Stand Alone :

For Galera Cluster :

Check Current values :

Enable General Log for Table :

Scenario:

Solution:

What is General Query Log in MariaDB

How to Enable General Log in MariaDB

How to Check Current values for General Query Log

Enable General Log for Table :

Error Log

Location of Error Log

How to Enable:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Scenario:

Solution:

Backup with Progress Bar:

Scenario:

Solution:

Scenario:

Solution: