[Advanced ]Mysqldump, a database backup/restore utility

I will try to cover all possible methods to take backup and restore backup using mysqldump utility.


How to take database backup using mysqldump
mysqldump -u[username] -p[password] [database name] > dum_file_name.sql
e.g
mysqldump -uroot -ppassword mysql > mysql.sql

How to take backup of multiple databases at the same time

mysqldump -u[username] -p[password] [database 1 name]..[database 2 name]
e.g

mysql -uroot -ppassword DB1 DB2 DB3

How to take backup all the databases
mysqldump -u[username] -p[password] -all-databases > [dumpfile.sql]

How to take backup of specific table of a database

mysqldump -u[username] -p[password] [database name] [table Name] > dumpFIle.sql

e.g.

mysqldump -uroot -ppassword mysql user > user.sql

How to backup of table structures:

mysqldump -d -uroot -ppassword database01 > dumpfile.sql

How to restore backup
Database backup can be restored using mysqldump as well as mysql command.

mysql -u[user] -p[password] database name < dumpfile.sql

e.g.
mysql -uroot -ppassword mysql < mysql.sql

I hope you find this article helpful.

Suggestions are always welcome :)

500 OOPS: cannot locate user entry:nobody

While routine examination I saw a process running on server of user nobody. I thought this user is suspicious so I killed all the processes of this user (pkill -u nobody) and then deleted the user using command (userdel -r nobody) however delete command gave me error userdel: / not owned by nobody, not removing. Due to this people are unable to connect to my server through FTP and when they try they get error like 500 OOPS: cannot locate user entry:nobody.


How to Fix: Just all add "ftp_username=nobody" in /etc/vsftp.conf. Then restart vsftpd service (service vsftpd restart).

Enjoy :)

What are control files and how we can back them up

Control files [Oracle]
       
The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information:

    * Database information (RESETLOGS SCN and their time stamp)
    * Archive log history
    * Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
    * Redo threads (current online redo log)
    * Database's creation date
    * database name
    * current archive log mode
    * Log records (sequence numbers, SCN range in each log)
    * RMAN catalog
    * Database block corruption information
    * Database ID, which is unique to each DB

The location of the control files is specified through the control_files init param.
 

Backing up Controlfiles
When a controlfile (multiplexed or not) becomes unavailable (because for example of a media failure, the database cannot continue to operate.Because control files keep track of datafiles and the control file should be backed up whenever a change is made to the database (alter database).

The command to back the control file up is: alter database backup controlfile to 'filename' e.g

alter database backup controlfile to trace as '/home/oracle/controlfile.txt';.
 

Recovering database when controlfile is lost

 
If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated (with create controlfile whose syntax is a bit awkyard.
 

e.g.

create controlfile database name-of-database

In order to recover from a lost spfile, the DBID is required.

Size of control files
The size of the control files is governed by the following parameters:

    * maxlogfiles
    * maxlogmembers
    * maxloghistory
    * maxinstances
    * control_file_record_keep_time

Sections

 
The controlfile contains the following sections:

    * ARCHIVED LOG (reusable)
    * BACKUP CORRUPTION (reusable)
    * BACKUP DATAFILE (reusable)
    * BACKUP PIECE (reusable)
    * BACKUP REDOLOG (reusable)
    * BACKUP SET (reusable)
    * BACKUP SPFILE
    * CKPT PROGRESS
    * COPY CORRUPTION (reusable)
    * DATABASE
    * DATAFILE
    * DATAFILE COPY (reusable)
    * DATAFILE HISTORY
    * DATABASE INCARNATION
    * DELETED OBJECT (reusable)
    * FILENAME
    * FLASHBACK LOG
    * INSTANCE SPACE RESERVATION
    * LOG HISTORY (reusable)
    * MTTR
    * OFFLINE RANGE (reusable)
    * RECOVERY DESTINATION
    * REMOVABLE RECOVERY FILES
    * RMAN STATUS
    * RMAN CONFIGURATION
    * REDO THREAD
    * REDO LOG
    * TABLESPACE
    * TEMPORARY FILENAME
    * THREAD INSTANCE NAME MAPPING
    * PROXY COPY

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.
These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.
Misc
SQL*Loader's behaviour is specified with the SQL*Loader control file. Although they sound similar, they have nothing in common.
ASM simplifies the optimal layout of controlfiles.

How to resize or re-create online redo log files in oracle

In oracle 10g by default there are three groups having one online redo log file of 50 MB in each group. It is recommended to re-size them before going to use oracle 10g in production environment. In this article I will explain how to resize redo log files from 50 MB to 250 MB. Please follow the steps give below.

Step 1 SQL> select a.group#, a.member, b.bytes/1024/1024 mb from v$logfile a, v$log b where a.group# = b.group#;

GROUP#     MEMBER                                                       MB
3                   /u01/app/oracle/oradata/ORCL/redo03.log        50
2                  /u01/app/oracle/oradata/ORCL/redo02.log         50
1                 /u01/app/oracle/oradata/ORCL/redo01.log          50

This query will show current group with redo log members and their size.

Step 2 Make the last redo log CURRENT one

To find which group is current at this moment use following query

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                    CURRENT
2                    INACTIVE
3                    INACTIVE
Now as you can see that the first group is marked as current but we need to make group 3 as current. So force group 3 to become current one by switching log file. To switch log file use following query.

SQL> alter system switch logfile;

GROUP#         STATUS
---------- ----------------
1                     INACTIVE
2                    CURRENT
3                    INACTIVE

SQL> alter system switch logfile;

GROUP#        STATUS
---------- ----------------
1                     INACTIVE
2                     INACTIVE
3                     CURRENT

Step 3 Drop the first online redo log

After making the last online redo log file the CURRENT one, drop the first online redo log:

SQL> alter database drop logfile group 1;

Database altered.

Note:

As a DBA, you should already be aware that if you are going to drop a logfile group, it cannot be the current logfile group.However, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)

ORA-00312: online log 1 thread 1: ''

Easy problem to resolve. Simply perform a checkpoint on the database:

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Step 4 You need to re-create dropped online redo log group with different size. Use the following query to achieve this.

SQL> alter database add logfile group 1 (
2 '/u01/app/oracle/oradata/ORCL/redo01.log',
3 '/u01/app/oracle/oradata/ORCL/redo01.log') size 250m reuse;

Database altered.

Step 5 Force another log switch

After re-creating the online redo log group, force a log switch. The online redo log group just created should become the "CURRENT" group:

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                     UNUSED
2                     INACTIVE
3                     CURRENT

SQL> alter system switch logfile;

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                      CURRENT
2                      INACTIVE
3                      ACTIVE

Step 6 # Loop back to Step 3 until all logs are rebuilt

After re-creating an online redo log group, continue to re-create (or resize) all online redo log groups until all of them are rebuilt.

I hope this article helps you in recreating and resizing the redo log files.

Varius Commands

route -r
This command shows default gateway of the network. The row with UG flag is the default gateway of network

cat /proc/cpuinfo
This command shows CPU information

cat /proc/meminfo

This command show memory (RAM) info

finger

This command shows the IP address from where people have accessed the server.

How to disable Direct Root access on Linux server.

In this article I will show you how to disable direct root access on your Linux server. I will also a good idea to secure your server form hacker as hacker first has to login to server using ordinary (non-root) user and then gain root access (su -) from there. So hacker has to undergo two security checks. Please find below steps to disable direct root access.

Step 1

You must create a user to access the server. Let suppose I want to create user 'zaeem'. Following commands are used.

[root@Localhost]# groupadd zaeem

[root@Localhost]# useradd zaeem –gzaeem

[root@Localhost]# passwd zaeem

Enter password that you want to set against user 'zaeem'. So now you are done with user creation. You must test connecting to server using this user before root access is disabled because many people disable root access and either they forget to define user or defined user is unable to connect. Use following command to test the connection.

ssh zaeem@192.168.0.121

if it is successful then use following process to disable direct root access.

Step 2

open the file /etc/ssh/sshd_config and find line '#Protocol 2,1' so modify it to look like as 'Protocol 2'.

After that locate 'PermitRootLogin yes' so un-comment it and change it to look like 'PermitRootLogin no'. Save the file and restart sshd (service sshd restart)

That's it. Cheers!

How to change SSH port.

Running a Linux server on default SSH port 22 is risky as there may be some brute force attack. So it is always good idea to change default SSH port. It is very simple to change ssh port as you just need to edit sshd_config and reload the new configuration and you're done with it. Please find below steps in achieving that.

Step 1:
Edit sshd_config normally placed at /etc/ssh/. Open it and there is variable "Port 22" so change it with new desired port as "Port 2222"

Step 2
You've with the configuration so you just need to reload sshd service so that changes can take effect. Use following command to achieve that.

root@localhost> service sshd reload

I hope you find this article helpful.