Client/Server architecture of MySQL
Networked environment using a client/server model.
-central acts as server
-Various client programs connects to the server and make requests.
Components Of MySQL Installations :
The MySQL Database also follows the client and server model architecture and following are the components of MySQL Server
- MySQL Server
- Client Programs
- Non Client Programs
MySQL Server :
The server is a database component where all the applications related data is stored and it is served for multiple clients who access that data from a particular database server and under every MySQL Server there is a program running called mysqld which takes the request from multiple application clients and process the data if the mysqld program is not running then client cannot access the data from server and it is a crucial program in the database server which directly gets the data from the storage.
Client Programs :
Client Programs are those which runs when the MySQL Server is running if not the client programs will not work because these programs does not have direct access to the file system then the client program requests the mysqld program which runs under MySQL Server which is responsible to access the data from MySQL Server.
Non Client Programs :
Non Client Programs are the programs which can run even if the MySQL Server is not running because these programs have direct access towards the File system of the server.
Mostly we use MySQL Workbench for administering and manipulating the changes in the database It is a gui tool for monitoring the MySQL Server and there also many client and non client programs for accessing and managing MySQL Server.
Mysqld is a server process and mysql is a client process, if server process is not running client process can’t run..
Non-Client Programs will run even server process is not running.
To enable remote clients to connect to server you need to bind the IP address in MySQL server.
Binding IP address in MySQL
Any configuration changes need to do in mysql you need to do it on /etc/mysql/my.conf file this file is like brine to mysql database.
|
1 2 3 4 5 6 7 8 9 10 11 |
root@ip-172-31-35-134:/home/ubuntu# ll /etc/mysql total 32 drwxr-xr-x 4 root root 4096 Oct 16 17:27 ./ drwxr-xr-x 90 root root 4096 Oct 16 17:27 ../ drwxr-xr-x 2 root root 4096 Oct 16 17:27 conf.d/ -rwxr-xr-x 1 root root 120 Jul 27 14:28 debian-start* -rw------- 1 root root 317 Oct 16 17:27 debian.cnf lrwxrwxrwx 1 root root 24 Oct 16 17:27 <strong>my.cnf</strong> -> /etc/alternatives/my.cnf -rw-r--r-- 1 root root 839 Aug 3 2016 my.cnf.fallback -rw-r--r-- 1 root root 682 Jan 12 2018 mysql.cnf drwxr-xr-x 2 root root 4096 Oct 16 17:27 mysql.conf.d/ |
In my.cnf file you will see several sessions such as clinet, mysqld_safe, mysqld etc…
root@ip-172-31-35-134:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
Comment BInd-address so that we can connect to this machine from remote machines.
In above screen shot bind-address is commented by default it will uncommented you need to comment it to allow other client systems to connect MySQL server once bind-address is hash marked as shown above you need to restart the MySQL server
|
1 2 |
root@ip-172-31-35-134:~# /etc/init.d/mysql restart [ ok ] Restarting mysql (via systemctl): mysql.service. |
Connect to mysql
|
1 2 3 4 5 6 7 8 9 10 |
root@ip-172-31-35-134:~# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2018, 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> |
Now create remote user with necessary authorizations as shown below.
|
1 2 |
mysql> grant all on *.* to 'remoteuser'@'%' identified by 'remoteuser'; Query OK, 0 rows affected, 1 warning (0.01 sec) |
How to check user created in mysql
|
1 2 3 4 5 6 7 8 9 10 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) |
By default all the users created under mysql database.
|
1 2 3 4 |
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 |
Check tables
|
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) |
describe the object called user.
select required columns from user objects.
To check which database we connected.
|
1 2 3 4 5 6 7 |
mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) |
To check only one user information we have limit clause.
To Select data in vertical format .
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...
