Purchase Linux CDs / DVDs / Flash Drives at OSDisc.com

Welcome to Our Community

While Linux.org has been around for a while, we recently changed management and had to purge most of the content (including users). If you signed up before April 23rd please sign up again. Thanks!

MySQL administration via command line

Discussion in 'Linux Other' started by Rob, May 10, 2017.

  1. Rob

    Rob Administrator
    Staff Member

    Joined:
    Oct 27, 2011
    Messages:
    147
    Likes Received:
    397
    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
     

Share This Page