---
title: 'MySQL basics | Zomro'
description: 'There are cases when you need to do some work with MySQL databases on a system that does not have convenient database tools (such as phpMyAdmin)...'
image: 'https://dx86q6oq7ry0e.cloudfront.net/uploads/media/blog/a44@server-panel.net/2022/11/01/1668505633_1668505623401.png'
---

![opened image]()

  * [Zomro](https://zomro.com/)
  * [FAQ](https://zomro.com/blog/faq/)
  * MySQL basics 



# MySQL basics

18-09-2022

There are cases when you need to do some work with MySQL databases on a system that does not have convenient database tools (such as phpMyAdmin), or if they have stopped working for some reason. The basics of working directly with MySQL to perform the simplest operations will be considered.  


### MySQL login

To login to mysql connect to the server via SSH as root user and run the command:
    
    
    mysql
    

![MySQL basics - 1](https://dx86q6oq7ry0e.cloudfront.net/uploads/media/blog/a44@server-panel.net/2022/11/01/1668505633_1668505623401.png)

  
If this command is not executed, returning an access error, this means that additional data is required to enter. In this case, enter the access data from the database with which you will work:
    
    
    mysql -u user -p db

  
Here:.  
**-u** _user_ \- database username, in this case, _user_ ;  
**-p** \- an option that specifies that later, during the execution of the command, you will need to enter a password. I do not recommend specifying the password in the console immediately after this parameter, as it will be saved in the command history;  
_db_ is the name of the database.  
  
If several versions of mysql database management systems are installed on the server and you need to connect to a system that runs on a different port, or if you need to connect to a database that is located on another server (remote access to it must be open), enter the command :
    
    
    mysql --host 127.0.0.1 --port 3306 -u user -p db

  
where:  
**\--host** _127.0.0.1_ specifies the server address. Specify the IP address or domain name of another server in case of a remote connection. If the server is local (at _127.0.0.1_ or _localhost_), the **\--host** parameter can be omitted;  
**\--port** _3306_ \- specify the port number if it is non-standard. If the port number is _3306_ , you can omit this parameter.

![MySQL basics - 2](https://dx86q6oq7ry0e.cloudfront.net/uploads/media/blog/a44@server-panel.net/2022/11/01/1668506189_1668506107251.png)

### Create a database dump

To dump the database to a file, run the following command:
    
    
    mysqldump db > db.sql

  
where _db.sql_ is the name of the file to which the dump will be written. The name can be anything.  
  
If you need to dump using a username or on a different server, you can use the same options as when connecting to mysql:
    
    
    mysqldump --host 127.0.0.1 --port 3306 -u user -p db > db.sql

  
If the dump was created without errors, nothing will be output.

### Loading the dump into the database

To load the dump, run the command:
    
    
    mysql db < db.sql

  
or, if the system is located on a different port or computer, run
    
    
    mysql --host 127.0.0.1 --port 3306 -u user -p db < db.sql

  
However, it is worth noting that if the database already has tables, then the dump may not be loaded or may be partially loaded. Also, if the dump was made in a different, newer version of mysql, it may not load into the older version without additional steps.  
  
If the database dump loaded correctly, without errors, no messages will be displayed.

### Working with databases in MySQL

After logging into mysql, you have access to a wide range of options for working with databases, which include creating, modifying, deleting, viewing, assigning users, and much more. Let's create a _newdatabase_ database, user _newuser_ , which will have a _SecretPassword_ password, and give all database privileges to that user. SQL commands are written in capital letters (commands are not case sensitive), lowercase letters are user data. All commands must end with a semicolon.  
  
Create a database:
    
    
    CREATE DATABASE newdatabase;

  
Create a local database user:
    
    
    CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'SecretPassword';

  
Assign rights to the database to the user:
    
    
    GRANT ALL PRIVILEGES ON newdatabase.* TO 'newuser'@'localhost';

![MySQL basics - 3](https://dx86q6oq7ry0e.cloudfront.net/uploads/media/blog/a44@server-panel.net/2022/11/01/1668939476_1668939403971.png)

  
You can also view which databases have been added to the system:
    
    
    SHOW DATABASES;

  
To see which tables are in the database, you must first select the database and then run the list tables command. For example, let's look at the tables in the _admin_wordpress_ database that was created for the _wordpress_ site.
    
    
    USE admin_wordpress;
    SHOW TABLES;

![MySQL basics - 4](https://dx86q6oq7ry0e.cloudfront.net/uploads/media/blog/a44@server-panel.net/2022/11/01/1668939659_1668939591074.png)

  
To see all the data in a table, type:
    
    
    SELECT * FROM wp_users;

  
where _wp_users_ is the name of the table.  
  
For example, let's change the wordpress password using the mysql command line. To do this, you need to change the _user_pass_ field in the _wp_users_ table in a special way. You can do this with the following command:
    
    
    UPDATE wp_users SET user_pass=MD5('NewPassword') WHERE user_login='admin';

![MySQL basics - 5](https://dx86q6oq7ry0e.cloudfront.net/uploads/media/blog/a44@server-panel.net/2022/11/01/1668941021_1668941016437.png)

  
The value is set using the **SET** command. Here **MD5** is the function needed to set the password hash. The wordpress password itself is not stored in the database in clear text. For other CMS, you may need to use other functions.  
  
You can select the desired user using the **WHERE** clause. In this example, the value is selected by the _user_login_ field, namely the _admin_ user. You can also search for the required strings using other fields, for example, _ID_ or _user_email_. Be careful. If there are multiple rows where the specified field matches the selected criteria, all of them will be changed. Choose fields that contain non-repeating (or key) values.  
  
You can delete the database with the command:
    
    
    DROP DATABASE newdatabase;

### Exit from MySQL

To exit the mysql console, you can execute the command **exit** , **quit** , or press the combination **Ctrl+d** __ or **Ctrl+c**.

### Conclusion

Only the basic commands for working with MySQL were considered, which allow you to perform basic operations with databases.

Similar articles:

[Minecraft Java Server Cores](https://zomro.com/blog/faq/332-minecraft-java-server-cores) [How to set up a Samba server on Linux and connect to it from Windows Server 2019](https://zomro.com/blog/faq/334-how-to-set-up-a-samba-server-on-linux-and-connect-to-it-from-windows-server-2019) [Dockerfile: A Complete Guide for Beginners and Professionals](https://zomro.com/blog/faq/477-dockerfile-a-complete-guide-for-beginners-and-professionals) [How to add a domain to DNS hosting](https://zomro.com/blog/faq/87-kak-dobavit-domen-v-dns-hosting) [Working with Cache and Buffered Memory in Linux: Overview and Cleaning Guide](https://zomro.com/blog/faq/368-working-with-cache-and-buffered-memory-in-linux-overview-and-cleaning-guideworking-with-cache-and-buffered-memory-in-linux-overview-and-cleaning-guideworking-with-cache-and-buffered-memory-in-linux-ov)

[ Dedicated servers from €88.80/mon ](/dedicated-servers)