MySQL

How do I manually backup the bridgegate database

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

How do I Resync broken MySQL Master-Master Replication

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

————