Mastering MySQL Command Line: Commands, Security, and Troubleshooting│MySQL & MariaDBの世界

1. Introduction

1.1 Overview of MySQL and the Importance of the Command Line

MySQL is widely used as an open-source relational database management system (RDBMS). Its primary advantages include efficient data management and flexible data manipulation using SQL (Structured Query Language). It’s used in many web applications and enterprise systems, and its powerful features are leveraged to their fullest extent through the MySQL command line.

1.2 Purpose of This Article

This article focuses on MySQL command line operations, covering everything from basic command usage for database creation, management, and user privilege settings to more advanced topics. It aims to equip beginners and intermediate users with practical knowledge to effectively use MySQL.

1.3 Target Audience

This guide is intended for beginners to intermediate users interested in MySQL. It is ideal for those who have basic database knowledge and are looking to use MySQL for data management or web development.

2. Basic MySQL Commands

2.1 Connecting to and Disconnecting from a Database

To access MySQL, you must first connect to the database. The most commonly used command to log in to the MySQL server is mysql -u root -p. Executing this command starts the MySQL server and attempts to log in with the specified user (in this case, root).

mysql -u root -p

After entering this command, you will be prompted to enter your password. Entering the correct password will grant you access to the MySQL command line.

To disconnect, use the exit or quit command.

exit

This will log you out of the MySQL server and return you to the command prompt.

2.2 Creating and Viewing Databases

To create a new database, use the CREATE DATABASE command. The following is an example of creating a database named mysqldemo.

CREATE DATABASE mysqldemo;

Executing the command will display a “Query OK” message, confirming that the database was created successfully.

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

SHOW DATABASES;

This command shows a list of all databases currently residing on the server.

2.3 Selecting a Database

If multiple databases exist, you need to specify the database you want to work with. You can select the current working database using the USE command.

USE mysqldemo;

This makes the mysqldemo database the current target for operations, and subsequent commands will be executed against this database.

3. Basic Table Operations

3.1 Creating a Table

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

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

In this command, three columns (id, name, email) are defined for the users table. The id column is an integer type with auto-increment enabled (AUTO_INCREMENT) and is designated as the primary key (PRIMARY KEY).

3.2 Viewing Tables

Once you’ve created tables, you can view a list of them. The SHOW TABLES command displays all tables within the currently selected database.

SHOW TABLES;

Furthermore, if you want to check the structure of a specific table, use the DESCRIBE command. This shows column information and data types within the table.

DESCRIBE users;

This command lists the data types and attributes (NULL allowance, key settings, etc.) for each column in the users table.

3.3 Adding and Viewing Data

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

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

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

To view the added 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 appropriate privileges. To create a user, use the CREATE USER command. The example below creates a new user user1 at localhost and sets their password to password123.

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

This command creates a new user named user1, and this user can only access from localhost.

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

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

This command gives user1 full privileges on all tables within the mysqldemo database. To apply privilege changes to the system, execute the FLUSH PRIVILEGES command.

FLUSH PRIVILEGES;

4.2 Changing Passwords

To change the password for an existing user, use the UPDATE command to update the user table in the mysql database. Below is an example of changing 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 password for the root user to newpassword. Executing FLUSH PRIVILEGES applies the changes to the system.

4.3 Best Practices for Enhancing Security

To enhance MySQL security, it is crucial to follow these best practices:

Remove unnecessary anonymous users: Delete default anonymous users so that only authenticated users can access the database.
Disable remote root login: For increased security, disable remote login by the root user.
Use strong passwords: Use strong, unpredictable passwords and change them regularly.

Implementing these measures will improve database security and help 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, you would 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 not to update all records in the table by accidentally omitting the WHERE clause.

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

DELETE FROM users WHERE id = 1;

This deletes the record with id 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 to an SQL file. For example, to create a backup of the mysqldemo database, you would do the following:

mysqldump -u root -p mysqldemo > mysqldemo_backup.sql

To perform a restore, use the source command. Below is an example of restoring a 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 would do the following:

"C:Program FilesMySQLMySQL Server 5.7binmysqld"

To stop the server, use the mysqladmin command.

"C:Program FilesMySQLMySQL Server 5.7binmysqladmin" -u root -p shutdown

This shuts down the MySQL server gracefully. Starting and stopping from the command prompt is particularly useful in environments where a GUI tool is not available.

6. Troubleshooting

6.1 Common Errors and Solutions

One common error encountered when using MySQL is the “Access denied for user” error. This occurs when the specified username or password is incorrect. To resolve this, recheck the username and password and attempt to log in using the correct information.

Additionally, the “Unknown database” error is displayed when the specified database does not exist. Use the SHOW DATABASES command to verify if the database exists, and create the database if necessary.

SHOW DATABASES;
CREATE DATABASE db_name;

6.2 Important Notes and Tips for Database Operations

When performing database operations, it is important to pay attention to the following points:

Take backups: It is crucial to always take a backup before performing database operations. This allows for restoration even if data is accidentally deleted.
Use transactions: When executing multiple queries as a single operation, use transactions to maintain data integrity. To use transactions, utilize the START TRANSACTION, COMMIT, and ROLLBACK commands.
Specify accurate conditions: When using UPDATE or DELETE commands, it is important to accurately specify the WHERE clause. This prevents unintended modification or deletion of records.

By keeping these points in mind, you can prevent MySQL troubles and perform database operations safely.

7. Conclusion

The MySQL command line is a powerful tool for performing database management and operations efficiently, from basic to advanced tasks. This article has explained how to use basic MySQL commands, covering database creation, table operations, user management, and data updating and deletion.

In the world of databases, security and data integrity are extremely important. Therefore, it is necessary to adhere to best practices such as setting user privileges, managing passwords, and taking backups. Having troubleshooting knowledge also allows for quick response when problems occur.

By mastering MySQL commands, you can perform database operations more efficiently and safely. Through practice and applying the knowledge you’ve gained, you will be able to perform efficient and secure database operations.

Mastering MySQL Command Line: Commands, Security, and Troubleshooting