She who rules databases rules the world. Even if you don’t want to rule the world, knowing a good set of database commands will make your life easier.
Most likely you won’t be performing many manual operations on your MariaDB database, such as creating tables and adding data, because it will be manipulated by other programs that use database backends. The following commands are more real-world, and show how to recover a root password, see what is in your database, how to get help, and how to search for a particular text string.
Lost Root Password
When you install MariaDB on Linux you have the option to create a root password. Chances are you immediately forgot it. No worries, because as long as you have Linux root access you can get into MariaDB.
First stop your database if it’s running. On Red Hat Linux, CentOS, and Fedora use the systemctl command:
$ sudo systemctl stop mariadb.service
On Debian, Ubuntu, and Linux Mint you can still use the service command:
$ sudo service mysql stop * Stopping MariaDB database server mysqld
Next, restart MariaDB with the mysqld_safe command, which is the safest way to start MariaDB. --skip-grant-tables starts the server with no user restrictions, so it’s wide open:
$ sudo mysqld_safe --skip-grant-tables --skip-networking & [1] 11278 carla@studio:~/Documents/1articles/linuxcom$ 141029 19:37:57 mysqld_safe Logging to syslog. 141029 19:37:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
The --skip-networking option prevents anyone from sneaking in over the network. Obviously, don’t use this if you’re logging in remotely. Now you can reset the root password by using the mysql command shell. Login to MariaDB, select the mysql database, reset the root password, and then immediately exit:
$ mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 1
Server version: 5.5.39-MariaDB-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> update user set password=PASSWORD("new-password") where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> exit
Bye
Now try logging in with your new password:
$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 2 Server version: 5.5.39-MariaDB-0ubuntu0.14.04.1 (Ubuntu)
Use this one-liner to change the password of any user without logging in to the MariaDB shell:
$ mysqladmin -u carla -p 'old-password' password 'new-password'
Debian’s Back Door
Debian stores the clear-text password for its system MariaDB user in /etc/mysql/debian.cnf:
# Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = LofpsiQCKOcGzoqJ
You can log in with this user and do whatever you want, including reset the root password. The permissions on this file should be read-write for root only.
Seeing What’s Inside
Be careful when you’re monkeying around with your database, because some things need to be there, such as certain system users. Log in, select the mysql database, and list your database users:
$ mysql -u root -p password: MariaDB [(none)]> use mysql; Database changed MariaDB [mysql]> SELECT user, host, password FROM user; +------------------+------------+---------------+ | user | host | password | +------------------+------------+---------------+ | root | localhost | *F6FE8C583C17 | | root | 127.0.0.1 | *F6FE8C583C17 | | root | ::1 | *F6FE8C583C17 | | debian-sys-maint | localhost | *21B2FE94870C | 5 rows in set (0.00 sec)
If you did not set a root password then root’s password field will be empty. Unlike the debian-sys-maint password in the configuration file, these are all encrypted. Why so many root users? Because MariaDB cares about where users make connections from, so root gets all the local ones by default.
You can see all of your databases:
MariaDB [mysql]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | owncloud | | redbooks | | bluebooks | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)
information_schema, mysql, and performance_schema are all internal MariaDB databases. Do not delete or change them, or you will be very sorry. You can look inside them and see all of their table names:
MariaDB [mysql]> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log |
You can view the table structures, which shows all the columns in the tables, and all the variables that can assigned to users:
MariaDB [mysql]> describe user; +------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv| enum | NO | | N | | | Insert_priv| enum | NO | | N | | | Update_priv| enum | NO | | N | |
Now you see how we knew which fields to select when we listed database users. What if you want to see the data in the table? This example shows all of it:
MariaDB [mysql]> select * from user;
This will look like crap because there are so many fields, and you’ll need a giant screen for the output to display correctly. So let’s narrow it down and see who has superuser privileges:
MariaDB [mysql]> SELECT user, super_priv FROM user; +------------------+------------+ | user | super_priv | +------------------+------------+ | root | Y | | root | Y | | root | Y | | root | Y | | debian-sys-maint | Y | | carla | Y | | layla | N | | toshi | N |
Now you know how to see which fields are in a table, and how to filter your searches with them.
Searching MariaDB For Arbitrary Text Strings
One way to search for an arbitrary bit of data is to dumb your databases into a text file, and then search the text file. This example formats the dump file with line breaks, so it is human-readable and grep-able:
$ mysqldump -u user -p --extended-insert=false --all-databases > dbdump.txt
Of course you may dump selected databases:
$ mysqldump -u user -p --extended-insert=false --databases db2 db3 > dbdump.txt
Then grep the dumpfile for your search string, like this search for pinecones:
$ grep -i pinecones dbdump.txt INSERT INTO `forest` VALUES (4,'PINECONE'); INSERT INTO `forest` VALUES (11,'PINECONES'); INSERT INTO `mountain` VALUES (21,'PINECONE');
This tell you which tables your search term is in– forest and mountain in this examples– and lists every occurrence of the search term.
MariaDB has detailed built-in help:
MariaDB [mysql]> help contents You asked for help about help category: "Contents" For more information, type 'help- ', where
- is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation
MySQL isn’t going away, but MariaDB is quickly becoming the default database in Linux distros, and non-Oracle fans are replacing MySQL as fast as they can. Please visit MariaDB.com to read a lot more good documentation, and Moving from MySQL should be helpful to MySQL users who want to make the switch.
