Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Is there a way to create a new instance on localhost on a different port?

T
Trenton last edited by

I would like to create a new instance on MySql and use a different port on localhost. Is that possible or MySql only runs locally on 3306? Why?

I could not find any articles related to that.

I am using MySql Workbench on Ubuntu 20.04.
Reply Quote 0
1 Reply Last reply
K
kalena last edited by

How to run multiple instances of MySQL Server on one server is in the MySQL manual: https://dev.mysql.com/doc/refman/8.0/en/multiple-servers.html
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

What's the most effective way to duplicate an instance on SQL 2008?
SQL, Database Testing • sql server 2008 instanc languagee • • Mystic

2

0
Votes

2
Posts

0
Views

S

You've hit the nail on the head: copy the files. I've used this to good effect. I'd say you have to define "cheap" to include investment of time to prepare a solution that doesn't involve xcopy
N

How to optimize this query?
SQL, Database Testing • mysql performance query performance index tuning • • Nykeriab

2

0
Votes

2
Posts

0
Views

T

Your query has 5 equality conditions in 5 different columns: select * from t_event where create_user_id=7 and event_create_date = '2012-12-18 00:00:00' and event_type_cd = 11 and domain_id = 602 and job_id =1 limit 1 ; -- why is this for? Now you have 2 indexes (domain_id_event_type_cd and event_user_FK), which cover only 3 of the 5 column/conditions of the query. So the plan that the optimizer chooses is an index_merge operation, e.g. it uses the two indexes to find rows that match the 2 and 1 conditions respectively, then intersects them (Using intersect) to find the common rows and then has to do an additional search on the table (Using where) for the rest 2 conditions that are not covered by the indices. You could add a compound index on all those columns. The order does not matter at all for this query, it matters for other queries you may have and whether it could be used for them: ALTER TABLE t_event ADD INDEX i5 (domain_id, event_type_cd, create_user_id, event_create_date, job_id) ; Then the Explain would show only Using index and only the above index would be used.
pros/cons of different ways to store whether a record is one of two options?
SQL, Database Testing • sql server database design best prac languagetic lan • • Analeea

2

0
Votes

2
Posts

0
Views

D

If you are really sure that those types don't change (or at least not often) you can also use a variation of 3) using a column AddressType of type varchar with a check constraint that limits the values to 'work' and 'home'. This is less flexible than a lookup table, but still better than a boolean that has some implicit meaning when set to false. And you wouldn't need the join (although I doubt a join against a table with two rows will be noticable)
B

Group By on column from joined table
SQL, Database Testing • mysql index join group by • • Burma

2

0
Votes

2
Posts

0
Views

C

Probably because the table "dict_verbs_german" is the last table joined. When I last researched this, "using temporary; using filesort" meant MySQL was writing the the join result into a temporary table, then sorting. (See note below.) In order to avoid "using temporary; using filesort", you'd have to meet all these conditions. dict_verbs_german is the first non-constant table in the JOIN order, and dict_verbs_german has an index that marches the ORDER BY column list. To avoid "using temporary", you'd have to meet all these conditions. dict_verbs_german is the first table in the JOIN order, and dict_verbs_german has an index that marches the ORDER BY column list. Note: I'm talking about ORDER BY, because that's specifically what my research dealt with, and I'm pulling these notes from an email I wrote in 2010. I think the same optimizations applied to GROUP BY, but I could be wrong. Improvements to the optimizer over the last couple of years might have changed things, too. MySQL documentation In some cases, MySQL cannot use indexes to resolve the ORDER BY . . .. These cases include . . . joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.) MySQL documentation for optimizing limit queries (I don't think this last reference will help you with this problem, but it's good to know where to find it.) Later . . . Cutting the WHERE clause seems to eliminate the filesort. (That might not be true for real-world data.) Maybe you can create a view without a WHERE clause and a LIMIT clause, then query the view and still avoid a filesort. I'm sorry that, for personal reasons, I'm not able to test that for you right now.
INNER JOIN with where
Software Programming • php mysql sql join • • emmalee

2

0
Votes

2
Posts

0
Views

O

Try this. SELECT PU.ID_USER, P.PERMISSAO FROM PERMISSOES_USERS PU INNER JOIN PERMISSOES P ON PU.ID_PERMISSAO = P.ID WHERE PU.ID_USER = 1 The errorNotice: Undefined index: permission in /Applications/XAMPP/xamppfiles/htdocs/DEV WEB/FinalBD/permission. php online 51It's because here $permissao = $row["permissao"] you are trying to access the column permissao but the column name is PERMISSAO (with capital letters).
T

Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based
SQL, Database Testing • mysql replication mariadb 10.1 binlog row • • tahishae

2

0
Votes

2
Posts

0
Views

O

You have encountered one of the reasons for saying that replication must not go from a newer Master to an older Slave. Would you care to explain the ultimate goal; maybe we can discuss a workaround.
R

Is there a way to set different destination order with mysql-router?
SQL, Database Testing • mysql innodb mysql c languageluster mysqlrouter • • Rossere

2

0
Votes

2
Posts

0
Views

E

using different specific host order, rather than using metadata-cache in mysql-router config "destination" option on each mysql-router seems to do it. ie. destination=server1,server2,server3
C

Updating MySQL records with ON DUPLICATE KEY UPDATE excluding empty cells
Software Programming • php mysql database xls • • charlenef

2

0
Votes

2
Posts

0
Views

There are empty cells in the records.What's in the emerging request to VALUES section if there's a empty cell?INSERT INTO `orders` (`id`,`name`,`status`) VALUES ('1','Иван','OK'),('2','Марья','') Right?If that's what you're doing, put Null in the ODKU section instead of an empty line, and then you're gonna have to make a cut:INSERT INTO `orders` (`id`,`name`,`status`) VALUES ('1','Иван','OK'),('2','Марья',Null) ON DUPLICATE KEY UPDATE `name` = COALESCE(VALUES(`name`),`name`), `status` = COALESCE(VALUES(`status`),`status`);
J

Difference between accessing SQL Server by instance name and cluster name
SQL, Database Testing • sql server availability groups failover • • jonetta

2

0
Votes

2
Posts

0
Views

G

Clustering works very differently in 2012, and using your knowledge from SQL Server 2008 could be confusing you. Try the following from PowerShell (as admin): Import-Module failoverclusters Get-ClusterGroup If you see something like: Name OwnerNode State ---- --------- ----- Cluster Group server1 Online Then you haven't configured AlwaysOn yet - all you've done is set up an initial cluster. In this case, connecting to cluster name/IP is meaningless. All it will do is connect you to the server that owns the Windows Failover Cluster, which is arbitrary and completely unrelated to your database configuration. Once you've correctly configured your SQL Server AlwaysOn Availability groups - basically, additional mini-clusters with their own name and IP address - then the additional names/IP addresses will be meaningful. For example, you might have three databases configured like this: Server1: Database1 (Active - Availability group sqlcluster1) Database2 (Active - Availability group sqlcluster1) Database3 (Standby - Availability group sqlcluster2) Server2: Database1 (Standby - Availability group sqlcluster1) Database2 (Standby - Availability group sqlcluster1) Database3 (Active - Availability group sqlcluster2) At this point, the server you're connecting to becomes extremely important. You will never want to use applications to directly connect to Server1 or Server2, because which databases are online and which are in recovery will depend on when you last failed over. Instead, all of your applicatons should connect to sqlcluster1 or sqlcluster2 (which have their own IP addresses), and they will automatically reach the database server that currently has that database online. Additional configuration of your server will be required if you don't have any Availability Groups set up yet.
A

How to do Sub Select in SQL
Software Programming • mysql sql • • Avante

2

0
Votes

2
Posts

0
Views

Answering your question:What you want is not subselect and yes JOIN.Example JOIN considering that your sale table will always have 1 book pertaining to sale:SELECT * FROM VENDAS VD INNER JOIN LIVROS LV ON LV.IDLIVRO = VD.IDLIVRO Filtering some fields:SELECT VD.IDVENDA, LV.TITULO, LV.PRECO FROM VENDAS VD JOIN LIVROS LV ON LV.IDLIVRO = VD.IDLIVRO (In the examples see I used INNER JOIN and JOIN. In this case it works the same way, because using only JOIN he assumes as if he is INNER.Extra examples:To bring ALL values Table VENDAS even No have filled the field IDLIVRO in table VENDAS, use the LEFT JOIN:SELECT * FROM VENDAS VD LEFT JOIN LIVROS LV ON LV.IDLIVRO = VD.IDLIVRO To bring ALL values Table LIVROS even No have filled the field IDLIVRO in table VENDAS, use the RIGHT JOIN:SELECT * FROM VENDAS VD RIGHT JOIN LIVROS LV ON LV.IDLIVRO = VD.IDLIVRO Reference JOINS that it would be nice to see as an example: https://pt.stackoverflow.com/a/274447/92908
C

Receipt of MySQL data only
Software Programming • php mysql sql database pdo • • Cleofas

2

0
Votes

2
Posts

0
Views

A

Follow-up https://ru.stackoverflow.com/users/180398/dmitry to remove the problem of the facility for each individual user site. The code applied to the OBD knows what site it is for. However, the author wishes to avoid the permanent transmission of the website ' s name (not importantly as the parameters of the request or static). The name of the site could be contained in a sessional variable, but no variables are allowed in the presentation.The solution may be a user function that returns the variable value that is allowed in the submission. TypeCREATE FUNCTION getsite() DETERMINISTIC NO SQL RETURNS VARCHAR(128) RETURN @site; and respectivelyCREATE VIEW sitedata() AS SELECT * FROM alldata WHERE site = getsite(); Then when we set up the connection, we immediately initiate a sessional variable, like SET @site = 'www.mysite.ru' and we turn to the vouvs, which within our compound will only receive data for the variable site.The castle, of course... and we need to check how it's going to behave when the compound is broken and re-established... and it doesn't apply if the pool of compounds is used.
R

Pass data from one database to another with different tables (MySql)
Software Programming • mysql • • rielyn

2

0
Votes

2
Posts

0
Views

R

In the end I have done it the following way:INSERT INTO newDatabase.table1 (Column1, Column2) SELECT column1, column2 FROM oldDatabase.table1; Thank you for the answers
T

Backup not creating a new file
SQL, Database Testing • sql server 2008 backup • • tahishae

2

0
Votes

2
Posts

0
Views

R

Instead of writing your own backup solution, I would suggest you to look at SQL Server Maintenance Solution by Ola Hallengren. If you want to use sqlcmd then you can do as below: Save below file as backup.sql DECLARE @DatabaseName sysname = N'test_kin' ,@DatabaseBackupFileName varchar(255); SET @DatabaseBackupFileName = 'C:\test\' + @DatabaseName + REPLACE(CONVERT(char(8), GETDATE(), 103), '/', '_') + '.bak'; BACKUP DATABASE @DatabaseName TO DISK = @DatabaseBackupFileName with init, stats =10; Then using sqlcmd you can do as below : sqlcmd -S ServerName\InstanceName -i C:\YourScriptDir\Backup.sql
Is there a less manual way of changing database properties for all databases in an instance?
SQL, Database Testing • sql server 2008 r2 t sql • • irl

2

0
Votes

2
Posts

0
Views

S

I often generate T-SQL like this: select 'ALTER DATABASE ' + quotename(name) + ' MODIFY FILE (...)' from sys.databases where databaseIsMisconfigured = 1 Select the entire column, past it into a new window and F5.
How to avoid duplicate records in my database?
Software Programming • php mysql html5 mysqli • • Demir

2

0
Votes

2
Posts

0
Views

A

It is important to know if your primary key, or user identifier in this case, is numerical and has set the auto option to increase with each new record. If this were the case, you should do the prior consultation as Christopher told you, to know if that ID already exists on the table.If on the contrary, your identifier is not autonumeric, you have two paths to choose:A) Do the same thing Christopher already told you, to find out if the ID already exists in the table with a simple SELECT query.B) Do not do the previous query, but instead, capture the Error that will throw you the database, regarding that you are trying to enter a user that already exists. This error is usually known as "intent to insert a duplicate key" (duplicated key). Search Mysql's documentation for server error codes, and get the exact error number for this situation. Then, in your code, with mysqli_errno() or affin function you get the error code, and if it's the one you know, you tell the user that is using the page that the user already exists.***With option B) you do not need to check if the registration exists previously.In your code, this capture would go here: $resultado = mysqli_query($conexion,$insertar); if (!$resultado) { $error_id_duplicado = XXXX; // tú reemplaza las X con el código que corresponde, investígalo en https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html if (mysqli_errno($conexion) == $error_id_duplicado) { echo '<script> alert("El funcionario Ya Existe."); </script>'; } else { echo '<script> alert("El funcionario NO fue registrado"); </script>'; } }else{ echo '<script> alert("El funcionario fue registrado"); </script>'; }

Is there a way to create a new instance on localhost on a different port?

Suggested Topics

What's the most effective way to duplicate an instance on SQL 2008? SQL, Database Testing • sql server 2008 instanc languagee • • Mystic

How to optimize this query? SQL, Database Testing • mysql performance query performance index tuning • • Nykeriab

pros/cons of different ways to store whether a record is one of two options? SQL, Database Testing • sql server database design best prac languagetic lan • • Analeea

Group By on column from joined table SQL, Database Testing • mysql index join group by • • Burma

INNER JOIN with where Software Programming • php mysql sql join • • emmalee

Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based SQL, Database Testing • mysql replication mariadb 10.1 binlog row • • tahishae

Is there a way to set different destination order with mysql-router? SQL, Database Testing • mysql innodb mysql c languageluster mysqlrouter • • Rossere

Updating MySQL records with ON DUPLICATE KEY UPDATE excluding empty cells Software Programming • php mysql database xls • • charlenef

Difference between accessing SQL Server by instance name and cluster name SQL, Database Testing • sql server availability groups failover • • jonetta

How to do Sub Select in SQL Software Programming • mysql sql • • Avante

Receipt of MySQL data only Software Programming • php mysql sql database pdo • • Cleofas

Pass data from one database to another with different tables (MySql) Software Programming • mysql • • rielyn

Backup not creating a new file SQL, Database Testing • sql server 2008 backup • • tahishae

Is there a less manual way of changing database properties for all databases in an instance? SQL, Database Testing • sql server 2008 r2 t sql • • irl

How to avoid duplicate records in my database? Software Programming • php mysql html5 mysqli • • Demir

What's the most effective way to duplicate an instance on SQL 2008?
SQL, Database Testing • sql server 2008 instanc languagee • • Mystic

How to optimize this query?
SQL, Database Testing • mysql performance query performance index tuning • • Nykeriab

pros/cons of different ways to store whether a record is one of two options?
SQL, Database Testing • sql server database design best prac languagetic lan • • Analeea

Group By on column from joined table
SQL, Database Testing • mysql index join group by • • Burma

INNER JOIN with where
Software Programming • php mysql sql join • • emmalee

Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based
SQL, Database Testing • mysql replication mariadb 10.1 binlog row • • tahishae

Is there a way to set different destination order with mysql-router?
SQL, Database Testing • mysql innodb mysql c languageluster mysqlrouter • • Rossere

Updating MySQL records with ON DUPLICATE KEY UPDATE excluding empty cells
Software Programming • php mysql database xls • • charlenef

Difference between accessing SQL Server by instance name and cluster name
SQL, Database Testing • sql server availability groups failover • • jonetta

How to do Sub Select in SQL
Software Programming • mysql sql • • Avante

Receipt of MySQL data only
Software Programming • php mysql sql database pdo • • Cleofas

Pass data from one database to another with different tables (MySql)
Software Programming • mysql • • rielyn

Backup not creating a new file
SQL, Database Testing • sql server 2008 backup • • tahishae

Is there a less manual way of changing database properties for all databases in an instance?
SQL, Database Testing • sql server 2008 r2 t sql • • irl

How to avoid duplicate records in my database?
Software Programming • php mysql html5 mysqli • • Demir