1. Overview
This guide provides practical MySQL commands for exporting and importing databases in both Linux and Windows environments.
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
--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
--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
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
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;
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;
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';
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';
'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'@'%';
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
| Object | Included by mysqldump | Recommended option |
|---|---|---|
| Tables | Yes | Default |
| Table data | Yes | Default |
| Views | Yes | Default |
| Stored Procedures | Yes, when routines are enabled | --routines |
| Stored Functions | Yes, when routines are enabled | --routines |
| Triggers | Yes by default | --triggers |
| Events | Only when enabled | --events |
| Cursors | Not standalone objects | Included 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 ;
--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;
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;
modified_time column, a typical configuration can be DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, depending on the application's intended behavior.
11. Recommended Backup and Restore Procedure
SHOW DATABASES;
SHOW DATABASES LIKE 'frt_test_ser_dev';
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'frt_test_ser_dev';
SELECT TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'frt_test_ser_dev';
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
ls -lh frt_test_ser_dev_complete.sql
grep -i "CREATE PROCEDURE" frt_test_ser_dev_complete.sql
grep -i "CREATE FUNCTION" frt_test_ser_dev_complete.sql
grep -i "CREATE VIEW" frt_test_ser_dev_complete.sql
grep -i "CREATE TRIGGER" frt_test_ser_dev_complete.sql
grep -i "CREATE EVENT" frt_test_ser_dev_complete.sql
CREATE DATABASE frt_test_ser_dev;
mysql -h localhost -P 3306 -u root -p frt_test_ser_dev < frt_test_ser_dev_complete.sql
USE frt_test_ser_dev;
SHOW TABLES;
SHOW CREATE PROCEDURE frt_test_ser_dev.recalculate_test_rank\G
SELECT TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'frt_test_ser_dev';
12. Common Problems and Fixes
| Problem | Check / 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
- Do not put production passwords directly into commands, scripts or Git.
- Use a dedicated backup user instead of
rootwhere practical. - Use
--routines --triggers --eventswhen application logic includes these objects. - Use
--single-transactionfor consistent logical backups of predominantly InnoDB databases. - Test every important backup by restoring it to a non-production environment.
- Verify procedures, functions, views, triggers and events after restoration.
- Keep MySQL client/server versions compatible where possible.
- Be careful with
DEFINERclauses in procedures, functions, views and triggers when moving dumps between environments. - Grant the minimum privileges required for application and backup users.
- Keep separate backup files for development, QA and production databases.
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.