How to Manage Alibaba MySQL Database from CLI

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud’s incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

Alibaba Cloud offers highly-available on-demand MySQL, SQL Server and PostgreSQL databases as part of their ApsaraDB for RDS (Relational Database Service).

RDS is easy to setup and deploy. The service handles all administrative tasks for a database server including provisioning, patching up and recovery in case of a disaster.

Apart from this ready-to-use database service, you can opt to setup your own Alibaba Cloud MySQL database from an Elastic Compute Service (ECS) instance.

Irrespective of your choice, you can manage your Alibaba MySQL databases from a Command Line Interface (CLI). This article teaches you how to handle common MySQL database administrative tasks from the command line.

Prerequisites

Before you begin, make sure you have the following:

  1. A valid Alibaba Cloud account.
  2. An ECS instance running an operating system (e.g. Ubuntu or Centos) and MySQL database server or ApsaraDB for RDS instance. You can choose any of these two configurations.
  3. MySQL master account username and password if you are using ApsaraDB
  4. A non-root user with sudo privileges if you have deployed MySQL database on an ECS instance.

Step 1: Connect to your MySQL instance

First make sure you have created a whitelist group for the IP address of the computer if you are connecting to ApsaraDB for RDS.

If you are running MySQL from an Alibaba ECS instance, you will need to SSH to it using your public IP address of the server.

Irrespective of your connection method and product type (ApsaraDB or ECS instance with MySQL server), the basic syntax for connecting to your MySQL is shown below:

MySQL connection syntax when connected to an ECS instance:

MySQL connection syntax when connected to ApsaraDB for RDS:

Example

Enter your password when prompted and hit Enter to continue:

Step 2: Managing your MySQL databases on the command line

Once you are connected to your MySQL database, you can execute SQL (Structured Query Language) commands to manage your MySQL instance including creating; users, databases, tables, columns/fields. You can also perform CRUD (Create Read Update and Delete) operations on any database table.

MySQL Show databases command

You can run the command below to view the available databases on your Alibaba MySQL server;

MySQL Create Database Command

To create a database on your Alibaba MySQL server, use the command below:

Remember to replace ‘sample2’ with the preferred name of your database.

Switching Between Databases in MySQL

You can switch from one database to another one in MySQL via the ‘use’ command. For instance, to switch to the database that you created above, use the command below:

MySQL Create Table Command

Once you switch to a database, you can create tables using the syntax below;

MySQL supports a wide range of data types and you can refer to a complete list here

You should use the correct data type depending on the needs of your application.

For instance, to create a table named students with two fields (student_id and student_name), we can use the syntax below:

MySQL Show Tables Command

To view all tables from the selected database, use the syntax below:

MySQL Describe Table Command

To get the structure of the table that you have created above, you can use the below SQL statement;

Step 3: MySQL CRUD Operations

You can manage records on a MySQL database table via the command line. CRUD is an acronym for Create, Read, Update and Delete.

Creating Records in a MySQL Table

To insert a record in your database table, use the syntax below:

Please note the number of columns and values should match.

For instance, to insert 3 students in our students table, we can use the below SQL commands:

Retrieving Records from MySQL Tables

Retrieving records is a part of a MySQL CRUD operation. To read records, use the select statement.

For instance, you can retrieve all students and all columns from the students table using the command below:

Sometimes, you may want only to retrieve certain columns from a table. For instance, to retrieve the ids of students without their names, we can use the syntax below:

Filtering Records

You can filter records when retrieving them using the ‘where’ clause. For instance, you can retrieve only the student with id ‘2’ using the below syntax:

Sorting MySQL Records in Ascending or Descending Order

When retrieving records from your Alibaba MySQL database, you might feel the need to sort them either by ascending or descending order via the syntax below:

Using the student’s table above, we can arrange students by their names alphabetically;

Editing and Updating MySQL Records

In a production environment, editing and updating MySQL records is inevitable. To do this, use the commands below:

For example, you can update student id ‘2’ and change the name to James Smith using the SQL command below:

If you run the select statement one more time, you will see that the student name was updated successfully:

Deleting a Table Record from MySQL

If you want to delete a record completely from the table, use the below SQL syntax:

For instance, you can delete student with id number 3 using the command below;

Deleting All Records from MySQL Table

If you have a lot of records and you want to wipe the entire table in a single command, use the truncate statement:

To delete all records in the students table, type:

If you try to retrieve the records again, MySQL will display an empty set:

Modifying Columns in MySQL Table

You can add a column to an existing table using the below syntax:

For instance, to add a column named ‘class’ with Varchar as the data type and a length value of 30 to the student’s table, use the command below:

Adding a Primary Key to MySQL Table

To uniquely identify each record in a table, you should add a primary key(PK). You should add it to the most unique column with no possibility for duplicates.

For instance, in our students table, the id field is unique because no two students can share the same identification number.

MySQL Auto Increment Field

You can instruct MySQL to assign sequence numbers automatically to a column using the Auto Increment feature. For instance, students can be assigned an id value when they are being admitted in a school. This will be done automatically once they are added in the database table.

MySQL auto increment has the below syntax:

To assign this to the students table, type the command below:

You can check the new structure of the table using describe statement to confirm the changes:

Deleting MySQL Database Table

If you no longer need a table on your database, use the command below

Deleting a Database

To delete a database completely from your MySQL server, use the command below

Example

Please note: Deleting a table or database is irreversible; you should backup your database first to avoid deleting the wrong property especially if you are in a production environment

Step 4: Managing MySQL Database Users

You can manage multiple users and assign them different privileges depending on the roles that you want them to perform on your database.

Listing MySQL Database Users

You can view the available users on your Alibaba MySQL database by typing the command below

Creating MySQL Database Users

To create a MySQL database user, use the syntax below:

Remember to replace the ‘example’ with your username and use a strong value for the Password.

If you want the user to connect from any host, use ‘%’ in place of localhost. For example;

Assigning Privileges to MySQL Users

Privileges are permissions that allow a user to perform certain tasks in a database table. To assign a privilege to a user, use the syntax below:

You can combine multiple privileges using commas e.g.

To assign all privileges to a user, use the syntax below:

To assign privileges for a MySQL user to a specific database use the syntax below:

Viewing User’s Granted Privileges on MySQL

To view granted privileges assigned to a user, use the syntax below:

Example

Revoking Privileges for MySQL Users

You can always revoke privileges for a MySQL user if you assigned permissions erroneously or if you no longer wish the user to do certain tasks on the MySQL server. To do this , use the commands below:

You can revoke multiple privileges by separating them with commas:

To revoke all privileges, use the syntax:

Example

Deleting MySQL Users

Example

Conclusion

In this guide, we have taken you through the steps of managing your MySQL databases and users hosted on Alibaba Cloud. The MySQL Command Line Interface is quite easy to use if you understand the correct syntax for Structured Query Language. This guide works for both ApsaraDB and ECS-deployed databases.

Also, familiarizing yourself with MySQL CLI commands allows you to troubleshoot MySQL database related problems for your website or applications even when you don’t have a GUI(Graphical User Interface) installed on your computer.

Reference:

https://www.alibabacloud.com/blog/how-to-manage-alibaba-mysql-database-from-cli_593981?spm=a2c4.12011662.0.0

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store