MariaDB. One of the most popular database servers. Made by the original developers of MySQL
MariaDB Server is one of the most popular database servers in the world. It’s made by the original developers of MySQL and guaranteed to stay open source. Notable users include Wikipedia, WordPress.com and Google.
MariaDB turns data into structured information in a wide array of applications, ranging from banking to websites. It is an enhanced, drop-in replacement for MySQL. MariaDB is used because it is fast, scalable and robust, with a rich ecosystem of storage engines, plugins and many other tools make it very versatile for a wide variety of use cases.
MariaDB is developed as open source software and as a relational database it provides an SQL interface for accessing data. The latest versions of MariaDB also include GIS and JSON features.
Install the MySQL server by using the Ubuntu package manager:
The installer installs MySQL and all dependencies.
If the secure installation utility does not launch automatically after the installation completes, enter the following command:
This utility prompts you to define the mysql root password and other security-related options, including removing remote access to the root user and setting the root password.
If you have iptables enabled and want to connect to the MySQL database from another machine, you must open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application that uses MySQL is running on the same server.
Run the following command to allow remote access to the mysql server:
After the installation is complete, you can start the database service by running the following command. If the service is already started, a message informs you that the service is already running:
To ensure that the database server launches after a reboot, run the following command:
MySQL, by default is no longer bound to ( listening on ) any remotely accessible interfaces. Edit the “bind-address” directive in /etc/mysql/mysql.conf.d/mysqld.cnf:
Restart the mysql service.
There is more than one way to work with a MySQL server, but this article focuses on the most basic and compatible approach, the mysql shell.
At the command prompt, run the following command to launch the mysql shell and enter it as the root user:
When you’re prompted for a password, enter the one that you set at installation time, or if you haven’t set one, press Enter to submit no password.
The following mysql shell prompt should appear:
If you logged in by entering a blank password, or if you want to change the root password that you set, you can create or change the password.
For versions earlier than MySQL 5.7, enter the following command in the mysql shell, replace password with your new password:
For version MySQL 5.7 and later, enter the following command in the mysql shell, replacing password with your new password:
To make the change take effect, reload the stored user information with the following command:
Note: We’re using all-caps for SQL commands. If you type those commands in lowercase, they’ll work. By convention, the commands are written in all-caps to make them stand out from field names and other data that’s being manipulated.
If you need to reset the root password later, see Reset a MySQL root password.
MySQL stores the user information in its own database. The name of the database is mysql. Inside that database the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:
The following list describes the parts of that command:
SELECT tells MySQL that you are asking for data.
User, Host, authentication_string tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case, you are looking for the username, the host associated with the username, and the encrypted password entry.
FROM mysql.user “ tells MySQL to get the data from the mysql database and the user table.
A semicolon (;) ends the command.
Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.
User hosts
The following example is the output for the preceding query:
Users are associated with a host, specifically, the host from which they connect. The root user in this example is defined for localhost, for the IP address of localhost, and the hostname of the server. You usually need to set a user for only one host, the one from which you typically connect.
If you’re running your application on the same computer as the MySQL server, the host that it connects to by default is localhost. Any new users that you create must have localhost in their host field.
If your application connects remotely, the host entry that MySQL looks for is the IP address or DNS hostname of the remote computer (the one from which the client is coming).
Anonymous users
In the example output, one entry has a host value but no username or password. That’s an anonymous user. When a client connects with no username specified, it’s trying to connect as an anonymous user.
You usually don’t want any anonymous users, but some MySQL installations include one by default. If you see one, you should either delete the user (refer to the username with empty quotes, like ‘ ‘) or set a password for it.
There is a difference between a database server and a database, even though those terms are often used interchangeably. MySQL is a database server, meaning it tracks databases and controls access to them. The database stores the data, and it is the database that applications are trying to access when they interact with MySQL.
Some applications create a database as part of their setup process, but others require you to create a database yourself and tell the application about it.
To create a database, log in to the mysql shell and run the following command, replacing demodb with the name of the database that you want to create:
After the database is created, you can verify its creation by running a query to list all databases. The following example shows the query and example output:
When applications connect to the database using the root user, they usually have more privileges than they need. You can add users that applications can use to connect to the new database. In the following example, a user named demouser is created.
To create a new user, run the following command in the mysql shell:
When you make changes to the user table in the mysql database, tell MySQL to read the changes by flushing the privileges, as follows:
Verify that the user was created by running a SELECT query again:
Right after you create a new user, it has no privileges. The user can log in, but can’t be used to make any database changes.
Give the user full permissions for your new database by running the following command:
Flush the privileges to make the change official by running the following command:
To verify that those privileges are set, run the following command:
MySQL returns the commands needed to reproduce that user’s permissions if you were to rebuild the server. USAGE on \*.\* means the users gets no privileges on anything by default. That command is overridden by the second command, which is the grant you ran for the new database.
By default you’ll find MySQL’s configuration files in:
If they’re not there, however, you can ask mysqld where it looks for its config. Run the command:
You’ll get a flood of text back. The first part describes the options you can send to the server when you launch it. The second part is all the configuration stuff that was set when the server was compiled.
What we’re looking for shows up near the start of the output. Find a couple lines that look like:
And there we are. The server works down that list until it finds a configuration file.
With the location in hand, open the my.cnf file and have a look inside.
Any lines starting with “#” are comments, and they mostly document what the different settings are for. They’re good to read through. You’ll find details like the location of log files and where the database files are kept.
Config groups
There are lines in the config file that just contain a word in square brackets, like “[client]” or “[mysqld]”. Those are “config groups” and they tell the programs that read the configuration file which parts they should pay attention to.
See, while we’ve been focusing on the server part of MySQL, it’s technically a collection of tools. That includes the server (mysqld), the client (mysql), and some other tools we’ll talk about in a bit. Those programs look in my.cnf to see how they should behave.
There’s a bit more to it, but basically: The “client” config section controls the mysql client, and the “mysqld” section controls the server config.
If something does go wrong the best place to start troubleshooting any program is its logs. By default MySQL stores its log files in the directory:
You may need to use sudo to get a listing of the files in that directory.
If you don’t find the MySQL logs in the default directory you’ll need to check MySQL’s config. Look in the my.cnf file and look for a “log_error” line, as in:
If you don’t see a line like that, create one in the “mysqld” section so MySQL will use its own error log. We recommend using the location in the example, creating the “/var/log/mysql” directory if it doesn’t already exist. Then restart MySQL to make the change.
Make sure the log directory you choose can be written to by the user controlling the MySQL process (usually “mysql”). The user running the process will be defined in the “user” config value for mysqld in my.cnf.
There might be a “port” setting under both the client and server config sections. The port under the server section controls what port the server will listen to. By default that’s 3306 but you can change it to anything you’d like.
The port in the client section tells the client what port to connect to by default. You’ll generally want the two port settings to match up.
If you don’t see the port entries in the config file that just means they’re using the default. If you want to change the port you would add the lines in the appropriate categories:
The other network setting to look for is the “bind-address” value. That usually gets set to the address for localhost, 127.0.0.1. By binding to localhost the server makes sure no one can connect to it from outside the local machine.
If you’re running your MySQL server on a different machine from your application you’ll want to bind to a remotely-accessible address instead of localhost. Change the bind-address setting to match your public IP address (or, better, a backend IP address on a network that fewer machines can access).
If you don’t see a “bind-address” entry you should put one into the “mysqld” category to help control access to the server:
Remember to account for the client’s hostname when you set up your database users and to poke a hole in your firewall if you’re running iptables.
Behind the scenes there are actually two versions of the MySQL server, “mysqld” and “mysqld_safe”. Both read the same config sections. The main difference is that mysqld_safe launches with a few more safety features enabled to make it easier to recover from a crash or other problem.
Both mysqld and mysqld_safe will read config entries in the “mysqld” section. If you include a “mysqld_safe” section, then only mysqld_safe will read those values in.
By default the mysql service launches “mysqld_safe”. That’s a good thing, and you should only look to change that if you really know what you’re doing.
The mysqladmin tool lets you perform some administrative functions from the command line. We won’t talk much about it here because we’re just trying to get you up and running with enough basics to get by. It’s worth looking at the tool in more depth later to see what it can do, particularly if you need to build scripts that perform functions like checking the status of the server or creating and dropping databases.
You have a few options when it comes to making backups of your databases apart from the usual “back up the whole machine” approach. The main two are copying the database files and using mysqldump.
File copy
By default MySQL creates a directory for each database in its data directory:
Once you’ve found the data directory, hold off a moment before making a copy of it. When the database server is active it could be writing new values to tables at any time. That means if it writes to a table halfway through your copy some files will change and lead to a corrupt backup. Not a good thing if you’re trying to plan for disaster recovery.
To make sure the database files are copied cleanly you can shut the MySQL server down entirely before the copy. That’s safe but isn’t always ideal.
Another approach you can take is to lock the database as read-only for the duration of the copy. Then when you’re done, release the lock. That way your applications can still read data while you’re backing up files.
Lock the databases to read-only by running, from the command line:
To unlock the database when you’re done, run:
We’re using a new option with the mysql client, “-e”. That tells the client to run the query in quotes as if we’d entered it in the mysql shell proper.
Note that if you’re setting these commands up in a script you can put the password in quotes right after “-p” with no space between the two, as in:
Just make sure you set the permissions on that file to restrict read access. We don’t want just anyone to be able to see that password.
mysqldump
Another approach to backing up your database is to use the “mysqldump” tool. Rather than copying the database files directly, mysqldump generates a text file that represents the database. By default the text file contains a list of SQL statements you would use to recreate the database, but you can also export the database in another format like CSV or XML. You can read the man page for mysqldump to see all its options.
The statements generated by mysqldump go straight to standard output. You’ll want to specify a file to redirect the output to when you run it. For example:
That command will tell mysqldump to recreate the “demodb” database in SQL statements and to write them to the file “dbbackup.sql”. Note that the username and password options function the same as the mysql client, so you can include the password directly after “-p” in a script.
Restore from mysqldump
Restoring a mysqldumped database looks similar to what was used to create it, but we use plain old “mysql” instead of “mysqldump”:
We also change from a greater-than to a less-than sign. That switches the command from redirecting its output to telling it to read its input from the existing file. That input is sent to the “mysql” command, causing the mysqldumped instructions to recreate the database.
Note that by default the SQL statements generated would just add to existing database tables, not overwrite them. If you’re restoring a backup over an existing database you should drop the database’s tables first, or drop and recreate the database itself. You can change that behavior by using the “–add-drop-table” option with the command that creates the mysqldump. That causes mysqldump to add a command to the backup files it writes that will drop tables before recreating them.
The MySQL root password allows the root user to have full access to the MySQL database. You must have (Linux) root or (Windows) Administrator access to the Cloud Server to reset the MySQL root password.
Note: The Cloud Server (Linux) root or (Windows) Administrator account password is not the same as the MySQL password. The Cloud Server password allows access to the server. The MySQL root password allows access only to the MySQL database.
Use the following steps to reset a MySQL root password by using the command line interface.
(Ubuntu and Debian) Run the following command:
(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following command:
Run the following command. The ampersand (&) at the end of the command is required.
Run the following command:
Run the following command:
(Ubuntu and Debian) Run the following commands:
(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following commands:
Test the new password by logging in to the database.
Enter your new password when prompted.