Questa guida è rivolta a chi vuole aumentare le performance e stabilità dei propri database.
La guida può comunque essere presa come spunto per Oracle MySQL.
La guida per l'installazione e disinstallazione è creata per il sistema operativo FreeBSD.
Per comodità ve la riporto solo in inglese, chi non sa leggere l'inglese non è adatto a lavorare a progetti di metin2, o almeno non come programmatore o sistemista.
Why prefer MariaDB instead MySQL?
MariaDB it's a fork of MySQL and it is compatible with MySQL. Forecasts indicate that the MariaDB fork replace Oracle MySQL Server as rdbms.
MariaDB have more features for performance and data integrity. One of this features is Aria storage engine. Aria is the upgrade of old MyISAM storage engine used on our Metin2 Private server. This old storage engine have some bugs and it isn't ACID like InnoDB. An upgrade of InnoDB is the fork XtraDB, used on MariaDB and Percona. So if you don't want/can't convert your metin2 tables from MyISAM to InnoDB or XtraDB (the best choice) you can use the similar storage engine: Aria.
How to install MariaDB on FreeBSD.
Administrate MariaDB it's like MySQL. The service is mysql-server and the path of MariaDB home directory is /var/db/mysql, like classical Oracle MySQL server.
Remove old MySQL server installation.
If you have mysql-server installed you need to deinstall it:
This thread was written when the lastest version of MariaDB was 10.1
Convert MyISAM to new storage engine
For better performance and security of your data you need to convert all of your table with a new storage engine. You can choose a different storage engine for each table.
I suggest you to choose between Aria (new MyISAM) or XtraDB (new InnoDB).
Choose Aria to read fastest and low write (example item_proto, mob_proto, shop, shop_item) or FULLTEXT index feature (to search fastest on text culomn).
Choose XtraDB to critical table with hight write frequency (example player, item, account).
To convert from one storage engine to another you have two ways:
This solution don't exclude the necessity of db backups.
Please scedule frequently backup of your applicative dbs from dump. See mysqldump utility.
For much safety, do also a datafile backup of entire mysql data directory (/var/db/mysql). It's for emergency restore of all mysql if dump import don't work.
Use datafile restore only for whole mysql directory and only if there aren't another solution with dump.
La guida può comunque essere presa come spunto per Oracle MySQL.
La guida per l'installazione e disinstallazione è creata per il sistema operativo FreeBSD.
Per comodità ve la riporto solo in inglese, chi non sa leggere l'inglese non è adatto a lavorare a progetti di metin2, o almeno non come programmatore o sistemista.
Why prefer MariaDB instead MySQL?
MariaDB it's a fork of MySQL and it is compatible with MySQL. Forecasts indicate that the MariaDB fork replace Oracle MySQL Server as rdbms.
MariaDB have more features for performance and data integrity. One of this features is Aria storage engine. Aria is the upgrade of old MyISAM storage engine used on our Metin2 Private server. This old storage engine have some bugs and it isn't ACID like InnoDB. An upgrade of InnoDB is the fork XtraDB, used on MariaDB and Percona. So if you don't want/can't convert your metin2 tables from MyISAM to InnoDB or XtraDB (the best choice) you can use the similar storage engine: Aria.
How to install MariaDB on FreeBSD.
Administrate MariaDB it's like MySQL. The service is mysql-server and the path of MariaDB home directory is /var/db/mysql, like classical Oracle MySQL server.
Remove old MySQL server installation.
If you have mysql-server installed you need to deinstall it:
- Make a security's backup of your mysql data directory: cd /var/db && tar -cvJf mysql.tar.xz mysql
- Make a real backup of your applicative db (metin2, website etc.) with dump. You can use the CLI utility mysqldump or GUI with Navicat (right click on db name->Dump SQL file->Structure and data).
- Stop Mysql-server with: service mysql-server stop
- Remove mysql directory: rm -rf /var/db/mysql
- Find the mysql version: mysql --version
- Deinstall mysql from ports: cd /usr/ports/databases/mysqlxx-server && make deinstall clean && cd /usr/ports/databases/mysqlxx-client && make deinstall clean
Where xx is the version find on previous step.
This thread was written when the lastest version of MariaDB was 10.1
- update your ports tree: portsnap fetch update
- Install MariaDB: cd /usr/ports/databases/mariadb101-server && make install clean
- if not present, enable mysql service: echo "mysql_enable=YES" >> /etc/rc.conf
- start mysql server: service mysql-server start
- Import your application databases (metin2, website etc.) from dump. Do not import from datafile!
Convert MyISAM to new storage engine
For better performance and security of your data you need to convert all of your table with a new storage engine. You can choose a different storage engine for each table.
I suggest you to choose between Aria (new MyISAM) or XtraDB (new InnoDB).
Choose Aria to read fastest and low write (example item_proto, mob_proto, shop, shop_item) or FULLTEXT index feature (to search fastest on text culomn).
Choose XtraDB to critical table with hight write frequency (example player, item, account).
To convert from one storage engine to another you have two ways:
- Via GUI like Navicat (one table at time). Right click on the table -> Design Table->Options->Engine. Change and save.
- Via query: ALTER TABLE <table_name> ENGINE=<engine>;
This solution don't exclude the necessity of db backups.
Please scedule frequently backup of your applicative dbs from dump. See mysqldump utility.
For much safety, do also a datafile backup of entire mysql data directory (/var/db/mysql). It's for emergency restore of all mysql if dump import don't work.
Use datafile restore only for whole mysql directory and only if there aren't another solution with dump.