MySQL is one of the most popular relational database management systems. There are a variety of ways to manage a MySQL server – countless frontends including PhpMyAdmin, MySQL Workbench and even Adminer.
However, clearly the most efficient way to manage a MySQL server is directly through command line.
The mysql command-line tool comes with the MySQL DBMS and is a simple SQL shell solution with input line editing capabilities. It supports both non-interactive and interactive use.
In case of interactive use query results are presented in an ASCII-table format. In situation of non-interactive use the result is presented in tab-separated format. The output format can be changed using command options.
First you will need have to access your server via SSH in case of Linux. To connect to Windows server you need to use Remote Desktop. It is also possible to access your MySQL database via direct connection.
To connect to MySQL from the command line, follow these steps:
1. Once the command line is available, enter the following, where USERNAME is your database username:
mysql -u USERNAME -p
2. You’ll be prompted for a password, enter your password. You should then see the “mysql>” prompt.
3. You can see a list of databases by typing in this command:
show databases;
4. To perform database tasks you need to tell MySQL which database you want to use. To access a particular database, type the following command, where DBNAME is the database you want to access:
use DBNAME;
5. Once you’ve run this command you have access to your database. Next, you can execute queries, get a listing of MySQL tables and much more. By the way, typing “help” will get you a list of commands you can use, while typing “\q” will get you out of MySQL.
Adding new users and creating a new database
You can add users and databases when you are logged in as the root user in MySQL. To log in as root, and create new users and databases, follow these steps:
1. Log into MySQL with root privileges using the following command:
mysql -u root -p
2. You’ll be prompted for your root password, fill it in and press return to proceed.
3. Creating a database is straight forward, you just need the following command. When entering it, replace username with your user you would like to add. Do that with password too. This is the command you need to enter:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
4. Note that the command we listed above will give the new user you create blanket permissions. Instead of granting all privileges you can limit the user. One example is the SELECT permission. To limit the user to permissions as specified by SELECT only you need to enter the following command:
GRANT SELECT ON *.* TO 'username'@'localhost';
5. Type \q to exit MySQL so that you can log in with the user that you have just created.
6. It’s simple to log in to MySQL with your new user, just enter the login command we specified previously, typing the new user’s name instead of Username:
mysql -u username -p
7. The user we create have all privileges assigned to them, including the ability to create a database. Create a database with the following command, using the name of your database instead of DBNAME.
CREATE DATABASE dbname;
8. Want to start using this newly created database? Run the following command, again replacing DBNAME with the name of your database:
USE dbname;
9. MySQL now knows which database you want to work with. You can create a database table by running the following command, for example:
CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(50) not null, constraint pk_example primary key (id) );
INSERT INTO example ( id, name ) VALUES ( null, 'Sample data' );
Scripting in SQL
You don’t need to run every single SQL command one command at a time, as in our previous example. Instead you can execute several commands all in one go by making use of a SQL script file.
This is how you use a SQL script file to create a new database, and to populate your new database:
1. Just like we did in the previous example we start by creating a new user for your database. These are the commands you need:
mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
\q
2. Next, create a new file on your computer, and call it sql. You can use any text editor you like to use, as long as the file is stored in plain text.
3. Copy and paste the following into your text file:
CREATE DATABASE dbname;
USE dbname;
CREATE TABLE tablename ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO tablename ( id, name ) VALUES ( null, 'Sample data' );
4. Before you save your file, change dbname into the name of your database and tablename into the name of the table you want to add to your new database.
Remember, you can modify this script to make as many tables as you like, and even to create multiple databases all in one go. Note that our example creates a very basic table: you might want to add more complex requirements by expanding on the script.
5. Save the changes to your file and close your text editor.
6. Processing your script is easy. Type the following command in, replacing username with the name of the user you created in the first step.
mysql -u username -p < example.sql
Note that MySQL will execute commands in a script one line at a time, in other words one statement at a time. In our example file, once MySQL has finished executing the entire file you will notice that a new database and a new table is created. Finally, the table will contain any data that you have specified in the INSERT statement.
Deleting a specific table, or an entire database
Performing a table deletion is not hard. Just type this command once you are logged into MySQL, using the name of the table you want to delete instead of tablename.
DROP TABLE tablename;
Note that to execute this command you need to have already selected which database you are working on via the use command.
Deleting a complete database is simple too. Just execute this command at the MySQL prompt, again replacing dbname with the name of the database you want to remove:
DROP DATABASE dbname;
Beware: MySQL will immediately remove the database when you submit that command, it does not ask for any type of confirmation: the database is permanently removed, including all of its contents.
Deleting a database user
You can view a full list of every database user by running the following command:
SELECT user FROM mysql.user GROUP BY user;
Deleting a single user is simple, just run the following command, but insert the name of the user you want to delete in the place of username:
DELETE FROM mysql.user WHERE user = 'username';
with
No comment yet, add your voice below!