MySQL 5.5.58 Installing Multiple Instances on RHEL7

MySQLmysql-5.5mysqld-multi

I am Avinash.

I am willing to install an additional MySQL instance on a UAT server for testing purposes. I am using MySQL Community Server 5.5.58 installed on RHEL7.2.

I am going through the MySQL Docs https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html , which tells me that I would require certain unique operating parameters such port, socket, pid, datadir and log files related parameters. To achieve better performance, it also recommends to use different tmpdir. This is well understood.

I can either use different binaries or the same binary for this purpose.

If I am using same binary, I'll have to use mysqld_multi and use the same config file. If I use mysqld_multi, I'll have to put different sections for mysqld daemon such as mysqld1, mysqld2 and so on. All using unique values for above mentioned operating parameters.

Please help me out here. Am I correct about mysqld_multi?

My second question is about bind-address variable.
https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address
Here, it explains that "The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces."

My question is that will I need an additional NIC and an additional IP address for the second MySQL instance?

For the existing instance, I have disabled the bind-address option.
Do I need to add the bind-addresses for both the instances.

Also, the second instance will be used only by me (for now).

Thanking you.

Best Answer

why dnt you start new instance with different cnf file (keeping diffrent PORT and diffrent socket file)

for example :

cd /opt/mysql_installation/u01/mysql_5.7.17/bin/ ./bin/mysqld_safe --defaults-file=/opt/mysql_installation/my.cnf &

Related Solutions

MySQL Cluster – strange syntax error

CREATE LOGFILE GROUP and CREATE TABLESPACE accept only bytes as unit.

http://bugs.mysql.com/bug.php?id=62858

With MB converted to bytes logfile group was created successfully for me.

Mysql – Why/when does creating a thesql trigger require SUPER privileges

With regard to SUPER and TRIGGER privileges, you need the TRIGGER privilege to create triggers on tables you have rights for.

If you play binlogs, there is the possibility of seeing commands to create triggers on tables you do not have rights for. This is implied from the MySQL Documentation:

CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in Section 19.7, “Binary Logging of Stored Programs”.

With regard to your second point

trusted not to create stored functions that will cause unsafe events to be written to the binary log.

There are occasions when certain SELECTs on a Master may yield different results on a Slave. For example, let's say you ran this query on a a MASTER

SELECT * FROM tbl WHERE name='...' LIMIT 10;

How could a SLAVE produce different results?

If you have a several hundred INSERTs commands occur on a Master, they get serialized in the Binary Logs. Even if a DB Connection 1 completes an INSERT before DB Connection 2, it is possible for DB Connection 2 to write its event in the Master's Binary Logs before DB Connection 1. This is particularly true in a write-intensive, heavy OLAP environment. This would make a Slave's copy of tbl physically out-of-sync with its Master. Running queries can hide this.

OK, you are probably thinking it's just a SELECT.

SELECTs are not in Binary Logs. INSERTs, DELETEs, and UPDATEs are.

Did you know you could do a query like this ?

DELETE FROM tbl LIMIT 10;

Replicating this can really make tbl out of sync.

Stored Functions can also have this headache if it the code within it is not DETERMINISTIC. This is why log_bin_trust_function_creators was invented. This alleviates mysqld of the responsibility of figuring out if data operations are synchronized in terms of replication. Yes, they fudged their way around downstream replication issues.

Related Question