How-to | Welcome to Linux how-to

CONTENTS:

About this Tutorial
MySQL Administration
Working with Tables
MySQL Commands vs. Postgres Commands

About this Tutorial
I’ll be adding to this tutorial in the coming weeks. If you have ideas of things you’d really like to see here, email me.
MySQL Administration Continue reading

Basic MySQL administration

CONTENTS:

About this Tutorial
MySQL Administration
Working with Tables
MySQL Commands vs. Postgres Commands

About this Tutorial
I’ll be adding to this tutorial in the coming weeks. If you have ideas of things you’d really like to see here, email me.
MySQL Administration
This section goes over the basics of maintaining a database rather than actual data. It includes creation of databases, creation of users, granting of privileges to users, changing passwords, and other activities from within the mysql CLI front end to MySQL.
Entering and Exiting the mysql Program

Your starting point for MySQL maintenance is the mysql program:

[nilesh@nilesh]$ mysql
ERROR 1045: Access denied for user: 'nilesh@localhost' (Using password: NO)
[nilesh@nilesh]$

OOPs! User nilesh is apparently password protected, so we must run mysql to query for a password, and then type in the password (which of course types invisibly)…

[nilesh@nilesh]$ mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
[nilesh@nilesh]$

In the preceding, you ran mysql with the -p option so that it would query for the password, you typed in the password, and then you exited the mysql program by typing quit.

By the way, if the account has no password (this is sometimes an installation default), you would just press Enter when prompted for the password.

Perhaps you want to log in as root instead of nilesh. This would probably be the case if you wanted to add a database or any other high responsibility action. Here’s how you log in as root:

[nilesh@nilesh]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
[nilesh@nilesh]$

The -u root tells mysql to log you in as root, and as previously discussed, the -p tells mysql to query for a password.

In mysql, when you see a prompt like this:

mysql>

you are in the mysql program. For the rest of this section, most examples will begin and end in mysql.

There are more than one root account, and you must password protect all of them. Read on…

mysql> use mysql
Database changed
mysql> select host, user, password from user;
+-----------------------+--------+------------------------------------------+
| host                  | user   | password                                 |
+-----------------------+--------+------------------------------------------+
| localhost             | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost.localdomain | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost             |        |                                          |
| localhost.localdomain |        |                                          |
| localhost             | nilesh | 195CABF93F868C84F7FB2CD44617E468487551B6 |
+-----------------------+--------+------------------------------------------+
5 rows in set (0.00 sec)

mysql>

As you can see, there’s a root account at localhost and another to localhost.localdomain. Both must be password protected. In reality, all accounts should be password protected.

Exploring Your MySQL Installation
From within mysql you can find quite a bit of information concerning your installation. As what user are you logged into mysql? What databases exist? What tables exist in a database? What is the structure of a table? What users exist?

Because different operations require different privileges, to save time we’ll perform all these actions logged into mysql as root.

Let’s start with finding out your username within mysql:

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

Now let’s list all the users authorized to log into mysql:

mysql> use mysql
Database changed
mysql> select host, user, password from user;
+-----------------------+--------+------------------------------------------+
| host                  | user   | password                                 |
+-----------------------+--------+------------------------------------------+
| localhost             | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost.localdomain | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost             |        |                                          |
| localhost.localdomain |        |                                          |
| localhost             | nilesh | 195CABF93F868C84F7FB2CD44617E468487551B6 |
+-----------------------+--------+------------------------------------------+
5 rows in set (0.00 sec)
mysql>

Notice there are two root logons — one at localhost and one at localhost.localdomain. They can have different passwords, or one of them can even have no password. Therefore, YOU’D BETTER check to make sure they’re both password protected. DO NOT forget the password(s). If you lose all administrative passwords, you lose control of your mysql installation. It’s not like Linux where you can just stick in a Knoppix CD, mount the root directory, and erase the root password in /etc/passwd.
User Maintenance
Before discussing user maintenance, it’s necessary to understand what properties exist for each user. To do that, we’ll use mysql -e option to execute SQL commands and push the output to stdout. Watch this:

[nilesh@nilesh]$ mysql -u root -p -e “use mysql;describe user;” | cut -f 1
Enter password:
Field
Host
User
password
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Show_db_priv
Super_priv
Create_tmp_table_priv
Lock_tables_priv
Execute_priv
Repl_slave_priv
Repl_client_priv
ssl_type
ssl_cipher
x509_issuer
x509_subject
max_questions
max_updates
max_connections
[nilesh@nilesh]$

The word “Field” is a heading, not a piece of information about a user. After that, the first three fields are the user’s host, username and password. The password is encoded. If you hadn’t included the cut command, you’d have seen that the same username can exist in multiple hosts, even if both hosts refer to the same physical machine. That’s why it’s vital to MAKE SURE TO password protect ALL users. The next several fields after password are privileges that can be granted to the user, or not.

As will be discussed later, there’s a way to grant ALL privileges to a user. From a security point of view this is very dangerous, as ordinary looking users can be turned into backdoors. I’d suggest always granting and revoking specific privileges. Here is a list of the privileges that MySQL users can have, and what those privileges allow them to do:

USER FIELD
PRIVILEGE FUNCTION

Select_priv Select Ability to use the select command. In other words, ability to read.

Insert_priv Insert Ability to insert new data — insert a new row.

Update_priv Update Ability to change existing data — change contents of a row.

Delete_priv Delete Ability to delete rows of existing data.

Create_priv Create Ability to create a new table.

Drop_priv Drop Ability to drop a table.

Reload_priv Reload

Shutdown_priv Shutdown

Process_priv Process

File_priv File

Grant_priv Grant Ability to grant and revoke privileges to others.

References_priv References

Index_priv Index Ability to create new indexes or drop indexes.

Alter_priv Alter Ability to change the structure of a table.

Show_db_priv

Super_priv

Create_tmp_table_priv
Ability to create temporary tables.

Lock_tables_priv
Ability to lock tables.

Execute_priv
Repl_slave_priv
Repl_client_priv

The root user, or any user given sufficient privileges, can create new users with the grant command:

mysql> grant select on test2.* to myuid@localhost identified by 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, password from user;
+-----------------------+--------+------------------------------------------+
| host                  | user   | password                                 |
+-----------------------+--------+------------------------------------------+
| localhost             | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost.localdomain | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost             |        |                                          |
| localhost.localdomain |        |                                          |
| localhost             | nilesh | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost             | myuid  | 195CABF93F868C84F7FB2CD44617E468487551B6 |
+-----------------------+--------+------------------------------------------+
6 rows in set (0.00 sec)

mysql>

In the preceding, we grant one privilege, select, on every table in the test2 database (test2.*), to user myuid at host localhost (myuid@localhost), giving that user the password “mypassword”. We then query table user in the mysql database (mysql.user) in order to see whether user myuid has been created. Indeed he has.

Granting select privilege is insufficient for any user using any app that modies data. Let’s give myuid more privileges in the test2 database:

mysql> grant Insert, Update, Delete, Create on test2.* to myuid@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>

Now user myuid can not only select, but can insert rows, update rows, delete rows, and create tables and databases. Conspicuously missing is the ability to drop tables and databases — that can be more dangerous, in the hands of a careless but not malicious user, than some of the other abilities.

Privileges granted by the grant command are not kept in the mysql.user table, but instead in the mysql.db table. You can see results by issuing this command from the operating system:

[nilesh@nilesh] mysql -u root -p -e 'select * from mysql.db where host="localhost" and user="myuid";' > temp.txt
Enter password:
[nilesh@nilesh]

The results look like this:

Host    Db    User    Select_priv    Insert_priv    Update_priv    Delete_priv    Create_priv    Drop_priv    Grant_priv    References_priv    Index_priv    Alter_priv    Create_tmp_table_priv    Lock_tables_priv
localhost    test2    myuid    Y    Y    Y    Y    Y    N    N    N    N    N    N    N

You can revoke privileges like this:

mysql> revoke Delete, Create on test2.* from myuid@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>

If you redo the select on mysql.db, you’ll see that those two privileges are now marked N.

To actually delete a user, you use a SQL statement to delete him from the mysql.users table after revoking all his privileges.

When deleting the user from mysql.user, if you forget the where clause, or any of its tests, especially the test on column user, you will delete too many users — possibly all users, in which case you’ll have no way to operate the database. BE VERY CAREFUL!

Now that you understand the potential landmines, here’s how you delete a user:

mysql> revoke all on test2.* from myuid@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where user='myuid' and host='localhost';
Query OK, 1 row affected (0.00 sec)

mysql>

Exploring Your Databases
You can refer to a table in two ways: By name after connecting to its database with a use statement, and with the databasename.tablename syntax. The former is much more common in applications, but the latter is often used in database administration, especially when you must access the system database (mysql) in order to perform work on a different database.

The first item for exploration is to find all databases:

mysql> show databases;
+-------------------+
| Database          |
+-------------------+
| depot_development |
| depot_production  |
| depot_test        |
| mysql             |
| test              |
| test2             |
+-------------------+
6 rows in set (0.00 sec)

mysql>

In the preceding, you went into the mysql program, determined what databases existed. Now let’s explore the test database:

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dogs           |
| people         |
+----------------+
2 rows in set (0.00 sec)

mysql>

So the database test has two tables, dogs and people. Let’s examine the columns in each of those tables:

mysql> show columns from dogs;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| owner | varchar(20) |      | PRI |         |       |
| name  | varchar(20) |      | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show columns from people;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| lname | varchar(20) |      | PRI |         |       |
| fname | varchar(20) |      |     |         |       |
| mname | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Another way to get the same information is with the describe command:

mysql> describe dogs;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| owner | varchar(20) |      | PRI |         |       |
| name  | varchar(20) |      | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe people;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| lname | varchar(20) |      | PRI |         |       |
| fname | varchar(20) |      |     |         |       |
| mname | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Working with Tables
The following script creates and loads the table. Explanations appear to the right of the code:

####################################################
# CREATE THE DATABASE
####################################################
#drop database if exists test2;
#create database test2;
#use test2;

####################################################
# REINITIALIZE THE TABLES. ASSUME USER IS ALREADY
# CONNECTED TO THE PROPER DATABASE FROM WITHIN
# mysql OR psql.
####################################################
drop table if exists members;
drop table if exists families;

####################################################
# CREATE THE TWO TABLES FORMING A 1 TO MANY RELATIONSHIP.
# FAMILIES IS THE ONE, AND MEMBERS IS THE MANY.
# CREATE UNIQUE INDEX SUCH THAT FAMILY_ID PLUS NAME IN
# MEMBERS IS FORCED TO BE UNIQUE.
####################################################
create table families (
id int not null auto_increment,
name varchar(20) not null,
primary key (id)
);
show tables;

create table members (
id int not null auto_increment,
family_id int not null,
name varchar(16) not null,
primary key (id),
foreign key (family_id) references families
on delete restrict
on update cascade
);

create unique index familymember on members (family_id, name);

describe families;
describe members;

####################################################
# LOAD families WITH THREE ROWS
####################################################
insert into families (name) values (‘Albertson’);
insert into families (name) values (‘Becker’);
insert into families (name) values (‘Cintez’);

####################################################
# LOAD members WITH THREE ROWS FOR THE ‘Albertson’
# FAMILY. USE MONOLITHIC SQL STATEMENTS TO ACCOMPLISH
# THIS.
####################################################
insert into members (family_id, name)
select families.id, ‘Alvin’ from families
where families.name = ‘Albertson’;
insert into members (family_id, name)
select families.id, ‘Andrea’ from families
where families.name = ‘Albertson’;
insert into members (family_id, name)
select families.id, ‘Arthur’ from families
where families.name = ‘Albertson’;

####################################################
# LOAD members WITH THREE ROWS FOR Becker and Cintez
# FAMILY. INSTEAD OF MONOLITHIC SQL STATEMENTS,
# LOOK UP families.id FROM families.name, AND THEN
# USE THAT id TO INSERT THE MEMBERS.
# SETTING @id TO NULL PREVENTS USAGE OF PREVIOUS VALUES WHEN
# THE SELECT”S WHERE CLAUSE FAILS
####################################################

select @id:=null;
select @id:=id from families where name=’Becker’;
insert into members (family_id, name) values(@id, ‘Betty’);
insert into members (family_id, name) values(@id, ‘Ben’);
insert into members (family_id, name) values(@id, ‘Bob’);

select @id:=null;
select @id:=id from families where name=’Cintez’;
insert into members (family_id, name) values(@id, ‘Charles’);
insert into members (family_id, name) values(@id, ‘Christina’);
insert into members (family_id, name) values(@id, ‘Cindy’);

####################################################
# SHOW EACH FAMILY AND ITS MEMBERS
####################################################
select families.id, families.name, members.name
from families, members where
(members.family_id = families.id);

Use this only if creating
a new database, otherwise
leave commented out

Drop the tables if they exist
to make room for new tables
of the same name

The families table is the 1 of
1 to many.

The members table is the many
of the 1 to many.
members.family_id matches
families.id.

Foreign key means you can’t
delete a family that
still has members

This index prevent two family
members from having the same
first name

Show the structures of the two
tables just created

Load the families table.

Monolithic insert from select

Monolithic insert from select

Monolithic insert from select

The following inserts are
performed more procedurally,
by first finding families.id
based on families.name, and
then using that id as
members.family_id.

Prevent ghosts of selects past
Find id from families.name
Do the insert

Prevent ghosts of selects past
Find id from families.name
Do the insert

Join the tables in the
where clause, and find
all family members

The preceding code exercised the creation of databases and tables, insertion of rows, and viewing a one to many relationship with a join created in a where clause. The preceding is pretty much DBMS independent, and runs on both MySQL and Postgres. However, certain legitimate SQL queries don’t work on some versions of MySQL:

MySQL Commands vs. Postgres Commands

 

TASK
MySQL
Postgres
DISCUSSION
Comments in SQL code # as first printable on line Anything between /* and */ According to my experimentation, MySQL comments produce an error in Postgres, and vice versa, which is too bad.
Connect to database use dbname; \c dbname
Find all databases show databases; \l Postgres command is a lower case L, not numeral 1. It errors out if  you end it with a semicolon. It can be used in a script file.
Find all tables in current database show tables; \dt \dt shows only user level tables. Use \dS to see system level tables.
Show structure of one table describe tblname; \d tblname Postgres version doesn’t show length of varchar.
Change database use dbname; \c dbname
Add user grant select on dbname.* to username@userhost identified by ‘userpassword’; create user username with encrypted password=’userpassword’; WARNING: I currently am having problems getting created Postgres users to be able to authenticate.
Grant privileges grant privlist on dbname.* to username@userhost identified by ‘userpassword’; grant privlist on tbllist to slitt; In Postgres, tbllist must be an explicit, comma delimited list of tables. No wildcards.
Change user password grant select  on dbname.* to username@userhost identified by “userpassword”; alter user username encrypted password ‘userpassword’; WARNING: I currently am having problems getting created Postgres users to be able to authenticate.

MySQL database backup script?

How to backup multiple mysql databases ?
A very powerful shell script to backup your mysql databases totally free.. enjoy

#!/bin/bash
NOW=`date +"%Y-%m"`;
BACKUPDIR="location/of/your/backup/dir/$NOW";

### Server Setup ###
#* MySQL login user name *#
MUSER="user";

#* MySQL login PASSWORD name *#
MPASS="pass";

#* MySQL login HOST name *#
MHOST="your-mysql-ip";
MPORT="your-mysql-port";

# DO NOT BACKUP these databases
IGNOREDB="
information_schema
mysql
test
"

#* MySQL binaries *#
MYSQL=`which mysql`;
MYSQLDUMP=`which mysqldump`;
GZIP=`which gzip`;

# assuming that /nas is mounted via /etc/fstab
if [ ! -d $BACKUPDIR ]; then
mkdir -p $BACKUPDIR
else
:
fi

# get all database listing
DBS="$(mysql -u $MUSER -p$MPASS -h $MHOST -P $MPORT -Bse 'show databases')"

# SET DATE AND TIME FOR THE FILE
NOW=`date +"d%dh%Hm%Ms%S"`; # day-hour-minute-sec format
# start to dump database one by one
for db in $DBS
do
DUMP="yes";
if [ "$IGNOREDB" != "" ]; then
for i in $IGNOREDB # Store all value of $IGNOREDB ON i
do
if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
DUMP="NO";         # SET value of DUMP to "no"
#echo "$i database is being ignored!";
fi
done
fi

if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
FILE="$BACKUPDIR/$NOW-$db.gz";
echo "BACKING UP $db";
$MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE
fi
done

The vi, vim editor in linux

In this quick tutorial I will share some vim basics. Vim is a powerful text editor used in CLI (command line interface). As there are lots of configuration files in gnu/linux, which are all in clear text format, you’ll often need to edit them using a text editor. vim (in short vi) is a great tool to use.
One of the first things to know about vi is that it typically functions in three different modes:

  1. Command mode
  2. Insert mode
  3. Last line mode

vi command mode

When you first start editing a file with the vi editor you will be in vi command mode. In this mode you can issue many vi commands, including commands like insert, append, and delete, and other search and navigation commands that let you move around your file.

Possibly the most important thing to know is that when you’re in command mode you can’t insert text immediately. You first need to issue an insert, append, or open command to insert text. These commands are actually fairly simple, and I’ve documented them in this

Lets talk about other vim modes.

vi insert mode

Once you issue a vi insert, append, or open command, you will be in vi insert mode. If you’re working with a modern vi or vim implementation, your vi editor is typically configured to show the current mode of operation, so when you go into insert mode, you’ll see a text string like this on the last line of your vi editor window:

-- INSERT --

At this point you can (a) type text into your file and (b) use the arrow keys to navigate around your file just as you would do with any other text editor.

A very important concept to know is that when you’re in vi insert mode, but you want to switch back to vi command mode, you easily move back to command mode by pressing the [Esc] key. This command is so important, I’ll show it again:

[Esc]

This command is very common, and I often see expert vi users press the [Esc] key several times in a row. They usually do this (a) to be sure they hit the key and they’re really back in command mode, and (b) to hear the beep from the computer, which happens when you press the [Esc] key when you’re already in vi command mode. This seems to serve as a form of feedback which assures them that they’re in command mode.

vi last line mode

The last vi mode is known as vi last line mode. You can only get to last line mode from command mode, and you get into last line mode by pressing the colon key, like this:

:

After pressing this key, you’ll see a colon character appear at the beginning of the last line of your vi editor window, and your cursor will be moved to that position. This indicates that vi is ready for you to type in a “last line command”.

From this vi command prompt you can do all sorts of really amazing things. You can do simple things, like quitting your vi session, like this:

:q

or this:

:q!

or this:

:wq

From last line more you can also perform some amazing vi search commands or vim search and replace commands. Another cool thing is that you can issue Linux or Unix commands from within your vi editor session, like this simple ls command:

:!ls

It’s really handy sometimes to be able to stay in your vi editing session but still be able to run Unix or Linux commands.

And finally, you can also issue many vi configuration commands, such as this command that tells vi to show lines numbers in your current editor window:

:set shownumber

There is a ton of power in this vi last line mode, and I’ve tried to share pieces of this power in a variety of different vi tutorials. (Just search this blog for “vi” or “vi editor” and you’ll find a wealth of vi tutorials.)

One last note about the vi last line mode: If you’re in last line mode, and you want to switch back to command mode, there are several different ways to do this. For consistency, one way to do this is to press the [Esc] key twice, like this:

[Esc][Esc]

(This is consistent with the method of moving from insert mode back to command mode, except you have to press the [Esc] key twice.)

A second way is to press the [Backspace] key until anything you typed and the initial “:” character are gone. At this point you’ll be back in command mode.

Finally, if you haven’t typed anything at all, and you’re just looking at the “:” prompt on the last line, you can just press [Enter], and you’ll be placed back in vi command mode.

After opening a new OR existing file in vim editor with vim you can try modes listed here.

Insert mod : lets you insert text in a document. shotcurt : “i” (insert where the cursor is) or “o” (insert at the beginning of the following line).

Visual mod : permits to select the text like you would do with a mouse but using the keyboard instead of the mouse. Useful to copy several lines for example. shotcurt : V

Let’s now speak about the command mode.

A command begins with the symbol “:”.

When you are in another mod you can use the escape key (sometimes you’ll need to hit it twice) to come back to command mod at any time.

save : :w
save and exit : :wq
exit : :q
force : ! (example :w! :q!)
vertical split : open a document and then type :vsplit /path-to-document/document and this will open the specified document and split the screen so you can see both documents.
copy : y
copy a line : yy
paste : p
cut : d
cut a line : dd

I repeat these are very basic commands for vim, but they are very useful, and I hope this will help you configuring your Linux.

How to disable users in linux/unix?

This how-to will show how to disable a user account under linux. This might be useful in the situation where you don’t want to permanently remove the user, but you just want it disabled and no longer able to use the system. The user will still receive emails for example, but he will not be able to login and check them out.

In latest linux systems /etc/shadow stores the encrypted user passwords. The quickest way to disable a user is to alter is password stored in /etc/shadow. Normally an active user account will have one line in /etc/shadow that will look like:

user:$1$eFd7EIOg$EeCk6XgKktWSUgi2pGUpk.:13852:0:99999:7:::

where the second field is the encrypted password. Note: Fields seperated by :

If we replace the password with “*” or “!” this will make the account unusable, and that means the user will not able to login on system any more:

user:*:13852:0:99999:7:::

This method has the disadvantage that the user password will be lost (unless saved somewhere, etc.) in the case we will want to re-enable it again later. From this point of view a much better method is to use the passwd command to lock the account:

passwd -l

and the output of the successful change will be “Password changed.”. This actually just changes the shadow file and adds “!” in front of the user password:

user:!$1$eFd7EIOg$EeCk6XgKktWSUgi2pGUpk.:13852:0:99999:7:::

Of course we could do this manually ourselves also if we want but its good to do through commands. There is a chance of human error if you try and edit shadow file by yourself.

If you will ever need to re-enable the account just unlock it:

passwd -u

or just remove manually the “!” character from the user’s password line in /etc/shadow.

Of course if you don’t need all this stuff and you just want to permanently remove the user just run:

userdel

this will keep user’s old files (home directory, mails, etc.) or to delete all his files on the system:

userdel -r

just be careful what is the home of the user before running this command as personally I have seen someone do this and erasing all the system… the user had set as home “/” .

Hope this helps.

How to change date and time in linux? date-time

Today I wanted to change date and time on one of my server quickly from command line. I am aware with command line options but every time I want to do it, I need to read man page or help to get perfect combination so i decided to write this small how to which can help me and you every time we want to change date and time in linux from command line.

Remember, CLI (command line interface) is most powerful for users / administrators like us.
To change system date in linux, type:-

#date MMDDhhmmYYYY.ss

For example: i want to change my system date to Dec 25 2009, 5.30pm, I will type:-
#date 122517302009.00

It’s simple, Isn’t it ?

Note:
MM – month
DD – day
YYYY – year
hh – hour is based on 24 hour
mm – minutes
ss – seconds

Hope this will help you.

How to search file(s) in linux / unix?

find is very famous and regularly used command to find files in the Linux/UNIX filesystem based on various different conditions. Let us review some practical examples of find command. All system administrators love this command, sometimes a life saver..
Syntax:

find [pathnames] [conditions]

How to find files containing a particular / specific word in its name?
The following command looks for all the files under /etc directory with cron
in the filename.

# find /etc -name "*cron*"

How to find all the files greater than certain size?
The following command will list all the files in the system greater than
10MB.

# find / -type f -size +10M

How to find files that are not modified in the last x number of days?
The following command will list all the files that were modified more than 30
days ago under the current directory.

# find . -mtime +30

How to find files that are modified in the last x number of days?
The following command will list all the files that were modified in the last
five days under the current directory.

# find . –mtime -5

How to delete all the archive files with extension *.tar.gz and greater than 50MB?
Please be careful while executing the following command as you don’t want
to delete the files by mistake. The best practice is to execute the same
command with ls –l to make sure you know which files will get deleted when
you execute the command with rm.

# find / -type f -name *.tar.gz -size +50M -exec ls -l {} \;
# find / -type f -name *.tar.gz -size +50M -exec rm -f {} \;

How to archive all the files that are not modified in the last x
number of days?

The following command finds all the files not modified in the last 30 days
under /home/nilesh directory and creates an archive files under /tmp in the
format of ddmmyyyy_archive.tar.

# find /home/nilesh -type f -mtime +30 | xargs tar -cvf
/tmp/`date '+%d%m%Y'_archive.tar`

On a side note, you can perform lot of file related activities (including finding
files) us

How to remove/purge binary logs in mysql? binlogs

By default, MySQL 5.x and above enables MySQL Binary Log. Keeping MySQL Binary Log will take up a lot of disk space for long run. Older MySQL Binary log can be removed in order to keep your hard disk space free.

MySQL Binary Log stores query event such as add, delete and update in a very details way. The Binary Log is used for two main purposes;

* 1. Replication between master and slave server, statement that has been made on Master server will later send it to slave server.
* 2. Recovery, certain recovery job required data stored in MySQL Binary Log.

MySQL Binary Log is located at the database’s root folder with the naming convention of mysql-bin.. Sample of the binary files as shown below;

Can I Remove MySQL Binary Log ?

Yes, as long as the data is replicated to Slave server, it’s safe to remove the file. It’s recommend only remove MySQL Binary Log older than 1 month.
Besides, if Recovery of data is the main concern, it’s recommend to archive MySQL Binary Log.
There are several ways to remove or clean up MySQL Binary Log, it’s not recommend to clean up the file manually, manually means running the remove command.
Remove MySQL Binary Log with RESET MASTER Statement
Reset Master statement is used for new database start up during replication for Master and Slave server. This statement can be used to remove all Binary Log.
To clean up Binary Log on Master Server

shell> mysql -u username -p
mysql> RESET MASTER;

To clean up Binary Log on Slave Server

mysql -u username -p
mysql> RESET SLAVE;

Remove MySQL Binary Log with PURGE BINARY LOGS Statement
PURGE BINARY LOGS statement can remove Binary Log based on date or up to a Binary Log sequence number
Base on the binary logs example shown above, I would like to remove binary up to mysql-bin.000015

shell> mysql -u username -p
mysql>PURGE BINARY LOGS TO 'mysql-bin.000015';

Alternatively, you can remove the binary older than a specific date.

shell> mysql -u username -p
mysql> PURGE BINARY LOGS BEFORE '2009-11-01 00:00:00';

Remove MySQL Binary Log with mysqladmin flush-logs Command
Another method is running mysqladmin flush-logs command, it will remove binary logs more than 3 days old.

shell> mysqladmin -u username -p flush-logs

Keep MySQL Binary Log for X Days

All of the methods above required monitoring on disk usage, to “rotate” and keep the binary logs for x number of day. The option below can be configured on MySQL’s config file, my.cnf

expire_logs_days = 7

Consider turning off MySQL Binary Log if MySQL Replication is not deploy on the database server and recovery is not the main concern.

I have additional 15GB if disk space now
Free disk space by purging mysql binary logs.
Are old mysql binary logs important ?
Can I remove old binary logs on mysql server?
Why my mysql server is creating these many binary logs ? How to remove them ?

How to recover mysql root password?

Don’t worry if you have forgotten / lost your mysql-server root (administrator) password.
You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password. you can add this option in /etc/my.cnf.
Step # 3: Connect to mysql server as the root user.
Step # 4: Setup new root password.Step # 5: Exit and restart MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 :

Stop mysql service
# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2:

Start to MySQL server w/o password:
# mysqld_safe --skip-grant-tables &

OR
Edit /etc/my.cnf and add skip-grant-tables

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-grant-tables

Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root

Output:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;

mysql> quit

Step # 5:

Stop MySQL Server:
# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+ Done mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server. Remove skip-grant-tables from /etc/my.cnf if you have added and test it

# /etc/init.d/mysql start
# mysql -u root -p

Errr. Help me, I forgot root password for mysql-server.
Quick how-to to recover / reset mysql-server root password.
Easy steps to recover / reset mysql root password.
Damn, Forgot mysql root / administrator password. can you help ?
How to reset mysql root password ?
How do I reset-recover MySQL root password under Linux, Debian, centos, fedora, suse, mandrake, linux-mint, Ubuntu, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?

How to Increase max_connections in mysql without restarting mysqld service? global-variable

By default in mysql database server max_connections is set to 100. This value indicates how many maximum concurrent connections mysql server can handle. If mysql reaches to it maximum (max) limit then you can see errors like “too many connections“. I assume that you have enough hardware resources (Mainly RAM )to handle more connections, here with this article I will share a TIP to increase max_connections in mysql.

As we know my.cnf is default configuration file for mysqld service and by default it is located in /etc directory unless and until you have changed it.

To find out how many max_connections are allowed currently on your mysql server use following command from mysql prompt.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 100                |
+-------------------+
1 row in set (0.00 sec)

max_connections is a GLOBAL variable. we can increase it on the fly without restarting mysqld service.
To do so use following command.

mysql> set global max_connections = 200;
Query OK, 0 rows affected (0.00 sec)

Now, If you check again you will see that limit of max_connections is increased.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 200               |
+-------------------+
1 row in set (0.00 sec)

Note:
It is important that you edit your /etc/my.cnf to add max_connections = 200 otherwise when you restart mysqld service in future, It will complain again after it hits the old max_connections limit.
Mysql server is throwing “too many connections” error ?
How to increase number of concurrent mysql connections without restarting mysqld service?
errrr… mysql-server not allowing any more connections ?
Quick how to on increasing max_connections in mysql. restart not required?