MySQL administration via command line

Rob

Administrator
Staff member
Joined
Oct 27, 2011
Messages
1,210
Reaction score
2,240
Credits
3,485
When using MySQL on a Linux server, you'll no doubt come into a situation where you'll want to manipulate something via command line. Some of you will have tools like PHPMyAdmin installed, but you should always know a way to do things via command line. By the way, these examples will also work with MariaDB.

Let's lay out some common things you'll come across.
  • Backup mysql database via command line
  • Import mysql database via command line
  • Drop (delete) mysql database via command line
  • Repair / optimize mysql database via command line
We'll go through these in order. I'm assuming you're already connected via ssh to your MySQL server.

Our DB info for these examples
mysql user: linuxorg_user
mysql user password: p4ssword
mysql database: linuxorg_db

Backup MySQL database via command line
When you backup a mysql database via command line, it will create a text file with everything you'll need to create that database again. We'll name that text file linuxorg_db.sql
Code:
mysqldump -u linuxorg_user -p linuxorg_db > linuxorg_db.sql
It will then ask you for your password (p4ssword) and backup the database to linuxorg_db.sql.

Import MySQL database via command line
Once you have a valid mysql dump file (linuxorg_db.sql), you can import it into an empty database easily and quickly with the following command. Again, it will ask you for your password.
Code:
mysql -u linuxorg_user -p linuxorg_db < linuxorg_db.sql

Drop MySQL database via command line
If you're done with a certain database on your server, you can drop (delete) it with this command. It will ask for your password.
Code:
mysql -u linuxorg_user -p -e 'drop database linuxorg_db'

Repair / optimize mysql database via command line
Sometimes you have a crashed database table which results in a badly performing application. You can easily repair and optimize all of the tables in your database with this command. It will ask you for your password.
Code:
mysqlcheck -u linuxorg_user -p --auto-repair --optimize linuxorg_db
 

Members online


Latest posts

Top