[Tutorial] Create Multiple Istance for MySQL on Linux

[destiny71]

Sorry for my poor english.

I at all, write this tutorial for helping other people to create a MySQL specific istance for DOL.

Create new instances with MySQL is not difficult since fortunately provides a command called mysqld_multi and the possibility to use the same configuration file for all instances or use a different file specific whit directive --defaults-file and in this tutorial we will use exactly this technique.

Note: Tested this step on ubuntu (13.04 / 13.10 64bit).

Create specific directory
If not present you need to create a new directory called mysql_multi in /etc/mysql

Code: Select all

sudo mkdir /etc/mysql/mysql_multi

Create alias
Open your .bashrc whit preferred editor and add this line

Code: Select all

alias mysqld_multi='mysqld_multi --defaults-file=/etc/mysql/mysql_multi/my.cnf'

Mark a specific istance in /etc/mysql/mysql_multi/my.cnf
To be properly invoked the new istance must be identified by a number (called GNR) that separate it from the others present. For example the section of the mysql in new my.cnf file takes the name of mysql1.

Create /etc/mysql/mysql_multi/my.cnf and add the following line

Code: Select all

[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin log = /var/log/multi_mysql.log [mysqld1] user = mysql pid-file = /var/run/mysqld/mysqld1.pid socket = /var/run/mysqld/mysqld1.sock basedir = /usr datadir = /var/lib/mysql1 tmpdir = /tmp lc-messages-dir = /usr/share/mysql log-error = /var/log/mysqld1.log key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M skip-grant-tables

Note: In the final step to be removed the skip-grant-tables directive.

Create mysql database
Now we need to create the initial database for new instance.

Code: Select all

sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql1

Run the new istance

Code: Select all

sudo mysqld_multi start

At this point, as requested in the instructions printed by mysql_install_db, its necessary to set an access password for the root user of the database you just created. The installation, however, restricts access to localhost and the hostname of the main machine, so it can not be accessed using a different IP address and the commands shown by mysql_install_db would not work.
For this you need to start the first time the service with the directive skip-grant-tables, so you can access however to the database and make the necessary corrections than installed by mysql_install_db. Now connect at mysql whit the following command

Code: Select all

sudo mysql -h mysql1.hostname -u root

and execute this command

Code: Select all

DROP DATABASE test; use mysql DELETE FROM user WHERE user=''; UPDATE user SET host='mysql1.hostname' WHERE host='your-pc-name'; UPDATE user SET Password=PASSWORD("your_preferred_password") WHERE User="root";

After this command exit from mysql console, stop the mysql1 istance, remove skip-grant-tables directive from your /etc/mysql/mysql_multi/my.cnf file and normaly restart mysql1 istance.

InnoDB
InnoDB is enabled by default for files larger than 10MB but unfortunately do not yet know enough about the operation of DOL and its database so I can give advice about the best configuration I could be wrong completely.

Have fun

[Leodagan]

In my Topic about running DOL on Linux you can find some config for InnoDB engine :

http://www.dolserver.net/viewtopic.php?f=5&t=21517

Code: Select all

# Total Memory dedicated to innoDB (will use more) innodb_buffer_pool_size = 1024M # use system memory alocation (faster on recent systems) innodb_use_sys_malloc = 1 # Number of instances for Memory pool, raise this if buffer_pool_size is set to 8G or more innodb_buffer_pool_instances = 2 # this is the default, increase it if you have lots of tables innodb_additional_mem_pool_size = 128M # the max is there to avoid run-away growth on your machine innodb_data_file_path = ibdata1:10M:autoextend:max:20G # we keep this at around 25% of of innodb_buffer_pool_size innodb_log_file_size = 256M # for large transactions innodb_log_buffer_size = 32M # see the innodb config docs, the other options are not always safe # This will delay disk sync prevent iowait, but data can be lost on crash, improves performances dramatically !! innodb_flush_log_at_trx_commit = 0

This should be fit to the targeted server...

[destiny71]

I Leodagan i see your post (tanks for it) but your configuration for InnoDB not good for MySQL.

innnodb_log_file_size = 256M is wrong and MySQL will not start and setting 128M same problem.

Code: Select all

2014-03-10 11:08:01 - Checked server status: Server is stopped. 2014-03-10 11:08:01 - Starting server... 2014-03-10 11:08:01 - Start server: 2014-03-10 11:08:01 - Start server: * Starting MySQL database server mysqld 2014-03-10 11:08:15 - Start server: ...fail! 2014-03-10 11:08:15 - Start server: 2014-03-10 11:08:15 - Checked server status: Server is stopped. 2014-03-10 11:08:15 - Server start done. 2014-03-10 11:08:15 - Checked server status: Server is stopped.

whitout this options work fine

Code: Select all

2014-03-10 11:17:19 - Checked server status: Server is stopped. 2014-03-10 11:17:19 - Starting server... 2014-03-10 11:17:19 - Start server: 2014-03-10 11:17:19 - Start server: * Starting MySQL database server mysqld 2014-03-10 11:17:22 - Start server: ...done. 2014-03-10 11:17:22 - Start server: * Checking for tables which need an upgrade, are corrupt or were 2014-03-10 11:17:22 - Start server: 2014-03-10 11:17:22 - Start server: not closed cleanly. 2014-03-10 11:17:22 - Start server: 2014-03-10 11:17:22 - Checked server status: Server is running. 2014-03-10 11:17:22 - Server start done. 2014-03-10 11:17:22 - Checked server status: Server is running.

--------------------
Destiny

[Leodagan]

sure

You need to remove the existing Innodb log files before changing their size (make sure you had a clean shutdown first !)

Existing innodb log file should be in : ${datadir}/ib_logfile[01]

${datadir} is the path configured in my.cnf

[destiny71]

sure

You need to remove the existing Innodb log files before changing their size (make sure you had a clean shutdown first !)

Existing innodb log file should be in : ${datadir}/ib_logfile[01]

${datadir} is the path configured in my.cnf

Of course it must be removed before the file but only if there is. I did that test from a copy of MySQL in a test environment on VirtualBox where up to that time had not yet been installed MySQL and the log file does not exist and it gave me problems from the start. After try again.

[Leodagan]

Could it be because you spelled "innnodb_log_file_size" with 3 n's ?

[destiny71]

That is an error of my writing. The configuration I've done with MySQL WorkBench

[destiny71]

That is an error of my writing. The configuration I've done with MySQL WorkBench

[Graveen]

Thanx ! on a side note, it was discussed the huge benefits of InnoDB on a box with enough ram. And it was impressive.

On my side i have totally switched on MariaDB. It is a binary replacement for MySQL (so it works out of the box) and, although i can't say precisely if it is, in our case, better or worse than MySQL - given it depends a lot of parameters - i don't had a single issue for all my project involving MySQL.

[Blue]

Why would anybody want multiple instances of mysql instead having multiple databases inside one instance? Sounds like wasting of resources.

[Leodagan]

I use multiple Instance MySQL for different "task" or "security" related environment

for example on a production Server I have a MySQL Instance used to dynamically configure Apache / DNS Server / FTP Account / Mail account, this instance doesn't listen on TCP/IP, has very low memory setup (I'd rather have a slow FTP account resolution than having a DB Instance eating memory for cache that is only used on service startup...) and have it's filesystem on a secure (but slow) Mirrored RAID...

An other instance is dedicated to Website and other "open" services (PhpBB, Wordpress, Wiki...), it listen on public TCP/IP for remote access with Admin Tools, have an average memory setup to be reactive enough but don't use too much server ressources...

Lastly an instance for DOL would be closed to TCP/IP (prevent hack attempt) and have a HUGE memory setup to give the most responsiveness for the Game Server, and even multiple thread pool to allow for more CPU Power (options available to MariaDB mostly...) and the filesystem would be on a fast Stripped RAID (with frequent backup)

If I would have a single Instance for all these tasks, one successful hack could drop all my services or corrupt them (password leak...), the dedicated resources could be taken by a DDOS or Brute Force that is launched against a web page and this would impact Game Server performances, and I'll had to choose between Fast unsecure filesystem or Slow secure filesystem (ok I could use mount points under the mysql_data_dir to take care of this... but I'm not sure the daemon will still run if one database become unstable)

Well there could be other reason to use multiple instanced MySQL services

[Ephemeral]

As a side note you can use Percona for better performance with MySQL on *nix

http://www.percona.com/

Its worth a shot for extra performance.