Ask Me

Showing posts with label MYSQL. Show all posts
Showing posts with label MYSQL. Show all posts

Post MySQL Installation generic Queries

 


1. Connect mysql instance.

[root@xcell ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>



2. Go for password less login for root.


Create file .my.cnf under home directory of root.



[root@xcell ~]# cat .my.cnf
[client]
password = XXXXXXXXXX
socket = /u01/mysql/mysql-5.7.28-el7-x86_64/mysql.sock
[root@xcell ~]#

[root@xcell ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>


3. See databases or schema resdes under mysql server



mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |---- all db object info etc
| mydb |
| mysql |---- Default database store all user db info
| performance_schema |---- gather performance data
| sys |
+--------------------+
5 rows in set (0.03 sec)




4. Select database for operation



mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb |
+------------+
1 row in set (0.00 sec)


mysql>


5. See tables under mydb database



mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| CORONA |
| DEPT |
| state_wise |
| travel_history |
+----------------+
4 rows in set (0.00 sec)


mysql>


6. see all connection details;




mysql> show processlist;
+----+---------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+-----------+------+---------+------+----------+------------------+
| 4 | oggadm1 | localhost | mydb | Sleep | 1739 | | NULL |
| 5 | oggadm1 | localhost | NULL | Sleep | 1740 | | NULL |
| 9 | root | localhost | mydb | Query | 0 | starting | show processlist |
+----+---------+-----------+------+---------+------+----------+------------------+
3 rows in set (0.10 sec)





[root@xcell ~]# mysqladmin processlist -p

Enter password:

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

| Id | User | Host | db | Command | Time | State | Info |
+----+---------+-----------+------+---------+------+----------+------------------+
| 4 | oggadm1 | localhost | mydb | Sleep | 1871 | | |
| 5 | oggadm1 | localhost | | Sleep | 1872 | | |
| 15 | root | localhost | | Query | 0 | starting | show processlist |
+----+---------+-----------+------+---------+------+----------+------------------+
[root@xcell ~]#


7. See status of server





[root@xcell ~]# mysqladmin status

Uptime: 6256 Threads: 3 Questions: 134 Slow queries: 0 Opens: 157 Flush tables: 1 Open tables: 150 Queries per second avg: 0.021

8. Server version





[root@xcell ~]# mysqladmin version
mysqladmin Ver 8.42 Distrib 5.7.28, for el7 on x86_64
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.7.28-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /u01/mysql/mysql-5.7.28-el7-x86_64/mysql.sock
Uptime: 1 hour 45 min 41 sec
Threads: 3 Questions: 138 Slow queries: 0 Opens: 157 Flush tables: 1 Open tables: 150 Queries per second avg: 0.021
[root@xcell ~]#





mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.28, for el7 (x86_64) using EditLine wrapper
Connection id: 17
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.28-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /u01/mysql/mysql-5.7.28-el7-x86_64/mysql.sock
Uptime: 1 hour 46 min 4 sec
Threads: 3 Questions: 143 Slow queries: 0 Opens: 157 Flush tables: 1 Open tables: 150 Queries per second avg: 0.022
--------------
mysql>



[root@xcell ~]# mysqladmin -u root -p ping
Enter password:
mysqld is alive
[root@xcell ~]#

8. Find connected user details 


mysql> select Host,user,account_locked,plugin from mysql.user;
+-----------+---------------+----------------+-----------------------+
| Host | user | account_locked | plugin |
+-----------+---------------+----------------+-----------------------+
| localhost | root | N | mysql_native_password |
| localhost | mysql.session | Y | mysql_native_password |
| localhost | mysql.sys | Y | mysql_native_password |
| % | deepak | N | mysql_native_password |
| localhost | ogguser1 | N | mysql_native_password |
| localhost | oggadm1 | N | mysql_native_password |
+-----------+---------------+----------------+-----------------------+
6 rows in set (0.00 sec)


mysql>



mysql> select Host,Db,User from mysq.db;
+-----------+--------------------+---------------+
| Host | Db | User |
+-----------+--------------------+---------------+
| localhost | performance_schema | mysql.session |
| localhost | sys | mysql.sys |
+-----------+--------------------+---------------+
2 rows in set (0.00 sec)




10. Show available engines.

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>


11. See current datafiles.



mysql> select * from information_schema.INNODB_SYS_DATAFILES;
+-------+---------------------------------------+
| SPACE | PATH |
+-------+---------------------------------------+
| 2 | ./mysql/plugin.ibd |
| 3 | ./mysql/servers.ibd |
| 4 | ./mysql/help_topic.ibd |
| 5 | ./mysql/help_category.ibd |
| 6 | ./mysql/help_relation.ibd |
| 7 | ./mysql/help_keyword.ibd |
| 8 | ./mysql/time_zone_name.ibd |
| 9 | ./mysql/time_zone.ibd |
| 10 | ./mysql/time_zone_transition.ibd |
| 11 | ./mysql/time_zone_transition_type.ibd |
| 12 | ./mysql/time_zone_leap_second.ibd |
| 13 | ./mysql/innodb_table_stats.ibd |
| 14 | ./mysql/innodb_index_stats.ibd |
| 15 | ./mysql/slave_relay_log_info.ibd |
| 16 | ./mysql/slave_master_info.ibd |
| 17 | ./mysql/slave_worker_info.ibd |
| 18 | ./mysql/gtid_executed.ibd |
| 19 | ./mysql/server_cost.ibd |
| 20 | ./mysql/engine_cost.ibd |
| 21 | ./sys/sys_config.ibd |
| 29 | ./mydb/CORONA.ibd |
| 296 | ./mydb/DEPT.ibd |
| 297 | ./mydb/travel_history.ibd |
| 298 | ./mydb/state_wise.ibd |
+-------+---------------------------------------+
24 rows in set (0.01 sec)


mysql>


12. See variables and global variables.




mysql> SHOW GLOBAL VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set (0.14 sec)


mysql> select @@PORT;
+--------+
| @@PORT |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)


mysql>

Installation of MySQL Database on Oracle Linux 7 using RPM Method

 1. Install Required  RPMS specified below , can be downloaded from oracle edelivery site.


rpm -ivh mysql-commercial-devel-5.7.29-1.1.el7.x86_64.rpm  --force --nodeps
rpm -ivh mysql-commercial-common-5.7.29-1.1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-commercial-client-5.7.29-1.1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-commercial-embedded-5.7.29-1.1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-commercial-embedded-compat-5.7.29-1.1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-commercial-embedded-devel-5.7.29-1.1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-commercial-libs-compat-5.7.29-1.1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-commercial-libs-5.7.29-1.1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-commercial-server-5.7.29-1.1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-commercial-test-5.7.29-1.1.el7.x86_64.rpm


2. Check mysql deamon -->root# service mysql status

3. Login to mysql -- you may get this error

[root@xcell trace]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@xcell trace]#

4. Get temporary pass from installation log file

[root@xcell ~]# grep "A temporary password" /var/log/mysqld.log
2020-04-02T12:17:50.275514Z 1 [Note] A temporary password is generated for root@localhost: X(kV9r51Yle*

5. Set current environment calling the utility mysql_secure_installation by using the temporary root password

[root@xcell ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password:

Re-enter new password:
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password:

Re-enter new password:
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password:

Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
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) : n

 ... skipping.
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!

--> Service mysqld restart


[root@xcell ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> help




mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using  EditLine wrapper

Connection id:          4
Current database:       mysql
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.29-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 2 min 10 sec

Threads: 1  Questions: 46  Slow queries: 0  Opens: 136  Flush tables: 1  Open tables: 129  Queries per second avg: 0.353
--------------

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


mysql> SELECT User, Host, plugin FROM mysql.user;
+-----------+-----------+-----------------------+
| User      | Host      | plugin                |
+-----------+-----------+-----------------------+
| root      | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
+-----------+-----------+-----------------------+
2 rows in set (0.00 sec)

mysql>

Popular Posts