Where technology and gadgets come together and play

MySql Administration on Linux

Create your own database server with MySql and Linux

Written By on in Linux

301 words, estimated reading time 2 minutes.

A collection of useful MySql administration snippets for use on Linux Systems. I will be using this as a reference for myself and adding to it from time to time.

Introduction to Linux Series
  1. Linux Tips for Beginners
  2. Beginners guide to Reading and Finding Files in Linux
  3. Using Grep to Search for Text in Linux
  4. Understanding Linux File Permissions
  5. How to Archive, Compress and Extract files in Linux
  6. Linux Piping and Redirection
  7. Linux Hardlinks and Softlinks
  8. Basic Data Recovery in Linux
  9. Essential Guide to Working with Files in Linux
  10. Apache Administration on Linux
  11. MySql Administration on Linux

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 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.

Last updated on: Wednesday 6th December 2017

 

Comments

There are no comments for this post. Be the first!

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me