How to Upload Mysql Database to Web Host

Uploading several rows of data from a text, csv, and excel file into a MySQL tabular array is a routine task for sysadmins and DBAs who are managing MySQL database. This article explains 4 practical examples on how to import data from a file to MySQL table using both mysqlimport and "load information local infile" method. Instead of importing data, if you want to backup and restore MySQL database, delight use mysqldump or mysqlhotcopy.

Create an employee table and employee.txt datafile

For the examples mentioned in this article, let us create a very uncomplicated employee table with iii columns–employee number, employee name and job.

# mysql -u root -ptmppassword mysql> use exam Database inverse mysql> create tabular array employee     -> (     -> empno int,     -> ename varchar(15),     -> job varchar(10)     -> ); Query OK, 0 rows affected (0.01 sec)

Create a test datafile employee.txt with fields delimited by tab equally shown below.

# cat employee.txt 100     John Doe        DBA 200     John Smith      Sysadmin 300     Raj Patel       Developer

i. Upload tab delimited datafile to MySQL table

Use mysqlimport to import the employee.txt datafile to employee table in test database, as shown below:

# mysqlimport -u root -ptmppassword --local test employee.txt test.employee: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Verify that the records got uploaded successfully.

# mysql -u root -ptmppassword mysql> use test; mysql> select * from employee; +-------+------------+-----------+ | empno | ename      | chore       | +-------+------------+-----------+ |   100 | John Doe   | DBA       | |   200 | John Smith | Sysadmin  | |   300 | Raj Patel  | Developer | +-------+------------+-----------+ 3 rows in prepare (0.00 sec)

Annotation: In mysqlimport, the name of the datafile should match the proper name of the table. The extension of the datafile can be anything. In the above example, only employee.* datafile can be used to upload information to employee tabular array. You'll get the following mistake message when the filename is not same as tablename:

# mysqlimport -u root -ptmppassword --local test emp.txt mysqlimport: Error: Tabular array 'test.emp' doesn't exist, when using table: emp          [Note: The table name is employee. Then, datafile name should exist employee.*]        

2. Import multiple datafiles into multiple MySQL tables

The following case uploads information from two different datafiles to two different tables. i.e It uploads employee.txt to employee tabular array and managing director.txt to managing director table.

# mysqlimport -u root -ptmppassword --local test employee.txt managing director.txt

three. Use LOAD Data LOCAL INFILE to upload data to MySQL tables

The mysqlimport client is simply a command-line interface to the LOAD Data LOCAL INFILE SQL statement. Most options to mysqlimport correspond straight to clauses of "load data local infile" syntax. Y'all can perfrom the aforementioned upload explained in example#ane using "load data local infile" instead of mysqlimport every bit explained beneath:

# mysql -u root -ptmppassword mysql> apply test; mysql> LOAD DATA LOCAL INFILE '/home/ramesh/employee.txt'     -> INTO TABLE employee     -> FIELDS TERMINATED BY '\t'     -> LINES TERMINATED BY '\n'     -> (empno, ename, task); Query OK, 3 rows affected (0.01 sec) Records: 3  Deleted: 0  Skipped: 0  Warnings: 0  mysql> select * from employee; +-------+------------+-----------+ | empno | ename      | job       | +-------+------------+-----------+ |   100 | John Doe   | DBA       | |   200 | John Smith | Sysadmin  | |   300 | Raj Patel  | Programmer | +-------+------------+-----------+ 3 rows in set (0.00 sec)

iv. Near frequently used mysqlimport options

The most oftentimes used mysqlimport options are shown in the instance below. Most of these options are self explanatory.

• shrink: Compress all information sent betwixt the client and the server
• delete: This pick is very handy when you lot desire to empty the tabular array before importing the text file
• local: Read input files locally from the client host
• lock-tables: Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.

# mysqlimport \     --user=root \     --password=tmppassword \     --columns=empno,ename,job \     --compress \     --delete \     --fields-optionally-enclosed-by='"' \     --fields-terminated-by='\t' \     --fields-escaped-by='' \     --lines-terminated-by='\n' \     --local \     --lock-tables \     --verbose \     test employee.txt

Output of the higher up mysqlimport command:

Connecting to localhost Selecting database test Locking tables for write Deleting the former data from table employee Loading data from LOCAL file: /home/ramesh/employee.txt into employee test.employee: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0 Disconnecting from localhost

cotemageting.blogspot.com

Source: https://www.thegeekstuff.com/2008/10/import-and-upload-data-to-mysql-tables-using-mysqlimport/

Share this post