Ubuntu SQLAdmin数据导入导出操作

Note: SQLAdmin is typically a web-based database management tool (e.g., for MySQL/MariaDB). The following steps focus on using command-line tools (like mysqldump) for data import/export—common methods in Ubuntu environments. If you specifically need SQLAdmin web interface steps, refer to tool-specific documentation.

Preparations

1. Install Database Tools: Ensure mysqldump (for MySQL/MariaDB) is installed. For MySQL:

   sudo apt update && sudo apt install mysql-client
   

   For MariaDB:

   sudo apt install mariadb-client
   

2. Backup Existing Data: Always back up databases before operations to avoid data loss. For all databases:

   sudo mysqldump --all-databases > /backup/mysql/all_databases.sql
   

   For a specific database (e.g., mydb):

   mysqldump -u username -p mydb > mydb_backup.sql
   

Exporting Data

Export an Entire Database

Use mysqldump to create a SQL file with the database structure and data:

mysqldump -u username -p database_name > backup_file.sql

• Replace username with your database user, database_name with the target database, and backup_file.sql with your desired filename.
• Enter your password when prompted. The SQL file will be saved in the current directory.

Export Specific Tables

To export only certain tables (e.g., table1, table2):

mysqldump -u username -p database_name table1 table2 > tables_backup.sql

Export Without Data (Structure Only)

Add the --no-data flag to export only the database schema:

mysqldump -u username -p --no-data database_name > structure_backup.sql

Export for Another Database System

If migrating to PostgreSQL, use pg_dump (PostgreSQL’s equivalent) or convert the MySQL dump file manually. For SQLite, use:

sqlite3 source.db .dump > dump.sql

Importing Data

Import into an Existing Database

1. Log in to MySQL:

   mysql -u username -p
   

2. Select the Target Database:

   USE database_name;
   

3. Import the SQL File:

   SOURCE /path/to/backup_file.sql;
   

   Replace /path/to/backup_file.sql with the actual file path.

Import into a New Database

1. Create the Database:

   mysql -u username -p -e "CREATE DATABASE new_database;"
   

2. Import the SQL File:

   mysql -u username -p new_database < /path/to/backup_file.sql
   

Alternative Import Method (Command Line)

Skip the MySQL login step by piping the SQL file directly:

mysql -u username -p database_name < /path/to/backup_file.sql

Post-Import Verification

1. Check Database Existence:

   mysql -u username -p -e "SHOW DATABASES;"
   

2. Verify Table Data:
   Log in to MySQL and run queries (e.g., SELECT * FROM table_name LIMIT 10;) to confirm data integrity.

Key Notes

• Permissions: Ensure your database user has sufficient privileges (e.g., EXPORT, IMPORT) for the operations.
• Large Databases: For big files, use pv (pipe viewer) to monitor progress:

  pv backup_file.sql | mysql -u username -p database_name
  

• Security: Store SQL files in secure locations (e.g., encrypted directories) to protect sensitive data.