A beginner's guide to MySQL / MariaDB
A basic tutorial on how to take control of a MySQL / MariaDB server, create a database and a user, and store data in a MySQL database.
October 20, 2015
Normally, you can complete all tasks with Sequel Pro, MySQL Workbench, HeidiSQL, DBeaver, or similar software, but there are times when you need to know how to use basic MySQL commands to complete your tasks.
MySQL / MariaDB installation on Ubuntu, Debian, and CentOS
If MySQL is not already installed on your server, you can quickly download it. Getting the correct packets depends on your system, but it’s very simple if you’re running Debian / Ubuntu or CentOS. When the system prompts you to enter a password for the root account, use a complex one. Use a different password for your server’s root / superuser account.
Debian and Ubuntu
sudo apt-get install mysql-server
sudo yum install mysql-server
Now you should have the MySQL server up and running.
You can start, restart and stop the server easily:
sudo /etc/init.d/mysql restart | stop | start
How to access the MySQL / MariaDB server
For creating and managing databases and users, I prefer a graphical interface, but you can also connect to the MySQL server by typing the following command into terminal:
mysql -u username -p
If this is your first time logging in, you probably don’t have any other users other than root, and the password for that user is whatever you entered when you installed the MySQL server.
Remember that all MySQL commands must be followed by a semicolon (;). Without it, the command will not run.
MySQL commands are typically written in uppercase, while everything else, such as database and user names, is written in lowercase to make them easier to distinguish. The MySQL command line, on the other hand, is not case sensitive, so you can type everything in lowercase if you prefer.
How to create, access and delete a MySQL / MariaDB Database
Let’s create a test database. Log in as root to the MySQL server and type:
CREATE DATABASE test;
CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
If you want to know what databases are available:
The command above shows all the databases that are created in the MySQL server.
After creating a database, you can start using it and store information in it. Open the database by typing:
You are now in the database and have the ability to create tables. Keep in mind that table and database names are case sensitive.
How to create a table inside of the database
Let’s call this table test. Every table must have a
PRIMARY KEY, and in this case, the key is
AUTO INCREMENT, which means that each row is automatically numbered. Because it identifies rows, the key must be unique.
A name and birthday will also be included.
VARCHAR is used for the name, and
DATE is used for the birthday. MySQL requires dates to be formatted as
CREATE TABLE test (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Now you can see what you just did by typing:
How to add information to a MySQL table
Use this format to insert information into each row:
INSERT INTO 'test' ('id','name','food','confirmed','signup_date')
VALUES (NULL, "Max", '1977-07-17');
If you want to see what table has been eating:
SELECT * FROM test;
You can also update existing data:
UPDATE 'test' SET 'bday' = '1981-07-17' WHERE 'test'.'name' ='Max';
How to delete a database
When the database is no longer needed you can drop it.
DROP DATABASE test;
How to use mysqldump to get backups or schema
I typically use the
mysqldump command to backup databases, but it can also extract a schema without data. I use it occasionally when saving initial settings to Github.
mysqldump -u youruser -p yourdatabase
-u flag specifies the username, while the ‘-p’ flag specifies that a password will be supplied. You will be prompted for your password after pressing
The password can also be supplied on the command line, but there must be no space between the
-p flag and the password. For instance, if the password was
tuspe, do the following:
mysqldump --d -u youruser -ptuspe yourdatabase
-d flag says not to include data in the dump. Alternatively you can use
–no-data instead if you find that easier to remember:
mysqldump -d -u youruser -p yourdatabase
mysqldump --no-data -u youruser -p yourdatabase
How to create a new MySQL / MariaDB user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
There should be only one database per user. It is more secure.
MySQL user permission
The following command grants the user full access to the specified database, but the user cannot grant or take privileges from other users. The user has the ability to manage the database, create tables and data, and delete everything. This is the most common method of granting database access.
GRANT ALL PRIVILEGES ON test.* TO 'newuser'@'localhost';
If you really want to, you can grant the user superuser privileges using the grant option. When working with CMS or apps, this is not recommended.
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;
If you do not want to grant all rights to the database or table, you can grant specific permissions.
GRANT [type of permission] ON [database name].[table name] TO 'username'@'localhost';
You can also revoke some rights if the user no longer requires them:
REVOKE [type of permission] ON [database name].[table name] FROM 'username'@'localhost';
You should flush privileges after making any changes.
Change user password
ALTER USER 'timo'@'localhost' IDENTIFIED BY 'newpassword?';
Delete a MySQL / MariaDB user
DROP USER 'demo'@'localhost';
mysqlcheck -u username -p --optimize --all-databases
Thank you for your time!