Tux Training

Basics of MySql on the Commandline #2

Posted on March 15th, 2008 in Basics, Mysql, Servers by admin

Please read Basics of MySql on the Commandline #1 first. In this MySql basics tutorial we will be covering a number of items. How to backup databases, delete tables, delete databases, and how to restore databases.

(Video to Come)

Backing Up Databases

Lets go ahead and gain root access with su (if on ubuntu and debian use sudo for every linux command (not for mysql commands).

Do a pwd to make sure what directory you are currently working in. After that lets go ahead and make a directory to store our backups.

mkdir mysqlbackups

Now type the following to perform a backup

mysqldump -u root -p test_database1 > /path/to/mysqlbackups/test_database1.sql

Note: the /path/to/ simply symbolizes the path pwd displayed to show where the mysqlbackups directory was when you created it.

Here’s an explanation of this command string. The mysqldump command does exactly what it says, it connects to the MySQL server, selects a database, then dumps all the information from it into a text file. The -u command tells mysqldump to use the MySQL root user account to connect to the MySQL server. The -p command tells MySQL to prompt the user for a password.

test_database1.sql is the name of the database you want to back up. The > character is called a “pipe,” and is a Linux command. Pipe is an apt name for what > does: it pipes, or places, the information provided by mysqldump into a file. /path/to/mysqlbackups/test_database1.sql is the location and filename in which we’re putting our mysql database.

If you were to type the command:

more /path/to/mysqlbackups/test_database1.sql you should be able to browse the contents of the text document the mysql dump created.

Deleting Tables

Type: mysql -u root -p test_database1

and enter the password which should take you to your mysql prompt.

At the mysql> prompt, type:

DROP TABLE name;

then press ENTER.

Type: SHOW TABLES;

then press ENTER.

The table name has been dropped, or deleted, from the test_database1 database:

Delete Databases

Type:

DROP DATABASE us_presidents; then press ENTER.

Type:

SHOW DATABASES; then press ENTER

As you can see, the databases test and mysql are there but our test_database1 is currently gone.

Restore Databases

Type:

CREATE DATABASE test_database1;

then press ENTER.

The database has been restored, but is empty. There are no tables or data in it.

Type:

\q;

Then press ENTER. This closes the MySQL client connection. You are closing the connection so you can use a Linux command line pipe ( > ) to restore the database.

Type:

mysql -u root -p test_database1.sql </path/to/mysqlbackups/test_database1.sql

then press ENTER.

This restores the data in the database test_database1.sql from the backup.

Type:

mysql -u root -p

then press ENTER and then enter your password.

Type:

USE test_database1.sql;

then press ENTER

Type:

SHOW TABLES;