As a webmaster, you will be managing a lot of databases. A good practice is to backup databases to multiple servers, so you need to automate creating, dropping and importing SQL databases from the command line
Creating Databases From Command Line
Let’s start with the simple command you need to create databases. Please keep in mind that you first need to drop any existing databases before you can create a database with the same name. So if you intend to automate SQL database backup processes, make sure you use the correct order: Drop, Create, Import
mysql -u user_name -pmycoolstring -e "CREATE database my_database"
The command is simple enough. user_name is the user that you granted access to. Don’t know what I am talking about? To better understand SQL databases, open your PHPMyAdmin interface and open the database mysql. You will find a table called user. Browse through this and you will find all of your SQL users, for example [email protected], [email protected] and so on.
The password “mycoolstring” does not require to be wrapped in quotes, instead simply append the string to -p without any space
Finally, the parameter -e allows you to execute almost any SQL command. Make sure to check the syntax first.
Make sure the “coolstring” is a random string with special characters, underscores and all that good stuff.
Similarly, dropping database works almost the same way, we only have to modify the e parameter:
mysql -u user_name -pmycoolstring -e "DROP database my_database"
Finally: Importing Databases
Now we know how to drop and create databases, the last step is to import it:
mysql -f --max_allowed_packet=650M -u user_name -pmycoolstring my_database < /path/to/sqlfile.sql
Simply enough, right?
The commands above are great to automate backups and restoration, but you are exposing your password to intruders. Make sure NOT to use your SQL root users and passwords. On top of that you may want to look into PAM, an authentication plugin for SQL. The command I recommend to use is checkpassword-pam. Because that is for more advanced users, I will write another article about this, I wanted to keep this very simple for beginners.
Importing HUGE SQL Databases
Because that is an entire topic, you may also want to read our article on importing huge SQL files from the command line