Category Archives: Notes

All Articles

MySQL Replication Setup

Mysql uses a Master-slave/Publisher-Subscriber model for Replication. MySQL replication is an asynchronous replication. In MySQL replication master keeps a log of all of the updates performed on the database. Then, one or more slaves connect to the Master(Publisher Server), read each log entry, and perform the indicated update on the slave (Subscriber) server databases. The master server is responsible for the track of log rotation and access control.

Each slave server has to keep the track of current position within the server’s transaction log. As new transactions occur on the server, they get logged on the master server and downloaded by each slave. Once the transaction has been committed by each slave, the slaves update their position in the server’s transaction log and wait for the next transaction.

In this article, I will show you the steps to configure the Master/Slave replication between two servers.

Step 1: Create a user on Master server which Slave server can use to connect. I have created the user named “repl_user”.

--Connect to MySQL Master server
mysql -u root -proot
--Execute the below code
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Step 2: We have to change the MySQL configuration file usually in the /etc/mysql.cnf location. Here we will add the replication configuration parameters.

log-bin – will be used to write a log on the desired location
binlog-do-db – will be used to enabled the database for writing log. I have used Publisher_Database, you have to specify your database name.
server-id – Specify the ID of the Master server

log-bin = /home/mysql/logs/mysql-bin.log
binlog-do-db=publisher_database
server-id=1

Step 3: Once you have added the above configuration parameters into the My.cnf, next step is restart the MySQL Master Instance.
You can use below command to restart the MySQl service.

/etc/init.d/mysqld restart
service mysqld restart

Step 4: We have to configure the /etc/my.cnf file on the slave server. Here we will add the below parameters in the configuration file.

server-id – gives the Slave its unique ID
master-host – tells the Slave the I.P address of the Master server for connection. You can get the IP address using IPConfig command.
master-connect-retry – Here we will specify the connection retry interval.
master-user – Specify the user which has permission access the Master server
master-password – Specify the password of the replication user mentioned above
replicate-do-db – Specify the subscriber database name
relay-log – direct slave to use relay log

server-id=2
master-host=128.20.30.1
master-connect-retry=60
master-user=repl_user
master-password=password
replicate-do-db=subscriber_slave
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

Step 5: Restart the slave MySQl instance

/etc/init.d/mysqld restart
service mysqld restart

Step 6: If your Master MySQL instance is live instance, you have to do the backup/restore using MySQLDump utility.

--Connect to MySQL Master server
mysql -u root -proot

--Stop the write operation
FLUSH TABLES WITH READ LOCK;

--Generate the dump of the database (backup)
--gzip command will compress the file and create the zip file name backup.sql.gz
mysqldump publisher_master -u root -p > /home/my_home_dir/backup.sql;
gzip /home/my_home_dir/backup.sql;

--execute below copy command on slave to copy the backup file
scp root@128.20.30.1:/home/my_home_dir/database.sql.gz /home/my_home_dir/

--Once copied, extract the file using gunzip
gunzip /home/my_home_dir/backup.sql.gz

--restore the databsae
mysql -u root -p subscriber_slave  </home/my_home_dir/backup.sql

Step 7: Execute the SHOW MASTER STATUS command on Master server. It will give you the bin log file name and position which we will use specify the slave.

SHOW MASTER STATUS;

Step 8: Execute the below commands on slave.

--Connect to MySQL Slave server
mysql -u root -proot
--stop the slave
slave stop;
-- Execute the below command
CHANGE MASTER TO MASTER_HOST='128.20.30.1', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=707;
--Start the Slave
slave start;

Step 9: Login to Master MySQL instance and unlock the tables.

--Connect to MySQL Master server
mysql -u root -proot
-- unlock the tables if you have executed lock tables command
unlock tables;

You are all set. Master to Slave replication has been started. Make sure while configuring the my.cnf file.
1. Take the copy of my.cnf file before starting the replication configuration.
2. Make sure skip-networking parameter is not enabled in the my.cnf file.

T-SQL Script to Check/Create directory

4 Replies

Recently I came across a situation where I need to check whether the directory is exists or not, in case if the directory does not exist, I have to create new one.

As a solution, I have created below script to fix the issue.

	declare @chkdirectory as nvarchar(4000)
	declare @folder_exists as int
	
	set @chkdirectory = 'C:\SQLDBPool\SQL\Articles'

	declare @file_results table 
	(file_exists int,
	file_is_a_directory int,
	parent_directory_exists int
	)

	insert into @file_results
	(file_exists, file_is_a_directory, parent_directory_exists)
	exec master.dbo.xp_fileexist @chkdirectory
	
	select @folder_exists = file_is_a_directory
	from @file_results
	
	--script to create directory		
	if @folder_exists = 0
	 begin
		print 'Directory is not exists, creating new one'
		EXECUTE master.dbo.xp_create_subdir @chkdirectory
		print @chkdirectory +  'created on' + @@servername
	 end		
	else
	print 'Directory already exists'
GO

Restoring a SQLServer database that uses Change Data Capture

How to Move Resource Database?

3 Replies

Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Resource database will be very useful in doing upgrades or in un-installing up-grades. In the previous versions of SQL Server up-grade needs creating/dropping of the system objects. From the SQL Server 2005 version upgrade is just procedure to copying resource database file to local server.

Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf

Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.

You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.

SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO
SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO

To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.

You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
NET START MSSQLSERVER /f /T3608
Named Instance
NET START MSSQL$instancename /f /T3608

Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')

How to kill all sessions that have open connection in a SQL Server Database?

4 Replies

As SQL Server DBAs, many times we need to KILL all Open Sessions against the SQL Server Database to proceed with Maintenance Task, Restore and more…

You can use below different techniques to KILL all open sessions against the database.

Technique – I
Here we will query the SysProcesses table to get the session running against the user database and prepare the dynamic SQL statement to KILL all the connection.

DECLARE @DbName nvarchar(50)
SET @DbName = N'Write a DB Name here'

DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''

SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND SPId  @@SPId

EXEC(@EXECSQL)

Technique – II
Take the database into Single User Mode and execute all the task needs to perform against the databse.

ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Once you are finish with all the required task make the database accessible to everyone.

ALTER DATABASE [Database Name] SET MULTI_USER

Technique – III
In case of restore the database by replacing existing database, you can take the database OFFLINE and restore it. Restore will bring the database online.

ALTER DATABASE [Database Name] SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Database Name] SET ONLINE

Technique – IV
Go to Activity Monitor, Select the desired database and right click on the database to KILL the process.

aiDeeva.com

AI for Thinkers, Builders, and Believers.