Mysql is a
database which is widely used for web applications. Let us have few insights about Mysql server
when an Oracle DBA asked to check the Mysql.
Connecting
to Mysql
1. Login to server using ssh
connection.
2. Use the below command
mysql –u root
If you get error message “ERROR 1045 (28000): Access denied for user
'root'@'localhost' (using password: NO)” , then you have to use.
mysql –u –root –p
[root@vm1 etc]# mysql -u root -p
Enter password:
It will prompt for the password for root user. Once password is
provided, it will take you to Mysql client.
How to
check what is there in database
The
concept of database is different in both Mysql server and Oracle.
In
Oracle, we will have a database and schemas under the database.
In Mysql
server, we will have many databases and in Mysql client, we can use the word
schema interchangeably with the word database.
1. Once we login to the Mysql client,
type the below command to see the databases in Mysql server,
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| database |
| mysql |
| performance_schema |
| test |
+--------------------+
In Mysql, we will have default databases
Information_schema,database,mysql,performance_schema and test. If you notice any other, then those are the
databases created.
2. To use any particular database ,
type
use <database_name>;
mysql> use mysql;
Database changed
3. To see the tables in the database,
type.
mysql> show tables;
4. If you want to see more details
about the tables, use the below command.
mysql> show table status;
The above command will show the tables stored
in the Engines. Storage Engines are
another concept in Mysql which is not in Oracle.
Most common storage engines are MyISAM and
Innodb.
MyISAM storage engine is available as default
prior to Mysql 5.5 version. In this storage, the update statements will do
table-level locking for read operations.
Innodb Storage Engine is available from Mysql
5.6 as default and this performs similar to Oracle with transaction consistent,
multi version concurrency, referential integrity and row-level locking.
5. SQL statements can be used as
follows.
At the end of each statement, we have to either
; or \G . If we give \G, the data will be transformed select * from table_name;
select * from database_name.table_name;
to column format. This is good for tables with
many columns.
select * from
information_schema.tables \G
6. If we need to see the script for
creating a table, we can use,
mysql> show create table <table_name>;
or
mysql> show create table
<database_name>.<table_name>;
7. To see the indexes for the table.
mysql> show indexes for <table_name>;
8. To view the description of the
tables, Mysql uses the command similar to oracle describe.
mysql> describe mysql.user
or
mysql> desc mysql.user
How
many users are connected to the database?
Once you
connected to the database, you may want to know how many users are connected to
the database. To list this, use
mysql> show processlist;
This
will list the following information for each user.
User – which user is connected.
Host - the machine from which the users are
connected
db – which database
Query – which commands the users are currently
executing.
How
the data is stored in OS
We have
seen how the data is stored in the database in Mysql. Now, we will see how the
data is stored at the OS level like data files, etc.
The
default location of the mysql datafiles is /var/lib/mysql.
If you
don’t find any files under this, just open to /etc/my.cnf and check for the
path of the data directory “datadir”.
In this
article, we have files under default location /var/lib/mysql
The
files for each database will have a directory with their names, here, we have
test,
performance_schema,mysql,database
Once you
go into the directory for the database, we will have following.
*.MYD –
For tables in MyISAM engine.
*.MYI
- For indexes in MyISAM engine.
*.frm –
this is the file that describes the structure of each table.
InnoDB
tables are stored in InnoDB tablespace which consists of one or more files.
This is similar to Oracle tablespaces.
By default, tables and indexes in InnoDB are stored in one tablespace
consisting of one file /var/lib/mysql/ibdata1.
Similar
to Oracle redo logs, we have logs in Innodb
that allows instance crash recovery.
By default we will have 2 files /var/lib/mysql/ib_logfile0 and
/var/lib/mysql/ib_logfile1. The
tablespace files also contain the Undo data.
Data
Dictionary in Mysql
Similar
to Oracle where we have data dictionary information, we will have tables
(actually views) which contain the details of the database like tables,
indexes, user privileges, etc., in Mysql.
This
information is stored in information_schema
database/schema. This database/schema is
available from Mysql 5.0.
mysql> use information_schema;
Database
changed
mysql>
mysql>
show tables;
mysql>
select * from tables where table_name=’<table_name>’;
To
know the users created in the database and their purpose
User
details are stored in mysql.user table. It also contains all the global
privileges the users contains.
select * from mysql.user;
By
default, Root user does not have any password and also we have an anonymous
user with no password. To make Mysql server
secure and clear few anonymous users with few default security holes, it is
recommended to run the script shown below from the operating system.
mysql_secure_installation
Note: Do
not use Mysql client for this.
In
Mysql, the users are uniquely identified with the combination of both user and
host.
eg.
Suppose if we have 2 users san@host1 and san@host2, the two users are
different, they can different password and different privileges.
Moreover,
in Mysql, the user can connect only from the host defined in mysql.user table.
i.e., if any of the users mentioned in our example tries to connect from host3,
then the access will be denied.
Now, let
us see how to create a user in Mysql and provide privileges to the user.
mysql> create user testuser@10.10.1.10 identified by 'testuser';
Query OK, 0 rows affected (6.59 sec)
mysql> grant all on mysql.* to testuser@10.10.1.10;
Query OK, 0 rows affected (2.65 sec)
mysql> grant all on test.* to testuser@10.10.1.10;
Query OK, 0 rows affected (0.00 sec)
To see
the database level privileges for, we can use the query
select * from mysql.db;
or
select * from mysql.db \G
If you
want the user to connect from anywhere, then we have to use the wildcard (%)
mysql> create user testuser1@'%' identified by 'testuser1';
Query OK, 0 rows affected (12.03 sec)
We can
even combine the creation and providing privileges in a single statement as
below.
mysql> grant all on mysql.* to testuser2@'%' identified by
'testuser2';
Query OK, 0 rows affected (1.47 sec)
mysql>
select host,user from mysql.user where user like 'testuse%';
+------------+-----------+
|
host | user |
+------------+-----------+
| % | testuser1 |
| % | testuser2 |
|
10.10.1.10 | testuser |
+------------+-----------+
3 rows
in set (0.00 sec)
How
to start and stop Mysql
Mysql
database can be started and stopped like normal services in unix.
[root@vm1
~]# service mysql stop
Shutting
down MySQL.......^[ ... [ OK ]
[root@vm1
~]# service mysql start
[root@vm1
~]# service mysql status
[root@vm1 ~]# ps -ef |grep mysql
root
6777 1 0 19:48 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql
--pid-file=/var/lib/mysql/vm1.pid
mysql
6887 6777 2 19:48 pts/1 00:00:03 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql
--plugin-dir=/usr/lib64/mysql/plugin --user=mysql
--log-error=/var/lib/mysql/vm1.err --pid-file=/var/lib/mysql/vm1.pid
root
7153 6216 0 19:51 pts/1 00:00:00 grep mysql
mysqld –
Mysql server processes
mysqld_safe
– process that monitors the Mysql server process and will restart if it has
failed.
By
default, error logs are stored in /var/lib/mysql/<server_name>.err
Configuration
file similar to oracle’s pfile or spfile
Mysql has
a file similar to pfiel or spfile and usually it will be in either of the below
locations. (my.ini in windows)
Default
options are read from the following files in the given order:
/etc/my.cnf
/etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
In my
case, it is /usr/etc/my.cnf. In this file, we have different labeled with
Mysqld and Mysql.
Mysqld –
it is the section which has configuration data for Mysql server.
Mysql –
it is the section which has configuration data for Mysql client.
Note: In
case, if you are not able find the /etc/my.cnf path , then use mysql --help and
get the path of default options.
No comments:
Post a Comment