In this blog post, I will be showing you the commands that we can use to do streaming backup using mbstream, and how to redirect the stream to a slave , which can be useful especially when you have insufficient disk space in the Master server to hold multiple copies of backup images.
To redirect backup stream to a slave server, we use the socat utility.
Socat stands for Socket Cat. It is a relay for bidirectional data transfer between two independent data channels.
You can install socat using the command below
yum install -y socat
This is the first command that we will execute in Slave.
The –compress-threads option defines the number of worker threads to use in compression. It can be used together with –parallel option. In the example above, we use –parallel=4, and –compress-threads=12. This means that it will create 4 I/O threads to read the data; then pipe it to 12 threads for compression.
When we execute the mariabackup command in the Master, below is what we will observe in slave. We will see that it is starting to do data transfer.
When the backup finishes, we will see that it exits with status 0.
In this blog post, I’m going to show you how to setup GTID replication using Mariabackup.
There are 2 main benefits of using global transaction:
Failover is easier than with file-based replication.
the state of the slave is recorded in a crash-safe way.
Here are the general steps:
Enable binary logging on the master
Enable GTID
Create a replication user on the master
Set a unique server_id on the slave
Take backup from the master
Restore on the slave
Execute the CHANGE MASTER TO command
Start the replication
1. Ensure that the server_id value and bind_address are configured differently in my.cnf in each of the server
that will be part of the replication.
In this example, we will configure a 2 node master-slave setup. The bind-address is the hostname IP.
vi my.cnf
In Master:
server_id=1
bind-address=192.168.1.115
In Replica:
server_id=2
bind-address=192.168.1.131
2. Enable binary logging and GTID strict mode in both servers
show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
show global variables like '%gtid_strict_mode%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| gtid_strict_mode | ON |
+------------------+-------+
If bin logging is not enabled, you may do so by adding the line below in my.cnf
log-bin = db1-bin
# Or specify an different path
log-bin = /mariadb/bin/logs/bin_logs/bin_log
Restart DB service for the change to take effect.
sudo service mysql stop
sudo serivice mysql start
If gtid is not enabled, add the line below in my.cnf, then enable it globally.
Add this line in cnf
gtid_strict_mode=1
Login to MariaDB, then set global_script_mode=1.
set global gtid_strict_mode=1;
3. Create a user in Master.
The replica is going to use this user connection to read the binary logs on the master and then put those into the relay logs on the replica.
CREATE USER 'repl'@'%' IDENTIFIED BY 'P@$$w0rd';
GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
4. Install qpress (As root) in both Master and Slave for compression/decompression.
6. In Replica, create a directory where we will place the backup from Master
mkdir -p /mariadb/backup/rep
7. In Master, use scp to transfer the entire backup image to the replica.
# Go to the directory where you placed the backup
cd /mariadb/backup
scp -rp full_backup mysql@192.168.1.131:/mariadb/backup/rep
8. In Replica, Stop DB Service
sudo service mysql stop
# Verify that DB service has been stopped
ps -ef| grep mysqld
9. In Replica, Remove all contents in Data Directory.
mkdir -p /mariadb/data/old_data
mv /mariadb/data/* /mariadb/data/old_data/
rm -rf /mariadb/data/old_data/
# Ensure that the data directory is empty
cd /mariadb/data
ls -la
10. In Replica, copy the backup image to data directory.
In this blog post, I’m gonna highlight the differences in GTID replication between MySQL and MariaDB
I will not go thru step-by-step setup, because they are many resources out there that do.
The main reason to use GTID is that it makes it easier to track and compare replicated transaction between master and replica; hence, allowing simpler failover and recovery.
Here are the differences between MySQL and MariaDB
1. Composition of GTID
In MariaDB, it is composed of three separated dashed numbers like x-y-z
x: first number – domain ID
y: second number – server ID
z: third number – sequence number
In MySQL, there are 2 parts:
source_id
transaction_id
2. Enabling GTIDs
To enable GTID, we have to set the following parameters in my.cnf
In MySQL:
gtid_mode
enforce_gtid_consistency
gtid-mode=ON
enforce-gtid-consistency
In MariaDB:
gtid_strict_mode
gtid_strict_mode=1
enforce_gtid_consistency does not exist in MariaDB
3. CHANGE MASTER TO statement
In order for the Replica to identify its Master (data source), and to use GTID-based auto-positioning, we need execute the CHANGE MASTER TO statement. In MySQL, we use the MASTER_AUTO_POSITION option to tell the replica that transactions will be identified by GTIDs.
In MariaDB, A slave is configured to use GTID by CHANGE MASTER TO master_use_gtid=slave_pos. The replication will start at the position of the last GTID replicated to slave when the slave connects to the master. Refer to the official documentation for more info https://mariadb.com/kb/en/gtid/
Example In MariaDB:
change master to
master_host='192.168.1.120',
master_port=3306,
master_user='repl',
master_password='password',
master_use_gtid=slave_pos;
Here is a SQL script that you can use to identify Blocking and Blocked Queries in MariaDB.
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx AS r
ON r.trx_id = w.requesting_trx_id
First, we will set up the environment. We will be having 1 master and 2 slaves. On top of those, we will have our MariaDB MaxScale server.
To make it easier to setup the environment, you may use the vagrant file below.
# -*- mode: ruby maxscale222
# vi: set ft=ruby :
# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
#: adding ansible stuff
config.ssh.insert_key = false
#: maxscale box
config.vm.define :maxscale do |maxscale|
maxscale.vm.hostname="maxscale"
maxscale.vm.box = "domflannery/centos7"
maxscale.vm.network "public_network"
end
######: MASTER / SLAVE SERVERS :######
#: master, async && semisync replication
config.vm.define :mariadb1, autostart: true do |mariadb1|
mariadb1.vm.hostname="mariadb1"
mariadb1.vm.box = "domflannery/centos7"
mariadb1.vm.network "public_network"
end
#: slave01, async && semisync replication
config.vm.define :mariadb2, autostart: true do |mariadb2|
mariadb2.vm.hostname="mariadb2"
mariadb2.vm.box = "domflannery/centos7"
mariadb2.vm.network "public_network"
end
#: slave02, async && semisync replication
config.vm.define :mariadb3, autostart: true do |mariadb3|
mariadb3.vm.hostname="mariadb3"
mariadb3.vm.box = "domflannery/centos7"
mariadb3.vm.network "public_network"
end
end
I chose public networks that is set automatically (through DHCP) for demo purposes. The VMs IP addresses will be in the same range as the host’s IP address.
Once all the virtual boxes are up, we will login to each of them to get the assigned public IP address. First, login to each server using local IP (127.0.0.1) and the respective port numbers (refer to the chart below). The login and password will be both vagrant
Hostname
IP address
Port
maxscale
127.0.0.1
2222
mariadb1
127.0.0.1
2200
mariadb2
127.0.0.1
2201
mariadb3
127.0.0.1
2202
login: vagrant password: vagrant
I got the port numbers above by looking at the logs after I issued vagrant up. Below is an example.
Get the assigned IP address using the command below (ifconfig will not work in this version of centos)
ip a
After getting the public IP addresses of each server, configure them in your putty. Give each connection sensible names. (Also, don’t include the /24 of the IP address. )
Install MariaDB in mariadb1, mariadb2, and mariadb3. I will be using MariaDB 10.4 on Centos7. If you want to use a different flavor of Linux, then refer to this link to get the correct repositories info.
Let’s create a repo file.
vim MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Move the file to /etc/yum.repos.d/
sudo mv -vi MariaDB.repo /etc/yum.repos.d/
Once the file is in place, we are ready to install MariaDB (The capitalization of the package names is important).
sudo yum install MariaDB-server MariaDB-client -y
SETUP MARIADB REPLICATION (USING GTID)
Get the master and replicas ready.
Login to mariadb1 as root, and edit the my.cnf
vi /etc/my.cnf
Delete the existing lines; then enter the following lines below (or just copy paste them).
Do the same (edit config file) in mariadb2 and mariadb3, but change the server_id to a different value for each server.
After editing the my.cnf file on all 3 servers, start mariadb service
systemctl start mariadb
STARTING UP REPLICATION
Create a user in the master, and grant “replication slave” to the user. The replica is going to use this user connection to read the binary logs on the master and then put those into the relay logs on the replica.
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%';
In a real-world production environment, you would create a user that can only connect from the IP address of your slaves, but for the sake of demonstration, we’ll just use the wild card character (%)
Point your slaves to the master by executed the commands below.
change master to
master_host='192.168.1.220',
master_user='repl',
master_password='password',
master_use_gtid=slave_pos;
Run the command below to start the slave
start slave;
Check slave status
show slave status \G
Both Slave_IO_Running and Slave_SQL_Running parameters should be Yes.
Execute on Master (only) to grant privileges to maxuser. This user will be used by maxscale.
grant reload, show databases, super, replication slave, replication client on *.* to 'maxuser'@'localhost' identified by 'P@$$w0rd';
grant reload, super, replication slave, replication client ON *.* TO 'repl'@'%';
grant select on mysql.user to 'maxuser'@'localhost';
grant select on mysql.db to 'maxuser'@'localhost';
grant select on mysql.tables_priv to 'maxuser'@'localhost';
grant select on mysql.roles_mapping to 'maxuser'@'localhost';
grant select on mysql.columns_priv to 'maxuser'@'localhost';
grant select on mysql.proxies_priv to 'maxuser'@'localhost';
flush privileges;"
grant reload, show databases, super, replication slave, replication client on *.* to 'maxuser'@'' identified by 'P@$$w0rd';
grant reload, super, replication slave, replication client ON *.* TO 'repuser'@'%';
grant select on mysql.user to 'maxuser'@'';
grant select on mysql.db to 'maxuser'@'';
grant select on mysql.tables_priv to 'maxuser'@'';
grant select on mysql.roles_mapping to 'maxuser'@'';
grant select on mysql.columns_priv to 'maxuser'@'';
grant select on mysql.proxies_priv to 'maxuser'@'';
flush privileges;"
In your maxscale server, run the command below to check list of servers being monitored.
maxctrl list servers
FAILOVER TEST
Let’s monitor the servers continuously every second. I suggest you open a new session of your maxscale server, and run the command below.
watch -n1 maxctrl list servers
Go to mariadb1, then stop MariaDB service.
systemctl stop mariadb
After a few seconds, you will see that mariadb2 has been promoted to master.
Let’s bring up mariadb1.
systemctl start mariadb
Monitor the servers, and you will see that they are all up and running.
SWITCHOVER
Let’s make mariadb1 server the master again. We will be doing a switchover, but before we proceed, we have to verify the monitor that we are going to use.
maxctrl list monitors
In our case, it is MariaDB-Monitor.
Now, we are ready to do the switchover. Here is the command to perform it.
call command indicates that it is a module command that is to be invoked. mariadbmon indicates the module whose command we want to invoke. switchover is the command we want to invoke. MariaDB-Monitor is the first argument to the command, the name of the monitor as specified in the configuration file. mariadb1 the name of the server we want to make into master mariadb2 current master.
We now have successfully switched over the master role back to mariadb1
LOAD BALANCING TEST
In this demo, we will be using the maxscale server as our client.
In the previous section, we’ve already installed MariaDB client. Verify that is installed successfully.
rpm -qa | grep -i MariaDB
In mariadb1 (master), create an app user.
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%' WITH GRANT OPTION;
To connect to our servers via maxscale, we use the connection string below. Essentially, we have to indicate the maxscale IP address. My maxscale IP address happens to be 192.168.1.87. We also have to include the Read/Write listener port that is 4006 as indicated in the maxscale config file (/etc/maxscale.cnf)
Execute the command below in your maxscale server.
mysql -u app_user -p -h192.168.1.87 -P4006
To know which server you are connect to, run the following statement.
SELECT @@hostname;
If we keep on executing this statement repeatedly, we will notice that we will be connected to our slaves: mariadb2 and mariadb3. This is load balancing at work. MaxScale knows that we are executing a read statement; hence it is redirecting the traffic to our read-only replicas.
Once we start a transaction, MaxScale now recognizes that we are about to update a record; hence the connection will be redirected to the master node only.
START TRANSACTION;
The connection will remain in master until we commit the transaction.
COMMIT;
After we commit the transaction, the connection will again be redirected to the read-only replicas.
This is also called the Read Write Splitting.
MaxScale analyze statements, and send where appropriate. Write statements to master; and Read statements to some slaves.
Here are the formulas for setting memory in SQL Server.
Single-instance dedicated Max server memory formula: physical memory – 4G (or 10% whichever is greater to leave some GBs for the OS).
Single-instance shared Max server memory formula: physical memory – 4G ( or 10% whichever is greater to leave some GBs for the OS ) – (memory for other services).
Use the sys.configurations view to query server configuration options including Max server memory(MB) and Min server memory (MB).
SELECT
SERVERPROPERTY('InstanceName') AS myinstance,
*
FROM
sys.configurations
WHERE name IN
(
'max server memory (MB)',
'min server memory (MB)'
)
GO
Use the sys.dm_os_sys_info view to query the total amount of physical memory in the server, number of schedulers, last server startup time, and if SQL Server runs in a virtual or physical environment
SELECT physical_memory_kb / 1024 AS Physical_Memory_MB
FROM sys.dm_os_sys_info
Use the sp_configure system stored procedure to set server configuration options to prevent potential UI data entry errors.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory (MB)', <Value>
GO
RECONFIGURE
GO
Instant file initialization is a setting that can make a positive impact at instance level on SQL Server I/O performance.
All I/O operations are done via the OS and the storage layer. For example:
Claiming disk space for creating a new database file
Autogrowing a database file
Restoring a database from backup
Whenever SQL Server needs to allocate space for these operations, it will first fill the space it needs with zeros. This zeroing phase, or zero initialization, can take a significant amount of time, depending on storage performance and size of the disk space to zeroed. Therefore, it can adversely impact file creation, autogrow, and database restore performance.
How to Turn-On Instant File Initialization
SQL Server doesn’t have a setting or checkbox to enable Instant File Initialization.
Instead, it detects whether or not the service account it’s running under has the Perform Volume Maintenance Tasks permission in the Windows Security Policy. You can find and edit this policy by running secpol.msc (Local Security Policy) in Windows.
Expand the Local Policies Folder.
Click on User Rights Assignment.
Go down to the “Perform Volume Maintenance Tasks” option and double click it.
Add your SQL Server Service account and click OK out of the dialog.
After granting this policy to the service account, you’ll need to restart the SQL Server service in order for the policy to take effect and for SQL Server to start using Initial File Initialization.
I’ve just migrated 8 of my demo databases from SQL 2014 to SQL 2017, and I want to change the compatibility level (from 120 to 140) of all those databases via TSQL. Here is a script to do just that.
BEGIN
SET NOCOUNT ON;
DECLARE @rc INT = 0, @i INT = 1, @v_cmd NVARCHAR(MAX);
DECLARE @cmds TABLE(RowNum INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Cmd NVARCHAR(MAX) NOT NULL);
INSERT INTO @cmds
SELECT 'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 140' FROM sys.databases
WHERE compatibility_level = 120
SET @rc = @@ROWCOUNT;
RAISERROR('The COMPATIBILITY_LEVEL of %d databases will be changed to 140', 10, 1, @rc) WITH NOWAIT;
WHILE @i <= @rc
BEGIN
SELECT @v_cmd = cmd FROM @cmds WHERE RowNum = @i;
PRINT @v_cmd;
EXEC(@v_cmd);
SET @i += 1;
END;
END
A SQL database will be marked with different states if one or more of its core files are in a inconsistent state. The type of state will depend on how serious the damage is. Here are some of the states:
Online – occurs when one of the data files is damaged during an execution of a query or some other operation.
Suspect – occurs when a database cannot be recovered during startup
Recovery Pending – occurs when SQL Server knows that recovery of the database is to be done, but something is obstructing before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, because it has not started yet
Database marked as ‘Recovery Pending’
Here a script to check the current state of all your databases in a SQL instance.
SELECT name, state_desc from sys.databases
GO
There are several reasons why a database is marked as ‘Recovery Pending‘
The database was not cleanly shutdown. There could be one or more transactions active at that time, resulting in the deletion of active transaction log file.
To overcome server performance issues, A user could have tried moving the log files to a new drive, but in the process, corrupted the files instead.
Insufficient memory or disk space that prevented database recovery from getting started.
Let’s Fix It
ALTER DATABASE TestDB SET EMERGENCY;
GO
ALTER DATABASE TestDB set single_user
GO
DBCC CHECKDB (OptimalTestDb, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
