Scribd
Upload
27 views7 pages

Create Users and Tables in MySQL Workbench

This document provides steps to create a new user and grant privileges using MySQL Workbench, as well as steps to import and export data. The key steps include: 1. Click the server menu and select "User and Privileges" to add a new user, set the password, and grant privileges and roles. 2. Create a new connection using the new user credentials to test login. 3. Import data by selecting tables and files from the "Data Import/Restore" menu. 4. Export data by selecting tables and options like stored procedures from the "Data Export" menu.

Uploaded by

peterdonniel.villanueva
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
27 views7 pages

Create Users and Tables in MySQL Workbench

This document provides steps to create a new user and grant privileges using MySQL Workbench, as well as steps to import and export data. The key steps include: 1. Click the server menu and select "User and Privileges" to add a new user, set the password, and grant privileges and roles. 2. Create a new connection using the new user credentials to test login. 3. Import data by selecting tables and files from the "Data Import/Restore" menu. 4. Export data by selecting tables and options like stored procedures from the "Data Export" menu.

Uploaded by

peterdonniel.villanueva
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

How to create a new user using MySQL Workbench

1. Click local instance MySQl80


2. Write password – ok (12345)
3. Click Server menu
User and Privileges Sub menu
Add Account – (leftmost button)
Write login name : User1
Write password : 1234
Confirm password : 1234
Apply

3. Account Limits tab – as is

4. Administrative Roles tab for Admin only

click DBA to select all


uncheck Security admin (for user) – to uncheck admin privileges
automatically Global privileges will uncheck privileges for admin inclusively
5. click Apply
6. click Add Entry – to select schemas bsinfotech2b and bsis2a
7. click ok
8. The user “user1@%” will have the following
1. Object Rights (all check)
2. DDL Rights (all check)
3. Other Rights (all check except:
a. GRANT OPTION
b. LOCK TABLE
4. Click Apply
9. Close the local instance MySQL80 (Admin)
10. Create new account connection by clicking the to add new connection account

Setup New Connection

1. Connection Name: User1.


2. Username: User1
3. Click Test Connection
4. Enter password : 1234
5. Ok
6. “Successfully made the MySQL
Note: Connection “ will display
Note: connection name – the name that will appear in the Welcome page would be the
same with the account name (Login).

Note: the username is case sensitive the created username should match to the Setup
New Connection
To Create Table
1. Click your username connection at the Welcome page
2. Double click the granted database
3. Right click the table
4. Create Table
5. Write the Tablename : Orders
6. Write the fieldnames under
Column Name Data Type PK NN
Idorders Int
Order col Varchar (45
7. Click Apply
8. Review the SQL Script to be Applied on the Database.
9. Apply
10. Applying Script to the database
11. Execute SQL Statements
SQL Script was successful applied to the database
12. Click Finish
13. Click/check all fieldnames as NN (not null)

To add item to each field name.

1. Click the table name at the Navigator pane.


2. Click the Table Icon
3. Write the items
4. Click apply
5. Review the SQL Script to be Applied on the Database
6. Click Apply
7. It will display “ SQL Script was Successfully applied to the database.
8. Click Finish
How to Create MySQL Database, add Tables, Grant Privileges and Import Data in
MySQL Workbench

1. Click Schema
Workbench is used to communicate with MySQL.
Sys—is the default schema
2. Right Click
3. Click container icon to create a database
4. Right click the database to create a table or click the table icon
5. Right click the table and click
SELECT ROWS-- Limit 1000 to view your table

Import Data in MySQL Workbench to


1. Click Administrator
2. Under the Management – click DATA EXPORT
3. Select the database then you can see the list of tables
4. Click Dump stored Procedures and functions
Dump Events
Dump triggers
5. Click : Export to Dump Projects folder:
c./ User/Admin /Documents/Dump…
then you can also select DESTINATION

Activity 1

How to Create Admin and Users Accounts

1. Select Local instance MySQL


Enter Password: 12345
Ok
2. Select Administration (under Navigator pane)
3. Select Users and Privileges (Management pane)
4. Click Add Account
Login Name : Admin + Password: 123
Click Apply
Account Limits
As is
Administrative Roles
SELECT ALL by
Clicking DBA
5. Close— to Return to the Welcome page
6. Add Account Connection by clicking MySQL Connection
7. Write Connection Name: Local Admin
This will appear in Welcome page
Write Username: Admin – given to case sensitive admin
8. Click Test Connection
Enter password: 123 (this is the password given to Admin account
“ Successfully made the MySQL connection”
9. Ok
10. Ok
11. Create a schema under (admin)
12. Click Local Admin Account
13. Select Schema (Navigator)
14. Click Database icon
15. Write the name of schema : bsinfotech/bsis
16. Click Apply
17. Applying SQL script to the database (SQL script was successfully applied to the
database)
Click Finish button
18. Double click bsis schema (Navigator)
19. Right Clicks Tables – to create new table: employees
20. Write the column name ( fieldname) (title of records)
21. Select the Data Type
22. Click Apply (if done)
23. Review the SQL Script to be Applied on the Database
Click Apply (SQL script was successfully applied to the database)
Click Finish

How to Back-up tables (EXPORT)

1. Select Administration (Navigator)


2. Select Data Export + Select schema
3. Select Table/s to Export
4. Check ALL objects
Dump Stored Procedures and Functions
Dump Events
Dump Triggers
5. Click Start Export
Data Import/Restore
Import from Disk tab (default)
1. Check/Select Import from Project Folder
E/LENOVO FILES/ Syllabus/ AUD/ / CC 205 Information Management/ Schemas

Select Database Object to Import


(only available for Project Folder)
Check the schema
Bsis
Orders
Employees] tables
2. Import Progress
Check/click Start Import button

MySQL Import Database using MySQL Workbench

1. Import a SQL Dump File into MySQL server:


-- No create Schema Statement
-- Include Create Schema Statement
2. Import multiple SQL files ( Project File)

Import MySQL DUMP FILE that doesn’t includes CREATE Schema Statement

1. Create a Schema (this is where you are going to import your table)
2. Select Data Import/Restore (Administration) or Server menu – Data Import
3. Click – Import from Self-Contained File
Browse to Select the Table
4. Select schema from—Default Target Schema
5. Select: Dump Structure and Data
6. Click Start Import
7. Select Schema tab – to check the imported table
8. Right click the table from Selected schema and;
9. Select REFRESH ALL

Import Multiple MySQL Dump Files

Note: The name of folder should be the same the schema


1. Create Schema: bsis
2. Select- Import from Dump Project Folder to select the directory of the bsis from
Local E drive
3. Open the Selected folder (bsis) and select the table/s
4. Click Import Progress
Click Start Import
5. Select Schemas pane and right click
6. Click REFRESH ALL

Creating table and inserting records

Note: Always click Apply button after each record. Do not wait to complete the records
or table before clicking Apply. So that the records will not discard when changes
happen.

To Record your data


1. Click this button

To Edit your column/Table


1. Click screw driver

MySQL Workbench alternative


1. XAMPP
2. DBeaver
3. PhpMyAdmin
4. HeidiSQL
5. DataGrip
6. dbForge studio for MySQL
7. Navicat
8. SQLyog
9. Adminer
How to Create a Point of Scale Application in Java Net Bears

Apache Net Bears IDE 11.0


1. Open A pen Net Bears
2. File
3. New Project
4. Java with Ant
Java Application
Next
5. Write Project Name: JavaPOS
6. Check Create Main Class to Uncheck
7. Finish
8. Under projects—Find JavaPOS Project
9. Right- click
New
JFrame Form
Class Name: JavaPOS
Finish
10. Right Click on the Frame
11. Set Lay-out – Absolute Layout
12. Right again
Select Properties
Code Tab
Designer Size = Set to [1400,800]
Check the Generate Center
13. Click the Palette pane (right most of the screen)
Select panel
Properties of Panel
Select Bevel Border
14. You can right click the Panel and copy-paste to be assign for the other designs.

Common questions

Powered by AI

Executing 'Apply' in MySQL Workbench is significant because it ensures that each modification—be it table creation, column addition, or data insertion—is immediately committed to the database. This prevents accidental data loss or discrepancies between user intention and database state. By applying changes often, users mitigate the risk of data inconsistency that could arise from unintentional user logout or system crashes before queueing all intended changes .

MySQL Workbench facilitates establishing new database connections through the 'Setup New Connection' interface. Users must accurately input the 'Connection Name' and 'Username', both of which are case sensitive to ensure compatibility with MySQL authentication processes. An incorrect case can lead to connection failures because MySQL treats usernames and associated connection names with strict adherence to their case. Once the setup is complete, testing the connection with a valid password ensures that the setup details align with the server's expectations .

Optimizing user privilege management during account creation in MySQL Workbench involves carefully selecting the necessary roles and privileges. When adding a new account, use the 'User and Privileges' menu to define account limits without granting excessive rights. Under the 'Administrative Roles' tab, assigning privileges like 'DBA' should be restricted to trusted users, while specific permissions such as accessing certain schemas or objects can be precisely configured under 'Object Rights', 'DDL Rights', and 'Other Rights'. Crucially, avoid granting 'GRANT OPTION' and 'LOCK TABLE' to minimize risks associated with unauthorized privilege escalation and potentially destabilizing database operations .

In MySQL Workbench, when creating tables, users must right-click and select 'Create Table', input the table and column details, and then review the generated SQL script on screen. This script lists the SQL commands MySQL Workbench will execute, allowing users to verify the accuracy of the column definitions, data types, key assignments, and other constraints. This review is crucial for identifying and correcting potential errors before they affect the database, ensuring intended schema configurations are executed correctly and maintaining data integrity .

To export database tables and related objects in MySQL Workbench, navigate to the 'Administration' section and select 'Data Export'. Choose the schema and tables you wish to export. Comprehensive options include dumping stored procedures, functions, events, and triggers to a designated folder, like 'c./ User/Admin /Documents/Dump'. You can customize the export process by selecting specific objects or opting for a full schema dump. The option to choose the destination directory allows for flexible organization of database dumps for backup or migration purposes .

Creating a new user in MySQL Workbench involves several steps. First, enter your instance by clicking on local instance MySQL80 and entering the required password. Navigate to the 'Server' menu, then 'Users and Privileges', and click 'Add Account'. Fill in the login name and password details, such as username 'User1' with password '1234'. Set account limits as is and navigate to the 'Administrative Roles' tab to assign roles—uncheck security admin privileges if necessary. Make sure to apply the changes. For schema access, use the 'Add Entry' option to select schemas like 'bsinfotech2b' and 'bsis2a', ensuring object, DDL, and other rights are appropriately set before applying these settings. Finally, close the instance. A new account connection is set up under 'Setup New Connection', using the connection name and username, followed by a password confirmation to complete the operation successfully .

In MySQL Workbench, creating a schema begins by accessing the 'Local Admin Account'. Navigate through the 'Navigator' pane to the 'Schema' option, and initiate the creation of a database by clicking the database icon. Define the name for your schema, such as 'bsinfotech' or 'bsis', then click 'Apply'. This will generate an SQL script which is executed to apply the schema to the database. Once applied, the schema appears on the left-side Navigator, and you may double-click it to verify its presence. From there, tables can be created within the schema by right-clicking on 'Tables' and selecting to add a new table, such as 'employees', while an SQL script accompanies each modification for review and application .

Backing up databases in MySQL Workbench involves selecting 'Data Export', choosing the relevant schema, and exporting tables, stored procedures, functions, events, and triggers to a secured directory. During this process, monitoring for complete dumps of all essential data components is critical. Restoring data entails navigating to 'Data Import/Restore', selecting to import from a project folder or dumped file, and identifying a target schema. Once the data import begins, it's crucial to verify the data integrity by checking the imported tables for expected records and relationships, thus ensuring stability and consistency in the recovery process .

Importing a MySQL dump file involves several precise steps. First, access 'Data Import/Restore' through the 'Administration' interface or the 'Server' menu. Choose 'Import from Self-Contained File' and browse to select your SQL file. Target the default schema where you wish the data to be imported, typically requiring that a schema already exists, such as 'bsis'. Use the 'Dump Structure and Data' option to import both the database's structure and its content. Initiating the import process by clicking 'Start Import' will execute the script, and you can check the import status under the schema tab where the imported tables can be refreshed to ensure they have been correctly integrated .

When setting up a user account in MySQL Workbench, administrative roles are configured through the 'Administrative Roles' tab. By clicking 'DBA', you can select all available roles, providing extensive database administration capabilities. For non-administrative accounts, uncheck options like 'Security admin' to restrict broader administrative access. This role unchecking ensures that non-admin users don't acquire elevated privileges inadvertently, which is critical in maintaining database security and operational integrity .

You might also like