Storing MySQL Authentication Credentials

mysql_config_editor:⭐️

• we can create encrypted file that contain root credential , so root doesn’t have to fill password each time it connect to MySQL
• mysql_config_editor is one of MySQL executable programmes that configure, it configure the authentication information in a hidden file of the current user’s directory.
• the hidden file name is .mylogin.cnf
• so when we invoke a client program like MySQL to connect to the server client actually uses this .mylogin.cnf to read the crentials .
• syntax: mysql_config_editor set-login=client --host=user=--password
• and by default client reads the

  and [mysql] group

Using mysql_config_editor

reference docs
To configure a MySQL account to store credentials in an encrypted format, allowing users to log in without entering a password, use the following syntax:
mysql_config_editor set --user=root --login-path=client --password.

To explore what can be done and to understand the available options, use the help command with:
mysql_config_editor set --help.

there are option such as specify which host can user login without needing to entering the password .
will start by configuring file for root user the command as below

mysql_config_editor set --user=root --login-path=client --password
the command will ask you to put the root password for MySQL

to confirm that mysql_config_editor has created the password file we can use the below command
mysql_config_editor print

now to connect we don’t need to use the following mysql -uroot -p
we will simply just type mysql and it should connect directory .

MySQL admin program

The MySQL admin client is a utility for performing various administrative tasks on a MySQL server. With it, you can execute several operations, such as:

• Shutting down the MySQL server.
• Creating or dropping a database with create [database name] or drop [database name].
• Checking the current status of the server.
• Pinging the server to verify if it’s running.
• Starting or stopping replication.

The syntax to use MySQL admin is: mysqladmin [options] [command].
After configuring the root user’s credentials with mysql_config_editor, it’s no longer necessary to use -uroot -p to authenticate. Instead, you can directly use mysqladmin [command].
example:

• mysqladmin status
• mysqladmin ping
• mysqladmin create database
• mysqladmin drop database

using MySQL admin program

Before using mysqladmin to create a database, it’s a good idea to first explore all available options with the -help command to familiarize yourself with mysqladmin functionalities.
mysqladmin --help

for example we can get the current version of MySQL server
mysqladmin version

we can use mysqladmin status to check if MySQL server is up or not

Wanna play ping-pong with your MySQL server to see if it’s awake? Just serve a mysqladmin ping and see if it hits back. It’s the quickest way to check if your MySQL server is up and ready to volley!

now we will use mysqladmin for our main task which is to create database
mysqladmin create test1

MYSQL Execute SQL Files

In this section, we’ll cover how to execute SQL files on a MySQL server, a common task for MySQL DBAs. Typically, you might be given an SQL file that contains statements for creating databases, users, tables, etc. The key requirement is that the .sql file should contain only SQL statements executable in the MySQL shell. Here are four main methods for executing an .sql file:

1. Source: Inside the MySQL shell, use the source command or \. shortcut.
   Syntax: mysql> \. file.sql or mysql> source file.sql
2. mysql Command: Execute the file by using the mysql client program, specifying the database and credentials.
   Syntax: mysql --host=hostname --user=username --password=your_password database_name < file.sql
3. Shell Script: Create an executable shell script that runs the SQL file.
   Example: mysql --host=hostname database_name < $1
4. Pipe Method: Use the cat command to display the contents of your SQL file, and then pipe it (|) directly into MySQL.
   Syntax:

cat file.sql | mysql

executing SQL Files in MYSQL

reference link

we will use employee.sql file and used wget to download it directly on the OS
wget https://github.com/datacharmer/test_db/archive/refs/heads/master.zip

next we will unzip the file
unzip master.zip

inside test_db-master you should find a file called called employees.sql that we will use

if use cat to read content of the file you will find SQL statement for creating table and inserting value
the file will also create database call employees

Execute using source

To execute the file, first log into MySQL. Switch to the employees database by running use employees. Next, execute the employees.sql file with the command:
source employees.sql.

Execute using mysql Command

For our second method, we’ll leverage the MySQL client program. This approach involves specifying the host and directing the contents of the SQL file into the MySQL server for a given database. Here’s how you do it:

Use the command mysql --host=localhost employees < employees.sql. This instructs the MySQL client to connect to the MySQL server on localhost, target the employees database, and execute the SQL statements contained within the employees.sql file. This method is efficient for applying a series of SQL commands stored in a file directly to your database.

mysql --host=localhost employees < employees.sql

Execute using SHELL SCRIPT

For our third method, we’ll craft a shell script, a handy approach for those who frequently execute .sql files, as it streamlines the process. Here’s the step-by-step guide:

1. Create a Shell Script: Begin by making a new .sh file named employees.sh. This file will contain all the necessary commands to execute your .sql file.
2. Script Content: Open your script file and insert the following command:
   

   mysql --host=localhost employees < $1

   This line tells the script to run the mysql command, connect to the local MySQL server, select the employees database, and execute the SQL commands from the file specified as the first argument to the script.

3. Make it Executable: To allow your script to run, you need to modify its permissions to make it executable. Use the command:
   

   chmod u+x employees.sh

4. Executing the Script: Finally, to run your script along with the .sql file, use:
   

   bash employees.sh employees.sql

   full command

vi employees.sh
msyql --host=localhost employees < $1
chmod u+x employees.sh
bash employees.sh employees.sql

Execute using pipe method

The fourth method is the simplest of all. Simply use cat to display the contents of your SQL file, and then pipe it (|) into MySQL for direct execution. This straightforward approach requires just a single line:

cat file.sql | mysql

This command concatenates the file’s content and directs it straight into MySQL, allowing the SQL statements within the file to be executed seamlessly.

cat employees.sql | mysql

Executing SQL Commands From Terminal 🌟

This section will cover executing SQL commands directly from the MySQL client program without logging into the MySQL shell, utilizing the -e option.
example : mysql -e ''select @@hostname , @@version ''

Importing data with mysqlimport

mysqlimport is a utility for importing data from text files into a MySQL table. The syntax allows specifying multiple text files as input, facilitating the loading of data directly into the database.
Here’s how the syntax looks:

mysqlimport [options] database file1.txt [file2.tx]...

note : the txt file name must be the same as table name that we want to import data to

using mysqlimport

reference link
i have this file called staff,txt , i will use cat to view the content of the file it has four rows

lets see if the staff table is there and if there are any existing rows

now lets’ import the data in using mysqlimport

mysqlimport employees staff.txt

The mysqlimport command encountered an issue due to the --secure-file-priv variable, which restricts where you can load files from. To find out the directory this variable points to, log into MySQL and query the variable using the LIKE operator:

SHOW VARIABLES LIKE 'secure_file_priv';

The secure_file_priv variable specifies the directory from which files can be loaded. To comply, you’ll need to move your staff.txt file into the designated path defined by secure_file_priv. You can do this by using the mv command in your operating system’s shell. Alternatively, you can execute the mvcommand directly from within MySQL by prefixing it with \!. This allows you to run system commands from the MySQL prompt.

no rerun the command by putting the new path of staff.txt file

mysqlimport employees /var/lib/mysql-files/staff.txt

The issue stems from the structure of the staff table, which doesn’t permit null values for the id column, and it exclusively accepts integers. Running DESC staff; in MySQL would reveal that the id column is set to disallow nulls and is configured to accept only integer values, aligning with its role as a primary key that auto-increments. This setup ensures data integrity by preventing null or non-integer values from being inserted into the id column, which could otherwise lead to database inconsistencies.

The staff.txt file attempts to insert ‘john’ into the id column, which only accepts integer values. To resolve this, you need to skip the first field during the import process. Check the mysqlimportdocumentation or use mysqlimport --help to see if there’s an option to specify or exclude specific column names during import.

-c, --columns=name allow us to specify column we want to import data to.

mysqlimport --columns=fname,lname,title,isActive employees /var/lib/mysql-files/staff.txt

Maintaining Integrity with mysqlcheck

After importing data into a table and executing .sql files against a database, how can we ensure the integrity of the data? That’s where the mysqlcheck utility becomes crucial.

• mysqlcheck serves as a table maintenance tool, offering functionalities to check, repair, optimize, and analyze tables.
• It examines tables for errors and attempts to fix any issues encountered, requiring the name of the table as input.
• Important note: Tables undergoing a mysqlcheck operation are locked, meaning no other database operations can be performed on them during this time. It’s advisable to run mysqlcheck during maintenance windows to avoid disrupting database access.
  syntax:
  mysqlcheck [options] db_name table_name

how to use mysqlcheck

reference link

as mentioned before to use mysqlcheck as usual check the --help to see other options

mysqlcheck --help

to use mysqlcheck is straight forward

mysqlcheck employees staff

Displaying useful Information with mysqlshow

As a DBA, you might often be asked for information like the number of tables in a specific database, the columns within a particular table, or the data types of those columns. In MySQL, there’s a convenient utility called mysqlshow that can help you with these requests. This tool is designed to display details about databases, tables, and columns. You can use it to:

• Show information about databases, tables, and their columns.
• Accept both database and table names as inputs to fetch specific details.

The syntax to use mysqlshow is as follows:

To display information about a table in a database:

mysqlshow [options] db_name table_name

To get more detailed information about specific columns within a table:

mysqlshow [options] db_name table_name [column_name]

using mysqlshow

will will use mysqlshow how many tables in employees database and print out staff table information

first as always we will view the help option to see the deferent options

mysqlshow --help

first we will display all table in employees database

mysqlshow employees

next we will display about column in staff table

mysqlshow employees staff

finally will will display the column [id] information

mysqlshow employees staff id

Time Zone Tables

In many applications that require handling time zones, it becomes necessary for the backend database, such as a MySQL server, to manage different time zone data efficiently.

MySQL includes a utility specifically for this purpose, named mysql_tzinfo_to_sql. This tool is designed to import time zone data from the zoneinfo database, which is a collection of files describing various time zones, typically found in /usr/share/zoneinfo on Linux systems, into the MySQL system database.

Time Zone Tables in MySQL:
The MySQL system database includes several tables that store time zone information:

1. Time_zone
2. Time_zone_name
3. Time_zone_transition
4. Time_zone_transition_type
5. Time_zone_leap_second

To load the time zone data into MySQL, use the following syntax:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql [options] db_name

This command reads the zoneinfo database and pipes its SQL representation directly into the MySQL database specified, allowing MySQL to handle time zone information more accurately for applications that rely on it.

using mysql_tzinfo_to_sql

Before we begin, it’s crucial to verify the existence of the zoneinfo database directory on the operating system. Check if the path /usr/share/zoneinfo is present by using the command:

ls /usr/share/zoneinfo

With the confirmation that the path /usr/share/zoneinfo exists and contains the time zone data files, you’re all set to proceed with loading this data into your MySQL server. To do this, you’ll use the mysql_tzinfo_to_sql utility. This command reads the time zone information from /usr/share/zoneinfo and pipes it into the MySQL system database. Here’s how you run the utility:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Remember to replace -u root -p with the appropriate username and password options for your MySQL server if necessary. This command will load the time zone data into the MySQL system database, ensuring your MySQL server can handle time zone conversions accurately.

the command will skip some files .taband .zi these are unnecessary in this case
the command didn’t output any error
let’s go login to MySQL and change to MySQL database and see if the data is loaded on the table we discussed before .

let’s see if one of table got data populated

select count(*) from time_zone;

Listing Binary Logs Events with mysqlbinlog

The mysqlbinlog utility is designed for reading binary log files in MySQL. Here’s a closer look:

• The binary log file is where any database changes are logged by the server.
• These changes are recorded as events within the binary log, and the log itself is written in a binary format hence their written in encrypted form we cannot display them .
• To convert and view these logs in a readable, plain text format, the mysqlbinlog utility comes into play.

Syntax examples include:

• To list binary logs: SHOW BINARY LOGS;
• To show events from a specific binary log file: SHOW BINLOG EVENTS IN 'binary_log_file';
• And to read a binary log file with mysqlbinlog:

mysqlbinlog [options] binary_log_file

using mysqlbinlog and reading the bin-log

in this senario we will drop a database in check if this cause event in binary log file

let’s first display how many binary log files we have in the system

SHOW BINARY LOGS;

Given that there are 9 binary logs, with the assumption that the most recent activities, such as a DROP statement, are in the last log, we’ll use mysqlbinlog to inspect this. Specifically, we’ll target the binary log file named 1.000009. The command will look something like this:

sudo mysqlbinlog /mysqldata/mysql/1.000009 > events.log

Here, > events.log directs the output into a file named events.log, which allows for easy reading of the contents.

It’s important to note that in this instance, the MySQL data directory is located at /mysqldata/mysql/, diverging from the default location, which is typically /var/lib/mysql/. This customization means the binary log files are stored in the specified, non-standard directory.

To explore the contents of the events.log file, you can use the cat command. However, if you’re specifically looking for actions such as a DROP statement within the file, employing the grep command with the -i option for case-insensitive search is more efficient. Here’s how you can filter the output to find occurrences of “DROP”:

grep -i DROP events.log