For security reasons, no one has the mysql FILE priv, which means you cannot "LOAD DATA INFILE". You can, however, use a "LOAD DATA LOCAL INFILE" statement as long as you have a mysql prompt on our system and have uploaded the data file to your account here first.
The "LOAD DATA LOCAL INFILE" statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.
To import a file this way, first upload your data file to your home directory on our system with FTP or SCP. Then get a shell prompt on our system, and then a MySQL Monitor prompt so that you can issue the SQL that will import your file.
For example, suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line. You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.
To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:
LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);
The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL test_table as columns field1, field2, and field3 respectively. Many of the above SQL clauses are optional and you should read the MySQL documentation on the proper use of this statement.
Tuesday, April 29, 2008
MS Access to MySQL
Char/VarChar Binary If a column marked BINARY is used in an expression, the whole expression is compared as a BINARY value, resulting in case-sensitive comparison You may use "LIKE BINARY" statement or stored procedure to implement case-sensitive comparison with Char/VarChar data without BINARY property.
Set Not supported in MS Access. An exception will be raised while copying table structures. You may alter the SQL datable to change the data type to Char type.
Enum Not supported in MS Access. An exception will be raised. You may alter the SQL table to change the data type to Char type.
IntegerType Unsigned Not supported in MS Access. These integer fields will be converted without the "unsigned" property, so it is better to have your application not to use unsigned data type, and handle "unsigned" constraints in the program. The only unsigned data type in MS Access is "Byte".
BigInt Not supported in MS Access. An BigInt like 9223372036854775807 will be converted to Double (8-byte) type and value will become 9.22337203685478E+18. If you really need BigInt data, you might not want to export / port the database to MS Access that does not support 8-byte integer.
TimeStamp After setting the default value of a DateTime field of MS Access as function "now()", the value in a row will be the time of inserting. However, when you modify the row later, the value will remain the same. There is no way for the JET Engine to support TimeStamp. So you may have to write codes to simulate TimeStamp effects.
Decimal The decimal type in Ms Access is not exactly equivalent with the decimal type in MySQL.
Set Not supported in MS Access. An exception will be raised while copying table structures. You may alter the SQL datable to change the data type to Char type.
Enum Not supported in MS Access. An exception will be raised. You may alter the SQL table to change the data type to Char type.
IntegerType Unsigned Not supported in MS Access. These integer fields will be converted without the "unsigned" property, so it is better to have your application not to use unsigned data type, and handle "unsigned" constraints in the program. The only unsigned data type in MS Access is "Byte".
BigInt Not supported in MS Access. An BigInt like 9223372036854775807 will be converted to Double (8-byte) type and value will become 9.22337203685478E+18. If you really need BigInt data, you might not want to export / port the database to MS Access that does not support 8-byte integer.
TimeStamp After setting the default value of a DateTime field of MS Access as function "now()", the value in a row will be the time of inserting. However, when you modify the row later, the value will remain the same. There is no way for the JET Engine to support TimeStamp. So you may have to write codes to simulate TimeStamp effects.
Decimal The decimal type in Ms Access is not exactly equivalent with the decimal type in MySQL.
Data type mapping from MySql to Access
MySQL Ms Access Size
Date : 3B
Time : 3B
DateTime : 8B
TimeStamp : 4B Date/Time 8B
Char : L255
VarChar : L255 Text /
Float : 4B N Single 4B
Double/Real:8B N Double 8B
TinyInt : 1B
Year : 1B N Byte 1B
SmallInt : 2B N Integer 2B
Integer/Int : 4B
MediumInt : 3B N Long Integer 4B
TinyText : L255
Text : L64KB
MediumText : L16MB
LongText : L4GB Memo /
TinyBlob : L255
Blob : L64KB
MediumBlob : L16MB
Longblob : L4GB OLE object /
Decimal/Numeric (M,D) N Decimal (M,D) 8B
All Integer
fields with
auto_increment
property Increment 4B
BigInt : 8B Text 20B
Date : 3B
Time : 3B
DateTime : 8B
TimeStamp : 4B Date/Time 8B
Char : L255
VarChar : L255 Text /
Float : 4B N Single 4B
Double/Real:8B N Double 8B
TinyInt : 1B
Year : 1B N Byte 1B
SmallInt : 2B N Integer 2B
Integer/Int : 4B
MediumInt : 3B N Long Integer 4B
TinyText : L255
Text : L64KB
MediumText : L16MB
LongText : L4GB Memo /
TinyBlob : L255
Blob : L64KB
MediumBlob : L16MB
Longblob : L4GB OLE object /
Decimal/Numeric (M,D) N Decimal (M,D) 8B
All Integer
fields with
auto_increment
property Increment 4B
BigInt : 8B Text 20B
Data type mapping from Ms Access to MySql
Ms Access (Max) Size MySQL
Boolean 1-bit TinyInt
N Byte 1-Byte TinyInt Unsigned
N Integer 2-Byte SmallInt
N Long Integer 4-Byte Integer
N Single 4-Byte Float
N Double 8-Byte Double
Currency 8-Byte Decimal (20, 4)
N Decimal 12-Byte Decimal (M, D)
Text 256 B VarChar (Size)
Date/Time 8-Byte DateTime
OLE 1 G MediumBlob
Memo 64 KB Text
Autoincrement 4-Byte Integer with autoincrement property
Boolean 1-bit TinyInt
N Byte 1-Byte TinyInt Unsigned
N Integer 2-Byte SmallInt
N Long Integer 4-Byte Integer
N Single 4-Byte Float
N Double 8-Byte Double
Currency 8-Byte Decimal (20, 4)
N Decimal 12-Byte Decimal (M, D)
Text 256 B VarChar (Size)
Date/Time 8-Byte DateTime
OLE 1 G MediumBlob
Memo 64 KB Text
Autoincrement 4-Byte Integer with autoincrement property
Friday, April 25, 2008
ApacheMySQLPHP
This is to help people setup and install a [WWW] LAMP (Linux-Apache-MySQL-PHP) server in Ubuntu, including Apache 2, PHP 5 (optionally PHP 4 in Ubuntu 6.10 and lower), and MySQL 4.1 or 5.0.
When installing from the Ubuntu 6.06 (Dapper Drake) "Server cd", you have the option of choosing to install a LAMP setup at the inital Ubuntu installation screen. That will install apache2, php5 and mysql 5.0.
Check Requirements
{i} As of Ubuntu 7.04 (Feisty), all PHP4 packages have been dropped from the repositories, as the PHP4 branch is not being further developed/supported by the PHP Group. Everything in this article pertaining to PHP4 can therefore only be executed in Ubuntu 6.10 and lower. If you absolutely depend on PHP4 and would like to use the current Ubuntu release, you have to compile it from source.
Some applications require php4 while others will work with php5. Be sure to install the version of php and the corresponding apache2 module for it. You cannot have both php4 and php5 modules running on the same instance of apache2 at the same time. Installing one may remove the other.
If you have both php4 and php5 installed, be aware of which version of the apache2 php module you have. If libapache2-mod-php5 is already installed, the php4 package will install libapache-mod-php4 and not libapache2-mod-php4 package.
If libapache2-mod-php5 is not installed, installing php4 will install the apache2 php module (libapache2-mod-php4). See this example.
Most web applications will use Apache2, php5 and mysql5.0. If no specific versions are mentioned in your web application's documentation, use those.
To install the default LAMP stack in Ubuntu 6.06 LTS (Dapper Drake)
If you did not use the LAMP installer option from the server cd but want to install those same packages without having to reinstall your operating system, use any method to install the following packages
apache2 php5-mysql libapache2-mod-php5 mysql-server
All of those packages are in the Ubuntu 6.06 LTS (Dapper Drake) main repository. Once LAMP is installed, you need to set a mysql root password and then, depending on your web application, create a database, user and password. That's it!
To install the default LAMP stack in Ubuntu 7.04 (Feisty Fawn)
In Feisty, the Ubuntu base system includes Tasksel. You can either install LAMP using tasksel or install the LAMP packages as detailed above.
sudo tasksel install lamp-server
See Tasksel
Starting over, How to remove the LAMP stack
To remove the LAMP stack remove the following packages:
apache2 apache2-mpm-prefork apache2-utils apache2.2-common libapache2-mod-php5 libapr1 libaprutil1 libdbd-mysql-perl libdbi-perl libmysqlclient15off libnet-daemon-perl libplrpc-perl libpq5 mysql-client-5.0 mysql-common mysql-server mysql-server-5.0 php5-common php5-mysql
To also remove the debconf data, use the purge option when removing. To get rid of any configurations you may have made to apache, manually remove the /etc/apache2 directory once the packages have been removed.
Installing Apache 2
To only install the apache2 webserver, use any method to install
apache2
Troubleshooting
If you get this error:
apache2: Could not determine the server's fully qualified domain name, using 127.0.0.1 for ServerName
then use a text editor such as "sudo nano" at the command line or "gksudo gedit" on the desktop to edit
/etc/apache2/httpd.conf
and add
ServerName localhost
at the end of the file.
Note: The Ubuntu/Debian setup uses config snippets in the /etc/apache2/conf.d directory rather than the httpd.conf file. Instead of using httpd.conf edit a new file /etc/apache2/conf.d/fqdn and add the line in there.
Virtual Hosts
Apache2 has the concept of sites, which are separate configuration files that Apache2 will read. These are available in /etc/apache2/sites-available. By default, there is one site available called default this is what you will see when you browse to [WWW] http://localhost or [WWW] http://127.0.0.1. You can have many different site configurations available, and activate only those that you need.
As an example, we want the default site to be /home/user/public_html/. To do this, we must create a new site and then enable it in Apache2.
To create a new site:
*
Copy the default website as a starting point. sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/mysite
*
Edit the new configuration file in a text editor "sudo nano" on the command line or "gksudo gedit", for example: gksudo gedit /etc/apache2/sites-available/mysite
*
Change the DocumentRoot to point to the new location. For example, /home/user/public_html/
*
Change the Directory directive, replace
*
You can also set separate logs for each site. To do this, change the ErrorLog and CustomLog directives. This is optional, but handy if you have many sites
*
Save the file
Now, we must deactivate the old site, and activate our new one. Ubuntu provides two small utilities that take care of this: a2ensite (apache2enable site) and a2dissite (apache2disable site).
sudo a2dissite default && sudo a2ensite mysite
Finally, we restart Apache2:
sudo /etc/init.d/apache2 restart
If you have not created /home/user/public_html/, you will receive an warning message
To test the new site, create a file in /home/user/public_html/:
echo 'Hello! It is working!' > /home/user/public_html/index.html
Finally, browse to [WWW] http://localhost/
Installing PHP 5
To only install PHP5. use any method to install
php5
If PHP4 is present on your system, also install
libapache2-mod-php5
Troubleshooting
Does your browser ask if you want to download the php file instead of displaying it? If Apache is not actually parsing the php after you restarted it, install libapache2-mod-php5. It is installed when you install the php5 package, but may have been removed inadvertently by packages which need to run a different version of php.
You may also need to actually enable it, by doing sudo a2enmod php5 followed by sudo /etc/init.d/apache2 restart. If sudo a2enmod php5 returns "$ This module does not exist!", you should purge (not just remove) the libapache2-mod-php5 package and reinstall it.
Be sure to clear your browser's cache before testing your site again.
Installing PHP 4
To install PHP4, Use any method to install
php4
If PHP5 is present on your system, installing php4 will install the php module for apache (version 1.3) and not apache2. To use php4 with apache2, install
libapache2-mod-php4
Troubleshooting
Does your browser ask if you want to download the php file instead of displaying it? If Apache is not actually parsing the php after you restarted it, install libapache2-mod-php4. It is installed when you install the php4 package, but may have been removed inadvertently by packages which need to run a different version of php.
You may also need to actually enable it, by doing sudo a2enmod php4 followed by sudo /etc/init.d/apache2 restart. If sudo a2enmod php4 returns "$ This module does not exist!", you should purge (not just remove) the libapache2-mod-php5 package and reinstall it.
Be sure to clear your browser's cache before testing your site again.
Installing MYSQL with PHP 5
Use any method to install
mysql-server libapache2-mod-auth-mysql php5-mysql
Installing MYSQL with PHP 4
First enable the universe repository since the packages are not in main
*
I am using Ubuntu server (command line)
*
I am using a desktop
Use any method to install
mysql-server libapache2-mod-auth-mysql php4-mysql
After installing PHP
You may need to increase the memory limit that PHP imposes on a script. Edit the /etc/php5/apache2/php.ini file and increase the memory_limit value.
After installing MySQL
Set mysql bind address
Before you can access the database from other computers in your network, you have to change its bind address. Note that this can be a security problem, because your database can be accessed by others computers than your own. Skip this step if the applications which require mysql are running on the same machine.
type:
nano /etc/mysql/my.cnf
and change the line:
bind-address = localhost
to your own internal ip address e.g. 192.168.1.20
bind-address = 192.168.1.20
If your ip address is dynamic you can also comment out the bind-address line and it will default to your current ip.
If you try to connect without changing the bind-address you will recieve a "Can not connect to mysql error 10061".
Set mysql root password
Before accessing the database by console you need to type:
mysql -u root
At the mysql console type:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');
A successful mysql command will show:
Query OK, 0 rows affected (0.00 sec)
Mysql commands can span several lines. Do not forget to end your mysql command with a semicolon.
Note: If you have already set a password for the mysql root, you will need to use:
mysql -u root -p
(Did you forget the mysql-root password? See MysqlPasswordReset.)
Create a mysql database
mysql> CREATE DATABASE database1;
Create a mysql user
For creating a new user with all privileges (use only for troubleshooting), at mysql prompt type:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
For creating a new user with fewer privileges (should work for most web applications) which can only use the database named "database1", at mysql prompt type:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON database1.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword';
yourusername and yourpassword can be anything you like. database1 is the name of the database the user gets access to. localhost is the location which gets access to your database. You can change it to '%' (or to hostnames or ip addresses) to allow connections from every location (or only from specific locations) to the database. Note, that this can be a security problem and should only be used for testing purposes!
To exit the mysql prompt type:
mysql> \q
Since the mysql root password is now set, if you need to use mysql again (as the mysql root), you will need to use:
mysql -u root -p
and then enter the password at the prompt.
Backup-Settings
Please, let's say something in which directories MySql stores the database information and how to configure a backup
Alternatively
There is more than just one way to set the mysql root password and create a database. For example mysqladmin can be used:
mysqladmin -u root -p password yourpassword
and
mysqladmin -u root -p create database1
mysqladmin is a command-line tool provided by the default LAMP install.
Phpmyadmin and mysql-admin
All mysql tasks including setting the root password and creating databases can be done via a graphical interface using phpmyadmin or mysql-admin.
To install one or both of them, first enable the universe repository
*
I am using Ubuntu server (command line)
*
I am using a desktop
Use any method to install
phpmyadmin
Troubleshooting
If you get blowfish_secret error: Choose and set a phrase for cryptography in the file /etc/phpmyadmin/blowfish_secret.inc.php and copy the line (not the php tags) into the file /etc/phpmyadmin/config.inc.php or you will receive an error.
Alternative: install phpMyAdmin from source
See the [WWW] phpMyAdmin page for instructions on how to install phpmyadmin from source:
Mysql-admin
Mysql-admin runs locally, on the desktop. Use any method to install
mysql-admin
For more information
You really ought to read [WWW] 2.9.3. Securing the Initial MySQL Accounts from the [WWW] MySQL Reference Manual.
Edit Apache Configuration
You may want your current user to be the PHP pages administrator. To do so, edit the Apache configuration file :
$ gksudo "gedit /etc/apache2/apache2.conf"
Search both the strings starting by "User" and "Group", and change the names by the current username and groupname you are using. Then you'll need to restart Apache. (look at the next chapter concerning apache commands)
Configuration options relating specifically to user websites (accessed through localhost/~username) are in /etc/apache2/mods-enabled/userdir.conf.
Edit PHP Configuration to Work With MYSQL (Ubuntu Dapper)
In Dapper Drake, "extension=mysql.so" and "extension=mysqli.so" are enabled in the php.ini file out-of-the-box. However, sometimes php is not looking for those files in the right directory. You have then to move your files or modify the php.ini configuration.:
First solution
locate the directory where the extension files are placed:
locate mysql.so
(change mysql.so in mysqli.so if you want to install the mysqli functions)
-then modify the php.ini file to indicate the right place for the extension directory:
$ gksudo "gedit /etc/php4/apache2/php.ini"
or if you are using php5
$ gksudo "gedit /etc/php5/apache2/php.ini"
Look for the 'extension_dir' property, and set it to the directory where you found the mysql(i).so file:
*
extension_dir= "/usr/lib/php5/20051025/"
Restart apache, and test if your mysql(i) functions are working.
Second solution
-locate the directory where the extension files are placed:
locate mysql.so
(change mysql.so in mysqli.so if you want to install the mysqli functions)
Let's say that you found the file in '/usr/lib/php5/20051025/'
-then check in the php.ini file for the extension directory
$ gksudo "gedit /etc/php4/apache2/php.ini"
or if you are using php5
$ gksudo "gedit /etc/php5/apache2/php.ini"
Look for the 'extension_dir' property. It should be by default '/usr/lib/php5/ext'. If it's not, change it for this value.
-Now create the default directory for extensions:
$ sudo mkdir /usr/lib/php5/ext
-Copy the extension file to the new directory:
$ sudo cp /usr/lib/php5/20051025/mysql.so /usr/lib/php5/ext/mysql.so
Change the first path to the one you found with the locate function, and change mysql.so into mysqli.so if you want to use mysqli functions.
-Restart apache (see below), and test if your mysql(i) functions are working.
Run, Stop, Test, And Restart Apache
Use the following command to run Apache :
$ sudo /usr/sbin/apache2ctl start
To stop it, use :
$ sudo /usr/sbin/apache2ctl stop
To test configuration changes, use :
$ sudo /usr/sbin/apache2ctl configtest
Finally, to restart it, run :
$ sudo /usr/sbin/apache2ctl restart
Using Apache
You can access apache by typing 127.0.0.1 or [WWW] http://localhost (by default it will be listening on port 80) in your browser address bar. By default the directory for apache server pages is /var/www . It needs root access in order to put files in. A way to do it is just starting the file browser as root in a terminal:
$ sudo nautilus
or
if you want to make /var/www your own. (Use only for non-production web servers - this is not the most secure way to do things.)
$ sudo chown -R $USER:$USER /var/www
Status
To check the status of your PHP installation:
$ gksudo "gedit /var/www/testphp.php"
and insert the following line
View this page on a web browser at [WWW] http://yourserveripaddress/testphp.php or [WWW] http://localhost/testphp.php
Securing Apache
If you just want to run your Apache install as a development server and want to prevent it from listening for incoming connection attempts, this is easy to do.
$ gksudo "gedit /etc/apache2/ports.conf"
$ password:
Change ports.conf so that it contains:
Listen 127.0.0.1:80
Save this file, and restart Apache (see above). Now Apache will serve only to your home domain, [WWW] http://127.0.0.1 or [WWW] http://localhost.
Password-Protect a Directory
There are 2 ways to password-protect a specific directory. The recommended way involves editing /etc/apache2/apache2.conf . (To do this, you need root access). The other way involves editing a .htaccess file in the directory to be protected. (To do this, you need access to that directory).
Password-Protect a Directory With .htaccess
See EnablingUseOfApacheHtaccessFiles
Warning: On at least some versions of Ubuntu, .htaccess files will not work by default. See EnablingUseOfApacheHtaccessFiles for help on enabling them.
thumbnails
If you direct your web browser to a directory (rather than a specific file), and there is no "index.html" file in that directory, Apache will generate an index file on-the-fly listing all the files and folders in that directory. Each folder has a little icon of a folder next to it.
To put a thumbnail of that specific image (rather than the generic "image icon") next to each image file (.jpg, .png, etc.):
... todo: add instructions on how to do thumbnails here, perhaps using [WWW] Apache::AutoIndex 0.08 or [WWW] Apache::Album 0.95 ...
Known problems
Skype incompatibility
Skype uses port 80 for incoming calls, and thus, may block Apache. The solution is to change the port in one of the applications. Usually, port 81 is free and works fine. To change the port number in Skype go to menu Tools > Options, then click on the Advanced tab, then in the box of the port for incoming calls write your preference.
Other Apache Options
*
ServerSideIncludes - enable SSI in Apache2
*
LocalhostSubdomain - access your local files as if you had different subdomains
Further Information
*
StrongPasswords is recommended reading!
*
BastilleLinux is also recommended if you're going to be running a live webserver.
*
You can compile PHP5FromSource, as well as MYSQL5FromSource.
*
PHPOracle will enable you to connect to Oracle databases.
*
PhpPear : PHP Extension and Application Repository
When installing from the Ubuntu 6.06 (Dapper Drake) "Server cd", you have the option of choosing to install a LAMP setup at the inital Ubuntu installation screen. That will install apache2, php5 and mysql 5.0.
Check Requirements
{i} As of Ubuntu 7.04 (Feisty), all PHP4 packages have been dropped from the repositories, as the PHP4 branch is not being further developed/supported by the PHP Group. Everything in this article pertaining to PHP4 can therefore only be executed in Ubuntu 6.10 and lower. If you absolutely depend on PHP4 and would like to use the current Ubuntu release, you have to compile it from source.
Some applications require php4 while others will work with php5. Be sure to install the version of php and the corresponding apache2 module for it. You cannot have both php4 and php5 modules running on the same instance of apache2 at the same time. Installing one may remove the other.
If you have both php4 and php5 installed, be aware of which version of the apache2 php module you have. If libapache2-mod-php5 is already installed, the php4 package will install libapache-mod-php4 and not libapache2-mod-php4 package.
If libapache2-mod-php5 is not installed, installing php4 will install the apache2 php module (libapache2-mod-php4). See this example.
Most web applications will use Apache2, php5 and mysql5.0. If no specific versions are mentioned in your web application's documentation, use those.
To install the default LAMP stack in Ubuntu 6.06 LTS (Dapper Drake)
If you did not use the LAMP installer option from the server cd but want to install those same packages without having to reinstall your operating system, use any method to install the following packages
apache2 php5-mysql libapache2-mod-php5 mysql-server
All of those packages are in the Ubuntu 6.06 LTS (Dapper Drake) main repository. Once LAMP is installed, you need to set a mysql root password and then, depending on your web application, create a database, user and password. That's it!
To install the default LAMP stack in Ubuntu 7.04 (Feisty Fawn)
In Feisty, the Ubuntu base system includes Tasksel. You can either install LAMP using tasksel or install the LAMP packages as detailed above.
sudo tasksel install lamp-server
See Tasksel
Starting over, How to remove the LAMP stack
To remove the LAMP stack remove the following packages:
apache2 apache2-mpm-prefork apache2-utils apache2.2-common libapache2-mod-php5 libapr1 libaprutil1 libdbd-mysql-perl libdbi-perl libmysqlclient15off libnet-daemon-perl libplrpc-perl libpq5 mysql-client-5.0 mysql-common mysql-server mysql-server-5.0 php5-common php5-mysql
To also remove the debconf data, use the purge option when removing. To get rid of any configurations you may have made to apache, manually remove the /etc/apache2 directory once the packages have been removed.
Installing Apache 2
To only install the apache2 webserver, use any method to install
apache2
Troubleshooting
If you get this error:
apache2: Could not determine the server's fully qualified domain name, using 127.0.0.1 for ServerName
then use a text editor such as "sudo nano" at the command line or "gksudo gedit" on the desktop to edit
/etc/apache2/httpd.conf
and add
ServerName localhost
at the end of the file.
Note: The Ubuntu/Debian setup uses config snippets in the /etc/apache2/conf.d directory rather than the httpd.conf file. Instead of using httpd.conf edit a new file /etc/apache2/conf.d/fqdn and add the line in there.
Virtual Hosts
Apache2 has the concept of sites, which are separate configuration files that Apache2 will read. These are available in /etc/apache2/sites-available. By default, there is one site available called default this is what you will see when you browse to [WWW] http://localhost or [WWW] http://127.0.0.1. You can have many different site configurations available, and activate only those that you need.
As an example, we want the default site to be /home/user/public_html/. To do this, we must create a new site and then enable it in Apache2.
To create a new site:
*
Copy the default website as a starting point. sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/mysite
*
Edit the new configuration file in a text editor "sudo nano" on the command line or "gksudo gedit", for example: gksudo gedit /etc/apache2/sites-available/mysite
*
Change the DocumentRoot to point to the new location. For example, /home/user/public_html/
*
Change the Directory directive, replace
*
You can also set separate logs for each site. To do this, change the ErrorLog and CustomLog directives. This is optional, but handy if you have many sites
*
Save the file
Now, we must deactivate the old site, and activate our new one. Ubuntu provides two small utilities that take care of this: a2ensite (apache2enable site) and a2dissite (apache2disable site).
sudo a2dissite default && sudo a2ensite mysite
Finally, we restart Apache2:
sudo /etc/init.d/apache2 restart
If you have not created /home/user/public_html/, you will receive an warning message
To test the new site, create a file in /home/user/public_html/:
echo 'Hello! It is working!' > /home/user/public_html/index.html
Finally, browse to [WWW] http://localhost/
Installing PHP 5
To only install PHP5. use any method to install
php5
If PHP4 is present on your system, also install
libapache2-mod-php5
Troubleshooting
Does your browser ask if you want to download the php file instead of displaying it? If Apache is not actually parsing the php after you restarted it, install libapache2-mod-php5. It is installed when you install the php5 package, but may have been removed inadvertently by packages which need to run a different version of php.
You may also need to actually enable it, by doing sudo a2enmod php5 followed by sudo /etc/init.d/apache2 restart. If sudo a2enmod php5 returns "$ This module does not exist!", you should purge (not just remove) the libapache2-mod-php5 package and reinstall it.
Be sure to clear your browser's cache before testing your site again.
Installing PHP 4
To install PHP4, Use any method to install
php4
If PHP5 is present on your system, installing php4 will install the php module for apache (version 1.3) and not apache2. To use php4 with apache2, install
libapache2-mod-php4
Troubleshooting
Does your browser ask if you want to download the php file instead of displaying it? If Apache is not actually parsing the php after you restarted it, install libapache2-mod-php4. It is installed when you install the php4 package, but may have been removed inadvertently by packages which need to run a different version of php.
You may also need to actually enable it, by doing sudo a2enmod php4 followed by sudo /etc/init.d/apache2 restart. If sudo a2enmod php4 returns "$ This module does not exist!", you should purge (not just remove) the libapache2-mod-php5 package and reinstall it.
Be sure to clear your browser's cache before testing your site again.
Installing MYSQL with PHP 5
Use any method to install
mysql-server libapache2-mod-auth-mysql php5-mysql
Installing MYSQL with PHP 4
First enable the universe repository since the packages are not in main
*
I am using Ubuntu server (command line)
*
I am using a desktop
Use any method to install
mysql-server libapache2-mod-auth-mysql php4-mysql
After installing PHP
You may need to increase the memory limit that PHP imposes on a script. Edit the /etc/php5/apache2/php.ini file and increase the memory_limit value.
After installing MySQL
Set mysql bind address
Before you can access the database from other computers in your network, you have to change its bind address. Note that this can be a security problem, because your database can be accessed by others computers than your own. Skip this step if the applications which require mysql are running on the same machine.
type:
nano /etc/mysql/my.cnf
and change the line:
bind-address = localhost
to your own internal ip address e.g. 192.168.1.20
bind-address = 192.168.1.20
If your ip address is dynamic you can also comment out the bind-address line and it will default to your current ip.
If you try to connect without changing the bind-address you will recieve a "Can not connect to mysql error 10061".
Set mysql root password
Before accessing the database by console you need to type:
mysql -u root
At the mysql console type:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');
A successful mysql command will show:
Query OK, 0 rows affected (0.00 sec)
Mysql commands can span several lines. Do not forget to end your mysql command with a semicolon.
Note: If you have already set a password for the mysql root, you will need to use:
mysql -u root -p
(Did you forget the mysql-root password? See MysqlPasswordReset.)
Create a mysql database
mysql> CREATE DATABASE database1;
Create a mysql user
For creating a new user with all privileges (use only for troubleshooting), at mysql prompt type:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
For creating a new user with fewer privileges (should work for most web applications) which can only use the database named "database1", at mysql prompt type:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON database1.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword';
yourusername and yourpassword can be anything you like. database1 is the name of the database the user gets access to. localhost is the location which gets access to your database. You can change it to '%' (or to hostnames or ip addresses) to allow connections from every location (or only from specific locations) to the database. Note, that this can be a security problem and should only be used for testing purposes!
To exit the mysql prompt type:
mysql> \q
Since the mysql root password is now set, if you need to use mysql again (as the mysql root), you will need to use:
mysql -u root -p
and then enter the password at the prompt.
Backup-Settings
Please, let's say something in which directories MySql stores the database information and how to configure a backup
Alternatively
There is more than just one way to set the mysql root password and create a database. For example mysqladmin can be used:
mysqladmin -u root -p password yourpassword
and
mysqladmin -u root -p create database1
mysqladmin is a command-line tool provided by the default LAMP install.
Phpmyadmin and mysql-admin
All mysql tasks including setting the root password and creating databases can be done via a graphical interface using phpmyadmin or mysql-admin.
To install one or both of them, first enable the universe repository
*
I am using Ubuntu server (command line)
*
I am using a desktop
Use any method to install
phpmyadmin
Troubleshooting
If you get blowfish_secret error: Choose and set a phrase for cryptography in the file /etc/phpmyadmin/blowfish_secret.inc.php and copy the line (not the php tags) into the file /etc/phpmyadmin/config.inc.php or you will receive an error.
Alternative: install phpMyAdmin from source
See the [WWW] phpMyAdmin page for instructions on how to install phpmyadmin from source:
Mysql-admin
Mysql-admin runs locally, on the desktop. Use any method to install
mysql-admin
For more information
You really ought to read [WWW] 2.9.3. Securing the Initial MySQL Accounts from the [WWW] MySQL Reference Manual.
Edit Apache Configuration
You may want your current user to be the PHP pages administrator. To do so, edit the Apache configuration file :
$ gksudo "gedit /etc/apache2/apache2.conf"
Search both the strings starting by "User" and "Group", and change the names by the current username and groupname you are using. Then you'll need to restart Apache. (look at the next chapter concerning apache commands)
Configuration options relating specifically to user websites (accessed through localhost/~username) are in /etc/apache2/mods-enabled/userdir.conf.
Edit PHP Configuration to Work With MYSQL (Ubuntu Dapper)
In Dapper Drake, "extension=mysql.so" and "extension=mysqli.so" are enabled in the php.ini file out-of-the-box. However, sometimes php is not looking for those files in the right directory. You have then to move your files or modify the php.ini configuration.:
First solution
locate the directory where the extension files are placed:
locate mysql.so
(change mysql.so in mysqli.so if you want to install the mysqli functions)
-then modify the php.ini file to indicate the right place for the extension directory:
$ gksudo "gedit /etc/php4/apache2/php.ini"
or if you are using php5
$ gksudo "gedit /etc/php5/apache2/php.ini"
Look for the 'extension_dir' property, and set it to the directory where you found the mysql(i).so file:
*
extension_dir= "/usr/lib/php5/20051025/"
Restart apache, and test if your mysql(i) functions are working.
Second solution
-locate the directory where the extension files are placed:
locate mysql.so
(change mysql.so in mysqli.so if you want to install the mysqli functions)
Let's say that you found the file in '/usr/lib/php5/20051025/'
-then check in the php.ini file for the extension directory
$ gksudo "gedit /etc/php4/apache2/php.ini"
or if you are using php5
$ gksudo "gedit /etc/php5/apache2/php.ini"
Look for the 'extension_dir' property. It should be by default '/usr/lib/php5/ext'. If it's not, change it for this value.
-Now create the default directory for extensions:
$ sudo mkdir /usr/lib/php5/ext
-Copy the extension file to the new directory:
$ sudo cp /usr/lib/php5/20051025/mysql.so /usr/lib/php5/ext/mysql.so
Change the first path to the one you found with the locate function, and change mysql.so into mysqli.so if you want to use mysqli functions.
-Restart apache (see below), and test if your mysql(i) functions are working.
Run, Stop, Test, And Restart Apache
Use the following command to run Apache :
$ sudo /usr/sbin/apache2ctl start
To stop it, use :
$ sudo /usr/sbin/apache2ctl stop
To test configuration changes, use :
$ sudo /usr/sbin/apache2ctl configtest
Finally, to restart it, run :
$ sudo /usr/sbin/apache2ctl restart
Using Apache
You can access apache by typing 127.0.0.1 or [WWW] http://localhost (by default it will be listening on port 80) in your browser address bar. By default the directory for apache server pages is /var/www . It needs root access in order to put files in. A way to do it is just starting the file browser as root in a terminal:
$ sudo nautilus
or
if you want to make /var/www your own. (Use only for non-production web servers - this is not the most secure way to do things.)
$ sudo chown -R $USER:$USER /var/www
Status
To check the status of your PHP installation:
$ gksudo "gedit /var/www/testphp.php"
and insert the following line
View this page on a web browser at [WWW] http://yourserveripaddress/testphp.php or [WWW] http://localhost/testphp.php
Securing Apache
If you just want to run your Apache install as a development server and want to prevent it from listening for incoming connection attempts, this is easy to do.
$ gksudo "gedit /etc/apache2/ports.conf"
$ password:
Change ports.conf so that it contains:
Listen 127.0.0.1:80
Save this file, and restart Apache (see above). Now Apache will serve only to your home domain, [WWW] http://127.0.0.1 or [WWW] http://localhost.
Password-Protect a Directory
There are 2 ways to password-protect a specific directory. The recommended way involves editing /etc/apache2/apache2.conf . (To do this, you need root access). The other way involves editing a .htaccess file in the directory to be protected. (To do this, you need access to that directory).
Password-Protect a Directory With .htaccess
See EnablingUseOfApacheHtaccessFiles
Warning: On at least some versions of Ubuntu, .htaccess files will not work by default. See EnablingUseOfApacheHtaccessFiles for help on enabling them.
thumbnails
If you direct your web browser to a directory (rather than a specific file), and there is no "index.html" file in that directory, Apache will generate an index file on-the-fly listing all the files and folders in that directory. Each folder has a little icon of a folder next to it.
To put a thumbnail of that specific image (rather than the generic "image icon") next to each image file (.jpg, .png, etc.):
... todo: add instructions on how to do thumbnails here, perhaps using [WWW] Apache::AutoIndex 0.08 or [WWW] Apache::Album 0.95 ...
Known problems
Skype incompatibility
Skype uses port 80 for incoming calls, and thus, may block Apache. The solution is to change the port in one of the applications. Usually, port 81 is free and works fine. To change the port number in Skype go to menu Tools > Options, then click on the Advanced tab, then in the box of the port for incoming calls write your preference.
Other Apache Options
*
ServerSideIncludes - enable SSI in Apache2
*
LocalhostSubdomain - access your local files as if you had different subdomains
Further Information
*
StrongPasswords is recommended reading!
*
BastilleLinux is also recommended if you're going to be running a live webserver.
*
You can compile PHP5FromSource, as well as MYSQL5FromSource.
*
PHPOracle will enable you to connect to Oracle databases.
*
PhpPear : PHP Extension and Application Repository
Tuesday, April 22, 2008
Ubuntu (Gutsy): Change Default Keyring Password
I changed my user password in Ubuntu, everything was OK until the next time I booted my Linux box. Keyring Manager was asking me for a password (as my wireless connection needed stored WPA password), immediately figured out that this was caused by the password change.
The problem is that Gnome Keyring manager doesn’t have an option to change the default keyring password. So, if your user password is changed, every time you log in Keyring Manager will ask for the password you supplied during Ubuntu’s installation (awkward).
Due the lack of password change in Keyring Manager we need another application: Seahorse. To install type the following:
$ sudo apt-get install seahorse
Once installed open it (Applications -> Accessories -> Passwords and Encryption Keys), and then go to Edit -> Preferences menu. Select GNOME Keyring tab and change the password to match your actual Linux user passwor
The problem is that Gnome Keyring manager doesn’t have an option to change the default keyring password. So, if your user password is changed, every time you log in Keyring Manager will ask for the password you supplied during Ubuntu’s installation (awkward).
Due the lack of password change in Keyring Manager we need another application: Seahorse. To install type the following:
$ sudo apt-get install seahorse
Once installed open it (Applications -> Accessories -> Passwords and Encryption Keys), and then go to Edit -> Preferences menu. Select GNOME Keyring tab and change the password to match your actual Linux user passwor
How to change the root password in Ubuntu
As default Ubuntu has no password set for the root user. To gain root access you have to type in your own user password. This is the password you set for the first user while installing Ubuntu.
To manually set a password for the root user, type in the following in the shell:
sudo passwd
After that you are asked to type in the new root password twice. Finally, your root user has its own password.
To manually set a password for the root user, type in the following in the shell:
sudo passwd
After that you are asked to type in the new root password twice. Finally, your root user has its own password.
Wednesday, April 9, 2008
How to install wine on Ubuntu
Wine for Ubuntu, Debian, and Debian-based distributions Debian Linux
Debian and Debian-based distributions, such as Ubuntu, utilize a special tool for managing packages known as APT. APT is able to automagically install all of the needed dependencies for a software package, as well as keep the package up to date, by scanning what are known as APT repositories. Debian-based distributions have their own repositories of software, many of which include Wine, however we keep our own repository of the latest available packages here for download.
There used to be graphical instructions here, however we have found that the terminal commands are actually simpler to describe and quicker for the user to input. Because the commands below use sudo, you may have to enter your user password after hitting enter.
Adding the WineHQ APT Repository:
First, open a terminal window. Then add the repository's key to your system's list of trusted APT keys by copy and pasting the following:
wget -q http://wine.budgetdedicated.com/apt/387EE263.gpg -O- | sudo apt-key add -
Next, add the repository to your system's list of APT sources:
For Ubuntu Gutsy (7.10):
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/gutsy.list -O /etc/apt/sources.list.d/winehq.list
For Ubuntu Feisty (7.04):
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/feisty.list -O /etc/apt/sources.list.d/winehq.list
For Ubuntu Edgy (6.10): *64-bit packages not available*
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/edgy.list -O /etc/apt/sources.list.d/winehq.list
For Ubuntu Dapper (6.06): *64-bit packages not available*
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/dapper.list -O /etc/apt/sources.list.d/winehq.list
For Debian Etch (4.0):
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/etch.list -O /etc/apt/sources.list.d/winehq.list
Then, you can install Wine from WineHQ like it were any other package, such as by using the Synaptic Package Manager under System->Administration. Alternatively, you can install from the terminal by running 'sudo apt-get update' to update APT's package information and then 'sudo apt-get install wine'.
Upgrading to a new version of Ubuntu
If you are upgrading the entire system, such as going from Ubuntu 7.04 to 7.10, you will need to come back to this page and enter the command for the new version above. The built in update manager will not switch the Wine repository automatically.
64-bit Users:
If you are using a very old version of Ubuntu where 64 bit packages are not available and would still like a working Wine package, there is a relatively easy hack that can be used to install the 32-bit package and have it function normally. See this page on the Wine wiki for more details.
Older .deb packages
Since the APT repository can only hold the latest packages, older versions of the packages are available at the WineHQ .deb packages archive.
You can install the packages by double-clicking on them.
Very fast and reliable webhosting for the APT repository is graciously provided by budgetdedicated.com.
Debian and Debian-based distributions, such as Ubuntu, utilize a special tool for managing packages known as APT. APT is able to automagically install all of the needed dependencies for a software package, as well as keep the package up to date, by scanning what are known as APT repositories. Debian-based distributions have their own repositories of software, many of which include Wine, however we keep our own repository of the latest available packages here for download.
There used to be graphical instructions here, however we have found that the terminal commands are actually simpler to describe and quicker for the user to input. Because the commands below use sudo, you may have to enter your user password after hitting enter.
Adding the WineHQ APT Repository:
First, open a terminal window. Then add the repository's key to your system's list of trusted APT keys by copy and pasting the following:
wget -q http://wine.budgetdedicated.com/apt/387EE263.gpg -O- | sudo apt-key add -
Next, add the repository to your system's list of APT sources:
For Ubuntu Gutsy (7.10):
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/gutsy.list -O /etc/apt/sources.list.d/winehq.list
For Ubuntu Feisty (7.04):
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/feisty.list -O /etc/apt/sources.list.d/winehq.list
For Ubuntu Edgy (6.10): *64-bit packages not available*
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/edgy.list -O /etc/apt/sources.list.d/winehq.list
For Ubuntu Dapper (6.06): *64-bit packages not available*
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/dapper.list -O /etc/apt/sources.list.d/winehq.list
For Debian Etch (4.0):
sudo wget http://wine.budgetdedicated.com/apt/sources.list.d/etch.list -O /etc/apt/sources.list.d/winehq.list
Then, you can install Wine from WineHQ like it were any other package, such as by using the Synaptic Package Manager under System->Administration. Alternatively, you can install from the terminal by running 'sudo apt-get update' to update APT's package information and then 'sudo apt-get install wine'.
Upgrading to a new version of Ubuntu
If you are upgrading the entire system, such as going from Ubuntu 7.04 to 7.10, you will need to come back to this page and enter the command for the new version above. The built in update manager will not switch the Wine repository automatically.
64-bit Users:
If you are using a very old version of Ubuntu where 64 bit packages are not available and would still like a working Wine package, there is a relatively easy hack that can be used to install the 32-bit package and have it function normally. See this page on the Wine wiki for more details.
Older .deb packages
Since the APT repository can only hold the latest packages, older versions of the packages are available at the WineHQ .deb packages archive.
You can install the packages by double-clicking on them.
Very fast and reliable webhosting for the APT repository is graciously provided by budgetdedicated.com.
Subscribe to:
Posts (Atom)
