If you have data that you need to bring into your MySQL database, there are a few ways to do it. Exporting data out of mysql is another topic, described here.
1. Using the LOAD DATA INFILE SQL statement
For security reasons, no one has the mysql FILE priv, which means you cannot “LOAD DATA INFILE”. You can, however, use a “LOAD DATA LOCAL INFILE” statement as long as you have a mysql prompt on our system and have uploaded the data file to your account here first.
The “LOAD DATA LOCAL INFILE” statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.
To import a file this way, first upload your data file to your home directory on our system with FTP or SCP. Then get a shell prompt on our system, and then a MySQL Monitor prompt so that you can issue the SQL that will import your file.
For example, suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line. You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.
To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:
LOAD DATA LOCAL INFILE ‘/importfile.csv‘
INTO TABLE test_table
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(field1, filed2, field3);
The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL test_table as columns field1, field2, and field3 respectively. Many of the above SQL clauses are optional and you should read the MySQL documentation on the proper use of this statement.
2. Using a script to parse and import the file
You can also write a script in any programming language that can connect to MySQL (such as PHP) to open your data file, break it up into an array of lines that each represent a row of data, split each line up by the delimiter character (such as a comma ‘,’, tab ‘\t’, semicolon ‘;’, space ‘ ‘, etc.), and then perform invididual MySQL INSERT queries (one INSERT for each line) to insert all your data from the file into the appropriate table fields.
Such scripts are not difficult to write in less than 15 lines and can import data from text files just as effectively as a LOAD DATA LOCAL INFILE command. A working example script written in PHP appears below in the Annotations.
3. Importing a mysqldump
If your data file actually comes from another MySQL database, and not from Excel or any other source, then the most direct way to export and import your data would be to dump out your table or entire MySQL database on the original database server using the mysqldump command, FTP the resulting dump file to your account here, and then import the dump file at a shell prompt.
Back up From the Command Line (using mysqldump)
If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:
- [uname] Your database username
- [pass] The password for your database (note there is no space between -p and the password)
- [dbname] The name of your database
- [backupfile.sql] The filename for your database backup
- [–opt] The mysqldump option
For example, to backup a database named ‘Tutorials’ with the username ‘root’ and with no password to a file tut_backup.sql, you should accomplish this command:
This command will backup the ‘Tutorials’ database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.
With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the ‘Tutorials’ database accomplish the command below. Each table name has to be separated by space.
Sometimes it is necessary to back up more that one database at once. In this case you can use the –database option followed by the list of databases you would like to backup. Each database name has to be separated by space.
If you want to back up all the databases in the server at one time you should use the –all-databases option. It tells MySQL to dump all the databases it has in storage.
The mysqldump command has also some other useful options:
–add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.
–no-data: Dumps only the database structure, not the contents.
–add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.
The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.
If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.
If you want to extract the .gz file, use the command below:
Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:
- Create an appropriately named database on the target machine
- Load the file using the mysql command:
Have a look how you can restore your tut_backup.sql file to the Tutorials database.
To restore compressed backup files you can do the following:
If you need to restore a database that already exists, you’ll need to use mysqlimport command. The syntax for mysqlimport is as follows:
Set mysql bind address
Before you can access the database from other computers in your network, you have to change its bind address. Note that this can be a security problem, because your database can be accessed by other computers than your own. Skip this step if the applications which require mysql are running on the same machine.
sudo vi /etc/mysql/my.conf
and change the line:
to your own internal ip address e.g. 192.168.3.209
If your ip address is dynamic you can also comment out the bind-address line and it will default to your current ip.
If you try to connect without changing the bind-address you will recieve a “Can not connect to mysql error 10061”.
Then restart mysql service :
sudo service mysql restart
First Login as Root: mysql> mysql -u root -p Then Creating New User(ex.ngs): mysql>
CREATE USER 'ngs'@'localhost' IDENTIFIED BY 'ngspass';Then Provide the Permissions using following: mysql>
GRANT ALL PRIVILEGES ON *.* TO 'ngs'@'localhost'
WITH GRANT OPTION;
MySQL database server is now owned by Oracle (formally Sun Microsystems) but can be installed using command line options without compiling anything under Ubuntu Linux. Open a terminal and type the following commands to upgrade package database:
sudo apt-get update
sudo apt-get upgrade
Install MySQL Server :
Type the following command to install latest stable MySQL server software:
sudo apt-get install mysql-server mysql-common mysql-client
If you want to reset root password from terminal use the following command:
/usr/bin/mysqladmin -u root password type_new_password_here
If it says Permissions Denied then try the above command with sudo keyword.
Then Restart the MySql Daemon by following command:
sudo /etc/init.d/mysqld restart
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
if you are new to installing mysql server you might probably face this error quite often if you type mysql in the shell prompt.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
- To fix:
First start the mysql daemon, then type mysql
> /etc/init.d/mysqld start
It worked for me!
- To update mysql root password
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('new-password') WHERE user='root';
mysql> FLUSH PRIVILEGES;
- To add a new user to mysql
1. First login as root then create a database ‘demo’
> mysql -u root -p
Enter password:(enter your password)
mysql> create database demo; //creates database namely demo.