Question

1. Write three command statements to back up the `Orders` Table for data only, structure with data and structure only using mysqldump. 2. Write the command statement to import the data using mysqlimport from customers.csv file. 3. What's the difference to use the LOAD DATA LOCAL INFILE vs mysqlimport. 4. Write SQL statements: a. Create a database user "cis495_dev1" with password "dolphins". b. Create the database "cis495Demo". c. Grant user "cis495_user1" with "select" ,"delete" and "execute" permission to DB "cis495Demo". d. Revoke the privilege "delete" for the user "cis495_user1". 5. List three ways ( SQL statement: update, alter, set) to change the password from "dolphines" to "icando" for the user "cis495_user1". 6. Explain the difference between user and role.

Answer 1

:: Solution ::

1. Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database.

# To export to file (data only)
mysqldump -t -u [user] -p[pass] -t orders > orders_data.sql

# To export to file and data

backup: # mysqldump -u[user] -p[pass] [orders] > dumpfilename.sql

# To export to file (structure only)
mysqldump -d -u [user] -p[pass] -d orders > orders_structure.sql

2. # To Import the data

$ mysqlimport -u [user] -p[pass] ~/customer.csv

Note : The options in use are:

• The -u flag indicates that the MySQL username will follow.
• The -p flag indicates we should be prompted for the password associated with the above username.
• database_name is of course the exact name of the database to export.
• The > symbol is a Unix directive for STDOUT, which allows Unix commands to output the text results of the issued command to another location. In this case, that output location is a file path, specified by output_file_path.

3. Differnce between The LOAD data local infile and mysqlimport

The LOAD DATA statement reads rows from a text file into a table at a very high speed. LOAD DATA is the complement of SELECT ... INTO OUTFILE. To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA. The syntax of the FIELDS and LINES clauses is the same for both statements. At some MySQL installations, the LOCAL loading capability may have been disabled for security reasons. If that is true at your site, omit LOCAL from the statement and specify the full pathname to the file. See Specifying the Datafile Location for more information on local versus non-local data loading.

MySQL also includes a utility program named mysqlimport that acts as a wrapper around LOAD DATA so that you can load input files directly from the command line. The mysqlimport command that is equivalent to the preceding LOAD DATA statement looks like this, assuming that mytbl is in the cookbook database

Where as in mysqlimport, as with other MySQL programs, you may need to specify connection parameter options such as --user or --host (Starting and Stopping mysql).

The following list describes LOAD DATA’s general characteristics and capabilities; mysqlimport shares most of these behaviors. There are some differences that we’ll note as we go along, but for the most part.

Thank you...