I have a second MySQL 8 instance running on Ubunut 20.04, started by this command: mysqld_safe --defaults-file=/home/user/mysqld_replica.cnf & The config file contains all needed settings like port, socket and data dir. That works well but I can not figure out how to start the second instance on boot. What would be the recommended way to start both instances at boot time?

How can a query or procedure return an empty copy of a new record? Ideally suited for objects which are in a transactional allocation process: It is highly preferable to have authoritative defaults and NULLS for arbitrary tables when constructing new objects() that *may-or-may not* ultimately be saved to the database. For example, a CMS user relies on AJAX which calls for a new post() or new product() dynamically. Rather than trusting stored profiles to set values in the scripting language, a blank "instance" of a row would be rather useful.

**-- EDIT -- Complete setup in the answer below. -- EDIT --** When I follow an article (link below) on how to setup a extra MariaDB or Mysql on Linux, and it works. However, I don't like the name "mysql1" & "mysql2" for instance names. But when I change just that aspect of the code, the server wont install when I run the start command. I wrote both setups here below for easy comparison: The one that does work where 2 instances are being made, and the one that ultimately doesn't work. I would very much love to figure how to make the one that doesn't work, work. Your help is very much appreciated! **- 1) Datadir (setup that works):** sudo mkdir -p -v /var/lib/mysql{1..2} sudo chown mysql:mysql -v /var/lib/mysql{1..2} **- 1) Datadir (setup that ultimately doesn't work):** sudo mkdir -p -v /var/lib/mysqlTest sudo chown mysql:mysql -v /var/lib/mysqlTest **- 2) Socket directory (setup that works):** sudo mkdir -p -v /usr/local/mysql/mdb{1..2} sudo chown -v -R mysql:mysql /usr/local/mysql/mdb{1..2} **- 2) Socket directory(setup that ultimately doesn't work):** sudo mkdir -p -v /usr/local/mysql/mdbTest sudo chown -v -R mysql:mysql /usr/local/mysql/mdbTest **- 3) Log directory (setup that works):** sudo mkdir -p -v /var/log/mysql{1..2} sudo chown -v -R mysql:mysql /var/log/mysql{1..2} **- 3) Log directory(setup that ultimately doesn't work):** sudo mkdir -p -v /var/log/mysqlTest sudo chown -v -R mysql:mysql /var/log/mysqlTest **- 4) I create the following file** "mysqld_multi_cls.cnf" Located at: /etc/mysql/ **- 5) content (setup that works):** # Configuration Settings for mysqld_multi , as well as separate instances. # [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin [mysqld1] socket = /tmp/mysql.sock1 port = 3307 pid-file = /usr/local/mysql/mdb1/ansible-host1.pid1 datadir = /var/lib/mysql1 log-error = /var/log/mysql1/error.log log_bin = /var/lib/mysql1/mdb1-mysql-bin.log server-id = 1 expire_logs_days = 72 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1 [mysqld2] socket = /tmp/mysql.sock2 port = 3308 pid-file = /usr/local/mysql/mdb2/ansible-host1.pid2 datadir = /var/lib/mysql2 log-error = /var/log/mysql2/error.log log-bin = /var/lib/mysql2/mdb2-mysql-bin.log server-id = 2 expire_logs_days = 64 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1 **- 5) content(setup that ultimately doesn't work):** # Configuration Settings for mysqld_multi , as well as separate instances. # [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin [mysqlTest] socket = /tmp/mysql.sock3 port = 3309 pid-file = /usr/local/mysql/mdbTest/ansible-host1.pid3 datadir = /var/lib/mysqlTest log-error = /var/log/mysqlTest/error.log log_bin = /var/lib/mysqlTest/mdbTest-mysql-bin.log server-id = 3 expire_logs_days = 72 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1 **-6) And now we start the server:** sudo mysqld_multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf --log=/etc/mysql/multi_ins.log start - If done in the 1st manor, the database is now installing and everyone is happy. - But if done in the 2nd manor where I only changed the name and made a single (extra) instance, nothing happens. What am I missing? Cheers and thank you very much! Here is the article on Linkedin by Michael Amadi: https://www.linkedin.com/pulse/running-multiple-instances-mariadb-single-server-different-amadi

I've got a file /etc/mysql/mysqld_multi_cls.cnf with everyting mariadb needs for multiple database instances. I can start these instances with the following command: sudo mariadbd-multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf start But I can't find a (neat) way to start these instances at boot. Adding the .cnf file in the /conf.d/ folder doesn't start the instance, unless I'm messing up unknowingly. The db is running on a Raspberry pi5, Raspberry Pi OS, which is Debian based. And I am quite unfamiliar/noob with Linux. After request, here is the .cnf file: # Configuration Settings for mysqld_multi , as well as separate instances. # [mysqld_multi] mysqld = /usr/bin/mariadbd-safe mysqladmin = /usr/bin/mariadb-admin [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/mdb2/ansible-host1.pid2 datadir = /var/lib/mysql2 log-error = /var/log/mysql2/error.log log-bin = /var/log/mysql2/mdb2-mysql-bin.log server-id = 2 expire-logs-days = 72 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1 [mysqld3] socket = /tmp/mysql.sock3 port = 3308 pid-file = /usr/local/mysql/mdb3/ansible-host1.pid3 datadir = /var/lib/mysql3 log-error = /var/log/mysql3/error.log log-bin = /var/log/mysql3/mdb3-mysql-bin.log server-id = 3 expire-logs-days = 64 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1 [mysqld4] socket = /tmp/mysql.sock4 port = 3309 pid-file = /usr/local/mysql/mdb4/ansible-host1.pid4 datadir = /var/lib/mysql4 log-error = /var/log/mysql4/error.log log-bin = /var/log/mysql4/mdb4-mysql-bin.log server-id = 4 expire-logs-days = 64 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1

Forgive me if this is an easy question, but I have tried finding an answer but I really don't know what to search (I'm a software dev, with only some limited DBA experience). We have test SQL servers setup with 3 instances each:

TSTSQL01\Enterprise
TSTSQL01\Warehouse
TSTSQL01\Legacy

TSTSQL02\Enterprise
TSTSQL02\Warehouse
TSTSQL02\Legacy

TSTSQL03\Enterprise
...

In production we don't use instances, we have 3 separate servers:

PRDSQL01 --"Enterprise" server
PRDSQL02 --"Warehouse" server
PRDSQL03 --"Legacy" server

Due to limitations of our legacy COBOL application, I am in need of a connection string that has EXACTLY 8 characters to match the length of the production connection strings. What I am wanting to do is create an alias for each test sql server that allows this, using the tstsql01 example above would ideally look like:

TSQL1ENT --maps to TSTSQL01\Enterprise
TSQL1WAR --maps to TSTSQL01\Warehouse
TSQL1LEG --maps to TSTSQL01\Legacy

My current workaround is to use the SQL Configuration Manager to add an alias . This lets me connect to the database on that server, but not on local workstations. Our legacy COBOL app runs 75% of the code from the server, so it works for that. But the remaining 25% of the code runs locally on their workstations. That means I have to add that alias on every single tester's machine (I've scripted it out via powershell, but testers aren't admins so it requires a help desk request for every single person for every single test server). Thanks in advance for the help!

I would like to install a second instance of MariaDB with MSI-Installer (Windows 10). The first instance is on drive C. When I want to install the second instance on drive D using MSI-Installer the installer recognizes that there is already an installation and asks if I want change/repair/remove. How can avoid this step and get the installation option? Windows 10/ MariaDB 10.6.8

There is good general advice for how to configure Maximum Degree of Parallelism (MAXDOP). However, I am in a special circumstance. There are two instances on my box. If I have 16 cores, how should I divide MAXDOP between them? What metrics would reveal that I have made a bad choice? I am on SQL Server 2019 and [the standard advice for MAXDOP changed in 2016](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16#recommendations) so info from before June 2016 (the release date of SQL Server 2016) may not be applicable.

So, I've ... inherited a SQL server environment. With LanSweeper, I see this particular server has two instances installed: sql001AK and sql001M5. LanSweeper tells me they have similar databases, except for UserDB3, which is on the M5 instance but not on the other. I use SSMS to connect with sql001M5 to look into this user db, but I can't find it. When I pull up the properties of this database, I see in the name attribute the name of the other instance! When logged into the actual server, I can clearly see in the SQL Server Config Manager that it's two separate instances, no aliases defined, no weird clustering, all seems fine. But in SSMS, entering the one name connects me to the other... What could explain this behaviour? How do I connect to my M5 instance?

I'm facing an interesting issue related to RAM usage, ReadThroughput, and Freeable Memory metrics with my PostgreSQL database on an RDS instance, and I'm hoping to get some insights here. Two months ago, I experienced a significant increase in the ReadThroughput metric, leading to a instance crash. I understand that a spike in ReadThroughput often suggests memory insufficiency. However, during this period, the Freeable Memory remained stable at around 12GB. In response, I upgraded to a more powerful instance type, which currently offers about 47GB of Freeable Memory. Following the upgrade, I've noticed a dramatic decrease in ReadThroughput, which is positive as it minimizes disk reads. However, despite the database size growing by approximately 5% over the past week, the Freeable Memory remains unchanged and ReadThroughput has slightly increased. Does this imply the RAM allocation remains constant despite the database growth? If so, why might this be the case? Additionally, I'm curious as to why a sudden increase in disk usage (reflected in the ReadThroughput) was observed two months ago while the Freeable Memory stayed steady. Any insights or advice from the community would be greatly appreciated.

So we are getting ready to make a major architecture shift and I get to be on the hook for it, yay! Currently we have 22 separate availability groups each with their own windows cluster across 44 servers. We want to move to 6 servers to hold the 22 AGs, yes I know it doesn't divide evenly. So for example a given server (VM) would be 4 SQL Server instances, each instance would be an availability group and have its own listener. So something like: * NODE1: SQLINST1, SQLINST2, SQLINST3, SQLINST4 * NODE2: SQLINST1, SQLINST2, SQLINST3, SQLINST4 * AGs: SQLINST1, SQLINST2, SQLINST3, SQLINST4 * Listeners: SQLINST1Lis, SQLINST2Lis, SQLINST3Lis, SQLINST4Lis My questions are: * does each SQL Server instance also need to be its own windows cluster or could one cluster suffice for all the named instances? * Will this work at all?

In the bottom-right hand side of SSMS's main screen and to the left of where it shows my user-name and the name of the database that I'm in, it gives the name of the server that I'm on. This string is the same as what I put in the "Server name" field when connecting to the server when I first open SSMS and is also shown in the Object Explorer. How does SSMS determine this name? I've tried

SELECT 
  HOST_NAME(),
  @@SERVICENAME,
  DB_NAME,
  @@SERVERNAME,
  SERVERPROPERTY('InstanceName'),
  SERVERPROPERTY('MachineName')

but only SERVERPROPERTY('MachineName') returns a substring of what SSMS shows. The rest have nothing at all to do with the string in question.

Coming from Oracle I have discovered that SSMS connects slightly different than Oracle's SQL Developer. Oracle you click ONE connection and then all tabs you open then use that Connection. SSMS creates a completely new Connect/Instance for each window/tab I open. **I don't want this**. TWO pet peeves come to mind with this behavior I am trying to get around. >1. View affect of Script before COMMIT in another Window I want to be able to run a script in one window... and in another Window have the ability to Query the Non-Committed data to simply view the results... Currently I have to use the SAME Window I ran the script in order to simply VIEW the affect. (Commit/Rollback buttons come to mind - Oracle I miss you sometimes). >2. View data stored in #TempTable from another Window I am creating a #TempTable using a script... but want to query it's data from another Window. Currently when I open another window (Unlike Oracle's SQL Developer) it opens the window using a new connection/instance. How do I query the data in #TempTable from another window rather than the window I ran the script in? The above is just simply for organizational reasons... it's SUCH a pain to run a script... and then from that same tab have to then write in other code when I don't want to edit/add any other code to the page that contains the script I ran. If I'm doing something wrong or missing some Point please let me know.

I've one server with a database who stress the server and is blocking other db. Developer has optimized the query, but there we need more power. The SQL server is a VM in a vCloud Director tenant, we think two solution: * **Two Instance on same server**: we add a second instance on same VM. We double the system resource, in second instance we move only the stressed DB, limit CPU e RAM usage, so when the DB is in stress, the first instance have dedicated resource and there's no blocking query, when the second instance is down, the first instance has more resource... as another vantage I dont have to pay another SQL server license... * **Two server**: New dedicated server dedicated to stressed DB... as disadvantage I have to pay another SQL license and the resource are only dedicated to the single DB even when is not under stress... At this time we think the two instance solution is the right way, we are wrong? There are disadvantage that we have not consider? What you think? Thanks in advance for response... Stefano

I came across the following statement in this article : > An instance is the software (and memory) that Oracle uses to > manipulate the data in the database. In order for the instance to be > able to manipulate that data, the instance must open the database. **A > database can be opened (or mounted) by more than one instance,** > however, an instance can open at most one database. Why would one create multiple instances of same database?

I'm trying to answer the following question (asked in a previous exam and supposedly having one correct answer): > To create a DATAFILE and Assign it to a DATABASE, the instance can't be > > 1. started in NOMOUNT mode > 2. started in MOUNT mode > 3. started in OPEN mode > 4. not started To this end, I started reading the relevant Oracle documentation in the article Database and Instance Startup and Shutdown . In the section *How a Database is Mounted*, the following was stated (emphasis mine):
> How a Database is Mounted > = > The instance mounts a database to associate the database with that > instance. To mount the database, the instance finds the database > control files and opens them. Control files are specified in the > CONTROL_FILES initialization parameter in the parameter file used to > start the instance. Oracle then reads the control files to get the > names of the database's datafiles and redo log files. > > At this point, ***the database is still closed and is accessible only to > the database administrator***. The database administrator can keep the > database closed while completing specific maintenance operations. > However, the database is not yet available for normal operations. According to this passage, when the database is mounted it can be accessed by the database administrator. Does *access* here mean that the administrator can only perform read operations, or can also modify the database (e.g. by creating a datafile)?

From Oracle Docs : > Start the instance, and mount and open > the database. This can be done in > unrestricted mode, allowing access to > all users, or in restricted mode, > allowing access for database > administrators only. > > .... > > To mount a database to a previously > started, but not opened instance, use > the SQL statement ALTER DATABASE with > the MOUNT clause as follows: > > ALTER DATABASE MOUNT; The first part gives me the impression that we are mounting the instance to the database. But the *"To mount a database to a previously started, but not opened instance"* part makes me think that we are mounting the database to an instance. What is the correct interpretation when working with Oracle database instances?

The Oracle database includes all the physical files belonging to the server while the Oracle instance will provide the interface between the user and the data he wants to manipulate. But I want to know which one is created first? I had thought the database should be created first, before the instance. But after I read this post [Creating a Database with the CREATE DATABASE Statement](https://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#ADMIN11073) I think I am. Because the manual shows theses instructions: Step 5: (Windows Only) Create an Instance
Step 9: Issue the CREATE DATABASE statement

I'm attempting to setup replication on a new SQL 2019 install but I'm encountering a catch-22 in errors. I have two (2) named instances setup side by side, each setup as a cluster role. The cluster role names, we'll call them: toolsdb catdb As installed, if I run on toolsdb and catdb, respectively: select @@SERVERNAME as ServerName, SERVERPROPERTY('ServerName') as ServerProp ...they return: | ServerName | ServerProp | | ----------- | ----------- | |TOOLSDB\TOOLS|TOOLSDB\TOOLS| | ServerName | ServerProp | | ---------- | ---------- | | CATDB\CAT | CATDB\CAT | The issue I'm encountering happens on either instance, but we'll use toolsdb for now. When I right-click on Replication > Configure Distribution, I get: > =================================== > > SQL Server is unable to connect to server 'toolsdb'. (Configure > Distribution Wizard) > > ------------------------------ For help, click: https://go.microsoft.com:80/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18410.0&EvtSrc=Microsoft.SqlServer.Management.UI.ConfigureWizardErrorSR&EvtID=CantConnect&LinkId=20476 > > =================================== > > SQL Server replication requires the actual server name to make a > connection to the server. Specify the actual server name, ''. > (Replication.Utilities) > > ------------------------------ Program Location: > > at > Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection > conn) at > Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open() > at > Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.PrepareToShow() The resolution to this was to have the value that the @@SERVERNAME function returns, match the cluster role name, using: sp_dropserver 'TOOLSDB\TOOLS' go sp_addserver 'TOOLSDB', LOCAL go Once I restart the role/service, and run this again: select @@SERVERNAME as ServerName, SERVERPROPERTY('ServerName') as ServerProp ...it now returns as expected: | ServerName | ServerProp | | ----------- | ----------- | | TOOLSDB |TOOLSDB\TOOLS| ...and I was able to proceed with replication setup. However, once I went through the Configure Distribution Wizard steps to the end and clicked Finish, the "Configuring the Distributor" action errored out with: > =================================== > > SQL Server could not configure 'toolsdb' as a Distributor. > (Microsoft.SqlServer.ConnectionInfo) > > =================================== > > An exception occurred while executing a Transact-SQL statement or > batch. (Microsoft.SqlServer.ConnectionInfo) > > ------------------------------ Program Location: > > at > Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String > sqlCommand, ExecutionTypes executionType, Boolean retry) at > Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String > commandIn) at > Microsoft.SqlServer.Replication.ReplicationServer.InstallDistributor(String > password, DistributionDatabase distributionDB) at > Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.InstallDistributor(WizardInputs > inputs, Boolean bScripting) at > Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.InstallDistributor(Boolean& > anyExceptions, Boolean bScripting, ApplicationException& outerEx, > StringBuilder command) > > =================================== > > Could not connect to server 'TOOLSDB\TOOLS' because > 'distributor_admin' is not defined as a remote login at the server. > Verify that you have specified the correct login name. . Changed > database context to 'master'. (.Net SqlClient Data Provider) > > ------------------------------ For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18483-database-engine-error > > ------------------------------ Server Name: toolsdb Error Number: 18483 Severity: 14 State: 1 Line Number: 1 > > > ------------------------------ Program Location: > > at > Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction > action, Object execObject, DataSet fillDataSet, Boolean > catchException) at > Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String > sqlCommand, ExecutionTypes executionType, Boolean retry) From the research I've read: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18483-database-engine-error ...it appears that the value that the @@SERVERNAME function returns needs to match the instance name, e.g.- TOOLSDB\TOOLS, but I cannot start the wizard with it set to this value. Any ideas? Thank you.

Could you please help to explain why one might need multiple instances of SQL Server on the same server? What is a good point to use separate instances? Why wouldn't one use two databases in one instance instead? At any point is it a good idea to have separate multiple instance in the same server?

I have a SQL Server 2008 R2 server with several instances installed. Is there a way to add a new instance and have it be to the same build as the existing (and updated / patched) instances?