Databases, Systems & Networks

In order to log in to a MySQL server, you can run mysql command along with your login credentials and server’s IP address as arguments. For example:

$ mysql -u $MYSQL_ROOT -p $MYSQL_PASS -h 192.168.10.1

However, besides the inconvenience of typing extra arguments, using plain-text login credentials in a command line like above is really not a secure way to access a MySQL server. In a multi-user Linux environment, what you type in command line can easily be revealed to others who happen to run ps on the same host at the same time.

MySQL offers a way for you to log in to MySQL server without password, by using an external MySQL configuration file. In Linux, there are two different kinds of MySQL configuration files: (1) /etc/my.cnf and (2) ~/.my.conf. While any system-wide MySQL configuration is defined in /etc/my.cnf, any user-specific MySQL configuration is stored in ~/.my.cnf. You can leverage ~/.my.cnf, to define your MySQL login credential in the file.

$ vi ~/.my.cnf

[client]
user=alice
password=alice_passwd
host=192.168.10.1

Make sure to have the configuration file readable to you only.

$ chmod 0600 ~/.my.cnf

Once ~/.my.cnf is created, simply typing mysql command will let you log in to 192.168.10.1 as alice, and you no longer need to provide login password separately.

Source: Xmodulo

Source: nixCraft

How do I use mysqldump command with Unix shell pipes?

The mysqldump client is a backup program which is used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format. The syntax is as follows to dump a database called foo to the remote server called server1.cyberciti.biz:

mysqldump db-name | mysql -h server1.dbsysnet.com db-name
mysqldump foo | mysql -h server1.dbsysnet.com foo

However, I recommend using VPN or ssh client to secure data transfer using the shell pipes as follows:

mysqldump db-name | ssh [email protected] mysql db-name
mysqldump -u vivek -p'myPassWord' foo | ssh [email protected] mysql -u backup -p'myPassword' bar

Source: nixCraft

I‘m a new MySQL server user. My server is running on a CentOS Linux. How can I check the number of active MySQL connections on Linux based system?

You can use the following commands on Linux or Unix-like systems:

• mysqladmin status command
• MySQL show status command
• netstat or ss commands

mysqladmin status command example

Open the terminal App or login to the remote server using ssh:

ssh [email protected]

Type the following command to get a short status message from the MySQL server:

mysqladmin status
## OR ##
mysqladmin status -u root -p
## OR ##
mysqladmin status -h db1.dbsysnet.com -u root -p

Sample outputs:

Uptime: 691356  Threads: 5  Questions: 83237956  Slow queries: 102736  Opens: 3585  Flush tables: 1  Open tables: 1019  Queries per second avg: 120.398

MySQL show status command to see open database connections example

First, connect to the your mysql server:

mysql -u root -p

Type the following sql query to see the number of connection attempts to the MySQL serverincludes both failed and successful connection attempts:

mysql> show status like 'Conn%';

Sample outputs:

You can use the following sql command to see the number of currently open connections at mysql> prompt:

mysql> show status like '%onn%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Aborted_connects         | 7       |
| Connections              | 6304067 |
| Max_used_connections     | 85      |
| Ssl_client_connects      | 0       |
| Ssl_connect_renegotiates | 0       |
| Ssl_finished_connects    | 0       |
| Threads_connected        | 7       | <---- No of currently open connections
+--------------------------+---------+
7 rows in set (0.00 sec)

Lire la suite…

One of the most daunting prospects of administering your own server on a public network is dealing with your server’s security. While security threats in a networked world are real and it is always important to be mindful of security issues, protecting against possible attacks is often a matter of exercising basic common sense and adhering to some general best practices.

This guide takes a broad overview of common security concerns and provides a number of possible solutions to common security problems. You are encouraged to consider deploying some of these measures to “harden” your server against possible attacks.

It’s important to remember that all of the solutions we present in this document are targeted at specific kinds of attacks, which themselves may be relevant only in specific configurations. Security solutions need to be tailored to the kind of services that you’re providing and the software you’re running, and the decision whether or not to deploy a specific security solution is often a matter of personal discretion and cost-benefit analysis.

Perhaps most importantly, it should be understood that security is a process, not a product (credit to Bruce Schneier.) There is no “magic bullet” set of guidelines that can be followed to ensure the security of any system. Threats are constantly evolving, so vigilance is required on the part of network administrators to prevent unauthorized access to systems.

Keep Systems and Software Up To Date

One of the most significant sources of security vulnerabilities are systems running out of date software with known security holes. Make a point of using your system’s package management tools to keep your software up to date; this will greatly assist in avoiding easily preventable security intrusions.

Running system updates with the package management tool, using apt-get update && apt-get upgrade (for Debian and Ubuntu Systems) or yum update (for CentOS and Fedora systems) is simple and straightforward. This practice ensures that if your distribution maintains active security updates, your system will be guarded against many security holes in commonly used software packages.

System update tools will, however, not keep software up to date that you’ve installed outside of package management. This includes software that you’ve compiled and installed “by hand” (e.g. with ./configure && make && make install) and web-based applications that you’ve installed from a software developer’s site, as is often the case with applications like WordPress and Drupal. Also excluded from protection will be libraries and packages you’ve installed with supplementary package management tools like Ruby’s Gems, Perl’s CPAN tool, Python easy_install, and Haskell Cabal. You will have to manage the process of keeping these files up to date yourself.

The method you use to make sure that your entire system is kept up to date is a matter of personal preference, and depends on the nature of your workflow. We would recommend trying very hard to use the versions of software provided by your operating system or other programming platform-specific package management tools. If you must install from “source,” we would recommend that you save the tarballs and source files for all such software in /src/ or ~/src/ so that you can keep track of what software you’ve installed in this manner. Often, you can remove a manually compiled application by issuing make uninstall in the source repository (directory). Additionally, it may be helpful to maintain a list of manually installed software, with version numbers and download locations. You may also want to investigate packaging your own software so that you can install it with apt, yum or pacman.

Because of the complexity of maintaining software outside of the system’s package management tools we strongly recommend avoiding manually installing software unless absolutely necessary. Your choice in a Linux distribution should be heavily biased by the availability of software in that distro’s repositories for the systems you need to run on your server.

Lire la suite…