Don's Pages and my Music

Wednesday, September 1, 2010

I'm learning how to setup SQL Databases on my Server...

I'm learning how to setup SQL Databases on my Server. I already have MySQL Up and running. But I need to set thing up in the Command Line, because I'm Running Caos Linux Server and can't install any SQL Gui Admin Apps in this System. Any help would be greatly appreciated. Heres what I have found so far...

Don
MySQL - Login Setup and Import database into MySQL
MySQL 101: Working with the MySQL DB on Linux | Linux.com
Import MySQL dumpfile, SQL datafile into my database
Modwest FAQ and Knowledge Base - How do I import a MySQL dumpfile into my database?
import database into MySQL - Google Search

MySQL info
MySQL 101: Working with the MySQL DB on Linux | Linux.com
import database into MySQL - Google Search
Modwest FAQ and Knowledge Base - How do I import a MySQL dumpfile into my database?
Modwest FAQ and Knowledge Base - How do I import delimited data into MySQL?
Modwest FAQ and Knowledge Base - How do I export and move my database tables between servers or copy databases?
Import MySQL dumpfile, SQL datafile into my database
MySQL :: MySQL 5.0 Reference Manual :: 4.5.5 mysqlimport — A Data Import Program
MySQL :: Migrating from Microsoft SQL Server and Access to MySQL
Here are some of the pages that I found helpful, from the links above.

MySQL 101: Working with the MySQL DB on Linux | Linux.com
Home Learn Linux Linux Tutorials MySQL 101: Working with the MySQL DB on Linux

MySQL 101: Working with the MySQL DB on Linux


Some people are born to be a database administrator, and others have database administration thrust upon them. If you're in the former group and you need to perform some very basic operations, like creating and backing up databases, you'll find it's much easier than it sounds. Let's take a look at some very basic MySQL administration to get started.

When I say basic, I do mean basic. This tutorial will not make you a database administrator or MySQL expert. But you'll have enough MySQL to get by for some standard scenarios like setting up WordPress or doing a quick backup of a MySQL database.

You can also do these things with phpMyAdmin, and many folks do, so why look at the command line? The first reason is that using the command line client provides one less piece of software to install, maintain, and worry about. If you find yourself interacting with MySQL infrequently, there's not much reason to worry about installing a separate piece of software to interact with it.

Also, knowing how to do things at the command line means being able to do them using scripts. You can't script interactions with phpMyAdmin (not easily, anyway). So if you want to write a script to back up your database on a weekly or daily basis, you'll need to know how to do that from the command line.

In this tutorial I'll cover the very bare basics of working with MySQL — creating a database and tables, creating users, dropping a database and tables, repairing a database, and working with backups. The assumption here is that you already have MySQL installed, and just need to get started with the commands.

Logging In

Let's start with the really easy stuff, logging into the MySQL shell. Just run mysql -u username -p. This will log you into the shell. To see what databases are available, run:

mysql> show databases;

Note the ; at the end. That tells MySQL that command is finished. Without it, it assumes another line of input is coming. This might be useful for entering longer SQL commands.

You'll also note that the MySQL shell saves its history. Hit the up arrow and you'll see the same command you just ran.

When you want to work with a specific database, run use database; where database is the name of the MySQL database you want to work with. What if you need to create a database? No problem.

Creating a Database and Tables

Creating a database is easy. At the MySQL shell, run create database database_foo. That will create database_foo, without any tables. You can also do this using from the bash shell using the mysqladmin utility, like so:

mysqladmin -u user -p create database 

This is faster if you are just going to create the database from the commandline, but slower if you're going to be using a bunch of commands. Why? Because you need to enter the password (-p) each time. Note that you can bypass this by creating a .my.cnf configuration file with your username and password. I don't recommend this, though, as it's less secure.

You can also use the mysqladmin utility to start, stop, and reload MySQL as well as many other administrative tasks. Need to shut MySQL down for some reason? Run mysqladmin -u user -p shutdown. Run mysqladmin --help for a list of commands.

To create a table, the syntax is similar to creating a database: create table table_foo and then the options for the table. Since each table can have quite a few fields with various data types, default information, etc., these commands can get lengthy. Here's an example statement that creates the wp_users table for WordPress:

CREATE TABLE `wp_users` (   `ID` bigint(20) unsigned NOT NULL auto_increment,   `user_login` varchar(60) NOT NULL default '',   `user_pass` varchar(64) NOT NULL default '',   `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',   `display_name` varchar(250) NOT NULL default '',   `spam` tinyint(2) NOT NULL default '0',   `deleted` tinyint(2) NOT NULL default '0',   PRIMARY KEY  (`ID`),   UNIQUE KEY `user_login` (`user_login`),   KEY `user_login_key` (`user_login`),   KEY `user_nicename` (`user_nicename`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; 

I omitted several of the statements for brevity, but the basic flavor is still there. After the create table statement, you see that you specify the field name, the data type, if a field can be NULL (i.e., have no value), and default values. The field statements are separated by commas, and the final line specifies the engine that MySQL will use (in this case, MyISAM), and other options for the table.

If that looks like gibberish, don't worry. This gets way beyond 101 administration and into actually creating schema. If you're working with applications that have pre-defined database schema, you shouldn't need to know how to come up with any of those statements.

Setting up a User

To start with you should have a root user for MySQL, which is different from the root user for the system. But you don't want to use the root account for everything, you should create a regular MySQL user as well. For instance, I have a different user for my WordPress database that has fewer privileges than the root user.

Creating a user is simple, just log into the MySQL shell and run the following:

 CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password';   

You will want to change the details, of course. Having a user doesn't do any good, though, without granting permission to one or more databases. So if you want to give permission to a user to have all privileges for a given database, you'd do something like this:

GRANT ALL PRIVILEGES ON wordpress_db.* to 'bob'@'localhost';  

You can limit the privileges that the user has to operations like select, delete, insert, etc. See the MySQL reference manual for more details there.

Creating a Database Dump

MySQL databases are stored on disk as binary files with your data, plus index files and a file that describes the format of the table. So for each database you'll have a directory under /var/lib/mysql plus three files for every table, and a .opt file with options for your database.

I believe it's possible to recover a database using the files on disk, but it's easier to work with a database dump. How do you get one? Simple. MySQL has a utility called, quite descriptively, mysqldump. To create a dump of a database called wordpress, you'd run:

mysqldump -u user -p wordpress > wordpress.sql     This creates a text file, possibly quite large if your database is fairly large, that has your data and the instructions for MySQL to create a new database. As an example, my WordPress database is 39MB on disk, and the text file created with mysqldump is 41MB. So it's not much larger, but there will be some overhead.

Note that you can use this utility in a more fine-grained fashion. That is, mysqldump can be used to create a dump of individual tables.

To restore from a MySQL dump file is even easier. Let's say you want to restore a database called "wordpress" from a dumpfile called wordpress.sql (as shown in the example above). All you need is the path to the dumpfile and the name of the database, plus a valid user with the correct privileges:

mysql -u user -p wordpress < wordpress.sql 

The database does need to exist before you can restore the data. So if you're moving a MySQL-based application from one system to another, you need to create the database (empty) before restoring the data.

Of course there's much, much more to learn about MySQL administration and SQL statements, etc. As I mentioned, you can also do a great deal of MySQL administration using phpMyAdmin. We'll look at that, and more advanced MySQL usage, in a future tutorial. Until then, I hope that this brief guide is useful for those users who just need to perform simple MySQL operations without needing to dive into DBA training.

Comments (0)Add Comment

Write comment
You must be logged in to post a comment. Please register if you do not have an account yet.

busy

Who we are ?

The Linux Foundation is a non-profit consortium dedicated to the growth of Linux.

More About the foundation...

Frequent Questions

Join / Members / Staff / Board

Import MySQL dumpfile, SQL datafile into my database

Import MySQL dumpfile, SQL datafile into my database

by Vivek Gite · 25 comments

Q. How can I import a MySQL dumpfile into my database? My old hosting provider gave me data.sql file. I do have access via ssh to server. I'm using CentOS Linux 5 server.

A. You can easily restore or import MySQL data with mysql command itself. First you need to login to your system using ssh or putty (from Windows based system). For example:

Type the following command at the shell prompt:

$ ssh loginname@server.hosting.com

Now type following command to import sql data file:
$ mysql -u username -p -h localhost data-base-name < data.sql

If you have dedicated database server, replace localhost name with actual server name or IP address:
$ mysql -u username -p -h 202.54.1.10 databasename < data.sql
OR use hostname such as mysql.hosting.com
$ mysql -u username -p -h mysql.hosting.com database-name < data.sql

If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h 202.54.1.10 < data.sql

Featured Articles:

Want to read Linux tips and tricks, but don't have time to check our blog everyday? Subscribe to our daily email newsletter to make sure you don't miss a single tip/tricks. Subscribe to our weekly newsletter here!

{ 25 comments… read them below or add one }

1 Cameron James October 13, 2006

If you’re having troubles try removing the space between -u and the username (eg: -uusername), -p and the password (eg: -ppassword), -h and the host (eg: -h11.11.11.11)

Reply

2 Steven Richards July 4, 2008

Worked like a charm! I did have to remove the spaces as Cameron suggested (thanks). I’m running Fedora Core 7.

Reply

3 mosh August 12, 2008

I’m so stupid ;)
I have decicated server and I was using all the time phpMyAdmin … that was terrible….

now I import DB in 2 sek :D

THX a lot for this very important for me command :)

Greetings, mosh

Reply

4 Cephalex November 4, 2008

if you want to import an UTF8 database you can use this command

$mysql -u databaseuser -p -h localhost –default-character-set=utf8 databasename < sqlfile.sql

Reply

5 Maple Syrup January 16, 2009

Thanks to Cameron James. Removing the spaces did it for me as well. Fought with that sucker for almost 4 hours…

Reply

6 kiran and niraj February 5, 2009

thanx a lot for this script. and thanks cephalex for utf8 option.

Reply

7 lady February 13, 2009

can anyone help me how to start using php in linux?i already installed it but i do not know how to run it using command line.
and one more thing how to convert data from pcap to mysql?
please give me a hint on how to start the program

Reply

8 fmuteam March 24, 2009

Very nice tut ! I just moving from another host to a new one. This commands works perfectly :)

Reply

9 Nordes June 17, 2009

Thx a lot ;) Very usefull tips

Reply

10 Brandon Broga June 29, 2009

If Your Using Red Hat Linux,
take the $ out.

and try this,

mysql -u username -p -h localhost data-base-name < data.sql

Thanks

Reply

11 Bhargav July 28, 2009

tnx … easy to understanc

Reply

12 Sandeep Tagore July 30, 2009

go to mysql folder and execute this command:
[root@localhost mysql]# bin/mysql [Database Name] < /home/sandeep/software/[file-name].sql

Reply

13 hammad September 4, 2009

I installed oracle 11g, but I want to know who do I create database table in it and also describe me about how to type command to see the database table in Sql.

Reply

14 bhavesh October 23, 2009

I want to add a “login” feature to my website, that consists of an email address, password and a unique user ID. The feature should also be able to store the names, send an email verification reply, provide for emailing the user for forgotten passwords, etc.

plz replay me on my mail id.

I am totally new at mysql data base and php script.

bksondagar

Reply

15 hary December 30, 2009

thx a lot, it’s very helpfull for me.

Reply

16 Wild_Bill January 5, 2010

Thanks alot for these. Its helped me out of a big jam that I was in..

Reply

17 Aashish January 8, 2010

mysql -u username -p data-base-name < data.sql

the above statement running but tables are not created in database

Reply

18 Aashish January 8, 2010

mysql -u username -p data-base-name < data.sql
gives error , can u help me
ERROR 1227 (42000) at line 500: Access denied; you need the SUPER privilege for this operation on win

Reply

19 sasikala January 27, 2010

How can I import a MySQL dumpfile into my database?
am using windows..

Reply

20 sascha February 7, 2010

yeah, everywhere instructions how to import sql file when it is stored in web server, but nowhere when it is stored in desktop. absolutly no one give us command how to upload stupid mysql file from desktop to web server without phpmyadmin.
I use Joomla 1.0.x CMS, and they have plugin to export but there is no import button in plugin and I don’t have command.

Reply

21 Vivek Gite February 8, 2010

Upload file to your server using ftp or sftp client. Once uploaded run above mysql command.

Reply

22 Don Carroll July 16, 2010

OK, nearly there, so thanks to the author and those who followed up in the comments. One question, though–how to add my sql dump (call it create.sql) to a specific table in the DB?

I have four existing tables in the database and don’t need/want to change them. I also have a newly created table (call it $xx_table) in which I wish to dump the data from create.sql. Any ideas?

Reply

23 dibish July 22, 2010

you did a great job dear……..its worked ……..thanks a lot …………

Reply

24 sqldumb August 17, 2010

Thanks man, that tip saved me lots of frustrations.

Reply

25 Jayashree August 24, 2010

can anybody know how to import vhdl(x-vhdl) document which is mysql dump file i have downloaded from server into mysql database ?

Reply

Previous FAQ:

Next FAQ:

How do I import a MySQL dumpfile into my database?

How do I import a MySQL dumpfile into my database?

import database into MySQL - Google Search

Search Results

  1. Modwest FAQ and Knowledge Base - How do I import a MySQL dumpfile ...

    Mar 3, 2010 ... After you have dumped out your data into a file as described here, ...
    support.modwest.com/.../how-do-i-import-a-mysql-dumpfile-into-my-database.html - Cached
  2. Modwest FAQ and Knowledge Base - How do I import delimited data ...

    Mar 3, 2010 ... How do I import delimited data into MySQL? Applies to: Shared System. If you have data that you need to bring into your MySQL database, ...
    support.modwest.com/.../how-do-i-import-delimited-data-into-mysql.html - Cached
  3. Import MySQL dumpfile, SQL datafile into my database

    Oct 3, 2007 ... Q. How can I import a MySQL dumpfile into my database? My old hosting provider gave me data.sql file. I do have access via ssh to server. ...
    www.cyberciti.bizFaqLinux - Similar
  4. Google Answers: MySQL dump/import command

    Jun 14, 2002 ... Q: MySQL dump/import command ( Answered 5 out of 5 stars ... I need to completley copy one database into the other (one is currently quite ...
    answers.google.com/answers/threadview?id=24558 - Cached - Similar
  5. MySQL :: MySQL 5.0 Reference Manual :: 4.5.5 mysqlimport — A Data ...

    While converting the data from Microsoft Access database to Mysql, ... If you are one of the many people trying to import a CSV file into MySQL using ...
    dev.mysql.com/doc/refman/5.0/en/mysqlimport.html - Cached - Similar
  6. MySQL :: Migrating from Microsoft SQL Server and Access to MySQL

    While converting an Access or SQL Server database to MySQL offers many .... can be used with both MSSQL and Microsoft Access to import tables into MySQL. ...
    dev.mysql.com/tech.../migrating-from-microsoft.html - Cached - Similar
  7. How to import Excel, Access or XML data into MySQL database by ...

    This article discusses a solution to import MS Excel, MS Access, XML data to local or Remote MySQL databases by using a famous MySQL Admin Tool called ...
    www.stardeveloper.com/articles/display.html?article...1 - Cached - Similar
  8. » Import large file into MySQL : use SOURCE command - John Andrews ...

    How to import a large sql file into mysql when phpmyadmin has hit it's 5 or so ... Create the destination database in MySQL; Put the .sql file into a ...
    www.johnon.com/import-large-file-into-mysql/ - Cached - Similar
  9. MySQL :: How do I import CSV data into MySqL

    Dec 6, 2007 ... I have and Excel/CSV file and I need to import this into MYSQL. ... I DO NOT have access to the MySql database file directory it is on a hosted environment ...
    forums.mysql.com/read.php?79,187065,187065 - Cached - Similar
  10. Import sql dump file to mysql database

    Import-export sql dump file to mysql database tutorial and step by step ... I need to completley copy one database into the other (one is currently quite ...
    www.velikan.net/import-sql-dump-file-to-mysql-database/ - Cached - Similar

No comments:

Post a Comment