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

安装服务

1
2
3
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.

移除服务

1
2
3
C:\ProfessionalSoftware\MariaDB\mariadb-10.2.10-winx64\bin
λ mysqld.exe --remove MariaDB
Service successfully removed.

启动MariaDB服务

1
2
3
4
C:\ProfessionalSoftware\MariaDB\mariadb-10.2.10-winx64\bin
λ net start MariaDB
MariaDB 服务正在启动 .
MariaDB 服务已经启动成功。

添加用户

1
2
C:\ProfessionSofware\Mariadb\Mariadb-10.2\bin
λ mysqladmin -u root password "root"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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 complete
1
2
3
4
5
6
7
8
9
10
C:\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 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[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

程序中使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<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>]]
1
2
3
4
5
6
7
8
9
10
<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?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 免安装) 的手动安装与使用