Posts Tagged ‘preferrably’

Redirect www to non www with .htaccess Apache rewrite rule

Thursday, July 2nd, 2015

https://pc-freak.net/images/redirect_domain_name_without_changing_url_apache_rewrite_rule_preventing_host_in_ip_mod_rewrite_
Sometimes it happens that some websites are indexed in Search Engines (Google, Yandex, Yahoo, Bing, Ask Jeeves etc.) with www.website-name.com and you want to get rid of the www in the hostname in favour of just the hostname in terms of Apache .htaccess redirect. I knwo redirect www to non-www, might seem a bit weird as usually people want to redirect their website domain without www to point to www but there is a good reason for that weirdness, if you're a Christian and you dislike the fact that WWW is being red as Waw Waw Waw's or Vav / Vav Vav letters in Hebrew which represents in hebrew 666 or the mark of the beast prophecised in last book of Holy Bible (Revelation) written by saint John, the book is also called often Apocalypse.

Using Apache mod_rewrite's .htaccess is a good way to do the redirect especially if you're in a shared hosting, where you don't have direct access to edit Apache Virtualhost httpd.conf file but have only access to your user's home public_html directory via lets say FTP or SFTP.

To achieve the www to non-www domain URL redirect, just edit .htaccess with available hosting editor (in case if shell SSH access is available) or web interface or download the .htaccess via FTP / SFTP modify it and upload it back to server.

You need to include following mod_rewrite RewriteCond rules to .htaccess (preferrably somewhere near beginning of file):
 

RewriteEngine On
RewriteCond %{HTTP_HOST} ^www.Your-Website.org [NC]
RewriteRule ^(.*)$ http://Your-Website.org/$1 [L,R=301]


As .htaccess is being dynamically red by Apache's mod_rewrite module no Apache webserver restart is required and you should see immediately the affect, hopefully if the webhosting doesn't imply some caching with mod_cache or there is no some cache expiry setting preventing the new .htaccess to be properly redable by webserver.
Also in case of troubles make sure the new uploaded .htaccess file is properly readable e.g. has some permissions such as 755. Also in case if it doesn't immediately works out, make sure to clean up your browser cache and assure your browser is not configured to use some caching proxy host (be it visible or transparent).
Besides this would work and your Search Engines in future will hopefully stop indexing your site with WWW. in front of domain name, there is a downside of using .htaccess instead of including it straight into Apache's VirtualHost configuration is that this will cause a bit of degraded performance and add some milliseconds slowness to serve requests to your domain, thus if you're on your own dedicated server and have access to Apache configuration implement the www to non www hostname redirect directly using VirtualHost as explained in my prior article here

How to list and exclude table names from a database in MySQL (exclude table names from an show tables in MySQL) by using information_schema

Wednesday, March 30th, 2011

Listing all table names from a MySQL database is a very easy and trivial task that every sql or system administrator out there is aware of.

However excluding certain table names from a whole list of tables belonging to a database is not that commonly used and therefore I believe many people have no clue how to do it when they have to.

Today for one of my sql backup scripts it was necessary that certain tables from a database to be excluded from the whole list of tables for a database I’m backupping.
My example database has the sample name exampledatabase and usually I do list all the table contents from that database with the well known command:

mysql> SHOW tables from exampledatabase;

However as my desire was to exclude certain tables from the list (preferrably with a certain SQL query) I had to ask around in irc.freenode.net for some hints on a ways to achieve my exclude table goals.

I was adviced by some people in #mysql that what I need to achieve my goal is the information_schema mysql structure, which is available since MySQL version 5.0.

After a bit of look around in the information_schema and the respective documentation on mysql.com, thanksfully I could comprehend the idea behind the information_schema, though to be honest the first time I saw the documentation it was completly foggy on how to use this information_schema;
It seems using the information_schema is very easy and is not much different from your normal queries syntax used to do trivial operations in the mysql server.

If you wonder just like I did what is mysql’s information_schema go and use the information_schema database (which I believe is a virtual database that is stored in the system memory).

For instance:

mysql> use information_schema;
Database changed
mysql> show tables
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
17 rows in set (0.00 sec)

To get a general view on what each of the tables in the information_schema database contains I used the normal SELECT command for example

mysql> select * from TABLES limit 10;

I used the limit clause in order to prevent being overfilled with data, where I could still see the table fields name to get general and few lines of the table to get an idea what kind of information the TABLES table contains.

If you haven’t got any ecperience with using the information_schema I would advice you do follow my example select and look around through all the listed tables in the information_schema database

That will also give you a few hints about the exact way the MySQL works and comprehends it’s contained data structures.

In short information_schema virtual database and it’s existing tables provides a very thorough information and if you’re an SQL admin you certainly want to look over it every now and then.

A bit of playing with it lead me to a command which is actually a good substitute for the normal SHOW TABLES; mysql command.
To achieve a SHOW TABLES from exampledatabase via the information_schema info structure you can for example issue:

select TABLE_NAME from TABLES where TABLE_SCHEMA='exampledatabase';

Now as I’ve said a few words about information_schema let me go back to the main topic of this small article, which is How to exclude table names from a SHOW tables list

Here is how exclude a number of tables from a complete list of tables belonging to a database:

select TABLE_NAME from TABLES where TABLE_SCHEMA='exampledatabase'
AND TABLE_NAME not in
('mysql_table1_to_exlude_from_list', 'mysql_table2_to_exclude_from_list', 'table3_to_exclude');

In this example the above mysql command will list all the tables content belonging to exampledatabase and instruct the MySQL server not to list the table names with names mysql_table1_to_exlude_from_list, mysql_table2_to_exclude_from_list, table3_to_exclude

If you need to exclude more tables from your mysql table listing just add some more tables after the …’table3_to_exclude’, ‘new_table4_to_exclude’,’etc..’);

Of course this example can easily be adopted to a MySQL backup script which requires the exclusion of certain tables from a backed up database.

An example on how you can use the above table exclude command straight from the bash shell would be:

debian:~# echo "use information_schema; select TABLE_NAME from TABLES where
TABLE_SCHEMA='exampledatabase' AND TABLE_NAME not in
('mysql_table1_to_exlude_from_list', 'mysql_table2_to_exclude_from_list', 'table3_to_exclude',);"
| mysql -u root -p

Now this little bash one-liner can easily be customized to a backup script to create backups of a certain databases with a certain tables (e.g. with excluded number of tables) from the backup.

It’s seriously a pity that by default the mysqldump command does not have an option for a certain tables exclude while making a database dump.
I’ve saw the mysqldump exclude option, being suggested somewhere online as a future feature of mysqldump, I’ve also seen it being reported in the mysql.com’s bug database, I truly hope in the upcoming releases we will see the exclude option to appear as a possible mysqldump argument.