How To Restore MySQL database

Restore a database

In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore.

# mysql -u root -ptmppassword

mysql> create database sugarcrm;
Query OK, 1 row affected (0.02 sec)

# mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

2. Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

Backup and Restore MySQL Database Using mysqldump

Backup a single database:

This example takes a backup of sugarcrm database and dumps the output to sugarcrm.sql

# mysqldump -u root -ptmppassword sugarcrm > sugarcrm.sql

# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

The sugarcrm.sql will contain drop table, create table and insert command for all the tables in the sugarcrm database. Following is a partial output of sugarcrm.sql, showing the dump information of accounts_contacts table:

Backup multiple databases:

If you want to backup multiple databases, first identify the databases that you want to backup using the show databases as shown below:

# mysql -u root -ptmppassword

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugs               |
| mysql              |
| sugarcr            |
+--------------------+
4 rows in set (0.00 sec)

For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below:

# mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

Verify the bugs_sugarcrm.sql dumpfile contains both the database backup.

# grep -i "Current database:" /tmp/bugs_sugarcrm.sql
-- Current Database: `mysql`
-- Current Database: `sugarcrm`

3. Backup all the databases:

The following example takes a backup of  all the database of the MySQL instance.

# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

Backup a specific table:

In this example, we backup only the accounts_contacts table from sugarcrm database.

# mysqldump -u root -ptmppassword sugarcrm accounts_contacts \
      > /tmp/sugarcrm_accounts_contacts.sql

4. Different mysqldump group options:

• –opt is a group option, which is same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. opt is enabled by default, disable with –skip-opt.
• –compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks

Oracle Database Control

Oracle Database Control

When Oracle starts it goes through 3 stages which are

Startup (nomount)	Oracle first reads the parameter file (pfile or spfile) and obtains all memory parameters in order to configure the SGA, memory allocatations take place and the necessary background processes are started. Remember the Memory allocation with the background processes is called the INSTANCE.
Mount	At this stage Oracle opens and reads the controlfile and obtains the location of the datafiles but it does not open them.
Open	The last stage is to open the datafiles (also called the DATABASE) for reading and writing, Oracle makes sure that the datafiles are consistent.

Startup

Nomount Mode	startup nomount; Note: The INSTANCE is born, memory allocated and background processes started but the controlfile has not been read and the datafiles are not open for reading or writing
Mount Mode	startup mount; Note: The controlfile has been read at this stage but the datafiles are not open for reading or writing.
Read Only Mode	startup open read only Note: the database files are open for reading only
Open Mode	startup startup open Note: database files will be open for reading and writing, this is the normal mode of operation.
Force mode	startup force; Note: this performs a 'shutdown abort' followed by a 'startup'
Restricted Mode	startup restrict; # To change into restricted mode alter system enable restricted session; alter system disable restricted session; Note: Users with restricted access can access the system only (normally DBA's)
Alter operation mode	# You must be in mount mode to alter the database, if not then shutdown the database first alter database open; alter database open read only; alter database restricted;
Display operation mode	# Display instance state in nomount mode v$database is unavailable. # MODES: #   started (nomount mode) #   mounted (mount mode) #   open (open mode) select status from v$instance; # Obtain the database operation mode, the database must be in mounted or open mode to access v$database # MODES: #   mounted (mount mode) #   read write (open mode) #   read only (open mode) select open_mode from v$database; # Is restricted session enabled select logins from v$instance;

Parameter File
As stated in the Oracle structure web page, Oracle can access two types of parameter file pfile (text based) or spfile (binary based), you can specify which parameter you want to start the database with

pfile	startup pfile='c:\oracle\pfiles\initD10.ora'
spfile	# There is no option to specify a spfile but you can use the pfile option and in the pfile # point to the spfile
Display the parameter file in use	select name, value from v$parameter where name = 'spfile'; show parameter spfile;

For more information on Oracle's parameter file see Oracle Structure.
Shutdown
Oracle has a number of options to shutdown the database, there are four to choose from

Normal	No new connections Will wait until all users have disconnected Note: you could be waiting a long time, not normally used
Transactional	No new connections or transactions Once all transactions have finished disconnect the client Note: again you could be waiting a long time
Immediate	No new connections Uncommitted transactins will be rolled back then disconnect the client Note: normal shutdown command used
Abort	No new connections Disconnect client immediately (no rolling back any transactions) Note: last resort shutdown command, a automatic recovery will take place when you start the database
Shutdown Commands
Shutdown commands	shutdown normal; shutdown transactional; shutdown immediate; shutdown abort; startup force; Note: 'startup force' will run a 'shutdown abort' immediately followed by a 'startup'
Display if shutdown is in progress	select shutdown_pending from v$instance;

Restricted and Quiesce Modes
When you have problems on an Oracle database sometimes you would like the database fully running but allow only privilege users access (normally DBA's), Oracle offers two modes

Restricted	In this mode any users without the restricted privilege will not be allowed to connect to the database, however any users that are still connected will be allowed to use any DML/DDL. Note: make sure all users are disconnected (need to kill their sessions) before changing the mode into restricted.
Quiesce	In this mode all users transactions are allowed to complete, once completed the user is unable to perform any DML/DDL commands (their sessions appear to freeze) but remain connected to the database. Users will be allowed to login but the session will again appear to freeze. DBA's can perform any DML/DDL as normal, their sessions are unaffected, this also applies to users with the restricted session privilege.
Useful Commands
Restricted	alter system enable restricted session; alter system disable restricted session; select logins from v$instance;
Grant restricted privilege	grant restricted session to <user>;
Quiesce	alter system quiesce restricted; alter system unquiesce; select active_state from v$instance;

Basic Database Information
To obtain general database information the following can be used

Display Parameter file	select name, value from v$parameter where name = 'spfile'; show parameter spfile;
Parameter settings	select name, value, isdefault, isses_modifiable, issys_modifiable from v$parameter;
Instance state	select status from v$instance;
Database operation mode	select open_mode from v$database;
Shutdown pending	select shutdown_pending from v$instance;
Restricted Session Mode	select logins from v$instance;
Quiesce Mode	select active_state from v$instance;
Oracle version	select * from v$version; select * from product_component_version;
Log Mode	select log_mode from v$database; archive log list;
License info	select * from v$license;
Database options	select * from v$option;
Memory Info	show sga select * from v$sga; select * from v$sgainfo; select * from v$ssgastat;
Display controlfiles	select * from v$controlfile; select * from v$controlfile_record_section;
Display Oracle processes	select * from v$bgprocesses;
Log file locations	# display background (alert.log destination), core and user destinations show parameter dump_dest;

 
 

Check Tablespace in Oracle

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Oracle Database 11g Release 1 (11.1) Installation

Download Software

Download the following software.

• Oracle Database 11g Release 1 (11.1) Software

Unpack Files

Unzip the files.

unzip linux_11gR1_database.zip

You should now have a single directory called "database" containing installation files.

Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

Set Kernel Parameters

Oracle recommend the following minimum parameter settings.

kernel.shmall = 2097152
kernel.shmmax = 2147483648 # Smallest of -> (Half the size of the physical memory) or (4GB - 1 byte)
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536 # 512 * PROCESSES
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=262144

The current values can be tested using the following command.

/sbin/sysctl -a | grep 

For Enterprise Linux 4.5, the following lines should be appended to the "/etc/sysctl.conf" file.

kernel.shmall = 2097152
kernel.shmmax = 2147483648 # Smallest of -> (Half the size of the physical memory) or (4GB - 1 byte)
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=262144

For Enterprise Linux 5.0, the following lines should be appended to the "/etc/sysctl.conf" file.

kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=262144

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the /etc/security/limits.conf file.

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536

Add the following line to the "/etc/pam.d/login" file, if it does not already exist.

session    required     pam_limits.so

Disable secure linux by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=disabled

Alternatively, this alteration can be done using the GUI tool (Applications > System Settings > Security Level). Click on the SELinux tab and disable the feature. If SELinux is disabled after installation, the server will need a reboot for the change to take effect.

Setup

Install the following packages for Enterprise Linux 4.5.

# From Enterprise Linux 4 Disk 1
cd /media/cdrom/Enterprise/RPMS
rpm -Uvh binutils-2.*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh libgcc-3.*
rpm -Uvh libstdc++-3.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh make-3.*
rpm -Uvh unixODBC-2.*
cd /
eject

# From Enterprise Linux 4 Disk 2
cd /media/cdrom/Enterprise/RPMS
rpm -Uvh glibc-devel-2.*
rpm -Uvh gcc-3.*
rpm -Uvh gcc-c++-3.*
rpm -Uvh libstdc++-devel-3.*
cd /
eject

# From Enterprise Linux 4 Disk 3
cd /media/cdrom/Enterprise/RPMS
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh sysstat-5.*
cd /
eject

# From Enterprise Linux 4 Disk 4
cd /media/cdrom/Enterprise/RPMS
rpm -Uvh elfutils-libelf-devel-0.*
rpm -Uvh unixODBC-devel-2.*
cd /
eject

Install the following packages for Enterprise Linux 5.0.

# From Enterprise Linux 5 Disk 1
cd /media/cdrom/Server
rpm -Uvh binutils-2.*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh libaio-0.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh make-3.*
cd /
eject

# From Enterprise Linux 5 Disk 2
cd /media/cdrom/Server
rpm -Uvh compat-libstdc++-33*
rpm -Uvh elfutils-libelf-devel-0.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
cd /
eject

# From Enterprise Linux 5 Disk 3
cd /media/cdrom/Server
rpm -Uvh sysstat-7.*
cd /
eject

Create the new groups and users.

groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin

useradd -g oinstall -G dba,oper,asmadmin oracle
passwd oracle

Note. We are not going to use the "asmadmin" group, since this installation will not use ASM.
Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/11.1.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Login as root and issue the following command.

xhost +

Login as the oracle user and add the following lines at the end of the ".bash_profile" file.

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=oel45.localdomain; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller