Author: harpalgohilon

Password Less SSH Between Centos

Password Less SSH Connection between Two Centos Server

Description

Here I have explained password less ssh configuration between two centos server. Please find below steps to configure it. Password less ssh configuration require when you want to upload backup using ssh.

Scenario:

Server 1: Source Server
Operating System: Centos 6
IP Address: 10.0.0.1
Server 2: Destination Server
Operating System: Centos 6
IP Address: 20.0.0.1

Procedure

  • Login to Server1 using root user
  • create the public key and private key by following command
# ssh-keygen -t rsa
  • It will ask for passphrase just press enter if you do not want to set any passphrase
  • After creating these keys you have to copy to the remote host [Server2] which you want to connect without password
  • Login to Server2 remote host.
  • Check .ssh directory is there or not by locate .ssh command
  • If .ssh directory is not there than create the directory by mkdir /root/.ssh
  • Give permission to .ssh directory using below command
# chmod 700 .ssh
  • Restart ssh service in Server2 using below command
# /etc/init.d/ssh restart
  • Come back to  Server1 and copy the key to the remote server [Server2] by following command
# cat id_rsa.pub | ssh -p 2220 root@20.0.0.1 ‘cat >>.ssh/authorized_keys’
Note: – You have to enter this command if port is different from 22 otherwise you can just follow the simple command
# ssh-copy-id  root@20.0.0.1
  • After that you can check the ssh login to the remote server [Server2]
# ssh –p 2220 root@20.0.0.1
  • It will not prompt you for the password and directly you will get the prompt.

Password Less SSH Between Centos

Password Less SSH Connection between Two Centos Server

Description

Here I have explained password less ssh configuration between two centos server. Please find below steps to configure it. Password less ssh configuration require when you want to upload backup using ssh.

Scenario:

Server 1: Source Server
Operating System: Centos 6
IP Address: 10.0.0.1
Server 2: Destination Server
Operating System: Centos 6
IP Address: 20.0.0.1

Procedure

  • Login to Server1 using root user
  • create the public key and private key by following command
# ssh-keygen -t rsa
  • It will ask for passphrase just press enter if you do not want to set any passphrase
  • After creating these keys you have to copy to the remote host [Server2] which you want to connect without password
  • Login to Server2 remote host.
  • Check .ssh directory is there or not by locate .ssh command
  • If .ssh directory is not there than create the directory by mkdir /root/.ssh
  • Give permission to .ssh directory using below command
# chmod 700 .ssh
  • Restart ssh service in Server2 using below command
# /etc/init.d/ssh restart
  • Come back to  Server1 and copy the key to the remote server [Server2] by following command
# cat id_rsa.pub | ssh -p 2220 root@20.0.0.1 ‘cat >>.ssh/authorized_keys’
Note: – You have to enter this command if port is different from 22 otherwise you can just follow the simple command
# ssh-copy-id  root@20.0.0.1
  • After that you can check the ssh login to the remote server [Server2]
# ssh –p 2220 root@20.0.0.1
  • It will not prompt you for the password and directly you will get the prompt.

Backup of MSSQL Database in Express Edition

Backup of databases in SQL_EXPRESS Edition

As we all know there is no inbuild functionality for backup in like available in standard and Enterprise Edition. Please find some different methods for Backup

Method-1
     1>  Create one stored procedure using below query  that use to take backup
[Stored Procedure Name =sp_BackupDatabases]
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: Backup Databases for SQLExpress
— Parameter1: databaseName
— Parameter2: backupType F=full, D=differential, L=log
— Parameter3: backup file location
— =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases] 
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200)
AS
       SET NOCOUNT ON;
          
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
          
             — Pick out only databases which are online in case ALL databases are chosen to be backed up
             — If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
          
            — Filter out databases which do not need to backed up
            IF @backupType=’F’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’AdventureWorks’)
                  END
            ELSE IF @backupType=’D’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
                  END
            ELSE IF @backupType=’L’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
          
            — Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000)
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                 
                      
            — Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
      WHILE @Loop IS NOT NULL
      BEGIN
— Database Names have to be in [dbname] format since some have – or _ in their name
      SET @DBNAME = ‘[‘+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+’]’
— Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),’/’,”) + ‘_’ +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”) 
— Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = ‘F’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_FULL_’+ @dateTime+ ‘.BAK’
      ELSE IF @backupType = ‘D’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_DIFF_’+ @dateTime+ ‘.BAK’
      ELSE IF @backupType = ‘L’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_LOG_’+ @dateTime+ ‘.TRN’
— Provide the backup a name for storing in the media
      IF @backupType = ‘F’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ full backup for ‘+ @dateTime
      IF @backupType = ‘D’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ differential backup for ‘+ @dateTime
      IF @backupType = ‘L’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ log backup for ‘+ @dateTime
— Generate the dynamic SQL command to be executed
       IF @backupType = ‘F’
                  BEGIN
               SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’
                  END
       IF @backupType = ‘D’
                  BEGIN
               SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH DIFFERENTIAL, INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’       
                  END
       IF @backupType = ‘L’
                  BEGIN
               SET @sqlCommand = ‘BACKUP LOG ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’       
                  END
— Execute the generated SQL command
       EXEC(@sqlCommand)
— Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 END
          2>  Open command prompt and run below command for take backup in one folder
C:\>sqlcmd -S .\SQL2012 -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackup\’, @backupType=’D'”
Method -2
1>  Create Folder in D:\SQL_Backup\scripts
2>  Create the TSQL script
DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)
–month variable
IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
   SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))
ELSE
   SET @monthSTR= ‘0’ + CAST(MONTH(GETDATE()) AS CHAR(2))
–day variable
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
   SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))
ELSE
   SET @daySTR=’0′ + CAST(DAY(GETDATE()) AS CHAR(2))
–hour variable
IF (SELECT LEN(DATEPART(hh, GETDATE())))=2
   SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
ELSE
   SET @hourStr= ‘0’ + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
–minute variable
IF (SELECT LEN(DATEPART(mi, GETDATE())))=2
   SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
ELSE
   SET @minStr= ‘0’ + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
–name variable based on time stamp
SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr
–=================================================================
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN (‘TEMPDB’)
WHILE @IDENT IS NOT NULL
BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
/*Change disk location here as required*/
   SELECT @SQL = ‘BACKUP DATABASE ‘+@DBNAME+’ TO DISK = ”D:\SQLBackup\’+@DBNAME+’_db_’ + @dateString +’.BAK” WITH INIT’
   EXEC (@SQL)
   `>@IDENT AND NAME NOT IN (‘TEMPDB’)
END
Save above file as BackupDB.sql in D:\SQL_Backup\scripts folder
Run Below command in command prompt :
sqlcmd -S.\SQL2012 -E -i”D:\scripts\SqlBakup.sql”
Note:  -S sql instance Name
3>  Create VB Script file for remove database older than 3 days save as D:\SQL_Backup\scripts\deleteBAK.vbs
On Error Resume Next
Dim fso, folder, files, sFolder, sFolderTarget
Set fso = CreateObject(“Scripting.FileSystemObject”)

‘location of the database backup files
sFolder = “E:\SQL_Backup\”

Set folder = fso.GetFolder(sFolder)
Set files = folder.Files

‘used for writing to textfile – generate report on database backups deleted
Const ForAppending = 8

‘you need to create a folder named “scripts” for ease of file management &
‘a file inside it named “LOG.txt” for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & “\scripts\LOG.txt”, ForAppending)

objFile.Write “================================================================” & VBCRLF & VBCRLF
objFile.Write ”                     DATABASE BACKUP FILE REPORT                ” & VBCRLF
objFile.Write ”                     DATE:  ” &    FormatDateTime(Now(),1)   & “” & VBCRLF
objFile.Write ”                     TIME:  ” &    FormatDateTime(Now(),3)   & “” & VBCRLF & VBCRLF
objFile.Write “================================================================” & VBCRLF

‘iterate thru each of the files in the database backup folder
For Each itemFiles In files
   ‘retrieve complete path of file for the DeleteFile method and to extract
        ‘file extension using the GetExtensionName method
   a=sFolder & itemFiles.Name

   ‘retrieve file extension
   b = fso.GetExtensionName(a)
       ‘check if the file extension is BAK
       If uCase(b)=”BAK” Then

           ‘check if the database backups are older than 3 days
           If DateDiff(“d”,itemFiles.DateCreated,Now()) >= 3 Then

               ‘Delete any old BACKUP files to cleanup folder
               fso.DeleteFile a
               objFile.WriteLine “BACKUP FILE DELETED: ” & a
           End If
       End If
Next

objFile.WriteLine “================================================================” & VBCRLF & VBCRLF

objFile.Close

Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing 

4>   Create the batch file that will call the TSQL script and the VBScript file
D:\SQL_Backup\scripts\databaseBackup.cmd
REM Run TSQL Script to backup databases
sqlcmd -S-E -i”E:\SQL_Backup\scripts\backupDB.sql”
REM Run database backup cleanup script
E:\SQL_Backup\scripts\deleteBAK.vbs

5>  Create a task in Windows Task Scheduler
Create a daily task in Windows Task Scheduler that will call the batch file created in the previous step. This can be found in the Control Panel -> Scheduled Tasks or under Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks.
Since we are using Windows authentication to run the TSQL script, use a Windows account that is a member of the db_backupoperator role of all the databases
  • Launch “Scheduled Tasks”
  • Click on Add Scheduled Task
  • Browse to the “E:\SQL_Backup\scripts” folder and select databaseBackup.cmd
  • Pick the frequency and time for the backups to run
  • Lastly, enter a Windows account that has at least db_backupoperator role privileges for all of the databases
Method-3
1>  Create T-Sql Script for backup as follow:
———————————————————DB_Name : Offline_DB——————————————————————————
DECLARE @pathName NVARCHAR(512)
SET @pathName = ‘D:\SQLBackup\Offline_DB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [Offline_DB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’Offline_DB’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : MPOS_Offline_DB————————————————————————-
SET @pathName = ‘D:\SQLBackup\MPOS_Offline_DB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [MPOS_Offline_DB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : ReportServer$SQL2012——————————————————————–
SET @pathName = ‘D:\SQLBackup\ReportServer$SQL2012_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [ReportServer$SQL2012] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : ReportServer$SQL2012——————————————————————–
SET @pathName = ‘D:\SQLBackup\ReportServer$SQL2012TempDB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [ReportServer$SQL2012TempDB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
2>  Save as BackupDB.sql and save it on D:\Scripts
3>  To Run BackupDB.sql file run below command to check whether it was working or not
Open cmd – Aa a Administrator
sqlcmd -S .\SQL2012 -E -i”D:\scripts\SQLbackup_Newsql.sql”
Note: database backup path define in .sql script
4>  If you want to compress backup then create batch file in D:\scripts folder also install 7Zip application. Copy 7z.exe in D:\scripts
5>  Create cmd or bat file as follow:
@echo Take Backup of databases
sqlcmd -S .\SQL2012 -E -i”D:\scripts\SQLbackup_Newsql.sql”
@echo Compress old databses
D:\scripts\7z.exe a -tzip D:\SQLBackup\MPOS_Offline_DB_%date%.zip  D:\SQLBackup\MPOS_Offline_DB_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\Offline_DB_%date%.zip  D:\SQLBackup\Offline_DB_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\ReportServer$SQL2012_%date%.zip  D:\SQLBackup\ReportServer$SQL2012_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\ReportServer$SQL2012TempDB_%date%.zip  D:\SQLBackup\ReportServer$SQL2012TempDB_*.bak
@echo Remove Backupfiles
del D:\SQLBackup\MPOS_Offline_DB_*.bak
del D:\SQLBackup\Offline_DB_*.bak
del D:\SQLBackup\ReportServer$SQL2012_*.bak

del D:\SQLBackup\ReportServer$SQL2012TempDB_*.bak

Backup of MSSQL Database in Express Edition

Backup of databases in SQL_EXPRESS Edition

As we all know there is no inbuild functionality for backup in like available in standard and Enterprise Edition. Please find some different methods for Backup

Method-1
     1>  Create one stored procedure using below query  that use to take backup
[Stored Procedure Name =sp_BackupDatabases]
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: Backup Databases for SQLExpress
— Parameter1: databaseName
— Parameter2: backupType F=full, D=differential, L=log
— Parameter3: backup file location
— =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases] 
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200)
AS
       SET NOCOUNT ON;
          
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
          
             — Pick out only databases which are online in case ALL databases are chosen to be backed up
             — If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
          
            — Filter out databases which do not need to backed up
            IF @backupType=’F’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’AdventureWorks’)
                  END
            ELSE IF @backupType=’D’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
                  END
            ELSE IF @backupType=’L’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
          
            — Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000)
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                 
                      
            — Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
      WHILE @Loop IS NOT NULL
      BEGIN
— Database Names have to be in [dbname] format since some have – or _ in their name
      SET @DBNAME = ‘[‘+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+’]’
— Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),’/’,”) + ‘_’ +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”) 
— Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = ‘F’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_FULL_’+ @dateTime+ ‘.BAK’
      ELSE IF @backupType = ‘D’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_DIFF_’+ @dateTime+ ‘.BAK’
      ELSE IF @backupType = ‘L’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_LOG_’+ @dateTime+ ‘.TRN’
— Provide the backup a name for storing in the media
      IF @backupType = ‘F’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ full backup for ‘+ @dateTime
      IF @backupType = ‘D’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ differential backup for ‘+ @dateTime
      IF @backupType = ‘L’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ log backup for ‘+ @dateTime
— Generate the dynamic SQL command to be executed
       IF @backupType = ‘F’
                  BEGIN
               SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’
                  END
       IF @backupType = ‘D’
                  BEGIN
               SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH DIFFERENTIAL, INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’       
                  END
       IF @backupType = ‘L’
                  BEGIN
               SET @sqlCommand = ‘BACKUP LOG ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’       
                  END
— Execute the generated SQL command
       EXEC(@sqlCommand)
— Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 END
          2>  Open command prompt and run below command for take backup in one folder
C:\>sqlcmd -S .\SQL2012 -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackup\’, @backupType=’D'”
Method -2
1>  Create Folder in D:\SQL_Backup\scripts
2>  Create the TSQL script
DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)
–month variable
IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
   SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))
ELSE
   SET @monthSTR= ‘0’ + CAST(MONTH(GETDATE()) AS CHAR(2))
–day variable
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
   SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))
ELSE
   SET @daySTR=’0′ + CAST(DAY(GETDATE()) AS CHAR(2))
–hour variable
IF (SELECT LEN(DATEPART(hh, GETDATE())))=2
   SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
ELSE
   SET @hourStr= ‘0’ + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
–minute variable
IF (SELECT LEN(DATEPART(mi, GETDATE())))=2
   SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
ELSE
   SET @minStr= ‘0’ + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
–name variable based on time stamp
SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr
–=================================================================
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN (‘TEMPDB’)
WHILE @IDENT IS NOT NULL
BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
/*Change disk location here as required*/
   SELECT @SQL = ‘BACKUP DATABASE ‘+@DBNAME+’ TO DISK = ”D:\SQLBackup\’+@DBNAME+’_db_’ + @dateString +’.BAK” WITH INIT’
   EXEC (@SQL)
   `>@IDENT AND NAME NOT IN (‘TEMPDB’)
END
Save above file as BackupDB.sql in D:\SQL_Backup\scripts folder
Run Below command in command prompt :
sqlcmd -S.\SQL2012 -E -i”D:\scripts\SqlBakup.sql”
Note:  -S sql instance Name
3>  Create VB Script file for remove database older than 3 days save as D:\SQL_Backup\scripts\deleteBAK.vbs
On Error Resume Next
Dim fso, folder, files, sFolder, sFolderTarget
Set fso = CreateObject(“Scripting.FileSystemObject”)

‘location of the database backup files
sFolder = “E:\SQL_Backup\”

Set folder = fso.GetFolder(sFolder)
Set files = folder.Files

‘used for writing to textfile – generate report on database backups deleted
Const ForAppending = 8

‘you need to create a folder named “scripts” for ease of file management &
‘a file inside it named “LOG.txt” for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & “\scripts\LOG.txt”, ForAppending)

objFile.Write “================================================================” & VBCRLF & VBCRLF
objFile.Write ”                     DATABASE BACKUP FILE REPORT                ” & VBCRLF
objFile.Write ”                     DATE:  ” &    FormatDateTime(Now(),1)   & “” & VBCRLF
objFile.Write ”                     TIME:  ” &    FormatDateTime(Now(),3)   & “” & VBCRLF & VBCRLF
objFile.Write “================================================================” & VBCRLF

‘iterate thru each of the files in the database backup folder
For Each itemFiles In files
   ‘retrieve complete path of file for the DeleteFile method and to extract
        ‘file extension using the GetExtensionName method
   a=sFolder & itemFiles.Name

   ‘retrieve file extension
   b = fso.GetExtensionName(a)
       ‘check if the file extension is BAK
       If uCase(b)=”BAK” Then

           ‘check if the database backups are older than 3 days
           If DateDiff(“d”,itemFiles.DateCreated,Now()) >= 3 Then

               ‘Delete any old BACKUP files to cleanup folder
               fso.DeleteFile a
               objFile.WriteLine “BACKUP FILE DELETED: ” & a
           End If
       End If
Next

objFile.WriteLine “================================================================” & VBCRLF & VBCRLF

objFile.Close

Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing 

4>   Create the batch file that will call the TSQL script and the VBScript file
D:\SQL_Backup\scripts\databaseBackup.cmd
REM Run TSQL Script to backup databases
sqlcmd -S-E -i”E:\SQL_Backup\scripts\backupDB.sql”
REM Run database backup cleanup script
E:\SQL_Backup\scripts\deleteBAK.vbs

5>  Create a task in Windows Task Scheduler
Create a daily task in Windows Task Scheduler that will call the batch file created in the previous step. This can be found in the Control Panel -> Scheduled Tasks or under Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks.
Since we are using Windows authentication to run the TSQL script, use a Windows account that is a member of the db_backupoperator role of all the databases
  • Launch “Scheduled Tasks”
  • Click on Add Scheduled Task
  • Browse to the “E:\SQL_Backup\scripts” folder and select databaseBackup.cmd
  • Pick the frequency and time for the backups to run
  • Lastly, enter a Windows account that has at least db_backupoperator role privileges for all of the databases
Method-3
1>  Create T-Sql Script for backup as follow:
———————————————————DB_Name : Offline_DB——————————————————————————
DECLARE @pathName NVARCHAR(512)
SET @pathName = ‘D:\SQLBackup\Offline_DB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [Offline_DB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’Offline_DB’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : MPOS_Offline_DB————————————————————————-
SET @pathName = ‘D:\SQLBackup\MPOS_Offline_DB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [MPOS_Offline_DB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : ReportServer$SQL2012——————————————————————–
SET @pathName = ‘D:\SQLBackup\ReportServer$SQL2012_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [ReportServer$SQL2012] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : ReportServer$SQL2012——————————————————————–
SET @pathName = ‘D:\SQLBackup\ReportServer$SQL2012TempDB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [ReportServer$SQL2012TempDB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
2>  Save as BackupDB.sql and save it on D:\Scripts
3>  To Run BackupDB.sql file run below command to check whether it was working or not
Open cmd – Aa a Administrator
sqlcmd -S .\SQL2012 -E -i”D:\scripts\SQLbackup_Newsql.sql”
Note: database backup path define in .sql script
4>  If you want to compress backup then create batch file in D:\scripts folder also install 7Zip application. Copy 7z.exe in D:\scripts
5>  Create cmd or bat file as follow:
@echo Take Backup of databases
sqlcmd -S .\SQL2012 -E -i”D:\scripts\SQLbackup_Newsql.sql”
@echo Compress old databses
D:\scripts\7z.exe a -tzip D:\SQLBackup\MPOS_Offline_DB_%date%.zip  D:\SQLBackup\MPOS_Offline_DB_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\Offline_DB_%date%.zip  D:\SQLBackup\Offline_DB_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\ReportServer$SQL2012_%date%.zip  D:\SQLBackup\ReportServer$SQL2012_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\ReportServer$SQL2012TempDB_%date%.zip  D:\SQLBackup\ReportServer$SQL2012TempDB_*.bak
@echo Remove Backupfiles
del D:\SQLBackup\MPOS_Offline_DB_*.bak
del D:\SQLBackup\Offline_DB_*.bak
del D:\SQLBackup\ReportServer$SQL2012_*.bak

del D:\SQLBackup\ReportServer$SQL2012TempDB_*.bak

MySql Basic Commands

Mysql Commands

1>    Login MySQL Console

# [mysql dir]mysql  -u username -p

2>    Create a database on the sql server.

mysql> create database [databasename];

3>    List all databases on the sql server.

mysql> show databases;

4>    Switch to a database.

mysql> use [db name];

5>    To see all the tables in the db.

mysql> show tables;

6>    To see database’s field formats.

mysql> describe [table name];

7>    To delete a db.

mysql> drop database [database name];

8>    To delete a table.

mysql> drop table [table name];

9>    Show all data in a table.

mysql> SELECT * FROM [table name];

10>Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

11>Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

12>Show all records containing the name “Bob” AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ’3444444′;

13>Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ’3444444′ order by phone_number;

14>Show all records starting with the letters ‘bob’ AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′;

15>Show all records starting with the letters ‘bob’ AND the phone number ’3444444′ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′ limit 1,5;

16>Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

17>Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

18>Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

19>Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

20>Sum column.

mysql> SELECT SUM(*) FROM [table name];

21>Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

22>Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update password.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

23>Change a user’s password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

24>Change a user’s password from MySQL prompt. Login as root. Set the password. Update password.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

25>Set a root password if there is on root password.

# mysqladmin -u root password newpassword

26>Update a root password.

# mysqladmin -u root -p oldpassword newpassword

27>Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

28>Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privileges. Update privileges.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

29>To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

30>Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

31>Update database permissions/privilages.

mysql> flush privileges;

32>Delete a column.

mysql> alter table [table name] drop column [column name];

33>Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

34>Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

35>Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

36>Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

37>Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

38>Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

39>Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

40>Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

41>Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

42>Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

43>Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

44>Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

45>Reset the admin password of any tables :

#mysql -uDATABASENAME -pPASSWORD
mysql> use DATABASENAME;

mysql>UPDATE `TABLENAME` SET `PASSWORD-FILED-NAME` = MD5( 'admin' ) WHERE `USER-FIELD-NAME`.`user_name` = "admin" ;

Error Log and Slow Query Log in MySQL

Error log and Slow query log in MySQL
      
      1>    Error Log:
   ·         For Enable logs need to change in my.cnf file as follow:
      root@server# vi  /etc/mysql/my.cnf
                Or
      root@server# vi  /etc/my.cnf
   ·         Define error log with log-error in both [mysqld_safe] and [mysqld] section in my.cnf file
          [mysqld_safe]
          log-error=/var/log/mysql/error.log
          [mysqld]
          log-error=/var/log/mysql/error.log
   ·         Save file and Restart mysql service
      2>    Slow Query Log:
    ·     First thing need to check whether “Slow Query Log” is enable or not. For that access MySql and try  to execute following command.
         root@server# mysql –u root –p
         password:
         mysql> show variables like ‘%slow%’;
+———————+———————————+
| Variable_name       | Value                           |
+———————+———————————+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+———————+———————————+
·   The command result shows slow query log is currently disabled in the server.  You have add the following entries in the “/etc/mysql/my.cnf” file in-order to enable “slow query log”.  Place the entries below the section “mysqld”
[mysqld]
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2
·         Check again whether the “slow query log” is enabled.
   mysql> show variables like ‘%slow%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| log_slow_queries | ON    |
| slow_launch_time | 2     |
+——————+——-+

MySql Basic Commands

Mysql Commands

1>    Login MySQL Console

# [mysql dir]mysql  -u username -p

2>    Create a database on the sql server.

mysql> create database [databasename];

3>    List all databases on the sql server.

mysql> show databases;

4>    Switch to a database.

mysql> use [db name];

5>    To see all the tables in the db.

mysql> show tables;

6>    To see database’s field formats.

mysql> describe [table name];

7>    To delete a db.

mysql> drop database [database name];

8>    To delete a table.

mysql> drop table [table name];

9>    Show all data in a table.

mysql> SELECT * FROM [table name];

10>Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

11>Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

12>Show all records containing the name “Bob” AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ’3444444′;

13>Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ’3444444′ order by phone_number;

14>Show all records starting with the letters ‘bob’ AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′;

15>Show all records starting with the letters ‘bob’ AND the phone number ’3444444′ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′ limit 1,5;

16>Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

17>Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

18>Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

19>Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

20>Sum column.

mysql> SELECT SUM(*) FROM [table name];

21>Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

22>Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update password.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

23>Change a user’s password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

24>Change a user’s password from MySQL prompt. Login as root. Set the password. Update password.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

25>Set a root password if there is on root password.

# mysqladmin -u root password newpassword

26>Update a root password.

# mysqladmin -u root -p oldpassword newpassword

27>Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

28>Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privileges. Update privileges.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

29>To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

30>Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

31>Update database permissions/privilages.

mysql> flush privileges;

32>Delete a column.

mysql> alter table [table name] drop column [column name];

33>Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

34>Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

35>Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

36>Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

37>Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

38>Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

39>Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

40>Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

41>Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

42>Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

43>Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

44>Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

45>Reset the admin password of any tables :

#mysql -uDATABASENAME -pPASSWORD
mysql> use DATABASENAME;

mysql>UPDATE `TABLENAME` SET `PASSWORD-FILED-NAME` = MD5( 'admin' ) WHERE `USER-FIELD-NAME`.`user_name` = "admin" ;

Error Log and Slow Query Log in MySQL

Error log and Slow query log in MySQL
      
      1>    Error Log:
   ·         For Enable logs need to change in my.cnf file as follow:
      root@server# vi  /etc/mysql/my.cnf
                Or
      root@server# vi  /etc/my.cnf
   ·         Define error log with log-error in both [mysqld_safe] and [mysqld] section in my.cnf file
          [mysqld_safe]
          log-error=/var/log/mysql/error.log
          [mysqld]
          log-error=/var/log/mysql/error.log
   ·         Save file and Restart mysql service
      2>    Slow Query Log:
    ·     First thing need to check whether “Slow Query Log” is enable or not. For that access MySql and try  to execute following command.
         root@server# mysql –u root –p
         password:
         mysql> show variables like ‘%slow%’;
+———————+———————————+
| Variable_name       | Value                           |
+———————+———————————+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+———————+———————————+
·   The command result shows slow query log is currently disabled in the server.  You have add the following entries in the “/etc/mysql/my.cnf” file in-order to enable “slow query log”.  Place the entries below the section “mysqld”
[mysqld]
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2
·         Check again whether the “slow query log” is enabled.
   mysql> show variables like ‘%slow%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| log_slow_queries | ON    |
| slow_launch_time | 2     |
+——————+——-+

Backup and Restore in MySQL

Backup and Restore MySQL Database
·        Mysql include mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT intosql-statements of the source database. To restore the database, execute the *.sql file on destination database.
Backup and Restore command line syntax
·        backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
·        restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
   1>    Backup a single database:
          # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
   2>    Backup multiple database:
               # mysqldump -u root -p[root_password] [database_name] [database_name_2] > dumpfilename.sql
   3>    Verify the dumpfilename.sql dump file contains both the database backup
         # grep -i “Current database:” /tmp/dumpfilename.sql
           Current Database: `database_name`
           Current Database: `database_name_2`
   4>    Backup all the databases:
         # mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql
   5>    Backup a specific table:
         # mysqldump -u root -ptmppassword database1 accounts_contacts /tmp/database1_accounts_contacts.sql
    6>    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
How to Restore MySQL database
    1>    Restore a database: When you are restoring the dumpfilename.sql on a remote database, make         sure to create the database before you can perform the restore.
# mysql -u root –ptmppassword
# mysql> create database database_name;
# mysql -u root -ptmppassword database_name < /tmp/dumpfilename.sql
    2>    Backup a local database and restore to remote server using single command: if you want to         create read only database in remote computer. For that you need to create database in remote               computer and then run below command.
[local-server]# mysqldump -u root -ptmppassword database_name  | mysql  \   -u root -ptmppassword –host=remote-server -C database_name1

Backup and Restore in MySQL

Backup and Restore MySQL Database
·        Mysql include mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT intosql-statements of the source database. To restore the database, execute the *.sql file on destination database.
Backup and Restore command line syntax
·        backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
·        restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
   1>    Backup a single database:
          # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
   2>    Backup multiple database:
               # mysqldump -u root -p[root_password] [database_name] [database_name_2] > dumpfilename.sql
   3>    Verify the dumpfilename.sql dump file contains both the database backup
         # grep -i “Current database:” /tmp/dumpfilename.sql
           Current Database: `database_name`
           Current Database: `database_name_2`
   4>    Backup all the databases:
         # mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql
   5>    Backup a specific table:
         # mysqldump -u root -ptmppassword database1 accounts_contacts /tmp/database1_accounts_contacts.sql
    6>    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
How to Restore MySQL database
    1>    Restore a database: When you are restoring the dumpfilename.sql on a remote database, make         sure to create the database before you can perform the restore.
# mysql -u root –ptmppassword
# mysql> create database database_name;
# mysql -u root -ptmppassword database_name < /tmp/dumpfilename.sql
    2>    Backup a local database and restore to remote server using single command: if you want to         create read only database in remote computer. For that you need to create database in remote               computer and then run below command.
[local-server]# mysqldump -u root -ptmppassword database_name  | mysql  \   -u root -ptmppassword –host=remote-server -C database_name1