Introduction to MySQL Database Administration.
What is MySQL?
MySQL is the most popular open source SQL database management system and it is developed, distributed and supported by Oracle Corporation.
Initially MySQL was introduced by MySQLAB organization and acquired by SUN MICRO SYSTEMS then MySQL acquired by ORACLE .
Overview of MySQL
MySQL is a relational database management system (RDBMS) released under the GNU General Public License (GPL). It is one of the most widely used open-source database systems, and is compatible with a multitude of website applications.
MySQL is available in two products
- Enterprise edition – is available as subscription based edition.
- Community – is available for free of cost.
OS supported by MySQL
MySQL supports most of the operating systems such as Linux, Windows, and MAC etc…
MySQL Tools
Workbench’s is one of the most popular GUI tool.
- Navicat for MySQL
- Sequel Pro
- HeidiSQL
- phpMyAdmin
- SQLyog
- DBTools Manager
- MyDB Studio
Installation of MYSQL
MySQL Enterprise DB installation on Linux.
You can download software from below URLs.
Community edition:
https://dev.mysql.com/downloads/
Enterprise Edition :
Login to server using putty and execute apt-get update to check updated are properly installed or not.
To Install MySQL DB execute below command, and make sure you are installing as root user.
|
1 |
sudo apt-get install mysql-server-5.6 |
Press enter then you will get below screen asking for root password, it is not mandatory but recommended to set root password.
Reenter the password. 
After reentering root password again it will resume installation.
Default locations on Linux are /usr/local/mysql/var
if you install MySQL from a source distribution /usr/local/mysql/data
if you install from a binary distribution and /var/lib/mysql
if you install from an RPM file. Under Windows, the default data directory is C:\mysql\data.
To check MySQL process is running or not executes
|
1 |
/etc/init.d/mysql status |
How to login to MySQL database
In command prompt $ mysql –uroot –p and press enter, it will prompt to enter password as shown below.
By default MySQL will create 3 system databases, you can see the using command
mysql> show databases;
- Information_schema database
INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains.
The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them,
and you cannot set triggers on them. Also, there is no database directory with that name.
- mysql System Database
The mysql database is the system database. It contains tables that store information required by the MySQL server as it runs.
- Performance_schema
Performance Schema provides a way to inspect internal execution of the server at runtime. The Performance Schema focuses primarily on performance data. Performance Schema monitors server events.
How to open a database
|
1 2 |
mysql> use information_schema Database changed |
How to check tables under information_schema database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | OPTIMIZER_TRACE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLESPACES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | | INNODB_LOCKS | | INNODB_TRX | | INNODB_SYS_DATAFILES | | INNODB_FT_CONFIG | | INNODB_SYS_VIRTUAL | | INNODB_CMP | | INNODB_FT_BEING_DELETED | | INNODB_CMP_RESET | | INNODB_CMP_PER_INDEX | | INNODB_CMPMEM_RESET | | INNODB_FT_DELETED | | INNODB_BUFFER_PAGE_LRU | | INNODB_LOCK_WAITS | | INNODB_TEMP_TABLE_INFO | | INNODB_SYS_INDEXES | | INNODB_SYS_TABLES | | INNODB_SYS_FIELDS | | INNODB_CMP_PER_INDEX_RESET | | INNODB_BUFFER_PAGE | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_FT_INDEX_TABLE | | INNODB_FT_INDEX_CACHE | | INNODB_SYS_TABLESPACES | | INNODB_METRICS | | INNODB_SYS_FOREIGN_COLS | | INNODB_CMPMEM | | INNODB_BUFFER_POOL_STATS | | INNODB_SYS_COLUMNS | | INNODB_SYS_FOREIGN | | INNODB_SYS_TABLESTATS | +---------------------------------------+ 61 rows in set (0.00 sec) |
To see the table structure
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> show create table CHARACTER_SETS -> ; +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CHARACTER_SETS | CREATE TEMPORARY TABLE `CHARACTER_SETS` ( `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', `MAXLEN` bigint(3) NOT NULL DEFAULT '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 | +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
To change the database.
|
1 2 3 4 5 |
mysql> use mysql 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 |
TO check the tables under mysql database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec) |
If we want to grep what mysql daemons are running on the background.
|
1 2 3 |
ubuntu@ip-172-31-45-18:~$ ps -elf | grep mysql 4 S mysql 3580 1 0 80 0 - 276914 - 14:48 ? 00:00:03 /usr/sbin/mysqld 0 S ubuntu 3769 1804 0 80 0 - 3236 pipe_w 17:16 pts/0 00:00:00 grep --color=auto mysql |
Here mysqld is a server program
mysql is a client program.
By default server program is running in /usr/sbin/mysqld
|
1 2 |
ubuntu@ip-172-31-45-18:~$ ll /usr/sbin/mysqld -rwxr-xr-x 1 root root 24895464 Jul 27 18:06 /usr/sbin/mysqld* |
Lets try to find out where mysql is running.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
root@ip-172-31-35-134:/home/ubuntu# find / -name mysql /var/lib/mysql /var/lib/mysql/mysql /var/log/mysql /snap/core/5328/etc/apparmor.d/abstractions/mysql /snap/core/5328/usr/share/bash-completion/completions/mysql /usr/lib/mysql /usr/bin/mysql /usr/share/bash-completion/completions/mysql /usr/share/mysql /etc/init.d/mysql /etc/apparmor.d/abstractions/mysql /etc/mysql |
These are different programs which are installed at the time mysql installation.(default).
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
