Essential MariaDB and MySql Administration Tips on Linux

Last Updated February 19, 2019 by . First Published in 2010.

Introduction to Linux Series
  1. Installing Linux Step by Step
  2. 8 Essential Linux Tips and Tools for Beginners
  3. Beginners guide to Reading and Finding Files in Linux
  4. Using Grep to Search Inside Files for Text in Linux
  5. Understanding Linux File Permissions and Permission Calculator
  6. How to Archive, Compress and Extract files in Linux
  7. Linux Piping and Redirection Explained
  8. Hardlinks and Softlinks in Linux Explained
  9. How to Create and Use Bash Scripts in Linux
  10. Basic Data Recovery in Linux - Recover your Data after Failures
  11. Apache Web Server Administration Cheat Sheet for Linux
  12. Essential MariaDB and MySql Administration Tips on Linux
  13. Complete Guide to Switching from Windows to Linux
Essential MariaDB and MySql Administration Tips on Linux

A collection of useful MariaDB and MySql administration snippets and configuration settings for use on home Linux Systems.

Installing MySQL or MariaDB and Creating Root User

Installation MySql or MariaDB (a fork of MySQL) is as easy as typing in these commands.

sudo apt-get install mysql-server python-mysqldb php-mysql

MySQL 5.7 changed the secure model so that now MySQL root login requires a sudo level execution. This means that root can no longer be used for web logins via PHP. The simplest and safest solution will be create a new user and grant required privileges.

sudo mysql --user=root mysql

Run the following commands, replacing some_pass by the desired password.

sql
CREATE USER 'lonewolf'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'lonewolf'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Now you should be able to login with this user.

MySql to Listen on all ports, not just localhost

If you want remote MySql administration on the server just edit the file /etc/mysql/my.cnf, find and comment out the line bind-address = 127.0.0.1: This will allow any computer access to the mysql server, so it should be used with care.

Restart MySql on Linux

You can restart the MySql server by entering the following command:

sudo /etc/init.d/mysql restart

Reset User Password

From time to time it may be necessary to reset the password of a MySql user. Here's how to do it. You will need to log onto the server using an account with root privileges, either on the MySql Administration GUI or through an application such as phpMyAdmin.

You can reset a password by executing the following SQL statement, after updating it to match your given username and password.

sql
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='MyUsername';
FLUSH PRIVILEGES;

MySql Backup Database

Using the command line you can easily backup a database to a SQL file.

mysqldump -u <username> -p <password>  database_name > backup-filename.sql

MySql Restore Database

Just as easily as backing up a database to a SQL file, you can restore it back to the server using this command:

mysql -u <username> -p <password> database_name < backup-filename.sql

Schedule Daily MySql Backup with Cron

We can add these commands to a crontab job to run at a specific time of the day to create automatic backups.

sudo pico /etc/crontab

Add this line and change accordingly

0 0 * * * root mysqldump -u root -pMyRootPassword --all-databases | gzip > /media/backup/mysql

The 0 0 * * * says run at 0 hours and 0 minutes each day, run the command as root and send the output to gzip in the directory /media/backup/mysql.

Comments

If you enjoyed reading this article, or it helped you in some way, all I ask in return is you leave a comment below or share this page with your friends. Thank you.

There are no comments yet. Why not get the discussion started?

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.