How to install MySQL on Ubuntu

How to install MySQL on Ubuntu

Step 1 — Installing MySQL

To install it, update the package index on your server if you’ve not done so recently:

sudo apt update or apt update


Then install the mysql-server package:
sudo apt install mysql-server


Ensure that the server is running using the systemctl start command:
sudo systemctl start mysql.service


Step 2 — Configuring MySQL

sudo mysql

Then run the following ALTER USER command to change the root user’s authentication method to one that uses a password. The following example changes the authentication method to mysql_native_password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
exit

Following that, you can run the mysql_secure_installation script without issue.
sudo mysql_secure_installation

This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options. The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the password strength of new MySQL users before deeming them valid.

If you elect to set up the Validate Password Plugin, any MySQL user you create that authenticates with a password will be required to have a password that satisfies the policy you select:

Output:

Securing the MySQL server deployment.
Enter password for user root:

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.
Estimated strength of the password: 25
Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y
New password:
Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.
All done!

$ mysql -u root -p

Then go back to using the default authentication method using this command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

This will mean that you can once again connect to MySQL as your root user using the sudo mysql command.


Step 3 — Creating a Dedicated MySQL User and Granting Privileges

sudo mysql

Note: If you installed MySQL with another tutorial and enabled password authentication for root, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will only gain administrator privileges within the database by authenticating:

mysql -u root -p

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

Run the following command to create a user that authenticates with caching_sha2_password. Be sure to change sammy to your preferred username and password to a strong password of your choosing:

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

Note: There is a known issue with some versions of PHP that causes problems with caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead:

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

If you aren’t sure, you can always create a user that authenticates with caching_sha2_plugin and then ALTER it later on with this command:

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

After creating your new user, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

Run this GRANT statement, replacing sammy with your own MySQL user’s name, to grant these privileges to your user:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.*

Note that this statement also includes WITH GRANT OPTION. This will allow your MySQL user to grant any permissions that it has to other users on the system

Warning: Some users may want to grant their MySQL user the ALL PRIVILEGES privilege, which will provide them with broad superuser privileges akin to the root user’s privileges, like so:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Such broad privileges should not be granted lightly, as anyone with access to this MySQL user will have complete control over every database on the server.

Following this, it’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:

FLUSH PRIVILEGES;
Then you can exit the MySQL client:

exit

In the future, to log in as your new MySQL user, you’d use a command like the following:

mysql -u sammy -p

The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.

Finally, let’s test the MySQL installation.

Step 4 — Testing MySQL

Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status.

systemctl status mysql.service

The output will be similar to the following:

Output

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2022-04-11 16:04:39 UTC; 2h 36min ago
Process: 2593 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 2601 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 1119)
Memory: 354.3M
CPU: 19.944s
CGroup: /system.slice/mysql.service
└─2601 /usr/sbin/mysqld


If MySQL isn’t running, you can start it with sudo systemctl start mysql.

Source/Reference:

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04

Granting a user in MySQL

Granting a user in MySQL

Examples on GRANT command

grant all privileges on *.* to 'user_name'@'localhost';

grant all privileges on *.* to 'user_name'@'%';

grant all privileges on *.* to 'user_name'@'localhost' with grant option;

grant all privileges on *.* to 'user_name'@'%' with grant option;

grant all privileges on dbname.* to 'user_name'@'localhost';

grant all privileges on dbname.* to 'user_name'@'%';

grant insert,update,delete on table dbname.tablename to 'user_name'@'localhost';

grant insert,update,delete on table dbname.tablename to 'user_name'@'%';

grant create on database.* to 'user_name'@'localhost';

grant create on database.* to 'user_name'@'%';

grant select , execute on database.* to 'user_name'@'%';

grant select , execute on database.* to 'user_name'@'localhost'; 

How to convert a user In MySQL

How to convert a user In MySQL

Make sure you are connected with root/admin user:

mysql> select current_user();

Create normal user if not created.

mysql> CREATE user 'super'@'%' IDENTIFIED BY 'super';

mysql> CREATE user 'super'@'localhost' IDENTIFIED BY 'super';

Grant privileges for making it super user:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' WITH GRANT OPTION;

Reload all the privileges

mysql> flush privileges;

View the grants:

mysql> show grants for super; 

MySQL User Management

MySQL User Management 

-- List all users present in mysql

mysql> select user,host,account_locked,password_expired from mysql.user;

Create user in mysql:

mysql> create user 'mqmtest_user'@'%' identified by 'mqmtest_user';

mysql> GRANT ALL ON *.* TO 'mqmtest_user'@'localhost';

View already created users:

mysql> select host, user from mysql.user;

Drop user:

mysql> drop user 'mqmtest_user'@'localhost';

Rename a user:

mysql> rename user 'mqmtest_user3' to 'mqmtest_user007';

change password of a user:

mysql> ALTER USER 'dbaprod'@'%' IDENTIFIED BY 'dbaprod';

Change resource option:

mysql> alter user 'dbaprod'@'%' with MAX_USER_CONNECTIONS 10;

Lock/unlock an account:

mysql> alter user 'dbaprod'@'%' account lock;

mysql> alter user 'dbaprod'@'%' account unlock; 

MySQL General Queries

MySQL General Queries

-- Drop database:

mysql> drop database qtest1;

mysql> show databases like 'information_schema%';

mysql> SELECT schema_name FROM information_schema.schemata;

How to get MySQL / MariaDB db size:

mysql> select table_schema as "database", sum(data_length + index_length) / 1024 / 1024 / 1024 as "size (gb)" from information_schema.tables group by table_schema;

mysql> select table_schema as "database", sum(data_length + index_length) / 1024 / 1024 as "size (mb)" from information_schema.tables group by table_schema;

How to find timezone info

Check whether time_zone table is updated or not.

mysql> select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp()), 'GMT', 'Europe/Dublin'), '%Y%u');

mysql> select @@global.time_zone, @@session.time_zone;

How to check the time and date?

mysql> select now();

Below commands can be used to find MySQL / MariaDB version:

mysql> select version();

OS

#[root@mqmopensource ~]# mysql --version

mysql> show variables like "%version%";

show all processes in mysql cluster.

mysql> show processlist;

show processes for a specific user:

mysql> select * from information_schema.processlist where user='root'\G;

Show processes for a particular database:

mysql> select * from information_schema.processlist where DB='information_schema';

Get the processid for the session:

mysql> show processlist;

Uptime of server

mysql> status;

Alternative command

mysql> \s

Server startup time:

mysql> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';

How to check the data directory:

mysql> show variables like '%data%';

mysql> select variable_value from information_schema.global_variables where variable_name = 'datadir';

mysql> select @@datadir;

Find current data/time on MySQL / MariaDB

mysql> select current_timestamp,current_date,localtime(0),localtimestamp(0);

Find MySQL / MariaDB configuration values

mysql> select variable_name,variable_value from information_schema.global_variables;

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name = 'port';

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name = 'datadir';

2. Alternatively you can check my.cnf file in /etc folder in Linux

cat /etc/my.cnf

Find the last MySQL / MariaDB Database service restarted / server reboot time

mysql> status;

-- In older version:

mysql> select time_format(sec_to_time(variable_value ),'%hh %im') as uptime from information_schema.global_status where variable_name='uptime';

-- In latest mysql version:

mysql> show global status like 'uptime';

mysql> show engines;

If the valid backup file is available with you, then you can restore the same using below command.

mysqldump -u root -p classicmodels < mysqldump.sql

-- If  dbdump.sql is the backup file , then restore command will be 

[mysql@localhost]# mysqldump –u root –pmysql sampledb > dbdump.sql

-- Backup multiple databases

For example, if you want to take backup of both sampledb and newdb database, execute the mysqldump as shown below:

[root@localhost]# mysqldump –u root –p --databases sampled newdb > /tmp/dbdump.sql

We cannot take backup of information_schema and performance_schema databases as these are metadata databases.

-- Backup all databases:

root# mysqldump -u root -p --all-databases > all-database.sql

Backup a specific table

SYNATX:

mysqldump –c –u username –p databasename tablename > /tmp/databasename.tablename.sql

In this example, we backup only the ta2 table from sampledb database.

root$ mysqldump –u root –p sample ta2 > /tmp/nwedb_ta2.sql

Restore all databases

[root]$ mysql -u root -p < /tmp/alldbs55.sql

Backup databases in compress format

With bzip2:

mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 

With gzip:

mysqldump --all-databases | gzip> databasebackup.sql.gz  

How to connect to MySQL / MariaDB database

How to connect to MySQL / MariaDB database

hostname$ export PATH=/usr/local/mysql/bin:$PATH

hostname$ which mysql

/usr/local/mysql/bin

SYNTAX - mysql -u user -p

C:\Program Files\MariaDB 10.4\bin>mysql -u root -p

Enter password: ****

Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 19.Server version: 10.4.18-MariaDB 

Find current connection info:

mysql> \s

Below is the alternative command:

mysql> status;

Get current user and current database:

mysql> select current_user,database();

switch to another database:

mysql> use qtest1;

Database changed

mysql> select database(); 

Database Management In MySQL?

Database Management In MySQL?

Create a database in MySQL / MariaDB

-- Below commands can be used to create database

mysql> CREATE DATABASE QTEST;

Query OK, 1 row affected (0.00 sec)

mysql> create database qtest1 character set UTF8mb4 collate utf8mb4_bin;

Query OK, 1 row affected (0.00 sec)

-- View the create database statement used for creating db 

mysql> SHOW CREATE DATABASE qtest1;

View database list

mysql> show databases;

mysql> select * from information_schema.schemata;

mysql> show databases like 'QTEST%';

 

Backups and Recovery in MYSQL ?

Backups and Recovery in MYSQL ?

1. Physical backup (cold backup/offline backup)

Taking backup of physical files.

2. Logical backups (online backups)

Taking backup of logical objects

3. Incremental backups ( we can perform this on only logical backups only)

Note:

Take backup using MYSQLDUMP utility.

Restore using MYSQLIMPORT or MYSQL utility.

1. Physical backup (cold backup/offline backup)

-Stop the mysql services

service mysqld stop

-Copy the datadir and mysql config file to different locations

select @@datadir;

find / -name *cnf*

find / -name *.cnf*

cp -rf /var/lib/mysql/auto.cnf /root/mysqlcoldbackup_16042022/

cp -rf /var/lib/mysql /root/mysqlcoldbackup_16042022/mysqlbkp

cp -rfv /var/lib/mysql /root/mysqlcoldbackup_16042022/mysql$(date +%s)

service mysqld start

2. Logical backups (online backups)

To check the databases:

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| QTEST              |

| classicmodels      |

| mysql              |

| performance_schema |

+--------------------+

5 rows in set (0.00 sec)

Database Level:

Backup a single database:

mysqldump -u root -p mysql > mysqldump.sql

Backup multiple databases:

mysqldump -u root -p --databases mysql classicmodels > multidatabasesdump.sql

Backup all databases:

mysqldump -u root -p --all-databases > all_databases.sql

Compressed MySQL Database Backup:

mysqldump -u root -p mysql | gzip > mysql.sql.gz

Backup with timestamp option:

mysqldump -u root -p mysql > mysql-$(date +%Y%m%d).sql

Table Level:

Backup a single table:

mysqldump -u root -p mysql servers > singletable.sql

Backup a multiple tables:

mysqldump -u root -p mysql servers db user > multi_table.sql

Restore Commands:

mysql> drop database mysql;

Query OK, 28 rows affected, 2 warnings (0.05 sec)

mysql> use mysql

ERROR 1049 (42000): Unknown database 'mysql'

mysql> create database mysql;

Query OK, 1 row affected (0.00 sec)

Restoring a database using backups:

[root@mqmopensource ~]# mysql -u root -p mysql > mysqldbkp.sql------> using same database backup dump

[root@mqmopensource ~]# mysql -u root -p mysql < all_database.sql  ------> using all database backup dump

mysql> use mysql

Database changed

mysql> show tables;

Empty set (0.00 sec)

Restore Command:

mysql -u root -p mysql < mysqldbkp.sql

mysql> use mysql

mysql> show tables;

To restore a single table from full/db backups in MYSQL:

mysql> use mysql;

mysql> show tables;

| user

| columns_priv              

| db                        

| event                     

| func                      

| general_log   

mysql> drop table user;

Query OK, 0 rows affected (0.00 sec)

MySQL Important Logfiles

MySQL Important Logfiles 

1. Error log file:

During instalation time this file is created , startup & shutdown, hang, maintenance related details are recorded.

show variables like '%log%';

log_error | /var/log/mysqld.log

2. General query log file:

Query related issues, instert, update, delete statements are recorded here.

Bydefault this log is not enabled.

show variables like '%log%';

general_log       | OFF                                   

general_log_file  | /var/lib/mysql/mqmopensource.log  

set global general_log=on/off;

3. Binary log file (STATIC VALUE):

Very useful for auditing like how many records are updated/deleted.

It records changes made to the database.

show variables like '%log%';

log_bin | OFF

log_bin_basename 

set global log_bin=on/off;

show binary logs;

flush logs;

4. Slow query log:

This is also very useful for slow query performance logs

slow_query_log      | OFF                                   |

slow_query_log_file | /var/lib/mysql/mqmopensource-slow.log |

set global slow_query_log=on/off;

show variables like '%time%';

long_query_time | 10.000000 (Seconds) 

Check data file location and tablespaces in MariaDB or MySQL

Check data file location and tablespaces in MariaDB or MySQL

Check size and location of data files or tablespaces present in MySQL or MariaDB

Check innodb_file_per_table parameter.

innodb_file_per_table=ON, InnoDB uses one tablespace file per table.

innodb_file_per_table=OFF, InnoDB stores all tables in the InnoDB system tablespace.

show variables like 'innodb_file_per_table'

Check the location of datafile/tablespace present in MySQL or MariaDB:

-- On MySQL

show variables like 'datadir';

Variable_name|Value                        

-------------+------------------------+

datadir      |/var/lib/mysql/|

Check files and tablespace for MySQL:

select * from information_schema.FILES;

Check files and tablespace detail for MariaDB:

select * from information_schema.innodb_sys_tablespaces; 

Check the Version of MariaDB / MySQL

Check the Version of MariaDB / MySQL

The first way to open a Command prompt and connect with MariaDB will show the version as follows:

C:\WINDOWS\system32>mysql -u root -p

Enter password: ********

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 14

Server version: 10.6.5-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();

Check used & available maximum connection in MySQL

Check used & available maximum connection in MySQL

Check maximum available connection in MySQL

mysql> show global variables like 'max_connections';

Check used connection in MySQL

mysql> show global status like 'max_used_connections';

Check connected session list or Process list in MySQL

select id,user,host,db,command,time,state,info from information_schema.processlist; 

Create structure, duplicate & back up the table in MySQL

Create structure, duplicate & back up the table in MySQL

Use of CTAS for creating the Duplicate table with data or only structure (by using where 1 = 0) in the following database as follows:

MySQL: Use CTAS Syntax:

-- Create structure only

create table test_table_new like test_table;

-- Insert data into backup table

insert test_table_new select * from test_table;

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

Kill idle connection in MySQL

Kill idle connection in MySQL

Login with root user:

Check the session is in sleep state and get kill command for release the sessions:

select count(*) from information_schema.processlist where Command='Sleep';

select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';

concat('KILL ',id,';')|

----------------------+

KILL 10;              |

KILL 9;               |

Execute the command to Kill the session got from upper query output.

KILL 10; 

Automatic Clean the idle Connection in MariaDB/MySQL:

Interactive operation: this means opening the MySQL client on your local computer and doing various SQL operations on the command prompt.

INTERACTIVE_TIMEOUT is used to automatically clean the interactive connection in MariaDB/MySQL. The number of seconds the server waits for activity on an interactive connection before closing it

Non-interactive: means calls from the program. Example Tomcat Web service call to the database server through JDBC to connect etc.

WAIT_TIMEOUT is used to automatically clean the idle connection in MariaDB/MySQL.

The number of seconds the server waits for activity on a connection before closing it.

Note: Default time for both is 28800 seconds i.e. 8 hours

Check the Variables Values:

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

Variable_name           |Value|

------------------------+-----+

wait_timeout            |28800|

show global variables like '%interactive_timeout%';

Variable_name      |Value|

-------------------+-----+

interactive_timeout|28800|

Change the time limit to 30 minutes for idle connection timeout: The value used in both variables is in Seconds i.e. 30 min – 1800 seconds or 1 hour – 3600 seconds.

SET GLOBAL wait_timeout=1800;

SET GLOBAL interactive_timeout=1800;

You can also change to the Default value:

SET GLOBAL wait_timeout=DEFAULT;

SET GLOBAL interactive_timeout=DEFAULT;

Find the dependence on Table in MYSQL / MariaDB

Find the dependence on Table in MYSQL / MariaDB

Check the Procedures and functions depending on the table:

select a.routine_name,b.table_name,a.routine_schema,a.routine_type from information_schema.routines a inner join (select table_name , table_schema from information_schema.tables ) b on a.routine_definition like concat('%',b.table_name,'%') where  b.table_schema = 'dbname'  and b.table_name = 'test_table' ;

Check the views dependence on the table.

select * from information_schema.views where table_schema='mqm1' and table_name = 'test_table';

  

  

Check the foreign key constraints depend on the table.

SELECT Constraint_Type ,Constraint_Name ,Table_Schema ,Table_Name FROM information_schema.table_constraints

WHERE Table_Schema ='dbname' AND Table_Name = 'tablename' and Constraint_Type = 'FOREIGN KEY';

Start and Stop and restart the MariaDB or MySQL on Linux

Start and Stop and restart the MariaDB or MySQL on Linux

Check status for the MariaDB or MySQL

ps -ef|grep mysql

Check status for the MariaDB or MySQL

service mysqld status

Service mysql status

/etc/init.d/mysql status

/etc/init.d/mysqld status

Start the MariaDB or MySQL in Linux Environment:

service mysqld start

service mysql start

/etc/init.d/mysql start

/etc/init.d/mysqld start

Stop the MariaDB or MySQL in Linux Environment

service mysqld stop

service mysql stop

/etc/init.d/mysql stop

/etc/init.d/mysqld stop

Restart the MariaDB or MySQL Services in Linux Environment

service mysqld restart

service mysql restar

/etc/init.d/mysql restart

/etc/init.d/mysqld restart

Trace the SQL Queries in MYSQL

Trace the SQL Queries in MYSQL

For trace, the Queries executing from the application side in MariaDB provides a general_log parameter for enabling and disabling the traces.

Check status of General Log

mysql> select @@general_log;

For Enable the trace of SQL Queries:

mysql> set global general_log=1;

mysql> select @@general_log;

For Disable the trace of SQL Queries:

set global general_log=0;

Check and change the general log output as FILE or TABLE

-- Check the log file output format:

mysql> select @@log_output;

Check the file location:

mysql> select @@general_log_file;

--Change the log file format to TABLE:

SET GLOBAL log_output='TABLE';

Check the table for Log generation or SQL traces:

select * from mysql.general_log;

--Empty the table if no need

truncate table mysql.general_log;