In this hour we will learn how to insert data into out database tables using a variety of methods.Alternatively if we wanted to simplifiy this and reduce the amount of typing we could use:
The INSERT command
This MySQL command is the first that we will use to insert data into our database tables. To use this command we need to be in MySQL monitor. So open up a connection to your database and login as normal.
A detailed account has already been written for using this command, and can be accessed via the following link, click here.
It should be noted that although you do not need to specify the names of the column within your table when using the INSERT command. However if you don't then you must ensure that each field within your table has an entry in your INSERT statement even if its empty or is going to use the default value. For example surpose you have a table called employees and you want to add a new record. The employees table has the following fields;employee_id, employee_startdate, employee_moddate, firstname, lastname, nickname
You are wanting to add the following data into a new record in this table:
James Hummersknott, start date: today. James does not have a nickname. The moddate is obviously also going to be today as thats when you lasted modified the record and the employee_id is your primary key and therefore auto increments.
The command we would use to insert this data is as follows:INSERT INTO employees
(employee_id, employee_startdate, employee_moddate,
firstname, lastname, nickname)
VALUES
('0', now(), now(), 'James', 'Hummersknott', '')
;
INSERT INTO employees
VALUES
('0', now(), now(), 'James', 'Hummersknott', '')
;
The LOAD DATA INFILE methodThis is the second method of importing data into your database and again this has been documenetd previously, click here to view the detail blog entry about how to use the LOAD DATA method.
Using mysqlimport
This is our 3rd method of importing data into our database. the mysqlimport must be run from a command prompt and uses a plain text file that contains the data that you want inserting. This command has various switches that you can alter to suit you own personal needs. The following is the basic summary of the syntax that is used for this method of data insertion:mysqlimport [options] databasename textfileOkay let's look at that command, the first bit is calling the mysqlimport function. Then we have the chance to enter any options that we wish to use, common options here are:-L to use a local file
--fields-enclosed-by
--fields-escaped-by
--fields-terminated-byI have only used 3 of these along with a couple of other ones when I perform my own data insertion using this method. This is the complete command syntax that I used to successfull insert some data from a text file:mysqlimport -L -v -u jg3723 -pmypassword
--fields-terminated-by=::: --fields-enclosed-by=\"
jg3723 master_name.txtThis successfully entered the contents of master_name.txt into my database called jg3723. It should be noted that this method of data insertion will import the data into the table of the same name as the text file, so in the case of the above example it inserted the data into a table called master_name.
My master_name.txt file consisted of the following data:
"0":::now():::now():::"Jayson":::"Doe"
"0":::now():::now():::"Donna":::"Smith"
"0":::now():::now():::"Tabitha":::"Carr"
"0":::now():::now():::"Naomi":::"Bell"
Still to be documeneted - sorry!
Still to be documeneted - sorry!
Still to be documeneted - sorry!
Still to be documeneted - sorry!
Still to be documeneted - sorry!
Well that's the end of this particular blog entry and I hope that some of the information is of help to you. Please feel free to comment, all questions will be answered to the best of my ability.
All the best Jayson
Okay this is the exact procedure that I used to perform this data insert. Using SSH secure shell open a terminal window to the OU server. Change to your web directory using the following command syntax:cd webLog into the MySQL monitor using the following syntax, changing the jg3723 to your OU username:mysql -u jg3723 -pWhen prompted enter you OU PI number remember letters should be in CAPITALS. Select the database to be used, in this case it should be the same name as you OU username, using this command syntax:
USE jg3723;Now its time to write the INSERT statement, for test purposes you could use the following, which is what I entered:
INSERT INTO master_nameHopefully, you should see the following message after pressing the ENTER key.
VALUES ('',now(),now(),'Amanda','Gurney');Query OK, 1 row affected (0.00 sec)Well that's the INSERT method successfull used, so it's now onto the LOAD DATA method next.
My ConclusionWith this method I pre wrote a DDL script and a plain text file that contained the data to be imported, these two files were then uploaded to my OU web directory If you would like to use my DDL script and data file click here to down load them.Hopefully you will still have your SSH terminal window open and logged into your MySQL database. If not please connect and login to your database again.
You now need to change the DDL file to point to your own database; using Notepad open the loaddata.sql file and change the first line so it reads the name of your database. Then save the file and close it.Now upload the DDL script and data file to your OU web directory. To check that they are there use the following syntax:lsHopefully you will see the a list similar to mine below:[jg3723@ttcfm web]$ ls
all_names.cfm createtables.sql loaddata.sql mytidy.sql
contactDB.sql eca master_name.txt
[jg3723@ttcfm web]$Okay the files are there so let's import the data. This is done by using the following syntax:source loaddata.sqlHopefully you will see the following messages displayed after pressing the ENTER key.Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
Well that's the end of my LOAD DATA and INSERT summary, you should now be able to alter the various commands and files to import any data that you wish to insert into your database. Have fun!
All the best Jayson
Kamae
Fudo Dachi
Sanchin Dachi
Zenkutsu Dachi
Uchi Waza
Chudan Tsuki
Keri Waza
Kin Geri
Uke Waza
Jodan Uke
Kata
Taikyoku sona Ich
Kumite
Not required
Renzoku Waza
Mae Geri Gyaku Tsuki
I was pleased to accept this grade in July 2004. I say please because I feel that if I did not want to accept the belt, grade and responsibilty I could have declined the grade/belt when it was offered to me by my Sensei.
What responsibility I hear you say.
Well okay maybe at this low grade it may look to on-lookers that you have none, but in reality you do. You have the responsibility to yourself, your sensei and others to continue to demonstrate the levels of standard that have been achived by yourself whilst grading. Admitted, once one progresses up throught the grades, more responsiblity is emposes on you, but I feel that at all levels you have a responsibility to demonstate those techniques that you have been accessed on to to all newcomers. Doing this as an early stage, I feel will make you a better dan grade once you achieve that level of experience.
Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.
Sensei Phil Snewin
Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.
Official Website: kamishin ryu karate do
Sensei Fred Bateman
Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.
Official Website: kodokan martial arts
Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.
Sensei Fred Bateman
Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum.
Although obtaining a blackbelt is one of my life long ambitions, I have decided that this should not be the main aim of my martial arts training. Continuous training, personal develpment and commitment to training will I am certain leaded me to my life long ambition. But unlike some people, training and development and learning will not stop there, to me that will be the start of my next level of marital arts training.
- 8th Kyu grading syallabus
- 7th Kyu grading syallabus
- 6th Kyu grading syallabus
On the 24th January 1970 I was born to my parents, Susan Water and Barry Gurney in Chelsea, London.
I left secondary school with only my Certificate of Secondary Education qualifications (8 in total) and I decided that it was a case of going to 6th Form to try and get a few General Certificate of Secondary Education under my belt.
I left 6th Form with a few better grades for some of my Certificates of Secondary Education (9 in total), newly aquired General Certificate of Secondary Education (3 in total) plus a couple of other qualifications; one by the Royal Society of Arts and a newly form qualification which I was one of the first in the county to take, namely a Certificate of Pre-Vocational Education.
4 th January 1988, I waved good-bye to my mum at the train station in my home town of Ashford and set off on my journey to join the Royal Corps of Transport, whos training depot was based in sunny Aldershot.
After completing my basic military training I embarked on trade training where I gained my Heavy Goods Vehicle License. It was then time to join my first active unit, 66 Squadron Royal Corps of Transport, based in Tidworth, Hampshire. After a few month at the unit it was time to head of to Germany for a NATO training exercise, which unbeknown to me was going to be the changing point of my military career. After just 3 days in Germany, I substained a serious injury to my spine, which took several hospitals and doctors many hours to decide what needed to be done.
Finally, the British Military decided that I needed a spinal fusion which they performed on my in the March. After spending some time off to recover, it was time for intense rehabilitation, which lasted several months. I was unhappy at this as it meant I was unable to go to Cyprus with the main unit, instead I was left behind on reserve party duties. Things turnt out ok in the end as when I returned to my unit I was promoted to Lance Corporal.
Various posting around the UK, and in January 1993 I was medically discharged from the Army.1994 - 1997
Time spent recoverying form ongoing medical problems realting to my spinal injury. Mid 1997, I went to college to gain qualifications in Information Technology and started to do some teacher training related courses.
Gained an NVQ Level 3 in Implementing IT Solutions and part of my NVQ Level 4 TDLB. Started work as an on-site maintenance engineer.
After careful consideration, I started my own IT consultancy company and my first major client was Orange PCS. Went to Germany and Italy to do consultancy work for two International Telecom companies.
Started work for an International Internet hosting company - Senior Network Administrator and Project Manager. Designed and implemented a corporate wide Microsoft messaging platform. Obtained Microsoft certification status.
Started to study with the Open University, with the aim of gaining various IT and Computing qualifications whilst on-route to acheiving a BSc Honours degree.2004
Obtained 8th & 7th Kyu in Kamishin Ryu Karate. Reunited with my father after 28 years at Darlington Railway Station. Meet my 3 sisters, Elizabeth, Nicola and Lauren for teh first time in December 2004. Also meet about 35-40 other relatives from my fathers side of the family.
[NATIONAL] CHAR (M) [BINARY]
county CHAR (75)
pg 80
[NATIONAL] VARCHAR (M) [BINARY]
pg 79
BIGINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
BIT
not used as yet
BLOB
not used as yet
CHAR
not used as yet
DATE
not used as yet
DATETIME
not used as yet
DECIMAL [ (M [,D] ) ] [ZEROFILL]
not used as yet
DOUBLE PRECISION [ (M,D) ] [ZEROFILL]
not used as yet
DOUBLE [ (M,D) ] [ZEROFILL]
not used as yet
ENUM ('value1' , 'value2' , 'value3', and so on)
pg 80
FLOAT [ (M,D) ] [ZEROFILL]
not used as yet
INT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
INTEGER [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
LONGBLOB
not used as yet
LONGTEXT
not used as yet
MEDIUMBLOB
not used as yet
MEDIUMINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
MEDIUMTEXT
not used as yet
NUMERIC (M,D) [ZEROFILL]
not used as yet
REAL [ (M,D) ] [ZEROFILL]
not used as yet
SET ('value1', 'value2', 'value3' and so on)
not used as yet
SMALLINT [ (M) ] [UNSIGNED] [ZEROFILL]
pg 79
TEXT
pg 81
TIME
not used as yet
TIMESTAMP [ (M) ]
not used as yet
TINYBLOB
TINYINT [ (M) ] [UNSIGNED] [ZEROFILL]
not used as yet
TINYTEXT
not used as yet
YEAR [ (2|4) ]
not used as yet
=
not used as yet
< >
not used as yet
!=
not used as yet
<= not used as yet
< not used as yet
>=
not used as yet
>
not used as yet
<=>
not used as yet
COALESCE (list)
not used as yet
expr BETWEEN min AND max
not used as yet
expr IN (value, and so on)
not used as yet
expr NOT IN (value, and so on)
not used as yet
INTERVAL (N,N1,N2,N3)
not used as yet
IS NOT NULL
not used as yet
IS NULL
not used as yet
ISNULL (expr)
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERESTRING FUNCTIONS
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
COMMAND HERE
not used as yet
The ColdFusion Express version 4.0 that is on the Cert in Web Apps Development CD-ROM has caused me numerous nightmares when it came to configuring my MySQL ODBC data source. My answer was to install a copy of the latest release ColdFusion MX 7 Developer Edition. This version is as far as I am currently aware FREE for development use so there is no time limit on its usage.
Okay let’s get the download! Using you web-browser, enter the following URL:
http://www.macromedia.com/cfusion/tdrc/index.cfm?product=coldfusion
If you are not already registered with Macromedia, you will need to do this and then revisit the above URL. Registration is FREE, simple and rather painless. When you return to the download page select the option to download ColdFusion MX 7 Developer Edition, which should be at the top of the page, then select the English Windows 275.79 MB option using the select button, then click on download. Save the file a suitable location on your system, so that you can easier retrieve it later.
BE WARNED that this download can take some time depending on you particular internet connection.
Whilst the installation file is downloading, it’s a good idea to gather the necessary information ready for your installation. Please remember that this installation brief is for use with an OpenSA web-server (Apache), which is distributed on the Cert in Web Apps Development CD-ROM. Note the following information which is what I personally used for my installation, I also used the default installation for OpenSA, so if you decided to install in any other manner you will need to alter the information below to mirror your own installations:
What is the serial number for ColdFusion MX 7?
Use the developer edition option
What is the type of installation?
Server Configuration
ColdFusion MX 7 installation directory?
C:\CFusionMX7
Configure web server or use built-in web server?
Configure
Choose web server to configure?
Apache
Apache configuration directory?
C:\OpenSA\Apache\Conf
Apache path to server binary?
C:\OpenSA\Apache
ColdFusion MX Administrator password?
You choose this but keep it a secret
Do you want to enable RDS?
NO
Okay you have the downloaded installation file and the required installation notes. Let’s get on with the main installation then. Ensure that the Apache web server is running you can do this by checking the service via the Windows XP Administrators control panel. Shut down all previous ColdFusion services if you have previously installed them – again this can be performed via the Windows XP Administrators control panel. Now double click on your downloaded installation file and follow the on screen instructions. Be sure to use the information that we gathered in Step 2, to ensure that the correct information is given to the installation routine. Hopefully all went well for you and you now have an installed ColdFusion MX 7 server.
Using the ColdFusion Administrator which is accessed via your web-browser and is delivered to you via your local web-server we are now going to configure your MySQL data source. Open your web-browser and enter the following address:
http://localhost/cfide/administrator/index.cfm
Enter the password that you set during the setup routine then click on the login button. You will then be in the ColdFusion Administrator, so now on the left you will see a link to Data & Services, click on this and then click on the Data Source link. We are now going to add a new data source, so enter the name of the data source, for the purposes of this installation guide enter the following: chelsea then using the selection button below select MySQL (3.x) as the driver. Then click the add button.
The next page is where we are going to configure this new data source. Enter the following information into their respective boxes:
Database - contactDB
Server - localhost
Username - root
Password - your MySQL root password
Then click the submit button, if all was ok with the data that you have just entered, the screen will change back to the original Data Source screen, BUT the newly created data source named chelsea will be verified as OK.
Using your HTML editor or Notepad, enter the following test code and then save then save it as odbctest.cfm Sample test script is as follows:<>html<>
<>head<>
<>title<>ColdFusion ODBC Test< /title >
< /head >
<>body<>
<>cfquery name="databases" datasource="chelsea" debug="yes">
show databases;
< /cfquery >
<>cfoutput<>
<>p<>Today is #DateFormat(now())#< /p >
< /cfoutput >
< /body >
< /html >Once the above has been created and save it to your root web directory, open up your web-browser and request the page via you local web-server. Calling the test file is simple - enter the following into your web-browsers address bar:
http://localhost/odbctest.cfm
If all is working correctly then the page will display the current date in your browser. Please note that the script only attempts to make a connection to your data source, and that no database data will be displayed. If the current date is not displayed then unfortunately, something is a miss. But if all goes well this will not be the case.
If you managed to get the current date displayed in your web-browser, then it looks like you have successfully installed and configured ColdFusion MX 7 Developer Edition to connect and access your MySQL data source. WELL DONE!
So what next I hear you say – well the world is your oyster as they say. Now that this has been configured and tested, you can start to develop your own ColdFusion pages. I hope that this BLOG helps some of you with you local installation of ColdFusion MX 7, if you have any comments feel free to post them.
BTW - this was written from memory so I have my fingers crossed and hope that there are no errors, but I am certain that you will be the first to tell me if there is - any errors will be investigated and subsequent amendments will be made.
All the best Jayson
The first task that I would recommend you do is create a root password, as by default the installation of MySQL leaves this all important user account unprotected as the password is left blank.
So who do you create/set the password for the root user? Well we use the MySQL monitor, so open a command prompt window and navigate to your MySQL directory. This will depend on where you installed MySQL to and also on your operating system. For the purpose of this BLOG I will assume you have installed your system the same as I have done locally using a Windows XP Pro system. I installed MySQL to C:\mysql so when I open a command prompt I enter the following syntax to access the mysqladmin utility:cd c:\mysql\binNow enter the following command at the command prompt:mysql -u root -pIf no password has been set, which should be the case for you if this is performed directly after a fresh installation of MySQL, you can just press the ENTER key. The following command will allow you to set a password for your super-duper root user:SET PASSWORD FORNow that you have set the new password its advised to flush your privileges, this is performed using the following syntax:
'root'@'localhost'=PASSWORD('yourpassword');FLUSH PRIVILEGES;
Task 3 - Delete the remote root userThis step will delete any anonymous users that are installed Note that the following command uses two single ' characters and not a "DELETE FROM mysql.user WHERE User=''
Now its time to removes the remote root user using the following command syntax:DELETE FROM mysql.user
WHERE User='root'
AND Host !='localhost';
Now we are going to delete the test database that the installation process creates. Use the following command syntax:DROP DATABASE test;
Now its time to remove any un-necessary privileges from the mysql database using the following command syntax:DELETE FROM mysql.db
WHERE Db ='test'
or Db = 'test\\_%';
Let's now flush the server's privileges and we are then ready to quit the MySQL monitor and start to use our lovely new MySQL server. Flushing is performed using the following command syntax:My ConclusionFLUSH PRIVILEGES;
Well that now concludes this BLOG and hopefully has helped you cleanup your newly installed MySQL server. If you have any comments please feel free to post them and I will try and answer them to the best of my ability.
All the best Jayson
The overal securing of a MySQL server installation requires various uniquie steps to be performed. It should also be noted now that once this procedure has been performed it still requires monitoring to ensure any new security breaches and back-doors etc are quickly identified and secured. I therefore recommend that you subscribe to a couple of the various security bulletins that are available on the WWW.
Okay that being said - lets identify the areas to be addressed:
As previously mentioned, at this stage of my blog development, I have not had the opportunity to document or actually follow the above staged in fine detail. Given time I shall carry out a detailed investigation of these stages.
- Step 1: Install MySQL on a Recent, NT-based version of Windows
- Step 2: Install MySQL on an NTFS File System
- Step 3: Install MySQL on a Standalone Machine
- Step 4: Install the Latest Production Version of MySQL
- Step 5: Secure the MySQL User Accounts
- Step 6: Disable TCP/IP Access
- Step 7: Bind the TCP/IP Address
- Step 8: Firewall the Server
- Step 9: Run The MySQL Service as a Limited User
- Step 10: Encrypt the Data Folder
- Step 11: GRANT the Minimum Privileges Necessary
- Step 12: Change the Name of the Root User
Some of these steps have already been documented in my previous blog titled 'MySQL Installation Cleanup'.
As you can see with a few simple step you can easierly secure your newly installed MySQL server. Obviously, security is an on-going task and is also a specialised area. There are however, a vast amount of detailed articles, guides etc on the WWW to aid you in your quest to keep your server secure - Good luck and please feel free in sharing your comments.
All the best Jayson
Hour 7 - Creating your database tables - Part IOkay, lets see about creating our database. One way to do this is via the mysqladmin using the following command:mysqladmin -u username -p create datebasenameI have not added the password for the user, instead it will be prompted once the command has been entered. Alternatively, if you want to include the password within the command use the following syntax:mysqladmin -u username -ppassword create datebasenameSo lets see the whole command with dummy username, password and database we want to create:mysqladmin -u root -pA51gA23 create chelseafcOnce the database has been created it needs to have its permissions and privileges set. Its advised to create a new user other than the main root user account who can administer your newly created database. We can do this using the MySql monitor using a command similar to the following:mysql -h localhost -u root -pYou will be prompted for the root user password, enter it and press enter - Remember that its case sensetive. We the need to use the mysql database which holds all the servers permission and accounts etc.use mysql;Now we are ready to enter the command to create our new database admin user who we will call supercontact for the chelseafc database.GRANT ALL ON chelseafc.*
TO 'supercontact'@'localhost'
IDENTIFIED BY 'password';
Hour 8 - Creating your database tables - Part IIThis hour of the book is about the common data types that you can define within your MySQL tables and creating tables. So lets create our table:CREATE TABLE players (
player_id SMALLINT UNSIGNED,
player_dateadded DATETIME,
player_datemodified DATETIME,
firstname VARCHAR (75),
lastname VARCHAR (75)
);The above statement would create our table but we can improve this which is what we will do in the next hour. To issue the command to create the above table as it stands you would issue it via the MySQL monitor, remember you need to login to the monitor first.mysql -u supercontact -pEnter the password when prompted. Now we need to tell MySQL what database we wish to create the tables in:USE chelseafc;We can now enter the above statment into the monitor. Alternatively, we could use an external file, that contained the statement. This method is my prefered method of issuing commands, as it allows me to create them in advanced or via E-R diagram applications (E-R diagrams will be a subject for a later blog entry). To create the external file I use Notepad and enter the command(s) that I want to issue. The file is then saved to a suitable location with a meaningful name and the following file extension .sql. At the top of the file you should add a line that tells the server what database you want this table to be created in. So following our previous table creation example the new file would read:USE chelseafc;To use this file to create our database table use the following syntax:
CREATE TABLE players (
player_id SMALLINT UNSIGNED,
player_dateadded DATETIME,
player_datemodified DATETIME,
firstname VARCHAR (75),
lastname VARCHAR (75)
);mysql -u supercontact -p
The aim of this hour is to define our data types for the table fields, add keys, indexes and to create our final table creation statement. First of our table needs a primary key which can not contain a NULL value. This is done by adding the following to the chosen table field:NOT NULL PRIMARY KEY AUTO_INCREMENTThe next step is to think about what fields in the table should be indexed, indexing helps improve searches BUT care should be given as you can degrade a servers overall performance if you index all data fields. The syntax for indexing a field is as follows:INDEX idx_fn (firstname);So lets put this all together and create are full table creation statement ready for use.USE chelseafc;
CREATE TABLE players (
player_id SMALLINT UNSIGNED NOT NULL
PRIMARY KEY AUTO_INCREMENT,
player_dateadded DATETIME,
player_datemodified DATETIME,firstname VARCHAR (75),
lastname VARCHAR (75),
INDEX idx_fn (firstname)
);Thats all that needs to be entered in the text file, enter this and save it as myfirsttable.sql The time has arrived to create the table. This is done via the MySQL monitor using the following syntax:mysql -u supercontact -pYou will be prompted for the supercontact password, enter this and press ENTER. To check that the table was created we can use the following syntax from within the MySQL monitor:SHOW tables;
Well this concludes the main areas for Week 3. Obviously the book goes into more detail, but hopefully you will get the main points from this summary. Please feel free to comment, or post questions relating to any of the areas discussed in this Blog entry.
All the best Jayson
In this hour the aim is to become familiar with the following client tools:
- mysql - Built-in MySQL Monitor
- mysqladmin - Built-in Administrator tool
- mysqldump - Built-in backup utility tool
- phpMyAdmin - 3rd party add-on
- MySQL-Front - 3rd party add-on
- MySQLManager - Add-on
MySQL MonitorThis is a command-line utility that is shipped with MySQL. To access this utilty simply enter the following at a command prompt:mysqlIf you have not done anything to the initial installation of your server this simple command will log you into your MySQL server with full blown access permissions. This is due to the default installtion creates a root user with no password. As previously mentioned in other blog entries this is a security risk and MUST BE ADDRESS ASAP.Once the root user has a valid password, you will still be able to login to the server using the same command, but this time you also include a user and password string. See below for an example of login as the root user:mysql -u root -pThe above command will prompt you to enter the password for the root user, once this has been entered you have full access once again. Once you have logged into your server you will probably want to access a database, this is acheived by issuing a statement to the server telling it what database you wish to use. See the example statement below for the syntax:USE databasename;A full list of commands that are available to you when you are logged in can be displayed using the following:help;
How to exit the MySQL MonitorTo exit the monitor and ensure maximum security for your server always log-out correctly. This is again a simple process - just type in the following:exit;
Using mysqladminThis admin applicatins is used to create and drop databases and viewing the current server status. It should be noted that if you are using this application throught your ISP you may find that you are limited to what commands can be issued. You can obtain help by using the following:mysqladmin --help
To create a database:mysqladmin create databasename
To drop (delete) a database:mysqladmin drop databasename
Using mysqldumpThis application allows you to backup your database table structure and also create the relevant DDL scripts that can be used to recreate your entire database scheme. A extreamly useful option of this particular tool is the ability to export the schema to an external DDL file or sql script. This is performed using the following command:msqldump databasename > filename.sqlI recommend that you always use this function prior to dropping (deleting) a table/database. Like most of the command you can obtain help for this function using the following syntax:mysqldump --help
phpMyAdminThis 3rd party add-on provides all the same functions as previously discussed in this blog. It however, requires not only a MySQL server, but also PHP and a web-server. Its very popular in the web-hosting arena, as it allows users to manage their own web-space, database etc. For the purpose of this course namely TT380, I have not installed this locally, but it's on the 'To-do-list'.
MySQL-Front and MySQLManagerThese are another two excellent frontend GUI interfaces to allow you to manage your MySQL server. Again I have not installd these and its therefore unfair for me to comment on their uses etc. I do know that some course members are however using them. I instead opted to useanother 3rd party application, DBManager Professional v3.0.2. I have blogged seperately about this particular application.
This hour is aimed at teaching your about securing your installation of MySQL. MySQL is continuosly monitoring 3 key things:
- Where you are accessing from (your host)
- Who you are (username and password)
- What you are allowed to do (your privileges)
When MySQL is installed a database called mysql is automatically created and it has several tables defined within it. This database is used to record all privileges that are set on all users, fields, columns, tables and databases.
The 2 Step Authentication ProcessAs previously mention MySQL checks 3 things during its authentication process, These are performed in 2 steps:
- Step 1 -MySQL looks at the host from where you are connecting from and the username and password pair. If the host is allowed to make the connection, your password is correct for the given username and finally the username is assigned as being able to access from the said host MySQl goes onto Step 2. If this step fails an error message is given.
- Step 2 - MySQL then checks that the command that you are trying to issue is infact allowed to be issued by you on the database, table or field.
It is a common practice for applications to utilise this type of authentication, it allows for different levels of users, basic users can select data to view it whilst others are given great access allowing them to insert and delete data. Working with User Privileges Depending on you set-up will determine what permissions you have.For example if you are connecting to your ISP hosting, the chances are you will only have a single user account and one database. Your users will however, have full control allowing you to create, delete, insert as you please on you won database. On the other hand if you are running a local server for say an internal intranet, then you mau have several users and several databases. Running your own server will almost certainly require you to add, amend and even delete users. So let us now look at how you can do just that!
How to Add UsersAdding users is very easy using DBManager Professional or a similar third-party application, however, its also very easy to do via a command-line using the GRANT command. You need to access the MySQL monitor using the root user. Once logged into the server you could use a command similar to the following to create a new user:GRANT privileges
ON databasename.tablename
TO username@host
IDENTIFIED BY 'password';Obviously the privileges given would depend on the users role, a complete list of these will follow this section. The example above uses both databasename and tablename, this would allow you to restrict the users privileges to that particular table, as opposed to allowing privileges across the entire databasename [replace .tablename with .*]. The username and host I think are like the password - self explanatory.However, an additional wildcard is available to you; using % rather than the host would give way to a very large host list.The other way is to use an INSERT command statement.
Removing PrivilegesAt times it is necessary to revoke certain users privileges, again this is easierly done via any of the previously mentions third-party applications. And again its just as easy to issue the command straight from a command-line. The syntax for this command is as follows:PEVOKE privileges
ON databasename.tablename
FROM username@hostname;As with before the privileges to be revoked would depend on the user, the databasename/tablename and hostname again can also be dealt with like those mention in the GRANT command above. Changes to the privileges table take immediate effect but as a safe measure and for the sake of a few extra keystrokes it's advised to issue a FLUSH PRIVILEGES; command statement.To completely remove a user from the database for example, when an employee leaves the company, you would need to issue a special DELETE statement to remove the said user from the user table in the mysql database. So what's this special DELETE statement that I have to issue to delete a user completely I hear you say! The statement below will do just that for you:DELETE FROM mysql.userThat should delete the user specific [replace username with the relevant username] who is connected via localhost from the main mysql.user table.
WHERE User='username'
AND Host != 'localhost';
Well once again we come to the end of this weeks study from the course book. Please feel free to post any comments. I will attempt to answer any questions to the best of my ability.
All the best Jayson