Enrique | SQL Conjuror

MySQL – Upgrading MySQL 5.5 to 5.7

By Enrique 04/01/2019 29/04/2020 MySQL, Database Administration

In this blog post, I will enumerate the steps to upgrade MySQL 5.5 to 5.7.

This is also known as the Logical Upgrade which involves exporting SQL from the old MySQL instance using a backup utility (mysqldump), installing the new MySQL server, and applying the SQL to your new MySQL instance.

1. Backup all your databases

 mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > /backup/data-for-upgrade.sql

Note: Use the --routines and --events options with mysqldump (as shown above) if your databases include stored programs. The --all-databases option includes all databases in the dump, including the mysqldatabase that holds the system tables.

2. Stop MySQL 5.5 instance

service mysqld stop

3. Move old datadir

mv /var/lib/mysql/ /var/lib/mysql-55

4. Install MySQL 5.7

5. Start MySQL

service mysqld start

6. Login to mysql with new root password

# Get temporary root password
grep 'temporary password' /var/log/mysqld.log

# Login to MySQL
mysql –u root –p

# Change root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password’;

7. Restore databases

mysql -u root -p < /backup/data-for-upgrade.sql

We have just performed the most cleanest and most stable upgrade path.

Cheers!

Facebook

Google

Twitter

No comments

ORACLE – Different Ways to Start and Stop Oracle RAC Instances

By Enrique 08/11/2018 29/04/2020 ORACLE, Database Administration

In this blog post, I will show you the different methods to stop and start an Oracle RAC instance and an Oracle RAC database.

1. Check the status of the database by using srvctl utility. (execute srvctl on unix prompt)

srvctl status database -d orclrac

2. Stop the instance instrac

srvctl stop instance -d orclrac -i instrac -o immediate

3. Verify that the instance instrac is down

# check out the status of instracin the output of the following command:
srvctl status database -d orclrac 

# check out the processes in the OS level
ps -ef | grep pmon

# connect to the database and verify to which instance the session is connected
sqlplus system/oracle@rac
SELECT INSTANCE_NAME FROM V$INSTANCE;

4. To stop two instances (e.g. instrac1, instrac2) in a single srvctl utility command.

srvctl stop instance -d rac -i instrac1,instrac2 -o immediate

5. Startup the database using the srvctl utility

Note: the startoption is optional and it defaults to open. With start command, it accepts the open, mount and nomout options.

srvctl stop instance -d rac -i instrac1,instrac2 -o immediate

6. Checkout the status of the database

srvctl status database -d orclrac

Oracle – Enabling Archivelog Mode in Oracle RAC

By Enrique 06/11/2018 29/04/2020 ORACLE, Database Administration, Uncategorized

Below are the steps to enable Archivelog Mode in Oracle RAC

# make sure orclrac database is running
srvctl status database -d orclrac

# stop the database
srvctl stop database -d orclrac -o immediate
srvctl status database -d orclrac

# start the database in mount state
srvctl start database -d orclrac -o mount

# using sqlplus, login as sysdba
sqlplus / as sysdba

# verify that the instances are in MOUNT state
SELECT INSTANCE_NAME,STATUS FROM GV$INSTANCE;

# verify that the database is operating in NOARCHIVE mode
ARCHIVE LOG LIST;

# define the destination of the archive log files
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=SPFILE;

# Note: because OMF is enabled, setting the
# LOG_ARCHIVE_FORMAT parameter has no effect.

# enable the archivelog mode
ALTER DATABASE ARCHIVELOG;

# restart the database
srvctl stop database -d orclrac
srvctl start database -d orclrac

# verify that the archivelog is enabled
sqlplus / as sysdba
archive log list

MySQL – Backup Stored Procedures, Functions and Triggers

By Enrique 31/10/2018 02/06/2021 Uncategorized

By default, mysqldump will backup all the triggers but NOT the stored procedures and functions.

There are 2 parameters that control this action:

--routines (FALSE by default)
--triggers (TRUE by default)

If you want to include the stored procedures and triggers, you need to add the –routines in your backup command as follows.

This command will backup the entire database including stored procedures.

mysqldump -u USERNAME -p --routines DBName > outputfile.sql

But if you wanna backup just the stored procedures and triggers (excluding table and data), use the following command.

mysqldump -u USERNAME -p --routines --no-create-info --no-data --no-create-db --skip-opt lm_cia > outputfile.sql

You can also put routines=true in the [mysqldump] section of your my.cnf

Cheers!

MySQL – Restore a Single Table from a FULL mysqldump file

By Enrique 24/08/2018 29/04/2020 MySQL, Database Administration

Use the sed command on your bash shell to separate the data of the table that you want to restore. For example, if we want to restore only the “film_actor” table to “sakila” database we execute the script below.


sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' mydump.sql > tabledump.sql

You can now import the newly created table dump file into MySQL database.

mysql -u root -p sakila < film_actor.sql

Cheers!

SQL Server – Get Database Owner Names via T-SQL

By Enrique 20/07/2018 29/04/2020 SQL SERVER, Database Administration, T-SQL

Below is a simple script to get the database owner names.

SELECT 
    name AS [Database Name], 
    suser_sname( owner_sid ) AS [Database Owner Name]
FROM 
    sys.databases

Cheers!

SQL Server – Get All Column Names from a Particular Table

By Enrique 11/07/2018 29/04/2020 SQL SERVER, Database Administration, T-SQL

There are two techniques to achieve this: 1) Using Sys Schema; 2) Using INFORMATION_SCHEMA.COLUMNS

Technique 1: Using Sys Schema

SELECT OBJECT_SCHEMA_NAME (c.object_id) SchemaName,
        o.Name AS Table_Name, 
        c.Name AS Field_Name,
        t.Name AS Data_Type,
        t.max_length AS Length_Size,
        t.precision AS Precision
FROM sys.columns c 
     INNER JOIN sys.objects o ON o.object_id = c.object_id
     LEFT JOIN  sys.types t on t.user_type_id  = c.user_type_id   
WHERE o.type = 'U'
-- and o.Name = N'TableName'
ORDER BY o.Name, c.Name

Technique 2: Using INFORMATION_SCHEMA.COLUMNS

SELECT *
FROM SVS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TableName'

Cheers!

SQL Server – How to Check if SSL is Enabled

By Enrique 10/07/2018 29/04/2020 SQL SERVER, Database Administration

Run the query below to immediately detect if SSL is configured on your SQL Server.

SELECT session_id, encrypt_option
FROM sys.dm_exec_connections

This will show the session_ids currently connected to SQL Server. If the value of encrypt_option is TRUE, then it is using a secured connection.

Cheers!

SQL Server – syspolicy_purge_history Job Failed to Run

By Enrique 27/06/2018 29/04/2020 SQL SERVER, Troubleshooting

I just installed an Enterprise Editon of SQL Server 2016 on Windows 2012 R2. I noticed the syspolicy_purge_history is failing.

Below is the error message. I observed the line set-ExecutionPolicy RemoteSigned -Scope process -Force

I tried to set the execution policy to RemoteSigned in Powershell as what was suggested in the error message but was not successful even when I run powershell as Administrator.

What is syspolicy_purge_history?

This job removes policies evaluation history. When a policy runs, it stores the results in MSDB. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.

CAUSE:

The ‘Execution Policy’ at the Machine Policy scope is currently set to Unrestricted. This may override the configuration at the ‘Process’ scope.

SOLUTION:

This issue may be resolved by setting the ‘ExecutionPolicy’ to ‘RemoteSigned‘. But I could not change the ‘ExecutionPolicy’ in Powershell. So what I did was to change it directly in the registry.