Cloud SQL stored procedures

This section provides a list of stored procedures that are available on Cloud SQL instances.

A stored procedure is prepared SQL code that you can reuse. Cloud SQL has stored procedures that you can execute on your instances. To execute the stored procedure, you use the following command, where procedure_name is the name of the stored procedure:

CALL procedure_name(parameters);

Stored procedures for indexing on replicas

mysql.addSecondaryIdxOnReplica

Syntax

mysql.addSecondaryIdxOnReplica(IDXTYPE, IDXNAME, TABLENAME, IDXDEFINITION, IDXOPTION)

Description

Adds a secondary index on the database. This stored procedure is a wrapper for the CREATE INDEX DDL statement.

  • IDXTYPE – Type of index to create. For example, pass UNIQUE to create a unique index.
  • IDXNAME – Name of the index.
  • TABLENAME – Name of the table in the format of schema.name.
  • IDXDEFINITION – Definition of the index. Do not include outer parentheses.
  • IDXOPTION – Any additional options to pass on index creation. For example, in MySQL 8.0, an option could pass INVISIBLE for an invisible index.

mysql.dropSecondaryIdxOnReplica

Syntax

mysql.dropSecondaryIdxOnReplica(IDXNAME, TABLENAME, IDXOPTION)

Description

Drops a secondary index on the database. This stored procedure is a wrapper for the DROP INDEX DDL statement.

  • IDXNAME – Name of the index.
  • TABLENAME – Name of the table in the format of schema.name.
  • IDXOPTION – Any additional options to pass when dropping an index. For example, an algorithm option like INPLACE.

Stored procedures for replication management

The following Cloud SQL stored procedures are installed after the demoteMaster request is called. They are removed after calling promoteReplica.

Execute the stored procedures in the mysql client in the Cloud SQL instance, using the following command:

    mysql> call mysql.setupExternalSource('1.1.1.1', 3306, 'test_user', 
'test_password', 'mysql-bin-changelog.000001', 1, false, false);

For more information, see Using a custom import to set up replication from large external databases

mysql.stopReplication

Syntax

mysql.stopReplication()

Description

Stops the replication. This stored procedure is a wrapper around STOP SLAVE that stops both SQL and I/O threads.

  • No parameters.

mysql.resetMaster

Syntax

mysql.resetMaster()

Description

Resets all replication settings for the primary instance. This procedure is a wrapper around RESET MASTER that clears out the replication configuration.

  • No parameters.

mysql.setupExternalSource

Syntax

mysql.setupExternalSource(HOST, PORT, USER_NAME, USER_PASSWORD, SOURCE_LOG_NAME,
SOURCE_LOG_POS, USE_SSL, USE_SSL_CLIENT_AUTH)

Description

Sets up replication with binlog-enabled source representation instance. Note: The SSL parameters should be set on the source representation instance before invoking this stored procedure.

  • HOST – Source endpoint.
  • PORT – Source port.
  • USER_NAME – Source user.
  • USER_PASSWORD – Source user password.
  • SOURCE_LOG_NAME – Name of the binary log on the source database instance that contains the replication information.
  • SOURCE_LOG_POS – The location in the mysql_binary_log_file_name binary log at which replication starts reading the replication information.
  • USE_SSL – Whether to use SSL-based replication. If yes, you need to set the caCertificate field in the demoteMaster request.
  • USE_SSL_CLIENT_AUTH – Whether to use SSL client authentication. If yes, you need to set the clientKey and clientCertificates fields in the demoteMaster request.

mysql.skipTransactionWithGtid

Syntax

mysql.skipTransactionWithGtid(GTID_TO_SKIP)

Description

Sets the _gtid_purged_ MySQL global variable. This stored procedure is a wrapper around the SET GLOBAL gtid_purged MySQL command that sets the gtid_purged variable.

  • GTID_TO_SKIP – The GTID set value to configure.

mysql.setupExternalSourceAutoPosition

Syntax

mysql.setupExternalSourceAutoPosition(host, port,
USER_NAME, USER_PASSWORD,
MASTER_AUTO_POSITION, USE_SSL, USE_SSL_CLIENT_AUTH)

Description

Sets up replication with a GTID-enabled source instance.

  • HOST – Source endpoint.
  • PORT – Source port.
  • USER_NAME – Source user.
  • USER_PASSWORD – Source user password.
  • MASTER_AUTO_POSITION – Value of the master_auto_position parameter. For more information, see the CHANGE MASTER TO statement.
  • USE_SSL – Whether to use SSL-based replication. If yes, you need to set the caCertificate field in the demoteMaster request.
  • USE_SSL_CLIENT_AUTH – Whether to use SSL client authentication. If yes, you need to set the clientKey and clientCertificates fields in the demoteMaster request.

mysql.startReplication

Syntax

mysql.startReplication()

Description

Starts the replication. This stored procedure is a wrapper around START SLAVE that starts both SQL and I/O threads.

  • No parameters.

What's next