How to uninstall or remove MySQL from Linux CentOS or RHEL - MySQL DBA Tutorial

How to uninstall or remove MySQL from Linux CentOS or RHEL 

Uninstalling or removing MySQL Server from Linux Centos or RHEL is pretty easy. Let's check the MySQL Service first if it is running and active.

sudo systemctl status mysqld
It should show you that the service is active and running.

Now to uninstall MySQL from RHEL or Centos, run below command

sudo  yum remove mysql
The above command will run the uninstallation process and ask you if you are sure to remove MySQL Server. type y and it will remove MySQL Client and MySQL Server. To make sure the MySQL Server is removed from Linux machine, you can run

sudo systemctl status mysqld
It should return you message that service does not exists.

How to uninstall MySQL Server from Linux

How to Install MySQL 8.0 on Linux RHEL - MySQL DBA Tutorial

How to Install MySQL 8.0 on Linux RHEL

Below are the steps to install MySQL 8.0.13 Server on RHEL Linux.

Download and add MySQL Yum Repository to Linux Repository
wget https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm


Install the downloaded package by using below command
yum localinstall mysql80-community-release-el7-1.noarch.rpm


Verify that the MySQL Yum repository has been added successfully
yum repolist enabled | grep "mysql.*-community.*"


Install latest MySQL that is going to be MySQL 8.0
yum install mysql-community-server


Start MySQL Server
sudo service mysqld start


Secure MySQL Server
When MySQL 8.0 version is installed, temporary password for root is created in /var/log/mysqld.log
grep 'temporary password' /var/log/mysqld.log

Get the password and then run below command. It will ask you to provide the password. Provide the temporary password and then change the root password.

sudo mysql_secure_installation


Your installation of MySQL 8.0 Server is completed by RHEL. To verify MySQL Server is running , run below command.
sudo systemctl status mysqld
It should show you that the mysqld service is active and running.

To login to MySQL server, you can use below command.
mysql -u root -p
Provide the root password and you are all good to work on MySQL Server.

How to Install MySQL Server 8.0 on Linux RHEL

How to Install MySQL 8.0 on Linux CentOS - MySQL DBA Tutorial

How to Install MySQL 8.0 on Linux CentOS

Below are the steps to install MySQL 8.0.13 Server on CentOS Linux.

Download and add MySQL Yum Repository to Linux Repository
wget https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm


Install the downloaded package by using below command
yum localinstall mysql80-community-release-el7-1.noarch.rpm


Verify that the MySQL Yum repository has been added successfully
yum repolist enabled | grep "mysql.*-community.*"


Install latest MySQL that is going to be MySQL 8.0
yum install mysql-community-server


Start MySQL Server
sudo service mysqld start


Secure MySQL Server
When MySQL 8.0 version is installed, temporary password for root is created in /var/log/mysqld.log
grep 'temporary password' /var/log/mysqld.log

Get the password and then run below command. It will ask you to provide the password. Provide the temporary password and then change the root password.

sudo mysql_secure_installation


Your installation of MySQL 8.0 Server is completed by CentOS. To verify MySQL Server is running , run below command.
sudo systemctl status mysqld
It should show you that the mysqld service is active and running.

To login to MySQL server, you can use below command.
mysql -u root -p
Provide the root password and you are all good to work on MySQL Server.

How to Install MySQL Server 8.0 on Linux CentOS


How to find on which port MySQL Server running on Windows Machine - MySQL DBA Tutorial

How to find on which port MySQL Server running on Windows Machine




There are multiple ways to find the port which MySQL Server is listening. If you are already connected to MySQL server and you want to check the port, you can run the below query.

show variables where variable_name in ('hostname','port');

Another way to find out the port which MySQL Server is using on Windows is , Go to my.ini file that is MySQL configuration  file and you can check the port.

To find the my.ini file for MySQL Server, you can go to services and then go to properties.
my.ini file location for MySQL Server in Windows

Once you know the location of my.ini file, open the .ini file and look for port.

Check my.ini file for MySQL Port on which it is listening in Windows


Check out the video to see details how to find the port on which MySQL Servers are running

How to find the location of my.ini file for MySQL Server on Windows Machine - MySQL DBA Tutorial

How to find the location of my.ini file for MySQL Server on Windows Machine

In you have default installation on Windows machine for MySQL Server, you will be able to find the my.ini file in C:\ProgramData\MySQL\MySQL Server XX\. The XX defined the version of MySQL server. In case you have installed 8.0 then path for my.ini will look like "C:\ProgramData\MySQL\MySQL Server 8.0".

my.ini location in windows for MySQL Server 8.0

In case you have done multiple installation of MySQL Server on windows machine, the ini file location can be different from above. 

To find the ini file for any MySQL server installation on Windows Machine, you can always go to Services and then right click on MySQL service and go to Properties, you will be able to see the ini file location.

See below example, I have installed second MySQL Server and I have my.ini file on different locaiton, also I have renamed the my.ini file to mysqldev.ini. 

Watch the video demo for detail explanation, Where to find the my.ini file location on windows for default and manually installed MySQL Server.

How to find Data Directory Location in MySQL on Windows Machine - MySQL DBA Tutorial

How to find Data Directory Location in MySQL on Windows Machine


There are multiple ways to find the location for Data Directory Location for MySQL on windows machine.

1) By using @@datadir:
If you are already connected with MySQL Server, you can run below query to find out the data directory location.

mysql > Select @@datadir;




2) Check value of datadir in my.ini file

You can go to my.ini file and find the line datadir=, that is the path for your data directory. In case of default installation, you will find ini file in C:\ProgramData\MySQL\MySQL Server 8.0.



Video demo, how to find the data directory for multiple MySQL servers installed on Windows


How to connect from windows command prompt to MySQL command line - MySQL DBA Tutorial

How to connect from windows command prompt to MySQL command line

To connect to MySQL server on Windows Machine, you can locate mysql application that is location in C:\Program Files\MySQL\MySQL Server xx\bin in case you have done default installation. If you have done manual installation then it depends where your base directory is. I have explained in video with details but most of the cases as you will have only single MySQL on Windows server, you will find mysql application under "C:\Program Files\MySQL\MySQL Server xx\bin".

xx defines the version. If I have used MySQL 8.0 for installation then it will be "C:\Program Files\MySQL\MySQL Server 8.0\bin".

To connect to MySQL Server on windows Machine, open Command line by going to search or going to Run and typing cmd.
On windows command line, go to "C:\Program Files\MySQL\MySQL Server 8.0\bin" by using 

cd C:\Program Files\MySQL\MySQL Server 8.0\bin

To connect to MySQL Server on default port , that is 3306, you will type


C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p
Provide the password and you will be connected to MySQL Server command line.

If you are using different port for MySQL then use below command, I have used xxxx, let's say if you are using port 3307 then change xxxx with 3307.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p --port=xxxx

Check the video for detail demo , how I connected with default MySQL server and MySQL running with different port than 3306.

How to Stop MySQL or Start MySQL Server on Windows Machine - MySQL DBA Tutorial

How to Stop and Start MySQL Server on Windows Machine

When you install MySQL Server on Windows Machine, the Service is installed with name MySQLXX, XX is version. Let's say if you have installed 8.0 then your MySQL Service name will be MySQL80, In case if you have installed MySQL 5.7, then Service name will be MySQL57. 

The Service name can be different as well, depend upon the person who have installed the MySQL on Windows Server if he/she has choosed different name for Service.

To Stop the MySQL Service or Start the MySQL Service, you will go search Services in windows. Once the Services are open, locate MySQL Service and then Right click and Stop or Start.

How to Start of Stop MySQL Server in Windows


Check the video demo to get more details about stopping and restarting multiple MySQL Servers


How to Remove or Uninstall MySQL WorkBench from Windows Machine - MySQL DBA Tutorial

How to Remove or Uninstall MySQL WorkBench from Windows Machine explains step by step removal of uninstall of workbench from Windows Machine.

MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system.


How to Remove or Uninstall MySQL WorkBench from Windows Machine

How to Uninstall or Remove Multiple MySQL Servers from Windows Machine - MySQL DBA Tutorial

How to Uninstall or Remove Multiple MySQL Servers from Windows Machine explains
how to remove or uninstall multiple MySQL Servers from Windows Machine, If you have installed MySQL by using MySQL Windows installer, then you can go to Control Panel, then go to Add Remove Programs and Uninstall MySQL Server from there but when you have Installed multiple MySQL Server of same versions manually, In that case you have to remove the MySQL Services manually by going to Registry.
If you have used MySQL Installer to install different versions of MySQL on same machine, you can remove them by going to Add Remove program.



How to Uninstall or Remove Multiple MySQL Servers from Windows Machine


How to UnInstall MySQL from Windows Machine Step by Step - MySQL DBA Tutorial

How to Uninstall MySQL from Windows Machine Step by Step explains how you can uninstall or remove MySQL Server which you have installed on Windows machine. If you have installed MySQL by using MySQL Windows Installer, then it is simple, you will be going to Control Panel and then find Add Remove Programs and then delete the MySQL Server.
If you have installed multiple MySQL Servers manually , then you have to delete the MySQL Services manually from Registry. I have another video that explains how you can uninstall or remove multiple MySQL Servers step by step.


How to Uninstall MySQL from Windows Machine Step by Step

How to Change MySQL Data directory location in Windows - MySQL DBA Tutorial

How to Change MySQL Data directory location in Windows


How to Change MySQL Data directory location in Windows explains what steps you need to take to change the data directory for MySQL Server. First of all you will be stopping the MySQL servers by going to Services. Once the MySQL server service is stopped, you will copy the Data directory for MySQL Server to new location.
Go to configuration file my.ini and then update the location for data directory. You will find a line in my.ini datadir=" provide new path for data directory".
After you update and save the my.ini file. Restart the service and test if you are able to create database, create table etc. to make sure everything working fine.


How to Change MySQL Data directory location in Windows

How to Install Multiple MySQL Servers on a Windows Machine - MySQL DBA Tutorial

How to Install Multiple MySQL Servers on Windows Machine


demo explains the limitations that we can't install the MySQL multiple Servers on same machine by using MySQL Installer. Below are the steps you have to perform to install another MySQL server on windows machine on which you already have installed MySQL.


  1. Create a new folder on C drive, let's say MYSQLDEV
  2. Copy all the folders from c:\programfile\MySQL\ MySQL Server XX to MYSQLDEV
  3. Search for my.ini in %PROGRAMDATA%\MySQL and copy to MYSQLDEV and also copy Data directory
  4. Change the port for [client] and [mysqlid] in mysqldev.ini file, I have updated to 3307
  5. 5Change the basedir and datadir location to new location
  6. Execute mysqld --install MYSQLDEV --defaults-file="c:/MYSQLDEV/mysqldev.ini"
  7. Start the services net start MYSQLDEV
  8. Connect to MySQL mysql -u root --Port=3307
  9. Run show variables where variable_name in ('hostname','port');
  10. Change the root password if you need to for newly installed MySQL service by using     ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';


How to install multiple MySQL Servers on single Windows Machine

How to install different versions of MySQL server on same Windows Machine - MySQL DBA Tutorial

How to install different versions of MySQL server on same Windows Machine

demo explains

  • How to download MySQL Community Server from web for multiple versions of MySQL
  • How to Install MySQL 5.7 on Windows Machine
  • How to Install MySQL 8.0.13 on same Windows Machine where we have installed MySQL 5.7
  • Items to consider when installing multiple versions of MySQL on same Windows Machine
  • Connect to multiple MySQL servers on same machine by using MySQL Workbench

How to install multiple MySQL servers on same Windows machine step by step

How to download and Install WorkBench for MySQL on Windows Machine - MySQL DBA Tutorial

How to download and Install WorkBench for MySQL on Windows Machine

demo shows

  • How to download the MySQL Workbench installer from internet
  • How to install MySQL Workbench on Windows step by step

How to download MySQL Workbench and Install on Windows Machine

How to Install MySQL on Windows Machine in Real time - MySQL DBA Tutorial

How to Install MySQL on Windows Machine in Real time

demo explains

  • How to download MySQL 8.0.13  Community version for Microsoft Windows
  • How to Install MySQL by using windows Installer step by step
  • Choose the right application from variety of options such as Development, Full, Server Only etc.
  • Follow steps to configure MySQL during installation and provide Root User password and create another user if required.
  • Save the Installation logs for reference
  • To follow step by step tutorial for MySQL DBA for beginner to Advance


How to Install MySQL 8.0 on Windows Machine step by step 

Real Time MariaDB Interview Questions

MariaDB Interview Questions

Installation/Upgrade/Migration

  1. What is MariaDB?
  2. What versions of MariaDB you have worked on?
  3. What are the steps to Install  MairaDB on windows Machine?
  4. What are steps to install  MariaDB on Linux Machine?
  5. What are the steps to install MariaDB with Galera cluster on multiple Nodes?
  6. How to change the default data directory after installation of MariaDB or MySQL?
  7. How you will reset root password after installation of MariaDB?
  8. You need to uninstall MariaDB from Windows Machine, how would you do that?
  9. You need to uninstall  MariaDB from Linux Machine, Which command you will use to uninstall MairaDB?
  10. After Installation, how would you check if MariaDB service is running?
  11. How to restart  MariaDB Service?
  12. How to Stop MariaDB Service?
  13. What steps you will take if you need to upgrade mariadb from old version to new version?
  14. What steps you will need to perform if you need to migration from MySQL to MariaDB?

Logging

  1. How many different types of logging available in MariaDB?
  2. where would you find errors for MariaDB?
  3. If you need to find all the statement ran on MairaDB , Where will you find them?
  4. If you need to find which delete, update or insert statements in MariaDB were executed , where you will try to find them?
  5. You have executed FLUSH LOGS, will that delete the error log file in MariaDB?
  6. The developers are complaining that the some queries are running slow on MariaDB, Where will you find the long running queries?
  7. You are asked to find all the queries which are running on MairaDB which are not using indexes, where you will find those queries?
  8. Junior DBA has enabled slow query logging,general log and error logging for MariaDB, how will you check where the log files are saving?
  9. How would you enable the syncing of binary logs to disk on each commit in MariaDB?
  10. How would you configure the binary logs to get expired after 5 days in MariaDB?

Import / Export

  1. How you will import CSV file to MySQL or MairaDB Table?
  2. Which command do you use to export results to CSV file in MySQL or MariaDB?
  3. Can you export the results to JSON from MySQL or MairaDB?
  4. Which free Graphical User Interface tools are available to Manage MySQL or MariaDB?
  5. How to import multiple files to MariaDB or MySQL table?

Backup / Restore

  1. How would you perform backup of a database in MairaDB?
  2. You need to create a new directory with timestamp every time you run the backup job and take the backup of all databases in new created directory, how would you do that?
  3. You are running low on disk, you need to create zip backups for MariaDB databases, how would you perform zip backups?
  4. You have 100 tables in a database but you need to take backup of only single table, how would you perform backup of single table in MariaDB?
  5. How would you backup every table from a database to separate file in MariaDB?
  6. How would you backup all Databases to single file in MariaDB?
  7. You are backing-up large database, how will you monitor the progress of backup in MariaDB?
  8. How would you restore a database in MariaDB?
  9. A database has created the zip backup files, you need to restore them in MariaDB , how would you do that?
  10. A single backup file was created for 10 databases, you need to restore only single database out of that backup file, what steps you will perform to restore single database from file which has backup of all databases in MariaDB?
  11. Is there a way to monitor the Restore progress of a database in MariaDB?
  12. You need to run backups on schedule , how would you schedule the backups every 30 minutes?
  13. You are using mysqldump to create the backups, you need to create backup user for that. What permissions "backupuser" need?


Global Variables

  1. How you will set the max connections to 200 in MariaDB?
  2. If you need to set the InnoDB buffer pool to 80% of your server RAM, how would you do that in MariaDB?
  3. You are getting complain from developers that they can't create large temporary tables, how would you check and increase the temporary table size in MariaDB?
  4. What is heap table and can you set the max size of heap table in MariaDB?
  5. You want to enable the query cache to improve the performance of query execution, how would you enable Query cache for all queries in MariaDB?
  6. You need to create an event and schedule it, How would you schedule it in MariaDB?
  7. How would you change the server level collation in MariaDB?

Security / Permissions

  1. How to create a user "myuser" that should be only able to connect from machine with ip 10.0.0.10 in MariaDB?
  2. You need to create a user "dba" that should be able to connect to MariaDB from any machine?
  3. You need to create login/user for your team "DBA", that should have all the permissions like root, What permissions you will provide to "DBA" user in MariaDB?
  4. You have created a user "MariaDBUser", but the forgot the password, How can you reset password of a user in MairaDB?
  5. You need to check the permission of a user in MariaDB, which command you will use to check the permission of a user in MariaDB?
  6. For Audit purpose you need to provide list of all users on MariaDB server, where you can get the information in MariaDB?
  7. For Audit purpose , you need to provide all users with permissions , which table can provide you that information in MariaDB?
  8. What is Role in MariaDB, How to create a Role and why we need to create Role in MariaDB?
  9. You need to change the password for Root user in MariaDB, how would you do that?
  10. You have created a user "DBAUser" that you need to rename to "dba", how can you do that in MariaDB?
  11. How to provide only Select permission on a table in a databases in MariaDB?
  12. How would you remove all anonymous users?
  13. You need to create a user which can also grant the permissions on the objects which he/she has the permissions, how would you create the user who can also grant the permissions?





How to Check and Change Server Level Setting for MySQL or MariaDB by using Heidi SQL - MariaDB Tutorial

How to Check and Change Server Level Setting for MySQL or MariaDB by using Heidi SQL demo explains how you can explore different sever level and session level settings and update them.



How to check Global Variables values and set them in MySQL or MariaDB




How to Check Currently Running Queries / Processes on MySQL or MariaDB by using Heidi SQL- MariaDB Tutorial

How to Check Currently Running Queries  Processes on MySQL or MariaDB by using Heidi SQL

demo explain
How to check currently running query on MySQL or MairaDB in Heidi SQL
How to use PROCESSLIST table from information_schema database to get currently running database processes.


Get list of currently running queries on MySQL or MairaDB

How to get Query History in Heidi SQL for MySQL or MairaDB - MariaDB Admin / Dev Tutorial

How to get Query History in Heidi SQL for MySQL or MairaDB

demo explains

  • How to get Query History in Heidi SQL for MySQL or MariaDB
  • How to change retention period for Query History
  • Understand limitations with Query History in Heidi SQL

How to get Query History in Heidi SQL for MySQL or MairaDB

How to mark different objects such as tables views as Favorite in Heidi SQL for MySQL or MariaDB - MariaDB Admin / Dev Tutorial

How to mark different objects such as tables views as Favorite in Heidi SQL for MySQL or MariaDB


demo explains
how you can mark the objects such as tables, Stored Procedures, views etc. as favorite. This really help to get to objects list those you work on daily basis.


Mark objects favorite in Heidi SQL For MySQL or MairaDB to filter them quickly

How to make a copy of a table in MySQL or MariaDB by using Heidi SQL - MariaDB Admin / Dev Tutorial

How to make a copy of a tables in MySQL or MariaDB by using Heidi SQL


demo explains

  • How to copy a table from one database to another database in MySQL or MariaDB by using Heidi SQL
  • How to copy the table from a database to same database in MySQL or MairaDB by using Heidi SQL
  • How to remove columns ( Select columns) for copy table in MySQL or MairaDB by using Heidi SQL



How to make a copy of a table in MySQL or MariaDB by using Heidi SQL

How to generate script for entire database with Data for MySQL or MariaDB by using Heidi SQL - MariaDB DBA / Dev Tutorial

How to generate script for entire database with Data for MySQL or MariaDB by using Heidi SQL


demo explains
  • How to chose a database for which you would like to generate scripts in Heidi SQL for MySQL or MariaDB
  • How to choose objects such as tables etc. to generate script for them in Heidi SQL for MySQL or MariaDB
  • How to save the generate script in clipboard, or as .sql file in Heidi SQL for MySQL or MariaDB


How to generate script for entire database with Data for MySQL or MariaDB by using Heidi SQL

How to load large size sql file into MySQL or MariaDB without opening it by using Heidi SQL - MariaDB Admin / Dev Tutorial

How to load large size sql file into MySQL or MariaDB without opening it by using Heidi SQL


demo explains

  • What are the issues to open large .sql files with Heidi SQL Tool
  • How you can run the large .sql files without opening in Heidi SQL Editor
  • How to monitor the progress of execution of large .sql files with Heidi SQL

How to load large size  sql file into MySQL or MariaDB without opening it by using Heidi SQL

How to Synchronize two database in MySQL or MariaDB by using Heidi SQL- MariaDB Admin / Dev Tutorial

How to Synchronize two database in MySQL or MariaDB by using Heidi SQL

demo explains

  • How to synchronize two MySQL or MairaDB Databases by using Heidi SQL
  • What are different options available when synchronizing two databases such as Insert, Delete + Insert( Truncate first), No Data, Insert Ignore etc.
  • Different Items to consider before running the synchronizing the databases


How to Synchronize two database in MySQL or MariaDB by using Heidi SQL

Create User and Provide Permission by using User Manager in MySQL or MariaDB by using Heidi SQL - MariaDB Tutorial

How to Create User and Provide Permission by using User Manager in MySQL or MariaDB by using Heidi SQL 

demo explains below


  • How to open User Manager in Heidi SQL
  • How to check all users from MySQL or MariaDB by using Heidi SQL User Manager
  • How to create new User in MySQL or MariaDB by using User Manager by using Heidi SQL
  • How to set different settings for user such as Max Connections or Max Queries per hours by using User Manager in Heidi SQL


How to create new user by using User Manager in Heidi SQL for MySQL or MairaDB

What is Binary Logging and How to enable in MariaDB - MariaDB DBA / DEV Tutorial

Binary Log File:

The binary log file stored the data in binary fomrat. That makes it quicker for writting log information. The binary log file only capture Data Changing information, it is also used for replication.

How to Enable Binary Log : 

In case of Stand alone MariaDB, Make changes to /etc/my.cnf file

[mysqld]
log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=row

In case of Galera Cluster, make changes to /etc/my.cnf.d/server.cnf


How to read binary log files:

sudo mysqlbinlog binary-log-file-name


How to Enable Binary Log in MySQL or MairaDB

How to Export Results To JSON from MySQL or MariaDB by using Heidi SQL- MariaDB Tutorial

How to Export Results To JSON from MySQL or MariaDB by using Heidi SQL


demo explains

  • How to connect to Heidi SQL and open MySQL or MariaDB
  • How to choose the results for export to Json in MySQL or MariaDB
  • How to export grid results to JSON from MySQL / MariaDB by using Heidi SQL

How to export results to JSON from MySQL or MariaDB by using Heidi SQL

Export Results to CSV file from MySQL or MairaDB by using Heidi SQL - MariaDB Admin / Dev Tutorial

How to Export Results to CSV file from MySQL or MairaDB by using Heidi SQL 


demo explains

  • How to get different result sets those you need to export from MySQL or MariaDB by using Heidi SQL
  • How to export limited rows from MySQL or MairaDB by using Heidi SQL
  • How to export Grid results to different formats to CSV, HTML, JSON and others by using Heidi SQL from MySQL or MairaDB
  • How to choose line terminator, field terminator and quote enclosed for exported CSV file in Heidi SQL for MySQL or MariaDB

Export Results to CSV file from MySQL or MairaDB by using Heidi SQL

How to Import CSV file into MySQL or MairaDB Table by using Heidi SQL - MariaDB Admin / Dev Tutorial

How to Import CSV file into MySQL or MairaDB Table by using Heidi SQL


demo explains

  • How to study CSV file to create table first before importing CSV file to it in MySQL or MariaDB
  • How to choose different options such as ignore header, remove duplicate or ignore duplicate while importing CSV file to Table in MySQL / MariaDB by using Heidi SQL
  • How to collect right file Encoding to import CVS file into MySQL or MariaDB  Table by using Heidi SQL
  • How to choose field terminator, line terminator in Import Wizard while importing CSV file to MySQL / MariaDB by using Heidi SQL
How to import CSV file into MySQL or MariaDB Table

How to search for text in a table or databases or entire MySQL or MariaDB by using Heidi SQL

How to search for text in a table or databases or entired MySQL or MariaDB by using Heidi SQL


demos explains
  • How to find text string in a table in MySQL or MariaDB by using Heidi SQL
  • How to find text in a database in all tables in MySQL or MariaDB by using Heidi SQL
  • How to search for text on entire MySQL or MariaDB Serve by using Heidi SQL
  • How to use wildcards to search exact text, left and right wildcards
  • How to find Case sensitive or Case Insensitive in MySQL or MariaDB by using Heidi SQL
  • Search for Text in all columns in all tables in MySQL or MariaDB by using Heidi SQL


Search Text in all the tables in MySQL or MairaDB 

How to use SQL Help , Code Examples , Snippets , Templates in Heidi SQL - MariaDB Admin Tutorial

How to use SQL Help  , Code Examples , Snippets , Templates in Heidi SQL

 explains
  • How to use SQL Help to search for code examples in Heidi SQL for MySQL or MariaDB
  • How to find sample template to create objects such as tables, views, Stored procedures and triggers in Heidi SQL for MySQL / MariaDB

How to use SQL Help  , Code Examples , Snippets , Templates in Heidi SQL

How to create a table in MySQL or MariaDB by using Heidi SQL - MariaDB Admin / Dev Tutorial

How to create a table in MySQL or MariaDB by using Heidi SQL 

explains

  • How to connect to MySQL or MariaDB from Heidi SQL
  • How to install Heidi SQL for MariaDB
  • How to create table Name and add comments
  • How to add new columns to table in MySQL / MariaDB
  • How to choose data type for column in table for MariaDB
  • How to set column for Auto Increment and add Primary key to it in MariaDB Table
  • How to see the definition of Table in MariaDB by using Heidi SQL


How to create a table in MySQL or MariaDB by using Heidi SQL

How to create a Database in MySQL or MariaDB by using Heidi SQL - MariaDB Admin / Dev Tutorial

How to create a Database in MySQL or MariaDB by using Heidi SQL 

demo  explains
  • How to open Heidi SQL and Connect to MySQL or MariaDB Server
  • How to explore databases lists for MySQL or MairaDB
  • How to create new database by selecting different options such as collation for database in MariaDB by using Heidi SQL

How to create database in MySQL or MariaDB by using Heidi SQL

How to connect to MySQL or MariaDB from Heidi SQL - MariaDB Admin/Dev Tutorial

How to connect to MySQL or MariaDB from Heidi SQL

explains following topics

  • How to open Heidi SQL in windows machine
  • how to create a user in MySQL or MariaDB  that Heidi SQL will use to  connect to MySQL or MairaDB from another machine
  • How to Rename session in Heidi SQL
  • How to see databases information in Heidi SQL for MySQL or MariaDB

Connect to MySQL or MariaDB by using Heidi SQL

How to download and Install Heidi SQL for MySQL or MariaDB - MariaDB Admin / Dev Tutorial

Scenario:

You are working as MySQL or MariaDB DBA, you been working on command line to run most of your tasks done. Which free Graphical User Interface tool you can use to perform DBA and Developer task for MySQL or MariaDB?

Solution:

Heidi SQL is open source tool that  you can use to perform DBA and Development task. It has a lot of feature those help you to perform the job quickly. In below video, I have explains how to download Heidi SQL and Install on Windows Machine.


How to download and Install Heidi SQL for MySQL or MariaDB

How to find data with Carriage Return in string in SQL Server

Scenario:


You are working as SQL server DBA and you have a table Test with column Name , you need to find if Name column contain any data which has carriage return, how would you find string with carriage return in SQL Server?

Solution:

You can use below sql statement to find our carriage return in SQL Server.

Select Name From dbo.Test
where Name like '%'+char(13)+'%' or Name like '%'+char(10)+'%'

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 : 

Login to MySQL or MariaDB and run below queries to check the current settings of slow query logging

 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 

Login to MariaDB and execute below queries to check if query log is enabled and where the log file is located.

MariaDB [(none)]>  SHOW VARIABLES LIKE 'general_log';
MariaDB [(none)]>  SHOW VARIABLES LIKE 'general_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.

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 

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