MySQL database migration from one server to another server.

MySQL database migration from one server to another server.

Connect to Testmysql01 using ssh

Take a dump of Test_database database

[root@Testmysql01 ~]# mysqldump -u root -p Test_database  > /opt/Test_database.sql

Copy the Dump file to Testmysql02

[root@Testmysql01 ~]# scp /opt/Test_database.sql root@Testmysql02.domain.com:/opt

[root@Testmysql01 ~]# Exit

Connect to Testmysql02 using ssh

connect to MySQL

[root@Testmysql02~]# MySQL -u root -p

Create Database and grant permission for the user

MySQL> create database Test_database;

MySQL> GRANT ALL ON Test_database.* TO testuser@'%' IDENTIFIED BY 'password';

MySQL> quit

Import Dump to new database

[root@Testmysql02~]# MySQL -u root -p Test_DBA < /opt/Test_DBA.sql

[root@Testmysql02~]# Exit

Connect to webserver using RDP.

Navigate to C:\inetpub\Test_database\Test_dat
abase\config

Edit file config.php and change 'dbhost' => in the file to Testmysql02.domain.com.

verify 'dbuser' => and 'dbpassword' => is defined correctly.

restart the site from IIS manager

connect to Test_database  site and verify everything is working fine.

Upgrade SQL Server 2014 Standard to Enterprise

Upgrade SQL Server 2014 Standard to Enterprise document..          Download

Script: list all Usernames, Roles for all the databases in SQL server

/**
Script: list all Usernames, Roles for all the databases.
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

**/
USE MASTER
GO
BEGIN
DECLARE @SQLVerNo INT;
SET @SQLVerNo = cast(substring(CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0,charindex('.',CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0)) as int);

IF @SQLVerNo >= 9 
    IF EXISTS (SELECT TOP 1 *
               FROM Tempdb.sys.objects (nolock)
               WHERE name LIKE '#TUser%')
        DROP TABLE #TUser
ELSE
IF @SQLVerNo = 8
BEGIN
    IF EXISTS (SELECT TOP 1 *
               FROM Tempdb.dbo.sysobjects (nolock)
               WHERE name LIKE '#TUser%')
        DROP TABLE #TUser
END

CREATE TABLE #TUser (
    ServerName    varchar(256),
    DBName        SYSNAME,
    [Name]        SYSNAME,
    GroupName     SYSNAME NULL,
    LoginName     SYSNAME NULL,
    default_database_name  SYSNAME NULL,
    default_schema_name    VARCHAR(256) NULL,
    Principal_id  INT,
    [sid]         VARBINARY(85))

IF @SQLVerNo = 8
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT 
   @@SERVERNAME,
   ''?'' as DBName,
   u.name As UserName,
   CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName,
   l.name AS LoginName,
   NULL AS Default_db_Name,
   NULL as default_Schema_name,
   u.uid,
   u.sid
FROM [?].dbo.sysUsers u
   LEFT JOIN ([?].dbo.sysMembers m 
   JOIN [?].dbo.sysUsers r
   ON m.groupuid = r.uid)
   ON m.memberuid = u.uid
   LEFT JOIN dbo.sysLogins l
   ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
   /*and u.name like ''tester''*/
ORDER BY u.name
'
END

ELSE 
IF @SQLVerNo >= 9
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT 
   @@SERVERNAME,
   ''?'',
   u.name,
   CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName,
   l.name LoginName,
   l.default_database_name,
   u.default_schema_name,
   u.principal_id,
   u.sid
FROM [?].sys.database_principals u
   LEFT JOIN ([?].sys.database_role_members m
   JOIN [?].sys.database_principals r 
   ON m.role_principal_id = r.principal_id)
   ON m.member_principal_id = u.principal_id
   LEFT JOIN [?].sys.server_principals l
   ON u.sid = l.sid
WHERE u.TYPE <> ''R''
   /*and u.name like ''tester''*/
order by u.name
'
END

SELECT *
FROM #TUser
ORDER BY DBName,
 [name],
 GroupName

DROP TABLE #TUser
END


/** end of script **/