MariaDB 笔记
看到一篇新闻，MariaDB 完成 C 轮 2700 万美金融资，阿里巴巴领投，看了以后觉得MariaDB挺眼熟的，就查了一下，然后感觉这个挺有意思，试了试。
而且对于像谷歌、亚马逊、FaceBook、微软、Twitter、阿里巴巴等这种公司，我觉得他们使用的产品很有可能在将来会比较流行。
11 月 2 日，MariaDB 宣布完成由阿里巴巴领投的 C 轮 2700 万美元的融资。融资完成后，MariaDB 社区将具备更强的实力参与数据库日益激烈的竞争。
MariaDB 是一家欧洲公司，其开发维护的 MariaDB 数据库是最受欢迎的开源数据库之一。总部位于芬兰赫尔辛基，在瑞典和美国设有办事处，拥有大约 1200 万名全球数据库用户。包括 booking.com、惠普、维珍移动、维基百科等。
MariaDB 是作为 MySQL 的一个分支。MySQL 被甲骨文收购后，MySQL 之父 Monty 为保证有一个始终开源的兼容 MySQL 的分支可用，创立了 MariaDB，名称来自其女儿 Maria 的名字。目前 MariaDB 在 Gartner 统计中是发展最快的开源数据库。
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。
MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。
安装 MariaDB
我的MariaDB解压在 C:\ProfessionalSoftware\MariaDB\mariadb-10.2.10-winx64 目录下，想把数据放到 D:/ProfessionalSoftWare/MariaDB/data 目录下，所以需要把解压完 C:\ProfessionalSoftware\MariaDB\mariadb-10.2.10-winx64\data 目录下的所有文件复制到 D:/ProfessionalSoftWare/MariaDB/data
如果不复制，会在启动的时候报 发生系统错误 1067 的错误，原因是 Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
安装服务
C:\ProfessionalSoftware\MariaDB\mariadb-10.2.10-winx64\bin
λ mysqld.exe --install MariaDB --defaults-file="C:\ProfessionalSoftware\MariaDB\mariadb-10.2.10-winx64\my.ini"
Service successfully installed.移除服务
C:\ProfessionalSoftware\MariaDB\mariadb-10.2.10-winx64\bin
λ mysqld.exe --remove MariaDB
Service successfully removed.启动MariaDB服务
C:\ProfessionalSoftware\MariaDB\mariadb-10.2.10-winx64\bin
λ net start MariaDB
MariaDB 服务正在启动 .
MariaDB 服务已经启动成功。添加用户
C:\ProfessionSofware\Mariadb\Mariadb-10.2\bin
λ mysqladmin -u root password "root"C:\ProfessionSofware\Mariadb\Mariadb-10.2\bin
λ mysqld --console
2017-11-05 10:10:18 11052 [Note] mysqld (mysqld 10.2.10-MariaDB) starting as process 10168 ...
2017-11-05 10:10:18 11052 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2017-11-05 10:10:18 11052 [Note] InnoDB: Uses event mutexes
2017-11-05 10:10:18 11052 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-11-05 10:10:18 11052 [Note] InnoDB: Number of pools: 1
2017-11-05 10:10:18 11052 [Note] InnoDB: Using generic crc32 instructions
2017-11-05 10:10:18 11052 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-11-05 10:10:18 11052 [Note] InnoDB: Completed initialization of buffer pool
2017-11-05 10:10:18 11052 [Note] InnoDB: Highest supported file format is Barracuda.
2017-11-05 10:10:18 11052 [Note] InnoDB: 128 out of 128 rollback segments are active.
2017-11-05 10:10:18 11052 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-11-05 10:10:18 11052 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-11-05 10:10:18 11052 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2017-11-05 10:10:18 11052 [Note] InnoDB: Waiting for purge to start
2017-11-05 10:10:18 11052 [Note] InnoDB: 5.7.20 started; log sequence number 1619987
2017-11-05 10:10:18 9084 [Note] InnoDB: Loading buffer pool(s) from C:\ProfessionSofware\Mariadb\Mariadb-10.2\data\ib_buffer_pool
2017-11-05 10:10:18 9084 [Note] InnoDB: Buffer pool(s) load completed at 171104 10:10:18
2017-11-05 10:10:18 11052 [Note] Plugin 'FEEDBACK' is disabled.
2017-11-05 10:10:18 11052 [Note] Server socket created on IP: '::'.
2017-11-05 10:10:18 11052 [Note] Reading of all Master_info entries succeded
2017-11-05 10:10:18 11052 [Note] Added new Master_info '' to hash table
2017-11-05 10:10:18 11052 [Note] mysqld: ready for connections.
Version: '10.2.10-MariaDB' socket: '' port: 3306 mariadb.org binary distribution
2017-11-05 11:00:24 10868 [Note] mysqld (unknown): Normal shutdown
2017-11-05 11:00:24 10868 [Note] Event Scheduler: Purging the queue. 0 events
2017-11-05 11:00:24 8456 [Note] InnoDB: FTS optimize thread exiting.
2017-11-05 11:00:24 10868 [Note] InnoDB: Starting shutdown...
2017-11-05 11:00:24 9084 [Note] InnoDB: Dumping buffer pool(s) to C:\ProfessionSofware\Mariadb\Mariadb-10.2\data\ib_buffer_pool
2017-11-05 11:00:24 9084 [Note] InnoDB: Buffer pool(s) dump completed at 171104 11:00:24
2017-11-05 11:00:25 10868 [Note] InnoDB: Shutdown completed; log sequence number 1620015
2017-11-05 11:00:25 10868 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-11-05 11:00:25 10868 [Note] mysqld: Shutdown completeC:\ProfessionSofware\Mariadb\Mariadb-10.2\bin
λ net start mariadb
MariaDB 服务正在启动 .
MariaDB 服务已经启动成功。
C:\ProfessionSofware\Mariadb\Mariadb-10.2\bin
λ net stop mariadb
MariaDB 服务正在停止.
MariaDB 服务已成功停止。my.ini 配置
[mysqld]
#datadir=C:/ProfessionalSoftware/MariaDB/MariaDB-10.3/data
datadir=D:/ProfessionalSoftWare/MariaDB/MariaDB-10.3/data
port=3306
#character-set-server=utf8
# utf8mb4 is a superset of utf8
character-set-server=utf8mb4
# mkdir for every database
innodb_file_per_table=1
# ignore lowercase
lower_case_table_names=1
# all import biggest 1024M file to mysql
max_allowed_packet=1024M
innodb_buffer_pool_size=1017M
#log
# Binary Log
log-bin=mysql-bin
# if query_time > 1s sql will log
long_query_time=1
# if query is slow, query will log version 5.6
slow-query-log=1
slow-query-log-file=D:/ProfessionalSoftWare/MariaDB/MariaDB-10.3/log/slow_query.log
general_log=ON
general_log_file=D:/ProfessionalSoftWare/MariaDB/MariaDB-10.3/log/all_query.log
[client]
port=3306
plugin-dir=C:/ProfessionalSoftware/MariaDB/MariaDB-10.3/lib/plugin
#default-character-set = utf8
# utf8mb4 is a superset of utf8
default-character-set = utf8mb4程序中使用
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.1.2</version>
</dependency>
driverClassName = org.mariadb.jdbc.Driver
url = jdbc:mariadb://localhost:3306/DB&useSSL=true&trustServerCertificate=true
user = admin
password = admin
Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/DB?user=root&password=myPassword");
final HikariDataSource ds = new HikariDataSource();
ds.setMaximumPoolSize(20);
ds.setDriverClassName("org.mariadb.jdbc.Driver");
ds.setJdbcUrl("jdbc:mariadb://localhost:3306/db");
ds.addDataSourceProperty("user", "root");
ds.addDataSourceProperty("password", "myPassword");
ds.setAutoCommit(false);
jdbc:(mysql|mariadb):[replication:|failover:|sequential:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]] <dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.25</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency><?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<pattern>%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.mariadb.jdbc" level="trace" additivity="false">
<appender-ref ref="STDOUT"/>
</logger>
<root level="error">
<appender-ref ref="STDOUT"/>
</root>
</configuration>References
[1] mariadb官网
[2] mariadb中文资料
[3] mariadb zip包安装
[4] mysql_install_dbexe
[5] mysql windows intall archive
[6] 在Windows下用 MSI 包安装 MariaDB
[7] 安装 MariaDB 并同时运行 MySQL
[8] 怎样从MySQL升级到 MariaDB？
[9] 升级mariadb
[10] java-connector-using-maven
[11] failover-and-high-availability-with-mariadb-connector-j
[12] option-batchmultisend-description
[13] 设置字符集和排序规则
[14] 一般查询日志
[15] 索引-缓存
[16] 在Windows安裝Zip (noinstall)版本的MariaDB
[17] Windows下如何安装MariaDB
[18] Windows 下 MariaDB (zip 免安装) 的手动安装与使用