Differences between MariaDB database and Mysql database

mariadb and MySQL belong to the same kind of database management system. However, mariadb and MySQL are different in the scope and purpose of use. Their main functions are to obtain the GPL license through the maintenance of the open source community. In terms of relationship, mariadb belongs to the development and upgrading version of database management system. It only represents a branch of MySQL.

MariaDB database management system is a branch of MySQL, which is mainly maintained by the open source community and licensed by GPL. One of the reasons for developing this branch is that after Oracle acquired mysql, there is a potential risk of closing the source of MySQL. Therefore, the community adopts the branch method to avoid this risk.

MariaDB aims to be fully compatible with MySQL, including API and command line, so that it can easily become a substitute for MySQL.

Installation of MariaDB database and Mysql database

Both MariaDB and Mysql databases belong to a distributed data structure and adopt the configuration mode of "port + service". If the port is not set properly, the port will often be occupied or the service will start abnormally. Therefore, it is demonstrated to use bat to install MariaDB or Mysql on the Windows platform.

MariaDB installation directory

1. Set the service name and port number for the database so that they can be distinguished (to avoid the conflict between port number and service name)

rem ==========Set Server Service Port==========

set SERVICE_MARIADB_PORT=10007

rem ==========Set Server Service Name==========
set SERVICE_MARIADB_NAME=MariaDB_Test

2. Check whether the service is installed on the local machine. If not, install the server

echo -----Install MariaDB Service-----	
rem set Mysql install path
cd /d "%~dp0mariadb-5.5.64-winx64"

rem install MariaDB Fully
reg query "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\%SERVICE_MARIADB_NAME%"
if %ERRORLEVEL% == 1 (
	echo -----Install MariaDB_Test Service-----
	del /f /s /q /a "%~dp0"\*.ini	
	del /f /s /q /a  "%~dp0"\mariadb-5.5.64-winx64\data\ib*
	%dir%:
	call modify.bat %AC_SERVICE_MARIADB_PORT%
	call mariadb_install.bat %AC_SERVICE_MARIADB_NAME%, %SERVICE_MARIADB_PORT%, %input%
	@goto normalSequence
) else (
	echo ----MariaDB_AC is already installed----
	@goto checkDatabaserunning
)

3. According to the requirements, transfer parameters to the configuration database (configuration service name and port number)

echo off
setlocal enabledelayedexpansion
set root=%cd%

@echo off >%root%/my-huge.ini
echo [client] >>my-huge.ini
echo port=%SERVICE_MARIADB_PORT% >>my-huge.ini
echo socket=/tmp/mysql.sock >>my-huge.ini
echo [mysqld] >>my-huge.ini
echo port=%SERVICE_MARIADB_PORT% >>my-huge.ini
echo socket=/tmp/mysql.sock >>my-huge.ini
echo basedir=%root:\=\\% >>my-huge.ini
echo datadir=%root:\=\\%\\data >>my-huge.ini
echo skip-external-locking >>my-huge.ini
echo key_buffer_size=384M >>my-huge.ini
echo max_allowed_packet=16M >>my-huge.ini
echo table_open_cache=512 >>my-huge.ini
echo sort_buffer_size=2M >>my-huge.ini
echo read_buffer_size=2M >>my-huge.ini
echo read_rnd_buffer_size=8M >>my-huge.ini
echo myisam_sort_buffer_size=64M >>my-huge.ini
echo thread_cache_size=8 >>my-huge.ini
echo query_cache_size=32M >>my-huge.ini
echo thread_concurrency=8 >>my-huge.ini
echo server-id=1 >>my-huge.ini
echo innodb_flush_log_at_trx_commit=0 >>my-huge.ini
echo innodb_buffer_pool_size=3072M >>my-huge.ini
echo [mysqldump] >>my-huge.ini
echo quick >>my-huge.ini
echo max_allowed_packet=16M >>my-huge.ini
echo [mysql] >>my-huge.ini
echo no-auto-rehash >>my-huge.ini
echo [myisamchk] >>my-huge.ini
echo key_buffer_size=256M >>my-huge.ini
echo sort_buffer_size=256M >>my-huge.ini
echo read_buffer=2M >>my-huge.ini
echo write_buffer=2M >>my-huge.ini
echo [mysqlhotcopy] >>my-huge.ini
echo interactive-timeout >>my-huge.ini
echo [WinMySQLAdmin] >>my-huge.ini  
echo Server=%root:\=\\%\\bin\mysqld.exe >>my-huge.ini

modify.bat implementation content

Execute modify After bat, my huge The INI configuration file becomes as follows:

[client] 
port=10007 
socket=/tmp/mysql.sock 
[mysqld] 
port=10007 
socket=/tmp/mysql.sock 
basedir=D:\\Program Files (x86)\\Test\\Server\\mariadb-5.5.64-winx64 
datadir=D:\\Program Files (x86)\\Test\\Server\\mariadb-5.5.64-winx64\\data 
skip-external-locking 
key_buffer_size=384M 
max_allowed_packet=16M 
table_open_cache=512 
sort_buffer_size=2M 
read_buffer_size=2M 
read_rnd_buffer_size=8M 
myisam_sort_buffer_size=64M 
thread_cache_size=8 
query_cache_size=32M 
thread_concurrency=8 
server-id=1 
innodb_flush_log_at_trx_commit=0 
innodb_buffer_pool_size=3072M 
[mysqldump] 
quick 
max_allowed_packet=16M 
[mysql] 
no-auto-rehash 
[myisamchk] 
key_buffer_size=256M 
sort_buffer_size=256M 
read_buffer=2M 
write_buffer=2M 
[mysqlhotcopy] 
interactive-timeout 
[WinMySQLAdmin]   
Server=D:\\Program Files (x86)\\Test\\Server\\mariadb-5.5.64-winx64\\bin\mysqld.exe 

4. Execute modify_install.bat to install the database

@echo off

echo ********************MariaDB Installing***********************
"%~dp0bin\mysqld" --install %SERVICE_MARIADB_NAME% --defaults-file="%~dp0my-huge.ini"

net start %SERVICE_MARIADB_NAME%

echo -----create Mysql database and table-----
set dbhost=127.0.0.1
set dbuser=root

set test1_sqlfile=test1.sql
set test2_sqlfile=test2.sql

cd /d "%~dp0"\bin

mysqladmin -P"%SERVICE_MARIADB_PORT%" -uroot flush-privileges password "%input%"

SET sqlpath="%~dp0"

mysql  -P"%SERVICE_MARIADB_PORT%" -u%dbuser% -p%input% < %sqlpath%%ac_sqlfile% --default-character-set=utf8
mysql -P"%SERVICE_MARIADB_PORT%" -u%dbuser% -p%input% < %sqlpath%%attentdance_sqlfile% --default-character-set=utf8

Note: input is the set database password, which can be transmitted through encryption to ensure data security.