mysql workbench tutorial for beginners. MySQL Workbench 6.3. mysql workbench 6.3 tutorial. A Complete Step by Step from Installation to Getting Started with MySQL Workbench 6.3.
To Learn or Teach Linux visit www.theskillpedia.com, A Marketplace for Learners and Trainers. To know about me visit my profile at www.rnsangwan.com. I deliver online training on Business Analysis, Linux, Unix, MySQL, Sybase, SQL Server, Perl, Python, R Data Analysis, Scala etc.
hi friends in this session what am i planning to discuss is very interesting the software my sequel workbench so I am working on RHEL 6.8 this is a not original segmented sorry sent to s 6.8 and I have installed my sequel workbench now hi I how I installed this how did I install it just let me brief you about that installation steps so for that first I enabled some repositories ok I am just telling you and showing you the repositories which I enabled this is a repository which is a EPE L ok I have enabled this repository which is required APL da trap oh sorry VI e PE l dot repo this is a repository which I enabled you can download it over this is a fedora website I mean this available on this website so this is a fedora based extended package library utility and and I have a uploaded you know enabled another repository which is this PKG repository which is listed here and i enabled this also sent to s repository so i have enabled these repositories okay and after that I simply installed this I gave the command Y M installed Mike my sequel – workbench M install my sequel – workbench this is a command which I give so if you are doing it for the first time you should check the repositories which I am enabled you can pause in between and check the repositories configure the repositories and then you simply give the command and you will be able to get the my sequel workbench already up and running now to start working on my sequel bark wench this is a shortcut is available here my sequel workbench you click on this and you will get this another ways you start from the command line also you type my sequel – workbench and use ampersand sign to run it in the background so that you see only that gy part then you get your prompt back now this is a workbench and this is the interface which you get first thing is you need to you know create connections connection settings so how to create connection settings just let me brief you quickly okay this is how we create the repositories I mean sorry the connections so this is one connection okay and this is another connection so create another connection I will simply go to this plus sign and I will create a connection with the name sanguine so this connection is was the sequel server okay sangwon is the username which I want to you know name of the connection which I am giving localhost root is the username password if I have I have not specified any password therefore I am NOT giving any password test connection and you can see that connection is successful then advanced the settings are there if you want to specify this or SSL settings if you want to specify that also and if you want to go for server management you can go to the server management also the configuration DB connection management and OS and all that stuff let's go to that also quickly and the server database connection tested successfully this is Linux operating system and this is not sent to s 6 this is Center is 5 so it is our H CL 6 or Oracle 6 or the same thing this is going to the same thing so I use Oracle 6 I can use this also alright okay and next the configuration files next continue and this is the connection settings all set and okay now you are connected and this is my son one connection click on this and you have the entire interface if you want to just look at the configuration settings here you can look at once again and manage access you know management access settings so go to this and you check this setting from here also and the most important to this one this file is basically start my sequel if this will not point to if it this file is not pointing to the right my sequel D script which is under in it dot B in RHEL you will not be able to manage the start and stop of the service from using this MySQL workbench so if you want to manage the start and stop of the service using MySQL workbench you must have this script properly pointing to the file and this is your configuration file my dot znf if it should also point to the right configuration file the pointing should be proper else it will not work properly let me tell you categorically so if you want to manage your services through this you have to have this ok this part is done now we can close this and you can see the server status from here and this is the server status you should be able to see that connections these are client connections now this client connections you will not be able to see and performance reports you will not be able to see these things until unless you have performance schema setup so in my case you can see the performance schema is a setup and how to set up the performance schema if it is not set up properly you will not be able to see these messages and you will get an error how performance schema is set up to check that and to ensure that it is set up properly so how do I do that let me just go through my dot say a file and help you out so for that we need to add it my dot CNF file and here in this file you must have the setting okay and which is a performance underscore schema is equal to on if it is not on this will throw an error and you will not be able to see that connections and you'll face a lot of problems in other things also other settings also so you must verify that a my dot CNM file and once you enable this performance schema you must restart your service so service my sequel D restart so ensure that a service is restarted perform performance schema is enabled so you must verify these two things and then only you should proceed working with the my sequel workbench and you will be able to see their performance schema right now it is enabled right okay now then we have the performance report right now we are not working we are not doing any work therefore we are not able to say anything this is dashboard network status my sequel status and in ODBC storage engines status outgoing network traffic so you can see that everything all the details this is our dashboard now quickly let's do thing something you know two three demonstrations I would like to give one is reverse engineering now this is my database for which I want to do a reverse engineering means this classic models is a database under which I have some tables customers employees offices and order details I want to understand how these tables are related to each other so for that I want to do a reverse engineering so for that go to database click on reverse engineer and then select the you know connections it is a step by step procedure wizard will take you to connections and then once you are connected you select the database for which you want to perform that you know reverse engineering click on next go to next and import my sequel table objects so click on execute next the state forward my friends close and here you are ready to go so this is your well model and where you have these tables classic models those these are the tables in the you know schema to click on the tables you can see the tables alright so you can double click on the tables you can see the tables also here once you see the tables and the reverse engineering basically ER diagram EER diagram where you actually see the entire diagram okay this is our diagram for this classic models and you see everything so this is the product lines customers order details payments okay all the tables offices orders products employees now relationships are there and you simply take the pointer there and it will show you where they are pointing to so customers tables sales rep employee number references are into employees table employee number on update and on delete same thing you can see anywhere with any relationship you can simply click on that and you see the relationship status so this reverse engineering this diagram is prepared by my sequel workbench for you so that is the interesting part my point friends you simply take the pointer there and you see the details so this is a diagram complete data Dyer you know data dictionary and the data model and you can actually see this you know directly and a lot of tools are available you can create your own these are you know you can create new layer or you can erase an object or delete an object or you can move the model you know by using this hand so if you move this you can directly move around go up go down using this so this is a pointer this is erasing or deleting this is a place and new text object if you want to write place a new object so this is editing tools and these are relationship defining things so if you want to draw from scratch you can draw it using this dashboard in utility so this is the reverse engineering let us go back I do not want to save it I am back to this another is let's do a another option called creating a user let's so manager user here so this is our server status okay I can see that server status and then client connections these are client connections right now who are connected and users and privileges now let's create a user here and give privileges to the user for that I will click on Add Account the user name is let us say Ram and the password I can specify so this is the password after that I can specify the limits also maximum number of queries account can execute within an hour so I want to put a restriction 20 queries maximum update 20 updates maximum connections per hour he can connect 50 times maximum concurrent connections not more than 10 so these are the limits then I can specify administrative load for example I want to assign user admin roles so this is create a user if I want to allow grant option also so I can select grant option also so these are administrative privileges I am given to this user then schema privileges let me give the schema privileges for classic model so let me select the classic model from here this is the database which I have so classic model database selected and from on classic models I want to specify this object specific privileges living executed show view privilege and from this side I am selecting this GDL rights okay I am NOT giving a drop and trigger right and grant option I am giving create temporary tables I am giving LOC tables I am NOT giving so this is these are the privileges which I given to this user and the user is added successfully now I can double click once again and I can see the user from where the user should be able to connect if I want to specify that user should be able to connect from only rim you know local server or local host I must specify the local local host here so 127 dot 0 dot 0 dot 1 which means that user will not be able to connect from remote machines only local connections are allowed for this user this is how you can create a user user is created privileges are specified here now after that we can look at the you know status and system variables these are system variables of my sequel all system variables are displayed by this if you want to filtered you can select filtered here if you want to say bill log these are bin log specific system variables command admin DDL DML general prepared statements replication commands show so these are all system variables sorry status variables if you want to look at status very a system variables you can click on this and these are system variables all right so you can check the values of the system variables anytime using this and you know you can check the value directly change the value also by clicking on that value for example if I say backup and in fact let me talk about the system variables and I want to change the value for in ODB data table and I want to specify a value where it would be data general settings and I want a specific value for in ODB let me find out that variable which I am looking at you know DB files per table I am actually looking at that in ODB files per table tablespace per table let me check that I am trying to figure out that or performance schema this is my performance kippah related settings performance query cache a replication alright threading so these are values and you can directly change the value okay so you know DB general I am not able to find that value status variable this might be system and here let me check you know DB here correctly no DB buffer pool data files data file path home directory you know DB full search general ok log files in memory then we have various other things so these are the current variables in this and same way my eyes am so logging policies alright logging audit different type of logins and log expire this is a very interesting setting expire log days if you want your log should expire in five days using you can set the setting okay and you simply click outside and it is done so this way you can set your system variables using this then we have a date I import/export let's try to export the data now from the data base so data export so this is export I want to select classic models customers Emprise all tables I want to export I want to I am not want to export stored procedure functions and I want one self-contained file to be located here okay and there are advanced options I can choose while exporting create option is to be given and compress I don't I yes I want compression utility flush privileges I want disabled keys so these settings I can verify which I want in my case alright and that's it I think that sufficient all the settings and now I simply can click on this and include create schema also if the schema is not existing I want to include the statement include create schema by selecting this and start export past for password for my sequel I do not have any password I am simply clicking on OK and export completed now we can go to this directory root dumps and I can check and I can show you the file is indeed created by this name ok so CD let me go to this directory and show you ok and you can indeed see that yes the directory the file is created LS siphon L and you see this is the file which has been created and now this is the time right now 14th December 9:53 as of now so file has been created if you want to have a look at this file you can check – 1.6 l okay this is the file which I have been created so this is a data export utility same way we can do import also from here data import or restore startup shutdown can be done from here and this will not work as I must warn you once again if the setting is not correct ok I can go to the performance report once again or the dashboard once again and check the performance performance schema connections users and privileges and server shutdown startup dashboard so I have already shown all the things now and that's all about this quick review or quick overview of this my sequel workbench thank you everybody thank you everybody for watching this thank you so much bye-bye take care