- get to bin directory of mysql using command prompt
- log in to mysql
- run source command with file parameter
cd C:\mysql\bin mysql -u root -p mysql> source c:\myfile.sql
cd C:\mysql\bin mysql -u root -p mysql> source c:\myfile.sql
To test a remote MySQL connection in Linux terminal, simply replace
hostname_or_ip with your own.
mysql -u username -h hostname_or_ip -p
Or via Command Line in Windows:
telnet 18.104.22.168.22 3306
If you are getting an error when trying to connect to your remote MySQL server
ERROR 2003 (HY000): Can't connect to MySQL server on '22.214.171.124.22' (111)
or in Windows Telnet
Connecting To 126.96.36.199.22...Could not open connection to the host, on port 3306: Connect failed
You may need to comment out
bind-address in the MySQL config file
mysqld.cnf with nano editor.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
W and search for
# Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1
The above line is telling MySQL to only accept local connections. Comment out this line by adding
# before it so it looks like:
# Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. # bind-address = 127.0.0.1
Restart the MySQL service for changes to take effect.
sudo service mysql restart
Now try to connect to MySQL remotely again.
If you still can’t connect, check if there is a firewall configured on your server. The most common firewall for Ubuntu server is
ufw is enabled:
sudo ufw status
If it’s enabled, you should see some rules:
To Action From -- ------ ---- OpenSSH ALLOW Anywhere Apache Full ALLOW Anywhere OpenSSH (v6) ALLOW Anywhere (v6) Apache Full (v6) ALLOW Anywhere (v6)
If MySQL is not listed, add a rule for it.
sudo ufw allow mysql
There should be a rule for MySQL:
To Action From -- ------ ---- OpenSSH ALLOW Anywhere Apache Full ALLOW Anywhere 3306 ALLOW Anywhere OpenSSH (v6) ALLOW Anywhere (v6) Apache Full (v6) ALLOW Anywhere (v6) 3306 (v6) ALLOW Anywhere (v6)
sudo service ufw restart
In this tutorial we are going to build a simple PHP/MySQL faceted navigation from scratch. In case you don’t know what faceted navigation is (or faceted search, layered navigation), it’s the nice filtering mechanism you see on most e-commerce sites in some form or another next to the product listing (see image).
With each newly selected filter the resultset is narrowed down accordingly which allows user to quickly find the items (products, classifieds, articles, etc.) that meet their criteria. This search mechanism is most useful when a dataset needs to be filtered based onmultiple properties.
Unfortunately MySQL does not support faceted search out-of-the-box. There are dedicated external search applications such as SOLR or ElasticSearch which index the facets automatically for filtering. However not everyone has the knowledge or resources required to run such an application. Luckily we can achieve similar results while maintaining good performance using MySQL and PHP by setting up an index table and making some clever table joins.
You can download an SQL export of all the example data here.
Let me first introduce the dataset we’re going to work our magic on for this example:
table: products +----+-----------------------+----------+-----------------+--------+ | id | name | type | brand | colour | +----+-----------------------+----------+-----------------+--------+ | 1 | Black Jacket by Acme | jacket | Acme Apparel | black | | 2 | Blue Jacket by Acme | jacket | Acme Apparel | blue | | 3 | Blue Jacket by FF | jacket | Fictive Fashion | blue | | 4 | Orange Trousers by FF | trousers | Fictive Fashion | orange | +----+-----------------------+----------+-----------------+--------+
The faceted navigation in this example without any of the filters selected will look something like this:
The user can filter the product overview simply clicking on the filter links which makes it a form of navigation to the user (hence the term faceted navigation).
Note the item counts next to each facet value, these will be updated to reflect the current resultset whenever a filter is added or removed.
To perform the faceted search in MySQL we need to create two extra tables. One table contains a list of the product facets and the other contains facts about the products.
table: product_facets +----+--------+ | id | name | +----+--------+ | 1 | type | | 2 | brand | | 3 | colour | +----+--------+ table: product_facts +------------+----------+------------+-----------------+ | product_id | facet_id | facet_name | value | +------------+----------+------------+-----------------+ | 1 | 1 | type | jacket | | 1 | 2 | brand | Acme Apparel | | 1 | 3 | colour | black | | 2 | 1 | type | jacket | | 2 | 2 | brand | Acme Apparel | | 2 | 3 | colour | blue | | 3 | 1 | type | jacket | | 3 | 2 | brand | Fictive Fashion | | 3 | 3 | colour | blue | | 4 | 1 | type | trousers | | 4 | 2 | brand | Fictive Fashion | | 4 | 3 | colour | orange | +------------+----------+------------+-----------------+
We used the following PHP function to automatically populate the facts table with data. You’ll need to make sure the product_facts table gets updated for each CRUD operation on the original dataset.
Now that we have set up all the required tables we will look at how to query the tables in the next section.
Let’s say a user just opened the product browse page and hasn’t selected any filters yet. To retrieve the product data we can simply execute the query:
This will return a list of all products.
To construct the faceted navigation HTML we need to query the product_facts table.
Which produces the following result:
+------------+-----------------+---+ | facet_name | value | c | +------------+-----------------+---+ | type | jacket | 3 | | type | trousers | 1 | | brand | Acme Apparel | 2 | | brand | Fictive Fashion | 2 | | colour | black | 1 | | colour | blue | 2 | | colour | orange | 1 | +------------+-----------------+---+
As you can see this contains all the data required to output the faceted navigation (including counts).
Now let’s say that a user selects the colour blue so that only blue products are shown. This changes the product query to:
Similarly the query to retrieve the facet data becomes:
Which results in:
+------------+-----------------+---+ | facet_name | value | c | +------------+-----------------+---+ | type | jacket | 2 | | brand | Acme Apparel | 1 | | brand | Fictive Fashion | 1 | | colour | blue | 2 | +------------+-----------------+---+
We can add an ORDER BY statement to sort the list of filters alphabetically:
When the user selects multiple filters we simply add multiple conditions in the WHERE clause based on the selected filters.
In the next post I’ll explain how to create the interactive faceted navigation HTML interface based on the work we’ve done so far.
If you have any questions please leave a comment below!
We can Install Apache, PHP and MySQL in Ubuntu 3 types as bellow:
sudo apt-get install lamp-server^
In this tutorial I use the hostname server1.example.com with the IP address 192.168.0.100. These settings might differ for you, so you have to replace them where appropriate.
I’m running all the steps in this tutorial with root privileges, so make sure you’re logged in as root:
First we install MySQL 5 like this:
apt-get install mysql-server mysql-client
You will be asked to provide a password for the MySQL root user – this password is valid for the user root@localhost as well as email@example.com, so we don’t have to specify a MySQL root password manually later on:
New password for the MySQL “root” user: <– yourrootsqlpassword
Repeat password for the MySQL “root” user: <– yourrootsqlpassword
Apache2 is available as an Ubuntu package, therefore we can install it like this:
apt-get install apache2
Now direct your browser to http://192.168.0.100, and you should see the Apache2 placeholder page (It works!):
Apache’s default document root is /var/www on Ubuntu, and the configuration file is /etc/apache2/apache2.conf. Additional configurations are stored in subdirectories of the /etc/apache2 directory such as /etc/apache2/mods-enabled (for Apache modules), /etc/apache2/sites-enabled (for virtual hosts), and/etc/apache2/conf.d.
We can install PHP5 and the Apache PHP5 module as follows:
apt-get install php5 libapache2-mod-php5
We must restart Apache afterwards:
The document root of the default web site is /var/www. We will now create a small PHP file (info.php) in that directory and call it in a browser. The file will display lots of useful details about our PHP installation, such as the installed PHP version.
<?php phpinfo(); ?>
Now we call that file in a browser (e.g. http://192.168.0.100/info.php):
As you see, PHP5 is working, and it’s working through the Apache 2.0 Handler, as shown in the Server API line. If you scroll further down, you will see all modules that are already enabled in PHP5. MySQL is not listed there which means we don’t have MySQL support in PHP5 yet.
To get MySQL support in PHP, we can install the php5-mysql package. It’s a good idea to install some other PHP5 modules as well as you might need them for your applications. You can search for available PHP5 modules like this:
apt-cache search php5
Pick the ones you need and install them like this:
apt-get install php5-mysql php5-curl php5-gd php5-intl php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-ming php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl
Now restart Apache2:
Xcache is a free and open PHP opcode cacher for caching and optimizing PHP intermediate code. It’s similar to other PHP opcode cachers, such as eAccelerator and APC. It is strongly recommended to have one of these installed to speed up your PHP page.
Xcache can be installed as follows:
apt-get install php5-xcache
Now restart Apache:
Now reload http://192.168.0.100/info.php in your browser and scroll down to the modules section again. You should now find lots of new modules there, including the MySQL module:
phpMyAdmin is a web interface through which you can manage your MySQL databases. It’s a good idea to install it:
apt-get install phpmyadmin
You will see the following questions:
Web server to reconfigure automatically: <– apache2
Configure database for phpmyadmin with dbconfig-common? <– No
Afterwards, you can access phpMyAdmin under http://192.168.0.100/phpmyadmin/:
I instaled MAMP and it worked great. However, when I tried to access mysql from the shell I was getting
$ mysql -u root -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
However, using a full path to MAMP binary worked.
$ /Applications/MAMP/Library/bin/mysql -u root -p
MAMP’s mysql works with another socket file which is located at /Applications/MAMP/tmp/mysql/mysql.sock
All you have to do is:
sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock
UPDATE: As it turns out the above soft link gets erased by the system, so you need to run the command every once in a while. At one point it got too frustrating so I figured out a permanent way to take care of this problem. Open /etc/my.cnf and add/edit these lines:
Instead of telling MAMP which socket file to use, you are instructing the mysql client to use MAMP’s default socket.
Let me know if this works for you.
Referenced by: http://boriskuzmanovic.wordpress.com/2008/11/26/mamp-error-2002-hy000-cant-connect-to-local-mysql-server-through-socket-tmpmysqlsock-2/
You can recover MySQL database server password with following five easy steps.
Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.
Step # 3: Connect to mysql server as the root user.
Step # 4: Setup new mysql root account password.
Step # 5: Exit and restart the MySQL server.
Here are commands you need to type for each step (login as the root user):
# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
# mysqld_safe --skip-grant-tables &
 5988 Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe: started
# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe: ended + Done mysqld_safe --skip-grant-tables
# /etc/init.d/mysql start
# mysql -u root -p
MySQL queries are not case-sensitive by default. Following is a simple query that is looking for ‘value’. However it will return ‘VALUE’, ‘value’, VaLuE’, etc…
The good news is that if you need to make a case-sensitive query, it is very easy to do:
Message Error in MySQL:
“Data truncation: Incorrect datetime value: ‘1969-12-31 16:00:00’ for column ‘LAST_LOGIN’ at row 1”
Now, open the my.ini file located at c:program files/MySQL/MySQL
Add a # at the begining of the line: sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
sql_mode=” on the next line and save the file.
We have mock up data in both table as below:
And we want to select data in table1 that is not avaiable in table2, so when you want to do as I said.
You should use:
SELECT table1.* FORM table1 LEFT JOIN table2 ON table1.ID = table2.Table1ID WHERE table2.Table1ID is null