MySQL + Windows: Command Line Tips
On this post, I’ll share some handy MySQL commands I’ve been through in the last months, and now they’re a essential part of my daily work ~~and I’m writing them down to never forget them anymore ;-)~~.
Important: none of these commands below will work unless you’ve properly have both root access and MySQL command line tools configured on your PATH environment variable.
mysql -u user -p database_name
Basic command to connect to a local MySQL server.
mysqldump -u user -p database_name > C:\path\to\file.sql
Export all database information (data and structure).
mysqldump -u user -p database_name --no-data > C:\path\to\file.sql
Export all database information except data (only structute).
echo create database database_name | mysql -u user -p
Using echo command from Windows batch script to create a database. Handy when you need to create a database without graphic tools. Note that you don’t need to use quotes neither semicolon.
mysqldump -u user -p --all-databases > C:\path\to\file.sql
Dump all information about all databases.
mysql -u root -p database_name < C:\path\to\file.sql
Import all information from file.sql into a database.
Note that all the commands assume that you’re handling with a local MySQL server (localhost). If you need to connect to a different host, use
-h hostname to perform every command, as
mysql -h hostname -u user -p database_name.