How to Manage Alibaba MySQL Database from CLI

This article teaches you how to handle common MySQL database administrative tasks from the command line (CLI) for databases built on either ApsaraDB for RDS or Elastic Compute Service (ECS) instances.

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:

A valid Alibaba Cloud account.
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.
MySQL master account username and password if you are using ApsaraDB
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 -uroot -p

MySQL connection syntax when connected to ApsaraDB for RDS:

$ mysql -uroot -h-p

Example

$ mysql -uroot -hserver-name.mysql.rds.aliyuncs.com -p

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

MySQL Create Database Command

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

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

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> use test1
Database changed

MySQL Create Table Command

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

create table  (field1 datatype, field2 datatype)

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> CREATE TABLE students (id INT NOT NULL, student_name VARCHAR (30) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

MySQL Show Tables Command

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

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| students        |
+-----------------+
1 row in set (0.01 sec)

MySQL Describe Table Command

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

mysql> describe students;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   |     | NULL    |       |
| student_name | varchar(30) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

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:

Insert into <table name> (column1, column2, column3), values (column1value, column2value, column3value);

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:

mysql> Insert into students (id, student_name) values ('1','John Doe');
Query OK, 1 row affected (0.02 sec)
mysql> Insert into students (id, student_name) values ('2','Richard Roe');
Query OK, 1 row affected (0.01 sec)
mysql> Insert into students (id, student_name) values ('3','Jane Doe');
Query OK, 1 row affected (0.01 sec)

Retrieving Records from MySQL Tables

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

Select column1,column2, ...columnx from tables;

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

mysql> select * from students;    
+----+--------------+
| id | student_name |
+----+--------------+
|  1 | John Doe     |
|  2 | Richard Roe  |
|  3 | Jane Doe     |
+----+--------------+
3 rows in set (0.02 sec)

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:

mysql> select id from students;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

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:

mysql> select * from students where id='2';
+----+--------------+
| id | student_name |
+----+--------------+
|  2 | Richard Roe  |
+----+--------------+
1 row in set (0.01 sec)

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:

SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ASC | DESC];

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

mysql> select * from students order by student_name asc;
+----+--------------+
| id | student_name |    
+----+--------------+
|  3 | Jane Doe     |
|  1 | John Doe     |
|  2 | Richard Roe  |
+----+--------------+
3 rows in set (0.01 sec)

Editing and Updating MySQL Records

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

Update table_name SET column1 = value1, column2 = value2 ... WHERE condition;

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

mysql> Update students set student_name='James Smith' where id='2';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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

mysql> select * from students;
+----+--------------+
| id | student_name |
+----+--------------+
|  1 | John Doe     |
|  2 | James Smith  |
|  3 | Jane Doe     |
+----+--------------+
3 rows in set (0.02 sec)

Deleting a Table Record from MySQL

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

Delete from table_name WHERE column = value

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

mysql> Delete from students where id='3';
Query OK, 1 row affected (0.02 sec)

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:

$ truncate

To delete all records in the students table, type:

mysql> truncate students;
Query OK, 0 rows affected (0.02 sec)

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

mysql> select * from students;
Empty set (0.02 sec)

Modifying Columns in MySQL Table

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

Alter table_name add

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:

mysql> Alter table students add class Varchar (30);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

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.

Alter table  add primary key (column_name);

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

mysql> Alter table students add primary key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

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:

Alter table  modify column  auto_increment

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

Mysql> Alter table students modify column id int auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

mysql> describe students;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(30) | NO   |     | NULL    |                |
| class        | varchar(30) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

Deleting MySQL Database Table

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

drop table table_name;

Deleting a Database

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

drop database database_name;

Example

drop database test1;

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

mysql> Select user, host from mysql.user;
+-----------------+--------------+
| user            | host         |
+-----------------+--------------+
| aurora          | %            |
| aurora_proxy    | %            |
| root            | %            |
| _rds_outer_user | 127.0.0.1    |
| aliyun_root     | 127.0.0.1    |
| mysql.session   | localhost    |
+-----------------+--------------+
6 rows in set (0.01 sec)

Creating MySQL Database Users

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

Create user ''@'' identified by 'PASSWORD';

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

Create user 'example_user'@'localhost' identified by 'PASSWORD';

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

Create user 'example_user'@'%' identified by 'PASSWORD';

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:

GRANT <privileges> ON <property> TO user_name@'<hostname>';
$ FLUSH PRIVILEGES;

You can combine multiple privileges using commas e.g.

GRANT select, insert, delete ON  TO user_name@'';

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

GRANT ALL ON *.* TO '<username>'@'<hostname>';
FLUSH PRIVILEGES;

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

GRANT ALL ON <database_name>.* TO '<username>'@'<hostname>';
FLUSH PRIVILEGES;

Viewing User's Granted Privileges on MySQL

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

show grants for '

Example

mysql> show grants for root;

| Grants for root@%                                                                                                                                                                                                                                                                                              
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-1 row in set (0.01 sec)

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:

Revoke  on  from ''@'';

You can revoke multiple privileges by separating them with commas:

Revoke select, insert, delete on  from ''@'';

To revoke all privileges, use the syntax:

Revoke all privileges on . from ''@'';

Example

Revoke all privileges on sample2.* from 'example_user'@'%';

Deleting MySQL Users

drop user ''@'';

Example

drop user 'example_user'@'%';

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.

Community

How to Manage Alibaba MySQL Database from CLI

Prerequisites

Step 1: Connect to your MySQL instance

Step 2: Managing your MySQL databases on the command line

MySQL Show databases command

MySQL Create Database Command

Switching Between Databases in MySQL

MySQL Create Table Command

MySQL Show Tables Command

MySQL Describe Table Command

Step 3: MySQL CRUD Operations

Creating Records in a MySQL Table

Retrieving Records from MySQL Tables

Filtering Records

Sorting MySQL Records in Ascending or Descending Order

Editing and Updating MySQL Records

Deleting a Table Record from MySQL

Deleting All Records from MySQL Table

Modifying Columns in MySQL Table

Adding a Primary Key to MySQL Table

MySQL Auto Increment Field

Deleting a Database

Step 4: Managing MySQL Database Users

Listing MySQL Database Users

Creating MySQL Database Users

Assigning Privileges to MySQL Users

Viewing User's Granted Privileges on MySQL

Revoking Privileges for MySQL Users

Deleting MySQL Users

Conclusion

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Raja_KT March 3, 2019 at 8:54 am

Alibaba Clouder

Related Products

ECS(Elastic Compute Service)

OSS(Object Storage Service)