MySQL administration

Today we will talk about the most common activities performed when administering a MySQL server. The first thing we need to know is how to log in to MySQL, this can be accomplished by using the following command:

$ mysql -h host_name_or_IP -P port_number -u user_name -p

Where: -h allows you to specify witch FQDN or IP address MySQL client must connect to (localhost by default), -P port number where MySQL is listening (3306 default), -u means username (root by default), and -p means prompt for password. Note: if we want to connect with the root account to localhost on port 3306 you just need to type:

$ mysql -p

Create users

There are three methods for creating users in MySQL, one is by using CREATE command, other is by manually modifying the user table of the MySQL database, the last one consist in creating users via GRANT commands. The first method is recommended by the MySQL developers because is less prone to human errors (depending of the human ;)). The syntax of the command is the following:

mysql> CREATE USER 'user_name'@'host_name_or_IP'\ 
       IDENTIFIED BY my_password';

With the last commands MySQL created a user called user_name that has permission to log to a MySQL server with the address host_name_or_IP and has the password my_password. For example if we want to create a user named aang, that has permission to log in localhost and has the password appa you have to use the following command:

mysql> CREATE USER 'aang'@'localhost' IDENTIFIED BY 'appa';

If we want to create a user with the second method, we do the following:

mysql> INSERT INTO mysql.user VALUES ('host_name_or_IP',\
      'user_name', PASSWORD('my_password'), 'Y', 'Y', 'Y', 'Y',\
      'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
      'Y', 'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '',\
      '', 0, 0, 0, 0);

So the user aang that logs in localhost and has appa for password will be created with this:

mysql> INSERT INTO mysql.user VALUES ('localhost', 'aang',\
       PASSWORD('appa'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
       'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
       'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);

With GRANT we will accomplish the same if we do the following:

GRANT ALL ON database_name.* TO 'user_name'@'host_name_or_IP' IDENTIFIED BY 'my_password';

Delete users

We have two methods for deleting users: DROP USER and DELETE FROM mysql.user. First method:

mysql> DROP USER 'aang'@'localhost';

Second method:

mysql> DELETE FROM mysql.user WHERE User='aang';

Welcome to my first blog.

The reason of this site is to make it a repository of some of the things I've learned in my work as a network administrator and playing with the various gadgets and gismos that have fallen into my hands.

This is nothing more than a site that will help me remember my experiences and possibly will serve you resolve certain problems and refresh some concepts.

I Hope that the information contained in this site will aid you in your journey.

Gabriel José Rojas Díaz

EOF

Nginx configuration and install

 

Nginx Install

In Debian it is advisable to use dotdeb repositories because it contains updated pre-compiled versions of Nginx. To do this place in /etc/apt/sources.list the repository packages.dotdeb.org, download and install their public key:

echo "deb http://packages.dotdeb.org squeeze all" >> /etc/apt/sources.list"
wget http://www.dotdeb.org/dotdeb.gpg
apt-key add dotdeb.gpg
apt-get update 

Then install Nginx with apt-get:

apt-get install nginx

On FreeBSD you can use pkg_add -vvv nginx (if you have FreeBSD 10 onward use pkg install nginx). You can also install it with make install clean from /usr/ports/www/nginx (only if you have ports installed an configured correctly). Nginx comes installed by default on OpenBSD starting from 5.2.

 

Configuration

Once installed we can find Nginx's configuration files on /etc/nginx. Those that uses apache will notice that Debian uses sites-available and sites-enabled folders (on /etc/nginx/). For those who doesn't have a clue about the folders in the first one you can find the configuration files of the "available" Web sites and on the second folder a symbolic link to the sites that are on sites-available. As you can infer if there is not a symbolic link from a web page on sites-available to sites-enabled Nginx will NOT server the site. Now i will show you an edited /etc/nginx/nginx.conf file and try to explain its options:

user www-data;
worker_processes 4;
pid /var/run/nginx.pid;

events {
    worker_connections 768;
}

http {
  sendfile on;
  tcp_nopush on;
  tcp_nodelay on;
  types_hash_max_size 2048;
  server_tokens off;
  include /etc/nginx/mime.types;
  default_type application/octet-stream;
  access_log /var/log/nginx/access.log;
  error_log /var/log/nginx/error.log;
  gzip on;
  gzip_disable "msie6";
  gzip_vary on;
  gzip_types text/plain text/css application/x-javascript application/xml
  application/json image/png image/gif image/jpeg image/jpg;
  gzip_comp_level 6;
  gzip_buffers 16 8k;
  client_body_in_single_buffer on;
  client_body_buffer_size  1K;
  client_header_buffer_size 1k;
  client_max_body_size 1k;
  large_client_header_buffers 2 1k;
  client_body_timeout   10;
  client_header_timeout 10;
  keepalive_timeout     30;
  send_timeout          10;
  include /etc/nginx/conf.d/*.conf;
  include /etc/nginx/sites-enabled/*;
}