Practical MySQL & MariaDB

MySQL Command Line Guide: Essential Commands for Beginners (with Examples)

Basic Operations
目次

1. Introduction

1.1 MySQL Overview and Why the Command Line Matters

MySQL is widely used as an open-source relational database management system (RDBMS). Its main benefits include efficient data management and flexible data operations using SQL (Structured Query Language). It is used in many web applications and enterprise systems, and its powerful features can be fully utilized through the MySQL command line.

1.2 Purpose of This Article

This article focuses on MySQL command line operations and explains everything from basic commands to more advanced usage, including creating and managing databases and configuring user privileges. It is especially designed to help beginners and intermediate users build practical skills to confidently use MySQL.

1.3 Who This Guide Is For

This guide is intended for beginners to intermediate users who are interested in MySQL. It is ideal for those who have basic database knowledge and want to start managing data or building web applications using MySQL.

2. Basic MySQL Commands

2.1 Connecting to and Disconnecting from the Database

To access MySQL, you first need to connect to the database server. One of the most commonly used commands to log in to a MySQL server is mysql -u root -p. When you run this command, the MySQL client starts and attempts to log in as the specified user (in this case, root).

mysql -u root -p

After entering this command, you will be prompted to enter a password. If you enter the correct password, you will be able to access the MySQL command line.

To disconnect, use the exit or quit command.

exit

This logs you out of the MySQL server and returns you to the command prompt.

2.2 Creating and Listing Databases

To create a new database, use the CREATE DATABASE command. The following example creates a database named mysqldemo.

CREATE DATABASE mysqldemo;

After running the command, you will see a “Query OK” message, confirming that the database was created successfully.

To display a list of databases you have created, use the SHOW DATABASES command.

SHOW DATABASES;

This command displays a list of all databases currently available on the server.

2.3 Selecting a Database

If multiple databases exist, you need to specify which one you want to work with. By using the USE command, you can select the database you want to operate on.

USE mysqldemo;

This sets mysqldemo as the current database, and all subsequent commands will be executed against this database.

3. Basic Table Commands

3.1 Creating a Table

To store data in a database, you first need to create a table. Use the CREATE TABLE command to create a new table. For example, if you want to create a table named users, write the command as follows.

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255),
    PRIMARY KEY (id)
);

This command defines three columns (id, name, and email) in the users table. The id column is an integer type with auto-increment (AUTO_INCREMENT) enabled and is specified as the primary key (PRIMARY KEY).

3.2 Listing Tables

After creating tables, you can check the list of tables in the database. By using the SHOW TABLES command, you can display all tables in the currently selected database.

SHOW TABLES;

In addition, if you want to check the structure of a specific table, use the DESCRIBE command. This displays column information and data types in the table.

DESCRIBE users;

This command lists the data types and attributes (such as whether NULL is allowed, key settings, etc.) for each column in the users table.

3.3 Inserting and Viewing Data

To add data to a table, use the INSERT INTO command. For example, to add a new user, do the following.

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

This command inserts a new record into the users table and stores the specified values in the name and email columns.

To view the inserted data, use the SELECT command. To display all users, use the following.

SELECT * FROM users;

This displays a list of all records in the users table.

4. User Management and Security

4.1 Creating Users and Setting Privileges

In MySQL, it is important to create users who can access the database and grant them the appropriate privileges. To create a user, use the CREATE USER command. In the following example, a new user user1 is created for localhost with the password set to password123.

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

This command creates a new user named user1, and the user can only connect from the local host.

To grant privileges to the created user, use the GRANT command. For example, to grant all privileges on the mysqldemo database to user1, use the following.

GRANT ALL PRIVILEGES ON mysqldemo.* TO 'user1'@'localhost';

This command grants user1 full privileges on all tables in the mysqldemo database. To apply privilege changes to the system, run the FLUSH PRIVILEGES command.

FLUSH PRIVILEGES;

4.2 Changing a Password

To change the password of an existing user, use the UPDATE command to update the user table in the mysql database. The following example changes the password for the root user to a new one.

UPDATE mysql.user SET authentication_string = PASSWORD('newpassword') WHERE User = 'root';
FLUSH PRIVILEGES;

This changes the root user’s password to newpassword. By running FLUSH PRIVILEGES, the change is applied to the system.

4.3 Best Practices for Strengthening Security

To improve MySQL security, it is important to follow these best practices.

  • Remove unnecessary anonymous users: Delete the default anonymous users so that only authenticated users can access the database.
  • Disable remote root login: For better security, disable logins by the root user from remote hosts.
  • Use strong passwords: Use strong, hard-to-guess passwords and change them regularly.

By implementing these measures, you can improve database security and prevent potential unauthorized access.

5. Advanced MySQL Commands

5.1 Updating and Deleting Data

To update data in a table, use the UPDATE command. For example, if you want to update the name column in the users table, do the following.

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

This command changes the value of the name column to Jane Doe for the record where id is 1. Be careful: if you omit the WHERE clause, all records in the table will be updated.

To delete data, use the DELETE command. For example, to delete the record where id is 1, use the following.

DELETE FROM users WHERE id = 1;

This deletes the record with id equal to 1 from the users table.

5.2 Backup and Restore

To create a database backup, use the mysqldump command. This command exports the entire database and saves it as an SQL file. For example, to back up the mysqldemo database, use the following.

mysqldump -u root -p mysqldemo > mysqldemo_backup.sql

To restore (recover) a database, use the source command. The following example restores the database from the mysqldemo_backup.sql file.

mysql -u root -p mysqldemo < mysqldemo_backup.sql

This command imports the contents of mysqldemo_backup.sql into the mysqldemo database.

5.3 Starting and Stopping the Server

To start the MySQL server from the command line, use the mysqld command. For example, in a Windows environment, you can run the following.

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld"

To stop the server, use the mysqladmin command.

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin" -u root -p shutdown

This stops the MySQL server properly. Starting and stopping from the command prompt is especially useful in environments where GUI tools are not available.

6. Troubleshooting

6.1 Common Errors and How to Fix Them

One common error that occurs while using MySQL is the “Access denied for user” error. This happens when the specified username or password is incorrect. To fix it, re-check the username and password and try logging in again with the correct credentials.

Another error, “Unknown database”, appears when the specified database does not exist. Use the SHOW DATABASES command to confirm whether the database exists, and create it if necessary.

SHOW DATABASES;
CREATE DATABASE db_name;

6.2 Tips and Precautions for Database Operations

When operating a database, you should pay attention to the following points.

  • Take backups: Before performing database operations, always create a backup. This allows you to restore data if you accidentally delete something.
  • Use transactions: When executing multiple queries as a single operation, use transactions to maintain data integrity. To use transactions, run START TRANSACTION, COMMIT, and ROLLBACK.
  • Specify conditions accurately: When using UPDATE or DELETE, it is important to specify the WHERE clause correctly. This helps prevent unintended updates or deletions.

By keeping these precautions in mind, you can prevent MySQL issues in advance and operate databases safely.

7. Conclusion

The MySQL command line is a powerful tool for efficiently performing everything from basic database management to advanced operations. In this article, we covered essential MySQL commands, including creating databases, working with tables, managing users, and updating or deleting data.

In the world of databases, security and data integrity are extremely important. For that reason, it is necessary to follow best practices such as configuring user privileges, managing passwords properly, and taking regular backups. In addition, troubleshooting knowledge helps you respond quickly when issues occur.

By mastering MySQL commands, you can operate databases more efficiently and securely. Keep practicing and applying what you have learned to become confident in safe and effective database operations.