So I was recently working with mysqld_multi and I realized that this was a feature that I do not see in very many blog posts these days. They do exist and I have listed some at the bottom of this post for your reference.
Your reasons are likely to vary and also be debatable when it comes to the concept of: should run more than one MySQL instance on the same hardware.
To avoid any confusion, if you want to install another MySQL instance for testing purposes and not as a production instance, then you should just work with MySQL Sandbox. If that for some reason that does not work, you can execute another server like many people typically do: create new my.cnf files and start the mysql server with the mysqld_safe and custom commands.
Mysqld_multi makes it a lot easier for you to run multiple servers.
For example:
You have a secondary server running on port 3306. It is a read_only slave and you have a lot of hardware in place waiting to become the new primary server when the current primary fails. You also would like to take advantage of the Percona toolkit and have a replicated secondary server that is running in a delayed mode. If you could update to MySQL 5.6 then you would not need pt-slave-delay but currently that is not an option.
In either case, you have budget limits and are not allowed another server. So do you give up? You have the disk space to hold another version of the server on your secondary box so why not? The idea of having to start and stop custom versions and etc can be off putting to some. So instead you can set up a new version of the my.cnf file but first you can do the following.
Pick you favorite editor (ie: vi )
Now you can take the [mysqld] section from your my.cnf file and copy it into this location.
If you use the command above edit to clean up so you just have the [mysqld] section copied over.
You can then create the 3307 port section.
And example of the configuration can be found here:Your reasons are likely to vary and also be debatable when it comes to the concept of: should run more than one MySQL instance on the same hardware.
To avoid any confusion, if you want to install another MySQL instance for testing purposes and not as a production instance, then you should just work with MySQL Sandbox. If that for some reason that does not work, you can execute another server like many people typically do: create new my.cnf files and start the mysql server with the mysqld_safe and custom commands.
Mysqld_multi makes it a lot easier for you to run multiple servers.
For example:
You have a secondary server running on port 3306. It is a read_only slave and you have a lot of hardware in place waiting to become the new primary server when the current primary fails. You also would like to take advantage of the Percona toolkit and have a replicated secondary server that is running in a delayed mode. If you could update to MySQL 5.6 then you would not need pt-slave-delay but currently that is not an option.
In either case, you have budget limits and are not allowed another server. So do you give up? You have the disk space to hold another version of the server on your secondary box so why not? The idea of having to start and stop custom versions and etc can be off putting to some. So instead you can set up a new version of the my.cnf file but first you can do the following.
Pick you favorite editor (ie: vi )
vi /etc/multi_my.cnf
[mysqld_multi]
mysqld = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user = mysql
log = /var/log/multi_mysql.log
# Port 3306 Server
[mysqld1]
>socket = /tmp/mysql_3306.sock
port = 3306
pid-file = /var/lib/mysql/mysql_3306.pid
datadir = /var/lib/mysql/
user = mysql
cat /etc/my.cnf >> /etc/multi_my.cnf
You can then create the 3307 port section.
# Port 3307 Server
[mysqld2]
socket = /tmp/mysql_3307.sock
port = 3307
pid-file = /var/lib/mysql2/mysql_3307.pid
datadir = /var/lib/mysql2/
user = mysqlhttp://dev.mysql.com/doc/refman/5.6/en/mysqld-multi.html
For this example I will assume you will create a backup of Port 3306 server with Percona Xtrabackup and place it into the new datadir.
innobackupex --defaults-file=/etc/my.cnf --user=root --password=<password> --port=3306 --no-timestamp /var/lib/mysql2/
innobackupex --apply-log /var/lib/mysql2/
You can copy this to your init.d directory or test it from the current location.
The script will default to the /etc/my.cnf file. So to start test this with the --default_file=/etc/multi_my.cnf report
The report option is the similar to the status argument to see if the server is running. If you choose to run this as the default process you can symlink or copy the /etc/multi_my.cnf as the new /etc/my.cnf
/etc/init.d/mysqld_multi.server report 1,2
/etc/init.d/mysqld_multi.server report 1
/etc/init.d/mysqld_multi.server report 2
The above would then give you running status for each give argument that of course references to a different MySQL instance. You can do the same the all of the following options : {start|stop|report|restart}
If everything went well you can "start 2" which will start the instance on port 3307. Then log in and change master with the binlog position information provided in the xtrabackup_binlog_info file.
CHANGE MASTER TO
MASTER_HOST='localhost',
MASTER_PORT=3306,
MASTER_LOG_FILE='<log filename>',
MASTER_LOG_POS=<position>;
Start slave;
pt-slave-delay --port=3307 --socket=/tmp/mysql_3307.sock --host=localhost
Hope this can at least get you started.
