Friday, February 12, 2010

Instructions to Import GeoNetMap in MySql

SkyHi @ Friday, February 12, 2010

The following article assumes you have already created a database or are planning to import it into an existing database.

1. Copy all the map files into the databases data directory. For example: c:\mysql\data\db_name

2. Download the MySqlGeoNetMapImport.sql script file and save the file to the current directory.

3. Open a MySql command window, and select the target database. ie Use db_name

4. Run the following command

source MySqlGeoNetMapImport.sql;

4. You can run the following query to test whether the importation succeeded:

select city
From Cities c
inner Join Subnets s on s.CityId=c.CityId
Where SubnetAddress='207.71.204';

If the tables are created succesfully but no data is in them, try executing this code here.

Instructions on performing a database update

1. Copy all the updated map files into the databases data directory. For example: c:\mysql\data\db_name

2. Open a MySql command window and select target database. ie Use db_name

3. Download the MySqlGeoNetMapUpdate.sql script file and save the file to the current directory.

4. Run the following command:

source MySqlGeoNetMapUpdate.sql;

Reference Executing SQL Statements from a Text File

The mysql client typically is used interactively, like this:

shell> mysql db_name

However, it is also possible to put your SQL statements in a file and then tell mysql to read its input from that file. To do so, create a text file text_file that contains the statements you wish to execute. Then invoke mysql as shown here:

shell> mysql db_name < text_file

If you place a USE db_name statement as the first statement in the file, it is unnecessary to specify the database name on the command line:

shell> mysql <>

If you are already running mysql, you can execute an SQL script file using the source command or \. command:

mysql> source file_name
mysql> \. file_name

Sometimes you may want your script to display progress information to the user. For this you can insert statements like this:

SELECT '' AS ' ';

The statement shown outputs .

You can also invoke mysql with the --verbose option, which causes each statement to be displayed before the result that it produces.

As of MySQL 5.0.54, mysql ignores Unicode byte order mark (BOM) characters at the beginning of input files. Previously, it read them and sent them to the server, resulting in a syntax error. Presence of a BOM does not cause mysql to change its default character set. To do that, invoke mysql with an option such as --default-character-set=utf8.

For more information about batch mode, see Section 3.5, “Using mysql in Batch Mode”.

User Comments

Posted by Sanjay Ichalkaranje on October 14 2003 4:21am[Delete] [Edit]

To run two sql scripts at a time you can use cat command available in Linux.

cat file1.sql file2.sql | mysql -u USERNAME -p

Posted by Jeff Zohrab on December 4 2005 6:14pm[Delete] [Edit]

For windows users, use forward slashes for the path delimiters. You also don't need to enclose the path to the file in quotes. E.g., the following works:

mysql> source C:/Documents and Settings/My name here/My Documents/spike_loadingMySQLDB/createTables.sql;

Posted by Dan Zaner on April 12 2006 9:43pm[Delete] [Edit]

If you are attempting to use a batch file that is UTF8-encoded (which will handle all your accented latin characters as well as chinese, japanese, etc.), make sure that you start 'mysql' with the '--default-character-set=utf8' option or you will end up with whatever the server default is. If the server default is not utf8, your batch file will most likely produce undesireable results.

Posted by theo theunissen on October 21 2009 4:20pm[Delete] [Edit]

We use subversion for both code and MySql database changes (script and data).

1. We have created a file /path/to/script/database.sql that contains the database changes. This file is committed
2. We have a bash script to update both code and executes MySQL changes

The script looks like:
svn up
mysql -u -p -h < /path/to/updated_script/database.sql
If the file database.sql is empty, then nothing is changed.


Load text into mysql command line:

1. create a database.

>create databases test;

2. copy the extracted zip files(files you wanted to import) into the /var/lib/mysql/test/

3. #cat MySqlGeoNetMapImport.sql //comment out what you want to load

4. a . mysql -uroot -pPassword
b. >use test;
c. > source /var/lib/mysql/test/MysqlGeoNetMapImport.sql

5. done.