Crear instancias Mysql MariaDB Ubuntu Server

Seguro que te has visto en la necesidad de tener más de un MariaDB, o base de datos basada MySql, para poder trabajar en diferente proyectos de desarrollo simultáneamente y tener las bases de datos completamente independientes.

Para ello, la mejor solución es crear diferentes instancias. He creado esta guía paso a paso que os servirá tanto para MariaDB como otras bases de datos basadas en MySql desplegadas en Ubuntu Server.

En esta guía vamos a montar dos instancias de MariaDB, una en el puerto 3308 y otra en el 3309. Recordad que el puerto por defecto es 3306 por tanto no montéis una instancia en ese puerto dado que en caso contrario creará conflicto con la instancia instalada por defecto que se crea al instalar MariaDB.

Privilegios de superuser

Lo primero que deberemos hacer es conseguir privilegios de administrados. Para ello teclearemos el siguiente comando:

$sudo su $(meteréis vuestra contraseña y enter) #(ahora estaréis como superusuario)

Detener servicio mysql

Lo siguiente que debemos hacer es detener el servicio mysql en nuestro servidor.

# service mysql stop

Directorios de datos

Ahora crearemos los directorios de datos. Los podéis crear donde convengáis. En mi caso voy a hacerlo en el directorio del usuario.

#cd /home/<tu_usuario> #mkdir mysql #mkdir 3308 #mkdir 3308/data #mkdir 3308/log #mkdir 3309 #mkdir 3309/data #mkdir 3309/log #chown -R mysql /<tu_usuario>/zeus/mysql

Archivos de configuración

Continuamos generando los archivos de configuración. Que no es más que copiar el archivo de configuración de la instancia ya instalada en nuestros directorios. Después lo modificaremos.

#cp /etc/mysql/mariadb.conf.d/50-server.cnf /home/<tu_usuario>/mysql/3308/server.cnf #cp /etc/mysql/mariadb.conf.d/50-server.cnf /home/<tu_usuario>/mysql/3308/server.cnf

Modificar archivos configuración

Para que todo funcione correctamente deberemos configurar los archivos, previamente copiados, adecuadamente para cada instancia. Recordad que el objetivo es montar una instancia en el puerto 3308 y otra en el 3309.

Los parámetros que vamos a cambiar son los siguientes

pid-file = /var/run/mysqld/mysqld<número_puerto>.pid socket = /var/run/mysqld/mysqld<número_puerto>.sock port = <numero_puerto> datadir = /home/<tu_usuario>/mysql/<número_puerto>/data (más abajo) log_error = /home/<tu_usuario>/mysql/<número_puerto>/log/error.log

Los archivos os deberían de quedar así.

Instancia 3308

# # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld3308.pid socket = /var/run/mysqld/mysqld3308.sock port = 3308 basedir = /usr datadir = /home/<tu_usuario>/mysql/3308/data tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = * # # * Fine Tuning # key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam_recover_options = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error log - should be very few entries. # log_error = /home/<tu_usuario>/mysql/3308/log/error.log # # Enable the slow query log to see queries with especially long duration #slow_query_log_file = /var/log/mysql/mariadb-slow.log #long_query_time = 10 #log_slow_rate_limit = 1000 #log_slow_verbosity = query_plan #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = exclude_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates you can use for example the GUI tool "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem # # Accept only connections using the latest and most secure TLS protocol version. # ..when MariaDB is compiled with OpenSSL: # ssl-cipher=TLSv1.2 # ..when MariaDB is compiled with YaSSL (default in Debian): # ssl=on # # * Character sets # # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full # utf8 4-byte character set. See also client.cnf # character-set-server = utf8mb4 collation-server = utf8mb4_general_ci # # * Unix socket authentication plugin is built-in since 10.0.22-6 # # Needed so the root database user can authenticate without a password but # only when running as the unix root user. # # Also available for other users if required. # See https://mariadb.com/kb/en/unix_socket-authentication-plugin/ # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.1 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.1]

Instancia 3309

# # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld3309.pid socket = /var/run/mysqld/mysqld3309.sock port = 3309 basedir = /usr datadir = /home/<tu_usuario>/mysql/3309/data tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = * # # * Fine Tuning # key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam_recover_options = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error log - should be very few entries. # log_error = /home/<tu_usuario>/mysql/3309/log/error.log # # Enable the slow query log to see queries with especially long duration #slow_query_log_file = /var/log/mysql/mariadb-slow.log #long_query_time = 10 #log_slow_rate_limit = 1000 #log_slow_verbosity = query_plan #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = exclude_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates you can use for example the GUI tool "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem # # Accept only connections using the latest and most secure TLS protocol version. # ..when MariaDB is compiled with OpenSSL: # ssl-cipher=TLSv1.2 # ..when MariaDB is compiled with YaSSL (default in Debian): # ssl=on # # * Character sets # # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full # utf8 4-byte character set. See also client.cnf # character-set-server = utf8mb4 collation-server = utf8mb4_general_ci # # * Unix socket authentication plugin is built-in since 10.0.22-6 # # Needed so the root database user can authenticate without a password but # only when running as the unix root user. # # Also available for other users if required. # See https://mariadb.com/kb/en/unix_socket-authentication-plugin/ # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.1 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.1]

Levantar servicio e instancias

Si has llegado has aquí eres un héroe. Ahora solo nos queda levantar las instancias. Para ello teclearemos los siguientes comandos:

# service mysql start # mysqld_safe --defaults-file=/home/<tu_usuario>/mysql/3308/server.cnf & mysqld_safe --defaults-file=/home/<tu_usuario>/mysql/3309/server.cnf

Puede ser que se os quede la consola pensando. CTR+C, solucionado y tranquilo, si lo has hecho bien seguirá funcionando en segundo plano.

Comprobar que funciona

para comprobar que funciona haremos lo siguiente.

Instancia puerto 3308

#mysql -h127.0.0.1 -P3308 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>

Ahora, en la consola de MariaDB escribimos:

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name IN ('hostname','port'); +---------------+--------+ | Variable_name | Value | +---------------+--------+ | hostname | server | | port | 3308 | +---------------+--------+ 2 rows in set (0.00 sec)

Instancia puerto 3309

#mysql -h127.0.0.1 -P3309 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>

Ahora, en la consola de MariaDB escribimos:

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name IN ('hostname','port'); +---------------+--------+ | Variable_name | Value | +---------------+--------+ | hostname | server | | port | 3309 | +---------------+--------+ 2 rows in set (0.00 sec)

Si en cada caso coincida el puerto que muestra la base de datos ya lo tenemos. ¡Genial! A disfrutar.

Ojo

Cada vez que reinicies el servidor deberemos levantar los servicios e instancias. Para que se iniciasen de forma automática habría que crear unos demonios configurados para cada instancia. Pero eso para otro artículo.

Que pases buen día colega, y... paciencia.