System Administration

My MySQL Cheat Sheet

Posted on by

I know, man.  No, I mean I know I could use ‘man pages’!  Or I could just ‘google it’ but then it isn’t mine.  Since I do not have time for a complete brain-dump this MySql “cheat sheet” will grow over time.  Feel free to add your favorite MySql commands in the comments, if their really useful I’ll add them to the list!

If you don’t know what MySql is…look it up!  And, who are you?!  Seriously…

Create a DB & Assign to a User:

Create a New DB, Create a User and Grant them permissions to the New DB.

mysql> create database someDB_name;
Query OK, 1 row affected (0.13 sec)

mysql> create user 'someUser_name'@'localhost' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.13 sec)

mysql> GRANT ALL PRIVILEGES ON someDB_name.* to someUser_name@localhost;
Query OK, 0 rows affected (0.05 sec)

The above should be pretty self explanatory but for thoroughness sake…  The first line creates an empty database.  At that point only the root or admin user can use this database.  The Second command, creates a user account and assigns it a password.  This user account has NO privileges at this point.  The Third line is the most important.  When you grant permissions you can grant global permissions *.* meaning you can access ALL databases (not a good idea).  OR you can set Database permissions like I did above; database_name.*.  That .* after the database name means you have full privileges to that database only.  OR you can refine the permissions even further and grant permissions to a specific table in the database: database_name.some_table. Hope that clarifies things.  To state it in a more succinct way use this framework:

 GRANT [type of permission] ON [database_name].[table_name] TO ‘[username]’@'localhost’;

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now be in effect.  I always like to test the account out before giving the account to the user.  To test out your new user, log out and log back in as the user:

mysql> quit 
mysql -u [username]-p

Revoke User Access or Delete a whole DB:

If you need to revoke a permission, the structure is almost identical to granting it:

 REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

You delete databases with DROP, you can also use DROP to delete a user altogether:

 DROP USER ‘demo’@‘localhost’;

 Recover Access when you have forgotten the root password:

Not that, that ever happens…

mysqld_safe --skip-grant-tables
mysql --user=root mysql

    update user set Password=PASSWORD('new-password') where user='root';
    flush privileges;
    exit;

That’s it for now.  More to follow…

Putting ‘lsof’ to use

Posted on by

lsof is a powerful tool that has proven very userful over the years in troubleshooting and forensic investigations.  Here are some useful lsof command examples:

In this example we are looking at all the files a given process has open (pid=1655 here this is the zabbix agent)

lsof -p 1767

Note you can clean up the output with something like the ‘cut’ or ‘awk’ command to focus in on the columns you are most interested in.  The output from the above command looks like this:

COMMAND    PID   USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
zabbix_ag 1767 zabbix  cwd    DIR  253,0     4096       2 /
zabbix_ag 1767 zabbix  rtd    DIR  253,0     4096       2 /
zabbix_ag 1767 zabbix  txt    REG  253,0   209432 1315973 /usr/sbin/zabbix_agentd
zabbix_ag 1767 zabbix  mem    REG  253,0   156872  917626 /lib64/ld-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0  1922152  917633 /lib64/libc-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0   145720  917661 /lib64/libpthread-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0    22536  917663 /lib64/libdl-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0    91096  917658 /lib64/libz.so.1.2.3
zabbix_ag 1767 zabbix  mem    REG  253,0   598680  917655 /lib64/libm-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0   113952  917683 /lib64/libresolv-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0    43392  917665 /lib64/libcrypt-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0   386040  917664 /lib64/libfreebl3.so
zabbix_ag 1767 zabbix  mem    REG  253,0   224328 1317809 /usr/lib64/libssl3.so
zabbix_ag 1767 zabbix  mem    REG  253,0  1286744 1317807 /usr/lib64/libnss3.so
zabbix_ag 1767 zabbix  mem    REG  253,0    21256  917689 /lib64/libplc4.so
zabbix_ag 1767 zabbix  mem    REG  253,0   243096  917688 /lib64/libnspr4.so
zabbix_ag 1767 zabbix  mem    REG  253,0   177952 1317480 /usr/lib64/libnssutil3.so
zabbix_ag 1767 zabbix  mem    REG  253,0    17096  917690 /lib64/libplds4.so
zabbix_ag 1767 zabbix  mem    REG  253,0   108728 1312777 /usr/lib64/libsasl2.so.2.0.23
zabbix_ag 1767 zabbix  mem    REG  253,0   183896 1317813 /usr/lib64/libsmime3.so
zabbix_ag 1767 zabbix  mem    REG  253,0    63304  917530 /lib64/liblber-2.4.so.2.5.6
zabbix_ag 1767 zabbix  mem    REG  253,0   317168  917569 /lib64/libldap-2.4.so.2.5.6
zabbix_ag 1767 zabbix  DEL    REG    0,4                0 /SYSV6c0004c9
zabbix_ag 1767 zabbix  mem    REG  253,0    65928  917605 /lib64/libnss_files-2.12.so
zabbix_ag 1767 zabbix    0r   CHR    1,3      0t0    3662 /dev/null
zabbix_ag 1767 zabbix    1w   REG  253,2      386     120 /var/log/zabbix/zabbix_agentd.log
zabbix_ag 1767 zabbix    2w   REG  253,2      386     120 /var/log/zabbix/zabbix_agentd.log
zabbix_ag 1767 zabbix    3wW  REG  253,2        4  389438 /var/run/zabbix/zabbix_agentd.pid
zabbix_ag 1767 zabbix    4u  IPv4  13481      0t0     TCP *:zabbix-agent (LISTEN)
zabbix_ag 1767 zabbix    5u  IPv6  13482      0t0     TCP *:zabbix-agent (LISTEN)

In the above: the FD column represents the File Descriptor and Mode (Read/Write).  The 4th line from the bottom has an FD value of (2w) meaning it is open for writing, makes sense since it is a log.

The -Z option for ‘lsof’ specifies how SELinux security contexts are to be handled.  This option is only available of Linux systems that have an SELinux enabled kernel.

# lsof -Z -p 1767
COMMAND    PID SECURITY-CONTEXT                USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  cwd    DIR  253,0     4096       2 /
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  rtd    DIR  253,0     4096       2 /
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  txt    REG  253,0   209432 1315973 /usr/sbin/zabbix_agentd
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   156872  917626 /lib64/ld-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0  1922152  917633 /lib64/libc-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   145720  917661 /lib64/libpthread-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    22536  917663 /lib64/libdl-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    91096  917658 /lib64/libz.so.1.2.3
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   598680  917655 /lib64/libm-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   113952  917683 /lib64/libresolv-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    43392  917665 /lib64/libcrypt-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   386040  917664 /lib64/libfreebl3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   224328 1317809 /usr/lib64/libssl3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0  1286744 1317807 /usr/lib64/libnss3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    21256  917689 /lib64/libplc4.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   243096  917688 /lib64/libnspr4.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   177952 1317480 /usr/lib64/libnssutil3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    17096  917690 /lib64/libplds4.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   108728 1312777 /usr/lib64/libsasl2.so.2.0.23
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   183896 1317813 /usr/lib64/libsmime3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    63304  917530 /lib64/liblber-2.4.so.2.5.6
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   317168  917569 /lib64/libldap-2.4.so.2.5.6
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  DEL    REG    0,4                0 /SYSV6c0004c9
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    65928  917605 /lib64/libnss_files-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    0r   CHR    1,3      0t0    3662 /dev/null
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    1w   REG  253,2      386     120 /var/log/zabbix/zabbix_agentd.log
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    2w   REG  253,2      386     120 /var/log/zabbix/zabbix_agentd.log
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    3wW  REG  253,2        4  389438 /var/run/zabbix/zabbix_agentd.pid
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    4u  IPv4  13481      0t0     TCP *:zabbix-agent (LISTEN)
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    5u  IPv6  13482      0t0     TCP *:zabbix-agent (LISTEN)

I’ll add more when I have time, comment if you want to see something specific.

 

 

 

Changing the Volume Group Name

Posted on by

One of the problems with cloning a system is that it has the same volume group names as the server it was cloned from.  Not a huge problem but it can limit your ability to leverage the volume group.  The fix appears easy but there is a gotcha.

RedHat provides a nice utility: vgrename

If you use that command and think you are done, you will be sorely mistaken if your root file system is on a volume group!  I’m speaking from experience there, so listen up!

If you issue the vgrename command:

# vgrename OldVG_Name NewVG_Name

it works like a charm.  If you happen to reboot your system at this point you are in big trouble… The system will Kernel Panic.

If you updated/changed the name of the VG that contains the root file system you need to modify the following two files to reflect the NewVG_Name.

  1. In /etc/fstab.   This one is obvious and I usually remember.
  2. In /etc/grub.conf.  Otherwise the kernel tries to mount the root file-system using the old volume group name.

The change is easy using ‘vi’.  Open the file in vi, then use sed from within vi; for example:

vi /etc/fstab
:%s/OldVG_Name/NewVG_Name/g
:wq

Don’t forget to save the changes.