Essential MariaDB and MySql Administration Tips on LinuxA collection of useful MariaDB and MySql administration snippets and configuration settings for use on home Linux Systems.
This article is part of a series of articles. Please use the links below to navigate between the articles.
- How to Download and Installing Linux Step by Step For Beginners
- Essential Guide to Working with Files in Linux
- Understanding Linux File Permissions and Permission Calculator
- How to Archive, Compress and Extract Files in Linux
- Linux Piping and Redirection Explained
- Hardlinks and Softlinks in Linux Explained With Examples
- How to Create and Use Bash Scripts in Linux
- Data Recovery in Linux - How To Recover Your Data after Drive Failures
- Apache Web Server Administration Cheat Sheet for Linux
- Essential MariaDB and MySql Administration Tips on Linux
- How to Switching from Windows to Linux - A Complete Guide
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 to create a new user and grant the required privileges.
sudo mysql --user=root mysql
Run the following commands, replacing some_pass with the desired password.
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 log in 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.
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='MyUsername';
FLUSH PRIVILEGES;
MySql Backup Database
Using the command line you can easily back up 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 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 it 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
.