- 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
MySQL 101: Working with the MySQL DB on Linux
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.
| |
Who we are ?
The Linux Foundation is a non-profit consortium dedicated to the growth of Linux.
Import MySQL dumpfile, SQL datafile into my database
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:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- My 10 UNIX Command Line Mistakes
- The Novice Guide To Buying A Linux Laptop
- 10 Tools To Add Some Spice To Your UNIX Shell Scripts
- Email FAQ to a friend
- Download PDF version
- Printable version
- Comment RSS feed
- Last Updated: 10/3/07
Previous FAQ: Apache 403 Forbidden Error and Solution
Advertise | Questions or Comments | Sitemap
How do I import a MySQL dumpfile into my database?
Search Results
-
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 -
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 -
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.biz › Faq › Linux - Similar -
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 - SimilarGet more discussion results
-
MySQL :: MySQL 5.0 Reference Manual :: 4.5.5 mysqlimport — A Data ...
-
MySQL :: Migrating from Microsoft SQL Server and Access to MySQL
-
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 -
» 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 -
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 - SimilarHOW CAN I IMPORT BIG SIZE DATABASE İNTO LOCALHOST - Apr 28, 2010
Re: Import data from an excel spreadsheet into an mysql ... - Jul 16, 2008
Import data from an excel spreadsheet into an mysql database - Apr 21, 2008
import from Excel to MySQL - Sep 25, 2004
-
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
{ 25 comments… read them below or add one }
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)
Worked like a charm! I did have to remove the spaces as Cameron suggested (thanks). I’m running Fedora Core 7.
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
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
Thanks to Cameron James. Removing the spaces did it for me as well. Fought with that sucker for almost 4 hours…
thanx a lot for this script. and thanks cephalex for utf8 option.
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
Very nice tut ! I just moving from another host to a new one. This commands works perfectly :)
Thx a lot ;) Very usefull tips
If Your Using Red Hat Linux,
take the $ out.
and try this,
mysql -u username -p -h localhost data-base-name < data.sql
Thanks
tnx … easy to understanc
go to mysql folder and execute this command:
[root@localhost mysql]# bin/mysql [Database Name] < /home/sandeep/software/[file-name].sql
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.
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
thx a lot, it’s very helpfull for me.
Thanks alot for these. Its helped me out of a big jam that I was in..
mysql -u username -p data-base-name < data.sql
the above statement running but tables are not created in database
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
How can I import a MySQL dumpfile into my database?
am using windows..
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.
Upload file to your server using ftp or sftp client. Once uploaded run above mysql command.
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?
you did a great job dear……..its worked ……..thanks a lot …………
Thanks man, that tip saved me lots of frustrations.
can anybody know how to import vhdl(x-vhdl) document which is mysql dump file i have downloaded from server into mysql database ?