MySQL

If your server.log file has the following ERROR and the server will not start the MySQL server is configured for binary logging.

ERROR [com.oidev.bridgegate.db.SchemaMigratorUtil] java.sql.SQLException: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

To resolve this add the following to the my.cnf file under the [mysqld] section

log_bin_trust_function_creators=1

Use the following linux commands to move the mysql or mariadb database location from the default installation location to a new drive

Step 1 – Mount new drive (SSD preferred)

If you Instance doesn’t already have a SSD attached to the instance use the AWS console to add a new SSD drive to the instance.

Go to Volumes and Create a General Purpose SSD  then attach it to your instance

Run the following command to view the attached drives and locate the device name.  It should be the device that has no mount.  ie  no / or /data In this example xvdc is the new drive

lsblk

NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT

xvda    202:0    0   10G  0 disk └─xvda1 202:1    0   10G  0 part / xvdf    202:80   0  150G  0 disk /data xvdc    202:32   0  100G  0 disk

Format the drive (Only if it is a new drive)
sudo mkfs.ext4 -Tnews /dev/xvdc

Create the new root folder for the drive

sudo mkdir /edata

Get the UUID For the drive.  Copy the UUID and add the new mount to /etc/fstab

blkid /dev/xvdf

/dev/xvdf: UUID=”b5c25450-84da-40a9-907b-b34071a9407e” TYPE=”ext4″

# sudo vi /etc/fstab Add the line below to the end of the fstab.  Using the UUID for the new drive.

UUID=b5c25450-84da-40a9-907b-b34071a9407e /edata ext4 defaults,nofail 0 0

Test mounting the drive.  You should see the /edata as the mount point for your drive

sudo mount -a
lsblk

NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT

xvda    202:0    0   10G  0 disk └─xvda1 202:1    0   10G  0 part / xvdf    202:80   0  150G  0 disk /data xvdc    202:32   0  100G  0 disk /edata

 

Step 2 – Move MySQL DB

Use the following steps to move the MySQL DB from the default installation location to a new drive. Change /edata  to the new drive.

sudo systemctl stop mysql
sudo rsync -av /var/lib/mysql   /edata
sudo mv /var/lib/mysql /var/lib/mysql.bak

sudo vi /etc/my.cnf

Change datadir and any other variable that is referencing /var/lib/mysql

datadir=/edata/mysql socket=/edata/mysql/mysql.sock log-error=/edata/mysql/log/mariadb.log slow_query_log_file = /edata/mysql/log/slow-query.log

Add the OS Permissions for the new mysql location

sudo semanage fcontext -a -t mysqld_db_t "/edata/mysql(/.*)?"

sudo restorecon -Rv /edata/mysql

Start MySql

sudo systemctl start mysql

When BridgeGate is first started it check the connection to the database.  If you receive the following exception the version of MySQL has a parameter change.

[com.oidev.bridgegate.clustering.BridgeGateLoadBalancePolicy] checkDB() Error executing sql! java.sql.SQLNonTransientConnectionException: Cannot load connection class because of underlying exception: com.mysql.cj.exceptions.WrongArgumentException: The connection property ‘zeroDateTimeBehavior’ acceptable values are: ‘CONVERT_TO_NULL’, ‘EXCEPTION’ or ‘ROUND’. The value ‘convertToNull’ is not acceptable. at

Update the /bridgegate/conf/bridgegate.xml   Change zeroDateTimeBehavior=convertToNull  to zeroDateTimeBehavior=CONVERT_TO_NULL

<DATABASE_CONNECTION_POOL ACQUIRE_INCREMENT=”2″ AUTO_COMMIT_ON_CLOSE=”false” CHECKOUTTIME=”600″ DRIVER=”com.mysql.cj.jdbc.Driver” IDLETIME=”600″ IDLE_TEST_PERIOD=”300″ JDBCURL=”jdbc:mysql://localhost/bridgegate?useSSL=false&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL&amp;serverTimezone=EST”   ….. />

WARN: Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false on the JDBC URL String or set useSSL=true and provide truststore for server certificate verification.

BridgeGate can be configured for mysql database failover with the following updates to the jdbc url in the bridgegate.xml

MySQL jdbc supports server failover. A failover happens when connection-related errors occur for an underlying, active connection.  ie Database down.  A connection using failover support works just like a standard connection: the client does not experience any disruptions in the failover process.

jdbc:mysql://[primary host][:port],[secondary host 1][:port][,[secondary host 2][:port]]...[/[database]]» [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

The host list in the connection URL comprises of two types of hosts, the primary and the secondary. When starting a new connection, the driver always tries to connect to the primary host first and, if required, fails over to the secondary hosts on the list sequentially when communication problems are experienced. Even if the initial connection to the primary host fails and the driver gets connected to a secondary host, the primary host never loses its special status: for example, it can be configured with an access mode distinct from those of the secondary hosts, and it can be put on a higher priority when a host is to be picked during a failover process.

Update the jdbc url to include the primary database IP and then a comma list of secondary IP address.

jdbc:mysql://192.168.10.17,192.168.10.18/bridgegate? …..

Below is an example of the bridgegate.xml configured with a primary db on IP 192.168.10.17  and the secondary db on IP 192.168.18

<DATABASE_CONNECTION_POOL ACQUIRE_INCREMENT=”2″ AUTO_COMMIT_ON_CLOSE=”false” CHECKOUTTIME=”600″ DRIVER=”com.mysql.cj.jdbc.Driver” IDLETIME=”600″ IDLE_TEST_PERIOD=”300″ JDBCURL=”jdbc:mysql://192.168.10.17,192.168.10.18/bridgegate?useSSL=false&amp;failOverReadOnly=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL” MAXCONN=”300″ MAX_STATEMENTS=”0″ MINCONN=”10″ PASSWORD=”ENC3AMq%2BPLK4Q%2Bzvhu2oJhQvw%3D%3D” USERNAME=”bridgegate”/>

 

MySQL also supports load balancing across DBs.

Update bridgegate.xml to use the mysql loadbalance feature. Append loadBalanceStrategy=bestResponseTime to the existing URL. For URL jdbc:mysql:loadbalance://192.168.10.88,192.168.10.89/bridgegate?useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=convertToNull&amp;loadBalanceStrategy=bestResponseTime

More details can be found on the mysql site.  See below

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-config-failover.html

Backup MySql Database using MySqlAdmin or mysqldump (Instructions below)

• Open a Command prompt on the desktop of the BridgeGate server.
• Assuming BridgeGate 7.0 is installed on c:
• At a c:\bridgegate\mysql\bin> prompt
• Replace MYSQLPASSWORD in the below command with your current MySql password
• Run: ” mysqldump -u bridgegate -pMYSQLPASSWORD –routines –hex-blob bridgegate > c:\bridgegate\bg.sql”
  • If you have MySQL databases other than bridgegate, you can backup all of them together with the following:
    • Run: “mysqldump -u bridgegate -pMYSQLPASSWORD –routines –hex-blob –databases bridgegate db1 db2 db3 db4 > c:\bridgegate\bg.sql”
    • Replace db1 db2 db3 db4 with the other database names

Below is a way to fix an out of sync instance of MySQL Master-Master Replication.

This way does not require restarting the MySQL services on either instance and requires no file deleting.

The steps will also work with a MySQL Master-Slave replication setup.

The key change is the –master-data attribute in the mysqldump command from the master database.

 https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_master-data

 “Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.”

 ________________________________________

——————-

MySQL Master-Master Replication is broken.

Note: Replace the following values.

 

Good Server IP:                192.168.10.11

Bad  Server IP:   192.168.10.22

Good Server NAME:       GOODHOST

Bad  Server NAME:          BADHOST

MySQL UserName: MySQLUSERNAME

MySQL Password: MySQLPASWORD

 

Note: All of these commands can be run from the ‘Good Server’, if you open two separate Command Prompts.

Note: If you run the ‘show slave status\G’ command too soon, you may not get the expected result.  Re-run the command again to verify.

Note: Most of the time will be spent during importing the mysqldump in step 3.

——————-

 

1. On Both Servers

STOP BridgeGate service on both systems.

Disable BridgeGate service to prevent auto-restarting.

 

2. On the Good Server

Open a Command Prompt.

Note: If extra databases are being replicated, add them to the list: “–databases bridgegate bd1 bd2 bd3”

Note: Do not include the following databases: mysql information_schema, and performance_schema

————

e:

cd bridgegate\mysql\bin

mysql -h 192.168.10.11 -u MySQLUSERNAME -p

MySQLPASWORD

show databases;

exit;

mysqldump -h 192.168.10.11 -u MySQLUSERNAME -p –add-drop-table –quick –master-data –databases bridgegate  > e:\bridgegate\bg.all.sql

MySQLPASWORD

————

 

 

3. On the Rebuilding Server

Note: Open a Command Prompt.

Note: Expected output of ‘slave status’ is “Slave_IO_State: Waiting for master to send event”

————

e:

cd bridgegate\mysql\bin

mysql -h 192.168.10.22 -u MySQLUSERNAME -p

MySQLPASWORD

stop slave;

exit;

mysql -h 192.168.10.22 -u MySQLUSERNAME -p bridgegate < e:\bridgegate\bg.all.sql

MySQLPASWORD

mysql -h 192.168.10.22 -u MySQLUSERNAME -p

MySQLPASWORD

start slave;

show slave status\G

flush tables with read lock;

show master status;

————

 

 

 

4. On the Good Server

Note: Set the slave here to be in synch with Rebuilding Server. Use the file name and log position from the previous step.

Note: Expected output of ‘slave status’ is “Slave_IO_State: Waiting for master to send event”

————

mysql -h 192.168.10.11 -u MySQLUSERNAME -p

MySQLPASWORD

stop slave;

change master to master_log_file=’BADHOST-bin.??????????’, master_log_pos=???????????;

start slave;

show slave status\G

————

 

 

5. On Both Servers

Enable BridgeGate service.

Start BridgeGate service on both systems.

Execute transactions on either server to test replication of database.

Also, test for Active=True/False and Loadbalancing settings at this time.

Note: Expected output of ‘slave status’ is “Slave_IO_State: Waiting for master to send event”

After both servers are up and running, check the ‘slave status’ of both MySQL servers.

————

show slave status\G

————