If you remember from a few post ago I talked about doing a project with the Elder Scrolls III Morrowind. I just recently started working on it. The goal of the posts in this series is to show this project step by step.
The first thing to do is get the data to work with. The Unofficial Elder Scrolls Pages were helpful in allowing their data to be used. The data received seems to be from the construction set of Morrowind, Oblivion, and Skyrim. For this project the data will be from Morrowind.
For this post MySQL Command Line Client will be used on Windows while the MySQL Workbench will be used on Linux
After installing MySQL Server on the Windows PC its time to setup the user accounts and load the database.
Enter the command: CREATE DATABASE [your database name];
For this project csdata.sql received from The Unofficial Elder Scroll Pages will be the file we load our database from. For some reason csdata < csdata.sql did not work. So the following command was used instead.
Enter the command: use [database name]; source [sql file]
More information about executing sql files can be found here.
Time to create the remote account.
The first thing to do is to create the user using the following command.
Enter the command: CREATE USER ‘[user name]’@'[user location]’ IDENTIFIED BY ‘[user password];
The user name and user password should be simple to understand what to input there but user location may not be. In a future post I may go more into networking but for now I’ll keep it simple and just focus on how it applies here. The user location is the host or in more simple terms the computer that will be used when logged in to this user account. If the user account will just be used on the computer hosting the database localhost can be used.
Example command: CREATE USER ‘john’@’localhost’ IDENTIFIED BY ‘password123’;
But for this project the data will be access from a remote host. On Windows the host address can be found by entering the following on cmd.exe. Cmd.exe can be access in many ways. Hold the windows key and press the r key, then enter cmd into the prompt. Also, pressing the Windows Key and entering cmd will work to. Once cmd, also known as command prompt, is open enter the following.
Enter the command: ipconfig
In this example Wireless LAN Adapter Wi-Fi 2 is being used to connect to the network. The address of our host would be the IPv4 address. For example IPv4 may be 10.0.0.123.
Example command: CREATE USER ‘john’@’10.0.0.123’ IDENTIFIED BY ‘password123’;
On Linux open the terminal and enter the following.
Enter the command: ifconfig
If you would like more info on ifconfig I suggest entering the following to read the man page.
Enter the command: man ifconfig
In this example wls3 is used to connect to the network. The address of this host is the inet addr. For example the inet addr could be 10.0.0.124.
Example command: CREATE USER ‘john’@’10.0.0.124’ IDENTIFIED BY ‘password123’;
After our user is made permissions need to be given to the account. Since no data is going to be changed this user only needs to have the select permission, basically just view the data. This user also received this permission on all databases. So if another database is added to the server the user will also be able to view data from it.
Enter the command: GRANT [permission, * for all] . [database, * for all] to ‘[username]’@'[user location]’;
Finlay enter the following command to apply the changes.
Enter the command: FLUSH PRIVILEGES;
More information on adding users can be found here.
Now we just need to set up our remote computer to connect to the server to access the database.
On the remote computer MySQL Workbench is installed. A MySQL connection needs to be added to access the database. Click the plus button next to MySQL Connections to setup a new connection. For this project the standard connection will be used. Type in the hostname. An example would be localhost or 10.0.0.123. Next enter a connection name. The default port for MySQL is 3306. Then type in the user name. To make sure everything is setup right click the Test Connection button. A password may be asked for. If the connection is successfully made click OK. Next just click on the new connect that came up to access the database.
Now that the database is set up the data can now be worked on. Next time the real fun begins.
Links of Interest