How to Manage Alibaba MySQL Database from CLI

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.

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

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)

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> 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);
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;
mysql> select * from students;    
+----+--------------+
| id | student_name |
+----+--------------+
| 1 | John Doe |
| 2 | Richard Roe |
| 3 | Jane Doe |
+----+--------------+
3 rows in set (0.02 sec)
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];
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;
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
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
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
mysql> truncate students;
Query OK, 0 rows affected (0.02 sec)
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
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);
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.

Alter table  modify column  auto_increment
Mysql> Alter table students modify column id int auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
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)
drop table table_name;

Deleting a Database

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

drop database database_name;
drop database test1;

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';
Create user 'example_user'@'localhost' identified by 'PASSWORD';
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;
GRANT select, insert, delete ON  TO user_name@'';
GRANT ALL ON *.* TO '<username>'@'<hostname>';
FLUSH PRIVILEGES;
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 '
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 ''@'';
Revoke select, insert, delete on  from ''@'';
Revoke all privileges on . from ''@'';
Revoke all privileges on sample2.* from 'example_user'@'%';

Deleting MySQL Users

drop user ''@'';
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.

--

--

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
Alibaba Cloud

Alibaba Cloud

4.97K Followers

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com