MySQL Export, Import, SOURCE & Permission Guide

Windows + Linux commands | Database dumps | Stored Procedures | Functions | Views | Triggers | Events | Users & Grants

1. Overview

This guide provides practical MySQL commands for exporting and importing databases in both Linux and Windows environments.

mysqldump mysql import SOURCE Stored Procedures Functions Views Triggers Events Users GRANT REVOKE
Important: Stored procedure cursors are not independent database objects. A cursor declared inside a stored procedure/function is exported as part of that routine's definition when the routine is included in the dump.
Password security: Avoid putting real passwords directly into scripts, command history, Git repositories, or HTML documentation. The password shown in the original notes should be treated as an example and changed if it is a real production credential.

2. MySQL Database Export — Linux

2.1 Basic database export

mysqldump -h localhost -P 3306 -u root -p frt_test_ser_dev > frt_test_ser_dev.sql

After pressing Enter, MySQL prompts for the password.

2.2 Recommended complete application dump

For a complete logical backup containing tables, data, views, stored procedures, stored functions, triggers and events:

mysqldump \
  -h localhost \
  -P 3306 \
  -u root \
  -p \
  --routines \
  --triggers \
  --events \
  --single-transaction \
  --set-gtid-purged=OFF \
  frt_test_ser_dev > frt_test_ser_dev_full.sql
Recommended options: --routines includes stored procedures/functions; --triggers includes triggers; --events includes scheduled events; --single-transaction provides a consistent dump for transactional tables such as InnoDB.

2.3 Export only routines

mysqldump -h localhost -P 3306 -u root -p \
  --no-create-info \
  --no-data \
  --no-create-db \
  --routines \
  --skip-triggers \
  frt_test_ser_dev > frt_test_ser_dev_routines.sql

2.4 Export structure only

mysqldump -h localhost -P 3306 -u root -p \
  --no-data \
  --routines \
  --triggers \
  --events \
  frt_test_ser_dev > frt_test_ser_dev_structure.sql

2.5 Export data only

mysqldump -h localhost -P 3306 -u root -p \
  --no-create-info \
  frt_test_ser_dev > frt_test_ser_dev_data.sql

2.6 Export multiple databases

mysqldump -h localhost -P 3306 -u root -p \
  --routines \
  --triggers \
  --events \
  --single-transaction \
  --databases frt_test_ser_dev futureminds \
  > multiple_databases.sql

2.7 Export all databases

mysqldump -h localhost -P 3306 -u root -p \
  --all-databases \
  --routines \
  --triggers \
  --events \
  --single-transaction \
  > all_databases.sql
Note: An --all-databases dump can contain MySQL system schemas and should be restored carefully, especially when moving between different MySQL versions.

3. MySQL Database Export — Windows

3.1 Basic export from Command Prompt

mysqldump -h localhost -P 3306 -u root -p frt_test_ser_dev > frt_test_ser_dev.sql

3.2 Recommended complete export

mysqldump -h localhost -P 3306 -u root -p --routines --triggers --events --single-transaction --set-gtid-purged=OFF frt_test_ser_dev > frt_test_ser_dev_full.sql

3.3 If MySQL is not in PATH

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" -h localhost -P 3306 -u root -p --routines --triggers --events --single-transaction frt_test_ser_dev > D:\FortressProjects\frt_test_ser_dev_full.sql
If mysqldump is not recognized, use the full path to mysqldump.exe or add the MySQL bin directory to the Windows PATH environment variable.

4. MySQL Import Commands

4.1 Linux — import into an existing database

mysql -h localhost -P 3306 -u root -p frt_test_ser_dev < frt_test_ser_dev.sql

This is the equivalent of the commands from the original notes:

mysql -h localhost -P 3306 -u root -p intercall < intercall.sql
mysql -h localhost -P 3306 -u root -p conceirge < donarDump20260110.sql
mysql -h localhost -P 3306 -u root -p aptdb < aptDump20260110.sql
mysql -h localhost -P 3306 -u root -p frt_test_ser_dev < frt_test_ser_dev.sql

4.2 Windows — import from Command Prompt

mysql -h localhost -P 3306 -u root -p frt_test_ser_dev < frt_test_ser_dev.sql

4.3 Windows — import using an absolute path

mysql -h localhost -P 3306 -u root -p frt_test_ser_dev < "D:\FortressProjects\frt_test_ser_dev.sql"

4.4 Import a dump that contains CREATE DATABASE

If the dump was created using --databases, the dump normally contains database creation/use statements. In that case:

mysql -h localhost -P 3306 -u root -p < multiple_databases.sql
Do not specify a target database when the dump is intentionally designed to create/select its own databases unless you have a specific reason to do so.

5. MySQL SOURCE Command

The SOURCE command is executed from inside the MySQL client. It executes SQL statements contained in a file.

5.1 Linux

mysql -u root -p

SOURCE /home/srinath/opt/apps/dumps/submit_tests.sql;
SOURCE /home/srinath/opt/apps/dumps/insert_test_details.sql;

5.2 Windows

mysql -u root -p

SOURCE D:/FortressProjects/submission_cleaning_script.sql;
SOURCE D:/FortressProjects/OTS/frt_test_ser_dev_procedures.sql;
Windows tip: Forward slashes are usually easier inside SOURCE commands. You can also use escaped backslashes where appropriate.

5.3 Your original Windows paths

SOURCE D:\\FortressProjects\\submission_cleaning_script.sql;
SOURCE D:\\FortressProjects\\OTS\\frt_test_ser_dev_procedures.sql;

5.4 Select the database before SOURCE

USE frt_test_ser_dev;

SOURCE D:/FortressProjects/OTS/frt_test_ser_dev_procedures.sql;
Best practice: Use USE database_name; before running a SQL script if the script contains objects such as tables, procedures or functions that should be created in a particular schema.

6. MySQL User Creation and Permissions

6.1 Create the user

CREATE USER 'springuser'@'localhost'
IDENTIFIED BY 'ChangeThisPassword';
If this is a real password, do not store it in documentation or source control. Use a strong, unique password or a secret-management solution.

6.2 Grant global privileges required for dumps

GRANT PROCESS, SELECT, SHOW VIEW, LOCK TABLES, EVENT, TRIGGER
ON *.* TO 'springuser'@'localhost';

6.3 Add EXECUTE privilege

GRANT PROCESS, SELECT, SHOW VIEW, LOCK TABLES, EVENT, TRIGGER, EXECUTE
ON *.* TO 'springuser'@'localhost';

The second command already includes the privileges in the first command, so normally you do not need to execute both.

6.4 Grant complete access to application databases

GRANT ALL PRIVILEGES
ON frt_test_ser_dev.* TO 'springuser'@'localhost';

GRANT ALL PRIVILEGES
ON frt_test_ser_qa.* TO 'springuser'@'localhost';

GRANT ALL PRIVILEGES
ON futureminds.* TO 'springuser'@'localhost';

6.5 Grant access for 127.0.0.1

GRANT ALL PRIVILEGES
ON futureminds.* TO 'springuser'@'127.0.0.1';
localhost vs 127.0.0.1: MySQL accounts are identified by both username and host. 'springuser'@'localhost' and 'springuser'@'127.0.0.1' are separate account entries.

6.6 FLUSH PRIVILEGES

FLUSH PRIVILEGES;

For CREATE USER and GRANT statements, MySQL normally updates the privilege tables automatically, so FLUSH PRIVILEGES is generally not required. It is harmless in normal administrative usage, but it should not be treated as mandatory after every GRANT.

6.7 Procedure-specific EXECUTE privilege

Use the database-qualified procedure name:

GRANT EXECUTE
ON PROCEDURE frt_test_ser_dev.recalculate_test_rank
TO 'springuser'@'%';

6.8 Table-specific privileges

GRANT SELECT, UPDATE
ON frt_test_ser_dev.test_submission
TO 'springuser'@'%';
Important correction: GRANT EXECUTE ON PROCEDURE recalculate_test_rank ... should normally be database-qualified, for example frt_test_ser_dev.recalculate_test_rank. Likewise, table privileges should normally use database.table when granting outside the current database context.

7. REVOKE Commands

7.1 Revoke a specific global privilege

REVOKE EXECUTE
ON *.* FROM 'springuser'@'localhost';

7.2 Revoke database-level privileges

REVOKE ALL PRIVILEGES
ON frt_test_ser_dev.* FROM 'springuser'@'localhost';

7.3 Revoke procedure EXECUTE privilege

REVOKE EXECUTE
ON PROCEDURE frt_test_ser_dev.recalculate_test_rank
FROM 'springuser'@'%';

7.4 Revoke table privileges

REVOKE SELECT, UPDATE
ON frt_test_ser_dev.test_submission
FROM 'springuser'@'%';

7.5 Remove the user

DROP USER 'springuser'@'localhost';

8. Exporting Stored Procedures, Functions, Views, Triggers, Events and Cursors

ObjectIncluded by mysqldumpRecommended option
TablesYesDefault
Table dataYesDefault
ViewsYesDefault
Stored ProceduresYes, when routines are enabled--routines
Stored FunctionsYes, when routines are enabled--routines
TriggersYes by default--triggers
EventsOnly when enabled--events
CursorsNot standalone objectsIncluded inside routine definition

8.1 Recommended command for your database

mysqldump -h localhost -P 3306 -u root -p \
  --routines \
  --triggers \
  --events \
  --single-transaction \
  --set-gtid-purged=OFF \
  frt_test_ser_dev > frt_test_ser_dev_complete.sql

This is the preferred starting point for a logical backup of your frt_test_ser_dev application database.

8.2 Example procedure containing a cursor

DELIMITER $$

CREATE PROCEDURE recalculate_test_rank()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_submission_id BIGINT;

    DECLARE submission_cursor CURSOR FOR
        SELECT id
        FROM test_submission;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN submission_cursor;

    read_loop: LOOP
        FETCH submission_cursor INTO v_submission_id;

        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Processing logic here

    END LOOP;

    CLOSE submission_cursor;
END$$

DELIMITER ;
When --routines is used, the procedure definition is dumped. The cursor declaration and cursor logic inside that procedure are therefore included as part of the procedure definition.

9. Verify Procedures, Functions and Views

9.1 Find a stored procedure

SELECT ROUTINE_NAME
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_SCHEMA = 'frt_test_ser_dev'
  AND ROUTINE_NAME = 'recalculate_test_rank';

9.2 Display the procedure definition

SHOW CREATE PROCEDURE frt_test_ser_dev.recalculate_test_rank\G

9.3 Check all procedures

SELECT ROUTINE_SCHEMA,
       ROUTINE_NAME,
       ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'frt_test_ser_dev'
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

9.4 Check functions

SELECT ROUTINE_NAME
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
  AND ROUTINE_SCHEMA = 'frt_test_ser_dev';

9.5 Check views

SELECT TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'frt_test_ser_dev'
ORDER BY TABLE_NAME;

9.6 Check triggers

SELECT TRIGGER_SCHEMA,
       TRIGGER_NAME,
       EVENT_OBJECT_TABLE,
       EVENT_MANIPULATION
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'frt_test_ser_dev';

9.7 Check scheduled events

SELECT EVENT_SCHEMA,
       EVENT_NAME,
       STATUS
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'frt_test_ser_dev';

9.8 Check the routine definition directly

SHOW CREATE PROCEDURE frt_test_ser_dev.recalculate_test_rank\G

9.9 Drop procedure before recreating it

DROP PROCEDURE IF EXISTS frt_test_ser_dev.recalculate_test_rank;
The query in the original notes using all_objects is Oracle-specific and is not the correct MySQL metadata query. For MySQL, use information_schema.ROUTINES and SHOW CREATE PROCEDURE.

10. Find modified_time Columns and Defaults

Use the following query to identify tables containing modified_time:

SELECT TABLE_NAME,
       COLUMN_NAME,
       COLUMN_DEFAULT,
       IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'frt_test_ser_dev'
  AND COLUMN_NAME = 'modified_time'
ORDER BY TABLE_NAME;

10.1 Find only columns without a default

SELECT TABLE_NAME,
       COLUMN_NAME,
       COLUMN_DEFAULT,
       IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'frt_test_ser_dev'
  AND COLUMN_NAME = 'modified_time'
  AND COLUMN_DEFAULT IS NULL
ORDER BY TABLE_NAME;

10.2 Check whether CURRENT_TIMESTAMP is configured

SELECT TABLE_NAME,
       COLUMN_NAME,
       COLUMN_DEFAULT,
       IS_NULLABLE,
       EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'frt_test_ser_dev'
  AND COLUMN_NAME = 'modified_time'
ORDER BY TABLE_NAME;
For a modified_time column, a typical configuration can be DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, depending on the application's intended behavior.

12. Common Problems and Fixes

ProblemCheck / Fix
mysqldump is not recognized Use the full path to mysqldump.exe or add MySQL's bin directory to PATH.
Access denied Check username, password, host and privileges using SHOW GRANTS.
Procedure missing from dump Use --routines.
Event missing from dump Use --events and ensure the dumping user has sufficient privileges.
Trigger missing Use --triggers and verify trigger privileges.
View missing / inaccessible Use --routines --triggers --events as appropriate and ensure SHOW VIEW privilege exists.
SOURCE file.sql fails Check the file path, current MySQL client, file permissions and database selected with USE.
Procedure creation fails during import Check DELIMITER, DEFINER, SQL mode, privileges and MySQL version compatibility.
Tables are locked during backup For InnoDB-heavy databases, use --single-transaction. Non-transactional tables may require additional handling.
GTID-related warning For many environment-to-environment logical dumps, --set-gtid-purged=OFF can avoid importing source GTID state. Review this choice for replication environments.

12.1 Check current user grants

SHOW GRANTS FOR 'springuser'@'localhost';

12.2 Check the 127.0.0.1 account

SHOW GRANTS FOR 'springuser'@'127.0.0.1';

12.3 Check the wildcard-host account

SHOW GRANTS FOR 'springuser'@'%';

12.4 Check users

SELECT User, Host
FROM mysql.user
WHERE User = 'springuser';

13. Quick Command Cheat Sheet

Export complete database

mysqldump -h localhost -P 3306 -u root -p \
--routines --triggers --events --single-transaction \
--set-gtid-purged=OFF frt_test_ser_dev > frt_test_ser_dev.sql

Import database

mysql -h localhost -P 3306 -u root -p \
frt_test_ser_dev < frt_test_ser_dev.sql

Open MySQL client

mysql -h localhost -P 3306 -u root -p

Select database

USE frt_test_ser_dev;

Run SQL file from MySQL client

SOURCE /home/srinath/opt/apps/dumps/submit_tests.sql;

Windows SOURCE

SOURCE D:/FortressProjects/OTS/frt_test_ser_dev_procedures.sql;

Create user

CREATE USER 'springuser'@'localhost'
IDENTIFIED BY 'ChangeThisPassword';

Grant database access

GRANT ALL PRIVILEGES
ON frt_test_ser_dev.* TO 'springuser'@'localhost';

Grant procedure execution

GRANT EXECUTE
ON PROCEDURE frt_test_ser_dev.recalculate_test_rank
TO 'springuser'@'%';

Grant table access

GRANT SELECT, UPDATE
ON frt_test_ser_dev.test_submission
TO 'springuser'@'%';

Show grants

SHOW GRANTS FOR 'springuser'@'localhost';

Revoke privilege

REVOKE EXECUTE
ON *.* FROM 'springuser'@'localhost';

Drop procedure

DROP PROCEDURE IF EXISTS
frt_test_ser_dev.recalculate_test_rank;

Show procedure

SHOW CREATE PROCEDURE
frt_test_ser_dev.recalculate_test_rank\G

14. Production Best Practices

Recommended command for your FRT application:

mysqldump -h localhost -P 3306 -u root -p --routines --triggers --events --single-transaction --set-gtid-purged=OFF frt_test_ser_dev > frt_test_ser_dev_complete.sql

This is the most useful general-purpose logical dump command for your stated requirement because it captures database structure/data plus stored procedures, functions, triggers, events and views. Cursors contained inside routines are included with those routines.