1

The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable to "build" the correct install (he knows much more than me about linux command line and the server we're using than I do, and we are running on a relatively short schedule). Thus, the only tool I have with which to create a MySQL relational database is the built-in MySQL Command Line.

  • Is it possible (practically speaking) to create a functioning relational database using only SQL code in the command line, or will I eventually need a tool such as MySQL Workbench?

    • If it is possible, could you please direct me to a good resource/reference on how to do so?

I have minimal experience with MySQL, but am familiar with databases and am generally computer-savvy. Thanks.

3

Yes, most linux system administrators only use the command lines client (or other cli tools to work with the database). Maybe the only other thing you will need for design is a text editor (for writing easily before executing), like vi(m), nano or emacs, and pen and paper.

Here it is a first crash course:

  • Create a database:

    CREATE DATABASE your_database_name;
    
  • Change the current database:

    use your_database_name
    
  • Create your first table:

     CREATE TABLE your_table (
         id bigint unsigned PRIMARY KEY auto_increment,
         name varchar(50) NOT NULL,
         description text,
         modified_on timestamp
     ) ENGINE = InnoDB;
    
  • Populate your table:

     INSERT INTO your_table (name, description) VALUES ('pheidlauf', 'You are awesome');
    
  • Select your table:

     SELECT * FROM your_table;
    

Learn about MySQL SQL variant, in particular DDL commands, DCL and about using the command line client. There are many resources (I have just linked to wikibooks and the official MySQL documentation), but the command line client also has integrated help:

mysql> help create table

Another quick startup guide.

  • 1
    +1 for getting to this first. I am a slow typer these days. – RolandoMySQLDBA Aug 7 '14 at 18:09
  • 1
    If you're a slow typist, time must slow down around a fast one! :-) – Vérace Aug 7 '14 at 19:08
2

When you connect to MySQL from the command line, simply run:

CREATE DATABASE mydb;

What does this do ?

  • This will create the folder /var/lib/mysql/mydb
  • The ownership will be mysql:mysql
  • File permissions will be set by the mysqld process

Of course, you need to connect to MySQL as root@localhost

Notwithstanding, I would strongly advise you to MySQL Workbench because you are a beginner.

First, download and install MySQL Workbench. Then, simply download MySQL Documentation and read it through. MySQL Workbench will provide a comfortable GUI for all the basica operational aspects of creating databases, tables, and all other moving parts of MySQL.

A more seasoned DBA may construct a database with his/her bare hands. MySQL Workbench and other DBA GUIs are there to streamline that process.

  • this is just conversation. As a MySQL teacher, for my students (if you are not an occasional user that just want things done), I tend to recommend going directly to the command line. GUI hides some things (like an IDE when programming) and I think in the end it is more beneficial if you really want to learn. What do you think? – jynus Aug 7 '14 at 18:13
  • I agree with RolandoMySQLDBA that because I'm a beginner the Workbench would be easier (and more ideal), however I do not have that as an available resource because we're running Suse Linux Enterprise Server. There are no builds available for that OS of MySQL workbench and my supervisor hasn't been able to custom build a version for it. I'm stuck without Workbench sadly. @jynus, command line would be better for end-result learning. However, my goal right now is end-result product. – pheidlauf Aug 7 '14 at 18:20
  • 1
    @pheidlauf MySQL workbench can be installed on a different machine than the server. It is a client application. – jynus Aug 7 '14 at 18:23
  • All of the non-server machines are run off of the big company policy. Apparently they don't like open source software (so my supervisor hasn't even attempted to get them to install it). – pheidlauf Aug 7 '14 at 18:26
  • 1
    @jynus I agree. GUI is great for monitoring, graphs, and quick syntax checking. I am a die-hard command line DBA. Hands-on learning along with concepts (what is a table, index, view, store proc, etc.) is best. Becoming a DBA can be a little terrifying for some because of the means one goes about learning (See my post dba.stackexchange.com/a/2913/877) – RolandoMySQLDBA Aug 7 '14 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.