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
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:.
-uuser - 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:
--host127.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;
--port3306 - specify the port number if it is non-standard. If the port number is 3306, you can omit this parameter.
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';
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 wordpresssite.
USE admin_wordpress;
SHOW TABLES;
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_userstable in a special way. You can do this with the following command:
UPDATE wp_users SET user_pass=MD5('NewPassword') WHERE user_login='admin';
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.