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.
Hazem 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
- Open Terminal on Your macOS Machine.
- 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.
