Vyacheslav

How to pull data from one column of a mysql table

To see data from one column, it is enough to execute the SQL query (where “abcd” is the name of the column in the table):

SELECT abcd FROM table;

To export to a file, just run the command in Linux:

mysql -u root -e "SELECT abcd FROM database;" -s -N > file.txt

Here is an example of exporting email addresses from a mysql table to an http page using PHP.
The thought immediately came to this plan (create a php file and open it through the browser):

<?php
// Connecting to mysql server
mysql_connect("localhost", "USER", "PASSWORD") or die (mysql_error ());
// Choosing a database
mysql_select_db("users") or die(mysql_error());
// SQL query
$rows = "SELECT * FROM account";
// Run this SQL query
$d = mysql_query($rows);
// Each row becomes an array ($row) using the mysql_fetch_array
while($row = mysql_fetch_array($d)) {
// Display the values of the email column
echo $row['email'] . "<br />";
}
// Close the connection to the database
mysql_close();
?>

Dnstop – monitoring of requests to the DNS server

Dnstop allows you to make a list of hosts that most send requests to the DNS server, so you can detect viruses on the network and understand who is attacking.

Configuring IGMP snooping on Cisco

I will give an example of setting up IGMP snooping on Cisco, there was a Cisco WS-C2960-24TT-L switch at hand and also some Cisco Nexus.

Back Up Cisco Catalyst 6500 Configuration

For the test, I sketched a Cisco Catalyst 6509-E automatic backup configuration script.

Actually the script itself:

#!/bin/bash
# Backup CISCO config
(
sleep 5
echo "user"
sleep 4
echo "password"
sleep 4
echo "copy running-config tftp:"
sleep 2
echo "192.168.1.4"
sleep 2
echo "cisco.cfg"
sleep 6

echo "exit"
) | telnet 192.168.1.5
mv /srv/tftp/cisco.cfg /backups/devices/cisco/`date +%Y-%m-%d`_cisco.cfg

find /backups/devices/cisco/ -type f -mtime +30 -exec rm {} \;

Add the contents of the script, for example, to the backup_cisco.sh file and add it to cron, adding the following line to the /etc/crontab file:

0 2 * * * root /backups/scripts/backup_cisco.sh > /dev/null 2>&1

The file can be opened for example in the text editor nano (Ctrl+X to exit, y/n to save or cancel changes):

sudo nano /etc/crontab

The script connects via telnet to 192.168.1.5 and copies the configuration to the tftp server 192.168.1.4, then the file is moved to a convenient directory for storage.
The last line in the script deletes files older than 30 days.
How to start the tftp server, see my articles: Installing and Configuring a TFTP Server in Ubuntu or Starting a TFTP server in Windows.
See also: Using and configuring CRON.

Configuring LoopBack Detection on D-Link Switches

For example, I will use the D-Link DES-3200 switch, on other models the setting for LoopBack Detection is essentially the same.

Activation of the LoopBack Detection (LBD) function on the switch:

enable loopdetect

Enabling LoopBack Detection on ports 1 through 16:

config loopdetect ports 1-16 state enable

Setting recover_timer (time in seconds for which the port will be disabled if a loop is detected), interval (interval between sending loop detection packets), mode (port-based — port is blocked or vlan-based — vlan traffic is blocked in which a loop is detected):

config loopdetect recover_timer 300 interval 10 mode port-based

Let us configure that it is written to the log that a loop was detected and the traps were not sent:

config loopdetect log state enable
config loopdetect trap none

In the web interface, you can configure this feature in the “L2 Features” – “Loopback Detection Settings”.

Configuring the switch D-Link DES-3200

For example, I will take the D-Link DES-3200-18 C1 switch with firmware 4.36.B012. Commands are similar for switches with a different number of ports, they can differ slightly only with different firmware versions and revisions.

Create an administrator account:

create account admin NAME
config admin local_enable

Storing the administrator password in encrypted form:

enable password encryption

Enabling password recovery:

enable password_recovery

Serial port parameters:

config serial_port baud_rate 115200 auto_logout never

Enable access via the web interface:

enable web 80

Disable switch management over SSH:

disable ssh

Enable telnet access:

enable telnet 23

Setting the terminal window width and displaying information in page mode:

config terminal width 80
enable clipaging

Setting the number of displayed lines of the terminal:

config terminal_line default

Disabling logging of input commands:

disable command logging

Delete default VLAN:

config vlan default delete 1-18
config vlan default advertisement enable

Creating a separate VLAN to manage the switch (17 – uplink):

create vlan core tag 50
config vlan core add tagged 17 advertisement disable

Creating a VLAN for users:

create vlan local_smart tag 51
config vlan local_smart add tagged 17
config vlan local_smart add untagged 1-16,18 advertisement disable

Disabling the encapsulation of VLAN tags in L2 VLAN tags:

disable qinq

Disabling auto VLAN configuration and assigning all ports to a PVID client VLAN:

disable gvrp
config port_vlan 1-18 gvrp_state disable ingress_checking enable acceptable_frame admit_all pvid 51

Enable automatic assignment of PVID ports (enabled by default):

enable pvid auto_assign

Assigning an IP address to a switch in a VLAN to manage:

config ipif System ipaddress 192.168.1.100/24 vlan core
config ipif System dhcp_option12 state disable
disable autoconfig 
config autoconfig timeout 50

Add default gateway:

create iproute default 192.168.1.1 1 primary

Enable restriction of broadcast traffic for all ports except uplink:

config traffic control 1-16,18 broadcast enable multicast disable unicast disable action drop threshold 100 countdown 0 time_interval 5
config traffic control auto_recover_time 0
config traffic trap none
config traffic control log state enable

Just in case, disable port mirroring:

disable mirror

Setting logs:

config log_save_timing on_demand
disable syslog
config system_severity trap information
config system_severity log information

Resolution of large jumbo frame packets and an example of port configuration:

enable jumbo_frame
config ports 1-16 speed auto flow_control disable learning enable state enable mdix auto
config ports 17 medium_type copper speed auto flow_control disable learning enable state enable mdix auto
config ports 17 medium_type fiber speed auto flow_control disable learning enable state enable
config ports 18 speed auto flow_control disable learning enable state enable

Let us manage the switch only from the specified IP addresses:

create trusted_host network 192.168.1.1/24 snmp telnet ssh http https ping
create trusted_host network 172.16.100.100/32 snmp telnet ssh http https ping

Configure snmp traps:

disable snmp traps
disable snmp authenticate_traps 
disable snmp linkchange_traps
config snmp linkchange_traps ports 1-18 disable
config snmp coldstart_traps enable
config snmp warmstart_traps enable
config rmon trap rising_alarm enable
config rmon trap falling_alarm enable

Enable and sample SNMP settings (where TEXT specify the desired password):

enable snmp
config snmp system_contact admin@ixnfo.com
delete snmp community public
delete snmp community private
delete snmp user initial
delete snmp group initial
create snmp group public v1 read_view CommunityView notify_view CommunityView 
create snmp group public v2c read_view CommunityView notify_view CommunityView 
create snmp community public view CommunityView read_only
create snmp group TEXT v2c read_view CommunityView write_view CommunityView notify_view CommunityView 
create snmp community TEXT view CommunityView read_write
disable community_encryption

Disable IGMP MULTICAST VLAN:

disable igmp_snooping multicast_vlan
config igmp_snooping multicast_vlan forward_unmatched disable

Setting and disabling PORT SECURITY:

config port_security system max_learning_addr no_limit
disable port_security trap_log
config port_security ports 1-18 admin_state disable max_learning_addr 32 lock_address_mode deleteonreset

Storage time (s) mac addresses in the table:

config fdb aging_time 300
config block tx ports 1-18 unicast disable

Let’s solve zero IP for a bunch of mac + ip addresses:

config address_binding ip_mac ports 1-18 allow_zeroip enable

You can enable NetBios filtering on ports, so to speak, to prohibit access to shared drives:

config filter netbios 1-18 state enable
config filter extensive_netbios 1-18 state enable

Configure filtering of harmful DoS packets:

config dos_prevention dos_type land_attack action drop state enable 
config dos_prevention dos_type blat_attack action drop state enable 
config dos_prevention dos_type tcp_null_scan action drop state enable 
config dos_prevention dos_type tcp_xmasscan action drop state enable 
config dos_prevention dos_type tcp_synfin action drop state enable 
config dos_prevention dos_type tcp_syn_srcport_less_1024 action drop state enable 
config dos_prevention dos_type ping_death_attack action drop state enable 
config dos_prevention dos_type tcp_tiny_frag_attack action drop state enable 
config dos_prevention trap disable
config dos_prevention log disable

Blocking of third-party DHCP servers on all ports except incoming:

config filter dhcp_server ports all state disable
config filter dhcp_server ports 1-16,18 state enable
config filter dhcp_server illegal_server_log_suppress_duration 30min
config filter dhcp_server trap_log enable

BPDU flood protection:

enable bpdu_protection
config bpdu_protection recovery_timer 300
config bpdu_protection trap none
config bpdu_protection log attack_detected
config bpdu_protection ports 1-16,18 state enable  
config bpdu_protection ports 1-18 mode drop

Enabling SAFEGUARD ENGINE:

config safeguard_engine state enable utilization rising 98 falling 90 trap_log enable mode fuzzy

Disable sending emails via SMTP:

disable smtp

Configure SNTP time settings:

enable sntp
config time_zone operator + hour 2 min 0
config sntp primary 192.168.1.1 secondary 0.0.0.0 poll-interval 40000
config dst disable

Disable management of multicast traffic and some standard parameters:

disable igmp_snooping
disable mld_snooping

ARP options:

config arp_aging time 20
config gratuitous_arp send ipif_status_up enable
config gratuitous_arp send dup_ip_detected enable
config gratuitous_arp learning enable

Setting temperature notifications:

config temperature threshold high 79
config temperature threshold low 11
config temperature trap state disable
config temperature log state enable

Saving configuration:

save all

Configuring Traffic Segmentation on D-Link Switches

I will give an example of setting up Traffic Segmentation on D-Link switches.
Traffic Segmentation prohibits ports to communicate with each other directly, on other manufacturers’ switches, this function is called Protected Ports, Port Isolation, etc.

Before configuring Traffic Segmentation, you need to know exactly which of the Uplink ports, let’s say on the switch DES-3200-18 C1, port 17 is incoming (uplink), then we execute the following two commands:

Continue reading “Configuring Traffic Segmentation on D-Link Switches”

Configuring DHCP+TFTP for DOCSIS

Recently, it was necessary to configure the issuance of IP addresses to several old DOCSIS modems and the host located after the modem.
At hand was the Arris Cadant C3 and Thomson TCM-420 modems.

First of all, let’s start a DHCP server that will issue IP addresses to modems, for example, as I described in this article – Installing and configuring isc-dhcp-server.
And also we will launch a TFTP server on which there will be files for modems, for example, as I described in the article – Installing and Configuring a TFTP Server

Configuring ports in Cisco switches

For the test I will configure ports on Cisco Catalyst 6509-E.

I’ll give an example of setting Access of the port (the traffic goes only over one specified vlan without a tag):

interface GigabitEthernet1/1
description TEXT
switchport
switchport access vlan 226
switchport mode access
no shutdown

Now I’ll give an example of setting Trunk of the port (traffic goes through one or several vlan with a tag only):

interface GigabitEthernet1/2
description TEXT
switchport
switchport trunk encapsulation dot1q
switchport trunk allowed vlan 207,228
switchport mode trunk
no shutdown

And the third option, Hybrid port (traffic goes only on one vlan without a tag and on one or several vlan with a tag):

interface GigabitEthernet1/3
description TEXT
switchport
switchport trunk encapsulation dot1q
switchport trunk native vlan 226
switchport trunk allowed vlan 207,226
switchport mode trunk
no shutdown

To specify parameters for several ports at once:

interface range GigabitEthernet1/1-24

We prohibit automatic switching of the port to access or trunk mode:

interface range GigabitEthernet1/1-24
switchport nonegotiate
exit

See also my article – Configuring link aggregation on the Cisco Catalyst 6500

Minicom error solution “Device /dev/ttyS0 is locked”

Sometimes if the connection to Linux from which the connection was made via Minicom is lost, then the next time you start Minicom, you could see an error:

Device /dev/ttyS0 is locked.

The name /dev/ttyS0 may be different, depending on what COM port you have.
To avoid such an error, it is necessary to correctly shut down Minicom using the CTRL-A keys and then the Q key.

You can solve the problem by killing the process as a root user with the command:

killall -9 minicom

In Ubuntu, use the command:

sudo killall -9 minicom

Or you can simply delete the file «LCK..ttyS0» in the /var/lock/ directory.

After that, the error will not be displayed.