MySQL synchronization of individual table contents in different databases on the same server

>>>>>>soft_wsx>>>>>>
--Data backup and restore >> synchronous standby server
--1, fully backing up the primary database
--2, using a RESTORE DATABASE statement with a sandby clause to recover from a full backup of the primary database to build the standby database
--3, creating jobs, using Backup log to schedule a log backup of the primary database
--4, creating jobs, using the RESTORE LOG statement with the STANDBY clause to periodically recover data from a log backup of the primary database to the standby database
--5, the primary database is unavailable, use the RESTORE LOG statement with the RECOVERY clause to promote the standby database to the primary database
----specific code and instructions are as follows
---->> First, create a database for Chung Yuen (primary database)
Create DATABASE Db_test_primary
On
(
Name=db_test_primary_data,
Filename= ' D:\db_test_primary_data.mdf ')
Log on
(
Name= ' Db_test_primary_log ',
Filename= ' D:\db_test_primary_log.ldf '
)
Go

--Create a table on the primary database
Create TABLE db_test_primary.dbo.db_test_primary1 (id int)
Insert Db_test_primary.dbo.db_test_primary1 Select top ID from dbo.sysobjects
Go
--Backup of the primary database
Backup Database Db_test_primary
To disk= ' D:\db_test_primary_data.bak '
With format
Go

--Create a standby database from the primary database backup file (to demonstrate synchronization between the primary database and the standby database)
Restore Database Db_test_second
From disk= ' D:\db_test_primary_data.bak '
With Replace,
Standby= ' D:\db_test_primary_bak.ldf ',
Move ' db_test_primary_data ' to ' d:\db_test_second_data.mdf ',
Move ' db_test_primary_log ' to ' d:\db_test_second_log.ldf '

--Start the SQL Agent service (the primary database needs to be synchronized to the standby database by job timing)
EXEC master.dbo.xp_cmdshell ' net start SQLServerAgent ', no_output
Go

--Create a job that synchronizes between the primary service database and the standby server database
DECLARE @jobid uniqueidentifier
EXEC msdb.dbo.sp_add_job
@[email protected] Output,
@job_name =n ' data synchronization '

--Create a synchronous process step
EXEC msdb.dbo.sp_add_jobstep
@[email protected],
@step_name =n ' data synchronization ',
@subsystem = ' TSQL ',
@command =n '
--log backup in the master database
Backup LOG db_test_primary
To disk= ' D:\test_log_primary.bak '
With format

--log backup of the primary database in the standby data (latest changes in the application master database)
--Note: The actual restore job for the primary and standby databases should be established on the primary and standby servers, respectively.
--and the backup file should be placed in a shared directory accessible to both the primary and standby servers
Restore Log Db_test_second
From disk= "D:\test_log_primary.bak"
With standby= ' D:\test_log.ldf ',
@retry_attempts = 5,
@retry_interval =5
--Create a schedule
EXEC msdb.dbo.sp_add_jobschedule
@[email protected],
@name = ' time schedule ',
@freq_TYPE = 4,
@freq_interval = 1,
@freq_subday_type =0x4,
@freq_subday_interval =1

--Add a directory server
EXEC msdb.dbo.sp_add_jobserver
@[email protected],
@server_name =n ' (local)
Go
--through the above processing, the synchronization relationship between the primary database and the standby database has been set to complete
--Start testing to see if synchronization is possible
CREATE TABLE Db_test_primary.dbo.db_test_primary3 (ID int)
Go
Insert Db_test_primary.dbo.db_test_primary3 Select Top ID from dbo.sysobjects
WAITFOR DELAY ' 00:01:30 '
Go

--Check the standby database to see if synchronization is successful

SELECT * FROM Db_test_second.dbo.db_test_primary3

drop table Db_test_primary.dbo.db_test_primary3

--Finally delete the test environment
Drop Database Msdb.dbo.sp_delete_job
@job_name =n ' data synchronization '

MySQL synchronization of individual table contents in different databases on the same server