Tutorial: Accessing MySQL Database and Exporting Data on Linux

This tutorial will guide you through accessing a MySQL database using the MySQL client CLI on a Linux server, listing all databases, accessing a specific database, showing all tables, counting records in a table, exporting a table using mysqldump, and finally, downloading the exported SQL file to a local macOS machine using scp.

22 Free MySQL/ MariaDB Database Backup Tools and Scripts

MySQL is a popular open-source relational database management system (RDBMS). It is widely used for web-based applications and is known for its ease of use and scalability. MySQL is used by many popular websites, including Facebook, Twitter, and YouTube. MySQL Backup is the process of creating a copy of the

MEDevel.com: Open-source for Healthcare, and EducationHazem Abbas

Step 1: Access MySQL Database Using MySQL Client CLI

Open Terminal on Your Linux Server.

Log in to the MySQL Database:To access the MySQL database, use the mysql command with the -u flag for the username and the -p flag to prompt for a password. Replace username with your MySQL username.

mysql -u username -p

After running this command, you will be prompted to enter your password.

Step 2: Show All Databases

Once logged in, you can list all databases available on the server.

SHOW DATABASES;

Step 3: Access a Specific Database

To access a specific database, use the USE statement followed by the database name. Replace database_name with the actual name of the database you want to access.

USE database_name;

Step 4: Show All Tables in the Database

After accessing the desired database, you can list all tables within it using the following command:

SHOW TABLES;

Step 5: Get Count of Records in a Table

To get the count of records in a specific table, use the SELECT COUNT(*) statement. Replace table_name with the actual table name.

SELECT COUNT(*) FROM table_name;

Step 6: Export the Table Using mysqldump

Exit the MySQL client by typing exit and hitting Enter.

Now, export the specific table to an SQL file using mysqldump. Replace username, database_name, table_name, and password with your MySQL credentials and details.

mysqldump -u username -p database_name table_name > table_name.sql

After entering the command, you will be prompted to enter your MySQL password.

Step 7: Download the SQL File to Local macOS Machine Using scp

1. Open Terminal on Your macOS Machine.
2. Enter Your Server Password:You will be prompted to enter your Linux server password. Once entered correctly, the file will transfer to your macOS machine.

Use the scp Command to Download the File:Replace server_username with your Linux server username, server_ip with your server’s IP address, and adjust the file paths as necessary.

scp server_username@server_ip:/path/to/table_name.sql /local/path/on/macos

For example, if the server username is user, the server IP address is 192.168.1.100, the SQL file is located in the home directory, and you want to save it to the desktop on your Mac, the command would be:

scp user@192.168.1.100:~/table_name.sql ~/Desktop/

Final Note

Following these steps, you will have successfully accessed the MySQL database on your Linux server, exported a specific table to an SQL file, and downloaded it to your macOS machine using scp. This workflow is useful for backing up database tables or migrating data between servers and local environments.