How to create a MySQL database from command line

---

---

We use currently for Podcastpedia.org a MySQL database and because we’ve recently gone open source on GitHub, we’ve created a README.md to explain the setup of the database. The content of that file is basically reproduced here, as “back-up”, for future reference and why not?, it might also serve others in the mean time.

Note: Although the steps presented here refer to the database backing Podcastpedia.org, they should be valid for any MySQL database you might want to setup either in Linux or Windows.

1. Install MySql 5.5 and above

1. Download MySQL Community Server version 5.5 or 5.6 for the platform of your choice.
2. Install the MySQL Server
   1. Installing MySQL on Linux
   2. Installing MySQL on Microsoft Windows
   3. Setup MySQL configuration file
      • For Windows place the configuration file where the MySQL server is installed – the my.ini file from above is an example used on a Windows 7 machine
      • For linux you need to use .cnf files. You can see in this blog post –Optimizing MySQL server settings – how the MySQL database is configured in production for Podcastpedia.org
   4. Set up the environment variables – the MySQL programs are installed in MySQL_installation_path/bin, so you’d want this location added to the PATH variable to be easily invoked from shells and command lines.
   5. Optional – install MySQL Workbench for easy DB development and administration

2. Connect to the MySql console

When installing the MySQL server, you were asked to set up a “root” user. Now use it to connect to the MySQL console:

shell> mysql --host=localhost --user=root -p

You will be asker for your root’s password

3. Create database and the (development) DB user

Once you are connected to the MySQL command line do following the steps:

-- delete the pcmDB database if existent
mysql> DROP DATABASE IF EXISTS pcmDB;

--create the pcmDB database
mysql> CREATE DATABASE pcmDB;

-- connect to the created database
mysal> use pcmDB;

-- drop 'pcm' user
mysql> DROP USER 'pcm'@'localhost';

-- create the development user 'pcm' identified by the password 'pcm_pw'
mysql> CREATE USER 'pcm'@'localhost' IDENTIFIED BY 'pcm_pw';

-- verify user has been created
mysql> select host, user, password from mysql.user;
+-----------+-------------+-------------------------------------------+
| host      | user        | password                                  |
+-----------+-------------+-------------------------------------------+
| localhost | root        | *C2BD8E7A5247DF69A9A8CB29C8C6E8FC83D3681F |
| 127.0.0.1 | root        | *C2BD8E7A5247DF69A9A8CB29C8C6E8FC83D3681F |
| ::1       | root        | *C2BD8E7A5247DF69A9A8CB29C8C6E8FC83D3681F |
| %         | pcm_user    | *32D8ED777E1B90734ED5A6AFCD0E354230826743 |
| %         | rest_demo   | *3B8DD81985A42FD9B56133326F3B25A2985A3F75 |
*** | localhost | pcm         | *68DC5C435B9AAA7280CA4C89391C28EFEEC0E946 |***
| localhost | pdp_user    | *F776A21503EFA57908FEF30C914DFB9A9FC78EF3 |
+-----------+-------------+-------------------------------------------+

-- check user privileges
mysql> SELECT host, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, alter_priv, password FROM mysql.user WHERE user='pcm';

-- should have no(N) privileges
+-----------+------+-------------+-------------+-------------+-------------+-------------+------------+-
| host      | user | select_priv | insert_priv | update_priv | delete_priv | create_priv | alter_priv |
+-----------+------+-------------+-------------+-------------+-------------+-------------+------------+-
| localhost | pcm  | N           | N           | N           | N           | N           | N          |
+-----------+------+-------------+-------------+-------------+-------------+-------------+------------+-

-- grant full privileges to the user, for easy development
mysql> GRANT ALL PRIVILEGES ON *.* TO 'pcm'@'localhost';

-- verify privileges were set (Y)
mysql> SELECT host, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, alter_priv, password FROM mysql.user WHERE user='pcm';

+-----------+------+-------------+-------------+-------------+-------------+-------------+------------+
| host      | user | select_priv | insert_priv | update_priv | delete_priv | create_priv | alter_priv |
+-----------+------+-------------+-------------+-------------+-------------+-------------+------------+
| localhost | pcm  | Y           | Y           | Y           | Y           | Y           | Y          |
+-----------+------+-------------+-------------+-------------+-------------+-------------+------------+

-- exit the mysql command line
exit;

Instead of executing all these commands manually, you can alternatively put them in a .sql script file, like prepare_database_for_import.sql,  and run the following command in the OS shell:

shell> mysql < "PATH_TO_FILE\prepare_database_for_import.sql"

Now that the database and user are put in place, you can start creating your tables and fill them with data.

4. Import database from file

Tables and data required for Podcastpedia.org will be imported via a single .sql file, which you can download podcastpedia-2014-06-17-dev-db.sql After download you can import the database data into the pcmDB database by issuing the following command on the command line:

shell> mysql -p -u pcm pcmDB < "PATH_TO_FILE\podcastpedia-2014-06-17-dev-db.sql"
-- e.g. mysql -p -u pcm pcmDB < "C:\tmp\podcastpedia-2014-06-17-dev-db.sql"

These should take a moment depending on your PC’s performance, and once is ready you can check that everything was OK by connecting to the mysql command line and issuing SQL commands like “show tables” or “select from a table”:

-- connect to the database with the development user
shell> mysql --host=localhost --user=pcm --password=pcm_pw

-- use the podcastpedia database
mysql> USE pcmDB;

-- show tables imported
mysql> SHOW TABLES;

-- select data from a table, e.g. "categories"
mysql> SELECT * from categories;
+-------------+-----------------------+--------------------+
| CATEGORY_ID | NAME                  | DESCRIPTION        |
+-------------+-----------------------+--------------------+
|          21 | science_technology    | science            |
|          22 | education             | education          |
|          24 | arts_culture          | Arts & culture     |
|          25 | health_medicine       | Health             |
|          27 | music                 | Music              |
|          28 | religion_spirituality | Religion           |
|          29 | tv_film               | science            |
|          31 | sport                 | Sport              |
|          33 | economy               | Economy            |
|          35 | hobby_freetime        | Hobby & free time  |
|          37 | family_children       | Family & children  |
|          38 | travel_transport      | Travel & Transport |
|          39 | people_society        | People             |
|          41 | internet_computer     | Internet           |
|          42 | news_politics         | News               |
|          43 | radio                 | Radio              |
|          44 | money_business        | Money              |
|          45 | entertainment         | Entertainment      |
|          46 | food_drink            | Food and drink     |
|          47 | nature_environment    | Nature             |
|          48 | general               | General            |
|          49 | history               | History            |
+-------------+-----------------------+--------------------+

5. Backup the database (optional)

If you ever want to backup up the database you can use the mysqldump program, by issuing a command similar to the following on the command line: ``

shell> mysqldump pcmdb -u pcm -p -h 127.0.0.1 --single-transaction > c:/tmp/pcmdb-backup-2014.06.22.sql

The pcmDB database will be than saved into the single file pcmdb-backup-2014.06.22.sql, which you can later import as mentioned on the previous step.

Source code for this post is available on Github - podcastpedia.org is an open source project.

Resources

Web

1.  MySQL documentation
   1. Connecting to the MySQL Server
   2. Adding User Accounts
   3. DROP DATABASE Syntax
2. How To Create a New User and Grant Permissions in MySQL