Common SQL commands and syntax

Select Statement

SELECT 'column_name' FROM 'table_name'

Distinct

SELECT DISTINCT 'column_name' 

FROM 'table_name'

Where

SELECT 'column_name' 

FROM 'table_name'

WHERE 'condition'

In

SELECT 'column_name'

FROM 'table_name'

WHERE 'condition'

Between

SELECT 'column_name'

FROM 'table_name'

WHERE 'column_name' BETWEEN 'value1'

AND 'value2'

Like

SELECT 'column_name'

FROM 'table_name'

WHERE 'column_name' LIKE {PATTERN}

Order By

SELECT 'column_name'

FROM 'table_name'

[WHERE 'condition']

ORDER BY 'column_name' [ASC, DESC]

Count

SELECT COUNT ('column_name')

FROM 'table_name'

Group By

SELECT 'column_name1',

SUM('column_name2')

FROM 'table_name'

GROUP BY 'column_name1'

Having

SELECT 'column_name1',

SUM('column_name2')

FROM 'table_name'

GROUP BY 'column_name1'

HAVING (arithmetic function condition)

Create Table Statement

CREATE TABLE 'table_name'

('column1' 'data_type_for_column_1',

'column2' 'data_type_for_column_2', ...)

Drop Table Statement

DROP TABLE 'table_name'

Truncate Table Statement

TRUNCATE TABLE 'table_name'

Insert Into Statement

INSERT INTO 'table_name' ('column1', 

'column2', ...)

VALUES('value1', 'value2', ...)

Insert Into Select Statement

INSERT INTO 'table1' ('column1','column2',...)

SELECT 'column3', 'column4', ...

FROM 'table2'

Update Statement

UPDATE 'table_name'

SET 'column1' = [new value]

WHERE {condition}

Delete From Statement

DELETE FROM 'table_name'

WHERE {condition}

How to setup Wordpress on Windows with XAMPP server

Hi, you might need a website, a blog, or a web app. You heard Wordpress but did not know how to start, this tutorial is perfect for you. First, you need to have a practice Wordpress site on your computer so that anytime-anywhere with your computer with you, you can try to play around with Wordpress.     

So today, I would like to make a tutorial for beginners on how to setup a Wordpress CMS on your local Windows machine.

If you do not have any web server installed on your machine, or in other case you like a windows development environment. You might need something like XAMPP or Cross-Platform (X), Apache (A), MariaDB (M), PHP (P) and Perl (P). Click here if you like to setup your XAMPP first. Of course there are other web server like MAMP, LAMP, and WAMP but for this tutorial I prefered XAMPP.

If you already installed and configured XAMPP you might see this page once you open http://localhost on your browser.

For beginners, XAMPP is an ideal local development environment, and its not meant for production deployments. As stated at XAMPP local dashboard page. However, if you need the real deal then XAMPP suggest to go here https://www.apachefriends.org/hosting.html

Let say you already setup your XAMPP.

1.) Open your web browser (in my case I have Google Chrome) then navigate to https://wordpress.org/download/

2.) Click Download Wordpress 4.9.1 (by the time of this post)

3.) Locate htdocs. Usually it is located at C:\xampp\htdocs
4.) Check wordpress filename. Initially it is a compressed zip file
5.) Click save button

6.) Once you have finished downloading a 9.9 MB wordpress-4.9.1.zip file, click the small down arrow button
7.) Then, click Show in folder menu (this will pop up the XAMPP htdocs window)

8.) Right click the wordpress-4.9.1 file then click Extract Here (using winrar)

9.) Then you will see Winrar extracting
10.) the wordpress folder

11.) Open your web browser and navigate to http://localhost/wordpress

12.) Select the language of your choice

13.) Then, click Continue button

14.) Click Let's go! button

Then you should see the next page which is related to database connection details. Skip this for a while because we need to make sure that the wordpress installation do not halt with errors due to the absence of the database that are needed by wordpress.

15.) So wee need add wordpress database first. Open another browser or another tab of the Google chrome browser and navigate to http://localhost/phpmyadmin

16.) Login the root account

17.) Then click Go button

18.) Now you should logged-in to the phpMyAdmin and click New button

19.) Type the name of the database that you need to add. This time type wordpress

20.) Then, click Create button

21.) Now go back to the previous page(wordpress installation page) wherein you are about to fill up some database connection details. Fill up all the information needed to create the connection of wordpress to its database. For this instance please follow below:

• Database Name : wordpress
• Username: root
• Password: [the password you have setup for root]
• Database Host: localhost (leave as is)
• Table Prefix : wp_ (leave as is)

22.) Click Submit button

23.) Now click Run the installation button.

24.) On my local XAMPP server, it just take almost 5 seconds to show up with this page. You just need to fill up the information needed for the final setup of wordpress.

• Site Title : [your choice of website title] 
• Username : [your initial wordpress admin user]
• Password : [your initial wordpress admin password]
• Your Email : [provide your initial wordpreass admin email address]
• Search Engine Visibility : [just leave uncheck if you to discourage search engine to index your site, anyway this is just your development environment setup, so either way should OK]

25.) Finally, click Install Wordpress button, and wait for wordpress to finish the installation process

26.) After waiting for almost 2 minutes, wordpress installation successfully finished. Then click on the Log In button to proceed.

27.) Then type your Login credentials

28.) And click the Log In button

29.) Congratulations! you are now Logged in to the Wordpress administration page

30.) To logout, hover the mouse on the upper right corner of wordpress administration page

31.) Then click Log Out menu

33.) Then you will be redirected to http://localhost/wordpress/wp-login.php?loggedout=true, or you may go to http://localhost/wordpress/wp-admin to Login again

32.) To see your wordpress site open a Google chrome browser and navigate to http://localhost/wordpress

The wordpress front page has a one page design by default. Changes you made in the administration page will be affected the front page.

On my future articles:

• How to create customized themes on Wordpress
• How to create plugins

References:

wordpress.org

MariaDB replication setup on CENTOS Linux

It is nice to think that somehow we have fault-tolerance in our database even we only have two servers running on our network. It lessens our worries that in any point of time we do not know when the server crashes and leave us problems in restoring data from the backup. So we need duplicates of our data or something that copies our data from one server to another as a real-time backup which also increases performance and improves reliability and availability. It requires sharing information between resources but it differs from clustering.

So here's what we called Replication, that we will be going to apply on a CENTOS machine. Basically, we only need at least two servers, the Master, and a Slave.

But in the real world application, replication can have one Master and multiple Slaves.

Some of the benefits of replication are the following:

• Scale-Out Solutions
• Spread load among multiple slaves
• All write and updates must occur on master
• Reads can occur on any slave
• Data Security
• Backups can be performed on slaves without affecting master (Ability to save data)
• Analytics
• Data analysis can be performed on any slave without affecting speed of master or other slaves
• Long-Distance Data Distribution
• Allows branch offices to have dedicated, offline server

But for this blog post, I will only configure a Master and a Slave setup.

On the Master server

1.) Connect to the server's ssh using putty

2.) Then vi/nano to /etc/my.cnf
#nano /etc/my.cnf

3.) Append the text below under [mysqld]:
#Binary Logging (For Replication)
log-basename=master
log-bin
binlog-format=row

4.) Then restart mariadb service
#system restart mariadb

5.) Then open MySQL Workbench, and connect to the Master server. And create a replication user then make sure your password you will set must not be longer than 32 characters in length and also not contains a "#" sign same for the user. Follow the sequence below:

6.) Then lock all tables to prevent changes

• Issue the following SQL query on the Master server using MySQL Workbench
• FLUSH TABLES WITH READ LOCK;
• Won't let you update tables
• Does not clear tables, but clears cache
• Lock remains in effect until session is closed
• View the current coordinates to the binary log on the master server
• SHOW MASTER STATUS

7.) Then set global server_id by issuing SQL query again:

set global server_id = 1;

8.) You may check and see the server_id:

show variables where variable_name = 'server_id';

On the Slave server

9.) Then connect to the SQL of your Slave server using MySQL Workbench

set global server_id = 2;

10.) Check server_id of Slave

show variables where variable_name = "server_id";

11.) Make sure slave is stopped first by issuing SQL query to the Slave server

slave stop;

11.) Then issue the following SQL query to configure slave:

change master to

master_host = '[IP address of the master] ',

master_user = '[Replication user]',

master_password = '[The password of the replication user]',

master_log_file = '[ can be seen at /var/lib/mysql (eg. mariadb-bin.000001) and by using "SHOW MASTER STATUS" at Master server SQL query]',

master_log_pos = [Log Position shown at Master server using "SHOW MASTER STATUS"(eg. 2221)],

master_port = 3306,

master_connect_retry = 10;

12.) Then start slave by issuing SQL query to the Slave server

slave start;

13.) Then check the slave status. You may found errors if there's any.

show slave status;

14.) Append to /etc/my.cnf of the Slave server
[mysqld]
server_id=2

Troubleshooting

• Be sure that the firewall on the master allows communication
• #firewall-cmd --list-ports
• TCP Port 3306
• Ping the master from the slave
• Telnet from the slave to the master on port 3306
• Verify binary logging has different server-id
• Verify user account has remote privileges and is configured for replication in "Global Privileges"

14.) There you go, you may see if replication is working, update a record on your Master server and check if the same database record on the Slave server copies the exact data.

Referrences:

mariadb.org

https://linoxide.com/how-tos/configure-mariadb-replication-centos-linux/

https://www.unixmen.com/setup-mariadb-master-slave-replication-in-centos-7/

https://www.youtube.com/channel/UCjCs0BMeJUOLs8j79BP-4Uw

Install Samba (Windows Share) on CENTOS 7

It is important for web developers to access the web files on a development server. However, if dev team requires to access it via windows share over CENTOS machine, then we need to install Samba.

Here's my quick list of installing Samba on CENTOS.

1.) Check for existing samba package if any using the following commands
#rpm -qa | grep samba
#yum list installed | grep samba

2.) If samba is installed, remove it using the below command
#yum remove samba*

3.) For SMB new User. Create a user and add to a group
#useradd -s /sbin/nologin [username]
#groupadd [smbgroupname]
Note: To know more about managing user and group just click here

4.) Now, install samba using the following command
#yum install samba* -y

5.) Assign the user username to  [smbgroupname], and set samba password to that user
#usermod -a -G [smbgroupname] [username]
#smbpasswd -a [username]

6.) Create a new share called “/samba/secure_share” and set the permissions to that share
#mkdir /samba/secure_share
#chmod -R 0755 /samba/secure_share
#chown -R [username]:[smbgroupname] /samba/secure_share

7.) Edit samba config file;
#vi /etc/samba/smb.conf

8.) Add the below lines at the bottom of samba config file.
[secure_share]
path = /samba/secure_share
writable = yes
browsable = yes
guest ok = no
valid users = @[smbgroupname]

9.) Test the samba configuration for any errors.
#testparm

10.) To start Samba service on system boot
#systemctl start smb
#systemctl start nmb
#systemctl enable smb
#systemctl enable nmb

11.) Firewall configuration. Allow Samba server default ports through firewall.
#firewall-cmd --permanent --zone=public --add-port=137/tcp
#firewall-cmd --permanent --zone=public --add-port=138/tcp
#firewall-cmd --permanent --zone=public --add-port=139/tcp
#firewall-cmd --permanent --zone=public --add-port=445/tcp
#firewall-cmd --permanent --zone=public --add-port=901/tcp

#firewall-cmd --permanent --add-port=137/tcp
#firewall-cmd --permanent --add-port=138/tcp
#firewall-cmd --permanent --add-port=139/tcp
#firewall-cmd --permanent --add-port=445/tcp
#firewall-cmd --permanent --add-port=901/tcp

#firewall-cmd --list-ports

12.) Restart firewall to apply the changes.
#firewall-cmd --reload

13.) SELinux Configuration ( Skip this part if you already disabled SELinux)

Turn the samba_enable_home_dirs Boolean on if you want to share home directories via Samba.
setsebool -P samba_enable_home_dirs on

Label  the /samba/secure_share/ with samba_share_t so that SELinux allows Samba to read and write to it.

#chcon -t samba_share_t /samba/secure_share/
#systemctl restart smb
#systemctl restart nmb

14.) To disable SELinux, edit file /etc/sysconfig/selinux,
#vi /etc/sysconfig/selinux

15.) Set SELinux value to disabled.
#SELINUX=disabled

16.) Finally, browse the shared folder you have configured.

References:
https://www.unixmen.com/install-configure-samba-server-centos-7/

Moodle: Network setup using dynamic or multiple IP in wwwroot

If you have multiple NIC to be used for local and public access. Sometimes you are facing this kind of problem in Moodle. All you have to do is change a PHP variable in config.php inside moodle web directory.

$CFG->wwwroot

value with :

$CFG->wwwroot = 'http://'.$_SERVER["HTTP_HOST"];

CENTOS Linux Filesystem Layout

Path	Description
/	Root Directory. Also contains all the key directories for the system.
/root	Root Home Area. Where all root's personal configuration and files are located.
/etc	Contains all important file configuration and settings for server applications.
/proc	A virtual representation of the status of the machine. It is a virtual filesystem used by the kernel to communicate with userland tools.Usually details of running system in the CPU can be seen using (/proc/cpuinfo), while the memory usage in (/proc/meminfo) etc.
/var	Short for variable. Which web contents (/var/www/html) and system log files (/var/log/) resides. Usually takes disk space quickly. That's why it is highly suggested to put this in a separate disk and also using Logical Volume Management(LVM) disk partition so that it will allows dynamic creation and resizing.
/boot	Contains the boot loader which are files that are needed to boot CENTOS. Includes configuration and kernel.Usually takes small partition size on the beginning of the disk.
/bin and /sbin	/bin Contains most of the user programs, while /sbin contains administration tools and privileged binaries.
/dev	Location for all devices files on the Linux system.
/home	Contains files and folders of users.
/lib	System libraries
/lost+found	Can be found in the root of ext2 or ext3 filesystem. Files and folders that are damaged, detached or missing from the system are restored here during disk checking (fsck) so that it can be recovered.
/media	Located on newer Linux systems which holds the mount point for removable storage such as USB drive.
/mnt	Located on older Linux systems
/usr	Contains the majority of user software. /usr/bin for regular user while /usr/sbin for root.
/opt	Usually used by large third party applications.
/srv	Contains files that are used by services. Most of the services use other location such as /var instead.
/sys	Contains information about the system.
/tmp	Contains temporary files and folder. It can be emptied once the system has been rebooted. It can be a perfect location if you have a temporary file to utilize. Take note that it is not safe to leave important files here.

To learn more just go to: https://en.wikipedia.org/wiki/Linux_Standard_Base

VLSM - Variable Length Subnet Mask

VLSM 
Variable length subnet mask is a technique that allows a network administrator to divide an IP address space into subnets of different sizes.


Subnetting
Allows you to create multiple logical networks that exist within a single class network.


Reasons why we subnet

• It helps the preservation of address space in order not to waste address
• It used for security
• It helps to control network traffic due to collision of packets transmitted by other nodes on the same segment

Legend

Δ	= Bit value
Sm	= Subnet Mask
UIP	= Usable IP
NA	= Notable Address

   
       32
       - n   

where n = bits

VLSM Table 

Sample rsync command

rsync -r -a -v -e"ssh -l username" /root/Documents username@[ipaddress]:/home/username

rsync -rav --progress /root/Documents username@[ipaddress]:/home/username

rsync -avz /root/Documents username@[ipaddress]:/home/username

CENTOS Linux User Group Management

Note: I frequently update this post whenever there are changes in my setup.

Show users
# cat /etc/passwd

Show groups
# cat /etc/group

Add user from group 
# usermod -a -G [groupname] [username]
or
# gpasswd -a [username] [groupname]

Delete user from group
# gpasswd -d [username] [groupname]

Change Directory owner
# chgrp [groupname] [foldername]


How to change Owner/Group permissions

# chown [username]:[groupname]

should change the ownership and group to what you want.

# chown -R [username]:[groupname] [directory/file]

should change the ownership of the directory, and the entire subtree under it.


Here is a list of what the shorthand represents:

Identities
u — the user who owns the file (that is, the owner)

g — the group to which the user belongs

o — others (not the owner or the owner's group)

a — everyone or all (u, g, and o)

Permissions
r — read access

w — write access

x — execute access

Actions
+ — adds the permission

- — removes the permission

= — makes it the only permission

Note that to create (or delete) a file in a directory, the user or group must have write permission to the directory. And to list a directory, the user or group must have 'x' permission to the directory.

# chmod ug+rwx [directory/file]

# chmod -R ug+rw [directory/file]

might do what you want.

But read the man pages previously recommended to make sure. And be especially careful with -R. Also, never use a '.*' wildcard for this sort of thing. It will do something very bad that you were not expecting.

References:

https://www.centos.org/docs/4/html/rhel-sbs-en-4/s1-navigating-ownership.html

man chown
man chgrp
man chmod

https://tecadmin.net/tutorial/linux/linux-modify-user/

A quick list of CENTOS 7 basic web server installation (LAMP) for web developers

Hi, today I would like to share you a quick list of installing CENTOS 7 Linux for web development purposes which composed with Apache + PHP5.6 + MariaDB by the time of this writing.

I expected that you have already inserted the CD/DVD installer to your machine, formatted and partitioned. If not yet then please click this link first before you proceed to this tutorial.

Note: I frequently update this post whenever there are changes in my setup.

1.) yum update
2.) reboot
3.) rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY*
4.) yum -y install epel-release
5.) yum update
6.) yum -y install mariadb-server mariadb
7.) systemctl start mariadb.service
8.) systemctl enable mariadb.service
9.) mysql_secure_installation

10.)Enter current password for root (enter for none): <--ENTER

Set root password? [Y/n]
New password: <--yourmariadbpassword
Re-enter new password: <--yourmariadbpassword
Password updated successfully!
Reloading privilege tables..
 ... Success!

Remove anonymous users? [Y/n] <--ENTER
 ... Success!

Disallow root login remotely? [Y/n] <--ENTER
 ... Success!

Remove test database and access to it? [Y/n] <--ENTER
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reload privilege tables now? [Y/n] <--ENTER
 ... Success!



11.) yum -y install httpd
12.) systemctl start httpd.service
13.) systemctl enable httpd.service

14.) Add port to pass through the firewall
#firewall-cmd --permanent --zone=public --add-service=http
#firewall-cmd --permanent --zone=public --add-port=5000/tcp
#firewall-cmd --permanent --zone=public --add-service=https

15.) Reload the firewall service for the changes to take effect
#firewall-cmd --reload
16.) Then check if the ports are already open/listening
#firewall-cmd --list-ports

17.) yum install wget

18.) http://www.techoism.com/how-to-upgrade-php-version-5-4-to-5-6-on-centosrhel/
 php -v
 rpm -Uvh https://mirror.webtatic.com/yum/el7/epel-release.rpm
 rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm

19.) yum install -y php56w php56w-opcache php56w-xml php56w-mcrypt php56w-gd php56w-devel php56w-mysql php56w-intl php56w-mbstring php56w-soap php56w-xmlrpc
20.) systemctl restart httpd.service
21.) php -v
22.) nano /var/www/html/info.php
 <?php
phpinfo();
?>
https://webtatic.com/packages/php56/

23.) 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 like curl etc there.:
24.) yum install phpMyAdmin
25.) nano /etc/httpd/conf.d/phpMyAdmin.conf
Now we configure phpMyAdmin. We change the Apache configuration so that phpMyAdmin allows connections not just from localhost (by commenting out the <Directory "/usr/share/phpmyadmin"> stanza):
[...]
Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin

#<Directory /usr/share/phpMyAdmin/>
#   <IfModule mod_authz_core.c>
#     # Apache 2.4
#     <RequireAny>
#       Require ip 127.0.0.1
#       Require ip ::1
#     </RequireAny>
#   </IfModule>
#   <IfModule !mod_authz_core.c>
#     # Apache 2.2
#     Order Deny,Allow
#     Deny from All
#     Allow from 127.0.0.1
#     Allow from ::1
#   </IfModule>
#</Directory>


<Directory /usr/share/phpMyAdmin/>
        Options none
        AllowOverride Limit
        Require all granted
</Directory>

[...]

26.) nano /etc/phpMyAdmin/config.inc.php
[...]
$cfg['Servers'][$i]['auth_type']     = 'http';    // Authentication method (config, http or cookie based)?
[...]

27.) systemctl restart  httpd.service
Afterwards, you can access phpMyAdmin under http://192.168.0.100/phpmyadmin/:


28.) Enabling Mod_Rewrite for WebApp
https://devops.profitbricks.com/tutorials/install-and-configure-mod_rewrite-for-apache-on-centos-7/
nano /etc/httpd/conf/httpd.conf
<Directory "/var/www/html">
Change "AllowOverride Off" to "AllowOverride All"

MySQL Login
mysql -u root -p
//Using password NO error
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

PHP MEMORY
https://premium.wpmudev.org/blog/increase-memory-limit/?rad=b&utm_expid=3606929-86.PHoDXYJeQYmWFQIujGOFZg.1&utm_referrer=https%3A%2F%2Fwww.google.com%2F
nano /etc/php.ini
display_errors = On/Off
upload_max_filesize = 1000M
post_max_size = 2000M
memory_limit = 3000M
file_uploads = On
max_execution_time = 180

29.) firewall-cmd --permanent --zone=public --add-service=mysql
30.) firewall-cmd --reload
31.) yum install zip
yum install unzip

unzip pics.zip  -d /tmp
http://www.cyberciti.biz/tips/how-can-i-zipping-and-unzipping-files-under-linux.html


References:
Apache: http://httpd.apache.org/
PHP: http://www.php.net/
MySQL: http://www.mysql.com/
CentOS: http://www.centos.org/
phpMyAdmin: http://www.phpmyadmin.net/
https://www.howtoforge.com/apache_php_mysql_on_centos_7_lamp