By reading this guide I assume you have already setup your server with working web server (e.g: MySQL, Nginx, etc), MySQL server and PHP. Whatever stack and Distro you are using, I believe the command is pretty similar. In this page you’ll see top most used and common MySQL commands to manage an unmanaged server. Remember here that each time I say “server”, it refers to both VPS and Dedicated Linux-based server. I don’t and I never will to write any tutorial about Windows-based server because that’s simply out of my expertise.
My personal goal and the purpose of this blog is to provide practical guide how to build a live server where you can host your files and database in it so I will not again explain what is MySQL. Also, these what you’ll see below are simply practical which it means all the basic things you’ll need so your website or blog can go online quickly. So don’t expect to see the way how to add entries and queries in tables. You can simply do that easier using PhpMyadmin.
1. Basic MySQL Command
You can launch MySql screen by calling it using this simple command:
mysql -u MYSQLUSER -p
**replace “MYSQLUSER” with your own user. If you are the first time using MySQL and/or never created new user before, simply use “root” to login. MySQL user account is different from UNIX/Linux login account. You have to create it first and I’ll tell you that below.
Type that command then hit enter and you should be asked to enter password. Once you are in MySQL environment, you can go out from there by using “\q” or type “quit” command.
mysql -u root -p
2. Create New Database
How to create new MySQL database via SSH? You have to enter MySQL screen and login, use command in step 1 above. Once you are logged in, type this to add new database in MySQL:
CREATE DATABASE databasename;
**change databasename with your own database name. You better not use space in the name. In this example I use “myblog”:
If you wish to use space you can use underscore e.g: wp_456.
3. Create New MySQL User and Grant Access To Database
The most common syntax used to do that task is:
GRANT ALL ON databasename.* TO sqluser@localhost IDENTIFIED BY 'password';
**change databasename, sqluser and password with your own. For example
GRANT ALL ON myblog.* TO sawiyatidb@localhost IDENTIFIED BY 'pass1234';
In that example I create “sawiyatidb” as my user for database named “myblog” while pass1234 is the password for user sawiyatidb. The command “GRANT ALL” means to assign all privileges to that user but can only access (read, delete, modify or add data) in that specific database, not another.
Please do note down, remember or paste username and password to Notepad. You’ll need this to connect your scripts (e.g: WordPress) to MySQL database. Otherwise your script will not be able to establish connection to database. Those three values are the most important credentials to allow your scripts getting access to database.
4. Other Most Common Command Syntax
Q: Which version is the MySQL server installed?A: You can find out which version is the MySQL currently installed on your server by using this simple command:
mysqladmin -u root -p version
You’ll need to enter password.
Q: I want to see all databases. How to list database?
A: You can retrieve all SQL database name using this command:
If you notice, all commands executed in MySQL are having “;” suffix. It means you have to login to MySQL first.
Q: Yes I can see that now. Then how to see tables of certain database?
A: To do that you have to use / select the database name you wish to see the list of existing tables there. In that matter simply use this:
then type this command:
There is no table from example above because the “myblog” database is newly created.
Q: How to connect and access remote MySQL server?
A: Yes you can do that via SSH and your server. But obviously you have to know the username and password to login to that remote MySQL server then use this command:
mysql -u mysqluser -h mysql.server.com -p databasename
– mysqluser is the username of remote MySQL, change this.
– mysql.server.com is the host location, change this.
– databasename is the database name, change this.
That’s all. I believe above commands are the most common MySQL command syntax you should know as the basic skill to build your own server for hosting your website or blog. There are several other command syntax combination but for me, the ones above are enough. If I need more advanced tasks I prefer installing PhpMyAdmin.