Linux 上的日志传送入门
07/01/2020
本文内容
适用于:
SQL Server(所有支持的版本) - Linux
SQL Server 日志传送是一种 HA 配置,支持将数据库从主服务器复制到一个或多个辅助服务器上。 简单地说,可将源数据库的备份还原到辅助服务器上。 随后,主服务器会定期创建事务日志备份,辅助服务器会还原备份,同时更新数据库的次要副本。
如图片所示,日志传送过程包含以下步骤:
在 SQL Server 主实例上备份事务日志文件
将事务日志备份文件通过网络复制到一个或多个辅助 SQL Server 实例
还原辅助 SQL Server 实例上的事务日志备份文件
必备条件
使用 CIFS 为日志传送设置网络共享
备注
本教程使用 CIFS 和 Samba 设置网络共享。 如果想使用 NFS,请留下评论,我们会将其添加到文档。
配置主服务器
运行以下命令以安装 Samba
sudo apt-get install samba #For Ubuntu
sudo yum -y install samba #For RHEL/CentOS
创建用于存储日志传送的日志的目录,并为 mssql 提供所需权限
mkdir /var/opt/mssql/tlogs
chown mssql:mssql /var/opt/mssql/tlogs
chmod 0700 /var/opt/mssql/tlogs
编辑 /etc/samba/smb.conf 文件(需要根权限)并添加以下部分:
[tlogs]
path=/var/opt/mssql/tlogs
available=yes
read only=yes
browsable=yes
public=yes
writable=no
为 Samba 创建 mssql 用户
sudo smbpasswd -a mssql
重启 Samba 服务
sudo systemctl restart smbd.service nmbd.service
配置辅助服务器
运行以下命令以安装 CIFS 客户端
sudo apt-get install cifs-utils #For Ubuntu
sudo yum -y install cifs-utils #For RHEL/CentOS
创建文件以存储凭据。 使用最近为 mssql Samba 帐户设置的密码
vim /var/opt/mssql/.tlogcreds
#Paste the following in .tlogcreds
username=mssql
domain=
password=
运行以下命令创建空目录,用于装载并正确设置权限和所有权
mkdir /var/opt/mssql/tlogs
sudo chown root:root /var/opt/mssql/tlogs
sudo chmod 0550 /var/opt/mssql/tlogs
sudo chown root:root /var/opt/mssql/.tlogcreds
sudo chmod 0660 /var/opt/mssql/.tlogcreds
将此行添加到 etc/fstab 以保留共享
///tlogs /var/opt/mssql/tlogs cifs credentials=/var/opt/mssql/.tlogcreds,ro,uid=mssql,gid=mssql 0 0
装载共享
sudo mount -a
通过 T-SQL 设置日志传送
从主服务器运行此脚本
BACKUP DATABASE SampleDB
TO DISK = '/var/opt/mssql/tlogs/SampleDB.bak'
GO
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int
EXECUTE @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'SampleDB'
,@backup_directory = N'/var/opt/mssql/tlogs'
,@backup_share = N'/var/opt/mssql/tlogs'
,@backup_job_name = N'LSBackup_SampleDB'
,@backup_retention_period = 4320
,@backup_compression = 2
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN
DECLARE @LS_BackUpScheduleUID As uniqueidentifier
DECLARE @LS_BackUpScheduleID AS int
EXECUTE msdb.dbo.sp_add_schedule
@schedule_name =N'LSBackupSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20170418
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT
EXECUTE msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID
EXECUTE msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1
END
EXECUTE master.dbo.sp_add_log_shipping_alert_job
EXECUTE master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'SampleDB'
,@secondary_server = N''
,@secondary_database = N'SampleDB'
,@overwrite = 1
从辅助服务器运行此脚本
RESTORE DATABASE SampleDB FROM DISK = '/var/opt/mssql/tlogs/SampleDB.bak'
WITH NORECOVERY;
DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
DECLARE @LS_Add_RetCode As int
EXECUTE @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N''
,@primary_database = N'SampleDB'
,@backup_source_directory = N'/var/opt/mssql/tlogs/'
,@backup_destination_directory = N'/var/opt/mssql/tlogs/'
,@copy_job_name = N'LSCopy_SampleDB'
,@restore_job_name = N'LSRestore_SampleDB'
,@file_retention_period = 4320
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryCopyJobScheduleID AS int
EXECUTE msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultCopyJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20170418
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
EXECUTE msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__CopyJobId
,@schedule_id = @LS_SecondaryCopyJobScheduleID
DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleID AS int
EXECUTE msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultRestoreJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20170418
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
EXECUTE msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__RestoreJobId
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
END
DECLARE @LS_Add_RetCode2 As int
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXECUTE @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'SampleDB'
,@primary_server = N''
,@primary_database = N'SampleDB'
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@overwrite = 1
END
IF (@@error = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXECUTE msdb.dbo.sp_update_job
@job_id = @LS_Secondary__CopyJobId
,@enabled = 1
EXECUTE msdb.dbo.sp_update_job
@job_id = @LS_Secondary__RestoreJobId
,@enabled = 1
END
验证日志传送是否正常运行
通过在主服务器上启动以下作业来验证日志传送是否正常运行
USE msdb ;
GO
EXECUTE dbo.sp_start_job N'LSBackup_SampleDB' ;
GO
通过在辅助服务器上启动以下作业来验证日志传送是否正常运行
USE msdb ;
GO
EXECUTE dbo.sp_start_job N'LSCopy_SampleDB' ;
GO
EXECUTE dbo.sp_start_job N'LSRestore_SampleDB' ;
GO
通过执行以下命令验证日志传送故障转移是否正常运行
警告
此命令将使辅助数据库处于联机状态并中断日志传送配置。 运行此命令后,将需要重新配置日志传送。
RESTORE DATABASE SampleDB WITH RECOVERY;